Chapter 12. JDBC

Table of Contents
Building the PostgreSQL JDBC Driver
Using the PostgreSQL Driver
Basic JDBC Syntax
Issues Specific to PostgreSQL and JDBC

This chapter covers JDBC (Java DataBase Connectivity), which is a set of classes and methods available for the Java programming language. The use of JDBC with Java is a simple, generic, and portable way of interacting with different types of databases. For this chapter, some existing knowledge on how to program in Java is assumed.

The JDBC interfaces, defined by Sun, cover all the interactions you can have with a standard SQL database. The vendor (in this case, PostgreSQL) supplies concrete implementations that implement these interfaces. These concrete implementations handle the vendor-specific interactions with the database: connecting, logging in, stored procedures, and so forth. These interfaces are designed this way so that a program using JDBC can connect to any JDBC-compliant database, without rewriting the code. However, there are some caveats.

One issue is that JDBC does not do any client-side SQL parsing or syntax checking. SQL statements are passed off transparently to the database, whether or not they are valid. Therefore, if the SQL being written is valid on one vendor's database, but invalid on another vendor's database, the implement won't know until the actual connection is made and the SQL is sent across. Sun is attempting to deal with this problem, and there may be some provisions made to correct this, either in later versions of JDBC or in a different standard.

Another issue is that each vendor has additional helper classes specific to that vendor. For instance, PostgreSQL has extensions for geometric data types. Other vendors won't support this extension; they are specific to PostgreSQL. If you use such a vendor-specific class, your program will not work with another JDBC database, despite using the JDBC "standard."

One advantage of the PostgreSQL JDBC driver is that it is a "Type 4" driver. This means that it is written in Pure Java, so it can be taken anywhere, and used anywhere, as long as the platform it is used on has TCP/IP capabilities, because the driver only connects via TCP/IP.

Building the PostgreSQL JDBC Driver

This section assumes that you already have a PostgreSQL database set up and ready to go. Make sure that you have it set to accept incoming TCP/IP connections. This can be configured when running the postmaster command. For more information on database start-up options, see Chapter 9.

Before you can use JDBC, you must build the PostgreSQL JDBC drivers. To do this, you must have the Java source code which is used to build the driver. This source is included both in the complete PostgreSQL package, and in the opt package. These can be downloaded from the PostgreSQL site; for more information about downloading and installing these, see Chapter 2.

You also need Ant. Ant is a standard build system for Java products, somewhat similar to gmake, and is created by Apache's Jakarta project. It is required to build the PostgreSQL JDBC driver. For more information on Ant, see: Make sure that Ant's bin directory is in your path.

First you need to configure the makefile system to recognize that you are using Java. If you did not originally build PostgreSQL with Java support, move into the top level of the PostgreSQL source tree, and type configure with-java, along with any other configure options you originally used. This will regenerate makefiles and, if necessary, will add support for Java.

Next is actually building the driver and implementations. Change to the src/interfaces/jdbc directory, and issue the gmake command. This will build two jar files: postgresql.jar, containing the Driver class and other concrete implementations, and postgresql-examples.jar, containing compiled example classes.