JDBC Programming

JDBC is Java's programming interface for universal data access, largely inspired by ODBC. Many JDBC calls have direct equivalents in ODBC. Although ODBC is designed for C and is a procedural interface, JDBC's architecture is object-oriented. Similar to ODBC, a good JDBC application should work with any DBMS vendor product using the same code because all JDBC drivers implement the interfaces in the java.sql and, optionally, the javax.sql packages.

Any well-architected interface for Java must be object-oriented, and JDBC is designed with this in mind. Different aspects of application-database interaction are maintained and controlled using Java objects, defined by classes that implement the JDBC interface specifications. JDBC drivers are initially registered with a noninstantiable DriverManager class, which is used to obtain database connections by specifying a URL. Each connection is represented using a Connection object. A Connection object provides methods to obtain and set connection-level properties, and to create Statement objects, which are used to execute SQL queries. Queries that return a result set have their cursor operations encapsulated in a ResultSet object.

If an error occurs during processing, an SQLException is thrown, which the application must handle. An additional aspect of error processing is handling warnings returned by DB2, but which are not thrown as exceptions. Warnings are stored in SQLWarning objects and can be obtained from Connection, Statement, or ResultSet objects. SQLWarning objects are an extension of SQLException, so they can be thrown by your application if the warning is deemed to be an application-level error.

It is important to note that JDBC is an extensive API, and there are additional objects to consider. First, we describe the fundamental objects mentioned previously, and introduce others as necessary when we discuss more advanced topics. Except for DriverManager, which is a class, each of the JDBC API members we name herein are formally defined as Java interfaces in the java.sql package.

The DB2 JDBC driver classes implement these interfaces, and have their own DB2-specific names. Because of the object-oriented programming concept known as polymorphism, your applications do not need to refer to the vendor-specific class names for the DB2 JDBC driver types. Instead, your Java programs should always declare objects using the JDBC API interface names.

Different types of JDBC drivers are designed to fulfill specific development needs. They are divided into four classes, based on their underlying implementation and capabilities. They are presented here with reference to DB2 as follows:

• Type I: JDBC-ODBC bridge plus ODBC Driver— The JDBC-ODBC Bridge Driver is supplied with the Java run-time libraries. This driver translates calls from a Java application into corresponding ODBC calls and issues them against an ODBC Driver Manager. Using the data source name, the ODBC Driver Manager then routes each ODBC call to the corresponding ODBC driver. Although this driver can be used with DB2, its intended use is with DBMS vendors that do not provide their own JDBC driver. The ODBC Driver Manager and a corresponding ODBC driver for the DBMS must be installed and configured for the JDBC-ODBC Bridge Driver to be used.

• Type II: Partly Java Driver Using Native-API— These drivers translate JDBC calls into DBMS-specific native-API calls. In DB2 v8 and earlier, the Type II driver provided is COM.ibm.db2.jdbc.app.DB2Driver, located in sqllib/java/db2java.zip. This driver translates JDBC calls into corresponding DB2 CLI calls via the Java Native Interface (JNI) and is known as the DB2 JDBC Application or “app” driver.

• Type III: JDBC-Net pure Java driver— Drivers that connect to a middleware process that perform DBMS-specific native-API calls fit into this category. The Type III JDBC driver in DB2 v8 is known as the DB2 JDBC applet or net driver. It is defined in the class COM.ibm.db2.jdbc.net.DB2Driver, located in sqllib/java/db2java.zip. This driver communicates with a DB2 Java daemon process, which translates calls into DB2 CLI. In DB2 v8, the Type III driver is deprecated but can still be used.

• Type IV: Pure Java Driver Using Native-protocol— Type IV drivers are written in pure Java, and directly establish a network connection to a DBMS and submit requests using a vendor-specific or standardized protocol. For example, DRDA is an industry standard protocol for communicating with a DBMS. A Type IV JDBC driver could communicate directly over the network to a DBMS using DRDA. DB2 will include its first Type IV driver in Fixpak 2 of DB2 v8. After the Type IV driver is released, it can be used in place of both the Type II and Type III drivers in applications and applets. The Type IV driver is located in the db2jcc.jar, and defined in the class com.ibm.db2.jcc.DB2Driver.

We will explore the app driver and the net driver in greater detail a little later, so you will become thoroughly acquainted with the intended use of each. You also can use the JDBC-ODBC bridge that is provided as part of the JDK or Java 2 SDK; however, this driver is designed to work with all vendors, so it does not take into account additional performance benefits that DB2-specific JDBC drivers can. Because the Type IV driver was unavailable at the time of this writing, it is not discussed in this book or included among the samples.

DB2 JDBC 2.1 Support

JDBC is an evolving interface. DB2 supports the JDBC 2.1 specification. JDBC 2.1 provides significant feature enhancements from JDBC 1.22, such as scrollable cursors, batch updates, connection pooling, distributed transactions (using the Java Transaction API), and better LOB support. DB2's JDBC 2.1 drivers support this functionality; however, they do not provide all of the features in the JDBC 2.1 specification. For example, DB2 does not have an Array data type, thus there is no support for use of SQL3 data types such as Array. DB2 v8 also doesn't support updatable result sets or customized SQL type mappings.

Functionality such as connection pooling and distributed transactions are specified in the JDBC 2.1 Standard Extension API, which requires the javax.sql packages. In addition, JDBC 2.1 applications often use the javax.transaction, javax.transaction.xa, and the javax.naming packages, which define the JTA and the JNDI. All of the javax packages mentioned are part of the Java 2 SDK Enterprise Edition.

DB2 JDBC Applet (or Net) Driver

The DB2 JDBC Applet or “net” driver is designed for use in Java applets that access DB2 databases. First, the client Web browser downloads db2java.zip prior to running the applet. Then, the DB2 net driver establishes a TCP/IP connection with a DB2 Java daemon process running on the system where the Web page was served. The DB2 Java daemon is simply a DB2 CLI application, which then fulfills database requests on behalf of the applet (e.g., see Figure 12.1). You can start a DB2 Java daemon process by running the command:

db2jstrt [port]

You can optionally specify the port number for the DB2 Java daemon to listen for TCP/IP connections. If no port is specified, the default port is 6789. It is possible to run multiple DB2 Java daemon processes on the same system as long as each listens on a different port.

Although the intended use for the net driver is with Java applets, it also can be used with Java applications and servlets. The benefit to this is that you don't need to install a DB2 client on the system where the Java program is running, and because the net driver is written entirely in Java, you can avoid the use of JNI. However, this is generally slower than using the app driver because the net driver needs to communicate with a middleware process to fulfill database requests.

DB2 JDBC Application (or App) Driver

The DB2 JDBC Application or “app” driver is intended for use with Java applications and servlets, or more generally, whenever the DB2 client libraries are installed on the system where the JDBC program is running (see Figure 12.2). The app driver communicates directly with a DB2 server using CLI calls made through the DB2 client libraries, and provides much better performance than the net driver because there is no DB2 Java daemon middleware process.

In addition, use of the DB2 net driver requires at least two network flows per flow that occurs using the app driver. This is because with the net driver, there is one flow from the program to the Java daemon, and a second from the Java daemon to the DB2 server (or IPC in the latter case if the DB2 Java daemon is running locally on a DB2 server). In comparison, the app driver requires a single network or IPC flow through the DB2 client libraries. Thus, the app driver should be used whenever possible.

It is not possible to use the app driver with Java applets because an applet is run on a client browser and most clients who view a Web page will not have the DB2 libraries installed on their system.

JDBC Driver Registration

Before a JDBC driver can be used in a Java program, it must be registered with a JDBC class known as the DriverManager. This only needs to be done once per driver in the application by instantiating a DB2 JDBC driver object using the Class.forName() method and specifying the fully qualified driver name. For the app driver, use the following syntax:

Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

Similarly, for the net driver, use the syntax:

Class.forName("COM.ibm.db2.jdbc.net.DB2Driver");

Optionally, you can append “.newInstance()” to each of these statements, however, the Java Virtual Machine (JVM) automatically creates a reference instance so this isn't strictly unnecessary. You can register a JDBC driver in a static initializer within the class definition, or within the run-time code. The former technique ensures that the driver is registered as soon as the class using it is loaded by the JVM. A static initializer can be written as follows:

Class MyClass {
   static {
      try {
         Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
         Class.forName("COM.ibm.db2.jdbc.net.DB2Driver");
      } catch (Exception e) {
         e.printStackTrace();
      }
   }
 // Additional class definition
}

In this example, we have registered both DB2 JDBC drivers. You can use as many JDBC drivers in your Java programs as you want, using the prescribed method. In addition to registering these drivers, ensure that you have imported all of the necessary JDBC packages. The following is an all-inclusive series of import commands:

import java.sql.*;
import javax.sql.*;
import javax.transaction.*;
import javax.transaction.xa.*;
import javax.naming.*;

It is not always necessary to import all of these packages into your class. For example, if you are only using the JDBC 2.1 Core API, you do not need the javax packages. These latter packages are used with the JDBC 2.1 Standard Extension API, the JTA, and the JNDI, which require the Java 2 SDK Enterprise Edition to be installed.

Supported Data Types

SQL data types need to be mapped to corresponding programming language data types to be accessed by an application. In Java, this is fairly straightforward. Every SQL data type has a corresponding JDBC type and a Java type. The JDBC types are defined in the java.sql.Types class, and map to standard Java primitive types or class objects (see Table 12.1).

Most character data is mapped into the Java String type, and binary data is mapped into the Java byte[] type. JDBC also defines several data types of its own to handle DATE, TIME, and TIMESTAMP data types, as well as BLOB and CLOB data.

Most types correspond to individual getXXX() and setXXX() methods in the ResultSet and PreparedStatement interfaces, which you use when retrieving values from result sets, and when binding statement parameters. We distinguish getXXX() and setXXX() methods from regular getter and setter methods. We use “XXX” as a variable term to indicate that a Java type name should be substituted for retrieving or binding an output column or parameter, for an SQL query.

JDBC Interfaces

The following sections discuss the JDBC interface specifications in detail, with a listing of many commonly used methods in each class or interface. However, for a complete reference of the JDBC API, please download or view the Java 2 SDK documentation for JDBC 2.1. You can find the specifications and the API online at the Sun Microsystems site for Java. The links at the end of the chapter includes these sites as well as others.

DriverManager

The JDBC DriverManager class is included in the java.sql package. JDBC drivers register themselves with the DriverManager when they are instantiated using Class.forName() syntax as discussed earlier. A JDBC application then calls a DriverManager.getConnection() method to obtain a database connection. The database connection information is specified using a URL. A valid URL for the DB2 app driver has the form:

jdbc:db2:<dbname>

The first part of the URL specifies the protocol, which in this case is JDBC, the second specifies the subprotocol, which corresponds to a DB2 database connection, and the third is the data source name. Only the third needs to be uniquely supplied by the application. For example, here is how you would connect locally to the DB2 mall database using the app driver.

String url = "jdbc:db2:db2mall";
Connection con = DriverManager.getConnection(url);

The database connection is returned in the form of a JDBC Connection object. The URL for the net driver is a little more complicated:

jdbc:db2://<server>:<port>/<dbname>

Following the protocol and subprotocol, the // symbol indicates a TCP/IP connection, followed by the server name. After this, the DB2 Java daemon listener port may optionally be specified. If no port is specified, then the default port 6789 is used. Finally, you indicate the database name by preceding it with a slash (/).

For example, using the net driver, you would connect to the db2mall database on a system known as sanyal1 using a DB2 Java daemon listening on TCP/IP port 7000 as follows:

String url = "jdbc:db2://sanyal1:7000/db2mall";
String uid = "steve";
String pwd = "mypassword";
Connection con = DriverManager.getConnection(url, uid, pwd);

This example used a slightly different form of the getConnection() method, but still received a Connection object. You have probably guessed that the Connection returned by the net driver is different from the one returned by the app driver, because they have different implementations. However, you can polymorphically consider them the same type of object, even though the actual classes are different. The various forms of the getConnection() method are described below.

DriverManager methods:

• Connection getConnection(String url)— Connects and returns a Connection object. Connecting with only a URL is valid for local database connections and for remote connections when DB2 server authentication type is set to CLIENT.

• Connection getConnection(String url, String userid, String passwd)— Connects and returns a Connection object. URL, userid, and password are all specified.

• Connection getConnection(String url, Properties info)— Connects and returns a Connection object. The user and password keys are specified in a Properties object.

Connection

All connection-level operations such as, transaction control and isolation level setting, are accomplished by calling methods in a Connection object. In addition, because every SQL query must reference a specific connection, JDBC Statement objects are also obtained by invoking methods in a Connection object. As just described, a DB2 Connection object is obtained by calling a DriverManager.getConnection() method with a valid DB2 database URL. We present many of the useful methods in this section, but leave examples until the next section, so that we can combine them with the discussion of Statement and ResultSet objects.

Connection methods:

• void close()— Closes the connection and deallocates resources for this Connection object.

• void commit()— Commits the current transaction.

• void rollback()— Rolls back the current transaction.

• void setTransactionIsolation(int level)— Sets the isolation level

• void setAutoCommit(boolean enableAutoCommit)— Turns autocommit mode on or off. When autocommit is on, every SQL statement results in a commit flow to the DB2 server.

• Statement createStatement()— Returns a Statement object which can be executed.

• PreparedStatement prepareStatement(String sql)— Prepares a statement for execution and returns the corresponding PreparedStatement object. A prepared statement can be executed multiple times with different input parameters.

• CallableStatement prepareCall(String sql)— Prepares a stored procedure call, and returns a CallableStatement object.

Statement

A Statement object lets you execute an SQL query using the corresponding JDBC Connection. When all of the values in your query can be specified literally, a Statement object will suffice. To dynamically prepare and execute statements, however, JDBC supplies the PreparedStatement interface, which extends Statement. PreparedStatement allows you to bind parameter markers, consistent with a dynamic SQL PREPARE operation. You can rebind these parameters and execute the same query many times with different sets of parameters this way, without having to perform a PREPARE operation each time. This is an obvious performance improvement because query preparation has the cost of generating an access plan.

In addition, the CallableStatement interface extends the interface defined by PreparedStatement. CallableStatement objects are used to call stored procedures that have output parameters. Thus, consistent with object-oriented inheritance concepts, PreparedStatement objects inherit the functionality of Statement objects, and CallableStatement objects inherit the functionality of both PreparedStatement and Statement.

Following are some of the commonly used methods in the Statement interface. We provide similar references for PreparedStatement and CallableStatement when we discuss their use in greater detail.

Statement methods:

• void addBatch(String sql)— Adds sql to this object's list of commands for batch execution.

• void cancel()— Cancels this statement's execution.

• void close()— Releases this statement's resources immediately.

• boolean getMoreResults()— Returns true if there are more results to be returned by this statement. This method is used when a stored procedure was executed.

• ResultSet getResultSet()— Returns the current result set. This is used to retrieve multiple result sets when a stored procedure was executed.

• boolean execute(String sql)— Executes an SQL statement that returns more than one result set. If no result sets are returned, then false is returned. This method is used for calling stored procedures.

• int [] executeBatch()— Executes the batch of queries and returns an array whose length is equal to the number of queries. Each entry contains the number of rows affected by the corresponding query execution.

• ResultSet executeQuery(String sql)— Executes sql, and returns the corresponding ResultSet object.

• int executeUpdate(String sql)— Executes INSERT, UPDATE, or DELETE queries and returns the number of rows affected.

• void setQueryTimeout(int seconds)— Sets the number of seconds before a query will time out during execution.

ResultSet

When a query is executed and produces a result set, the corresponding Statement object returns a ResultSet object. A ResultSet object manipulates a cursor and allows you to retrieve values for each column. In DB2, forward-only result sets can use updatable cursors. The DB2 JDBC 2.1 driver also introduces read-only scrollable result sets.

ResultSet methods:

• boolean absolute(int row)— Moves cursor position to specified row. If row is negative, then it is specified from end of result set. If the row number does not exist, then false is returned.

• void close()— Closes the cursor for this result set.

• int getInt(int columnNumber)— Returns an int value for the column specified by columnNumber.

• int getInt(String columnName)— Returns an int value for the output column specified by columnName. Requires additional lookup, so this is slower than the form of this method that specifies column number.

• boolean next()— Fetches the next row in the result set, and returns false if there are no more rows.

• boolean previous()— Fetches the previous row in the result set.

• boolean relative(int rows)— Fetches the row specified, relative to the current row.

• boolean wasNull()— Returns true if the last value was a NULL value.

SQLException

Dealing with errors is greatly facilitated in JDBC applications because the JDBC driver does all error checking. If an error occurs, the driver will throw an SQLException, which the application must catch. This is cleaner than ODBC, where the application must check the return code for every function call, and call appropriate error-handling routines when required. JDBC error checking uses standard try-catch block syntax as follows:

try {
   // JDBC operations
} catch (SQLException e) {
   //Error handling code
}

A try-catch block like this one should surround all JDBC sections of your Java code. Otherwise, ensure that methods that perform JDBC operations but do not catch exceptions declare this in a throws declaration. For example:

void myJDBCMethod() throws SQLException
{
// method contents
}

This fictitious method declares that it will throw an SQLException if one occurs, thus leaving the responsibility to the caller to handle it. There are several useful methods from SQLException objects, which are outlined next. We do not use try-catch blocks or method-throw declarations in our samples in this chapter for simplicity and readability, but you should be aware that they are required.

SQLException methods:

• String getSQLState()— Returns the SQL state for the error.

• int getErrorCode()— Returns the DB2-specific SQL code for the error.

• String getMessage()— Returns the error message associated with the error.

• void printStackTrace()— Prints out the Java stack traceback for the error.

SQLWarning

The SQLWarning class is included in the java.sql package and is used to report warnings from a DB2 operation. The Connection, Statement, and ResultSet classes each have a getWarnings() method that returns the first warning associated with the object.

Executing Statements

The previous section described how to connect to a DB2 database using the DriverManager.getConnection() methods. This section describes in detail how to execute SQL queries and process result sets. The first example updates the PRODUCT table UNITS_IN_STOCK inventory account column:

// Declare variables
String sql = "UPDATE product " +
              "SET units_in_stock=units_in_stock-10 " +
              "WHERE product_id=123";

Connection con = null;
int rowCount = 0;

// Obtain connection and set autocommit on
con = DriverManager.getConnection("jdbc:db2:db2mall");
con.setAutoCommit(true);

Statement stmt = con.createStatement();
rowCount = stmt.executeUpdate(sql);
stmt.close();

// Disconnect
con.close();

Because we knew all of the values in our query beforehand, we hard-coded them, and thus used a Statement object obtained from the connection. In addition, because there was only a single statement in our transaction, we turned on autocommit. It is on by default, but we used setAutoCommit() for clarity.

We then called the executeUpdate() method of the Statement object to execute our query. This method is used with all INSERT, DELETE, and UPDATE statements, and returns a count of the number of rows affected. We stored this value in the rowCount variable in the example, but we did not use it for processing purposes. Notice that we called the close() method for each JDBC object, thereby ensuring that we did not continue to use resources we did not need.

If you notice that iterative execution within your application results in increased memory usage each time, be sure that you are closing off objects when you are finished with them. Otherwise, your applications will have to wait for Java's garbage collector thread to destroy objects, which is less efficient.

Using Prepared Statements

To execute the same statement multiple times, you should use a PreparedStatement instead of a Statement object. This way you save the overhead of obtaining a new Statement object and the PREPARE cost of each additional query. This is possible because the syntax of each query is the same, and only parameters in the SET and WHERE clauses differ. The following demonstrates how to use PreparedStatement objects by modifying the previous example:

// Declare variables
String sql = "UPDATE product " +
              "SET units_in_stock = units_in_stock - ? " +
              "WHERE product_id = ?";
int [] newUnitsInStock = {10, 20, 30};
int [] productIds = {123, 456, 789};

Connection con = null;
int rowCount = 0;

// Obtain connection and turn off autocommit
con = DriverManager.getConnection("jdbc:db2:db2mall");
con.setAutoCommit(false);

// Prepare the statement and process each update
PreparedStatement ps = con.prepareStatement(sql);
for(int i=0; i < newUnitsInStock.length; i++)
{
   ps.setInt(1, newUnitsInStock[i]);
   ps.setInt(2, productIds[i]);
   rowCount = ps.executeUpdate();
}

// Commit the transaction
ps.close();
con.commit();

// Disconnect
con.close();

Notice that parameter markers are specified using the ? character in the query string this time, and the setXXX() methods are used in the PreparedStatement object to set each parameter's value according to data type. In the example, both parameters were integers, so we used setInt(). Also, notice that the PreparedStatement object is not closed until after all of the statements have been executed, because we reference the same prepared statement with each execution.

In addition, this time we turned off autocommit to explicitly commit the transaction only after all three queries were successful. This would make sense, for example, if the customer had purchased multiple items, and you needed to ensure that all of the UNITS_IN_STOCK columns were updated before the transaction was completed.

Here are some of the popular methods in the PreparedStatement interface.

PreparedStatement methods:

• ResultSet executeQuery()— Executes the prepared query and returns the corresponding ResultSet.

• boolean execute()— Returns true if this execute statement returns one or more result sets. This is used for calling stored procedures.

• int executeUpdate()— Executes an INSERT, UPDATE, or DELETE statement and returns the number of affected rows.

• void setNull(int parameterIndex, int jdbcType)— Sets the value of the parameter specified by the parameterIndex to NULL, using the specified jdbcType (for all JDBC types, see the Types class in java.sql).

• setInt(int parameterIndex, int x)— Sets the value of the parameter specified by the parameterIndex to the value x.

Retrieving Result Sets

So far, all the examples have involved updating data. Let's shift our focus to using queries that return a result set. A ResultSet object is returned when you execute the query using a Statement or PreparedStatement object. For example, you can execute a literal query using a Statement object and assign the ResultSet to a variable as follows:

ResultSet rs = null;
Statement stmt = null;
String sql = "SELECT C1 FROM T1";
Connection con =
   DriverManager.getConnection("jdbc:db2:db2mall");
stmt = con.createStatement();
rs = stmt.executeQuery(query);

Similarly, you can prepare a SELECT query and obtain a ResultSet using a PreparedStatement object:

ResultSet rs = null;
PreparedStatement ps = null;
String sql = "SELECT C1 FROM T1";
Connection con =
   DriverManager.getConnection("jdbc:db2:db2mall");
ps = con.prepareStatement(sql);
rs = stmt.executeQuery();

Both examples used different forms of the query execution method, however, in each case, a ResultSet object was returned. You process the result set the same way in each case, using the next() method to fetch the next row of data until false is returned, and using getXXX() methods to obtain each column value.

while (rs.next()) {
   int col1 = rs.getInt(1);
   // Further process retrieved data
}
rs.close();

This example loops through the result set, fetching each column into a local variable within the loop upon each iteration. Although the example specified the column to fetch by its relative position in the result set, you could also request it by name using an overloaded version of each getXXX() method. Later in this chapter, you learn how to further automate result set processing, using ResultSetMetaData objects.

NULL Values

In all of the code we have presented, we assumed that there were no NULL values in the columns retrieved in a ResultSet. Each JDBC type has a default value that is returned if a value was NULL, however, this is not always a reliable mechanism to determine whether a value was NULL. Methods that return an object will return Java's NULL constant, but methods that return primitives will return 0. For example, the getInt() method returns 0 if the value retrieved was NULL, but 0 is certainly a legitimate value for an INTEGER column. For this reason, there is an additional method defined in the ResultSet interface to determine whether the last column extracted from the ResultSet was NULL. This method is appropriately named wasNull() and it returns a boolean. There are also setNull() methods in the PreparedStatement interface to allow you to bind NULL values in a query.

Updating Rows in a Result Set

By default, the DB2 JDBC drivers use forward-only, read-only cursors. This means that you can only move forward through the result set, and you cannot update or delete any of the rows you retrieve by referencing that cursor. Under this scenario, you would need to create a separate Statement or PreparedStatement object where you could uniquely specify the row to update or delete using a key. This is less efficient, because DB2 will have to determine which row matches the specified criteria (such as a key). However, this type of strategy may be required at times if you cache an entire result set in a GUI tool. Otherwise, you should use a forward-only updatable cursor such as in the following example:

// Queries and cursor name
String sqlSelect = "SELECT C1, C2 FROM T1 FOR UPDATE";
String sqlUpdate = "UPDATE T1 SET C2=? WHERE CURRENT OF ";
String cursorName = null;

// Execute SELECT statement and process result set
Statement stmt = con.createStatement(sqlSelect);
ResultSet rs = stmt.executeQuery();
cursorName = rs.getCursorName();
PreparedStatement ps = con.prepareStatement(sqlUpdate + cursorName);

while (rs.next()) {
   String c1 = rs.getString(1);
   String c2 = rs.getString(2);

   // Apply business logic on the retrieved row
   if ( businessLogic is true ) {
      String newC2 = "new value";
      ps.setString(1, newC2);
      ps.executeUpdate();
   }
}
rs.close();
ps.close();
stmt.close();

The getCursorName() method in the ResultSet object is used to create the second query. In doing so, the code will run more efficiently because DB2 will not have to find the row to update, it knows that it is referenced by the current cursor position.

Specifying the Result Set Type

The Statement creation methods in the Connection interface are overloaded to specify the result set type and concurrency type you want to use. Each of these types corresponds to a static integer constant defined in the ResultSet interface. There are two types of concurrency: CONCUR_READ_ONLY and CONCUR_UPDATABLE, which correspond to whether the query is FOR FETCH ONLY or FOR UPDATE, respectively.

In addition, there are three types of result sets: TYPE_FORWARD_ONLY, TYPE_SCROLL _INSENSITIVE, and TYPE_SCROLL_SENSITIVE. TYPE_FORWARD_ONLY result sets only allow you to scroll forward. The other two types are both scrollable, meaning that you can scroll backward or forward, either by one record or by a relative number of records. A sensitive result set will show you changes that have been made to the underlying data if you query the same row more than once. A scroll-insensitive result set will not reflect these changes until the query is re-executed. Table 12.2 describes each concurrency type and result set type supported by DB2.

This table shows there are two types of ResultSet objects you can specify:

• TYPE_FORWARD_ONLY, CONCUR_READ_ONLY

• TYPE_SCROLL_SENSITIVE, CONCUR_READ_ONLY

From this, it is important to note that although we have discussed updatable result sets in the previous section, the DB2 JDBC drivers do not support the CONCUR_UPDATABLE parameter. Thus the updateXXX() methods specified in the JDBC 2.1 ResultSet interface (used with CONCUR_UPDATABLE result sets) are not supported. To use updatable result sets, you will have to continue using the technique shown in the previous section using the cursor name to update the current row.

Using Scrollable Result Sets

Navigating through a scrollable result set is simple, and you only need to know a handful of methods in the ResultSet interface. The previous() method is analogous to next(), in that it moves back one row in the result set, rather than forward. You also can specify a row relative to the current row using the relative() method, which takes a positive or negative integer as a parameter.

To move to an absolute position in the result set, use the absolute() method, which also takes a positive or negative integer as a parameter. A positive value specifies a position relative to the beginning of the result set, with the first row being 1. A negative value specifies a position relative to the last row in the result set, with –1 being the last row. You also can use the first() and last() methods to move to the beginning or the end of the result set. Both of these methods return a boolean, which indicates that there were no rows in the result set when the value is false.

Calling Stored Procedures

Stored procedures can be called with a Statement, PreparedStatement, or CallableStatement object. If the stored procedure has no parameters, or only input parameters that you are specifying literally, you can use a Statement object. When you want to use parameter markers and the stored procedure only has input parameters, use a PreparedStatement object. A stored procedure with output parameters is called a CallableStatement object. Because we have already explained how to use these types of objects, we will focus here on how to use CallableStatement objects.

A CallableStatement object is obtained using a prepareCall() method in the corresponding Connection object. The prepareCall() is issued in exactly the same manner as prepareStatement(). You also bind IN and INOUT parameters for the stored procedure using the setXXX() methods in PreparedStatement. However, for INOUT and OUT parameters, you also must make a registerOutParameter() call for each parameter of either of these types. Upon execution of the stored procedure, you use getXXX() methods from the CallableStatement interface, equivalent to the ones you used in the ResultSet interface to retrieve output columns.

One of the main features of stored procedures is their capability to return one or more result sets. If your stored procedure returns a single result set, then to execute your procedure, use the executeQuery() method, which returns a ResultSet object. The following example demonstrates a stored procedure that returns an integer output parameter and returns a result set:

// Prepare and execute a stored procedure call
// for a single result set stored procedure.
CallableStatement cstmt = con.prepareCall("CALL PROC1(?)");
cstmt.registerOutParameter(1, Types.INTEGER);
ResultSet rs = cstmt.executeQuery();
int output = cstmt.getString(1);
processResults(rs);

This example obtains the result set and then passes it to another method, processResults() for handling. If your stored procedure returns zero result sets, or more than one result set, then use the execute() method, which returns a boolean value. If execute() returns true, this means that there are result sets returned. If false is returned, then there were no result sets returned. Here is the programming logic to use:

if(cstmt.execute()) {
     do {
      ResultSet rs = cstmt.getResultSet();
      processResults(rs);
   } while(cstmt.getMoreResults());
} else {
   int updateCount = cstmt.getUpdateCount();
}

The task of determining whether to retrieve result sets, or to obtain the update count is automated. A loop condition checks whether more result sets are present. Notice that you do not call the getMoreResults() method until you have processed our ResultSet object because you can only process one ResultSet on a stored procedure call at one time. Every time you call getMoreResults(), the previous ResultSet will be closed, if it has not already been.

Although CallableStatement is a child-class of PreparedStatement, stored procedure calls are not the same as other dynamically prepared SQL statements. The fundamental difference is that there is no access plan generated by DB2 when calling a stored procedure, however, CallableStatement allows you to bind input and output parameters for the stored procedure, conceptually similar to a PreparedStatement object. It is important to know this because a stored procedure call will not require the same overhead as other prepared statements if you are concerned about performance. In fact, they may offer significant performance improvements, as discussed in Chapter 3.

CallableStatement methods:

• void registerOutParameter(int parameterIndex, int jdbcType)— Registers the return type for the corresponding output parameter.

• boolean wasNull()— Returns true if the most recently retrieved parameter was a NULL value.

• int getInt(int parameterIndex)— Standard getter method for output parameters. Other getter methods have similar format.

Batch Execution

DB2 supports JDBC 2.1's performance improving batch-processing feature. When you execute a set of queries against the same DB2 database as a batch, they are all flowed to the DB2 server together, thereby reducing the number of network flows required if they were each executed through separate statement objects. Batch updates are only valid for UPDATE, DELETE, INSERT, and stored procedure calls that have no output parameters nor return any result sets. For example, if a customer has purchased several items and you need to update the inventory of several items in the PRODUCT table of the mall database, you can send all of the updates at once. Here is an example of how to use batch updates:

int [] stockReduction = { 10, 20, 30 };
int [] productIds = { 123, 456, 789,};

String sql = "UPDATE product " +
              "SET units_in_stock = units_in_stock - ? " +
              "WHERE product_id = ?";

PreparedStatement ps = con.prepareStatement(sql);

for(int i=0; i < stockReduction.length; i++) {
   ps.setInt(1, stockReduction [i]);
   ps.setInt(2, productIds[i]);
   ps.addBatch();
}
int [] rowCounts = ps.executeBatch();

The addBatch() method is used on the PreparedStatement to add a statement to a current batch. Thus, the addBatch() method understands that if you call addBatch() multiple times on the same PreparedStatement object, you are actually specifying more than one statement to execute.

In addition, the executeBatch() method is called to actually run the registered batch queries. This method returns an array of integers, each of which is an update count for the corresponding statement in the batch of queries. Each query in the batch is executed in the order in which it was added.

LOBs

LOBs, such as Binary Large Objects (BLOBs) and Character Large Objects (CLOBs), are handled differently in JDBC 1.22 and JDBC 2.1. In JDBC 1.22, you have several choices of how to bind and retrieve LOB data. The getXXX() methods for LOB data are summarized in the following API section. There is a simlar setXXX() method for each getXXX(), which we have not listed (see the Java API documentation for details).

LOB getXXX() methods for JDBC 1.22:

• byte [] getBytes(int columnIndex)— Returns a byte array containing the value of a BLOB column.

• String getString(int columnIndex)— Returns a CLOB column as a Unicode string.

• InputStream getBinaryStream(int columnIndex)— Returns a stream to retrieve BLOB data from an output column.

• InputStream getUnicodeStream(int columnIndex)— Returns a stream to retrieve CLOB data from an output column. The data is converted from the DB2 server code page to the DB2 client code page, and then from the DB2 client code page into Unicode.

• InputStream getAsciiStream(int columnIndex)— Returns a stream to retrieve CLOB data from an output column. The data is converted from the DB2 server code page to the DB2 client page.

JDBC 2.1 defines interfaces for Blob and Clob objects, and corresponding getter (getBlob() and getClob()) and setter (setBlob() and setClob()) methods for each in ResultSet and PreparedStatement, respectively. DB2's JDBC 2.1 drivers support the use of these methods and provide implementations of the Blob and Clob interfaces. You also can use the JDBC 1.22 methods for LOB access with the DB2 JDBC 2.1 drivers. Note that the DB2 v8 JDBC drivers do not support BLOB and CLOB data types in stored procedures or UDFs, so you will need to use JDBC 1.22 when implementing these.

Metadata

Metadata is data that describes other data. There are two types of metadata interfaces, ResultSetMetaData and DatabaseMetaData. A Connection object is used to obtain a corresponding DatabaseMetaData object, which you can use to determine the functionality and supported properties about the database to which you are connected. For example, if you connect to a DB2 UDB v7.1 database, and a DB2 UDB v8 database, each will have slightly different supported properties, such as the maximum number of columns per table. There are more than 150 methods in the DatabaseMetaData interface that let you query this type of information, and it is fully documented in the JDBC API documentation.

Similarly, a ResultSetMetaData object is obtained from a ResultSet object. The most popular method used here is the getColumnCount(), which returns the total number of columns in the ResultSet. Another useful method is getColumnLabel(), which returns the name of the specified column. Because most data can be converted to character format and retrieved using a getString() method from a Statement object, using getColumnCount() and getColumnLabel() is an easy way to write a for loop to process any ResultSet. There is an example of this in the processResultSet() method of the DB2MetaDataAnalyzer.java sample included with this book.

This sample calls all of the DatabaseMetaData methods from against a JDBC Connection object and outputs the result to screen. You can use this sample as an easy way to discover the properties of your DB2 databases, and to assist you in coding your JDBC applications. Because some of the DatabaseMetaData methods return a ResultSet object, we've written a simple processResultSet() method to display any ResultSet object. Here is a simplified version of this method:

public void processResultSet(ResultSet rs) throws SQLException
{
ResultSetMetaData rsmd = rs.getMetaData();

int colCount = rsmd.getColumnCount();

// Output column labels
for(int i=1; i < colCount+1; i++)
{
   System.out.print(rsmd.getColumnLabel(i) + "  ");
}

// Scroll through result set and output
// column values.  No formatting included.
while(rs.next())
{
   for(int i=1; i < colCount+1; i++)
   {
      String columnValue = rs.getString(i);
      if(rs.wasNull())
         columnValue = "NULL";
      System.out.print(columnValue + "  ");
   }
   System.out.println();
}
} // end processResultSet

First, we obtain the ResultSetMetaData object from the ResultSet, then we obtain a column count. We then print out all of the column names and follow by scrolling through the result set one row at a time. The column count helped us determine the number of iterations required in the second for loop.

Concurrency and Isolation Levels

Achieving desired concurrency goals in your applications requires considerable planning and thought. You must decide what isolation levels are suitable for your applications and also carefully define your transaction (discussed in Chapter 3). In JDBC, the Connection interface provides a setTransactionIsolation() method, which takes an integer constant defined in the Connection interface as an argument.

The possible values for this parameter can be referenced using the static constants defined in the Connection interface, however, it is important to note that the name for a particular isolation level in JDBC does not correspond to the isolation levels specified in DB2. JDBC's isolation levels directly correspond with those defined in ODBC, which are mapped to the appropriate DB2 isolation level in Table 12.3.

Relationships between Base JDBC Objects

We have now discussed the major classes and interfaces in the JDBC 2.1 Core API. The relationships between these classes and interfaces is illustrated in Figure 12.3 using the Unified Modeling Language (UML). The Driver Manager keeps a reference to all registered driver classes. Connections are obtained from driver classes by specifying a valid URL. Statement objects have three inherited forms, and are obtained from Connection objects, and in turn Statement objects can yield ResultSet objects. There are two types of metadata objects to describe the database and result set properties, and also two classes to handle error and warning processing.

Using DataSource Objects and Connection Pooling

Thus far, we have connected to DB2 using DriverManager.getConnection(). The JDBC 2.1 Standard Extension API introduces the DataSource interface, which provides an alternative means for connecting to databases. The DB2DataSource class implements this interface, and a single DataSource object is a factory for connections to a specific DB2 database. As a programmer, you never worry about whether you are using the app driver or the net driver when you use a DataSource object. The object itself, based on how you configure its properties, determines this. Table 12.4 summarizes the supported DataSource properties in the DB2 JDBC 2.1 drivers. Each property has a corresponding getter and setter method in the DB2DataSource class.

To obtain a connection to the represented data source, you call a getConnection() method, similar to what you called in the DriverManager class. However, there are a few important differences.

There is no URL to specify with a DataSource object; these details are configured using property setter methods. The user and password for connection can be set as properties as well. As a result, the getConnection() method in DataSource has an overloaded form that takes no password. Alternatively, you can use a unique userid and password for each connection by specifying these as parameters to getConnection().

JDBC 2.1 also provides additional DataSource interfaces for connection pooling because obtaining a database connection is a costly process. This way, a connection can be pooled when you attempt to close it. The DB2DataSource class implements connection pooling using the DB2ConnectionPoolDataSource and DB2PooledConnection objects, which are based on the ConnectionPoolDataSource and PooledConnection interfaces in the JDBC 2.1 Standard Extension API. Thus, when you call the close() method on the Connection object you have received, it doesn't actually close the connection. Here is a simple test you can try:

Connection con = null;
DataSource ds = null;

try {
   ds = new DB2DataSource();
   ds.setDatabaseName("db2mall");
   con = ds.getConnection();

  // Pause after obtaining connection. 
  // Do LIST APPLICATIONS here.
   BufferedReader in = new BufferedReader(
                           new InputStreamReader
                                       (System.in));
   in.readLine();

   // This won't actually close the connection
   con.close();

   // Pause again, after the connection has been closed. 
   // LIST APPLICATIONS shows that it is still there.
   in.readLine();
} catch (Exception e) { }

This example, obtains a connection and then closes it. Because we only specified the database name in the example, the DB2DataSource object knew it should use the app driver to establish the connection. To use the net driver, we would also need to use the setServerName() and setPortNumber() methods so that the DB2DataSource object could construct the equivalent of a valid net driver URL. Note that calling setPortNumber() is optional, just like in a net driver URL. If only setServerName() is called, then port 6789 will be assumed by default.

Returning to the example, we also created a BufferedReader object to prompt you at different stages so that you can issue a LIST APPLICTIONS command from the DB2 CLP. If you do this, you will notice that the same connection remains alive after the close() method has been called. This is because the connection has been returned to a pool, and is not disconnected. This allows the next connection against this database with the same userid and password, to reuse the pooled connection, saving time and resources. You will notice that pooled connections provide a large performance gain especially if your application frequently connects and disconnects.

You also can write your own DataSource classes that use DB2's connection pooling classes (DB2PooledConnection and DB2ConnectionPoolDataSource). In doing so, you can add functionality, such as idle timeout and a maximum pool size.

Java Naming and Directory Interface Support

The Java 2 SDK Enterprise Edition includes the javax.naming package, which defines the JNDI. JNDI allows you to create your own directories of DB2DataSource and DB2ConnectionPool objects within a JNDI Context.

The DB2 JDBC drivers include the DB2Context class, which you can use to store DataSource objects. When you create an instance of DB2Context, you can bind data sources by name. This class is a very basic implementation of the JNDI Context API, and it is not intended for production use. However, you might find it useful for development and testing.

When you call the close() method in the DB2Context object, the names you have registered within the context are stored along with their corresponding DataSource objects to a file called .db2.jndi in the directory specified in the user.home Java environment variable. All of your Java applications can access these DataSource objects after they are stored, requiring you to only set up their properties once.

Enterprise Java and Distributed Transactions

With the emergence of the Java 2 Enterprise Edition (J2EE) over the past few years, JDBC has taken a prominent role in communicating with relational database servers in enterprise applications. The foundations of J2EE technology are application servers that provide extensive built-in functionality such as naming directory services, database connection pooling, and distributed transaction support. Application servers typically perform distributed transaction management for you, by proxying all JDBC database operations through adapter DataSource and Connection objects provided by the application server.

To support distributed transactions, DB2 provides the DB2XADataSource class, which serves as a factory for DB2XAConnection objects. These two classes implement the XADataSource and XAConnection interfaces of the javax.sql package, respectively. Each XAConnection can participate in a single global transaction at a time. To participate in a transaction, the client application needs to enlist in a transaction using the DB2XAResource class, which implements the XAResource interface of the javax.transaction.xa package. A transaction is associated with a DB2XAResource by specifying an Xid, which identifies a transaction.

JDBC Static SQL

Because the DB2 v8 JDBC drivers use the DB2 CLI driver, you can take advantage of CLI's static-query execution feature. Accomplishing this task involves modifying your db2cli.ini file, and optionally providing a capture file that contains the SQL statements to statically bind. Details of this are in the previous chapter, which focuses on DB2 CLI.