JDBC: A Walk Through

The 1.5 API scroll down for what it includes

Within JDBC there are four particularly important classes: DriverManager, Connection, PreparedStatement, and ResultSet. Each class corresponds to a indispensable phase of database access:

The DriverManager loads and configures your database driver on your client.

The Connection class performs connection and authentication to your database server using URLs.

PreparedStatement moves SQL to the database engine for preprocessing and eventually execution.

The ResultSet class allows for the inspection of results from "select" statements.

I will be using the application SimpleSelect.java in these notes (also Employee.java for reference). Basically, I will demonstrate characteristics of using JDBC by walking through this program using tutorial materials

First, the JDBC kernel package needs to be imported

import java.net.URL;
import java.sql.*;

class SimpleSelect {

 public static void main (String args[]) {
   String url   = "jdbc:odbc:my-dsn";
   String query = "SELECT * FROM question_table"; 

   try {
       Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

       Connection con = DriverManager.getConnection (url, "my-user","my-pswd");

Using the Bridge

The Bridge is used by opening a JDBC connection using a URL with the odbc subprotocol. See below for URL examples. Before a connection can be established, the bridge driver class, sun.jdbc.odbc.JdbcOdbcDriver, must either be added to the java.lang.System property named jdbc.drivers, or it must be explicitly loaded using the Java class loader. Explicit loading is done with the following line of code:

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

When loaded, the ODBC driver (like all good JDBC drivers) creates an instance of itself and registers this with the JDBC driver manager. For more on the Class Loader, see an email

Most Browsers Do Not Support the Bridge

Since the Bridge is an optional component of the JDK, it may not be provided by a browser. Even if it is provided, only trusted applets (those allowed to write to files) or servlets will be able to use the Bridge. This is required in order to preserve the security of the applet sandbox. Finally, even if the applet is trusted, ODBC and the DBMS client library must be configured on each client.

Opening a Connection

A Connection object represents a connection with a database. A connection session includes the SQL statements that are executed and the results that are returned over that connection. A single application can have one or more connections with a single database, or it can have connections with many different databases.

The standard way to establish a connection with a database is to call the method DriverManager.getConnection. This method takes a string containing a URL. The DriverManager class, referred to as the JDBC management layer, attempts to locate a driver than can connect to the database represented by that URL. The DriverManager class maintains a list of registered Driver classes, and when the method getConnection is called, it checks with each driver in the list until it finds one that can connect to the database specified in the URL. The Driver method connect uses this URL to actually establish the connection. In addition, the DriverManager class attends to things like driver login time limits and the printing of log and tracing messages.

For simple applications, the only method in this class that a general programmer needs to use directly is DriverManager.getConnection. As its name implies, this method establishes a connection to a database. JDBC allows the user to call the DriverManager methods getDriver, getDrivers, and registerDriver as well as the Driver method connect, but in most cases it is better to let the DriverManager class manage the details of establishing a connection.

Keeping Track of Available Drivers

The DriverManager class maintains a list of Driver classes that have registered themselves by calling the method DriverManager.registerDriver. All Driver classes should be written with a static section that creates an instance of the class and then registers it with the DriverManager class when it is loaded. Thus, a user would not normally call DriverManager.registerDriver directly; it should be called automatically by a driver when it is loaded. A Driver class is loaded, and therefore automatically registered with the DriverManager, in two ways:

By calling the method Class.forName. This explicitly loads the driver class. Since it does not depend on any external setup, this way of loading a driver is recommended. The following code loads the class acme.db.Driver:
```
    Class.forName("acme.db.Driver");
```
If acme.db.Driver has been written so that loading it causes an instance to be created and also calls DriverManager.registerDriver with that instance as the parameter (as it should do), then it is in the DriverManager's list of drivers and available for creating a connection.
By adding the driver to the java.lang.System property jdbc.drivers. This is a list of driver classnames, separated by colons, that the DriverManager class loads. When the DriverManager class is intialized, it looks for the system property jdbc.drivers, and if the user has entered one or more drivers, the DriverManager class attempts to load them. The following code illustrates how a programmer might enter three driver classes in ~/.hotjava/properties (HotJava loads these into the system properties list on startup):

    jdbc.drivers=foo.bah.Driver:wombat.sql.Driver:bad.test.ourDriver;

The first call to a DriverManager method will automatically cause these driver classes to be loaded.

Note that this second way of loading drivers requires a preset environment that is persistent. If there is any doubt about that being the case, it is safer to call the method Class.forName to explicitly load each driver. This is also the method to use to bring in a particular driver since once the DriverManager class has been initialized, it will never recheck the jdbc.drivers property list.

In both of the cases listed above, it is the responsibility of the newly-loaded Driver class to register itself by calling DriverManager.registerDriver. As mentioned above, this should be done automatically when the class is loaded.

For security reasons, the JDBC management layer will keep track of which class loader provided which driver. Then when the DriverManager class is opening a connection, it will use only drivers that come from the local file system or from the same class loader as the code issuing the request for a connection.

Establishing a Connection

Once the Driver classes have been loaded and registered with the DriverManager class, they are available for establishing a connection with a database. When a request for a connection is made with a call to the DriverManager.getConnection method, the DriverManager tests each driver in turn to see if it can establish a connection.

It may sometimes be the case that more than one JDBC driver is capable of connecting to a given URL. For example, when connecting to a given remote database, it might be possible to use a JDBC-ODBC bridge driver, a JDBC-to- generic-network-protocol driver, or a driver supplied by the database vendor. In such cases, the order in which the drivers are tested is significant because the DriverManager will use the first driver it finds that can successfully connect to the given URL.

First the DriverManager tries to use each of the drivers in the order they were registered. (The drivers listed in jdbc.drivers are always registered first.) It will skip any drivers which are untrusted code, unless they have been loaded from the same source as the code that is trying to open the connection.

It tests the drivers by calling the method Driver.connect on each one in turn, passing them the URL that the user originally passed to the method DriverManager.getConnection. The first driver that recognizes the URL makes the connection.

At first glance this may seem inefficient, but it requires only a few procedure calls and string comparisons per connection since it is unlikely that dozens of drivers will be loaded concurrently.

The following code is an example of all that is normally needed to set up a connection with a driver such as a JDBC-ODBC bridge driver:

    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  //loads the driver
    String url = "jdbc:odbc:fred";
    DriverManager.getConnection(url, "userID", "passwd");

JDBC URLs

A JDBC URL provides a way of identifying a database so that the appropriate driver will recognize it and establish a connection with it. Driver writers are the ones who actually determine what the JDBC URL that identifies their particular driver will be. Users do not need to worry about how to form a JDBC URL; they simply use the URL supplied with the drivers they are using. JDBC's role is to recommend some conventions for driver writers to follow in structuring their JDBC URLs.

Since JDBC URLs are used with various kinds of drivers, the conventions are of necessity very flexible. First, they allow different drivers to use different schemes for naming databases. The odbc subprotocol, for example, lets the URL contain attribute values (but does not require them).

Second, JDBC URLs allow driver writers to encode all necessary connection information within them. This makes it possible, for example, for an applet that wants to talk to a given database to open the database connection without requiring the user to do any system administration chores.

Third, JDBC URLs allow a level of indirection. This means that the JDBC URL may refer to a logical host or database name that is dynamically translated to the actual name by a network naming system. This allows system administrators to avoid specifying particular hosts as part of the JDBC name. There are a number of different network name services (such as DNS, NIS, and DCE), and there is no restriction about which ones can be used.

The standard syntax for JDBC URLs is shown below. It has three parts, which are separated by colons:

      jdbc:<subprotocol>:<subname>

The three parts of a JDBC URL are broken down as follows:

jdbc-the protocol. The protocol in a JDBC URL is always jdbc.

<subprotocol>-the name of the driver or the name of a database connectivity mechanism, which may be supported by one or more drivers. A prominent example of a subprotocol name is "odbc", which has been reserved for URLs that specify ODBC-style data source names. For example, to access a database through a JDBC-ODBC bridge, one might use a URL such as the following:
```
      jdbc:odbc:fred
```
In this example, the subprotocol is "odbc", and the subname "fred" is a local ODBC data source.
If one wants to use a network name service (so that the database name in the JDBC URL does not have to be its actual name), the naming service can be the subprotocol. So, for example, one might have a URL like:
```
      jdbc:dcenaming:accounts-payable
```
In this example, the URL specifies that the local DCE naming service should resolve the database name "accounts-payable" into a more specific name that can be used to connect to the real database.
<subname>-a way to identify the database. The subname can vary, depending on the subprotocol, and it can have a subsubname with any internal syntax the driver writer chooses. The point of a subname is to give enough information to locate the database. In the previous example, "fred" is enough because ODBC provides the remainder of the information. A database on a remote server requires more information, however. If the database is to be accessed over the Internet, for example, the network address should be included in the JDBC URL as part of the subname and should follow the standard URL naming convention of

       //hostname:port/subsubname

Supposing that "dbnet" is a protocol for connecting to a host on the Internet, a JDBC URL might look like this:

      jdbc:dbnet://wombat:356/fred

on tiglon the URL to connect is

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ect-sun.ecst.csuchico.edu)(PORT=1521)))(CONNECT_DATA=(SID=ecstDB)(SERVER=DEDICATED)))

The "odbc" Subprotocol

The subprotocol odbc is a special case. It has been reserved for URLs that specify ODBC-style data source names and has the special feature of allowing any number of attribute values to be specified after the subname (the data source name). The full syntax for the odbc subprotocol is:

      jdbc:odbc:<data-source-name>[;<attribute-name>=<attribute-value>]*

Thus all of the following are valid jdbc:odbc names:

      jdbc:odbc:qeor7
      jdbc:odbc:wombat
      jdbc:odbc:wombat;CacheSize=20;ExtensionCase=LOWER
      jdbc:odbc:qeora;UID=kgh;PWD=fooey

Back to SimpleSelect!

notice that almost the whole main method is in a try catch clause

         // If we were unable to connect, an exception
         // would have been thrown.  So, if we get here,
         // we are successfully connected to the URL

         // Check for, and display and warnings generated
         // by the connect.

       checkForWarning (con.getWarnings ());

checkForWarning method code is much futher below in case you are wondering what this method call is doing here

         // Get the DatabaseMetaData object and display
         // some information about the connection

       DatabaseMetaData dma = con.getMetaData ();

       System.out.println("\nConnected to " + dma.getURL());
       System.out.println("Driver       " + dma.getDriverName());
       System.out.println("Version      " + dma.getDriverVersion());
       System.out.println("");

Information about a database

When you need to know about the capabilities, or the vendor, of a database, ask the associated Connection object for its metadata.

There are many questions you can ask. For example, the following code fragment asks the database for its product name and how many simultaneous connections can be made to it.

if (dma==null) {
    System.out.println("No Database Meta Data");
} else {
    System.out.println("Database Product Name   : " + dma.getDatabaseProductName());
    System.out.println("Allowable active connections: "+ dma.getMaxConnections());
}

See DatabaseMetaData API for more info.

       // Create a Statement object so we can submit
       // SQL statements to the driver

       Statement stmt = con.createStatement ();

       // Submit a query, creating a ResultSet object

       ResultSet rs = stmt.executeQuery (query);

Remember, query from the instance variables:
String query = "SELECT * FROM question_table";

A Statement object is used to send SQL statements to a database. There are actually three kinds of Statement objects, all of which act as containers for executing SQL statements on a given connection: Statement, PreparedStatement, which inherits from Statement, and CallableStatement, which inherits from PreparedStatement. They are specialized for sending particular types of SQL statements:

a Statement object is used to execute a simple SQL statement with no parameters;
a PreparedStatement object is used to execute a precompiled SQL statement with or without IN parameters; and
a CallableStatement object is used to execute a call to a database stored procedure.

See Getting Started : Statement , Prepared Statements , and Callable Statements

The Statement interface provides basic methods for executing statements and retrieving results. The PreparedStatement interface adds methods for dealing with IN parameters; CallableStatement adds methods for dealing with OUT parameters.

Creating Statement Objects

Once a connection to a particular database is established, that connection can be used to send SQL statements. A Statement object is created with the Connection method createStatement, as in the following code fragment:

   Connection con = DriverManager.getConnection(url, "sunny", "");
   Statement stmt = con.createStatement();

The SQL statement that will be sent to the database is supplied as the argument to one of the methods for executing a Statement object:

    ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table2");

Executing Statements Using Statement objects

The Statement interface provides three different methods for executing SQL statements, executeQuery, executeUpdate, and execute. The one to use is determined by what the SQL statement produces.

The method executeQuery is designed for statements that produce a single result set, such as SELECT statements.

The method executeUpdate is used to execute INSERT, UPDATE, or DELETE statements and also SQL DDL (Data Definition Language) statements like CREATE TABLE and DROP TABLE. The effect of an INSERT, UPDATE, or DELETE statement is a modification of one or more columns in zero or more rows in a table. The return value of executeUpdate is an integer indicating the number of rows that were affected (referred to as the update count). For statements such as CREATE TABLE or DROP TABLE, which do not operate on rows, the return value of executeUpdate is always zero.

The method execute is used to execute statements that return more than one result set, more than one update count, or a combination of the two. Because it is an advanced feature that most programmers will never need, it is explained in its own section later in this overview.

All of the methods for executing statements close the calling Statement object's current result set if there is one open. This means that one needs to complete any processing of the current ResultSet object before re-executing a Statement object.

It should be noted that the PreparedStatement interface, which inherits all of the methods in the Statement interface, has its own versions of the methods executeQuery, executeUpdate and execute. Statement objects do not themselves contain an SQL statement; therefore, one must be provided as the argument to the Statement.execute methods. PreparedStatement objects do not supply an SQL statement as a parameter to these methods because they already contain a precompiled SQL statement. CallableStatement objects inherit the PreparedStatement forms of these methods. Using a query parameter with the PreparedStatement or CallableStatement versions of these methods will cause an SQLException to be thrown.

For further details, see the tutorial, the other tutorial and Getting Started

Back to the code, in the following line, note that the dispResultSet method code is futher below

       // Display all columns and rows from the result set
       dispResultSet (rs);

       // Close the result set

       rs.close();

       // Close the statement

       stmt.close();

Statement objects will be closed automatically by the Java garbage collector. Nevertheless, it is recommended as good programming practice that they be closed explicitly when they are no longer needed. This frees DBMS resources immediately and helps avoid potential memory problems.


   catch (SQLException ex) {

   // A SQLException was generated.  Catch it and
   // display the error information.  Note that there
   // could be multiple error objects chained together

   System.out.println ("\n*** SQLException caught ***\n");

   while (ex != null) {
      System.out.println ("SQLState: " +
            ex.getSQLState ());
      System.out.println ("Message:  " + ex.getMessage ());
      System.out.println ("Vendor:   " +
            ex.getErrorCode ());
       ex = ex.getNextException ();
       System.out.println ("");
   }
  }
   catch (java.lang.Exception ex) {

      // Got some other type of exception.  Dump it.

      ex.printStackTrace ();
   }
 }

 //-------------------------------------------------------------------
 // checkForWarning
 // Checks for and displays warnings.  Returns true if a warning
 // existed
 //-------------------------------------------------------------------

 private static boolean checkForWarning (SQLWarning warn) throws SQLException{
   boolean rc = false;

   // If a SQLWarning object was given, display the
   // warning messages.  Note that there could be
   // multiple warnings chained together

   if (warn != null) {
      System.out.println ("\n *** Warning ***\n");
      rc = true;
      while (warn != null) {
         System.out.println ("SQLState: " +
            warn.getSQLState ());
         System.out.println ("Message:  " +
            warn.getMessage ());
         System.out.println ("Vendor:   " +
            warn.getErrorCode ());
         System.out.println ("");
            warn = warn.getNextWarning ();
      }
   }
   return rc;
 }

The following method dispResultSet (ResultSet rs) makes heavy use of the ResultSetMetaData class. Some examples:

In SQL, the cursor for a result table is named. If a database allows positioned updates or positioned deletes, the name of the cursor needs to be supplied as a parameter to the update or delete command. This cursor name can be obtained by calling the method getCursorName.

Note that not all DBMSs support positioned update and delete. The DatabaseMetaData.supportsPositionedDelete and supportsPositionedUpdate methods can be used to discover whether a particular connection supports these operations. When they are supported, the DBMS/driver must ensure that rows selected are properly locked so that positioned updates do not result in update anomalies or other concurrency problems.

Information about the columns in a ResultSet is available by calling the method ResultSet.getMetaData. The ResultSetMetaData object returned gives the number, types, and properties of its ResultSet object's columns.

For example, you can query the ResultSetMetaData easily to determine how many columns there are:

int columns = meta.getColumnCount();

and then walk the list of columns printing out their name and type:

int numbers = 0;
for (int i=1;i<=columns;i++) {
    System.out.println (meta.getColumnLabel(i) + "\t"
                  + meta.getColumnTypeName(i));
    if (meta.isSigned(i)) { // is it a signed number?
        numbers++;
    }
}
System.out.println ("Columns: " + columns + " Numeric: " + numbers);

If the name of a column is known, but not its index, the method findColumn can be used to find the column number.

 //-------------------------------------------------------------------
 // dispResultSet Displays all columns and rows in the given result set
 //-------------------------------------------------------------------

 private static void dispResultSet (ResultSet rs)
   throws SQLException
 {
   int i;

   // Get the ResultSetMetaData.  This will be used for the column headings

   ResultSetMetaData rsmd = rs.getMetaData ();

   // Get the number of columns in the result set

   int numCols = rsmd.getColumnCount ();

   // Display column headings

   for (i=1; i<=numCols; i++) {
      if (i > 1) System.out.print(",");
      System.out.print(rsmd.getColumnLabel(i));
   }
   System.out.println("");
             
   // Display data, fetching until end of the result set

   boolean more = rs.next ();
   while (more) {

      // Loop through each column, getting the column data and displaying

      for (i=1; i<=numCols; i++) {
         //if (i > 1) System.out.print(",");
          System.out.print(rs.getString(i)+"\n");
      }
      //System.out.println("");

      // Fetch the next result set row

      more = rs.next ();
   }
 }
}