A Quick JDBC How-To

Posted by Paul on 9th July 2009

In order to access a persistence store using JDBC, it’s necessary to load the JDBC driver using the Class class’s static method forName(). Once the JDBC driver has been loaded, it should be possible to make a connection to a database managed by the DBMS. Here’s how the postgresql JDBC driver is loaded (don’t forget to make sure the JDBC driver jar file is either on the class path, or referenced directly), and then used to get a database connection:

// Load the driver class
 Class.forName("org.postgresql.Driver");

// Obtain a connection to the DBMS
java.sql.Connection connection =
    java.sql.DriverManager.getConnection(
        "jdbc:postgresql://localhost/dbname",
        "username",
        "password");

Once you have a connection you can start manipulating the database. Use the java.sql.Statement class to do this with the java.sql.ResultSet to manage the results of executing a statement.

There are two other types of statement available, namely CallableStatement and PreparedStatement, which offer stored procedure statements and pre-parsed SQL statements, respectively. These have the advantage of reducing the overhead of parsing executed SQL at run-time. The PreparedStatement is likely the more useful due to its simplicity and improved efficiency. Here’s an example of its use:

try {
    PreparedStatement ps = null;

    ps = c.prepareStatement("INSERT INTO authors VALUES (?, ?, ?)");
    ps.setInt(1, 495);
    ps.setString(2, "Light-Williams");
    ps.setString(3, "Corwin");

    ps.executeUpdate();
} catch (SQLException se) {
    System.out.println(
        "We got an exception while preparing a statement:" +
        "Probably bad SQL.");
    se.printStackTrace();
    System.exit(1);
}

After executing an SQL statement, obtain the results set data, including the names and types of columns that have been updated by a statement, using an instance of ResultsSetMetaData obtained from the ResultsSet instance.

Similarly the DatabaseMetaData can be used to obtain information such as the catalogues available from the connected database, the producer of the database and the user who is used for the connection. Get an instance of the DatabaseMetaData via the Connection object instance through the getMetaData() method.

It’s worth noting a few points about the results set returned by executing an SQL statement. Firstly the returned ResultsSet object starts off pointing to the the position prior to the first record. This means that the next() method must be called on the ResultsSet object in order to get the first record. Also, there is no way of finding out the number of records held by a returned ResultsSet instance except by stepping through it and counting the number of records. Finally, in multi-threaded applications ensure that each thread uses its own ResultsSet objects.

Tags: database, Java