A Generic JDBC Abstraction Framework

It's not enough to understand the JDBC API and the issues in using it correctly. Using JDBC directly is simply too much effort, and too error-prone. We need not just understanding, but a code framework that makes JDBC easier to use.

In this section we'll look at one such solution: a generic JDBC abstraction framework, which we'll use in the sample application, and which can significantly simplify application code and minimize the likelihood of making errors.

Important 

The JDBC API is too low-level for using it directly to be a viable option. When using JDBC, always use helper classes to simplify application code. However, don't try to write your own O/R mapping layer. If you need O/R mapping, use an existing solution.

In this section, we'll examine the implementation and usage of the JDBC abstraction framework used in the sample application, which is included in the framework code in the download accompanying this book. This is a generic framework, which you can use in your own applications to simplify use of JDBC and lessen the likelihood of JDBC-related errors.

Motivation

Writing low-level JDBC code is painful. The problem is not the SQL, which is usually easy to author and understand, and accounts for little of the total code volume, but the incidentals of using JDBC. The JDBC API is not particularly elegant and is too low-level for use in application code.

As we've seen, using the JDBC API even to perform very simple tasks is relatively complex and error-prone. Take the following example, which queries the IDs of available seats in our ticketing system:

    public List getAvailableSeatlds(DataSource ds, int performanceld,        int seatType) throws ApplicationException {      String sql = "SELECT seat_id AS id FROM available_seats " +                   "WHERE performance_id = ? AND price_band_id = ?";                                                               List seatlds = new LinkedList();      Connection con = null;      PreparedStatement ps = null;      ResultSet rs = null;      try {        con = ds.getConnection();        ps = con.prepareStatement(sql);        ps.setlnt(1, performanceld);        ps.setlnt(2, seatType);        rs = ps.executeQuery();        while (rs.next()) {          int seatld = rs.getlnt(1);          seatlds.add(new Integer(seatld));        }                                                                                                                            rs.close();        ps.close();      }      catch (SQLException ex) {        throw new ApplicationException ("Couldn't run query [" + sql + "]", ex);      }      finally {        try {          if (con != null)            con.close();        }        catch (SQLException ex) {          // Log and ignore        }      }      return seatlds;    } 

Although the SQL is trivial, because of the use of a view in the database to conceal the outer join required, executing this query requires 30-odd lines of JDBC code, few of which actually do anything. (I've highlighted the functional lines of code.) Such a predominance of "plumbing" code that doesn't deliver functionality indicates a poor implementation approach. An approach that makes very simple things so hard is unworkable. If we use this approach every time we need to perform a query, we'll end up with reams of code and the potential for many errors. Important operations like closing the connection, which are incidental to the data retrieval operation, dominate the listing. Note that closing the connection requires a second trycatch block in the finally block of the outer trycatch. Whenever I see a nested trycatch, I feel a powerful desire to refactor that code into a framework class.

Note 

I haven't included code to obtain the DataSource, which would use JNDI in a J2EE application. I assume that this is accomplished elsewhere using a suitable helper class to avoid JNDI access further bloating the code.

A simple abstraction framework can make this query much simpler, while still using the JDBC API under the covers. The following code defines a reusable, threadsafe query object extending a framework class, which we'll look at in detail later:

    class AvailabilityQuery extends        com.interface21.jdbc.object.ManualExtractionSqlQuery {      public AvailabilityQuery (DataSource ds) {        super (ds, "SELECT seat_id AS id FROM available_seats WHERE " +                   "performance_id = ? AND price_band_id = ?");        declareParameter(new SqlParameter (Types. NUMERIC));        declareParameter(new SqlParameter (Types. NUMERIC));        compile();      }      protected Object extract (ResultSet rs, int rownum) throws SQLException {        return new Integer (rs.getlnt("id"));      }    } 

This takes one third of the code of the first version, and every line does something. The troublesome error handling is left to the superclass. We need only to write code to provide the SQL query, provide the bind parameters (after specifying their JDBC types) and extract the results. In other words, we only need to implement application functionality, not plumbing. The framework superclass provides a number of execute methods, one of which we can run like this:

    AvailabilityQuery availabilityQuery = new AvailabilityQuery (ds);    List 1 = availabilityQuery. execute(1, 1); 

Once constructed, we can reuse this query object. The execute() method throws a runtime exception, not a checked exception, meaning that we need only catch it if it's recoverable. The resemblance to the JDO Query interface, which we've just discussed, is intentional. The JDO API is easy to use and already familiar to a growing number of developers, hence it makes sense to apply the same principles to JDBC. The key difference, of course, is that our JDBC query doesn't return mapped objects; changes to the returned objects will have no effect on the database.

In the remainder of this section we'll look at how this abstraction framework is implemented and how it can be used to simplify application code.

Aims

Remember the Pareto Principle (the 80:20 rule)? The best results can be achieved with JDBC abstraction by not trying to achieve too much.

The following areas account for most developer effort using the JDBC API directly and are most error-prone. Our abstraction should try to address each:

  • Too much code - Queries, for example, must acquire a Connection, a Statement and a ResultSet, and iterate over the ResultSet.

  • Correct cleanup in the event of errors (ensuring that connections and statements are closed, as we discussed above). Most competent Java developers have cleaned up a lot of broken trycatch blocks around JDBC code, after seeing the number of open connections gradually mount in server-side applications.

  • Dealing with SQLExceptions - These are checked exceptions, which application code shouldn't really be forced to catch, as catching them requires knowledge of JDBC.

  • Making it easier for user code to establish the cause of exceptions, without needing to examine SQLState or vendor codes.

Surprisingly, there don't seem to be many libraries or frameworks that address these problems. Most are much more ambitious (aiming at some form of O/R mapping, for example), meaning they become complex to implement and to use. Hence, I've developed a framework for the sample application, which we'll talk about now and which can be used in any application using JDBC (these classes are actually the most recent in a line of such classes I've developed for various clients).

This isn't the only way to address the challenges of working with JDBC, but it's simple and highly effective.

Another key problem—the danger of SQL being sprinkled around the application—is best addressed with the DAO pattern, which we've already discussed. In this section, we'll talk about implementing data access objects, not their place in overall application architecture.

Exception Handling

Exception handling is such an important issue that we should develop a consistent strategy for it before we begin to implement our framework proper.

The JDBC API is an object lesson in how not to use exceptions. As well as ensuring that we release resources even if we encounter errors, our abstraction layer should provide a better exception handling model for application code than JDBC does.

JDBC uses a single exception class—java.lang.SQLException—for all problems except data truncation. Catching a SQLException in itself provides no more information than "something went wrong". As we've seen, it's only possible to distinguish between problems by examining potentially vendor-specific codes included in SQL exceptions.

JDBC could in fact have offered more sophisticated error handling while retaining portability between databases. The following are just a few of the errors that are meaningful in any RDBMS:

  • Grammatical error in SQL statement issued using JDBC

  • Data integrity constraint violation

  • Attempt to set the value of a SQL bind variable to an incorrect type

Such standard problems could—and should—have been modeled as individual subclasses of Java.lang.SQLException. In our abstraction framework, we will provide a richer exception hierarchy that does use individual exception classes.

We'll also address two other issues with JDBC exception handling.

  • We don't want to tie code using our abstraction layer to JDBC. Our abstraction layer is primarily intended for use to implement the Data-Access Object pattern. If code using DAOs ever needs to catch resource-specific exceptions, such as SQLExceptions, the decoupling of business logic and data access implementation that the DAO pattern exists to provide is lost. (Exceptions thrown are an integral part of method signatures.) So while the JDBC API, reasonably enough, uses JDBC-specific exceptions, we won't tie our exceptions to JDBC.

  • Following our discussion of checked and runtime exceptions in Chapter 4, we will make our API much easier to use by making all exceptions runtime, rather than checked exceptions. JDO takes this approach with good results. Using runtime exceptions, callers need catch only those exceptions (if any) that they may be able to recover from. This models JDBC (and other data-access) usage well: JDBC exceptions are often unrecoverable. For example, if a SQL query contained an invalid column name, there's no point catching it and retrying. We want the fatal error logged, but need to correct the offending application code.

  • If we're running the query from an EJB, we can simply let the EJB container catch the runtime exception and roll back the current transaction. If we caught a checked exception in the EJB implementation we'd need to roll back the transaction ourselves, or throw a runtime exception that would cause the container to do so. In this example, using a checked exception would allow more scope for application code to cause incorrect behavior. Likewise, failure to connect to a database is probably fatal. In contrast, a recoverable error that we might want to catch would be an optimistic locking violation.

A Generic Data-Access Exception Hierarchy

We can meet all our exception handling goals by creating a generic data-access exception hierarchy. This won't be limited to use with JDBC, although some JDBC-specific exceptions will be derived from generic exceptions within it. This exception hierarchy will enable code using DAOs to handle exceptions in a database-agnostic way.

The root of all data-access exceptions is the abstract DataAccessException class, which extends the NestedRuntimeException class discussed in Chapter 4. Using the NestedRuntimeException superclass enables us to preserve the stack trace of any exceptions (such as SQLExceptions) we may need to wrap.

Unlike SQLException, DataAccessException has several subclasses that indicate specific data-access problems. The direct subclasses are:

  • DataAccessResourceFailureException
    Complete failure to access the resource in question. In JDBC implementation, this would result from failure to get a connection from a datasource. However, such errors are meaningful for any persistence strategy.

  • CleanupFailureDataAccessException
    Failure to clean up (for example, by closing a JDBC Connection) after successfully completing an operation. In some cases we might want to treat this as a recoverable error, preventing the current transaction from being rolled back (for example, we know that any update succeeded).

  • DataIntegrityViolationException
    Thrown when an attempted update is rejected by the database because it would have violated data integrity.

  • InvalidDataAccessApiUsageException
    This exception indicates not a problem from the underlying resource (such as a SQLException), but incorrect usage of the data-access API. The JDBC abstraction layer discussed below throws this exception when it is used incorrectly.

  • InvalidDataAccessResourceUsageException
    This exception indicates that the data-access resource (such as an RDBMS) was used incorrectly. For example, our JDBC abstraction layer will throw a subclass of this exception on an attempt to execute invalid SQL.

  • OptimisticLockingViolationException
    This indicates an optimistic locking violation and is thrown when a competing update is detected. This exception will normally be thrown by a Data-Access Object, rather than an underlying API like JDBC.

  • DeadlockLoserDataAccessException
    Indicates that the current operation was a deadlock loser, causing it to be rejected by the database.

  • UncategorizedDataAccessException
    An exception that doesn't fit within the recognized categories listed above. This exception is abstract, so resource-specific exceptions will extend it carrying additional information. Our JDBC abstraction layer will throw this exception when it catches a SQLException it cannot classify.

All these concepts are meaningful for any persistence strategy, not just JDBC. Generic exceptions will include nested root causes, such as SQLExceptions, ensuring that no information is lost and ensuring that all available information can be logged.

The following UML class diagram illustrates our exception hierarchy, contained in the com.interface21.dao package. Note how some JDBC-specific exceptions in the com.interface21.jdbc.core package (shown below the horizontal line) extend generic exceptions, enabling the maximum information to be included about a problem without making calling code dependent on JDBC. Calling code will normally catch generic exceptions, although JDBC-specific DAO implementations can catch JDBC-specific subclasses they understand, while letting fatal errors propagate to business objects that use them.

click to expand

Although this exception hierarchy may appear complex, it can help to deliver a great simplification in application code. All these exception classes will be thrown by our framework; application code need only catch those it considers recoverable. Most application code won't catch any of these exceptions.

This hierarchy automatically gives us much more useful information than a SQLException and makes application code that needs to work with exceptions much more readable. While we can choose to catch the base DataAccessException if we want to know if anything went wrong (in the same way as we catch a SQLException), it will be more useful in most cases to catch only a particular subclass.

Consider a plausible requirement to perform an update operation and apply a recovery strategy if the operation resulted in a data integrity violation. In an RDBMS context, the operation might have attempted to set a non-nullable column's value to null. Using the exception hierarchy we've just examined, all we need to do is catch com.interface21.dao.DataIntegrityViolationException. All other exceptions can be ignored, as they're unchecked. The following code clearly conveys the recovery requirement just described. As a bonus, it isn't dependent on JDBC:

    try {          //do data operation using JDBC abstraction layer    }    catch (DataIntegrityViolationException ex) {          // Apply recovery strategy    } 

Now consider how we would need to do this using JDBC, without an abstraction layer. As SQLExceptions are checked, we can't let exceptions we're not interested in through. The only way to establish whether we are interested in the exception is to examine its SQLState string or vendor code. In the following example we check the SQLState string. As this may be null, we need to check it's non-null before examining it:

    try {      //do data operation using JDBC    }    catch (SQLException ex) (      boolean recovered = false;      String sqlstate = sqlex.getSQLStat();      if (sqlstate != null) {        String classCode = sqlstate.substring(0, 2);        if ("23".equals(classCode) ||            "27".equals(classCode) ||            "44".equals(classCode)) {          // Apply recovery strategy          recovered = true;        }      }      if (!recovered)        throw new ApplicationSpecificException("Other SQL exception", ex);                                                      }    // Finally block omitted 

There's no doubt which is the simpler, more readable, and more maintainable approach. Not only is the second approach far more complex, it makes the calling code dependent on intimate details of the JDBC API.

Converting JDBC Exceptions to Generic Exceptions

In the class diagram above, all the classes above the horizontal line are generic. These are the exceptions that calling code will work with.

The exceptions below the line areJDBC-specific subclasses of generic data access exceptions. Most callers won't use these exceptions directly (because such use would tie them to JDBC), but they provide more specific information if necessary. The BadSqlGrammarException, for example, extends the generic InvalidDataAccessResourceUsageException, and is thrown when the specified SQL is invalid.

So far we haven't considered how to convert between SQL exceptions and our generic exceptions. To do this we'll need to examine SQLState or vendor codes. However, as SQLState codes aren't sufficient to diagnose all problems, we must retain the option of RDBMS-specific implementation.

We do this by creating an interface that defines the translation functionality required, enabling us to use any implementation without affecting the working of our abstraction framework. (As usual, interface-based design is the best way to achieve portability). Our JDBC abstraction layer will use an implementation of the com.interface21.jdbc.core.SQLExceptionTranslater interface, which knows how to translate SQLExceptions encountered during JDBC operations to exceptions in our generic hierarchy:

    public interface SQLExceptionTranslater {      DataAccessException translate (String task,                                     String sql,                                     SQLException sqlex);    } 

The default implementation is com.interface21.jdbc.core.SQLStateSQLExceptionTranslater, which uses the portable SQLState code. This implementation avoids the need for chains of if/else statements by building a static data structure of SQLState class codes. The following is a partial listing:

    public class SQLStateSQLExceptionTranslater        implements SQLExceptionTranslater {      private static Set BAD_SQL_CODES = new HashSet();      private static Set INTEGRITY_VIOLATION_CODES = new HashSet();      static {        BAD_SQL_CODES.add("42");        BAD_SQL_CODES.add("65");        INTEGRITY_VIOLATION_CODES.add("23");        INTEGRITY_VIOLATION_CODES.add("27");        INTEGRITY_VIOLATION_CODES.add("44");    }    public DataAccessException translate (String task, String sql,        SQLException sqlex) {      String sqlstate = sqlex.getSQLState();      if (sqlstate != null) {        String classCode = sqlstate.substring(0, 2);        if (BAD_SQL_CODES. contains(classCode))          throw new BadSqlGrammarException("(" + task +                "): SQL grammatical error "' + sql + ""', sql, sqlex);        if (INTEGRITY_VIOLATION_CODES. contains (classCode))          throw new DataIntegrityViolationException("(" + task +                "): data integrity violated by SQL "' + sql + ""', sqlex);      }      // We couldn't categorize the problem      return new UncategorizedSQLException("(" + task +             "): encountered SQLException [" + sqlex.getMessage() +             "]", sql, sqlex);      }    } 

The following partial listing illustrates an Oracle-specific implementation, com.interface21.jdbc.core.oracle.OracleSQLExceptionTranslater, which uses the vendor code to identify the same range of problems. Note that vendor codes are far more expressive than SQLState codes:

    public class OracleSQLExceptionTranslater        implements SQLExceptionTranslater {      public DataAccessException translate(String task, String sql,           SQLException sqlex) {        switch (sqlex.getErrorCode()) {          case 1 :            // Unique constraint violated            return new DataIntegrityViolationException(                       task + ": " + sqlex.getMessage(), sqlex);          case 1400:            // Can't insert null into non-nullable column            return new DataIntegrityViolationException(                       task + ": " + sqlex.getMessage(), sqlex);          case 936 :            // Missing expression                   return new BadSqlGrammarException(task, sql, sqlex);          case 942 :            // Table or view does not exist               return new BadSqlGrammarException(task, sql, sqlex);        }        // We couldn't identify the problem more precisely        return new UncategorizedSQLException(" ( " + task +                   "): encountered SQLException [" +                   sqlex.getMessage() + "]", sql, sqlex);      }    } 

Two Levels of Abstraction

Now that we have a powerful, database-agnostic approach to exception handling, let's look at implementing an abstraction framework that will make JDBC much easier to use.

The JDBC framework used in the sample application involves two levels of abstraction. (In Chapter 4 we discussed how frameworks are often layered, enabling developers using them to work with the appropriate level of abstraction for the task in hand.)

The lower level of abstraction, in the com.interface21.jdbc.core package, takes care of JDBC workflow and exception handling. It achieves this using a callback approach, requiring application code to implement simple callback interfaces.

The higher level of abstraction, in the com.interface21.jdbc.object package (which we've seen in use), builds on this to provide a more object-oriented, JDO-like interface that models RDBMS operations as Java objects. This completely hides the details of JDBC from application code that initiates these operations.

Let's look at each level of abstraction in turn.

A Framework to Control JDBC Workflow and Error Handling

The lower level of abstraction handles the issuing of JDBC queries and updates, taking care of resource cleanup and translating SQLExceptions into generic exceptions using the SQLExceptionTranslater discussed above.

"Inversion of Control" Revisited

Remember our discussion of the Strategy design pattern and "inversion of control" in Chapter 4? We saw that sometimes the only way to ensure that complex error handling is concealed by generic infrastructure code is for infrastructure code to invoke application code (an approach termed "inversion of control"), rather than for application code to invoke infrastructure code as in a traditional class library. We saw that infrastructure packages that apply this approach are usually termed frameworks, rather than class libraries.

The complexity of JDBC error handling demands this approach. We want a framework class to use the JDBC API to execute queries and updates and handle any errors, while we supply the SQL and any parameter values. To achieve this, the framework class must invoke our code, rather than the reverse.

The com.interface21.jdbc.core package

All JDBC-specific classes comprising the lower level of abstraction are in the com.interface21.jdbc.core package.

The most important class in the com.interface21.jdbc.core package is JdbcTemplate, which implements core workflow and invokes application code as necessary. The methods in JdbcTemplate run queries while delegating the creation of PreparedStatements and the extraction of the results from JDBC ResultSets to two callback interfaces: the PreparedStatementCreator interface and the RowCallbackHandler interface. Application developers will need to provide implementations of these interfaces, not execute JDBC statements directly or implement JDBC exception handling.

The following UML class diagram illustrates the relationship of the JdbcTemplate class to the helper classes it uses and the application-specific classes that enable it to be parameterized:

click to expand

Let's consider each class and interface in turn. The classes above the horizontal line belong to the framework; those below the line indicate how application-specific classes implement framework interfaces.

The PreparedStatementCreator Interface and Related Classes

The PreparedStatementCreator interface must be implemented by application-specific classes to create a java.sql.PreparedStatement, given a java.sql.Connection. This means specifying the SQL and setting bind parameters for an application-specific query or update, which will be run by the JdbcTemplate class. Note that an implementation of this interface doesn't need to worry about obtaining a connection or catching any SQLExceptions that may result from its work. The PreparedStatement it creates will be executed by the JdbcTemplate class. The interface is as follows:

    public interface PreparedStatementCreator {      PreparedStatement createPreparedStatement (Connection conn)        throws SQLException;    } 

The following shows a typical implementation, which uses standard JDBC methods to construct a PreparedStatement with the desired SQL and set bind variable values. Like many implementations of such simple interfaces, this is an anonymous inner class:

    PreparedStatementCreator psc = new PreparedStatementCreator() {      public PreparedStatement createPreparedStatement (Connection conn)          throws SQLException {        PreparedStatement ps = conn. prepareStatement (          "SELECT seat_id AS id FROM available_seats WHERE " +          "performance_id = ? AND price_band_id = ?");        ps.setInt(1, performanceId);        ps.setInt(2, seatType);        return ps;      }    }; 

The PreparedStatementCreatorFactory class is a generic helper that can be used repeatedly to create PreparedStatementCreator objects with different parameter values, based on the same SQL statement and bind variable declarations. This class is largely used by the higher-level, object-abstraction framework described below; application code will normally define PreparedStatementCreator classes as shown above.

The RowCallbackHandler Interface and Related Classes

The RowCallbackHandler interface must be implemented by application-specific classes to extract column values from each row of the ResultSet returned by a query. The JdbcTemplate class handles iteration over the ResultSet. The implementation of this interface may also leave SQLExceptions uncaught: the JdbcTemplate will handle them. The interface is as follows:

    public interface RowCallbackHandler {      void processRow(ResultSet rs) throws SQLException;    } 

Implementations should know the number of columns and data types to expect in the ResultSet. The following shows a typical implementation, which extracts an int value from each row and adds it to a list defined in the enclosing method:

    RowCallbackHandler rch = new RowCallbackHandler() {      public void processRow(ResultSet rs) throws SQLException {        int seatId = rs.getInt(1) ;        list.add(new Integer (seatId) );      }    }; 

The RowCountCallbackHandler class is a convenient framework implementation of this interface that stores metadata about column names and types and counts the rows in the ResultSet. Although it's a concrete class, its main use is as a superclass of application-specific classes.

The ResultReader interface extends RowCallbackHandler to save the retrieved results in a java.util.List:

    public interface ResultReader extends RowCallbackHandler {      List getResults() ;    } 

The conversion of each row of the ResultSet to an object is likely to vary widely between implementations, so there is no standard implementation of this interface.

Miscellaneous Classes

We've already examined the SQLExceptionTranslater interface and two implementations. The JdbcTemplate class uses an object of type SQLExceptionTranslater to convert SQLExceptions into our generic exception hierarchy, ensuring that this important part of the JdbcTemplate class's behavior can be parameterized.

The DataSourceUtils class contains a static method to obtain a Connection from a javax.sql.DataSource, converting any SQLException to an exception from our generic hierarchy, a method to close a Connection (again with appropriate error handling) and a method to obtain a DataSource fromJNDI. The JdbcTemplate class uses the DataSourceUtils class to simplify obtaining and closing connections.

The JdbcTemplateClass: The Central Workflow

Now let's look at the implementation of the JdbcTemplate class itself. This is the only class that runs JDBC statements and catches SQLExceptions. It contains methods that can perform any query or update: the JDBC statement executed is parameterized by static SQL or a PreparedStatementCreator implementation supplied as a method argument.

The following is a complete listing of the JdbcTemplate class:

    package com.interface21.jdbc.core;    import java.sql.Connection;    import java.sql.PreparedStatement;    import java.sql.ResultSet;    import java.sql.SQLException;    import java.sql.SQLWarning;    import java.sql.Statement;    import java14.java.util.logging.Level;    import java14.java.util.logging.Logger;    import javax.sql.DataSource;    import com.interface21.dao.DataAccessException;    public class JdbcTemplate { 

The instance data consists of a Logger object, used to log info messages about SQL operations, a DataSource object, a boolean indicating whether SQLWarnings should be ignored or treated as errors, and the SQLExceptionTranslater helper object. All these instance variables are read-only after the JdbcTemplate has been configured, meaning that JdbcTemplate objects are threadsafe:

    protected final Logger logger = Logger.getLogger(getClass().getName() );    private DataSource dataSource;    private boolean ignoreWarnings = true;    private SQLExceptionTranslater exceptionTranslater; 

The constructor takes a DataSource, which will be used throughout the JdbcTemplate's lifecycle, and instantiates a default SQLExceptionTranslater object:

    public JdbcTemplate(DataSource dataSource) {      this.dataSource = dataSource;      this.exceptionTranslater = new SQLStateSQLExceptionTranslater() ;    } 

The following methods can be optionally used to modify default configuration before using the JdbcTemplate. They enable behavior on warnings and exception translation to be parameterized:

    public void setIgnoreWarnings(boolean ignoreWarnings) {      this.ignoreWarnings = ignoreWarnings;    }    public boolean getIgnoreWarnings() {      return ignoreWarnings;    }    public void setExceptionTranslater(      SQLExceptionTranslater exceptionTranslater) {      this.exceptionTranslater = exceptionTranslater;    }    public DataSource getDataSource() {      return dataSource;    } 

The remainder of the JdbcTemplate class consists of methods that perform JDBC workflow using the callback interfaces discussed above.

The simplest query() method, which takes a static SQL string and a RowCallbackHandler to extract results from the query ResultSet, illustrates how the use of callbacks centralizes control flow and error handling in the JdbcTemplate class. Note that this method throws our generic com.interface21.dao.DataAccessException, allowing callers to find the cause of any error without usingJDBC:

    public void query(String sql, RowCallbackHandler callbackHandler)        throws DataAccessException {      Connection con = null;      PreparedStatement ps = null;      ResultSet rs = null;      try {        con = DataSourceUtils.getConnection(this.dataSource);        ps = con.prepareStatement(sql);        rs = ps.executeQuery();        if (logger.isLoggable(Level.INFO))          logger.info("Executing static SQL query "' + sql +""');        while (rs.next()) {          callbackHandler.processRow(rs);        }        SQLWarning warning = ps.getWarnings();        rs.close();        ps.close();        throwExceptionOnWarningIfNotIgnoringWarnings(warning);                                                                    }      catch (SQLException ex) {        throw this.exceptionTranslater.translate("JdbcTemplate.query(sql)",                                                 sql, ex);                                                                        }      finally {        DataSourceUtils.closeConnectionIfNecessary(this.dataSource, con);                                                         }    } // query 

The throwExceptionOnWarningIfNotIgnoringWarnings() method is a private helper used in several public workflow methods. If the warning it is passed is non-null and the JdbcTemplate is configured not to ignore exceptions, it throws an exception. If a warning was encountered, but the JdbcTemplate is configured to ignore warnings, it logs the warning:

    private void throwExceptionOnWarningIfNotIgnoringWarnings(        SQLWarning warning) throws SQLWarningException {      if (warning != null) {        if (this.ignoreWarnings) {          logger.warning("SQLWarning ignored: " + warning);        } else {          throw new SQLWarningException("Warning not ignored", warning);        }      }    } 

A more general query() method issues a PreparedStatement, which must be created by a PreparedStatementCreator implementation. I've highlighted the major difference from the query() method listed above:

    public void query(PreparedStatementCreator psc,        RowCallbackHandler callbackHandler) throws DataAccessException {      Connection con = null;      Statement s = null;      ResultSet rs = null;      try {        con = DataSourceUtils.getConnection(this.dataSource);        PreparedStatement ps = psc.createPreparedStatement(con);                                                                    if (logger.isLoggable(Level.INFO) )          logger.info("Executing SQL query using PreparedStatement: ["                      + psc + "]");        rs = ps.executeQuery();        while (rs.next()){          if (logger.isLoggable(Level.FINEST) )            logger.finest("Processing row of ResultSet");            callbackHandler.processRow(rs);        }        SQLWarning warning = ps.getWarnings();        rs.close();        ps.close();        throwExceptionOnWarningIfNotIgnoringWarnings(warning);      }      catch (SQLException ex) {        throw this.exceptionTranslater.translate(              "JdbcTemplate.query(psc) with PreparedStatementCreator ["+              psc + "]", null, ex);      }      finally {        DataSourceUtils.closeConnectionIfNecessary(this.dataSource, con);      }    } 

We apply the same approach to updates. The following method allows the execution of multiple updates using a single JDBC Connection. An array of PreparedStatementCreator objects supplies the SQL and bind parameters to use. This method returns an array containing the number of rows affected by each update:

    public int[] update(PreparedStatementCreator[] pscs)        throws DataAccessException {      Connection con = null;      Statement s = null;      int index = 0;      try {        con = DataSourceUtils.getConnection(this.dataSource);        int[] retvals = new int[pscs.length];        for (index = 0; index < retvals.length; index++) {          PreparedStatement ps = pscs[index].createPreparedStatement(con);          retvals[index] = ps.executeUpdate();          if (logger.isLoggable(Level.INFO) )            logger.info("JDBCTemplate: update affected " + retvals[index] +                      " rows");          ps.close();        }        return retvals;      }      catch (SQLException ex) {        throw this.exceptionTranslater.translate ("processing update " +             (index + 1) + " of " + pscs.length + "; update was [" +             pscs[index] + "]", null, ex);      }      finally {        DataSourceUtils.closeConnectionIfNecessary(this.dataSource, con);      }    } 

The following convenience methods use the above method to execute a single update, given static SQL or a single PreparedStatementCreator parameter:

   public int update(final String sql) throws DataAccessException {     if (logger.isLoggable(Level.INFO) )       logger.info("Running SQL update "' + sql + ""');     return update(PreparedStatementCreatorFactory.       getPreparedStatementCreator(sql) );   }   public int update(PreparedStatementCreator psc)       throws DataAccessException {     return update(new PreparedStatementCreator[] { psc })[0];   } } 

Note that the JdbcTemplate object maintains a javax.sql.DataSource instance variable, from which it obtains connections for each operation. It's important that the API should work with DataSource objects rather than java.sql.Connection objects, because otherwise:

  • We'd have to obtain the connections elsewhere, meaning more complexity in application code and the need to catch SQLExceptions if using the DataSource.getConnection() method.

  • It's important that JdbcTemplate closes the connections it works with, as closing a connection can result in an exception, and we want our framework to take care of all JDBC exception handling. Obtaining the connections elsewhere and closing them in the JdbcTemplate class wouldn't make sense and would introduce the risk of attempts to use closed connections.

Using a DataSource doesn't limit the JdbcTemplate class to use within aJ2EE container: the DataSource interface is fairly simple, making it easy to implement for testing purposes or in standalone applications.

Using the JdbcTemplate Class

Now that we've looked at the implementation, let's see the com.interface21.jdbc.core package in action.

Performing Queries

Using the JdbcTemplate class, we can accomplish the JDBC query for seat IDs, shown under Motivation above, as follows, using anonymous inner classes to implement the RowCallbackHandler and PreparedStatementCreator interfaces. This implementation of RowCallbackHandler saves data in a list of Integers defined in the enclosing method:

    public List getAvailableSeatIdsWithJdbcTemplate(          DataSource ds, final int performanceId, final int seatType)          throws DataAccessException {        JdbcTemplate t = new JdbcTemplate(ds);        final List 1 = new LinkedList();        PreparedStatementCreator psc = new PreparedStatementCreator() {          public PreparedStatement createPreparedStatement(Connection conn)              throws SQLException {            PreparedStatement ps = conn.prepareStatement(               "SELECT seat_id AS id FROM available_seats WHERE " +               "performance_id = ? AND price_band_id = ?");            ps.setInt(1, performanceId);            ps.setInt(2, seatType);            return ps;          }        };        RowCallbackHandler rch = new RowCallbackHandler() {          public void processRow(ResultSet rs) throws SQLException {            int seatId = rs.getInt(1);            1.add(new Integer(seatId));          }        };        t.query(psc, rch);        return 1;    } 

This halves the amount of code required and addresses most of the problems we identified in using the JDBC API directly. We can work with PreparedStatement and ResultSet objects with a minimum of irrelevant code. Most importantly, using the JdbcTemplate class eliminates the major causes of errors. There is no risk that the connection won't be closed: the JdbcTemplate class ensures this. Application code doesn't need to catch checked, uninformative SQLExceptions: our generic data access exception hierarchy offers a richer classification of exceptions, but, since data access exceptions are unchecked, application code can typically leave exceptions to be dealt with by the application server.

Performing Updates

If we use the JDBC API directly, updates call for a similar amount of code to queries. Again error handling is dominant and the SQL we want to execute is obscured. Again the JdbcTemplate class can deliver real benefits. The JdbcTemplate update() method is capable of running updates using the PreparedStatementCreator callback interface we've already seen. The following example (not part of our sample application!) would delete all seat reservations and bookings for a particular seat type in a given performance. The example assumes we already have a JdbcTemplate object in scope. As JdbcTemplate objects are threadsafe, we will normally keep one as an instance variable in DAO implementations usingJDBC:

    class PerformanceCleanerPSC implements PreparedStatementCreator {         private int pid;         private int pb;         public PerformanceCleanerPSC(int pid, int pb) {           this.pid = pid;           this.pb = pb;         }         public PreparedStatement createPreparedStatement(Connection conn)             throws SQLException {           PreparedStatement ps = conn.prepareStatement("UPDATE seat_status " +             "SET booking_id = null WHERE performance_id = ? AND " +             "price_band_id = ?");           ps.setInt(1, pid);           ps.setInt(2, pb);           return ps;         }       };    PreparedStatementCreator psc = new PerformanceCleanerPSC (1, 1);    int rowsAffected = jdbcTemplate.update(psc); 

Updates using static SQL are even easier. The following example would mark all seats in our ticketing application as available, without any need to implement callback interfaces:

    template.update("UPDATE SEAT_STATUS SET BOOKING_ID = NULL"); 

A Higher Level of Abstraction: Modeling RDBMS Operations as Java Objects

Using the JdbcTemplate class solves most of the problems we saw with use of raw JDBC, but it's still arguably too low-level. Application code using the JdbcTemplate class still requires knowledge of JDBC Statements and ResultSets. The use of callbacks, although essential to move workflow within the framework, is conceptually complex (although the implementations of callback interfaces are usually very simple).

The com.interface21.jdbc.core package solves the most complex problems of using JDBC, but we want to present the solution more simply to application code. We need a higher level of abstraction, building on the functionality provided by the com.interface21.jdbc.core package.

Implementation of the com.interface21.jdbc.object Package

The com.interface21.jdbc.object package conceals the com.interface21.jdbc.core package (on which it is built), offering a higher-level, JDO-like object abstraction. Application code doesn't need to implement callback methods. Instead, each query, or other RDBMS operation, is modeled as a reusable, threadsafe object. Once configured, each object can be run repeatedly, with different parameters, mapped onto SQL bind variables, supplied each time.

An important difference from JDO is that because our RDBMS operation objects are classes, not interfaces, it is possible for application queries to subclass them. (Application code using JDO has to obtain instances of the Query interface from a PersistenceManager.) By using an individual subclass for each RDBMS operation we can completely conceal SQL from calling code. Objects representing RDBMS operations may implement interfaces that aren't tied to relational databases.

This approach can be used consistently for queries, updates, and stored procedures. The following UML class diagram illustrates the inheritance hierarchy among the framework classes, and the place of a hypothetical ApplicationSpecificQuery class in it. It also shows how these classes relate to the lower-level JDBC abstraction classes discussed above:

click to expand

Let's look at each class in this inheritance hierarchy in detail. Due to the number of classes involved, it's impossible to provide a complete listing here. Please refer to the com.interface21.jdbc.object package under the /framework/src directory of the download as necessary while reading the description below.

The RdbmsOperation Base Class

RdbmsOperation is an abstract class and is the root of the class hierarchy. It holds a javax.sql.DataSource and SQL string as instance variables and allows bind parameters to be declared. (Queries, updates and stored procedures share the concept of bind variables.) Once it has been configured, an RdbmsOperation must be "compiled"—another idea borrowed from the JDO Query interface. The meaning of compilation will vary between subclasses, but it will at least validate that a DataSource and SQL have been supplied. After compilation, no more parameters can be added, but the operation can be executed repeatedly.

RDBMS operations are JavaBeans. The SQL to execute and the DataSource to use are exposed as bean properties, and subclasses will normally expose their own configuration properties as bean properties.

Parameters are declared using the declareParameter(SqlParameter) method. The SqlParameter class is defined in the com.interface21.jdbc.core package, but is used principally by this package. The main purpose of parameter declaration is to specify each parameter's JDBC type, as enumerated in the java.sql.Types class. This ensures that the framework code can use the JDBC PreparedStatement set<Type>() methods rather than setObject() to set bind variables, which is necessary to ensure correct handling of null values and is potentially more efficient. Parameters to queries and updates don't require names (merely the correct ordering and types); stored procedures support output as well as input parameters, and thus require parameter names.

Although the RdbmsOperation class ensures consistent concepts for queries, updates, and stored procedures, it doesn't know how to perform database operations. Subclasses must add methods to perform these.

Important 

An RdbmsOperation is a reusable Java object representing a SQL query, update, or stored procedure. RdbmsOperations may have parameters, declared before they are used, which correspond to SQL bind variables. Once configured and "compiled", an RdbmsOperation object can be executed repeatedly, with different parameter values each time.

Note 

The class hierarchy described here is a good example of appropriate use of concrete inheritance. In this case we want to inherit instance variables and implementation, as well as enable polymorphism. As parameterization is largely handled by the com.interface21.jdbc.core package, there is no need to use interface-based design to allow greater flexibility.

The SqlOperation Class

SqlOperation is an abstract class that extends RdbmsOperation to serve as a superclass for SQL-based queries and updates (as opposed to stored procedures). Its compilation implementation checks that the number of bind variables expected in the SQL statement (that is, the number of ? characters) matches the number of SqlParameters declared, and configures a PreparedStatementCreatorFactory that can efficiently create PreparedStatementCreator objects for the SQL and parameters specified at configuration time. The SqlOperation class creates a JdbcTemplate that subclasses can use to perform database operations.

The SqlQuery Class

This is the superclass for all query objects, and uses the JdbcTemplate instance variable inherited from SqlOperation to execute queries given SQL and bind variables.

SqlQuery is abstract, using the Template Method design pattern to defer the extraction of results to subclasses, which are required to implement the following protected abstract method:

    protected abstract ResultReader newResultReader(         int rowsExpected, Object[] parameters); 

We discussed the ResultReader interface earlier: this is defined in the com.interface21.jdbc.core package and saves results from RowCallbackHandler callbacks in a List of objects, each representing the data from one row.

Like the JDO Query interface, the SqlQuery class provides a number of convenient execute() methods with different arguments, such as execute(int), execute(int, int) and execute(String). As with the JDO Query, the execute(Object[] method—which all other execute() methods invoke—takes an array of Object as an argument. The arguments to execute() methods represent the dynamic values of the bind variables declared in the SqlQuery object. All execute() methods return a list of results, produced by the SqlQuery's ResultReader implementation.

The SqlQuery class also exposes a number of convenient findObject() methods. These are analogous to single-object entity bean finders, such as findByPrimaryKey(); they raise an error if more than one object matches.

Subclasses can either rely on the inherited execute() or findObject() methods, or implement their own query methods with meaningful names. For example, a subclass method that required four parameters might take an object argument and conceal the work of invoking the generic superclass method:

    public List purchasesByAddress (Address a) {      return execute (new Object[] {        a.getStreet(), a.getLine2() , a.getPostCode()      });    } 

A subclass finder that returned a single Seat object might be implemented as follows, concealing the necessary type cast from code using it:

    public Seat findSeat(int seatid) {      return (Seat) super .findObject (seatId);    } 

A subclass method might also convert a List returned by an inherited execute() method to a typed array, as the subclass would know what application object represented each row of the query's result.

The ManualExtractionSqlQuery Class

Application queries don't usually extend SqlQuery directly, but instead subclass the abstract ManualExtractionSqlQuery subclass of SqlQuery. The ManualExtractionSqlQuery class uses the Template Method design pattern to force subclasses to implement the following abstract method, which is invoked for each row of the ResultSet resulting from the query. However, this is much simpler for subclasses to implement than the SqlQuery newResultReader() method:

    protected abstract Object extract (ResultSet rs, int rownum)      throws SQLException; 

The implementation will look like that of the RowCallbackHandler interface we looked at above. The ManualExtractionSqlQuery class takes care of building a List from each returned object. The following implementation of the extract() method creates a hypothetical, application-specific Customer object from each row of a returned ResultSet:

    protected Object extract (ResultSet rs, int rownum) throws SQLException {      customer cust = new Customer();      cust.setForename(rs.getstring("forename"));      cust.setid(rs.getint("id"));      return cust;    } 

Note that subclasses of RdbmsOperation don't need to catch SQLExceptions. Any SQLException thrown will be handled by the JdcTeplate class, as discussed above, causing a generic data access exception to be thrown. Note also that subclass code can easily set multiple property values based on a single column value. We'll look at several complete subclasses of the ManualExtractionSqlQuery class below, when we look at using this framework.

You may be wondering why I didn't go the extra mile and do away with the "manual extraction" of an object from each row of the ResultSet. It would be possible to implement a ReflectionExtractionSqlQuery that used reflection to create a JavaBean and set bean properties from column values from each row of the ResultSet. This is appealing as it further reduces the amount of application code required.

I'm normally optimistic about use of reflection and I've implemented this approach several times. However, I'm not convinced that it adds any real value. It has the following disadvantages:

  • It makes the framework significantly more complex.

  • It arguably moves complexity, rather than eliminating it. For example, it will be necessary to use mappings controlling conversion from RDBMS column values to JavaBean properties. Even if mappings are held outside Java code, they will need to be created and maintained.

  • It doesn't allow for computed properties such as properties based on the value of several columns, without the introduction of great complexity in the framework.

  • It doesn't allow different object types to be used for different rows (for example, if a subclass is sometimes indicated).

This decision shows the Pareto Principle in action. The saving of a relatively few lines of code to extract values from a single row of a ResultSet (which are no longer very complex because there is no need to catch SQLExceptions) doesn't add sufficient value to justify the complexity involved.

The SqlFunction Class

SQL functions can be viewed as special cases of SQL queries, returning a single row. Thus, we can easily apply the same approach. The com.interface21.jdbc.object.SqlFunction class is a simple concrete class that extends ManualExtractionSqlQuery to enable queries whose results can be held in a java int to be run simply by providing SQL and declaring parameters. A SqlFunction object can be constructed as follows:

    SqlFunction freeSeatsFunction = new SqlFunction(dataSource,      "SELECT count(seat_id) FROM available_seats WHERE performance_id = ?");    freeSeatsFunction.declareParameter(new SqlParameter(Types.NUMERIC));    freeSeatsFunction.compile() 

This SqlFunction can then be used like this:

    freeSeatsFunction.run(performanceId); 

As with the SqlQuery class, the SqlFunction class provides a number of convenient run() methods with different arguments, as well as a generic form that takes an array of Object.

The SqlUpdate Class

Updates share many concepts with queries, such as SQL, bind variable declarations, and the use of a JdbcTemplate class to help implementation. Hence the SqlUpdate class extends SqlOperation, inheriting the JdbcTemplate helper and the validation logic that checks that bind variable declarations tally with the supplied SQL.

The SqlUpdate class is concrete, as there are no results to extract and no need for subclasses to implement custom extraction. It exposes a number of update() methods, each returning the number of rows affected by the update. As with query methods, all update() methods invoke a generic update method that takes an array of Object parameter values:

    public int update(Object[] args)      throws InvalidDataAccessApiUsageException 

The StoredProcedure Class

Our modeling naturally supports stored procedures as well as ordinary SQL queries and updates. The abstract StoredProcedure class extends RdbmsOperation, as it doesn't require a JdbcTemplate helper and, as the supplied SQL is merely the name of the stored procedure, it is impossible to validate it against bind variable declarations. (Only when the stored procedure is invoked at run time will an incorrect number of parameters cause a failure.)

Calling a stored procedure using JDBC directly involves creating an object of the Java.sql.CallableStatement interface and providing a call string. Call strings include placeholders like the ones used for JDBC prepared statements, and look like the following example, used in the sample application:

    {call reserve_seats(?, ?, ?, ?)} 

Once a CallableStatement object is created, invoking the stored procedure requires similar error handling to queries and updates. Stored procedures can return ResultSets, but more often we use output parameters. (The mechanics of getting stored procedures to return ResultSets varies between RDBMSs. It's quite complex in Oracle.)

The StoredProcedure class must be subclassed by application-specific classes. Each subclass effectively becomes a Java proxy for the stored procedure. The only major difference from the queries and updates we've seen is that a stored procedure can have input/output as well as input parameters. The StoredProcedure class automatically builds the call string and conceals the use of a CallableStatement and the necessary error handling. Input parameters are supplied and output parameters returned in java.util.Map objects.

Using the JDBC Object Abstraction

Now that we've taken a look at the implementation of this object-based JDBC abstraction, let's look at using it to perform common tasks.

Performing Queries

JDBC is very efficient at performing queries, although we cannot transparently update objects created from JDBC queries. We can, however, perform queries with many parameters and involving complex joins that cannot easily be accomplished using O/R mapping. The objects resulting from JDBC queries are ideal value objects, as they're disconnected from the database and have no dependencies on persistence APIs.

The following three code examples illustrate three idioms that can be used with RdbmsOperations and subclasses.

Application-specific queries will normally subclass ManualExtractionsqlQuery. As a minimum, we will need to implement the protected abstract extract() method. The following anonymous inner class implements just this method. Note that the query is configured using the configuration methods inherited from RdbmsOperation:

    SqlQuery customerQuery = new ManualExtractionSqlQuery() {      protected Object extract (ResultSet rs, int rownum) throws SQLException {        Customer cust = new Customer();        cust.SetForename (rs.getString ("forename"));        cust.setId(rs.getInt ("id"));        return cust;      }    };    customerQuery.setDataSource (ds);    customerQuery.setSql("SELECT id AS id, forename AS forename FROM " +                         "customer WHERE id=?");    customerQuery.declareParameter (new SqlParameter (Types.NUMERIC));    customerQuery.compile(); 

This query can be executed with one of the convenient execute() methods from SqlQuery as follows:

    List 1 = customerQuery.execute(1); 

The following query implementation is slightly more sophisticated. It hides the SQL, parameter declaration, and compilation process into the constructor, providing better encapsulation. The implementation of the extract() method is the same as in our first example:

    private class CustomerQuery extends ManualExtractionsqlQuery {      public CustomerQuery (DataSource ds) {        super (ds, "SELECT forename, id FROM customer WHERE id=?");        declareParameter (new SqlParameter (Types. NUMERIC));        compile();      } 

      protected Object extract (ResultSet rs, int rownum) throws SQLException {        Customer cust = new Customer();        cust.setForename (rs .getString ("forename"));        cust.setId(gtnt("id");        return cust;      }    } 

It is simpler to create objects of this type, but we must still rely on the inherited execute() methods. This query object can be used as follows:

    SqlQuery customerQuery = new CustomerQuery (dataSource);    List customers = customerQuery.execute(6); 

As RdbmsOperation objects are threadsafe, we will typically construct such objects once, and hold them as instance variables in Data-Access Objects.

The following version is a more sophisticated query that not only hides SQL, parameter declaration and compilation inside its constructor, but implements a new query method, which enables it to take a combination of parameters for which there is no convenient execute() method in the SqlQuery class:

    class CustomerQuery extends ManualExtractionSqlQuery {      public CustomerQuery (DataSource ds) {        super (ds, "SELECT id AS id, forename AS forename FROM customer " +               "WHERE mystring=? AND myint=? AND string3=?");        declareParameter (new SqlParameter (Types. VARCHAR));        declareParameter (new SqlParameter (Types. NUMERIC));        declareParameter (new SqlParameter (Types. VARCHAR));        compile();      }      protected Object extract (ResultSet rs, int rownum) throws SQLException {        Customer cust = new Customer();        cust.setForename (rs.getString("forename"));        cust.setId(rs.getINt("id"));        return cust;      } 

The new query method can take strongly typed parameters, and can be given a meaningful name:

    public List findWithMeaningfulName(        String myString, int id, String string3) {      return execute (new Object[] {        myString, new Integer (id), string3 } );      }    }; 

We can use this as shown below. Note that this code is self-documenting:

    CustomerQuery customerQuery = new CustomerQuery (ds);    List 1 = customerQuery. findWithMeaningfulName ("foo",1, "bar"); 

It's easy to avoid code duplication in practice by the use of inheritance among query objects. For example, the implementation of the extract() method can be provided in a base class, while subclasses provide different SQL and variable declarations. When only the SQL WHERE clause varies, multiple query objects of the same class can be created, each configured to execute different SQL.

Peforming Updates

Using a SQL-based JDBC approach we can't have true O/R mapping, which typically results in transparent updates when object properties change. However, we can achieve efficient updates where O/R mapping is inappropriate, such as updates affecting multiple rows and updates using stored procedures.

The following update object performs the same update as the JdbcTemplate update shown above, but conceals the SQL used and the parameter declarations in its constructor:

    class PerformanceCleaner extends com.interface21.jdbc.object.SqlUpdate {      public PerformanceCleaner (DataSource dataSource) {          SetSql ("UPDATE seat_status SET booking_id = null" +                  "WHERE performance_id = ? AND price_band_id = ?");        setDataSource (dataSource);        declareParameter (new SqlParameter (Types.NUMERIC));        declareParameter (new SqlParameter (Types.NUMERIC));        compile();      }      public int clearBookings (int performanceId, int type) {          return update(new Object [] {        new Integer (performanceId), new Integer (type) });      }    } 

The clearBookings() method invokes the superclass update (Object[]) method to execute with the given parameters, simplifying the API for callers—the same approach we've previously seen for query execute() methods.

This update object can be used as follows:

    PerformanceCleaner pc = new PerformanceCleaner (dataSource);    pc.clearBookings (1, 1); 

Calling Stored Procedures

As the stored procedure invocations in our sample application are fairly complex and use proprietary Oracle features, let's consider an example outside the sample application of calling a stored procedure that has two numeric input parameters and one output parameter.

The constructor is very similar to those of the queries and updates we've seen, declaring parameters and invoking the compile() method. Note that the SQL is the name of the stored procedure:

    class Addinvoice extends com.interface21.jdbc.object.StoredProcedure {      public Addinvoice (DataSource ds) {        setDataSource (ds);        setSql ("add_invoice");        declareParameter (new SqlParameter ("amount", Types .INTEGER));        declareParameter (new SqlParameter ("custid", Types.INTEGER) );        declareParameter (new OutputParameter ("newid", Types.INTEGER));        compile();      } 

We must implement a method to execute the stored procedure. (Although I've used the name execute(), we could give this method any name.) The highlighted line is a call to the StoredProcedure class's protected execute (Map) method. We invoke this with a Map of input parameters built from the new method's arguments. We build a return value from a Map of output parameters returned by the execute() method. This means that code using our stored procedure object can use strong typing. For more complex stored procedures, arguments and return values could be of application object types:

   public int execute(int amount, int custid) {     Map in = new HashMap();     in.put ("amount"))'     new Integer (amount));     in.put ("custid", new Integer (custid));     Map out = execute (in);                                                                                                     Number Id = (Number) out.get ("newid");     return Id. intValue();   } } 

Just 20 lines of Java code, and we have an object that could implement an interface that's not stored procedure or JDBC-specific.

Note 

An improvement in JDBC 3.0 makes it possible to use named parameters, instead of indexed parameters, with the CallableStatement interface. This brings into the JDBC API one of the features of the StoredProcedure class I've presented. However, it still makes sense to use a higher level of abstraction than the JDBC API, as the error handling issue remains.

JDBC Abstraction Summary

The abstractions we've just described aren't the only valid approach to making JDBC easier to work with. However, they are much preferable to using JDBC directly and can drastically reduce the amount of code in applications and the likelihood of errors.

The biggest gains concern error handling. We've seen how a generic hierarchy of data-access exceptions can give application code the ability to react to specific problems, while allowing it to ignore the majority of unrecoverable problems. This also ensures that business objects don' t require any knowledge of the persistence strategy (such as JDBC) used by the application.

We've looked at two levels of abstraction. The com.interface21.jdbc.core package, which uses callback interfaces to enable JDBC workflow and error handling to be managed by the framework, makes JDBC easier to use, but leaves application code to work with JDBC) PreparedStatements and ResultSets.

The com.interface21.jdbc.object package builds on the com.interface21.jdbc.core package to offer a higher level of abstraction in which RDBMS operations (queries, updates, and stored procedures) are modeled as reusable objects. This is usually a better approach, as it localizes SQL operations within RdbmsOperation objects and makes code using them simple and largely self-documenting.

Unlike most O/R mapping approaches, these abstractions don't sacrifice any control over use of the RDBMS. We can execute any SQL we like; we can easily execute stored procedures. We've simply made JDBC easier to use.

The alert reader will have spotted that I've ignored my own advice (in Chapter 4) about externalizing strings. The classes shown above include SQL string literals—not even constants. The desirability of separating SQL from Java code is questionable. While we should always externalize configuration strings, SQL is not really configuration data, but code. If the SQL changes, the behavior of the Java code that uses it may also change, so externalizing SQL strings may be unwise. For example, imagine that we have two versions of the same query, one with FOR UPDATE suffixed. These are not the same query—they'll behave very differently. As making it easy to change the SQL without changing the Java code can be dangerous, the SQL belongs in DAOs. (In contrast, making it easy to change configuration without changing code is wholly beneficial.)

Note that while we may not want to separate SQL from Java, we definitely do want to localize all SQL in DAO implementations. Not only does this make it easy to change the SQL if necessary, but it hides SQL from the rest of the application.

Application code using these JDBC abstraction packages is easy to test. As all framework classes take connections from a datasource, we can easily provide a test datasource, enabling data access code to be tested without an application server. As any code running within a J2EE server can obtain a managed DataSource from JNDI, we can use such DAOs inside or outside an EJB container, boosting architectural flexibility.

The framework code itself is relatively simple. It doesn't attempt to solve really complex problems: it just removes the obstacles that make working with JDBC awkward and error-prone. Hence, it doesn't have a steep learning curve. For example, this approach doesn't provide any special handling for locking or concurrency. Essentially, it lets us use SQL with a minimum of hassle. It's up to us to ensure proper behavior for our target RDBMS.

Similarly, the JDBC abstraction interface doesn't make any effort at transaction management. The "global" JTA API or EJB CMT should be used for managing transactions. If we use the JDBC API to manage transactions we deprive the J2EE server of the ability to enlist multiple resources in the same transaction and to roll back all operations automatically if necessary.

Note 

Floyd Marinescu describes the "Data Access Command Bean" pattern in EJB Design Patterns, giving examples and common superclasses for JDBC. This approach has similar goals to the approach described here, but differs in that data-access objects are commands (intended for single use), the API is based on the javax.sql.RowSet interface, and application code is forced to catch exceptions when extracting each column value from the result set. When working with many database columns, this will prove extremely verbose. (Nor is there any concept of data store-agnostic exception handling: application code is left to use JDBC error handling directly.) The "Data Access Command Bean" approach is preferable to using JDBC directly, but I believe that the approach described in this chapter is superior.

An RdbmsOperation is not a command. While commands are typically created per use case, an RdbmsOperation is created once and reused. However, the RdbmsOperation model is compatible with the Command design pattern. Once created and configured, an RdbmsOperation can execute commands repeatedly.



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