Java Enterprise in a Nutshell, 2nd Edition By Jim Farley, William Crawford, David Flanagan Second Edition April 2002 0-596-00152-5, Order Number: 1525 992 pages, $39.95 US $61.95 CA

Chapter 2
JDBC

The JDBC[1] API provides Java applications with mid-level access to most database systems, via the Structured Query Language (SQL). JDBC is a key enterprise API, as it's hard to imagine an enterprise application that doesn't use a database in some way.

In the first edition of this book, we focused on the original JDBC 1.0 API, and touched briefly on the new features provided by the JDBC 2.0 API. JDBC 2.1 is now a standard component of the J2SE platform, and drivers supporting the upgraded specification are widely available. In this edition, we discuss the JDBC 2.1 API and the JDBC 2.0 Optional Packages (previously known as the JDBC 2.0 Standard Extension) and take a look at the upcoming JDBC 3.0 API.

A word of caution: while the java.sql package is not tremendously complex, it does require grounding in general database concepts and the SQL language itself. This book includes a brief SQL reference (see Chapter 12), but if you have never worked with a relational database system before, this chapter is not the place to start. For a more complete treatment of JDBC and general database concepts, we recommend Database Programming with JDBC and Java by George Reese (O'Reilly).

JDBC Architecture

Different database systems have surprisingly little in common: just a similar purpose and a mostly compatible query language. Beyond that, every database has its own API that you must learn to write programs that interact with the database. This has meant that writing code capable of interfacing with databases from more than one vendor has been a daunting challenge. Cross-database APIs exist, most notably Microsoft's ODBC API, but these tend to find themselves, at best, limited to a particular platform.

JDBC is Sun's attempt to create a platform-neutral interface between databases and Java. With JDBC, you can count on a standard set of database access features and (usually) a particular subset of SQL, SQL-92. The JDBC API defines a set of interfaces that encapsulate major database functionality, including running queries, processing results, and determining configuration information. A database vendor or third-party developer writes a JDBC driver, which is a set of classes that implements these interfaces for a particular database system. An application can use a number of drivers interchangeably. Figure 2-1 shows how an application uses JDBC to interact with one or more databases without knowing about the underlying driver implementations.

Figure 2-1. JDBC-database interaction

JDBC Basics

Before we discuss all of the individual components of JDBC, let's look at a simple example that incorporates most of the major pieces of JDBC functionality. Example 2-1 loads a driver, connects to the database, executes some SQL, and retrieves the results. It also keeps an eye out for any database-related errors.

Example 2-1: A Simple JDBC Example

import java.sql.*;
 
public class JDBCSample {
 
 public static void main(java.lang.String[] args) {
   try {
     // This is where we load the driver
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   } 
   catch (ClassNotFoundException e) {
     System.out.println("Unable to load Driver Class");
     return;
   }
  
   try {
     // All database access is within a try/catch block. Connect to database,
     // specifying particular database, username, and password
     Connection con = DriverManager.getConnection("jdbc:odbc:companydb",
              "", "");
  
     // Create and execute an SQL Statement
     Statement stmt = con.createStatement(  );
     ResultSet rs = stmt.executeQuery("SELECT FIRST_NAME FROM EMPLOYEES");
 
     // Display the SQL Results
     while(rs.next(  )) {
       System.out.println(rs.getString("FIRST_NAME"));
     }
 
     // Make sure our database resources are released
     rs.close(  );
     stmt.close(  );
     con.close(  );
 
     } 
     catch (SQLException se) {
       // Inform user of any SQL errors
       System.out.println("SQL Exception: " + se.getMessage(  ));
       se.printStackTrace(System.out);
      } 
    } 
}

Example 2-1 starts out by loading a JDBC driver class (in this case, Sun's JDBC-ODBC Bridge). Then it creates a database connection, represented by a Connection object, using that driver. With the database connection, we can create a Statement object to represent an SQL statement. Executing an SQL statement produces a ResultSet that contains the results of a query. The program displays the results and then cleans up the resources it has used. If an error occurs, a SQLException is thrown, so our program traps that exception and displays some of the information it encapsulates.

Clearly, there is a lot going on in this simple program. Every Java application that uses JDBC follows these basic steps, so the following sections discuss each step in much more detail.

JDBC Drivers

Before you can use a driver, it must be registered with the JDBC DriverManager. This is typically done by loading the driver class using the Class.forName( ) method:

try {
 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 Class.forName("com.oracle.jdbc.OracleDriver");
} 
catch (ClassNotFoundException e) { 
 /* Handle Exception */ 
}

One reason most programs call Class.forName( ) is that this method accepts a String argument, meaning that the program can store driver selection information dynamically (e.g., in a properties file).

Another way to register drivers is to add the driver classes to the jdbc.drivers property. To use this technique, add a line like the following to ~/.hotjava/properties (on Windows systems this file can be found in your Java SDK installation directory):

jdbc.drivers=com.oracle.jdbc.OracleDriver:foo.driver.dbDriver:
     com.al.AlDriver;

Separate the names of individual drivers with colons and be sure the line ends with a semicolon. (Programs rarely use this approach, as it requires additional configuration work on the part of end users.) Every user needs to have the appropriate JDBC driver classes specified in his properties file.

Finally, drivers can be loaded by a J2EE server and provided to the application via JNDI. We'll see more about that the end of this chapter.

JDBC drivers are available for most database platforms, from a number of vendors and in a number of different flavors. There are four categories of drivers:

Type 1 JDBC-ODBC bridge drivers

Type 1 drivers use a bridge technology to connect a Java client to an ODBC database system. The JDBC-ODBC Bridge from Sun and InterSolv is the only existing example of a Type 1 driver. Type 1 drivers require some sort of non-Java software to be installed on the machine running your code, and they are implemented using native code.

Type 2 Native-API partly Java drivers

Type 2 drivers use a native code library to access a database, wrapping a thin layer of Java around the native library. For example, with Oracle databases, the native access might be through the Oracle Call Interface (OCI) libraries that were originally designed for C/C++ programmers. Type 2 drivers are implemented with native code, so they may perform better than all-Java drivers, but they also add an element of risk, as a defect in the native code can crash the Java Virtual Machine.

Type 3 Net-protocol All-Java drivers

Type 3 drivers define a generic network protocol that interfaces with a piece of custom middleware. The middleware component might use any other type of driver to provide the actual database access. BEA's WebLogic product line (formerly known as WebLogic Tengah and before that as jdbcKona/T3) is an example. These drivers are especially useful for applet deployment, since the actual JDBC classes can be written entirely in Java and downloaded by the client on the fly.

Type 4 Native-protocol All-Java drivers

Type 4 drivers are written entirely in Java. They understand database-specific networking protocols and can access the database directly without any additional software. These drivers are also well suited for applet programming, provided that the Java security manager allows TCP/IP connections to the database server.

When you are selecting a driver, you need to balance speed, reliability, and portability. Different applications have different needs. A standalone, GUI-intensive program that always runs on a Windows NT system will benefit from the additional speed of a Type 2, native-code driver. An applet might need to use a Type 3 driver to get around a firewall. A servlet that is deployed across multiple platforms might require the flexibility of a Type 4 driver.

A list of currently available JDBC drivers is available at http://java.sun.com/products/jdbc/jdbc.drivers.html.

JDBC URLs

A JDBC driver uses a JDBC URL to identify and connect to a particular database. These URLs are generally of the form:

jdbc:driver:databasename

The actual standard is quite fluid, however, as different databases require different information to connect successfully. For example, the Oracle JDBC-Thin driver uses a URL of the form:

jdbc:oracle:thin:@site:port:database

while the JDBC-ODBC Bridge uses:

jdbc:odbc:datasource;odbcoptions

The only requirement is that a driver be able to recognize its own URLs.

The JDBC-ODBC Bridge

The JDBC-ODBC Bridge ships with JDK 1.1 and the Java 2 SDK for Windows and Solaris systems. The bridge provides an interface between JDBC and database drivers written using Microsoft's Open DataBase Connectivity (ODBC) API. The bridge was originally written to allow the developer community to get up and running quickly with JDBC. Since the bridge makes extensive use of native method calls, it is not recommended for long-term or high-volume deployment.

The bridge is not a required component of the Java SDK, so it is not supported by most web browsers or other runtime environments. Using the bridge in an applet requires a browser with a JVM that supports the JDBC-ODBC Bridge, as well as a properly configured ODBC driver and data source on the client side. Finally, due to different implementations of the native methods interface, the bridge doesn't work with some development environments, most notably Microsoft Visual J++.

The JDBC URL subprotocol odbc has been reserved for the bridge. Like most JDBC URLs, it allows programs to encode extra information about the connection. ODBC URLs are of the form:

jdbc:odbc:datasourcename[;attribute-name=attribute-value]*

For instance, a JDBC URL pointing to an ODBC data source named companydb with the CacheSize attribute set to 10 looks like this:

jdbc:odbc:companydb;CacheSize=10

Connecting to the Database

The java.sql.Connection object, which encapsulates a single connection to a particular database, forms the basis of all JDBC data-handling code. An application can maintain multiple connections, up to the limits imposed by the database system itself. A standard small office or web server Oracle installation can support 50 or so connections, while a major corporate database could host several thousand. The DriverManager.getConnection( ) method creates a connection:

Connection con = DriverManager.getConnection("url", "user", "password");

You pass three arguments to getConnection( ): a JDBC URL, a database username, and a password. For databases that don't require explicit logins, the user and password strings should be left blank. When the method is called, the DriverManager queries each registered driver, asking if it understands the URL. If a driver recognizes the URL, it returns a Connection object. Because the getConnection( ) method checks each driver in turn, you should avoid loading more drivers than are necessary for your application.

The getConnection( ) method has two other variants that are less frequently used. One variant takes a single String argument and tries to create a connection to that JDBC URL without a username or password, or with a username and password embedded in the URL itself. The other version takes a JDBC URL and a java.util.Properties object that contains a set of name/value pairs. You generally need to provide at least username=value and password=value pairs.

When a Connection has outlived its usefulness, you should be sure to explicitly close it by calling its close( ) method. This frees up any memory being used by the object, and, more importantly, it releases any other database resources the connection may be holding on to. These resources (cursors, handles, and so on) can be much more valuable than a few bytes of memory, as they are often quite limited. This is particularly important in applications such as servlets that might need to create and destroy thousands of JDBC connections between restarts. Because of the way some JDBC drivers are designed, it is not safe to rely on Java's garbage collection to remove unneeded JDBC connections.

The JDBC 2.0 standard extension, discussed later in this chapter, provides a facility for connection pooling, whereby an application can maintain several open database connections and spread the load among them. This is often necessary for enterprise-level applications, such as servlets, that may be called upon to perform tens of thousands of database transactions a day.

Statements

Once you have created a Connection, you can begin using it to execute SQL statements. This is usually done via Statement objects. There are actually three kinds of statements in JDBC:

Statement

Represents a basic SQL statement

PreparedStatement

Represents a precompiled SQL statement, which can offer improved performance

CallableStatement

Allows JDBC programs complete access to stored procedures within the database itself

We're just going to discuss the Statement object for now; PreparedStatement and CallableStatement are covered in detail later in this chapter.

To get a Statement object, call the createStatement( ) method of a Connection:

Statement stmt = con.createStatement(  );

Once you have created a Statement, use it to execute SQL statements. A statement can either be a query that returns results or an operation that manipulates the database in some way. If you are performing a query, use the executeQuery( ) method of the Statement object:

ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMERS");

Here we've used executeQuery( ) to run a SELECT statement. This call returns a ResultSet object that contains the results of the query (we'll take a closer look at ResultSet in the next section).

Statement also provides an executeUpdate( ) method, for running SQL statements that don't return results, such as the UPDATE and DELETE statements. executeUpdate( ) returns an integer that indicates the number of rows in the database that were altered.

If you don't know whether a SQL statement is going to return results (such as when the user is entering the statement in a form field), you can use the execute( ) method of Statement. This method returns true if there is a result associated with the statement. In this case, the ResultSet can be retrieved using the getResultSet( ) method and the number of updated rows can be retrieved using getUpdateCount( ):

Statement unknownSQL = con.createStatement(  );
if(unknownSQL.execute(sqlString)) {
 ResultSet rs = unknownSQL.getResultSet(  );
 // display the results
} 
else {
 System.out.println("Rows updated: " + unknownSQL.getUpdateCount(  ));
}

It is important to remember that a Statement object represents a single SQL statement. A call to executeQuery( ), executeUpdate( ), or execute( ) implicitly closes any active ResultSet associated with the Statement. In other words, you need to be sure you are done with the results from a query before you execute another query with the same Statement object. If your application needs to execute more than one simultaneous query, you need to use multiple Statement objects. As a general rule, calling the close( ) method of any JDBC object also closes any dependent objects, such as a Statement generated by a Connection or a ResultSet generated by a Statement, but well-written JDBC code closes everything explicitly.

Multiple Result Sets

It is possible to write a SQL statement that returns more than one ResultSet or update count (exact methods of doing so vary depending on the database). The Statement object supports this functionality via the getMoreResults( ) method. Calling this method implicitly closes any existing ResultSet and moves to the next set of results for the statement. getMoreResults( ) returns true if there is another ResultSet available to be retrieved by getResultSet( ). However, the method returns false if the next statement is an update, even if there is another set of results waiting farther down the line. To be sure you've processed all the results for a Statement, you need to check that getMoreResults( ) returns false and that getUpdateCount( ) returns -1.

We can modify the previous execute( ) example to handle multiple results:

Statement unknownSQL = con.createStatement(  );
unknownSQL.execute(sqlString);
while (true) { 
 rs = unknownSQL.getResultSet(  );
 if(rs != null)
 // display the results
 else
 // process the update data
  
 // Advance and quit if done
 if((unknownSQL.getMoreResults(  ) == false) && 
 (unknownSQL.getUpdateCount(  ) == -1))
 break; 
}

Statements that return multiple results are actually quite rare. They generally arise from stored procedures or SQL implementations that allow multiple statements to be executed in a batch. Under SyBase, for instance, multiple SELECT statements may be separated by newline (\n) characters.

Results

When an SQL query executes, the results form a pseudo-table that contains all rows that fit the query criteria. For instance, here's a textual representation of the results of the query string "SELECT NAME, CUSTOMER_ID, PHONE FROM CUSTOMERS":

NAME                             CUSTOMER_ID  PHONE
-------------------------------- ----------- -------------------
Jane Markham                      1           617 555-1212
Louis Smith                       2           617 555-1213
Woodrow Lang                      3           508 555-7171
Dr. John Smith                    4           (011) 42 323-1239

This kind of textual representation is not very useful for Java programs. Instead, JDBC uses the java.sql.ResultSet interface to encapsulate the query results as Java primitive types and objects. You can think of a ResultSet as an object that represents an underlying table of query results, where you use method calls to navigate between rows and retrieve particular column values.

A Java program might handle the previous query as follows:

Statement stmt = con.createStatement(  );
ResultSet rs = stmt.executeQuery(
 "SELECT NAME, CUSTOMER_ID, PHONE FROM CUSTOMERS");
 
while(rs.next(  )) {
 System.out.print("Customer #" + rs.getString("CUSTOMER_ID"));
 System.out.print(", " + rs.getString("NAME"));
 System.out.println(", is at " + rs.getString("PHONE");
}
rs.close(  );
stmt.close(  );

Here's the resulting output:

Customer #1, Jane Markham, is at 617 555-1212
Customer #2, Louis Smith, is at 617 555-1213
Customer #3, Woodrow Lang, is at 508 555-7171
Customer #4, Dr. John Smith, is at (011) 42 323-1239

The code loops through each row of the ResultSet using the next( ) method. When you start working with a ResultSet, you are positioned before the first row of results. That means you have to call next( ) once just to access the first row. Each time you call next( ), you move to the next row. If there are no more rows to read, next( ) returns false. Note that with the JDBC 1.0 ResultSet, you can only move forward through the results and, since there is no way to go back to the beginning, you can read them only once. The JDBC 2.0 ResultSet, which we discuss later, overcomes these limitations.

Individual column values are read using the getString( ) method. getString( ) is one of a family of getXXX( ) methods, each of which returns data of a particular type. There are two versions of each getXXX( ) method: one that takes the case-insensitive String name of the column to be read (e.g., "PHONE", "CUSTOMER_ID") and one that takes a SQL-style column index. Note that column indexes run from 1 to n, unlike Java array indexes, which run from 0 to n-1, where n is the number of columns.

The most important getXXX( ) method is getObject( ), which can return any kind of data packaged in an object wrapper. For example, calling getObject( ) on an integer field returns an Integer object, while calling it on a date field yields a java.sql.Date object. Table 2-1 lists the different getXXX( ) methods, along with the corresponding SQL data type and Java data type. Where the return type for a getXXX( ) method is different from the Java type, the return type is shown in parentheses. Note that thejava.sql.Types class defines integer constants that represent the standard SQL data types.

Table 2-1: SQL Data Types, Java Types, and Default getXXX( ) Methods

SQL Data Type	Java Type	getXXX( ) Method
CHAR	String	getString( )
VARCHAR	String	getString( )
LONGVARCHAR	String	getString( )
NUMERIC	java.math.BigDecimal	getBigDecimal( )
DECIMAL	java.math.BigDecimal	getBigDecimal( )
BIT	Boolean (boolean)	getBoolean( )
TINYINT	Integer (byte)	getByte( )
SMALLINT	Integer (short)	getShort( )
INTEGER	Integer (int)	getInt( )
BIGINT	Long (long)	getLong( )
REAL	Float (float)	getFloat( )
FLOAT	Double (double)	getDouble( )
DOUBLE	Double (double)	getDouble( )
BINARY	byte[]	getBytes( )
VARBINARY	byte[]	getBytes( )
LONGVARBINARY	byte[]	getBytes( )
DATE	java.sql.Date	getDate( )
TIME	java.sql.Time	getTime( )
TIMESTAMP	java.sql.Timestamp	getTimestamp( )
BLOB	java.sql.Blob	getBlob( )
CLOB	java.sql.Clob	getClob( )

Note that this table merely lists the default mappings according to the JDBC specification, and some drivers don't follow these mappings exactly. Also, a certain amount of casting is permitted. For instance, the getString( ) method returns a String representation of just about any data type.

Handling Nulls

Sometimes database columns contain null, or empty, values. However, because of the way certain database APIs are written, it is impossible for JDBC to provide a method to determine before the fact whether or not a column is null.[2] Methods that don't return an object of some sort are especially vulnerable. getInt( ), for instance, resorts to returning a value of -1. JDBC deals with this problem via the wasNull( ) method, which indicates whether or not the last column read was null:

int numberInStock = rs.getInt("STOCK");
if(rs.wasNull(  ))
 System.out.println("Result was null");
else
 System.out.println("In Stock: " + numberInStock);

Alternately, you can call getObject( ) and test to see if the result is null:[3]

Object numberInStock = rs.getObject("STOCK");
if(numberInStock == null)
 System.out.println("Result was null");

Large Data Types

You can retrieve large chunks of data from a ResultSet as a stream. This can be useful when reading images from a database or loading large documents from a data store, for example. The relevant ResultSet methods are getAsciiStream( ), getBinaryStream( ), and getUnicodeStream( ), where each method has column name and column index variants, just like the other getXXX( ) methods. Each of these methods returns an InputStream. Here's a code sample that retrieves an image from a PICTURES table and writes the image to an OutputStream of some kind (this might be a ServletOutputStream for a Java servlet that produces a GIF from a database):

ResultSet rs =
 stmt.executeQuery("SELECT IMAGE FROM PICTURES WHERE PID = " +
 req.getParameter("PID"));
 
if (rs.next(  )) {
 BufferedInputStream gifData =
 new BufferedInputStream(rs.getBinaryStream("IMAGE"));
 byte[] buf = new byte[4 * 1024]; // 4K buffer
 int len;
 while ((len = gifData.read(buf, 0, buf.length)) != -1) {
 out.write(buf, 0, len);
 }
}

The JDBC 2.0 API includes Blob and Clob objects to handle large data types; we discuss these objects later in this chapter.

Dates and Times

JDBC defines three classes devoted to storing date and time information: java.sql.Date, java.sql.Time, and java.sql.Timestamp. These correspond to the SQL DATE, TIME, and TIMESTAMP types. The java.util.Date class is not suitable for any of them, so JDBC defines a new set of wrapper classes that extend (or limit) the standard Date class to fit the JDBC mold.

The SQL DATE type contains only a date, so the java.sql.Date class contains only a day, month, and year. SQL TIME (java.sql.Time) includes only a time of day, without date information. SQL TIMESTAMP (java.sql.Timestamp) includes both, but at nanosecond precision (the standard Date class is incapable of handling more than milliseconds).

Since different DBMS packages have different methods of encoding date and time information, JDBC supports the ISO date escape sequences, and individual drivers must translate these sequences into whatever form the underlying DBMS requires. The syntax for dates, times, and timestamps is:

{d 'yyyy-mm-dd'}
{t 'hh:mm:ss'}
{ts 'yyyy-mm-dd hh:mm:ss.ms.microseconds.ns'}

A TIMESTAMP needs only to be specified up to seconds; the remaining values are optional. Here is an example that uses a date escape sequence (where dateSQL is a Statement of some sort):

dateSQL.execute("INSERT INTO FRIENDS(BIRTHDAY) VALUES ({d '1978-12-14'})");

Advanced Results Handling

With JDBC 1.0, the functionality provided by the ResultSet interface is rather limited. There is no support for updates of any kind, and access to rows is limited to a single, sequential read (i.e., first row, second row, third row, etc., and no going back). JDBC 2.0 supports scrollable and updateable result sets, which allows for advanced record navigation and in-place data manipulation.

With scrolling, you can move forward and backward through the results of a query, rather than just using the next( ) method to move to the next row. In terms of scrolling, there are now three distinct types of ResultSet objects: forward-only (as in JDBC 1.0), scroll-insensitive, and scroll-sensitive. A scroll-insensitive result set generally doesn't reflect changes to the underlying data, while scroll-sensitive ones do. In fact, the number of rows in a sensitive result set doesn't even need to be fixed.

As of JDBC 2.0, result sets are also updateable. From this perspective, there are two different kinds of result sets: read-only result sets that don't allow changes to the underlying data and updateable result sets that allow such changes, subject to transaction limitations and so on.

To create an updateable, scroll-sensitive result set, we pass two extra arguments to the createStatement( ) method.

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
 ResultSet.CONCUR_UPDATEABLE);

If you don't pass any arguments to createStatement( ), you get a forward-only, read-only result set, just as you would using JDBC 1.0. Note that if you specify a scrollable result set (either sensitive or insensitive), you must also specify whether or not the result set is updateable. After you have created a scrollable ResultSet, use the methods listed in Table 2-2 to navigate through it. As with JDBC 1.0, when you start working with a ResultSet, you are positioned before the first row of results.

Table 2-2: JDBC 2.0 Record Scrolling Functions

Method	Function
first( )	Move to the first record.
last( )	Move to the last record.
next( )	Move to the next record.
previous( )	Move to the previous record.
beforeFirst( )	Move to immediately before the first record.
afterLast( )	Move to immediately after the last record.
absolute(int)	Move to an absolute row number. Takes a positive or negative argument.
relative(int)	Move backward or forward a specified number of rows. Takes a positive or negative argument.

The JDBC 2.0 API also includes a number of methods that tell you where you are in a ResultSet. You can think of your position in a ResultSet as the location of a cursor in the results. The isFirst( ) and isLast( ) methods return true if the cursor is located on the first or last record, respectively. isAfterLast( ) returns true if the cursor is after the last row in the result set, while isBeforeFirst( ) returns true if the cursor is before the first row.

With an updateable ResultSet, you can change data in an existing row, insert an entirely new row, or delete an existing row. To change data in an existing row, use the newupdateXXX( ) methods of ResultSet. Let's assume we want to update the CUSTOMER_ID field of the first row we retrieve (okay, it's a contrived example, but bear with us):

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
 ResultSet.CONCUR_UPDATEABLE);
ResultSet rs = stmt.executeQuery("SELECT NAME, CUSTOMER_ID FROM CUSTOMERS");
 
rs.first(  );
rs.updateInt(2, 35243); 
rs.updateRow(  );

Here we use first( ) to navigate to the first row of the result set and then call updateInt( ) to change the value of the customer ID column in the result set. After making the change, call updateRow( ) to actually make the change in the database. If you forget to call updateRow( ) before moving to another row in the result set, any changes you made are lost. If you need to make a number of changes in a single row, do so with multiple calls to updateXXX( ) methods and then a single call to updateRow( ). Just be sure you call updateRow( ) before moving on to another row.

The technique for inserting a row is similar to updating data in an existing row, with a few important differences. The first step is to move to what is called the insert row, using the moveToInsertRow( ) method. The insert row is a blank row associated with the ResultSet that contains all the fields, but no data; you can think of it as a pseudo-row in which you can compose a new row. After you have moved to the insert row, use updateXXX( ) methods to load new data into the insert row and then call insertRow( ) to append the new row to the ResultSet and the underlying database. Here's an example that adds a new customer to the database:

ResultSet rs = stmt.executeQuery(
     "SELECT NAME, CUSTOMER_ID FROM CUSTOMERS");
rs.moveToInsertRow(  );
rs.updateString(1, "Tom Flynn");
rs.updateInt(2, 35244); 
rs.insertRow(  );

Note that you don't have to supply a value for every column, as long as the columns you omit can accept null values. If you don't specify a value for a column that can't be null, you'll get a SQLException. After you call insertRow( ), you can create another new row, or you can move back to the ResultSet using the various navigation methods shown in Table 2-2. One final navigation method that isn't listed in the table is moveToCurrentRow( ). This method takes you back to where you were before you called moveToInsertRow( ); it can only be called while you are in the insert row.

Deleting a row from an updateable result set is easy. Simply move to the row you want to delete and call the deleteRow( ) method. Here's how to delete the last record in a ResultSet:

rs.last(  );
rs.deleteRow(  );

Calling deleteRow( ) also deletes the row from the underlying database.

Note that not all ResultSet objects are updateable. In general, the query must reference only a single table without any joins. Due to differences in database implementations, there is no single set of requirements for what makes an updateable ResultSet.

As useful as scrollable and updateable result sets are, the JDBC 2.0 specification doesn't require driver vendors to support them. If you are building middleware or some other kind of system that requires interaction with a wide range of database drivers, you should avoid this functionality for the time being. The extended JDBC 2.0 DatabaseMetaData object can provide information about scrolling and concurrency support.

Java-Aware Databases

Java is object-oriented; relational databases aren't. As a result, it's decidedly difficult to shoehorn a Java object into a stubbornly primitive-oriented database table. Luckily, the wind is changing, and newer database systems, including object-oriented database management systems (OODBMS) and Java-relational database management systems,[4] provide direct support for storing and manipulating objects. While a regular relational database can store only a limited number of primitive types, a JDBMS system can store entire, arbitrary Java objects.

Say we want to store a customized Java Account object in the ACCOUNTS table in a database. With a standard DBMS and JDBC 1.0, we have to pull each piece of data (account number, account holder, balance, etc.) out of the Account object and write it to a complicated database table. To get data out, we reverse the process. Short of serializing the Account object and writing it to a binary field (a rather complex operation), we're stuck with this clumsy approach.[5]

With JDBC 2.0, the getObject( ) method has been extended to support these new Java-aware databases. Provided that the database supports a Java-object type, we can read the Account object just like any primitive type:

ResultSet rs = stmt.executeQuery("SELECT ACCOUNT FROM ACCOUNTS");
rs.next(  );
Account a = (Account)rs.getObject(1);

To store an object, we use a PreparedStatement and the setObject( ) method:

Account a = new Account(  ); 
// Fill in appropriate fields in Account object
 
PreparedStatement stmt = con.prepareStatement(
 "INSERT INTO ACCOUNTS (ACCOUNT) VALUE (?)");
stmt.setObject(1, a);
stmt.executeUpdate(  );

A column that stores a Java object has a type of Types.JAVA_OBJECT. The JDBC API doesn't take any special steps to locate the bytecodes associated with any particular class, so you should make sure that any necessary objects can be instantiated with a call to Class.forName( ).

Handling Errors

Any JDBC object that encounters an error serious enough to halt execution throws a SQLException. For example, database connection errors, malformed SQL statements, and insufficient database privileges all throw SQLException objects.

The SQLException class extends the normal java.lang.Exception class and defines an additional method called getNextException( ). This allows JDBC classes to chain a series of SQLException objects together. SQLException also defines the getSQLState( ) and getErrorCode( ) methods to provide additional information about an error. The value returned by getSQLState( ) is one of the ANSI-92 SQL state codes; these codes are listed in Chapter 12. getErrorCode( ) returns a vendor-specific error code.

An extremely conscientious application might have a catch block that looks something like this:

try {
 // Actual database code
} 
catch (SQLException e) {
 while(e != null) { 
 System.out.println("\nSQL Exception:");
 System.out.println(e.getMessage(  ));
 System.out.println("ANSI-92 SQL State: " + e.getSQLState(  ));
 System.out.println("Vendor Error Code: " + e.getErrorCode(  ));
 e = e.getNextException(  );
 } 
}

SQL Warnings

JDBC classes also have the option of generating (but not throwing) a SQLWarning exception when something is not quite right, but at the same time, not sufficiently serious to warrant halting the entire program. For example, attempting to set a transaction isolation mode that is not supported by the underlying database might generate a warning rather than an exception. Remember, exactly what qualifies as a warning condition varies by database.

SQLWarning encapsulates the same information as SQLException and is used in a similar fashion. However, unlike SQLException objects, which are caught in try/catch blocks, warnings are retrieved using the getWarnings( ) methods of the Connection, Statement, ResultSet, CallableStatement, and PreparedStatement interfaces. SQLWarning implements the getMessage( ), getSQLState( ), and getErrorCode( ) methods in the same manner as SQLException.

If you are debugging an application, and you want to be aware of every little thing that goes wrong within the database, you might use a printWarnings( ) method like this one:

void printWarnings(SQLWarning warn) {
 while (warn != null) {
 System.out.println("\nSQL Warning:");
 System.out.println(warn.getMessage(  ));
 System.out.println("ANSI-92 SQL State: " + warn.getSQLState(  ));
 System.out.println("Vendor Error Code: " + warn.getErrorCode(  ));
 warn = warn.getNextWarning(  );
 }
}

You can then use the printWarnings( ) method as follows:

// Database initialization code here
ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMERS");
printWarnings(stmt.getWarnings(  ));
printWarnings(rs.getWarnings(  ));
// Rest of database code

Prepared Statements

The PreparedStatement object is a close relative of the Statement object. Both accomplish roughly the same thing: running SQL statements. PreparedStatement, however, allows you to precompile your SQL and run it repeatedly, adjusting specific parameters as necessary. Since processing SQL strings is a large part of a database's overhead, getting compilation out of the way at the start can significantly improve performance. With proper use, it can also simplify otherwise tedious database tasks.

As with Statement, you create a PreparedStatement object from a Connection object. In this case, though, the SQL is specified at creation instead of execution, using the prepareStatement( ) method of Connection:

PreparedStatement pstmt = con.prepareStatement(
 "INSERT INTO EMPLOYEES (NAME, PHONE) VALUES (?, ?)");

This SQL statement inserts a new row into the EMPLOYEES table, setting the NAME and PHONE columns to certain values. Since the whole point of a PreparedStatement is to be able to execute the statement repeatedly, we don't specify values in the call to prepareStatement( ), but instead use question marks (?) to indicate parameters for the statement. To actually run the statement, we specify values for the parameters and then execute the statement:

pstmt.clearParameters(  );
pstmt.setString(1, "Jimmy Adelphi");
pstmt.setString(2, "201 555-7823");
pstmt.executeUpdate(  );

Before setting parameters, we clear out any previously specified parameters with the clearParameters( ) method. Then we can set the value for each parameter (indexed from 1 to the number of question marks) using the setString( ) method. PreparedStatement defines numerous setXXX( ) methods for specifying different types of parameters; see the java.sql reference material later in this book for a complete list. Finally, we use the executeUpdate( ) method to run the SQL.

The setObject( ) method can insert Java object types into the database, provided that those objects can be converted to standard SQL types. setObject( ) comes in three flavors:

setObject(int parameterIndex, Object x, int targetSqlType, int scale)
setObject(int parameterIndex, Object x, int targetSqlType)
setObject(int parameterIndex, Object x)

Calling setObject( ) with only a parameter index and an Object causes the method to try and automatically map the Object to a standard SQL type (see Table 2-1). Calling setObject( ) with a type specified allows you to control the mapping. The setXXX( ) methods work a little differently, in that they attempt to map Java primitive types to JDBC types.

You can use PreparedStatement to insert null values into a database, either by calling the setNull( ) method or by passing a null value to one of the setXXX( ) methods that take an Object. In either case, you must specify the target SQL type.

Let's clarify with an example. We want to set the first parameter of a prepared statement to the value of an Integer object, while the second parameter, which is a VARCHAR, should be null. Here's some code that does that:

Integer i = new Integer(32);
pstmt.setObject(1, i, Types.INTEGER);
pstmt.setObject(2, null, Types.VARCHAR);
// or pstmt.setNull(2, Types.VARCHAR);

Batch Updates

The original JDBC standard was not very efficient for loading large amounts of information into a database. Even if you use a PreparedStatement, your program still executes a separate query for each piece of data inserted. If your software inserts 10,000 rows into the database, it can introduce a substantial performance bottleneck.

The new addBatch( ) method of Statement allows you to lump multiple update statements as a unit and execute them at once. Call addBatch( ) after you create the statement, and before execution:

con.setAutoCommit(false); // If some fail, we want to rollback the rest
Statement stmt = con.createStatement(  );
 
stmt.addBatch(
"INSERT INTO CUSTOMERS VALUES (1, "J Smith", "617 555-1323");
stmt.addBatch(
"INSERT INTO CUSTOMERS VALUES (2, "A Smith", "617 555-1132");
stmt.addBatch(
"INSERT INTO CUSTOMERS VALUES (3, "C Smith", "617 555-1238");
stmt.addBatch(
"INSERT INTO CUSTOMERS VALUES (4, "K Smith", "617 555-7823");
 
int[] upCounts = stmt.executeBatch(  );
con.commit(  );

Notice that we turn transaction auto-commit off before creating the batch. This is because we want to roll back all the SQL statements if one or more of them fail to execute properly (a more detailed discussion of transaction handling may be found later in this chapter, in the section "Transactions"). After calling addBatch( ) multiple times to create our batch, we call executeBatch( ) to send the SQL statements off to the database to be executed as a batch. Batch statements are executed in the order they are added to the batch. executeBatch( ) returns an array of update counts, in which each value in the array represents the number of rows affected by the corresponding batch statement. If you need to remove the statements from a pending batch job, you can call clearBatch( ), as long as you call it before calling executeBatch( ).

Note that you can use only SQL statements that return an update count (e.g., CREATE, DROP, INSERT, UPDATE, DELETE) as part of a batch. If you include a statement that returns a result set, such as SELECT, you get a SQLException when you execute the batch. If one of the statements in a batch can't be executed for some reason, executeBatch( ) throws a BatchUpdateException. This exception, derived from SQLException, contains an array of update counts for the batch statements that executed successfully before the exception was thrown. If we then call rollback( ), the components of the batch transaction that did execute successfully will be rolled back.

The addBatch( ) method works slightly differently for PreparedStatement and CallableStatement objects. To use batch updating with a PreparedStatement, create the statement normally, set the input parameters, and then call the addBatch( ) method with no arguments. Repeat as necessary and then call executeBatch( ) when you're finished:

con.setAutoCommit(false); // If some fail, we want to rollback the rest
PreparedStatement stmt = con.prepareStatement(
 "INSERT INTO CUSTOMERS VALUES (?,?,?)");
 
stmt.setInt(1,1);
stmt.setString(2, "J Smith");
stmt.setString(3, "617 555-1323");
stmt.addBatch(  );
 
stmt.setInt(1,2);
stmt.setString(2, "A Smith");
stmt.setString(3, "617 555-1132");
stmt.addBatch(  );
 
int[] upCounts = stmt.executeBatch(  );
con.commit(  );

This batch functionality also works with CallableStatement objects for stored procedures. The catch is that each stored procedure must return an update count and may not take any OUT or INOUT parameters.

BLOBs and CLOBs

As users began to increase the volume of data stored in databases, vendors introduced support for Large Objects (LOBs). The two varieties of LOBs, binary large objects (BLOBs) and character large objects (CLOBs), store large amounts of binary or character data, respectively.

Support for LOB types across databases varies. Some don't support them at all, and most have unique type names (BINARY, LONG RAW, and so forth). JDBC 1.0 makes programs retrieve BLOB and CLOB data using the getBinaryStream( ) or getAsciiStream( ) methods. (A third method, getUnicodeStream( ), has been deprecated in favor of the new getCharacterStream( ) method, which returns a Reader.)

In JDBC 2.0, the ResultSet interface includes getBlob( ) and getClob( ) methods, which return Blob and Clob objects, respectively. The Blob and Clob objects themselves allow access to their data via streams (the getBinaryStream( ) method of Blob and the getCharacterStream( ) method of Clob) or via direct-read methods (the getBytes( ) method of Blob and the getSubString( ) method of Clob).

To retrieve the data from a CLOB, simply retrieve the Clob object and call the getCharacterStream( ) method:

String s;
Clob clob = blobResultSet.getBlob("CLOBFIELD");
BufferedReader clobData = new BufferedReader(clob.getCharacterStream(  ));
while((s = clobData.readLine(  )) != null)
  System.out.println(s);

In addition, you can set Blob and Clob objects when you are working with a PreparedStatement, using the setBlob( ) and setClob( ) methods. While the API provides update methods for streams, there are no updateBlob( ) or updateClob( ) methods, and the Blob interface provides no mechanism for altering the contents of a Blob already stored in the database (although some drivers support updating of BLOB and CLOB types via the setBinaryStream( ) and setCharacterStream( ) methods of PreparedStatement). Note that the lifespan of a Blob or Clob object is limited to the transaction that created it.

JDBC driver support for BLOB and CLOB types varies wildly. Some vendors don't support any LOB functionality at all, and others (including Oracle) have added extensions to allow manipulation of LOB data. Check your driver documentation for more details.

Metadata

Most JDBC programs are designed to work with a specific database and particular tables in that database; the program knows exactly what kind of data it is dealing with. Some applications, however, need to dynamically discover information about result set structures or underlying database configurations. This information is called metadata, and JDBC provides two classes for dealing with it: DatabaseMetaData and ResultSetMetaData. If you are developing a JDBC application that will be deployed outside a known environment, you need to be familiar with these interfaces.

DatabaseMetaData

You can retrieve general information about the structure of a database with the java.sql.DatabaseMetaData interface. By making thorough use of this class, a program can tailor its SQL and use of JDBC on the fly, to accommodate different levels of database and JDBC driver support.

Database metadata is associated with a particular connection, so DatabaseMetaData objects are created with the getMetaData( ) method of Connection:

DatabaseMetaData dbmeta = con.getMetaData(  );

DatabaseMetaData provides an overwhelming number of methods you can call to get actual configuration information about the database. Some of these return String objects (getURL( )), some return boolean values (nullsAreSortedHigh( )), and still others return integers (getMaxConnections( )).

A number of other methods return ResultSet objects. These methods, such as getColumns( ), getTableTypes( ), and getPrivileges( ), generally encapsulate complex or variable-length information. The getTables( ) method, for instance, returns a ResultSet that contains the name of every table in the database as well as a good deal of extra information.

Many of the DatabaseMetaData methods take string patterns as arguments, allowing for simple wildcard searching. A percent sign (%) substitutes for any number of characters, and an underscore (_) calls for a single character match. Thus, %CUSTOMER% matches NEW_CUSTOMERS, CUSTOMER, and CUSTOMERS, while CUSTOMER% matches only CUSTOMER and CUSTOMERS. All of these patterns are case-sensitive.

Example 2-2 shows a simple program that displays some basic database characteristics, a list of tables, and a list of indexes on each table. The program assumes a JDBC driver with full support for all the DatabaseMetaData commands.

Example 2-2: DBViewer Program

import java.sql.*;
import java.util.StringTokenizer;
 
public class DBViewer {
 
  final static String jdbcURL = "jdbc:odbc:customerdsn";
  final static String jdbcDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
 
  public static void main(java.lang.String[] args) {
 
    System.out.println("--- Database Viewer ---");
   
    try {
      Class.forName(jdbcDriver);
      Connection con = DriverManager.getConnection(jdbcURL, "", "");
 
      DatabaseMetaData dbmd = con.getMetaData(  );
 
      System.out.println("Driver Name: " + dbmd.getDriverName(  ));
      System.out.println("Database Product: 
             " + dbmd.getDatabaseProductName(  ));
      System.out.println("SQL Keywords Supported:");
      StringTokenizer st = new StringTokenizer(dbmd.getSQLKeywords(  ), ",");
      while(st.hasMoreTokens(  ))
        System.out.println(" " + st.nextToken(  ));
  
      // Get a ResultSet that contains all of the tables in this database
      // We specify a table_type of "TABLE" to prevent seeing system tables,
      // views and so forth
      String[] tableTypes = { "TABLE" };
      ResultSet allTables = dbmd.getTables(null,null,null,tableTypes);
      while(allTables.next(  )) {
        String table_name = allTables.getString("TABLE_NAME");
        System.out.println("Table Name: " + table_name);
        System.out.println("Table Type:  
            " + allTables.getString("TABLE_TYPE"));
        System.out.println("Indexes: ");
 
        // Get a list of all the indexes for this table
        ResultSet indexList = 
                     dbmd.getIndexInfo(null,null,table_name,false,false);
        while(indexList.next(  )) {
          System.out.println(" Index Name: 
                     "+indexList.getString("INDEX_NAME"));
          System.out.println(" Column Name:
                     "+indexList.getString("COLUMN_NAME"));
        }
        indexList.close(  ); 
      }
 
      allTables.close(  );
      con.close(  );
    } 
    catch (ClassNotFoundException e) {
      System.out.println("Unable to load database driver class");
    }
    catch (SQLException e) {
      System.out.println("SQL Exception: " + e.getMessage(  ));
    }
  }
}

Here's some sample output when this program is run against a Microsoft Access database via the JDBC-ODBC bridge (snipped slightly to prevent several pages of uninteresting text):

--- Database Viewer ---
Driver Name: JDBC-ODBC Bridge (odbcjt32.dll)
Database Product: ACCESS
SQL Keywords Supported:
 ALPHANUMERIC
 AUTOINCREMENT
 BINARY
 BYTE
 FLOAT8
 ...
Table Name: Customers
Table Type: TABLE
Indexes: 
 Index Name: PrimaryKey
 Column Name:CustNo
 Index Name: AddressIndex
 Column Name:Address
 ...

ResultSetMetaData

The ResultSetMetaData interface provides information about the structure of a particular ResultSet. Data provided by ResultSetMetaData includes the number of available columns, the names of those columns, and the kind of data available in each. Example 2-3 shows a short program that displays the contents of a table and shows the data type for each column.

Example 2-3: TableViewer Program

import java.sql.*;
import java.util.StringTokenizer;
 
public class TableViewer {
 
  final static String jdbcURL = "jdbc:oracle:customerdb";
  final static String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
  final static String table = "CUSTOMERS";
 
  public static void main(java.lang.String[] args) {
  
    System.out.println("--- Table Viewer ---");
   
    try {
      Class.forName(jdbcDriver);
      Connection con = DriverManager.getConnection(jdbcURL, "", "");
      Statement stmt = con.createStatement(  );
      ResultSet rs = stmt.executeQuery("SELECT * FROM "+ table);
 
      ResultSetMetaData rsmd = rs.getMetaData(  );
      int columnCount = rsmd.getColumnCount(  );
      for(int col = 1; col <= columnCount; col++) {
        System.out.print(rsmd.getColumnLabel(col));
        System.out.print(" (" + rsmd.getColumnTypeName(col)+")");
        if(col < columnCount) 
          System.out.print(", ");
      }
      System.out.println(  );
  
      while(rs.next(  )) {
        for(int col = 1; col <= columnCount; col++) {
          System.out.print(rs.getString(col));
          if(col < columnCount) 
            System.out.print(", ");
        } 
        System.out.println(  );
      }  
  
      rs.close(  );
      stmt.close(  );
      con.close(  );
    }
    catch (ClassNotFoundException e) {
      System.out.println("Unable to load database driver class");
    }
    catch (SQLException e) {
      System.out.println("SQL Exception: " + e.getMessage(  ));
    }
  }
}

The key methods used here are getColumnCount( ), getColumnLabel( ), and getColumnTypeName( ). Note that type names returned by getColumnTypeName( ) are database-specific (e.g., Oracle refers to a string value as a VARCHAR; Microsoft Access calls it TEXT). Here's some sample output for TableViewer:

--- Table Viewer ---
CustNo (SHORT), CustName (VARCHAR), CustAddress (VARCHAR)
1, Jane Markham, 12 Stevens St
2, Louis Smith, 45 Morrison Lane
3, Woodrow Lang, 4 Times Square

Transactions

A transaction is a group of several operations that must behave atomically, i.e., as if they are a single, indivisible operation. With regards to databases, transactions allow you to combine one or more database actions into a single atomic unit. If you have an application that needs to execute multiple SQL statements to fulfill one goal (say, an inventory management system that needs to move items from an INVENTORY table to a SHIPPING table), you probably want to use JDBC's transaction services to accomplish the goal.

Working with a transaction involves the following steps: start the transaction, perform its component operations, and then either commit the transaction if all the component operations succeed or roll it back if one of the operations fails. The ability to roll back a transaction is the key feature. This means that if any one SQL statement fails, the entire operation fails, and it is as though none of the component operations took place. Therefore it is impossible to end up with a situation where, for example, the INVENTORY table has been debited, but the SHIPPING table has not been credited.

Another issue with transactions and databases concerns changes to the database becoming visible to the rest of the system. Transactions can operate at varying levels of isolation from the rest of the database. At the most isolated level, the results of all the component SQL statements become visible to the rest of the system only when the transaction is committed. In other words, nobody sees the reduced inventory before the shipping data is updated.

The Connection object in JDBC is responsible for transaction management. With JDBC, you are always using transactions in some form. By default, a new connection starts out in transaction auto-commit mode, which means that every SQL statement is executed as an individual transaction that is immediately committed to the database.

To perform a transaction that uses multiple statements, you have to call the setAutoCommit( ) method with a false argument. (You can check the status of auto-commit with the getAutoCommit( ) method.) Now you can execute the SQL statements that comprise your transaction. When you are done, call the commit( ) method to commit the transaction or the rollback( ) method to undo it. Here's an example:

try {
 con.setAutoCommit(false);
 // run some SQL
 stmt.executeUpdate("UPDATE INVENTORY SET ONHAND = 10 WHERE ID = 5");
 stmt.executeUpdate("INSERT INTO SHIPPING (QTY) VALUES (5)");
 con.commit(  );
} 
catch (SQLException e) {
 con.rollback(  ); //undo the results of the transaction
}

When auto-commit is set to false, you must remember to call commit( ) (or rollback( )) at the end of each transaction, or your changes will be lost.

JDBC supports a number of transaction isolation modes that allow you to control how the database deals with transaction conflicts--in other words, who sees what when. JDBC defines five modes, some of which may not be supported by all databases. The default mode varies depending on the underlying database and driver. Higher isolation levels yield poorer performance. Here are the five standard options, which are defined as integer constants in the Connection interface:

TRANSACTION_NONE

Transactions are either disabled or not supported.

TRANSACTION_READ_UNCOMMITTED

Minimal transaction support that allows dirty reads. In other words, other transactions can see the results of a transaction's SQL statements before the transaction commits itself. If you roll back your transaction, other transactions may be left with invalid data.

TRANSACTION_READ_COMMITTED

Transactions are prevented from reading rows with uncommitted changes; in other words, dirty reads aren't allowed.

TRANSACTION_REPEATABLE_READ

Protects against repeatable reads as well as dirty reads. Say one transaction reads a row that is subsequently altered (and committed) by another transaction. If the first transaction reads the row again, the first transaction doesn't get a different value the second time around. The new data is visible to the first transaction only after it calls commit( ) and performs another read.

TRANSACTION_SERIALIZABLE

Provides all the support of TRANSACTION_REAPEATABLE_READ and guards against row insertions as well. Say one transaction reads a set of rows, and then another transaction adds a row to the set. If the first transaction reads the set again, it doesn't see the newly added row. Put another way, this level of isolation forces the database to treat transactions as if they occurred one at a time.

Transaction isolation modes are set by the setTransactionIsolation( ) method. For example:

con.setTransactionIsolation(TRANSACTION_READ_COMMITTED);

You can use the DatabaseMetaData class to determine the transaction support of the underlying database. The most useful methods are getDefaultTransaction-Isolation( ), supportsTransactions( ), supportsTransactionIsolationLevel( ), and supportsDataDefinitionAndDataManipulationTransactions( ) (which may well be the longest method name in the Java API).

An application that uses transactions is a prime candidate for also using a connection pool (available in JDBC 2.0). Since each database transaction requires its own Connection object, an application that performs multiple simultaneous transactions (for instance, spawning threads that perform database updates) needs multiple connections available. Maintaining a pool of connections is much more efficient than creating a new one whenever you need a new transaction.

Stored Procedures

Most RDBMS systems include some sort of internal programming language (e.g., Oracle's PL/SQL). These languages allow database developers to embed procedural application code directly within the database and then call that code from other applications. The advantage of this approach is that the code can be written just once and then used in multiple different applications (even with different platforms and languages). It also allows application code to be divorced from the underlying table structure. If stored procedures handle all of the SQL, and applications just call the procedures, only the stored procedures need to be modified if the table structure is changed later on.

Here is an Oracle PL/SQL stored procedure:[6]

CREATE OR REPLACE PROCEDURE sp_interest
(id IN INTEGER
bal IN OUT FLOAT) IS
BEGIN
SELECT balance
INTO bal
FROM accounts
WHERE account_id = id;
 
bal := bal + bal * 0.03;
 
UPDATE accounts
SET balance = bal
WHERE account_id = id;
 
END;

This PL/SQL procedure takes two input values, an account ID and a balance, and returns an updated balance.

The CallableStatement interface is the JDBC object that supports stored procedures. The Connection class has a prepareCall( ) method that is very similar to the prepareStatement( ) method we used to create a PreparedStatement. Because each database has its own syntax for accessing stored procedures, JDBC defines a standardized escape syntax for accessing stored procedures with CallableStatement. The syntax for a stored procedure that doesn't return a result set is:

{call procedure_name[(?[,?...])]}

The syntax for a stored procedure that returns a result is:

{? = call procedure_name[(?[,?...])]}

In this syntax, each question mark (?) represents a placeholder for a procedure parameter or a return value. Note that the parameters are optional. The JDBC driver is responsible for translating the escape syntax into the database's own stored procedure syntax.

Here's a code fragment that uses CallableStatement to run the sp_interest stored procedure:

CallableStatment cstmt = con.prepareCall("{call sp_interest(?,?)}");
cstmt.registerOutParameter(2, Types.FLOAT);
cstmt.setInt(1, accountID);
cstmt.setFloat(2, 2343.23);
cstmt.execute(  );
out.println("New Balance:" + cstmt.getFloat(2));

In this example, we first create a CallableStatement using the prepareCall( ) method and passing in the appropriate escape syntax for the stored procedure. Since this stored procedure has an output parameter (actually, in this case, an IN OUT parameter, which means it also serves as an input parameter), we use the registerOutParameter( ) method to identify that parameter as an output of type FLOAT. Note that just as with prepared statements, substituted parameters are numbered from 1 to n, left to right. Any time you have an output parameter in a stored procedure, you need to register its type using registerOutParameter( ) before you execute the stored procedure.

Next we set the two input parameters, the account ID and the balance, using the appropriate setXXX( ) methods. Finally, we execute the stored procedure and then use the getFloat( ) method to display the new balance. The getXXX( ) methods of CallableStatement are similar to those of the ResultSet.

You need to use CallableStatement only with stored procedures that have output values, such as the one we just saw. You can use either of the other statement objects to execute stored procedures that take parameters but don't return anything.

Escape Sequences

Escape sequences allow JDBC programs to package certain database commands in a database-independent manner. Since different databases implement different features (especially scalar SQL functions) in different ways, in order to be truly portable, JDBC needs to provide a standard way to access at least a subset of that functionality. We've already seen escape sequences twice: with the various SQL date and time functions, and with the CallableStatement object.

A JDBC escape sequence consists of a pair of curly braces, a keyword, and a set of parameters. Thus, call is the keyword for stored procedures, while d, t, and ts are keywords for dates and times. One keyword we haven't seen yet is escape. This keyword specifies the character that is used to escape wildcard characters in a LIKE statement:

stmt.executeQuery(
 "SELECT * FROM ApiDocs WHERE Field_Name like 'TRANS\_%' {escape '\'}");

Normally, the underscore ( _ ) character is treated as a single-character wildcard, while the percent sign (%) is the multiple-character wildcard. By specifying the backslash (\) as the escape character, we can match on the underscore character itself. Note that the escape keyword can also be used outside wildcard searches. For example, SQL string termination characters (such as the single quote) need to be escaped when appearing within strings.

The fn keyword allows the use of internal scalar database functions. Scalar functions are a fairly standard component of most database architectures, even though the actual implementations vary. For instance, many databases support the SOUNDEX(string) function, which translates a character string into a numerical representation of its sound. Another function, DIFFERENCE(string1, string2), computes the difference between the soundex values for two strings. If the values are close enough, you can assume the two words sound the same ("Beacon" and "Bacon"). If your database supports DIFFERENCE, you can use it by executing a SQL statement that looks like this:

{fn DIFFERENCE("Beacon", "Bacon")}

Available scalar functions differ depending on the database being used. Also, some drivers, such as Oracle's, don't support the {fn} escape mechanism at all.

The last escape keyword is oj, which is used for outer joins. The syntax is simply:

{oj outer-join}

Outer joins aren't supported by some databases and are sufficiently complex (and unrelated to the JDBC API per se) as to be beyond the scope of this chapter. For more information, consult the SQL documentation for your database.

Note that when performance is an issue, you can use the setEscapeProcessing( ) method of Statement to turn off escape-sequence processing.

The JDBC Optional Package

The javax.sql package is an optional extension of the JDBC 2.1 API. It includes support for a variety of enterprise-development activities. It's a standard component of the J2EE platform, and the supporting classes can also be downloaded separately for use with any Java 2 system.

DataSource Objects

The DataSource interface provides an alternative to the DriverManager class and conventional JDBC URLs. Instead, information about a database is stored within a naming service and retrieved via the JNDI API. Connection information (drivers, server locations, and so forth) are stored within the DataSource object, which uses them to create the actual Connection object used to execute JDBC commands. DataSource objects are also used to provide native driver-level support for connection pooling and distributed transactions.

Each DataSource is assigned a logical name, by convention beginning with "jdbc/". The logical name and associated connection metadata are configured in the J2EE setup process. This makes code more portable and allows for easy changes in drivers and connection information. Accessing a DataSource via JNDI is very simple:

Context ctx = new InitialContext(  ); 
DataSource ds = (DataSource)ctx.lookup("jdbc/CamelDB"); 
Connection con = ds.getConnection("lawrence", "arabia");

The first two lines obtain the DataSource object from the naming service. The getConnection( ) method of DataSource then logs into the database and returns a Connection object. Unlike DriverManager, the only information required is a username and password.

Connection pooling

The ConnectionPoolDataSource provides a transparent interface to a "pool" of available Connection objects. When using a connection pool, the JVM creates a set of connections and distributes them to programs as needed (often on a per-thread basis). Once a connection has been used, it is returned to the pool to be reused later. This eliminates the substantial overhead of creating a new Connection for each request (a delay that can often be measured in seconds). For applications that make intensive use of database connections over extended periods, such as Java servlets (see Chapter 5), this added efficiency can be vital.

Like regular DataSources, connection pools are configured by the J2EE server administrator, and must be supported by the database driver itself. Developers need only to remember to explicitly close all Connection objects after use, which is good programming practice anyway. The best way to handle this is via a try...catch...finally block:

Connection con = null;
try {
 ds = (DataSource)cvs.lookup("jdbc/oasisDB");
 con = ds.getConnection("larryl", "camel");
 // ... some worthwhile action
} catch (Exception e) {
} finally {
     if(con != null)
             con.close(  );
}

It is also possible to perform connection pooling without the JDBC Optional Packages. An excellent open source connection pool, in use since 1998, is available from JavaExchange at http://www.javaexchange.com. It allows failure-tolerant pooling of connections using any JDBC driver.

Distributed transactions

With appropriate driver support, the XADataSource interface can also be used to create connections supporting distributed transactions. Like pooled connections, the DataSource must be configured by the administrator of the J2EE environment.

While connections supporting distributed transactions are nearly indistinguishable from regular connections, there is a functional difference: auto-commit mode defaults to off, and when a connection is used within a distributed transaction, the rollback( ), commit( ), and setAutoCommit( ) methods should not be called.

Connections received from an XADataSource may be used for nondistributed transactions as well. All of the usual transaction management commands may be used in a nondistributed transaction.

RowSets

The JDBC 2.0 optional package also includes a new RowSet interface. A RowSet, as the name implies, encapsulates a set of rows produced by a query. Since a RowSet is a JavaBean, it can be used easily in a graphical development environment.

The J2EE environment doesn't ship with any RowSet implementations, but Sun has made a number available at http://developer.java.sun.com/developer/earlyAccess/crs/index.html. Here's how to use the simplest of these, JdbcRowSet, which simply encapsulates a ResultSet:

 sun.jdbc.rowset.JdbcRowSet jdbcRowSet 
        = new sun.jdbc.rowset.JdbcRowSet(  );
 jdbcRowSet.setCommand("SELECT * FROM CUSTOMERS WHERE CUSTNO = ?");
 jdbcRowSet.setUrl("jdbc:oracle:thin:@dbhost.co.com:1521:ORCL");
 jdbcRowSet.setUsername("SAMSON");
 jdbcRowSet.setPassword("DELILAH");
 jdbcRowSet.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);
 jdbcRowSet.setConcurrency(ResultSet.CONCUR_UPDATABLE);
 jdbcRowSet.setInt(1, 10);
 jdbcRowSet.execute(  );
 
 jdbcRowSet.first(  );
 System.out.println(jdbcRowSet.getString(1));
 jdbcRowSet.last(  );
 System.out.println(jdbcRowSet.getString(1));
 
 jdbcRowSet.close(  );

The getXXX and setXXX methods are the same as in the ResultSet interface. Support for scrollable and updateable row sets depends on the underlying driver and database implementation.

The addRowSetListener( ) method of RowSet can be used to register other components as listeners. They must implement the RowSetListener interface to be implemented.

The sun.jdbc.rowset package also contains a CachedRowSet object that will hold a ResultSet independently of the originating JDBC connection.

Connection con = DriverManager.getConnection(dbURL, dbUser, dbPassword);
Statement stmt = con.createStatement(  );
ResultSet rs = stmt.executeQuery("SELECT * FROM CUSTOMERS");
CachedRowSet crs = new CachedRowSet(  );
crs.populate(rs);
rs.close(  );
stmt.close(  );
crs.next(  );
System.out.println(crs.getString("CUSTNAME"));

In order to update a CachedRowSet, you must set the JDBC connection information and specify the underlying SQL via the setCommand( ) method. Now, let's continue our code sample:

crs.setUrl(dbURL);
crs.setUsername(dbUser);
crs.setPassword(dbPassword);
crs.setCommand("SELECT * FROM CUSTOMERS");
//...
crs.setString("CUSTNAME", "John Smith");
crs.updateRow(  );
crs.moveToCurrentRow(  );
crs.acceptChanges(  );

Note that we call the acceptChanges( ) method after performing our update. This is necessary to propagate the changes back to the original datasource.

The final RowSet available from Sun is the WebRowSet, which is identical to the CachedRowSet but has a persistence engine based on XML.

While a RowSet object would generally be used with JDBC, there is no actual requirement that this be so. RowSet implementations could be written to act against tabular data, text files, and more esoteric storage mechanisms.

JDBC 3.0

At the time of this writing, Version 3.0 of the JDBC API was in its fourth proposed final draft, and was on target for inclusion in J2SE and J2EE Version 1.4. JDBC 3.0 adds increased support for SQL99 features, increasingly capable transaction support, full read/write handling of BLOB and CLOB fields, URL datatypes, and various minor enhancements to the rest of the API. JDBC 3.0 adds a number of methods to the DatabaseMetaData interface, allowing programmers to determine which new features are supported.

JDBC 3.0 is also intended to integrate well with the J2EE Connector standard, allowing drivers and configuration information to be packaged into a Resource adapter ARchive, or RAR file. This allows easier deployment of JDBC connections into a J2EE server, but doesn't change the way programmers interact with the API.

Savepoints

Savepoints allow transactions to be partially rolled back. If the underlying database and driver support the functionality, the new setSavepoint(String name) method of Connection creates a named savepoint in the current transaction, and returns an object implementing the Savepoint interface. The object can be passed to the rollback( ) method of Connection to roll back all components of the current transaction that took place after the setSavepoint( ) method was called:

Statement stmt = con.createStatement(  );
stmt.executeUpdate("delete from clients");
stmt.executeUpdate("insert into clients (NAME, ID) values ('Charles Babbage', 1)");
Savepoint save = con.setSavepoint("INSERT_POINT");
stmt.executeUpdate("update clients set NAME = 'Ada Lovelace' where ID = 1");
con.rollback(save);
con.commit(  );

This example will leave the "clients" table with a single row, with a value of 1 in the ID column and "Charles Babbage" in the NAME column.

Savepoints can't be used in distributed transactions.

SQL99 Types

SQL99 added a number of new datatypes to the SQL92 standard that guided earlier versions of the JDBC specification. Out of these, JDBC 3.0 introduces the DATALINK type, which maps to a java.net.URL object. DATALINK fields can be retrieved from queries using the new getURL( ) method of ResultSet.

The SQL99 specification also defines BLOB and CLOB datatypes for Binary and Character Large Objects (LOBs). Initial support for BLOB and CLOB types was added in JDBC 2.0, but they were read-only. The java.sql.Blob interface now includes a setBytes( ) method to alter the BLOB's content, and the java.sql.Clob class includes a setString( ) method.

It's up to the driver whether to update a local copy of the LOB or to directly update the copy in the database. The locatorsUpdateCopy( ) method of DatabaseMetaData will tell you which approach is used by your driver. If the method returns true, you'll need to issue a separate update statement to commit the changes to the LOB back to the database. To insert data in a new BLOB or CLOB field, first create a row with an empty LOB, select the row, retrieve the LOB, edit its contents, and write it back if necessary.

Modified Blob and Clob objects can be passed to the setBlob( ) and setClob( ) methods of PreparedStatement, and to the new updateBlob( ) and updateClob( ) methods of ResultSet (for updateable result sets).

---

1. According to Sun, JDBC is not an acronym for Java Database Connectivity.

2. The driver can figure this out after reading the object, but since some driver implementations and database connection protocols allow you to reliably read a value from a column only once, implementing an isNull( ) method requires the ResultSet to cache the entire row in memory. While many programs do exactly this, it is not appropriate behavior for the lowest-level result handler.

3. Some drivers, including early versions of Oracle's JDBC drivers, don't properly support this behavior.

4. This is Sun's term. We have yet to see any packages actually marketed as Java-relational databases, but many newer packages, including Oracle 8i, are capable of storing Java classes. A number of these products also use Java as a trigger language, generally in a JDBC structure.

5. Various commercial products, such as Sun's Forte developer tool, automatically handle mapping objects to database records and vice versa. Check the site http://www.javasoft.com/products/java-blend/index.html for more information.

6. If it looks familiar, that's because it is from George Reese's Database Programming with JDBC (O'Reilly).

Back to: Java Enterprise in a Nutshell, 2nd Edition