JDBC Interface
Chapter 5. JDBC Interface
- Table of Contents
- 5.1. Setting up the JDBC Driver
- 5.1.1. Getting the Driver
- 5.1.2. Setting up the Class Path
- 5.1.3. Preparing the Database for JDBC
- 5.2. Using the Driver
- 5.2.1. Importing JDBC
- 5.2.2. Loading the Driver
- 5.2.3. Connecting to the Database
- 5.2.4. Closing the Connection
- 5.3. Issuing a Query and Processing the Result
- 5.4. Performing Updates
- 5.5. Creating and Modifying Database Objects
- 5.6. Storing Binary Data
- 5.7. PostgreSQL Extensions to the JDBC API
- 5.7.1. Accessing the Extensions
- 5.7.2. Geometric Data Types
- 5.7.3. Large Objects
- 5.8. Using the driver in a multithreaded or a servlet environment
- 5.9. Connection Pools And DataSources
- 5.10. Further Reading
Author: Originally written by Peter T. Mount (
<[email protected]>), 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.
5.1. Setting up the JDBC Driver
5.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 Java 2 JDK (e.g., JDK 1.2 or JDK 1.3) you will build a version that supports the JDBC 2 specification.
5.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 jdbc7.2-1.1.jar or jdbc7.2-1.2.jar for the JDBC 1 and JDBC 2 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, I have an application that uses the JDBC driver to access a large database containing astronomical objects. I have the application and the JDBC driver installed in the /usr/local/lib directory, and the Java JDK installed in /usr/local/jdk1.3.1. To run the application, I would use:
export CLASSPATH=/usr/local/lib/finder.jar(1):/usr/local/pgsql/share/java/postgresql.jar:. java Finder
- (1)
- finder.jar contains the Finder application.
Loading the driver from within the application is covered in Section 5.2.
5.1.3. Preparing the Database 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 the Administrator's Guide for details. The JDBC Driver supports the trust, ident, password, md5, and crypt authentication methods.