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.5) at IMADA, the appropriate driver is pg73jdbc3.jar. It can be found at most linux machines at IMADA as /usr/local/java/pg73jdbc3.jar. Further information on drivers can be found at the PostgreSQL JDBC web site.

Next, you need to update your environment variable CLASSPATH so that Java can find the driver (or specify the same info using the -classpath parameter when calling javac and java). 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 can be accessed through the note on using PostgreSQL at IMADA. 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. For allowed matches of SQL types and Java types in JDBC, see section 5.1.6 of Getting Started with the JDBC API guide.

One issue is the interaction of encoding of data between Java, PostgreSQL, and textfiles with data loaded using the \copy meta-command in psql. Encodings in databases can be specified when they are created by using the -E option of the createdb command, as in

createdb -E UNICODE dbname 

This uses UTF-8 as encoding in the database. Using the same for textfiles with data should eliminate encoding problems. The available encodings in PostgreSQL are listed in the documentation. The Unix command recode can be used to change encodings of files. The Unix command file will tell the encoding of files.