Creating a Table

The first step to writing a DAO is to have a database table. (Without a database table you can waste trillions of man-hours wondering why your object doesn't work.) Here I use a table called "Member" for storing registered members of an online community; see Example 1. This table covers the basic data types used in most applications. The support classes are easily extendable. The primary key for Member is MemberID, a unique number generated in code. Both the VARCHAR2 and CHAR string data types are represented. The EmailBouncing column is useful for flagging members who may have recently changed e-mail addresses. Oracle is the database I use here, and as typical with Oracle schemas, a CHAR(1) field with values of T and F is used to define a Boolean type. I also use Oracle's Date type to store the member's registration date.

The DAO Interface

For simplicity, my access object is a bean, but not a full-fledged Enterprise JavaBean (EJB). This doesn't mean that the data access code for an EJB is more complex. Rather, an EJB introduces details that are not of concern here. For example, my insert method has a method signature of boolean create(StringBuffer ErrorMessages) and returns False on failure; whereas, in EJB it would be void ejbCreate and throw an EJB exception on failure. With minor tweaks, everything presented here is easily transferable to EJBs.

The Member class begins with private member variables and public accessors and mutators. For the MemberID property, I use the Long wrapper class instead of the primitive long Java type, because its utility methods are useful for clients of the Member class. For the EmailBouncing property I use the boolean primitive type instead of the Boolean wrapper because the Boolean type offers little in terms of usefulness due to its limited number of methods. Of course, these choices are a matter of personal preference. RegistrationDate is exposed as a java.util.Date. It's important for clarity to fully qualify the package name, because there is also a java.sql.Date class.

With the addition of a constructor, the class now resembles Listing One(available electronically; see "Resource Center"). The constructor initializes MemberID to a unique Long by using System.currentTimeMillis(), which returns the number of milliseconds since midnight, January 1, 1970. In a distributed application with web servers hitting a single database server, you want to append the web server's IP address as well as other fields to guarantee a unique identifier. To keep things simple, I'm overlooking these details. I made MemberID a NUMBER(14) in the table. The currentTimeMillis() method currently returns a 12-digit number, but on Saturday, September 8, 2001 at 8:46:40 pm it rolls over and increases to a 13-digit number. Although I'm not superstitious, I'd rather add an extra digit and make the column a NUMBER(14) instead of 13. Then I can rest safe and sound until the year 5138.

With the basic class skeleton in place and without going further, it is time to write the test code. The first step is to override the toString() method inherited from java.lang.Object. This method should return a string that reflects the object's current state. Every Java class should override this method to aid debugging and testing. I've written Member::toString() so the call System.out.println( memberInstance ); results in the output in Example 2 if called immediately after object creation.

Next is the actual test program. Every DAO test class should, at a minimum, insert a test row, find it again, update it, and then delete it. I could easily do this following my own conventions for checking return codes, displaying errors, and the like. Instead, my MemberTester class uses the JUnit testing framework. Written by industry-heavyweights Eric Gamma and Kent Beck, it is rapidly becoming the most popular unit testing tool for Java. A more detailed discussion of JUnit is beyond the scope of this article, but please refer to the MemberTester code available electronically and at http://www.JUnit.org/.

Creating A PreparedStatement

Now it's time to get into the real code — the JDBC calls. I begin with the create() method for inserting a new row into the table. The first step is to obtain a database connection. Every development project seems to obtain the database connection differently. Sometimes it's passed into the DAO method. In other cases, it is a single Connection instance or a connection pool object retrieved from either the ServletContext or the EJBContext. To avoid tying the examples presented here to the ServletContext or EJBContext, I'm using a basic ConnectionPool class. It implements the singleton pattern by exposing a static method for obtaining a single, shared instance. The details for writing a ConnectionPool are beyond the scope of this article, but nearly every book on Java server programming has its own implementation of one. I'm using one largely based on that presented in Marty Hall's Core Servlets and Java Server Pages (Prentice Hall, 2000) because it is one of the best I've seen. Retrieving a connection is straightforward:

DBConnectionPool dbConnectionPool =

DBConnectionPool.getInstance();

Connection dbConnection = dbConnection- Pool.getConnection();

Next is the process of constructing the database statement. Of the three statement types available in JDBC, I use prepared statements for executing the SQL statements, but not for the typical efficiency reasons. (For more on JDBC statements see the accompanying text box entitled "Make a Statement with JDBC.") A PreparedStatement is normally preferred when executing the same SQL statement "definition" multiple times, each time changing its parameters. With DAOs, however, it is rare to execute the same statement multiple times in such a manner. Normally one is created, used once, and destroyed within a single method call. Even in the case of findXxx methods, which return a collection, a single select is usually executed and followed by looping through the result set to build up the collection instead of making multiple selects. Nevertheless, a PreparedStatement still has advantages. First, building a SQL statement by setting the parameters one at a time is easier than concatenating a dynamic SQL statement. Prepared statements also take care of some of the irritating details for you that must be specially handled when building up dynamic SQL. For example, if a user's name includes a single quote ("O'Donnell," for example) and you try to concatenate that into your SQL in the following manner: SQLStringBuffer.append(""" + m_First Name + ''', ");, the SQL statement will fail due to syntax error. With Oracle, the same applies to an ampersand, which Oracle interprets as an escape character. Ampersands must be converted to "^&." However, prepared statements will handle all of this for you. Example 3(a) demonstrates constructing a prepared statement to insert into the Member table.

With the template constructed, the next step is to set the values for the placeholders. Example 3(b) shows the typical JDBC code for setting a Long on a prepared statement. The code lets the Long attribute be null and results in a NULL value in the database instead of a zero. In this case, MemberID should never be null since it is the primary key for the table, but coding for the possibility allows for the creation of a reusable code block that can be moved into a support routine later. Listing Two (available electronically) shows the complete code for the create() method. Strings are handled like longs. For strings, the code passes java.sql.Types.CHAR to Statement.setNull on both CHAR and VARCHAR2 database fields. There is a java.sql.Types.VARCHAR type, but passing java.sql.Types.CHAR in both instances will not affect the outcome (NULL is NULL after all). Doing so allows yet another reusable code block to be constructed. This one provides the additional benefit of encapsulating the code from the exact SQL string types used in the database. Next, the Boolean type m_EmailBouncing must be converted to either "Y" or "N" strings before insertion. The Utilities.booleanToString method takes care of the conversion.

Storing date types is an even more interesting problem. After a brief glance at the JDBC APIs, your first guess might be to convert the java.util.Date type of RegistrationDate into a java.sql.Date and then pass it to PreparedStatement.setDate(). You might be surprised to learn that setDate() only stores the date and not the time. (Similarly, setTime() only stores the time.) However, Oracle's Date type can store the date and the time in a single field. The best solution seems to be the setTimestamp method. Although it is designed to map to the TIMESTAMP SQL type, Oracle does not implement this type, relying solely on its Date type to store both the date and the time. Using setTimestamp() with an Oracle Date works perfectly. By calling m_RegistrationDate's getTime(), the number of milliseconds since January 1, 1970 can be retrieved. This value is passed to the constructor of a java.sql.Timestamp instance, which in turn is passed to setTimestamp().

Reading From a ResultSet

With the create() method complete, the same code blocks used to set different data types into a PreparedStatement can be used in the update()/delete() methods, as well as on the select for the findByEmail() method. When reading values from the ResultSet in findByEmail(), you might think that a mirror image of create() can be performed just by replacing the setXxx() methods with getXxx() methods. Unfortunately, this doesn't work as you might expect.

The problem involves reading database NULL values with JDBC. ResultSet.getInt() returns zero if the value read was a database NULL. There are at least three possible solutions to this problem. The first possibility is to call the wasNull() method on the ResultSet after reading the data into a temporary variable. If it returns True, then the last value read was null, and you can set the Integer attribute to null. Otherwise, an Integer can be created using the temporary int. The second alternative is to use one of the getBigDecimal methods. The method with signature getBigDecimal(int columnIndex, int scale) is deprecated, and getBigDecimal(int columnIndex) is new with JDBC 2.0. The third solution is to use the getObject() method. If a column is null, getObject() always returns null. JDBC maps every data type in the SQL-92 standard to a Java data type. The SQL NUMERIC type (NUMBER in Oracle) maps to java.math.BigDecimal. Thus, the return value of getObject() is cast and stored into a variable of type BigDecimal using this solution.

Any of these styles (and I'm sure there are others) are valid and any one of them may be the best fit with your requirements. In this article I have chosen the third technique of using getObject(). Not wanting to tie myself to using JDBC 2.0 or higher drivers, I discarded the option of using getBigDecimal. (By the time you read this article, the number and variety of JDBC 2.0 drivers will have likely reached the saturation point and dismissed this as a concern.) Another consideration was consistency. In some implementations, when reading strings from a result set, getString() returns the empty string if a column is null. However, getObject() always returns null if a column is null. This may not be as much of a problem now as it was in the earlier days of JDBC, but using getObject() appears to be the safest method all around. Since I use getObject() for Strings, I use it for Integers also.

The EasyStatement Wrapper

With the basic code in place for using a PreparedStatement and reading from a ResultSet, a quick code review is in order. Listings Two and Three (both available electronically) point out a few glaring problems. First, the code is too long and complicated, especially for a table with only seven columns. Second, the common code blocks such as how to read and write a string to the database are repeated in code instead of being properly placed in support routines. Support routines would also solve another problem. Imagine if the DAOs for 300 tables were written like Listings Two and Three. Then, the decision is made to use getBigDecimal() for reading longs as described earlier. Search and replace you say? Did I mention that a dozen or more developers wrote the code for the various 300 data access objects?

What is needed is a wrapper object for PreparedStatement that will hide the implementation details. EasyStatement is just such an object; see Listing Four (available electronically). EasyStatement has only one constructor, and it takes a Connection object as the only parameter. Then the object's client calls setSQL(String SQL), at which point EasyStatement creates its internal PreparedStatement instance. It also sets its internal m_ColumnIndex variable to one. Having the column index handled internally by EasyStatement is very convenient. Then the client calls one of the overloaded write(...) methods. At this point, the client doesn't even need to know what data types are being set in the PreparedStatement nor how each of them needs to be handled. This has all been encapsulated away. Next, the client only needs to call either executeUpdate or executeQuery. All of this is demonstrated in the new code for the create() method in Listing Five (available electronically). It is important to note that EasyStatement does not commit or rollback the transaction. That is left to the DAO object because commit and rollback are logically tied to the connection, not to the statement.

The EasyResultSet Wrapper

The executeUpdate method on EasyStatement returns the number of rows affected by the update as would be expected; however, executeQuery returns an EasyResultSet instead of a normal ResultSet. EasyResultSet is as simple as EasyStatement (see Listing Six, available electronically). It encapsulates an internal ResultSet object and exposes next() and close() methods that are straight pass-throughs to that record set. Unlike EasyStatement, however, it does not have the benefit of method overloading. Instead, the client must call one of the readString(), readInteger(), readLong(), readDate(), or readBoolean() methods. Compared to Listing Three, it is not too much to ask for the results demonstrated in Listing Seven (also available electronically).

Conclusion

The EasyStatement and EasyResultSet wrappers can dramatically simplify data access code. They do so through simple applications of encapsulation. Their design pattern is easily extensible to other data types you may need to handle such as BLOBs, CLOBs, and the like. Using them will save you time, clean up your code, and make future enhancements easier.

DDJ