1187-1189

Previous Table of Contents Next

Page 1187

 // txtURL is a java.lang.TextField that accepts a // database url provided by the user. // theDriver is a valid driver reference int i = 0; String propName = ""; boolean isRequired = false; DriverPropertyInfo props[] = theDriver.getPropertyInfo(txtURL.getText(),  new java.util.Properties()); for (i = 0; ;i++) {     try {         propName = props[i].name;         isRequired = props[i].required;         // do something to display and accept input for required properties     }     catch (ArrayIndexOutOfBoundsException e) {         break;     } } 

You can use the information gathered using the DriverPropertyInfo objects to populate a grid of property names and associated values as input by the user. This allows an application to provide a driver and RDBMS-independent connect dialog.

Using DatabaseMetaData

The DatabaseMetaData interface is invaluable to applications that must support multiple database engines or multiple versions of the same RDBMS. You can use it to determine the name and version of the driver, the specific RDBMS and version, the SQL grammar supported, the transaction isolation level, levels of procedural support, and so on. The DatabaseMetaData interface is returned by the getMetaData() method of the Connection interface. This code segment illustrates one possible use of this interface:

 // DbConn references a valid java.sql.Connection java.SQL.DatabaseMetadata metadata = DbConn.getMetadata(); String DriverName = metadata.getDriverName(); String DriverVer = metadata.getDriverVersion(); String DBMSname = metadata.getDatabaseProductName(); String DBMSver = metadata.getDatabaseProductVersion(); 

An application can use the DBMS vendor and DBMS version to dynamically generate SQL appropriate to the specific RDBMS. Outer joins, for example, vary greatly from one RDBMS to another. JDBC provides escape sequences to handle many of these inconsistencies, but unfortunately , drivers are not consistent in their support for escape sequences. An example of JDBC escape syntax is provided in the section on accessing procedures and functions. In some cases, it might be necessary to use the escape syntax, whereas in others, it might be more reliable to generate DBMS-specific or driver-specific SQL within the application. This choice depends largely on the number of DBMS products and versions that must be supported (if this is even known in advance).

This point might seem trivial, but the impact of differing SQL grammar and driver implementations can be significant if not accounted for in the design of an application. Many of the methods in the DatabaseMetaData interface would be unnecessary if this were not the case.

Page 1188

Retrieving Results

The JDBC ResultSet interface, as the name implies, is designed for the sole purpose of retrieving results. However, you cannot access a result set directly from the Connection interface. Unlike ODBC, which employs an all-purpose statement handle, JDBC specializes the Statement interface to better encapsulate the differences between result sets, DML statements, and procedural extensions.

The JDBC Statement is the base interface for interacting with the database. It is extended by the CallableStatement and the PreparedStatement interfaces. You can obtain a ResultSet through the Statement interface or through the PreparedStatement interface. (The CallableStatement is used primarily for procedural extensions and is discussed in the following sections.)

You should use the Statement interface to create a ResultSet only when the SQL query is static. For parameterized SQL queries or queries that are executed frequently, use the PreparedStatement interface. When a PreparedStatement is used, you can precompile the SQL and bind the access plan in the database. The PreparedStatement interface contains methods to set parameter values prior to execution. This allows the precompiled SQL to be executed repeatedly with different parameter values, which can provide considerable performance gains, particularly for complex queries.

To illustrate these different methods, assume that an application is used to display employee data by Social Security number. This can be accomplished using either the Statement or PreparedStatement interface. The following code segment demonstrates direct execution using the Statement interface:

 // DbConn references a valid java.sql.Connection // txtSSN references a java.awt.TextField that accepts // a Social Security number as user input String SQL = "SELECT last, first, salary FROM payroll WHERE ssn = "     + txtSSN.getText(); java.sql.Statement stmt = DbConn.createStatement(); java.sql.ResultSet rs = stmt.executeQuery(SQL); 

The same query can be parameterized and executed as follows :

 // DbConn references a valid java.sql.Connection // txtSSN references a java.awt.TextField that accepts // a Social Security number as user input String SQL = "SELECT last, first, salary FROM payroll WHERE ssn = ?"; java.sql.PreparedStatement pstmt = DbConn.prepareStatement(SQL); pstmt.setLong(1, java.lang.Long.parseLong(txtSSN.getText())); java.sql.ResultSet rs = pstmt.executeQuery (); 

Using the PreparedStatement interface lets you re-execute this query with a new parameter any number of times, creating a new ResultSet:

 pstmt.setLong(1, java.lang.Long.parseLong(txtSSN.getText())); java.sql.ResultSet rs = pstmt.executeQuery (); 

The setLong() method is one of several methods of PreparedStatement used to set parameter values. Each of these methods accepts a one-based parameter index and the parameter value. For example, setString() accepts the parameter index and a java.lang.String.

Page 1189

Unfortunately, not all drivers support all JDBC datatypes, and not all drivers handle prepared execution in the same way. Some drivers might delay parsing and parameter setting until the query is executed. This can improve performance, but it might also confuse exception handling by delaying the throwing of certain exceptions. For example, a SQLException might be thrown when one of the set() methods is called with a datatype that is incompatible with the corresponding database column. If parsing and parameter setting is delayed, the same exception is not thrown until the executeQuery() method is called.

Regardless of whether you use direct execution (using Statement) or prepared execution (using PreparedStatement), the ResultSet returned is the same. A row is accessed using the next() method of the ResultSet interface. This method returns a boolean value, which is true until all rows are read. It might also throw a SQLException if an error occurs.

After the cursor is positioned on a row, column values are retrieved using one of the get() methods of the ResultSet interface (getString(), getInt(), and so on). Each of these methods accepts either the one-based index of the column to retrieve or the column's name. The following code segment extends the query example presented in the previous segments:

 String LastName = ""; String FirstName = ""; float Salary = 0; while (rs.next()) {     LastName = rs.getString(1);     FirstName = rs.getString(2);     Salary = rs.getFloat("salary");     // do something to display the values for this row } rs.close(); 

It is a good practice to close the result set as soon as it is no longer needed. The close() method also applies to Statement and PreparedStatement interfaces. However, it might be desirable to leave Statement interfaces open for future executions because closing them frees all resources associated with them. If a Statement reference is invalidated, the close() method is invoked automatically when garbage is collected. Calling close() prior to invalidating the reference ensures that external resources are freed immediately.

Using ResultSetMetaData

You use the ResultSetMetaData interface to obtain information about a result set and its columns at runtime. This interface can be a powerful tool when used to develop reusable display components , where the nature of the result set is unknown at design time. You can obtain a ResultSetMetaData interface from any valid ResultSet reference:

 // rs references a valid java.sql.ResultSet java.sql.ResultSetMetaData rsinfo = rs.getMetadata(); 

The ResultSetMetaData interface provides methods to determine the number of columns, column names, datatypes, display widths, and so on. You can use the following code

Previous Table of Contents Next