Recipe 20.4 JDBC Setup and Connection

Problem

You want to access a database via JDBC.

Solution

Use Class.forName( ) and DriverManager.getConnection( ).

Discussion

While DB and friends have their place, most of the modern database action is in relational databases, and accordingly Java database action is in JDBC. So the bulk of this chapter is devoted to JDBC.

This is not the place for a tutorial on relational databases. I'll assume that you know a little bit about SQL, the universal language used to control relational databases. SQL has queries like "SELECT * from userdb", which means to select all columns (the *) from all rows (entries) in a database table named userdb (all rows are selected because the SELECT statement has no "where" clause). SQL also has updates like INSERT, DELETE, CREATE, and DROP. If you need more information on SQL or relational databases, many good books can introduce you to the topic in more detail.

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. This chapter concentrates on common features, primarily Level 1.

Ian's Basic Steps: Using a JDBC Query

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 (see Chapter 25) 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?

But wait, there's more! 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.

For the curious, the static code block in a Driver called BarFileDriver looks something like this:

/** Static code block, to initialize with the DriverManager. */ static {     try {          DriverManager.registerDriver(new BarFileDriver( ));     } catch (SQLException e) {          DriverManager.println("Can't load driver" +                                 "darwinsys.sql.BarFileDriver");     } }

Example 20-6 shows a bit of code that tries to load two drivers. The first is the JDBC-to-ODBC bridge described in the Introduction. The second is one of the commercial drivers from Oracle.

Example 20-6. 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);             // Try to load an Oracle driver.             Class d = Class.forName("oracle.jdbc.driver.OracleDriver");             System.out.println("Loaded " + d);         } catch (ClassNotFoundException ex) {             System.err.println(ex);         }     } }

The first load succeeds; the second fails since I don't have the Oracle driver installed on my notebook:

daroad.darwinsys.com$ java LoadDriver Loaded class sun.jdbc.odbc.JdbcOdbcDriver java.lang.ClassNotFoundException: oracle/jdbc/driver/OracleDriver daroad.darwinsys.com$

It is also possible to preregister a driver using the -D option to load it into the System Properties; in this case, you can skip the Class.forName( ) step:

java -Djdbc.drivers=com.acmewidgets.AcmeDriver:foo.bar.OhMyDriver MyClass

Once you have registered the driver, you are ready to connect to the database.