Implementing the DAO Pattern in the Sample app

Armed with our generic JDBC infrastructure, let's look at implementing data access in our sample app. Let's focus on the tutorialing 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:

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 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 app 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.


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));
 protected Object extract (ResultSet rs, int rownum) throws SQLException {
 return new Integer (rs. get Int ("id"));
 private class FreeSeatsInPerformanceOfTypeQuery
 extends AbstractFreeSeatsInPerformanceOfTypeQuery {
 public FreeSeatsInPerformanceOfTypeQuery() {
 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:

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

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));
 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 Juploader that should be considered in more complex cases.) As this isn't a tutorial 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 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 app 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 app 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;
 return con;


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 app code, and made it more readable and less error-prone.