PostgreSQL JDBC Documentation

I.                PostgreSQL JDBC Documentation

1. Introduction

If you can't find the information you need here, you should look at the PostgreSQL documentation which includes documentation for the JDBC interface to PostgreSQL. Current production documentation (7.1) can be found here. Current development documentation (7.2) can be found here.

The second place to look is the pgsql-jdbc mail list archives.

A third place is Sun's JDBC site.

So, why have a documentation here? Well it's often the first place people look. Also, the main documentation doesn't get updated that often (usually at release time). Here it's hoped to have current information.

1.1.     Index:

1.     How to compile the driver?

2.    How to install the driver?

3.    How to load the driver in your code?

4.    Why ANT?

5.    Why still use make?

6.    How do libpq environment variables affect the driver?

2. How to compile the driver?

To compile the JDBC driver, you need to have ANT installed. ANT is part of the Apache Jakarta project.

Once you have ant installed, change to the root of the PostgreSQL source tree and run configure. e.g.:

$> ./configure --with-java

This will configure the PostgreSQL build to include the JDBC driver.

Now run make:

$> make

This builds PostgreSQL, the interfaces but this time JDBC as well.

What version of JDBC it builds is dependent on what version of the JDK you have installed.

JDK Installed	JDBC Driver built	Definition
JDK1.1.8	JDBC 1.2	The original specification driver
J2SE	JDBC 2.2 Standard	The current specification driver.
J2EE	JDBC 2.2 Standard + JDBC 2.0 Enterprise	As Standard edition, but support for the javax.sql package is also included.

Legend:
SE - Standard Edition, your JVM has not got the javax.sql package installed.
EE - Enterprise Edition. This includes the javax.sql package (as well as EJB's etc). It is an optional download from Sun.

3. How to install the driver?

Once you have compiled the driver, you need to install it. To install, simply run make again:

$> make install

This then installs the jar file (postgresql.jar) into the {dir here} directory.

Never extract the contents of these jar files!
The operation of the driver relies on resources contained within them, and we cannot guarantee it will work if the contents are expanded!

4. How to load the driver into your code?

There's several things you must do before using JDBC with PostgreSQL.

1.     Place postgresql.jar into your CLASSPATH.

2.     Have the PostgreSQL postmaster running with the -i flag!

3.     Have a database and know it's JDBC URL.

4.     Know the Drivers base class

5.     Load the Driver

4.1.     First the classpath.

When java loads any class, it searches a list known as the classpath. This is a list of directories where classes are placed, or a list of jar files (archives containing classes and other resources) or both.

To use the JDBC driver, you need to put it into your class path. You can do this in many different methods, but the most command are:

• Setting the CLASSPATH environment variable.
• putting it on the command line using the -cp parameter.
• placing it in the JVM/s lib/ext directory.

The following table shows the pros and cons of each method:

Method	Description	Benefits	Problems
CLASSPATH environment variable	You add the environment variable before you run your application. export CLASSPATH=.:postgresql.jar set CLASSPATH .;c:\postgresql.jar	Good for development Can be used with command line based applications	Cannot be used with: Applets Servlets/JSP EJB containers Daemon applications
lib/ext directory of the JVM	You place the jar file in the JVM's lib/ext directory. For example: c:\java\jre\lib\ext\postgresql.jar	All jar files in this directory are available to anything that uses this JVM. I tend to use this method in production.	Can't use it with Applets (unless you have access to the users browser installation). Bad for development. Because all uses of the JVM use it, you end up with two copies of the same classes available and all hell breaks loose.
container's ext directory	Some projects have their own ext directory (JBoss, Tomcat etc).	Same as lib/ext	Same as lib/ext. Development problems are not as bad as the JVM equivalent but still there.
-cp on the command line	When you run the java command you set the vm class path: java -cp .:postgresql.jar		You have to include where the core jar files are located as this overides all other methods!

4.1.1.                          Examples:

Setting the CLASSPATH environment variable:

$> export CLASSPATH=/usr/share/lib/postgresql.jar:${CLASSPATH}
$> java myApp

4.2.     Now the postmaster.

Because JDBC uses the network, and Java does not support Unix domain sockets, you must run the backend with the -i flag. ie:

$> postmaster -i -S

Also you must configure the pg_hba.conf file to allow the machine running the jdbc driver access. Details on how to do this are in that file and on the main PostgreSQL site.

4.3.     The JDBC URL

In JDBC all url's begin with jdbc:protocol: This is the standard. After this is driver specific, and no two drivers are the same.

So our url? It's one of the following:

jdbc:postgresql:database
jdbc:postgresql://host/database
jdbc:postgresql://host:port/database

where database is the database to connect to, host the server to connect to, and port the port number.

If left out, host defaults to localhost (not 127.0.0.1 see applets!) and port to 5432 (configurable at build time).

4.4.     The Driver Class

All JDBC drivers have a base class. For PostgreSQL it's:

org.postgresql.Driver

4.5.     Loading the Driver

Again more choices, but this time there's only two.

The first allows your code to be totally independent on what driver it uses as it doesn't involve hard coding the driver into your code.

You do this by setting the jdbc.drivers system property.

For example, for command line apps you can use:

$> java -Djdbc.drivers=org.postgresql.Driver uk.org.retep.finder.Main

Here, the JVM upon startup will load the drivers automatically.

Some applications (JBoss, Tomcat etc) support a .properties file which they use to save putting this on the command line.

The second method is the most common and involves you loading the driver yourself. It's simple:

Class.forName("org.postgresql.Driver");

From then on you can get connections from DriverManager.

Note: Class.forName() throws ClassNotFoundException so you will need to catch and handle it.

5. Why ant?

ANT (part of the Apache Jakarta project) is an open source build tool built in java.

In 6.3.x the Makefile for the driver was some 27K and had several support classes needed to detect the JVM version. With ant, you have a build.xml file which defines how the driver is built. This is now only 6K. Ant build files are easier to maintain.

We realize requiring ANT makes it more difficult for the end user to build the driver as it requires installing yet another component. We are evaluating moving away from ANT for this reason. If you have strong feelings either way, email them to the mail list.

6. Why still use make?

Why have the Makefile and still use make when we use ANT to do the actual build.

Peter Eisentraut replied this which sums up nicely why we still do:

The nice thing about standards is that there are so many to choose from. At PostgreSQL, we use the GNU-style build system, which is already the "standard" for compiling almost all open source projects.

One important aspect of this being a "system" is that it works uniformly. That means you can go to any subdirectory and run 'make; make install', etc., no matter if Ant, MakeMaker, or broken-Python-build-environment is running behind the scenes.

Now normally you run make just after configure, and assuming you used --with-java then the driver will be built.

However, here's the supported ways of calling make

Command Line	What it does
make make all	Either of these will build the driver
make install make installdirs	Installs the compiled driver
make uninstall	Uninstalls the compiled driver
make clean make distclean make maintainer-clean	These clean the source tree, removing any .class files (and Driver.java) from the source.

7. How do libpq environment variables affect the driver?

Sometimes people familiar with libpq expect the JDBC driver to accept libpq environment variables either through the system or in the Properties object you can pass when you open a connection.

This does not work. This is because the JDBC driver does not use libpq. It is a completely independent implementation of the network protocol based on the JDBC specifications.

At this time, the only properties supported are those listed below. However these can be overidden in the JDBC url.

Property	Description
user	The username to connect as
password	The password to use
PGHOST	The hostname to connect to
PGPORT	The port to connect to
charSet	The character set to use for data sent to the database or recieved from the database
compatible	The driver version you wish to remain compatilble with. For example in 7.2 certain functionality changes between the 7.1 driver and the 7.2 driver were made that are not backwardly compatible. Setting this parameter to 7.1 will revert the 7.2 driver back to the 7.1 behavior.

These parameters can be either passed in the Parameters object when creating a connection, or can be specified in the JCDC URL (i.e. jdbc:postgresql://localhost:5432/testdb?charSet=LATIN1)