Implementing the DAO Pattern in the Sample Application

Armed with our generic JDBC infrastructure, let's look at implementing data access in our sample application.

Let's focus on the booking process. To use the DAO pattern, we need to separate persistence logic operations from business logic operations. The first step is to try to create a technology-agnostic DAO interface, which we can then implement with whatever persistence technology we choose.

We'll look at the implementation of business logic in the next chapter. For now, let's look at the most important methods on the BoxOffice interface—the public interface of the ticket reservation system:

    public interface BoxOffice {      int getSeatCount (int performanceId);      int getFreeSeatCount (int performanceId);      SeatQuote allocateSeats (SeatQuoteRequest request)        throws NotEnoughSeatsException;      // Additional methods omitted    } 

For now, we can ignore the details of the SeatQuote and SeatQuoteRequest parameter classes. The PriceBand class is a read-only object that we'll examine below.

We want to keep the seat allocation algorithm in Java. In this case, it's a simple exercise to separate out the persistence operations required into a DAO interface. (Often this separation is harder to accomplish; occasionally it is impossible.) We must try to ensure that the interface, while not dictating a persistence strategy, allows for efficient implementation. A suitable DAO interface will look like this:

    public interface BoxOfficeDAO {      /**       * @return collection of Seat objects       */      List getAllSeats (int performanceld) throws DataAccessException;      /**       * @return list of PriceBand objects for this performance       */      List getPriceBands (int performanceld) throws DataAccessException;      /**       * @return list of Integer ids of free seats       * @param lock guarantees that these seats will be available       * to the current transaction       */      List getFreeSeats(int performanceld, int classld, boolean lock)        throws DataAccessException;      int getFreeSeatCount(int performanceld) throws DataAccessException;      String reserve(SeatQuote quote);      // Purchase operation omitted    } 

Nothing in this interface ties us to using JDBC, Oracle, or even an RDBMS. Each of these methods throws our generic com.interface21.dao.DataAccessException, ensuring that even in the event of error, business objects using it don't need to work with RDBMS concepts.

The third, lock parameter to the getFreeSeats() method allows us to choose programmatically whether to lock the seats returned. If this parameter is true, the DAO must ensure that the seats with the returned IDs are locked against reservation by other users, and are guaranteed to be able to be reserved in the current transaction. The lock will be relinquished once the current transaction completes. If this parameter is false, we want to query current availability without impacting on other users, to display information, rather than as part of the reservation process.

This interface doesn't encompass transaction management, which should be accomplished using JTA. We plan to use a stateless session EJB with CMT to handle this declaratively, but don't need to worry about transaction management to implement and test the DAO.

Now we've defined a DAO interface, we should write tests against it. Tests involving persistent data tend to be verbose, so we won't show the test classes here, but this step is very important. Once we have a test harness, we can use it to test any DAO implementation; this will prove invaluable if we ever need to migrate to another persistence technology or database. To create effective tests, we must:

• Ensure that we create test data before each test run. We must never run such tests against a production database. We may create test data in the setUp() method of a JUnit test case, or using Ant's SQL capability before the test case is run.

• Write test methods that verify database work. For each test, we will write code that checks the results of the operation in question, connecting directly to the database. Using our JDBC abstraction API (which we can assume has already been tested) we can issue queries without writing too much code.

• Ensure that we return the database to its old state. We will have committed many transactions during the test run, so we won't be able to roll back, so this step may be complex. We can use the tearDown() method of a JUnit test case or Ant or a similar tool.

We can write and run tests before we attempt a real implementation of our DAO interface, by using a dummy implementation in which all tests should fail. (The dummy implementation's methods should do nothing, return null, or throw java.lang.UnsupportedOperationException to check the test harness. Any IDE will help us create such a dummy implementation of an interface without manual coding.)

Now that we have a complete test harness, let's look at implementing our DAO interface using JDBC. (We could use SQLJ, but with our JDBC abstraction layer it's hardly necessary.) See the com.wrox.expertj2ee.ticket.boxoffice.support.jdbc.OracleJdbcSeatingPlanDAO class for a full listing of the implementation.

Using the object-based JDBC abstraction described above, we will create an RdbmsOperation object for each query, update, and stored procedure invocation required. All the RdbmsOperation objects used will be modeled as inner classes, as they should only be visible inside the OracleJdbcSeatingPlanDAO class. This also has the advantage that the enclosing class's DataSource member variable ds is visible, simplifying object construction.

First, let's consider the implementation of the PriceBand query, which returns lightweight read-only objects (an ideal application for JDBC):

    private class PriceBandQuery extends ManualExtractionSqlQuery {      public PriceBandQuery() {        super (ds, "SELECT id AS class_id, price AS price " +               "FROM price_band WHERE price_band.price_structure_id = " +               "(SELECT price_structure_id FROM performance WHERE id = ?) " +               "ORDER BY price DESC");        declareParameter (new SqlParameter ("price_structure_id",                          Types. NUMERIC));        compile() ;      }      protected Object extract (ResultSet rs, int rownum) throws SQLException {        int id = rs.getlnt ("class_id");        double price = rs.getDouble ("price");        return new PriceBand(id, price);      }    } 

This shows a slightly more complex query than we've seen so far. The fact that the SQL accounts for much of the object's code shows that we have a concise Java API! The query behind the getAllSeats() method will be similar.

We declare a PriceBandQuery object as an instance variable in the OracleJdbcSeatingPlanDAO class, and initialize it in the constructor as shown below:

    private PriceBandQuery priceBandQuery;    ... 

    this. priceBandQuery = new PriceBandQuery(); 

Using the PriceBandQuery instance, the implementation of the getPriceBands() method from the BoxOfficeDAO interface is trivial:

    public List getPriceBands (int performanceld) {      return (List) priceBandQuery. execute (performanceld);    } 

Now let's consider the queries for available seats. Remember that the database schema simplifies our task in two important respects: it provides the AVAILABLE_SEATS view to prevent us needing to perform an outer join to find available seats, and it provides the reserve_seats stored procedure that conceals the generation of a new primary key for the BOOKING table and the associated updates and inserts. This makes the queries straightforward. To get the ids of the free seats of a given type for a performance, we can run a query like the following against our view:

    SELECT seat_id AS id FROM available_seats            WHERE performance_id = ? AND price_band_id = ? 

To ensure that we honor the contract of the getFreeSeats() method when the lock parameter is true, we need a query that appends FOR UPDATE to the select shown above. These two queries are quite distinct, so I've modeled them as separate objects.

Note 

Two points to consider here: Oracle, reasonably enough, permits SELECT…FOR UPDATE only in a transaction. We must remember this, and ensure that we have a transaction, when testing this code; and the FOR UPDATE clause used against a view will correctly lock the underlying tables.

Both queries share the same parameters and extraction logic, which can be gathered in a common base class. The usage in which an abstract superclass implements the extraction of each row of data, while subclasses vary the query's WHERE clause and parameters is very powerful; this is merely a trivial example. The two queries, and their superclass, will look like this:

    private static final String FREE_SEATS_IN_CLASS_QUERY_SQL =          "SELECT seat_id AS id FROM available_seats" +          "WHERE performance_id = ? AND price_band_id = ?";    private abstract class AbstractFreeSeatsInPerformanceOfTypeQuery        extends ManualExtractionSqlQuery {      public AbstractFreeSeatsInPerformanceOfTypeQuery (String sql) {        super (ds, sql);        declareParameter (new SqlParameter ("performance_id", Types. NUMERIC));        declareParameter (new SqlParameter ("price_band_id", Types. NUMERIC));        compile();      }      protected Object extract (ResultSet rs, int rownum) throws SQLException {        return new Integer (rs. get Int ("id"));      }    }    private class FreeSeatsInPerformanceOfTypeQuery        extends AbstractFreeSeatsInPerformanceOfTypeQuery {      public FreeSeatsInPerformanceOfTypeQuery() {        super (FREE_SEATS_IN_CLASS_QUERY_SQL);      }    }    private class LockingFreeSeatsInPerformanceOfTypeQuery        extends AbstractFreeSeatsInPerformanceOfTypeQuery {      public LockingFreeSeatsInPerformanceOfTypeQuery() {        super (FREE_SEATS_IN_CLASS_QUERY_SQL + " for update");      }    } 

The OracleJdbcSeatingPlanDAO constructor (after a DataSource is available) can create new objects of each of these two concrete classes like this:

    freeSeatsQuery = new FreeSeatsInPerformanceOfTypeQuery();    freeSeatsLockingQuery = new LockingFreeSeatsInPerformanceOfTypeQuery(); 

We can now query for free seats with either of these queries by calling the execute(int, int) method from the SqlQuery superclass. We use the lock parameter to the getFreeSeats() method from the SeatingPlanDAO interface to choose which query to execute. The complete implementation of this method is:

    public List getFreeSeats (int performanceld, int classld, boolean lock) {      if (lock) {        return freeSeatsLockingQuery. execute (performanceld, classld);      } else {        return freeSeatsQuery. execute (performanceld, classld);      }    } 

Calling the stored procedure to make a reservation is a little more complicated. Although we've implicitly coded to Oracle by relying on our knowledge of Oracle's locking behavior, so far we haven't done anything proprietary. As the PL/SQL stored procedure takes a table parameter, enabling the IDs of the seats to be reserved to be passed in a single database call, we need to jump through some Oracle-specific hoops.

First, let's consider what we need to do in the database. We need to define the following custom types:

    CREATE or REPLACE TYPE seatobj AS object (id NUMERIC);    /    CREATE or REPLACE TYPE seat_range AS table OF seatobj;    / 

Now we can use the seat_range type as a table parameter to the following PL/SQL stored procedure:

    CREATE or REPLACE    PROCEDURE reserve_seats (                     perf_id IN NUMERIC,                     seats IN seat_range,                     hold_till DATE,                     new_booking_id OUT NUMBER)    AS    BEGIN          -- Get a new pk for the booking table          SELECT booking_seq.nextval INTO new_booking_id FROM dual;          -- Create a new booking         INSERT INTO booking(id, date_made, reserved_until)              VALUES (new_booking_id, sysdate, hold_till);          -- Associate each seat with the booking          FOR i in 1..seats. count LOOP           UPDATE seat_status                     SET booking_id = new_booking_id                     WHERE seat_id = seats (i). id                     AND performance_id = perf_id;           END LOOP;    END;    / 

To pass a Java array as the table parameter to the stored procedure, we need to perform some Oracle-specific operations in our JDBC as well.

The StoredProcedure superclass allows us not merely to pass in a Map to the execute() method, but also to pass in a callback interface that creates a parameter map given a Connection:

    protected interface ParameterMapper {      Map createMap (Connection con) throws SQLException;    } 

This is necessary when we need the Connection to construct the appropriate parameters, as we do in this case. The reserve_seats stored procedure object needs to make the database types seat and seat_range available to JDBC, before executing the stored procedure. These types can only be made available using an Oracle connection. Apart from this, the process of creating an input parameter Map and extracting output parameters is as in the simple StoredProcedure example we looked at earlier:

    private class SeatReserver extends StoredProcedure {      public SeatReserver (DataSource ds) {         super (ds, "reserve_seats");         declareParameter (new SqlParameter("perf_id", Types.INTEGER));         declareParameter (new SqlParameter("seats", Types.ARRAY));         declareParameter (new SqlParameter("hold_till", Types.TIMESTAMP));         declareParameter (new OutputParameter("new_booking_id", Types.INTEGER));         compile();}      }      public int execute (final int performanceId, final int[] seats) {        Map out = execute (new StoredProcedure.ParameterMapper(){          public Map createMap (Connection con) throws SQLException {            con = getOracleConnection(con);            //Types MUST be upper case            StructDescriptor sd = StructDescriptor.createDescriptor(               "SEATOBJ", con);            ArrayDescriptor ad = ArrayDescriptor.createDescriptor(               "SEAT_RANGE", con);            Object[] arrayObj = new Object [seats.length];              for (int i = 0; i arrayObj.length; i++){                arrayObj[i] = new Object[] { new Integer (seats[i])};                //System.out.println("Will reserve seat with id " +                //  new Integer (seats [i]));            }            //Need Con to create object (association with Map)            ARRAY seatIds = new ARRAY(ad, con, arrayObj);            Map in = new HashMap();            in.put("perf_id", new Integer (performanceId));            in.put("seats", seatIds);            Timestamp holdTill = new Timestamp(System.currentTimeMillis()                                              + millisToHold);            in.put("hold_till", holdTill);            return in;          }        ));        Number Id = (Number) out.get("new_booking_id");        return Id.intValue();      }    } 

Working with a custom type in the database increases the complexity of JDBC code, whatever abstraction layer we use. (Type descriptors can be used to take advantage of object-relational features in Oracle and other databases, although there are simpler approaches such as Oracle's JPublisher that should be considered in more complex cases.) As this isn't a book on advanced JDBC or Oracle JDBC, we won't dwell on the details of this listing: the point is to show how we can use proprietary RDBMS features without ill effect to our architecture, as they are gathered in a single class that implements a common interface.

Note 

Note that we use a java.sql.Timestamp to hold the Oracle DATE type. If we use a java.sql.Date we will lose precision.

The highlighted line shows that this StoredProcedure requires the ability to obtain an Oracle-specific connection from the connection it is given by its datasource. In most application servers, datasources will return wrapped connections rather than an RDBMS vendor-specific connection. (Connection wrapping is used to implement connection pooling and transaction control.) However, we can always obtain the underlying connection if necessary. We need to override the OracleJdbcSeatingPlanDAO getOracleConnection() method, which takes a pooled Connection and returns the underlying Oracle-specific connection, for each application server. The following override, in the JBoss30OracleJdbcSeatingPlanDAO class, will work for JBoss 3.0:

    protected Connection getOracleConnection (Connection con) {      org.jboss.resource.adapter.jdbc.local.ConnectionInPool cp=        (org.jboss.resource.adapter.jdbc.local.ConnectionInPool) con;      con=cp.getUnderlyingConnection();      return con;    } 

Note 

For the complete listing of the OracleJdbcSeatingPlanDAO and JBoss30OracleJdbcSeatingPlanDAO classes, see the download.

By using the DAO pattern, we have been able to use Oracle-specific features without affecting the portability of our architecture. We have a simple interface that we can implement for any other database, relational, or otherwise. By using a JDBC abstraction library, we have greatly simplified application code, and made it more readable and less error-prone.