jdbc.sgml

PostgreSQL7.4.6 for Linux

      <listitem>
       <para>
        You can use a single <classname>Statement</classname> instance
        as many times as you want. You could create one as soon as you
        open the connection and use it for the connection's
        lifetime. But you have to remember that only one
        <classname>ResultSet</classname> can exist per
        <classname>Statement</classname> or 
        <classname>PreparedStatement</classname> at a given time.
       </para>
      </listitem>

      <listitem>
       <para>
        If you need to perform a query while processing a
        <classname>ResultSet</classname>, you can simply create and
        use another <classname>Statement</classname>.
       </para>
      </listitem>

      <listitem>
       <para>
        If you are using threads, and several are using the database,
        you must use a separate <classname>Statement</classname> for
        each thread. Refer to <xref linkend="jdbc-thread"> if you are
        thinking of using threads, as it covers some important points.
       </para>
      </listitem>

      <listitem>
       <para>
        When you are done using the <classname>Statement</classname>
        or <classname>PreparedStatement</classname>
        you should close it.
       </para>
      </listitem>
     </itemizedlist>
    </para>
   </sect2>

   <sect2>
    <title>Using the <classname>ResultSet</classname> Interface</title>

    <para>
     The following must be considered when using the
     <classname>ResultSet</classname> interface:

     <itemizedlist>
      <listitem>
       <para>
        Before reading any values, you must call
        <function>next()</function>. This returns true if there is a
        result, but more importantly, it prepares the row for
        processing.
       </para>
      </listitem>

      <listitem>
       <para>
        Under the <acronym>JDBC</acronym> specification, you should
        access a field only once. It is safest to stick to this rule,
        although at the current time, the
        <productname>PostgreSQL</productname> driver will allow you to
        access a field as many times as you want.
       </para>
      </listitem>

      <listitem>
       <para>
        You must close a <classname>ResultSet</classname> by calling
        <function>close()</function> once you have finished using it.
       </para>
      </listitem>

      <listitem>
       <para>
        Once you make another query with the
        <classname>Statement</classname> used to create a
        <classname>ResultSet</classname>, the currently open
        <classname>ResultSet</classname> instance is closed
        automatically.
       </para>
      </listitem>

     </itemizedlist>
    </para>
   </sect2>
  </sect1>

  <sect1 id="jdbc-update">
   <title>Performing Updates</title>

   <para>
    To change data (perform an <command>INSERT</command>,
    <command>UPDATE</command>, or <command>DELETE</command>) you use
    the <function>executeUpdate()</function> method.  This method is
    similar to the method <function>executeQuery()</function> used to
    issue a <command>SELECT</command> statement, but it doesn't return
    a <classname>ResultSet</classname>; instead it returns the number
    of rows affected by the <command>INSERT</command>,
    <command>UPDATE</command>, or <command>DELETE</command> statement.
    <xref linkend="jdbc-delete-example"> illustrates the usage.
   </para>

  <example id="jdbc-delete-example">
   <title>Deleting Rows in <acronym>JDBC</acronym></title>
   <para>
     This example will issue a simple <command>DELETE</command>
     statement and print out the number of rows deleted.
<programlisting>
int foovalue = 500;
PreparedStatement st = db.prepareStatement("DELETE FROM mytable WHERE columnfoo = ?");
st.setInt(1, foovalue);
int rowsDeleted = st.executeUpdate();
System.out.println(rowsDeleted + " rows deleted");
st.close();
</programlisting>
   </para>
  </example>
  </sect1>


  <sect1 id="jdbc-callproc">
    <title>Calling Stored Functions</title>

    <para><productname>PostgreSQL's</productname> JDBC driver fully
      supports calling <productname>PostgreSQL</productname> stored
      functions.</para>

    <example id="jdbc-call-function">
      <title>Calling a built in stored function</title>

      <para>This example shows how to call
	a <productname>PostgreSQL</productname> built in
	function, <command>upper</command>, which simply converts the
	supplied string argument to uppercase.

<programlisting>
// Turn transactions off.
con.setAutoCommit(false);
// Procedure call.
CallableStatement upperProc = con.prepareCall("{ ? = call upper( ? ) }");
upperProc.registerOutParameter(1, Types.VARCHAR);
upperProc.setString(2, "lowercase to uppercase");
upperProc.execute();
String upperCased = upperProc.getString(1);
upperProc.close();
</programlisting>
      </para>
    </example>

    <sect2>
      <title>Using the <classname>CallableStatement</classname> Interface</title>

      <para>
	All the considerations that apply
	for <classname>Statement</classname>
	and <classname>PreparedStatement</classname> apply
	for <classname>CallableStatement</classname> but in addition
	you must also consider one extra restriction:
      </para>

      <itemizedlist>
	<listitem>
	  <para>You can only call a stored function from within a
	    transaction.</para>
	</listitem>
      </itemizedlist>

    </sect2>

    <sect2>
      <title>Obtaining <classname>ResultSet</classname> from a stored function</title>
     
      <para><productname>PostgreSQL's</productname> stored function
	can return results by means of a <type>refcursor</type>
	value. A <type>refcursor</type>.</para>

      <para>As an extension to JDBC,
	the <productname>PostgreSQL</productname> JDBC driver can
	return <type>refcursor</type> values
	as <classname>ResultSet</classname> values.</para>

      <example id="get-refcursor-from-function-call">
	<title>Getting <type>refcursor</type> values from a
	function</title>

	<para>When calling a function that returns
	  a <type>refcursor</type> you must cast the return type
	  of <function>getObject</function> to
	  a <classname>ResultSet</classname></para>

<programlisting>
// Turn transactions off.
con.setAutoCommit(false);
// Procedure call.
CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
proc.registerOutParameter(1, Types.Other);
proc.setInt(2, -1);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
while (results.next()) {
  // do something with the results...
}
results.close();
proc.close();
</programlisting>
      </example>

      <para>It is also possible to treat the <type>refcursor</type>
      return value as a distinct type in itself. The JDBC driver
	provides
      the <classname>org.postgresql.PGRefCursorResultSet</classname>
	class for this purpose.</para>

      <example>
	<title>Treating <type>refcursor</type> as a distinct
	type</title>

<programlisting>
con.setAutoCommit(false);
CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
proc.registerOutParameter(1, Types.Other);
proc.setInt(2, 0);
org.postgresql.PGRefCursorResultSet refcurs 
    = (PGRefCursorResultSet) con.getObject(1);
String cursorName = refcurs.getRefCursor();
proc.close();
</programlisting>
      </example>
    </sect2>

  </sect1>


  <sect1 id="jdbc-ddl">
   <title>Creating and Modifying Database Objects</title>

   <para>
    To create, modify or drop a database object like a table or view
    you use the <function>execute()</function> method.  This method is
    similar to the method <function>executeQuery()</function>, but it
    doesn't return a result.  <xref linkend="jdbc-drop-table-example">
    illustrates the usage.
   </para>

  <example id="jdbc-drop-table-example">
   <title>Dropping a Table in JDBC</title>
   <para>
     This example will drop a table.
<programlisting>
Statement st = db.createStatement();
st.execute("DROP TABLE mytable");
st.close();
</programlisting>
   </para>
  </example>
  </sect1>

 <sect1 id="jdbc-binary-data">
  <title>Storing Binary Data</title>

  <indexterm zone="jdbc-binary-data">
   <primary>bytea</primary>
   <secondary sortas="JDBC">in JDBC</secondary>
  </indexterm>

  <indexterm zone="jdbc-binary-data">
   <primary>large object</primary>
   <secondary sortas="JDBC">in JDBC</secondary>
  </indexterm>

  <para>
    <application>PostgreSQL</application> provides two distinct ways to 
    store binary data.  Binary data can be stored in a table using 
    the data type <type>bytea</type> or by using the Large Object
    feature which stores the binary data in a separate table in a special 
    format and refers to that table by storing a value of type 
    <type>oid</type> in your table.
  </para>

  <para>
    In order to determine which method is appropriate you 
    need to understand the limitations of each method.  The 
    <type>bytea</type> data type is not well suited for storing very 
    large amounts of binary data.  While a column of type 
    <type>bytea</type> can hold up to 1 GB of binary data, it would 
    require a huge amount of memory to 
    process such a large value.  The Large Object method for 
    storing binary data is better suited to storing very large values, 
    but it has its own limitations.  Specifically deleting a row 
    that contains a Large Object reference does not delete the Large Object.
    Deleting the Large Object is a separate operation that needs to
    be performed.  Large Objects also have some security
    issues since anyone connected to the database can view 
    and/or modify any Large Object, even if they don't have 
    permissions to view/update the row containing the Large Object reference.
  </para>

  <para>
    Version 7.2 was the first release of the <acronym>JDBC</acronym> driver 
    that supports the <type>bytea</type> data type.  The introduction of 
    this functionality in 7.2 has introduced a change in behavior 
    as compared to previous releases.  Since 7.2, the methods 
    <function>getBytes()</function>, <function>setBytes()</function>, 
    <function>getBinaryStream()</function>, and 
    <function>setBinaryStream()</function> operate on 
    the <type>bytea</type> data type.  In 7.1 and earlier, these methods operated 
    on the <type>oid</type> data type associated with Large Objects.  
    It is possible to revert the driver back to the old 7.1 behavior 
    by setting the property <literal>compatible</literal> on 
    the <classname>Connection</classname> object to the value
    <literal>7.1</literal>.
  </para>

  <para>
    To use the <type>bytea</type> data type you should simply use 
    the <function>getBytes()</function>, <function>setBytes()</function>,
    <function>getBinaryStream()</function>, or 
    <function>setBinaryStream()</function> methods.
  </para>

  <para>
    To use the Large Object functionality you can use either the 
    <classname>LargeObject</classname> class
    provided by the <application>PostgreSQL</application> 
    <acronym>JDBC</acronym> driver, or by using the 
    <function>getBLOB()</function> and <function>setBLOB()</function>
    methods.
  </para>

  <important>
   <para>
    You must access Large Objects within an <acronym>SQL</acronym>
    transaction block.  You can start a transaction block by calling
    <function>setAutoCommit(false)</function>.
   </para>
  </important>

  <note>
   <para>
      In a future release of the
      <acronym>JDBC</acronym> driver, the <function>getBLOB()</function>
      and <function>setBLOB()</function> methods may no longer 
      interact with Large Objects and will instead work on the data type
      <type>bytea</type>.  So it is recommended that you 
      use the <classname>LargeObject</classname> <acronym>API</acronym> 
      if you intend to use Large Objects.
   </para>
  </note>

  <para>
   <xref linkend="jdbc-binary-data-example"> contains some examples on
   how to process binary data using the PostgreSQL <acronym>JDBC</> driver.
  </para>

  <example id="jdbc-binary-data-example">
   <title>Processing Binary Data in <acronym>JDBC</></title>

   <para>
    For example, suppose you have a table containing the file names of
    images and you also want to store the image in a <type>bytea</type>
    column:
<programlisting>
CREATE TABLE images (imgname text, img bytea);
</programlisting>
   </para>

   <para>
    To insert an image, you would use:
<programlisting>
File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps = conn.prepareStatement("INSERT INTO images VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setBinaryStream(2, fis, file.length());
ps.executeUpdate();
ps.close();
fis.close();
</programlisting>

    Here, <function>setBinaryStream()</function> transfers a set number
    of bytes from a stream into the column of type <type>bytea</type>.
    This also could have been done using the <function>setBytes()</function>
    method if the contents of the image was already in a 
    <classname>byte[]</classname>. 
   </para>

   <para>
    Retrieving an image is even easier.  (We use
    <classname>PreparedStatement</classname> here, but the
    <classname>Statement</classname> class can equally be used.)

<programlisting>
PreparedStatement ps = con.prepareStatement("SELECT img FROM images WHERE imgname = ?");
ps.setString(1, "myimage.gif");
ResultSet rs = ps.executeQuery();
if (rs != null) {
    while (rs.next()) {
        byte[] imgBytes = rs.getBytes(1);
        // use the data in some way here
    }
    rs.close();
}
ps.close();
</programlisting>
   </para>

   <para>
    Here the binary data was retrieved as an
    <classname>byte[]</classname>.  You could have used a 
    <classname>InputStream</classname> object instead.  
   </para>

   <para>
    Alternatively you could be storing a very large file and want to use
    the <classname>LargeObject</classname> <acronym>API</acronym> to 
    store the file:
<programlisting>
CREATE TABLE imageslo (imgname text, imgoid oid);
</programlisting>
   </para>

   <para>
    To insert an image, you would use:
<programlisting>
// All LargeObject API calls must be within a transaction block
conn.setAutoCommit(false);

// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();

// Create a new large object
int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);

// Open the large object for writing
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);

// Now open the file
File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);

// Copy the data from the file to the large object
byte buf[] = new byte[2048];
int s, tl = 0;
while ((s = fis.read(buf, 0, 2048)) > 0) {
    obj.write(buf, 0, s);
    tl += s;
}