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:

• The DoC installation of Postgres.
• The DoC installation of MS Windows SQL Server.

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

• A bog-standard Java application, with a main method.
• A JSP (Java Servlet Pages) Webpage, accessed through Tomcat. See our Servlet and JSP document for an introduction to JSP.
• A Java Servlet, accessed through Tomcat. Again, see our Servlet and JSP document for an introduction to Servlets.

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:

• To execute the SQL statement SELECT * FROM films
• To format each returned record as a row of a table.

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 (
        "RDBMS_specific_connect_string",
        "Database_Username", "Database_Password" );
  Statement stmt = conn.createStatement();
 
  // Now execute our query
  ResultSet rs = stmt.executeQuery("SELECT * FROM films");

  // foreach result record
  while ( rs.next() ) {
    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 );
  }

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

The Postgres specific Details

• For Postgres, the driver class is called "org.postgresql.Driver", and is contained in the jar file /usr/share/java/postgresql.jar on DoC's Ubuntu Linux systems. See the CLASSPATH note for more detail on this.
• The connect string is "jdbc:postgresql://db.doc.ic.ac.uk/DATABASE", where DATABASE is the specific database you wish to connect to. You might have your own database (named after your username, or a group project groupname), or you might be using a pre-existing database like films. For our purposes here in this FAQ, we will use the connect string "jdbc:postgresql://db.doc.ic.ac.uk/films".
• If you want readonly access to the films dataset, you can use "lab" as both the database username and the database password.

• Alternatively, when you joined DoC, we generated you a Postgres database and Postgres user account on our teaching database server (db.doc.ic.ac.uk), and automatically emailed the details to you. This could be a perfect time to use this Postgres database. If you've mislaid the postgres email, you can contact us on help@doc.ic.ac.uk to regenerate the postgres email for you.

The Microsoft SQL Server specific Details

• For SQL Server, the driver class is called "com.microsoft.jdbc.sqlserver.SQLServerDriver", and is contained in three "ms*.jar" files found in /usr/share/java on DoC's Ubuntu Linux systems. See the CLASSPATH note for more detail on this.
• The connect string is "jdbc:microsoft:sqlserver://db-ms.doc.ic.ac.uk:1433".
• Just like Postgres, if you want readonly access to the films dataset, use "lab" as both the database username and the database password.

• Alternatively, contact CSG to obtain a MS SQL Server database password, by emailing help@doc.ic.ac.uk.

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

echo $CLASSPATH

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.

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:

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!