Java Tips - JDBC Setup and Connection

JDBC has two Levels, JDBC 1 and JDBC 2. Level 1 is included in all JDBC implementation and drivers; Level 2 is optional and requires a Level 2 driver.

To create a JDBC query:

1. Load the appropriate Driver class, which has the side effect of registering with the DriverManager.
2. Get a Connection object, using DriverManager.getConnection( ) :

   Connection con = DriverManager.getConnection (dbURL, name, pass);

3. Get a Statement object, using the Connection object's createStatement( ) :

   Statement stmt = con.createStatement( );

4. Get a ResultSet object, using the Statement object's executeQuery( ) :

   ResultSet rs = stmt.executeQuery("select * from MyTable");

5. Iterate over the ResultSet:

   while (rs.next( )) {
   
       int x = rs.getInt("CustNO");

6. Close the ResultSet.
7. Close the Statement.
8. Close the Connection.
    

The first step in using JDBC 1 is to load your database's driver. This is performed using some Java JVM magic. The class java.lang.Class has a method called forName( ) that takes a string containing the full Java name for a class and loads the class, returning a Class object describing it. This is part of the introspection or reflection API  but can be used anytime to ensure that a class has been correctly configured into your CLASSPATH. This is the use that we'll see here. And, in fact, part of the challenge of installing JDBC drivers is ensuring that they are in your CLASSPATH at deployment time. The advantage of my slightly convoluted approach is that the drivers do not have to be on your CLASSPATH at compile time. In some cases, this can allow customers to use your software with database drivers that didn't even exist when your software was written; how's that for flexibility?

In addition to checking your CLASSPATH, this method also registers the driver with another class called the DriverManager . How does it work? Each valid JDBC driver has a bit of method-like code called a static initializer . This is used whenever the class is loaded—just what the doctor ordered! So the static block registers the class with the DriverManager when you call Class.forName( ) on the driver class.

Example: LoadDriver.java

import java.awt.*;

import java.sql.*;



/** Load some drivers.  */

public class LoadDriver {

    public static void main(String[] av) {

        try {

            // Try to load the jdbc-odbc bridge driver

            // Should be present on Sun JDK implementations.

            Class c = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

            System.out.println("Loaded " + c);
           
        } catch (ClassNotFoundException ex) {

            System.err.println(ex);

        }

    }

}