JDBC Subtleties

Since we'll be working with JDBC in the sample application, let's consider the major challenges of using JDBC in practice, and examine some subtle points of the JDBC API that are often neglected. This is an important topic; in my experience, J2EE applications often contain sloppy JDBC code, which can cause major problems.

Correct Exception Handling

The main challenge in writing robust JDBC code is ensuring correct exception handling. Most JDBC API methods can throw the checked java.sql.SQLException. Not only must we ensure that we catch this exception, we must ensure that cleanup occurs even if an exception is thrown.

The following code listing, which performs a trivial SELECT, shows a common JDBC error, with serious consequences:

    public String getPassword(String forename) throws ApplicationException {      String sql = "SELECT password FROM customer WHERE forename= ' " +                     forename + " ' ";      String password = null;      Connection con = null;      Statement s = null;      ResultSet rs = null;      try {        con = <get connection from DataSource>;        s = con.createStatement();        rs = s.executeQuery (sql);        while (rs.next()) {          password = rs.getString(1);        }        rs. close();        s. close();        con. close();                                                                                                              } catch (SQLException ex) {        throw new ApplicationException ("Couldn't run query [" + sql + "]", ex);      }      return password;    } 

Code like this can crash a whole application, and even bring down a database. The problem is that each of the highlighted lines can throw a SQLException. If this happens, we will fail to close the Connection, as the connection is closed only if we reach the last line of the try block. The result will be a "leaked" connection, stolen from the application server's connection pool. Although the server may eventually detect the problem and release the stolen connection, the impact on other users of the connection pool may be severe. Each connection costs the RDBMS resources to maintain, and pooled connections are a valuable resource. Eventually all connections in the pool may become unavailable, bringing the application server to a halt, and the application server may be unable to increase the size of the pool because the database can support no more concurrent connections.

The following, correct, version of this method adds a finally block (highlighted) to ensure that the connection is always closed. Note that in this version, as in the first version, we need to throw an application-specific exception (ApplicationException) wrapping any SQLExceptions encountered:

    public String getPassword (String forename) throws ApplicationException {      String sql = "SELECT password FROM customer WHERE forename= ' " +                     forename + " ' ";      String password = null;      Connection con = null;      Statement s = null;      ResultSet rs = null;      try {        con = <get connection from DataSource>;        s = con.createStatement();        rs = s.executeQuery(sql);        while (rs.next()) {          password = rs.getString(1);        }        rs. close();        s. close();      }      catch (SQLException ex) {        throw new ApplicationException ("Couldn't run query [" + sql + "]", ex);      }      finally {        try {          if (con != null) {            con. close();          }        }        catch (SQLException ex) {          throw new ApplicationException ("Failed to close connection", ex);        }      }                                                                                                                            return password;    } 

This is much more robust, but it's also much more verbose. It's absurd to have to write 39 lines of code to accomplish just about the simplest possible JDBC operation. It's clear that we need a better solution than using the JDBC API directly.

Important 

When using JDBC directly, it's essential to use a finally block to ensure that the connection is closed, even in the event of an exception.

Extracting Information from SQLExceptions

Another important facet of JDBC exception handling is extracting the maximum possible information from SQLExceptions. Unfortunately, JDBC makes this rather cumbersome.

java.sql.SQLExceptions can be chained. The getNextException() method on SQLException returns the next SQLException associated with the failure, (or null, if there is no further information. Whether or not checking for further exceptions is necessary seems to depend on the RDBMS and driver (for example, I've found it very useful in Cloudscape 3.6, but generally unnecessary in Oracle).

Although JDBC uses the one exception class (SQLException) for almost all problems, it is possible to extract useful details from a SQLException. SQLExceptions also include two potentially useful error codes: the vendor code and the SQLState code.

The vendor code is an int returned by the getErrorCode() method. As the name implies, the vendor code may differ between vendors, even for common error conditions such as deadlocks. Thus, code that relies on it won't be portable. For example, in the case of Oracle, the vendor code will be the numeric code that Oracle reports in all its error messages (not just to JDBC), such as "ORA-00060: deadlock detected while waiting for resource". Occasionally this non-portable functionality is vital, as it's otherwise impossible to ascertain exactly what went wrong with a SQL statement.

We can even use an error code ourselves for custom application exceptions. Consider an Oracle stored procedure that includes the following line:

    raise_application_error (-20100, 'My special error condition' ); 

This error code will appear to JDBC like a standard error code: the message with the SQLException will be ORA-20100: My special error condition and the vendor code will be 20100.

The SQLException.getSQLState() method returns a 5-character string that is at least theoretically a portable error code. The first two characters contain high-level information in a class code; the next three more specific (often vendor-specific) information. The SQLState codes aren't documented in the JDBC API. The only free online documentation I have been able to find is at http://developer.mimer.se/documentation/html_91/Mimer_SQL_Mobile_DocSet_App_return_status2.html#1110406.

Unfortunately, the SQLState codes are not guaranteed to be supported by all databases, and may not carry enough information to establish the nature of a problem. Sometimes the vendor code is the only way to obtain the detail we need. Most databases have many more vendor codes than SQLState codes, and better documentation describing them. Sometimes there is no standard SQLState code to represent a problem, such as Oracle's 8177 ("can't serialize access for this transaction"), which may carry crucial information to a J2EE application but which is unique to the implementation of a particular RDBMS.

We must understand the java.sql.SQLWarning exception as well as SQLExceptions. Like SQLException, this is a checked exception (it's actually a subclass of SQLException), but it's never actually thrown by the JDBC API. Non-fatal SQL errors, such as data truncation on reading, can be reported as SQL warnings, which are attached to ResultSets, Statements, or Connections after the execution of offending statements. Each of these JDBC API objects has a getWarnings() method that returns null if there are no warnings, or the first in a chain of SQLWarnings analogous to a chain of SQLExceptions if warnings were raised. Warnings are reset when a new operation is undertaken.

Warnings are another reason that using JDBC directly isn't a viable option. In a framework implemented to simplify JDBC, it's easy to throw an exception on warnings if desired, or save warnings for future reference. In code that uses the JDBC API directly, the process of checking for warnings adds still further code bloat; it's simply too much trouble to do in practice, unless we resort to a massive cut and paste exercise that will produce many other problems.

The PreparedStatement Question

It's important to understand the distinction between the java.sql.PreparedStatement interface and the java.sql.Statement interface. With a prepared statement, we provide the necessary SQL containing ? place holders for bind variables and set a parameter for each placeholder. With an ordinary statement, we provide the entire SQL string, including a string representation of each parameter value.

Prepared statements should generally be preferred.

First, they significantly simplify the handling of strings and other objects. If we don't use prepared statements, we will need to escape illegal characters in strings. For example, the following query will fail:

    SELECT id FROM customers WHERE surname='d'Artagnan' 

The surname d'Artagnan contains an illegal 'character, which the database will view as the terminator of a string literal. In Oracle we'll get error ORA-01756 with the message quoted string not properly terminated. We'll need to amend the query as follows to get it to work:

    SELECT id FROM customers WHERE surname='d"Artagnan' 

We will also have problems with DATEs and other standard object types, for which we'll need to use an RDMBS-specific string format. With prepared statements we don't need to worry about escaping or object conversion. If we call the correct setxxxx() methods or know the appropriate JDBC type (one of the constants defined in the java.sql.Types class) corresponding to the RDBMS type, the JDBC driver will transparently take care of the conversion and any escaping. Our JDBC code will also be more portable, as the driver will know how to convert standard Java objects to and from the SQL types appropriate for the target database.

Second, prepared statements are also potentially more efficient than string statements, although the extent of any performance gain will vary between platforms. Prepared statements are more expensive to create than ordinary string statements, yet may reduce the amount of work the JDBC driver, and perhaps even the database, has to do if they are reused. The first time a prepared statement is executed, it can be parsed and compiled by the database, and is likely to be placed in a statement cache. The database is thus able to store the execution path for the statement, which is likely to reduce the amount of work it needs to do to execute the statement in future. In contrast, regular SQL string statements cannot be cached in this way if they have variable parameters, because statements with different literal parameter values appear different to the database. Not only does this prevent useful caching, it may also significantly increase overall load on the database by breaking the caching of other statements. The database's statement cache may fill up with the apparently different statements, leaving no room for genuinely cacheable statements from the same or other applications.

Unfortunately it's impossible to be certain of this behavior with all databases, JDBC drivers, and application servers, as the JDBC 2.0 API (the version guaranteed with J2EE 1.3) and J2EE specifications do not require prepared statement caching. Nor can we ensure caching ourselves: we're forced to close prepared statements after we create them if we want to close the connection we obtained (which we usually do in a J2EE environment). This means that we're reliant on the application server's JDBC connection pool, rather than our code, for any reuse of PreparedStatements. Fortunately, most modern servers appear to pool PreparedStatements. The performance benefit this brings will depend on the underlying JDBC driver and the database.

JDBC 3.0 introduces PreparedStatement caching as standard. This is largely transparent to developers: it's assumed that a connection pool implementation may provide support for prepared statement caching. Thus PreparedStatements will become still more attractive in the future.

Important 

Prefer JDBC PreparedStatements to Statements. They lessen the likelihood of type errors and may improve efficiency.



Expert One-on-One J2EE Design and Development
Microsoft Office PowerPoint 2007 On Demand
ISBN: B0085SG5O4
EAN: 2147483647
Year: 2005
Pages: 183

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net