Frank4DD, @2008-2014

Introduction

JDBC is a set of classes and interfaces written in Java that allows Java programs to access a database. PostgreSQL is a popular open source database, and a free JDBC driver is provided.

Download driver package

At the time of testing, I got a package called postgresql-9.2-1003.jdbc4.jar. The latest driver is available here. Note that there are different versions to match the installed Java version.

Install the driver package

In order to work with the driver, Java must be able to find it when called. We can achieve this by adding the drivers jar file location to the Java classpath, or by simply placing the driver files into the Java standard directory for extensions, for JAVA JRE this is the $JAVA_HOME/jre/lib/ext (i.e. /usr/lib64/jvm/jre-1.6.0-ibm/lib/ext/) directory. We can simply copy the postgresql-9.2-1003.jdbc4.jar file there.

Use the driver to access PostgreSQL through Java

The following example code JdbcTestPostgreSQL.java can be used to quickly access and test the JDBC connection. Unlike most other databases, PostgreSQL only allows connections from the local machine by default. In order to connect over the network, the "listen_addresses" needs to be updated (postgresql.conf), and the host-based authentication set in $PGDATA/pg_hba.conf, and the DB restarted after these changes. 

//JdbcTestpostgreSQL.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

class JdbcTestPostgreSQL {
   public static void main (String args[]) {
      try {
         Class.forName("org.postgresql.Driver");
      }
      catch (ClassNotFoundException e) {
         System.err.println (e);
         System.exit (-1);
      }
      try {
         // open connection to database
         Connection connection = DriverManager.getConnection(
         //"jdbc:postgresql://dbhost:port/dbname", "user", "dbpass");
         "jdbc:postgresql://127.0.0.1:5432/postgres", "pgsql", "p0stgr3s1");

         // build query, here we get info about all databases"
         String query = "SELECT datname FROM pg_database WHERE datistemplate = false";

         // execute query
         Statement statement = connection.createStatement ();
         ResultSet rs = statement.executeQuery (query);

         // return query result
         while ( rs.next () )
            // display table name
            System.out.println ("PostgreSQL Query result: " + rs.getString ("datname"));
         connection.close ();
      }
      catch (java.sql.SQLException e) {
         System.err.println (e);
         System.exit (-1);
      }
   }
}

Compile and test run

fm@susie:~> javac JdbcTestPostgreSQL.java
fm@susie:~> java JdbcTestPostgreSQL
PostgreSQL Query result: postgres

Should this test fail, typical reasons are:

Additional Literature: PostgreSQL JDBC documentation