Database Programming with JDBC & Java: Developing Multi-Tier Applications

by George Reese, Andy Oram (Editor)

Overview

Java and databases make a powerful combination. Getting the two sides to work together, however, takes some effort—largely because Java deals in objects while most databases do not.

This book describes the standard Java interfaces that make portable object-oriented access to relational databases possible and offers a robust model for writing applications that are easy to maintain. It introduces the JDBC and RMI packages and uses them to develop three-tier applications (applications divided into a user interface, an object-oriented logic component, and an information store).

The book begins with a quick overview of SQL for developers who may be asked to handle a database for the first time. It then explains how to issue database queries and updates through SQL and JDBC. It also covers the use of stored procedures and other measures to improve efficiency, where these are available.

But the book's key contribution is a set of patterns that let developers isolate critical tasks like object creation, information storage and retrieval, and the committing or aborting of transactions.

The second edition includes more basics of JDBC and SQL, with more examples, and a deeper discussion about the architecture of a robust, maintainable database application. The second edition also explains the relationship between JDBC and Enterprise JavaBeans.

Product Details

ISBN-13:	9781565926165
Publisher:	O'Reilly Media, Incorporated
Publication date:	09/28/2000
Series:	Java (O'Reilly)
Edition description:	Second Edition
Pages:	348
Product dimensions:	9.16(w) x 7.04(h) x 0.66(d)

About the Author

George Reese has taken an unusual path into business software development. After earning a B.A. in philosophy from Bates College in Lewiston, Maine, George went off to Hollywood where he worked on television shows such as "The People's Court" and ESPN's "Up Close". The L.A. riots convinced him to return to Maine where he finally became involved with software development and the Internet. George has since specialized in the development of Internet-oriented Java enterprise systems and the strategic role of technology in business processes. He is the author of Database Programming with JDBC and Java, 2nd Edition and the world's first JDBC driver, the mSQL-JDBC driver for mSQL. He currently lives in Minneapolis, Minnesota with his wife Monique and three cats, Misty, Gypsy, and Tia. He makes a living as the National Practice Director of Technology Strategy for digital@jwt in Minneapolis.

Read an Excerpt

Chapter 9: Persistence

Mementos and Delegates

One of the key features of a solid persistence architecture is a separation of business logic from persistence logic. This separation is critical for these reasons:

• The skill set required for writing business components is very different from that required for database programming. By separating different kinds of behavior in various components, different people can easily "own" the development and maintenance of those components.

• If a business component is independent of the persistence logic, it requires no changes should the persistence logic change; even if that change involves a migration to a new database engine or even a new persistence mechanism.

You will use two key design patterns to support the separation of business logic from persistence logic: the memento pattern and delegation. BaseEntity specifically delegates its implementation of the Persistent interface in Example 9-2 to a specialized persistence component. This sample code shows how that delegation works:

public final void store(Transaction trans)     throws StoreException {     Memento mem = new Memento(this);       if( !isValid ) {         throw new StoreException("This object is no longer valid.");     }     handler.store(trans, mem); } 

The BaseEntity class references an attribute called handler that is an instance of a class implementing the PersistenceSupport interface. This object is called the delegate. It supports the persistence operations for an entity. Each method delegated to it requires a Transaction object to identify what transaction governs the persistence operation and a memento that captures the entity's current state.

I briefly introduced the classic memento design pattern in Chapter 7, Distributed Application Architecture. The memento pattern enables an object's state to be decoupled from its implementation. In order to perform a persistence operation, the delegate depends only on the Memento class.[1] It gets all of the entity's state information from that memento. As a result, an entity can go through major code changes without any impact on its persistence delegate. Using these two tools, you now have a system for which a business component has no dependencies on the underlying data model, and a persistence delegate has no depencies on the business component it persists. Example 9-3 shows the generic PersistenceSupport interface.

Example 9-3: The PersistenceSupport Interface for Delegating Persistence Operations

package com.imaginary.lwp;   import java.util.Collection;   public interface PersistenceSupport {     public abstract void create(Transaction trans, Memento mem)         throws CreateException;       public abstract Collection find(Transaction trans, SearchCriteria sc)         throws FindException;       public abstract void load(Transaction trans, Memento mem)         throws LoadException;       public abstract void remove(Transaction trans, Memento mem)         throws RemoveException;          public abstract void store(Transaction trans, Memento mem)         throws StoreException; } 

This interface contains no mention of JDBC or of the entity it is saving. It knows only about its transaction context and the memento.

JDBC Persistence

Now that you have a general foundation for object persistence, you can use these classes to create a JDBC-based persistence package. The generic library has set aside implementations of the PersistenceSupport and Transaction interfaces as the places where data store-specific persistence operations should occur. To create a database persistence library, you thus need to create database-specific extensions of these two classes.

Here you get the chance to put your JDBC skills to use. I already showed how a JDBCTransaction class might implement commit( ) and rollback( ) methods. JDBC support requires still more work. You need to create JDBC Connection instances used to talk to the database. You also need to write the actual methods that talk to the database. A getConnection( ) method in the JDBCTransaction class takes care of the first problem:

public Connection getConnection(  ) throws SQLException {     if( connection == null ) {              Context ctx = new InitialContext(  );         DataSource ds = (DataSource)ctx.lookup("jdbc/ora");                  connection = ds.getConnection("borg", "pw");         connection.setAutoCommit(false);     }     return connection; } 

In this code, I use the JDBC 2.0 Optional Package method for connecting to a database. You may not have the JDBC 2.0 Optional Package available to you, in which case you may want to use the old-fashioned DriverManager approach to making a Connection. Either way, you definitely want a pooled connection. Without access to the JDBC 2.0 Optional Package, you have to roll your own connection pooling.

The heart of JDBC persistence rests in the persistence delegate. As you saw before in the PersistenceSupport interface, an implementation is responsible for the SQL that inserts, updates, or deletes the object in question from the database. Each implementation is dependent on the particular entity it is persisting. Example 9-4 provides the store( ) method in the AccountSupport class to save an Account entity to the database.

Example 9-4: The store( ) Method for an Account Persistence Delegate

static private String UPDATE =      "UPDATE Account " +     "SET balance = ?, " +     "lastUpdateID = ?, " +     "lastUpdateTime = ? " +     "WHERE objectID = ? " +     "AND lastUpdateID = ? " +     "AND lastUpdateTime = ?";   public void store(Transaction trans, Memento mem)  throws StoreException {     long oid = mem.getObjectID(  );     long lut = mem.getLastUpdateTime(  );     String luid = mem.getLastUpdateID(  );     Connection conn = null;           try {         PreparedStatement stmt;         Double d;           conn = ((JDBCTransaction)trans).getConnection(  );         stmt = conn.prepareStatement(UPDATE);         d = (Double)mem.get(Account.class,                              Account.BALANCE);         if( d == null ) {             stmt.setNull(1, Types.REAL);         }         else {             stmt.setDouble(1, d.doubleValue(  ));         }         stmt.setString(2, trans.getIdentifier().getUserID(  ));         stmt.setLong(3, trans.getTimestamp(  ));         stmt.setLong(4, oid);         stmt.setString(5, luid);         stmt.setLong(6, lut);         if( stmt.executeUpdate(  ) != 1 ) {             throw new StoreException("No row modified.");         }         stmt.close(  );     }     catch( SQLException e ) {         throw new CreateException(e);     } } 

You may have noticed the getLastUpdateID( ) and getLastUpdateTime( ) methods in the Persistent interface earlier in the chapter and wondered what their purpose was. They specifically enable you to work with a database in optimistic concurrency mode. Pessimistic concurrency means that the database will lock data on read and not release that lock without a commit. In other words, if you do a SELECT to find an account, the row--or perhaps more--will be locked until you issue a commit. No one else can read or write to that row.

As you can imagine, pessimistic concurrency is very bad for performance. With optimistic concurrency, however, you risk dirty writes. A dirty write is a situation in which two clients have read the same data simultaneously and then attempt to make different writes. For example, consider when a teller reads customer information to change the customer address, and the bank manager reads information about the same customer to add a comment to the customer file. If they both read the data at the same time, the person to save last risks erasing the changes made by the first person to save. By using the user ID of the last person to make a change, along with a timestamp noting when the change was made, you can get the performance benefit of optimistic concurrency with the protection against dirty writes of pessimistic concurrency.

Under this model, when you query the database, you get the user ID of the last user to make a change and the time the change was made. When you update the database with that data, you use that user ID and timestamp in the WHERE clause. If someone else changed the data before you, your WHERE clause will not match any rows in the database and will thus throw an exception.

Searches

Not only does the persistence delegate support the basic database inserts, updates, and deletes, but it also supports the component model's searches. Writing logic to support arbitrary searches, however, can be very complex. You really do not want to have to repeat the complexity of search logic for every single component in your system if you can avoid it. Fortunately, you can avoid it by capturing search logic in a single place, the persistence delegate.

The final example in this chapter, Example 9-5, is the full source code to the JDBCSupport class, an implementation of the PersistenceSupport class. It does not, on its own, provide implementations of the persistence operations you discussed so far in the chapter. Business components require subclasses of JDBCSupport that specifically map a specific business component to a data model.[2] The base class does have, however, a generalized search engine that accepts the SearchCriteria object, translates it into SQL, and finally returns the results.

Example 9-5: The Abstract JDBCSupport Class with a Generic SQL Search Algorithm

package com.imaginary.lwp.jdbc;   import com.imaginary.lwp.BaseFacade; import com.imaginary.lwp.FindException; import com.imaginary.lwp.PersistenceSupport; import com.imaginary.lwp.SearchBinding; import com.imaginary.lwp.SearchCriteria; import com.imaginary.lwp.Transaction; import com.imaginary.util.DistributedList; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.Iterator;   /**  * Persistence support for JDBC-based persistence.  * <BR>  * Last modified $Date: 2000/08/08 15:52:01 $  * @version $Revision: 1.14 $  * @author George Reese ([e-mail])  */ public abstract class JDBCSupport implements PersistenceSupport {     /**      * Provides a generalized mechanism for binding a set      * of values to any possible prepared statement. A calling      * method specifies a statement and the index from which      * binding should begin, as well as the actual bindings.      * This index is the index that gets passed to a      * prepared statement's setXXX(  ) method for binding      * the values in the bindinds list      * @param stmt the statement being set up      * @param ind the index to start binding at      * @param bindings the bindings to bind      * @throws com.imaginary.lwp.FindException      * @throws java.sql.SQLException an error occurred binding the bindings      * to the statement      */     private void bind(PreparedStatement stmt, int ind, Iterator bindings)         throws FindException, SQLException  {         while( bindings.hasNext(  ) ) {             SearchBinding bdg = (SearchBinding)bindings.next(  );             Object val = bdg.getValue(  );                          if( val instanceof SearchCriteria ) {                 SearchCriteria sc = (SearchCriteria)val;                   bind(stmt, ind, sc.bindings(  ));             }             else if( val instanceof BaseFacade ) {                 BaseFacade ref = (BaseFacade)val;                   stmt.setLong(ind++, ref.getObjectID(  ));             }             else {                 stmt.setObject(ind++, val);             }         }     }       /**      * Executes a search for objects meeting the specified criteria      * using the specified transaction.      * @param tr the transaction to use for the find operation      * @param sc the search criteria to base the find on      * @return an iterator of matching objects      * @throws com.imaginary.lwp.FindException an error occurred      * searching for objects meeting the search criteria      */     public Collection find(Transaction tr, SearchCriteria sc)         throws FindException {         Iterator bindings = sc.bindings(  );         DistributedList list = new DistributedList(  );         String sql = getFindSQL(sc);           try {             JDBCTransaction trans;             Connection conn;               trans = (JDBCTransaction)tr;             try {                 conn = trans.getConnection(  );             }             catch( Exception e ) {                 e.printStackTrace(  );                 return null;             }             PreparedStatement stmt = conn.prepareStatement(sql);             ResultSetMetaData meta;             ResultSet rs;             int cc;               bind(stmt, 1, bindings);             rs = stmt.executeQuery(  );             meta = rs.getMetaData(  );             cc = meta.getColumnCount(  );             // This loop places result set values into             // a hash map with the column name as the key             // and the column value as the value. This             // map then gets passed to a new facade for             // pre-caching values.             while( rs.next(  ) ) {                 HashMap map = new HashMap(  );                 long oid = rs.getLong(1);                 String cls = rs.getString(2);                                  for(int i=3; i<=cc; i++) {                     String tbl = meta.getTableName(i).toUpperCase(  );                     String name = meta.getColumnLabel(i).toUpperCase(  );                     Object val = rs.getObject(i);                       if( tbl.equals("") ) {                         tbl = getPrimaryTable().toUpperCase(  );                     }                     name = tbl + "." + name;                     if( rs.wasNull(  ) ) {                         val = null;                     }                     map.put(name, val);                 }                 list.add(getFacade(oid, cls, map));             }             return list;         }         catch( SQLException e ) {             throw new FindException(e);         }     }       /**      * Provides the facade object for entities supported by this      * persistence support delegate.      * @param oid the object ID of the desired object      * @param cls the reference class name      * @param vals the initial cache values      * @return an instance of the reference class pointing to the specified      * object      * @throws com.imaginary.lwp.FindException the specified class could not      * be loaded      */     public final BaseFacade getFacade(long oid, String cls, HashMap vals)         throws FindException {         try {             BaseFacade ref;               ref = (BaseFacade)Class.forName(cls).newInstance(  );             ref.assign(oid, vals);             return ref;         }         catch( Exception e ) {             e.printStackTrace(  );             throw new FindException(e);         }     }       /**      * Special method for building a <CODE>SELECT</CODE> statement that      * will perform a search using the named search critieria.      * @param sc the search criteria to build SQL from      * @return the SQL that performs the select      * @throws com.imaginary.lwp.FindException the SQL could not be built      */     protected String getFindSQL(SearchCriteria sc) throws FindException {         StringBuffer sql = new StringBuffer("SELECT ");         ArrayList tables = new ArrayList(  );         String where, order;         Iterator it;                  sql.append(getPrimaryTable(  ) + ".OBJECTID");         sql.append(", " + getPrimaryTable(  ) + ".CRT_CLASS");         tables.add(getPrimaryTable(  ));         it = sc.preloads(  );         while( it.hasNext(  ) ) {             String fld = mapField((String)it.next(  ));             int i = fld.indexOf(".");             String tbl;               if( i != -1 ) {                 tbl = fld.substring(0, i);                 if( !tables.contains(tbl) ) {                     tables.add(tbl);                 }             }             sql.append(", ");             sql.append(fld);         }         where = getWhere(sc.bindings(  ), tables);         order = getOrder(sc.sorts(  ), tables);         it = tables.iterator(  );         sql.append(" FROM ");         while( it.hasNext(  ) ) {             sql.append((String)it.next(  ));             if( it.hasNext(  ) ) {                 sql.append(", ");             }         }         if( where.length(  ) > 0 ) {             sql.append(" WHERE ");             sql.append("(" + where + ")");         }         else if( tables.size(  ) > 1 ) {             sql.append(" WHERE ");         }         it = tables.iterator(  );         while( it.hasNext(  ) ) {             String tbl = (String)it.next(  );             JDBCJoin join;                          if( tbl.equals(getPrimaryTable(  )) ) {                 continue;             }             join = getJoin(tbl);             sql.append(" AND " + join.toString(  ) + " ");         }         if( order.length(  ) > 0 ) {             sql.append(" ORDER BY " + order);         }         return sql.toString(  );     }       /**      * Given a table, this method needs to provide a portion of a      * <CODE>WHERE</CODE> clause that supports joining to the specified      * table.      * @param tbl the table to join to      * @return the join object that represents a join for the primary      * table to the specified table      * @throws com.imaginary.lwp.FindException a join could not be constructed      */     protected abstract JDBCJoin getJoin(String tbl) throws FindException;       /**      * Provides the <CODE>ORDER BY</CODE> clause to support ordering of      * the results.      * @param sorts the sort criteria from the search criteria object      * @param a pass by reference thing where any new tables that need      * to be joined to are added to this list      * @return a string with the <CODE>ORDER BY</CODE> clause      * @throws com.imaginary.lwp.FindException the clause could not be      * built      */     private String getOrder(Iterator sorts, ArrayList tables)         throws FindException {         StringBuffer order = null;           if( !sorts.hasNext(  ) ) {             return "";         }         do {             String col = (String)sorts.next(  );             int i;               if( order == null ) {                 order = new StringBuffer(  );             }             else {                 order.append(", ");             }             col = mapField(col);             order.append(col);             i = col.indexOf(".");             if( i != -1 ) {                 String tbl = col.substring(0, i);                   if( !tables.contains(tbl) ) {                     tables.add(tbl);                 }             }         } while( sorts.hasNext(  ) );         return order.toString(  );     }       /**      * Implemented by subclasses to provide the name of the primary      * table for storing objects supported by this class.      * @return the name of the primary table      */     protected abstract String getPrimaryTable(  );       /**      * Provides the <CODE>WHERE</CODE> clause to support a find.      * @param bindings the search bindings from the search criteria object      * @param a pass by reference thing where any new tables that need      * to be joined to are added to this list      * @return a string with the <CODE>WHERE</CODE> clause      * @throws com.imaginary.lwp.FindException the clause could not be      * built      */     private String getWhere(Iterator bindings, ArrayList tables)         throws FindException {         StringBuffer where = null;           if( !bindings.hasNext(  ) ) {             return "";         }         do {             SearchBinding bdg = (SearchBinding)bindings.next(  );             Object val = bdg.getValue(  );             String fld = bdg.getField(  );               if( where == null ) {                 where = new StringBuffer(  );             }             else {                 where.append(" " + bdg.getBoolean().toString(  ) + " ");             }             if( val instanceof SearchCriteria ) {                 SearchCriteria sc = (SearchCriteria)val;                   where.append("(");                 where.append(getWhere(sc.bindings(  ), tables));                 where.append(")");             }             else {                 int i;                                  fld = mapField(fld);                 where.append(fld);                 i = fld.indexOf(".");                 if( i != -1 ) {                     String tbl = fld.substring(0, i);                       if( !tables.contains(tbl) ) {                         tables.add(tbl);                     }                 }                 where.append(" " + bdg.getOperator().toString(  ) + " ?");             }         } while( bindings.hasNext(  ) );         if( where == null ) {             return "";         }         else {             return where.toString(  );         }     }       /**      * Maps a field from the supported object's attributes to a database      * field.      * @param fld the Java object.attribute for the field to map      * @return the database table to map the field to      * @throws com.imaginary.lwp.FindException the field could not be mapped      */     protected abstract String mapField(String fld) throws FindException; } 

The bulk of work done in this class is done by the getFindSQL( ) method. It takes a SearchCriteria instance and builds SQL to support the desired criteria. The SearchCriteria represents a set of criteria on which to perform a search independent of the underlying data store semantics. You can arbitrarily associate attributes with values and the nature of that relationship. For example, you can use the SearchCriteria to specify that an attribute must equal some value and a second attribute be greater than another value. Your client might construct a search in the following way:

String[] precache = { "lastName", "firstName" }; SearchCriteria sc = new SearchCriteria(precache);   // ssn is the social security number being sought sc.addBinding("taxID", ssn); sc.addBinding(SearchBoolean.OR, "birthDate",               SearchOperator.EQUALS, bd); 

The result is a collection of façades containing customers who either have the specified social security number or the specified birth date. Each façade will be precached with the customer's first and last name.

All other methods in the class basically support the SQL building: the getWhere( ) providing the WHERE clause and the getOrder( ) supporting any potential ORDER BY clause. Once the SQL is built, the find( ) method uses that SQL and help from ResultSetMetaData to execute the SQL and process the results. For each matching row, a Façade is instantiated and placed into a Collection specially optimized for distributed searches...

Table of Contents

• Preface
• The JDBC API
  • Chapter 1: Java in the Enterprise
  • Chapter 2: Relational Databases and SQL
  • Chapter 3: Introduction to JDBC
  • Chapter 4: Advanced JDBC
  • Chapter 5: The JDBC Optional Package
• Applied JDBC
  • Chapter 6: Other Enterprise APIs
  • Chapter 7: Distributed Application Architecture
  • Chapter 8: Distributed Component Models
  • Chapter 9: Persistence
  • Chapter 10: The User Interface
• Reference
  • Chapter 11: JDBC Reference
  • Chapter 12: The JDBC Optional Package Reference
• Colophon

Editorial Reviews

O'Reilly books are rarely for neophytes, but advanced users swear by them, and these will be no exception. Englander covers a hot Java subtopic for students, programmers, and professionals already familar with Java and object-oriented programming. He discusses events, event adapters, properties, persistence, java archive files, the BeanBox tool, property editors, ActiveX, and the java.beans Package. Flanagan's work is the book Java programmers want nearby when they are at the keyboard. A complete ready-reference work, this belongs in all collections supporting programmers. Java is a constantly changing language so Nutshell will be coming out often with new editions; always have the newest one on hand. Reese goes beyond simple applet design to relational databases, SQL, object-oriented database applications, application servers, and remote object manipulation. The examples used throughout the book are based on a banking application designed in Java.

Library Journal

Describes the Java interfaces that make portable object-oriented access to relational databases possible, and offers a robust model for writing applications that are easy to maintain. Section I focuses on the JDBC API, and Section II applies information in Section I to real- world database programming within the context of a banking application example. Section III is a reference for the JDBC Core and Optional Package APIs. This second edition is updated for JDBC 2.0. Annotation c. Book News, Inc., Portland, OR (booknews.com)

Booknews