- Special Edition Using Java, 2nd Edition -

Chapter 45

JDBC Explored

by Krishna Sankar

In the last chapter, you saw how JDBC has ushered in an era of simple yet powerful 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.

• The Statement classes
• Statements—specifically SQL statements—are the vehicles by which the application program interacts with the underlying DBMS. JDBC provides three types of Statement objects to handle SQL interaction: Statement, PreparedStatement, and CallableStatement.
• The ResultSet class
• Most of the time, SQL statements return rows and rows of data. The ResultSet object provides methods to handle this data from a Java program.
• Supporting classes
• JDBC provides classes like Date, Time, TimeStamp, DataTruncation and more to handle various types of situations during the execution of a Java program using the JDBC API.
• The present and future of JDBC
• This last section wraps up the trilogy of JDBC chapters with a few observations.

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.

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:

• Statement
• PreparedStatement
• CallableStatement

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 call. 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, then many query plans usually are 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.

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

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 ere more than one ResultSet, the execute() method should be used.

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.

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 for more information.

 

In this example, you will list all of the subjects (classes) available in the enrollment database and their location and 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 straight forward. 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

One of the major feature 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.

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 for more information.

 

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 parametrized 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

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

If a student wants to find out the grades for a subject, in the database schema shown in Chapter 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 is call is of the form

studentGrade = getStudentGrade(StudentID,ClassID)

In the JDBC call, you create a CallableStatement object with the “?” symbol as placeholders 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. May be in the future, these steps will become more simpler.

Now that you have seen how to communicate with the underlying DBMS with SQL, let’s 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

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, let us look at some of the supporting classes that are available in JDBC. These classes include the Date, Time, TimeStamp, Numeric, 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 gives a Java program the capability to handle SQL DATE information with only year, month, and day values. This package contrasts with the java.util.Date, where the time in hours, minutes, and seconds is also kept.

Table 45.7 java.sql.Date Methods

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

java.sql.Timestamp

The java.sql.Timestamp package adds the TimeStamp class to the java.util.Date package. 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

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

java.sql.Numeric

The java.sql.Numeric class adds the capability of representing SQL fixed-point NUMERIC and DECIMAL values to the java.lang.Number class. The Numeric objects are defined in terms of four factors:

• Precision
• Scale
• Sign
• Value

The precision is the total number of digits in a Numeric object. The Scale is the number of digits to the right of the decimal point. The Numeric class is a final class and, therefore, cannot be redefined. This class provides complete arithmetic operations as well as equality comparisons. Conversion methods to the basic types are also provided. The following tables list the methods for the Numeric class.

Table 45.11 java.sql.Numeric Methods

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:

• Index. Gives the column or parameter number.
• parameter flag. True if the truncation is on a parameter and false if the truncation is on a column.
• read flag. True if the truncation is during a read and false if the truncation is on a write.

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.12 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.12 java.sql.DataTruncation Methods

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 version 1.0 JDBC specification is fixed, so the developers and driver vendors are not chasing a moving target.

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.

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 useful especially in a distributed processing environment such as the Internet.

| Previous Chapter | Next Chapter |

|Table of Contents | Book Home Page |

| Que Home Page | Digital Bookshelf | Disclaimer |

To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select Talk to Us.

© 1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company