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:
- Wrong database port? Please check the server settings
- Wrong database user, or database user does not have admin rights
Additional Literature: PostgreSQL JDBC documentation