Chapter 8. JDBC Interface

Table of Contents
8.1. Setting up the JDBC Driver
8.1.1. Getting the Driver
8.1.2. Setting up the Class Path
8.1.3. Preparing the Database for JDBC
8.2. Using the Driver
8.2.1. Importing JDBC
8.2.2. Loading the Driver
8.2.3. Connecting to the Database
8.2.4. Closing the Connection
8.3. Issuing a Query and Processing the Result
8.3.1. Using the Statement or PreparedStatement Interface
8.3.2. Using the ResultSet Interface
8.4. Performing Updates
8.5. Creating and Modifying Database Objects
8.6. Storing Binary Data
8.7. PostgreSQL Extensions to the JDBC API
8.7.1. Accessing the Extensions
8.7.2. Geometric Data Types
8.7.3. Large Objects
8.8. Using the driver in a multi-threaded or a servlet environment
8.9. Further Reading

Author: Originally written by Peter T. Mount (), the original author of the JDBC driver.

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. The basic example is used here.

8.1. Setting up the JDBC Driver

8.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. Although you should only need to do this if you are making changes to the source code.

Starting with PostgreSQL version 7.1, the JDBC driver is built using Ant, a special tool for building Java-based packages. You should download Ant from the Ant web site and install it before proceeding. Precompiled Ant distributions are typically set up to read a file .antrc in the current user's home directory for configuration. For example, to use a different JDK than the default, this may work:

JAVA_HOME=/usr/local/sun-jdk1.3
JAVACMD=$JAVA_HOME/bin/java

To build the driver, add the --with-java option to your configure command line, e.g.,

$ ./configure --prefix=xxx --with-java ...

This will build and install the driver along with the rest of the PostgreSQL package when you issue the make/gmake and make/gmake install commands. If you only want to build the driver and not the rest of PostgreSQL, change into the directory src/interfaces/jdbc and issue the respective make/gmake command there. Refer to the PostgreSQL installation instructions for more information about the configuration and build process.

When building the driver from source the jar file that is created will be named postgresql.jar. The build will create this file in the src/interfaces/jdbc/jars directory. 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 jdbc1 specification, if you build with a Java2 JDK (i.e. JDK1.2 or JDK1.3) you will build a version that supports the jdbc2 specification.

Note: Do not try to build the driver by calling javac directly, as the driver uses some dynamic loading techniques for performance reasons, and javac cannot cope. Do not try to run ant directly either, because some configuration information is communicated through the makefiles. Running ant directly without providing these parameters will result in a broken driver.