Chapter 31. JDBC Interface

Table of Contents
31.1. Setting up the JDBC Driver
31.1.1. Getting the Driver
31.1.2. Setting up the Class Path
31.1.3. Preparing the Database Server for JDBC
31.2. Initializing the Driver
31.2.1. Importing JDBC
31.2.2. Loading the Driver
31.2.3. Connecting to the Database
31.2.4. Closing the Connection
31.3. Issuing a Query and Processing the Result
31.3.1. Getting results based on a cursor
31.3.2. Using the Statement or PreparedStatement Interface
31.3.3. Using the ResultSet Interface
31.4. Performing Updates
31.5. Calling Stored Functions
31.5.1. Using the CallableStatement Interface
31.5.2. Obtaining ResultSet from a stored function
31.6. Creating and Modifying Database Objects
31.7. Storing Binary Data
31.8. PostgreSQL Extensions to the JDBC API
31.8.1. Accessing the Extensions
31.8.2. Geometric Data Types
31.8.3. Large Objects
31.9. Using the Driver in a Multithreaded or a Servlet Environment
31.10. Connection Pools and Data Sources
31.10.1. Overview
31.10.2. Application Servers: ConnectionPoolDataSource
31.10.3. Applications: DataSource
31.10.4. Data Sources and JNDI
31.11. Further Reading

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

PostgreSQL provides a type 4 JDBC 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.

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

31.1. Setting up the JDBC Driver

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

31.1.1. Getting the Driver

Precompiled versions of the driver can be downloaded from the PostgreSQL JDBC web site.

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 PostgreSQL installation instructions. After installation, the driver should be found in PREFIX/share/java/postgresql.jar. The resulting driver will be built for the version of Java you are running. If you build with a 1.1 JDK you will build a version that supports the JDBC 1 specification, if you build with a 1.2 or 1.3 JDK you will build a version that supports the JDBC 2 specification, and finally if you build with a 1.4 JDK you will build a version that supports the JDBC 3 specification.

31.1.2. Setting up the Class Path

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

For instance, assume we have an application that uses the JDBC driver to access a database, and that application is installed as /usr/local/lib/myapp.jar. The PostgreSQL JDBC driver installed as /usr/local/pgsql/share/java/postgresql.jar. To run the application, we would use:

export CLASSPATH=/usr/local/lib/myapp.jar:/usr/local/pgsql/share/java/postgresql.jar:.
java MyApp

Loading the driver from within the application is covered in Section 31.2.

31.1.3. Preparing the Database Server for JDBC

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

Also, the client authentication setup in the pg_hba.conf file may need to be configured. Refer to Chapter 19 for details. The JDBC driver supports the trust, ident, password, md5, and crypt authentication methods.