Using JDBC with PostgreSQL at IMADA

This note explains how to use JDBC with PostgreSQL, in particular with the PostgreSQL system at IMADA.

First, you need access to a driver. For the versions (PostgreSQL 7.3 and Java 1.4) at machine dbhost at IMADA, the appropriate driver is pg73jdbc3.jar. It can be found at most linux machines at IMADA as /usr/local/java/pg73jdbc3.jar. You can also download it from the PostgreSQL JDBC web site and store it in you own directory.

Next, you need to update your environment variable CLASSPATH so that Java can find the driver. Assuming the path to the driver is /dir/dir/pg73jdbc3.jar, you can set the variable by the command

setenv CLASSPATH ${CLASSPATH}:/dir/dir/pg73jdbc3.jar

if you are using the tcsh shell. In particular, the driver may be located in the current directory when you are running Java, in which case /dir/dir/pg73jdbc3.jar can be given as ./pg73jdbc3.jar .

In case you are running the bash shell, the command

export CLASSPATH=${CLASSPATH}:/dir/dir/pg73jdbc3.jar

should do it (if you do not know which shell you are using, simply try each command - only the one for your shell will succeed).

This should be done each time you log into a machine at IMADA, unless you include the command as a line in your .tcshrc or .bashrc configuration file (for tcsh and bash, respectively).

Here is an example of a Java program using JDBC, which should work on all IMADA machines. It accesses a database with a relation student, of which an instance was included in the data on Weekly Note 4. On this data, it produces the following output. The strings databasename, dbusername, and dbuserpassword in the program must be changed to suit your situation.

For more details on using JDBC, see the JDBC documentation for PostgreSQL and the documentation for the java.sql package in Java.