Using JDBC with Postgres and MS SQL Server: A Worked Example

JDBC (Java Database Connectivity) is the generalised Java to RDBMS interconnection layer, analagous to ODBC in the Windows world, or Perl's DBI framework if you prefer. Using JDBC with an appropriate RDBMS-specific driver .jar file, you can write highly portable Java programs that retrieve records (or, for that matter, create tables, insert or update records) from any RDBMS tables using standard SQL statements. Any Java program can use JDBC (although applets typically run into difficulty with the applet security model if they try to open network connections to any machine other than the webserver the applet came from).

Here we show how to write Java programs which use JDBC to connect to databases on both the supported DoC databases:

Throughout this document, we'll use a single Reference Problem described in more detail below, and show how to solve it in:

The Reference Problem

The dataset we have chosen to illustrate the principles of JDBC is called the Film Example. It comprises a single test table called films containing information about selected feature films and their directors. This table was created by the following SQL create statement:

create table films (
        title varchar(40),
        director varchar(40),
        origin varchar(10),
        made datetime,
        length int

After creating the above table, we then inserted specific 23 film records into it. If you're interested, here's the whole set of 24 SQL statements that we used - the above create statement and 23 inserts. You could even use this dataset to recreate the films table on your favourite RDBMS on your home machine - but beware, not all SQL implementations implement the datetime type - you might have to change that..

Having created the above dataset, our problem is extremely simple:

The Generic Java Structure of the Solution

Ok, let's show the basic Java structure of the solution to our problem:

import java.sql.*;
// Load the relevent RDBMS JDBC driver
try {
  Class.forName( "RDBMS_specific_driver_name" );
} catch (ClassNotFoundException e) {
  System.err.println( "Driver not found: " + e + "\n" + e.getMessage() );

// Use the driver to connect to the database
try {
  Connection conn = DriverManager.getConnection (
        "Database_Username", "Database_Password" );
  Statement stmt = conn.createStatement();
  // Now execute our query
  ResultSet rs = stmt.executeQuery("SELECT * FROM films");

  // foreach result record
  while ( ) {
    String title = rs.getString("title");
    String director = rs.getString("director");
    String origin = rs.getString("origin");
    String made = rs.getString("made");
    String length = rs.getString("length");

    // print the row 
    PrintRow( title, directory, origin, made, length );

} catch (Exception e) {
  System.err.println("Exception: " + e + "\n" + e.getMessage() );

The Postgres specific Details

The Microsoft SQL Server specific Details

A Note on Classpaths

Both the Postgres and Microsoft SQL Server JDBC drivers are installed on all DoC's Ubuntu Linux machines. The drivers are automatically placed on the Tomcat classpath for JSP and Servlet programming, but you may need to ensure that they are on your personal classpath for testing purposes. For Linux, we strongly recommend that you edit your ~/.cshrc file and add:

set want_classpath_share

to the top of the file, save the file and leave the editor and then type

source ~/.cshrc

into the command shell to re-execute the commands in the file without needing to log out and in again. Having done this, if you now type


into the command shell, you should now find a nice big classpath has been set, including the above support classes and some other useful classes, such as classes to parse XML. After doing this, any precompiled java program that loads postgres or MSSQL JDBC classes should run fine.

Running on Windows (Vista or XP)

On Windows Vista (and XP), we don't currently have a classpath maintenance system like /usr/share/java on Linux. However, we have verified that the plain versions below work fine on Windows XP if you copy the ms*.jar files and the postgresql.jar files from Linux's /usr/share/java into the directory where the class file lives, and run java setting the classpath explicitly. You may find the following one line runwin.bat batch script helpful (although you may need to modify the java.exe path on some versions of Windows):

"C:\Program Files\Java\bin\java.exe" -cp mssqlserver.jar;msutil.jar;postgresql.jar;. %1

Copying that batch file into the same directory as the .jar files and the .class files, you can then run (for example) Plain_MSSQL.class by saying:

runwin Plain_MSSQL

in the Windows command shell (cmd).

The Solutions

Now we will present the 6 solutions to our reference problem. The plain Java version formats the output as simple Comma-Separated Values, whereas the Servlet and the JSP versions produce a nice HTML table.


Microsoft SQL Server





Plain Java

Plain_Postgres source

Plain_Postgres output

Plain_MSSQL source

Plain_MSSQL output

Java Servlet

Servlet_Postgres source

Servlet_Postgres output

Servlet_MSSQL source

Servlet_MSSQL output



Postgres.jsp output


MSSQL.jsp output

Note that in the JSP examples, to import "java.sql.*" you need to write:

<%@ page language="java" import="java.sql.*"%>

To keep us honest, here are the automatically generated (and hence up to date) differences between the Postgres and MSSQL versions of all 3 versions:

Postgres vs Microsoft SQL Server Differences

Plain Java

differences between and

Java Servlet

differences between and


differences between JSP_Postgres.jsp and JSP_MSSQL.jsp

We hope that this worked example is useful to everyone. Please let us know if it's not clear enough..

We've just rewritten the JSP and Servlets tutorials to use our new Personal Tomcat setup, and we've included the above JSP JDBC examples in the JSP tutorial, and the Servlet JDBC examples in the Servlet tutorial. Have a go!

guides/java/jdbc (last edited 2009-06-18 16:08:29 by dcw)