jdbc.sgml

PostgreSQL7.4.6 for Linux

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/Attic/jdbc.sgml,v 1.50 2003/11/01 01:56:29 petere Exp $
-->

 <chapter id="jdbc">
  <title><acronym>JDBC</acronym> Interface</title>

  <indexterm zone="jdbc">
   <primary>JDBC</primary>
  </indexterm>

  <indexterm zone="jdbc">
   <primary>Java</primary>
  </indexterm>

  <para>
   <acronym>JDBC</acronym> is a core <acronym>API</acronym> of Java 1.1 and later.
   It provides a standard set of
   interfaces to <acronym>SQL</acronym>-compliant databases.
  </para>

  <para>
   <productname>PostgreSQL</> provides a <firstterm>type
   4</firstterm> <acronym>JDBC</acronym> driver.  Type 4 indicates
   that the driver is written in Pure Java, and communicates in the
   database system's own network protocol. Because of this, the driver
   is platform independent; once compiled, the driver can be used on
   any system.
  </para>

  <para>
   This chapter is not intended as a complete guide to
   <acronym>JDBC</acronym> programming, but should help to get you
   started. For more information refer to the standard
   <acronym>JDBC</acronym> <acronym>API</acronym> documentation.
   Also, take a look at the examples included with the source.
  </para>

 <sect1 id="jdbc-setup">
  <title>Setting up the <acronym>JDBC</acronym> Driver</title>

  <para>
   This section describes the steps you need to take before you can
   write or run programs that use the <acronym>JDBC</> interface.
  </para>

  <sect2 id="jdbc-build">
   <title>Getting the Driver</title>

   <para>
    Precompiled versions of the driver can be downloaded from
    the <ulink
    url="http://jdbc.postgresql.org"><productname>PostgreSQL</productname>
    <acronym>JDBC</acronym> web site</ulink>.
   </para>

   <para>
    Alternatively you can build the driver from source, but you should
    only need to do this if you are making changes to the source code.
    For details, refer to the <productname>PostgreSQL</>
    <link linkend="installation">installation instructions</link>.
    After installation, the driver should be found in
    <filename><replaceable>PREFIX</>/share/java/postgresql.jar</filename>.
    The resulting driver will be built for the version of Java you are
    running.  If you build with a 1.1 <acronym>JDK</> you will build a
    version that supports the <acronym>JDBC</> 1 specification, if you build
    with a 1.2 or 1.3 <acronym>JDK</> you will build a version that supports
    the <acronym>JDBC</> 2 specification, and finally if you build with a
    1.4 <acronym>JDK</acronym> you will build a version that supports the
    <acronym>JDBC</> 3 specification.
   </para>
  </sect2>

  <sect2 id="jdbc-classpath">
   <title>Setting up the Class Path</title>

   <indexterm zone="jdbc-classpath">
    <primary>class path</primary>
   </indexterm>

   <indexterm zone="jdbc-classpath">
    <primary>CLASSPATH</primary>
   </indexterm>

   <para>
    To use the driver, the JAR archive (named
    <filename>postgresql.jar</filename> if you built from source, otherwise
    it will likely be named <filename>pg&majorversion;jdbc1.jar</filename>,
    <filename>pg&majorversion;jdbc2.jar</filename>, or
    <filename>pg&majorversion;jdbc3.jar</filename> for the <acronym>JDBC</> 1,
    <acronym>JDBC</> 2, and <acronym>JDBC</> 3 versions respectively)
    needs to be included in the class path, either by putting it in the
    <envar>CLASSPATH</envar> environment variable, or by using flags on the
    <command>java</command> command line.
   </para>

   <para>
    For instance, assume we have an application that uses the
    <acronym>JDBC</acronym> driver to access a database, and that
    application is installed as
    <filename>/usr/local/lib/myapp.jar</filename>.  The PostgreSQL
    <acronym>JDBC</> driver installed as
    <filename>/usr/local/pgsql/share/java/postgresql.jar</>.  To run
    the application, we would use:
<programlisting>
export CLASSPATH=/usr/local/lib/myapp.jar:/usr/local/pgsql/share/java/postgresql.jar:.
java MyApp
</programlisting>
    </para>

    <para>
     Loading the driver from within the application is covered in
     <xref linkend="jdbc-use">.
    </para>
  </sect2>

  <sect2 id="jdbc-prepare">
   <title>Preparing the Database Server for <acronym>JDBC</acronym></title>

   <para>
    Because Java only uses TCP/IP connections, the
    <application>PostgreSQL</application> server must be configured to
    accept TCP/IP connections.  This can be done by setting 
    <literal>tcpip_socket = true</literal> in the 
    <filename>postgresql.conf</filename> file or by supplying the
    <option>-i</option> option flag when starting
    <command>postmaster</command>.
   </para>

   <para>
    Also, the client authentication setup in the
    <filename>pg_hba.conf</filename> file may need to be configured.
    Refer to <xref linkend="client-authentication"> for details.  The
    <acronym>JDBC</acronym> driver supports the <literal>trust</>,
    <literal>ident</>, <literal>password</>, <literal>md5</>, and
    <literal>crypt</> authentication methods.
   </para>
  </sect2>
 </sect1>

 <sect1 id="jdbc-use">
  <title>Initializing the Driver</title>

  <para>
   This section describes how to load and initialize the <acronym>JDBC</>
   driver in your programs.
  </para>

  <sect2 id="jdbc-import">
   <title>Importing <acronym>JDBC</acronym></title>

   <para>
    Any source that uses <acronym>JDBC</acronym> needs to import the
    <literal>java.sql</literal> package, using:

<programlisting>
import java.sql.*;
</programlisting>
   </para>

    <note>
     <para>
      Do not import the <literal>org.postgresql</literal> package. If
      you do, your source will not compile, as
      <command>javac</command> will get confused.
     </para>
    </note>
  </sect2>

  <sect2 id="jdbc-load">
   <title>Loading the Driver</title>

   <para>
    Before you can connect to a database, you need to load the
    driver. There are two methods available, and it depends on your
    code which is the best one to use.
   </para>

   <para>
    In the first method, your code implicitly loads the driver using the
    <function>Class.forName()</function> method.
    For <productname>PostgreSQL</>, you would use:

<programlisting>
Class.forName("org.postgresql.Driver");
</programlisting>

    This will load the driver, and while loading, the driver will automatically
    register itself with <acronym>JDBC</acronym>.
   </para>

    <note>
     <para>
      The <function>forName()</function> method can throw a
      <classname>ClassNotFoundException</classname> if the driver is
      not available.
     </para>
    </note>

   <para>
    This is the most common method to use, but restricts your code to
    use just <productname>PostgreSQL</productname>.  If your code may
    access another database system in the future, and you do not use
    any <productname>PostgreSQL</productname>-specific extensions, then
    the second method is advisable.
   </para>

   <para>
    The second method passes the driver as a parameter to the
    <acronym>JVM</acronym> as it starts, using the <option>-D</option>
    argument. Example:
<programlisting>
java -Djdbc.drivers=org.postgresql.Driver example.ImageViewer
</programlisting>
    In this example, the <acronym>JVM</acronym> will attempt to load
    the driver as part of its initialization. Once done, the
    <classname>ImageViewer</classname> is started.
   </para>

   <para>
    Now, this method is the better one to use because it allows your
    code to be used with other database packages without recompiling
    the code. The only thing that would also change is the connection
    <acronym>URL</acronym>, which is covered next.
   </para>

   <para>
    One last thing: When your code then tries to open a
    <classname>Connection</classname>, and you get a <errorname>No
    driver available</errorname> <classname>SQLException</classname>
    being thrown, this is probably caused by the driver not being in
    the class path, or the value in the parameter not being correct.
   </para>
  </sect2>

  <sect2 id="jdbc-connect">
   <title>Connecting to the Database</title>

   <para>
    With <acronym>JDBC</acronym>, a database is represented by a
    <acronym>URL</acronym> (Uniform Resource Locator).  With
    <application>PostgreSQL</application>, this takes one of the
    following forms:

    <itemizedlist>
     <listitem>
<synopsis>
jdbc:postgresql:<replaceable class="parameter">database</replaceable>
</synopsis>
     </listitem>

     <listitem>
<synopsis>
jdbc:postgresql://<replaceable class="parameter">host</replaceable>/<replaceable class="parameter">database</replaceable>
</synopsis>
     </listitem>

     <listitem>
<synopsis>
jdbc:postgresql://<replaceable class="parameter">host</replaceable>:<replaceable class="parameter">port</replaceable>/<replaceable class="parameter">database</replaceable>
</synopsis>
     </listitem>
    </itemizedlist>

    The parameters have the following meanings:

    <variablelist>
     <varlistentry>
      <term>
       <replaceable class="parameter">host</replaceable>
      </term>
      <listitem>
       <para>
        The host name of the server. Defaults to <literal>localhost</literal>.  To specify an IPv6 address your must enclose the <replaceable class="parameter">host</replaceable> parameter with square brackets, for example:
<programlisting>
jdbc:postgresql://[::1]:5740/accounting
</programlisting>
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
       <replaceable class="parameter">port</replaceable>
      </term>
      <listitem>
       <para>
        The port number the server is listening on. Defaults to the
        <productname>PostgreSQL</productname> standard port number (5432).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
       <replaceable class="parameter">database</replaceable>
      </term>
      <listitem>
       <para>
        The database name.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>

   <para>
    To connect, you need to get a <classname>Connection</classname> instance from 
    <acronym>JDBC</acronym>. To do this,
    you use the <function>DriverManager.getConnection()</function> method:

<programlisting>
Connection db = DriverManager.getConnection(url, username, password);
</programlisting>
   </para>
  </sect2>

  <sect2 id="jdbc-disconnect">
   <title>Closing the Connection</title>

   <para>
    To close the database connection, simply call the
    <function>close()</function> method to the <classname>Connection</classname>:
<programlisting>
db.close();
</programlisting>
   </para>
  </sect2>
 </sect1>


  <sect1 id="jdbc-query">
   <title>Issuing a Query and Processing the Result</title>

   <indexterm zone="jdbc-query">
    <primary>Statement</primary>
   </indexterm>

   <indexterm zone="jdbc-query">
    <primary>PreparedStatement</primary>
   </indexterm>

   <indexterm zone="jdbc-query">
    <primary>ResultSet</primary>
   </indexterm>

   <para>
    Any time you want to issue <acronym>SQL</acronym> statements to
    the database, you require a <classname>Statement</classname> or 
    <classname>PreparedStatement</classname> instance. Once you have 
    a <classname>Statement</classname> or 
    <classname>PreparedStatement</classname>, you can use issue a
    query. This will return a <classname>ResultSet</classname>
    instance, which contains the entire result (see <xref linkend="jdbc-query-with-cursor">
    here for how to alter this behaviour).
    <xref linkend="jdbc-query-example"> illustrates this process.
   </para>

   <example id="jdbc-query-example">
    <title>Processing a Simple Query in <acronym>JDBC</acronym></title>

    <para>
     This example will issue a simple query and print out the first
     column of each row using a <classname>Statement</classname>.
<programlisting>
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");
while (rs.next()) {
    System.out.print("Column 1 returned ");
    System.out.println(rs.getString(1));
}
rs.close();
st.close();
</programlisting>
    </para>

    <para>
     This example issues the same query as before but uses
     a <classname>PreparedStatement</classname>
     and a bind value in the query.
<programlisting>
int foovalue = 500;
PreparedStatement st = db.prepareStatement("SELECT * FROM mytable WHERE columnfoo = ?");
st.setInt(1, foovalue);
ResultSet rs = st.executeQuery();
while (rs.next()) {
    System.out.print("Column 1 returned ");
    System.out.println(rs.getString(1));
}
rs.close();
st.close();
</programlisting>
    </para>
   </example>

   <sect2 id="jdbc-query-with-cursor">
     <title>Getting results based on a cursor</title>

     <para>By default the driver collects all the results for the
       query at once. This can be inconvenient for large data sets so
       the JDBC driver provides a means of basing
       a <classname>ResultSet</classname> on a database cursor and
       only fetching a small number of rows.</para>

     <para>A small number of rows are cached on the
       client side of the connection and when exhausted the next
       block of rows is retrieved by repositioning the cursor.
     </para>

     <example>
       <title>Setting fetch size to turn cursors on and off.</title>

     <para>Changing code to cursor mode is as simple as setting the
       fetch size of the <classname>Statement</classname> to the
       appropriate size. Setting the fetch size back to 0 will cause
       all rows to be cached (the default behaviour).

<programlisting>
Statement st = db.createStatement();
// Turn use of the cursor on.
st.setFetchSize(50);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
   System.out.print("a row was returned.");
}
rs.close();
// Turn the cursor off.
st.setFetchSize(0);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
   System.out.print("many rows were returned.");
}
rs.close();
// Close the statement.
st.close();
</programlisting>
     </para>
   </example>
   </sect2>

   <sect2>
    <title>Using the <classname>Statement</classname> or <classname>PreparedStatement</classname> Interface</title>

    <para>
     The following must be considered when using the
     <classname>Statement</classname> or 
     <classname>PreparedStatement</classname> interface:

     <itemizedlist>