Chapter 45
JDBC Explored

by Krishna Sankar

In the last chapter, you saw how JDBC has ushered in an era of simple yet powerful SQL database access for Java programs. You saw how JDBC works, and a couple of JDBC examples were presented. In this chapter, you explore more of JDBC, especially how JDBC handles SQL statements and the variety of ways we can process the ResultSet it returns.

Statements

The Statement object does all of the work to interact with the Database Management System in terms of SQL statements. You can create many Statement objects from one Connection object. Internally, the Statement object would be storing the various data needed to interact with a database, including state information, buffer handles, and so on. But these are transparent to the JDBC application program.

NOTE: When a program attempts an operation that is not in sync with the internal state of the system (for example, a next() method to get a row when no SQL statements have been executed) this discrepancy is caught and an exception is raised. This exception, normally, is probed by the application program using the methods in the SQLException object.

JDBC supports three types of statements:

Before you explore these different statements, see the steps that a SQL statement goes through.

A Java application program first builds the SQL statement in a string buffer and passes this buffer to the underlying DBMS through some API calls. A SQL statement needs to be verified syntactically, optimized, and converted to an executable form before execution. In the Call Level Interface (CLI) Application Program Interface (API) model, the application program through the driver passes the SQL statement to the underlying DBMS, which prepares and executes the SQL statement.

After the DBMS receives the SQL string buffer, it parses the statement and does a syntax check run. If the statement is not syntactically correct, the system returns an error condition to the driver, which generates a SQLException. If the statement is syntactically correct, depending on the DBMS, many query plans are usually generated that are run through an optimizer (often a cost-based optimizer). Then the optimum plan is translated into a binary execution plan. After the execution plan is prepared, the DBMS usually returns a handle or identifier to this optimized binary version of the SQL statement to the application program.

The three JDBC statement types (Statement, PreparedStatement, and CallableStatement) differ in the timing of the SQL statement preparation and the statement execution. In the case of the simple Statement object, the SQL is prepared and executed in one step (at least from the application program point of view. Internally, the driver might get the identifier, command the DBMS to execute the query, and then discard the handle). In the case of a PreparedStatement object, the driver stores the execution plan handle for later use. In the case of the CallableStatement object, the SQL statement is actually making a call to a stored procedure that is usually already optimized.

NOTE: As you know, stored procedures are encapsulated business rules or procedures that reside in the database server. They also enforce uniformity across applications, as well as provide security to the database access. Stored procedures last beyond the execution of the program, so the application program does not spend any time waiting for the DBMS to create the execution plan.

Now look at each type of statement more closely and see what each has to offer a Java program.

statement

A Statement object is created using the createStatement() method in the Connection object. Table 45.1 shows all methods available for the Statement object.
Table 45.1 Statement Object Methods
Return Type Method Name Parameter
ResultSet executeQuery (String sql)
int executeUpdate (String sql)
boolean execute (String sql)
boolean getMoreResults ()
void close ()
int getMaxFieldSize ()
void setMaxFieldSize (int max)
int getMaxRows ()
void setMaxRows (int max)
void setEscapeProcessing (boolean enable)
int getQueryTimeout ()
void setQueryTimeout (int seconds)
void cancel ()
java.sql.SQLWarning getWarnings ()
void clearWarnings ()
void setCursorName (String name)
ResultSet getResultSet ()
int getUpdateCount ()
The most important methods are executeQuery(), executeUpdate(), and execute(). As you create a Statement object with a SQL statement, the executeQuery() method takes a SQL string. It passes the SQL string to the underlying data source through the driver manager and gets the ResultSet back to the application program. The executeQuery() method returns only one ResultSet. For those cases that return more than one ResultSet, the execute() method should be used.

CAUTION:
Only one ResultSet can be opened per Statement object at one time.

For SQL statements that do not return a ResultSet like the UPDATE, DELETE, and DDL statements, the Statement object has the executeUpdate() method that takes a SQL string and returns an integer. This integer indicates the number of rows that are affected by the SQL statement.

NOTE: The JDBC processing is synchronous; that is, the application program must wait for the SQL statements to complete. But because Java is a multithreaded platform, the JDBC designers suggest using threads to simulate asynchronous processing.

The Statement object is best suited for ad hoc SQL statements or SQL statements that are executed once. The DBMS goes through the syntax run, query plan optimization, and the execution plan generation stages as soon as this SQL statement is received. The DBMS executes the query and then discards the optimized execution plan, so, if the executeQuery() method is called again, the DBMS goes through all of the steps again.

The following example program shows how to use the Statement class to access a database.

See "Anatomy of a JDBC Application," Chapter 44

In this example, you will list all of the subjects (classes) available in the enrollment database and their location, Day, and times. The SQL statement for this is:

SELECT ClassName, Location, DaysAndTimes FROM Classes

You create a Statement object and pass the SQL string during the executeQuery() method call to get this data.

//Declare a method and some variables.
public void ListClasses() throws SQLException {
   int i, NoOfColumns;
   String ClassName,ClassLocation, ClassSchedule;
   //Initialize and load the JDBC-ODBC driver.
   Class.forName ("jdbc.odbc.JdbcOdbcDriver");
   //Make the connection object.
   Connection Ex1Con = DriverManager.getConnection( "jdbc:odbc:StudentDB;uid="admin";pw="sa");
   //Create a simple Statement object.
   Statement Ex1Stmt = Ex1Con.createStatement();
   //Make a SQL string, pass it to the DBMS, and execute the SQL statement.
   ResultSet Ex1rs = Ex1Stmt.executeQuery(
      "SELECT ClassName, Location, DaysAndTimes FROM Classes");
   //Process each row until there are no more rows.
   // And display the results on the console.
   System.out.println("Class         Location      Schedule");
   while (Ex1rs.next()) {
      // Get the column values into Java variables
      ClassName = Ex1rs.getString(1);
      ClassLocation = Ex1rs.getString(2);
      ClassSchedule = Ex1rs.getString(3);
      System.out.println(ClassName,ClassLocation,ClassSchedule);
      }
   }

As you can see, the program is very straightforward. You do the initial connection and create a Statement object. You pass the SQL along with the method executeQuery() call. The driver passes the SQL string to the DBMS which performs the query and returns the results. After the statement is finished, the optimized execution plan is lost.

PreparedStatement

In the case of a PreparedStatement object, as the name implies, the application program prepares a SQL statement using the java.sql.Connection.prepareStatement() method. The PreparedStatement() method takes a SQL string, which is passed to the underlying DBMS. The DBMS goes through the syntax run, query plan optimization, and the execution plan generation stages, but does not execute the SQL statement. Possibly, it returns a handle to the optimized execution plan that the JDBC driver stores internally in the PreparedStatement object.

The methods of the PreparedStatement object are shown in Table 45.2. Notice that the executeQuery(), executeUpdate(), and execute() methods do not take any parameters. They are just calls to the underlying DBMS to perform the already-optimized SQL statement.
Table 45.2 PreparedStatement Object Methods
Return Type Method Name Parameter
ResultSet executeQuery ()
int executeUpdate ()
boolean execute ()
One of the major features of a PreparedStatement is that it can handle IN types of parameters. The parameters are indicated in a SQL statement by placing the "?" as the parameter marker instead of the actual values. In the Java program, the association is made to the parameters with the setXXXX() methods, as shown in Table 45.3. All of the setXXXX() methods take the parameter index, which is 1 for the first "?," 2 for the second "?," and so on.

Table 45.3 java.sql.PreparedStatement--Parameter-Related Methods
Return Type Method Name Parameter
void clearParameters ()
void setAsciiStream (int parameterIndex, java.io.InputStream x, int length)
void setBinaryStream (int parameterIndex, java.io.InputStream x, int length)
void setBoolean (int parameterIndex, boolean x)
void setByte (int parameterIndex, byte x)
void 1setBytes (int parameterIndex, byte x[])
void setDate (int parameterIndex, java.sql.Date x)
void setDouble (int parameterIndex, double x)
void setFloat (int parameterIndex, float x)
void setInt (int parameterIndex, int x)
void setLong (int parameterIndex, long x)
void setNull (int parameterIndex, int sqlType)
void setBignum (int parameterIndex, Bignum x)
void setShort (int parameterIndex, short x)
void setString (int parameterIndex, String x)
void setTime (int parameterIndex, java.sql.Time x)
void setTimestamp (int parameterIndex, java.sql.Timestamp x)
void setUnicodeStream (int parameterIndex, java.io.InputStream x, int length)
Advanced Features--Object Manipulation
void setObject (int parameterIndex, Object x, int targetSqlType, int scale)
void setObject (int parameterIndex, Object x, int targetSqlType)
void setObject (int parameterIndex, Object x)
In the case of the PreparedStatement, the driver actually sends only the execution plan ID and the parameters to the DBMS. This results in less network traffic and is well-suited for Java applications on the Internet. The PreparedStatement should be used when you need to execute the SQL statement many times in a Java application. But remember, even though the optimized execution plan is available during the execution of a Java program, the DBMS discards the execution plan at the end of the program. So, the DBMS must go through all of the steps of creating an execution plan every time the program runs. The PreparedStatement object achieves faster SQL execution performance than the simple Statement object, as the DBMS does not have to run through the steps of creating the execution plan.

The following example program shows how to use the PreparedStatement class to access a database. The database schema is shown in Chapter 44. In this example, you optimize the example you developed in the Statement example.

See "Anatomy of a JDBC Application," Chapter 44

The simple Statement example can be improved in a few major ways. First, the DBMS goes through building the execution plan every time, so you make it a PreparedStatement. Secondly, the query lists all courses which could scroll away. You improve this situation by building a parameterized query as follows:

//Declare class variables
Connection Con;
PreparedStatement PrepStmt;
boolean Initialized = false;
private void InitConnection() throws SQLException {
   //Initialize and load the JDBC-ODBC driver.
   Class.forName ("jdbc.odbc.JdbcOdbcDriver");
   //Make the connection object.
   Con = DriverManager.getConnection( "jdbc:odbc:StudentDB;uid="admin";pw="sa");
   //Create a prepared Statement object.
   PrepStmt = Ex1Con.prepareStatement(
    "SELECT ClassName, Location, DaysAndTimes FROM Classes WHERE ClassName = ?");
   Initialized = True;
   }
public void ListOneClass(String ListClassName) throws SQLException {
   int i, NoOfColumns;
   String ClassName,ClassLocation, ClassSchedule;
   if (! Initialized) {
      InitConnection();
      }
   // Set the SQL parameter to the one passed into this method
   PrepStmt.setString(1,ListClassName);
   ResultSet Ex1rs = PrepStmt.executeQuery()
   //Process each row until there are no more rows and 
   // display the results on the console.
   System.out.println("Class         Location      Schedule");
   while (Ex1rs.next()) {
      // Get the column values into Java variables
      ClassName = Ex1rs.getString(1);
      ClassLocation = Ex1rs.getString(2);
      ClassSchedule = Ex1rs.getString(3);
      System.out.println(ClassName,ClassLocation,ClassSchedule);
      }
   }

Now, if a student wants to check the details of one subject interactively, this example program can be used. You can save execution time and network traffic from the second invocation onwards because you are using the PreparedStatement object.

CallableStatement

For a secure, consistent, and manageable multi-tier client/server system, the data access should allow the use of stored procedures. Stored procedures centralize the business logic in terms of manageability and also in terms of running the query. Java applets running on clients with limited resources cannot be expected to run huge queries. But the results are important to those clients. JDBC allows the use of stored procedures by the CallableStatement class and with the escape clause string.

A CallableStatement object is created by the prepareCall() method in the Connection object. The prepareCall() method takes a string as the parameter. This string, called an escape clause, is of the form

{[? =] call <stored procedure name> [<parameter>,<parameter> ...]}

The CallableStatement class supports parameters. These parameters are of the OUT kind from a stored procedure or the IN kind to pass values into a stored procedure. The parameter marker (question mark) must be used for the return value (if any) and any output arguments because the parameter marker is bound to a program variable in the stored procedure. Input arguments can be either literals or parameters. For a dynamic parameterized statement, the escape clause string takes the form

{[? =] call <stored procedure name> [<?>,<?> ...]}

The OUT parameters should be registered using the registerOutparameter() method--as shown in Table 45.4--before the call to the executeQuery(), executeUpdate(), or execute() methods.
Table 45.4 CallableStatement--OUT Parameter Register Methods
Return Type Method Name Parameter
void registerOutParameter (int parameterIndex, int sqlType)
void registerOutParameter (int parameterIndex, int sqlType, int scale)
After the stored procedure is executed, the DBMS returns the result value to the JDBC driver. This return value is accessed by the Java program using the methods in Table 45.5.
Table 45.5 CallableStatement Parameter Access Methods
Return Type Method Name Parameter
boolean getBoolean (int parameterIndex)
byte getByte (int parameterIndex)
byte[] getBytes (int parameterIndex)
java.sql.Date getDate (int parameterIndex)
double getDouble (int parameterIndex)
float getFloat (int parameterIndex)
int getInt (int parameterIndex)
long getLong (int parameterIndex)
java.lang.Bignum getBignum (int parameterIndex, int scale)
Object getObject (int parameterIndex)
short getShort (int parameterIndex)
String getString (int parameterIndex)
java.sql.Time getTime (int parameterIndex)
java.sql.Timestamp getTimestamp (int parameterIndex)
Miscellaneous Functions
boolean wasNull ()
If a student wants to find out the grades for a subject, in the database schema shown inChapter 44, you need to do many operations on various tables, such as find all assignments for the student, match them with class name, calculate grade points, and so on. This is a business logic well-suited for a stored procedure. In this example, you give the stored procedure a student ID and class ID, and it returns the grade. Your client program becomes simple, and all the processing is done at the server. This is where you will use a CallableStatement.

The stored procedure call is of the form:

studentGrade = getStudentGrade(StudentID,ClassID)

In the JDBC call, you create a CallableStatement object with the "?" symbol as a placeholder for parameters, and then connect Java variables to the parameters as shown in the following example:

public void DisplayGrade(String StudentID, String ClassID) throws SQLException {
   int Grade;
   //Initialize and load the JDBC-ODBC driver.
   Class.forName ("jdbc.odbc.JdbcOdbcDriver");
   //Make the connection object.
   Connection Con = DriverManager.getConnection( "jdbc:odbc:StudentDB;uid="admin";pw="sa");
   //Create a Callable Statement object.
   CallableStatement CStmt = Con.prepareCall({?=call getStudentGrade[?,?]});
   // Now tie the placeholders with actual parameters.
   // Register the return value from the stored procedure
   // as an integer type so that the driver knows how to handle it.
   // Note the type is defined in the java.sql.Types.
   CStmt.registerOutParameter(1,java.sql.Types.INTEGER);
   // Set the In parameters (which are inherited from the PreparedStatement class)
   CStmt.setString(1,StudentID);
   CStmt.setString(2,ClassID);
   // Now we are ready to call the stored procedure
   int RetVal = CStmt.executeUpdate();
   // Get the OUT parameter from the registered parameter
   // Note that we get the result from the CallableStatement object
   Grade = CStmt.getInt(1);
   // And display the results on the console.
   System.out.println(" The Grade is : ");
   System.out.println(Grade);
   }

As you can see, JDBC has minimized the complexities of getting results from a stored procedure. It still is a little involved, but is simpler. Maybe in the future these steps will become even more simple.

Now that you have seen how to communicate with the underlying DBMS with SQL, see what you need to do to process the results sent back from the database as a result of the SQL statements.

ResultSet Processing: Retrieving Results

The ResultSet object is actually a tubular data set; that is, it consists of rows of data organized in uniform columns. In JDBC, the Java program can see only one row of data at one time. The program uses the next() method to go to the next row. JDBC does not provide any methods to move backwards along the ResultSet or to remember the row positions (called bookmarks in ODBC). Once the program has a row, it can use the positional index (1 for the first column, 2 for the second column, and so on) or the column name to get the field value by using the getXXXX() methods. Table 45.6 shows the methods associated with the ResultSet object.
Table 45.6 java.sql.ResultSet Methods
Return Type Method Name Parameter
boolean next ()
void close ()
boolean wasNull ()
Get Data By Column Position
java.io.InputStream getAsciiStream (int columnIndex)
java.io.InputStream getBinaryStream (int columnIndex)
boolean getBoolean (int columnIndex)
byte getByte (int columnIndex)
byte[] getBytes (int columnIndex)
java.sql.Date getDate (int columnIndex)
double getDouble (int columnIndex)
float getFloat (int columnIndex)
int getInt (int columnIndex)
long getLong (int columnIndex)
java.lang.Bignum getBignum (int columnIndex, int scale)
Object getObject (int columnIndex)
short getShort (int columnIndex)
String getString (int columnIndex)
java.sql.Time getTime (int columnIndex)
java.sql.Timestamp getTimestamp (int columnIndex)
java.io.InputStream getUnicodeStream (int columnIndex)
Get Data By Column Name
java.io.InputStream getAsciiStream (String columnName)
java.io.InputStream getBinaryStream (String columnName)
boolean getBoolean (String columnName)
byte getByte (String columnName)
byte[] getBytes (String columnName)
java.sql.Date getDate (String columnName)
double getDouble (String columnName)
float getFloat (String columnName)
int getInt (String columnName)
long getLong (String columnName)
java.lang.Bignum getBignum (String columnName, int scale)
Object getObject (String columnName)
short getShort (String columnName)
String getString (String columnName)
java.sql.Time getTime (String columnName)
java.sql.Timestamp getTimestamp (String columnName)
java.io.InputStream getUnicodeStream (String columnName)
int findColumn (String columnName)
SQLWarning getWarnings ()
void clearWarnings ()
String getCursorName ()
ResultSetMetaData getMetaData ()
As you can see, the ResultSet methods--even though there are many--are very simple. The major ones are the getXXX() methods. The getMetaData() method returns the meta data information about a ResultSet. The DatabaseMetaData also returns the results in the ResultSet form. The ResultSet also has methods for the silent SQLWarnings. It is a good practice to check any warnings using the getWarning() method that returns a null if there are no warnings.

Other JDBC Classes

Now that you have seen all of the main database-related classes, look at some of the supporting classes that are available in JDBC. These classes include the Date, Time, TimeStamp, and so on. Most of these classes extend the basic Java classes to add capability to handle and translate data types that are specific to SQL.

java.sql.Date

This package (see Table 45.7) gives a Java program the capability to handle SQL DATE information with only year, month, and day values.
Table 45.7 java.sql.Date Methods
Return Type Method Name Parameter
Date Date (int year, int month, int day)
Date Date (long date)
Date valueOf (String s)
String toString ()
int getHours ()
int getMinutes ()
int getSeconds ()
void setHours (int Hr)
void setMinutes (int Min)
void setSeconds (int Sec)
void setTime (long date)

java.sql.Time

As seen in Table 45.8, the java.sql.Time adds the Time object to the java.util.Date package to handle only hours, minutes, and seconds. java.sql.Time is also used to represent SQL TIME information.
Table 45.8 java.sql.Time Methods
Return Type Method Name Parameter
Time Time (int hour, int minute, int second)
Time Time (long time)
Time Time valueOf(String s)
String toString ()
int getDate ()
int getDay ()
int getMonth ()
int getYear ()
void setDate (int date)
void setMonth (int month)
void setTime (int time)
void setYear (int year)

java.sql.Timestamp

The java.sql.Timestamp package adds the TimeStamp class to the java.util.Date package (see Table 45.9). It adds the capability of handling nanoseconds. But the granularity of the subsecond timestamp depends on the database field as well as the operating system.
Table 45.9 java.sql.Timestamp Methods
Return Type Method Name Parameter
TimeStamp TimeStamp (int year, int month, int date, int hour, int minute, int second, int nano)
TimeStamp TimeStamp (long time)
TimeStamp valueOf (String s)
String toString ()
int getNanos ()
void setNanos (int n)
boolean after (TimeStamp ts)
boolean before (TimeStamp ts)
boolean equals (TimeStamp ts)

java.sql.Types

This class defines a set of XOPEN equivalent integer constants that identify SQL types. The constants are final types. Therefore, they cannot be redefined in applications or applets. Table 45.10 lists the constant names and their values.
Table 45.10 java.sql.Types Constants
Constant Name Value
BIGINT -5
BINARY -2
BIT -7
CHAR 1
DATE 91
DECIMAL 3
DOUBLE 8
FLOAT 6
INTEGER 4
LONGVARBINARY -4
LONGVARCHAR -1
NULL 0
NUMERIC 2
OTHER 1111
REAL 7
SMALLINT 5
TIME 92
TIMESTAMP 93
TINYINT -6
VARBINARY -3
VARCHAR 12

java.sql.DataTruncation

This class provides methods for getting details when a DataTruncation warning or exception is thrown by a SQL statement. The data truncation could happen to a column value or parameter.

The main elements of a DataTruncation object are:

The DataTruncation object also consists of a datasize element that has the actual size (in bytes) of the truncated value and the transfer size, which is the number of bytes actually transferred.

The various methods, as listed in Table 45.11, let the Java program retrieve the values of these elements. For example, the getRead() method returns true if data truncation occurred during a read and a false if the truncation occurred during a write.
Table 45.11 java.sql.DataTruncation Methods
Return Type Method Name Parameter
int getDataSize ()
int getIndex ()
boolean getParameter ()
boolean getRead ()
int getTransferSize ()

JDBC in Perspective

JDBC is an important step in the right direction to elevate the Java language to the Java platform. The Java APIs--including the Enterprise APIs (JDBC, RMI, Serialization, and IDL), Security APIs, and the Server APIs--are the essential ingredients for developing enterprise-level, distributed, multi-tier client/server applications.

The JDBC specification life cycle happened in the speed of the Net--one Net year is widely clocked as equaling seven normal years. The JDBC specification is fixed, so the developers and driver vendors are not chasing a moving target.

JDBC Compliant
Javasoft has instituted the JDBC Compliant certification for drivers. A particular driver will be called JDBC Compliant if it passes JDBC compliance tests developed by Javasoft and Intersolv. At present, a driver should support at least ANSI SQL92 Entry Level to pass the compliance tests.

The JDBC Compliant certification is very useful for developers as they can confidently develop applications using JDBC and can be assured database access (in client machines) with JDBC Compliant drivers.

NOTE: The JDBC released with JDK 1.1 (JDBC 1.2x) is not compatible with JDBC 1.10. Drivers written for JDBC 1.10 will not work with JDBC 1.2x The main difference is that in JDBC 1.10, the numeric SQL data type is mapped to java.lang.Bignum. In JDBC 1.2x, the java.lang.Bignum class is replaced by the java.math.BigDecimal and java.math.BigInteger classes. If you are using JDBC 1.2x, you should use the java.math.BigDecimal and java.math.BigInteger methods instead of the java.lang.Bignum methods.

Another factor in favor of JDBC is its similarity to ODBC. JavaSoft made the right decision to follow ODBC philosophy and abstractions, thus making it easy for ISVs and users to leverage their ODBC experience and existing ODBC drivers. In the JDBC specification, this goal is described as "JDBC must be implementable on top of common database interfaces."

By making JDBC a part of the Java language, you received all of the advantages of the Java language concepts for database access. Also, as all implementers have to support the Java APIs, JDBC has become a universal standard. This philosophy, stated in the JDBC specification as "provide a Java interface that is consistent with the rest of the Java system," makes JDBC an ideal candidate for use in Java-based database development.

Another good design philosophy is the driver independence of the JDBC. The underlying database drivers can either be native libraries--such as a DLL for the Windows system or Java routines connecting to listeners. The full Java implementation of JDBC is suitable for a variety of Network and other Java OS computers, thus making JDBC a versatile set of APIs.

NOTE: In my humble opinion, the most important advantage of JDBC is its simplicity and versatility. The goal of the designers was to keep the API and common cases simple and "support the weird stuff in separate interfaces." Also, they wanted to use multiple methods for multiple functionality. They have achieved their goals even in this first version. For example, the Statement object has the executeQuery() method for SQL statements returning rows of data, and the executeUpdate() method for statements without data to return. Also, uncommon cases, such as statements returning multiple ResultSets, have a separate method: execute().

As more applications are developed with JDBC and as the Java platform matures, more and more features will be added to JDBC. One of the required features, especially for client/server processing, is a more versatile cursor. The current design leaves the cursor management details to the driver. I would prefer more application-level control for scrollable cursors, positioned update/delete capability, and so on. Another related feature is the bookmark feature, which is especially useful in a distributed processing environment such as the Internet.