Sample Code
The following sections show sample code that defines and implements much of the layered structure defined in the example in the Context section. There are three layers: the connection management layer, the data accessor layer, and the active domain objects layer.
Connection Management Layer
The connection management layer is the bottom-most layer in this example. It is responsible for managing database connections and statement handles. The ConnectionManager interface defines this layer's abstraction. It exposes operations for executing SQL queries and updates to any data source defined in the system.
public interface ConnectionManager {
/**
Executes an SQL query statement.
@param dataSource The data source.
@param sql The SQL statement.
@return The ResultSet. It is the caller's
responsibility to close this ResultSet
when it finishes using it.
*/
ResultSet executeQuery(String dataSource,
String sql)
throws SQLException;
/**
Executes an SQL update statement.
@param dataSource The data source.
@param sql The SQL statement.
@return The number of affected rows.
*/
int executeUpdate(String dataSource,
String sql)
throws SQLException;
}
The ConcreteConnectionManager is a simple implementation that plugs the data source name directly into a JDBC URL to establish new connections. It creates at most one connection for each data source and reuses that connection for subsequent operations.
public class ConcreteConnectionManager
implements ConnectionManager {
// Maps data sources to connections.
private Map connections = new HashMap();
public ResultSet executeQuery(String dataSource,
String sql)
throws SQLException {
Connection connection = resolveConnection(dataSource);
Statement statement = connection.createStatement();
return statement.executeQuery(sql);
}
public int executeUpdate(String dataSource,
String sql)
throws SQLException {
Connection connection = resolveConnection(dataSource);
Statement statement = connection.createStatement();
int rowsUpdated = statement.executeUpdate(sql);
statement.close();
return rowsUpdated;
}
private Connection resolveConnection(String dataSource)
throws SQLException {
// If the map already contains a connection for
// this data source, then use it. Otherwise,
// initialize a new one.
synchronized(connections) {
Connection connection
= (Connection)connections.get(dataSource);
if (connection == null) {
connection = DriverManager.getConnection(
"jdbc:subprotocol://" + dataSource,
myUser, myPassword);
connections.put(dataSource, connection);
}
return connection;
}
}
}
ConcreteConnectionManager's implementation has a few problems. First of all, any two clients that invoke operations using the same data source name utilize the same database connection. This works fine most of the time. However, like most call-level interfaces, JDBC maintains at most one active transaction per database connection. As a result, ConcreteConnectionManager forces multiple operations to run within the same transaction. Applications may not always expect this behavior. You can fix this problem by building a connection pool that this layer encapsulates. Each operation uses its own connection from the pool and returns it when it is done.
Another problem with ConcreteConnectionManager is that it contains a statement handle leak. The executeQuery operation creates a new statement handle, but neglects to close it. In JDBC, closing a statement handle forces the physical database driver to implicitly close the result set as well, thus rendering the query results unavailable to client code. You cannot expect the client to close the statement handle, because the client does not have a reference to it. You can solve this problem by wrapping the result set object in a Resource Decorator (103) that maintains a reference to the statement handle. The decorator can take care of closing the statement handle when the client code closes the result set.
A final problem with ConcreteConnectionManager is that the executeQuery operation returns an open result set. It clearly documents that the client is responsible for closing this result set when it is done. However, if the client's developer does not heed this documentation or writes defective code, his/her code may leave the result set and its associated database resources open indefinitely. You can account for this possibility by incorporating a Resource Timer (137) that automatically closes the result set after a configured amount of time.
It is safe to say this implementation has significant scalability problems. However, it works fine on a small scale and certainly suffices for initial prototyping and development testing. It is an example stub implementation that demonstrates how you can solve problems after you build the remaining layers in the system. Later, you can enhance it to incorporate additional functionality, such as a more sophisticated data distribution mechanism or user and password management.
Data Accessor Layer
The data accessor layer is responsible for implementing physical data access operations on behalf of its client. The DataAccessor interface defines this layer's abstraction using logical database operations and does not expose any SQL or call-level interface semantics at all. This is the same interface that the sample code for Data Accessor (9) previously defined.
public interface DataAccessor {
List read(String table,
String[] columns,
Row selectionRow,
String[] sortColumns) throws DataException;
void insert(String table, List rows) throws DataException;
void update(String table,
Row selectionRow,
Row updateRow) throws DataException;
void delete(String table, Row selectionRow)
throws DataException;
}
The ConcreteDataAccessor implementation is similar to that shown in the "Sample Code" section for the Data Accessor pattern. The difference is that it is implemented here in terms of the ConnectionManager interface rather than the physical database driver. This illustrates how the data accessor layer now sits on top of the connection management layer. ConcreteDataAccessor effectively acts as an adapter from the DataAccessor interface to the ConnectionManager interface.
public class ConcreteDataAccessor
implements DataAccessor {
private ConnectionManager connectionManager;
/**
Constructs a ConcreteDataAccessor object.
@param connectionManager The connection manager.
*/
public ConcreteDataAccessor(
ConnectionManager connectionManager) {
this.connectionManager = connectionManager;
}
public List read(String table,
String[] columns,
Row selectionRow,
String[] sortColumns)
throws DataException {
try {
// Generate the SQL SELECT statement based on
// the client input.
StringBuffer buffer = new StringBuffer();
buffer.append("SELECT ");
// List the columns if the caller specified any.
if (columns != null) {
for(int i = 0; i < columns.length; ++i) {
if (i > 0)
buffer.append(", ");
buffer.append(columns[i]);
}
}
else
buffer.append(" * ");
// Include the resolved qualified table name.
buffer.append(" FROM ");
buffer.append(resolveQualifiedTable(table));
// Generate the WHERE clause if the caller
// specified a selection row.
if (selectionRow != null) {
buffer.append(
generateWhereClause(selectionRow));
}
// Generate the ORDER BY clause if the caller
// specified sort columns.
if (sortColumns != null) {
buffer.append(" ORDER BY ");
for(int i = 0; i < sortColumns.length; ++i) {
if (i > 0)
buffer.append(", ");
buffer.append(sortColumns[i]);
buffer.append(" ASC");
}
}
// Use the ConnectionManager to execute the query.
String dataSource = resolveDataSource(table);
ResultSet resultSet
= connectionManager.executeQuery(dataSource,
buffer.toString());
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
// Create a list of result rows based on the
// contents of the result set.
List resultRows = new LinkedList();
while(resultSet.next()) {
Row resultRow = new Row();
for(int i = 1; i <= columnCount; ++i) {
resultRow.addColumn(
rsmd.getColumnName(i),
resultSet.getObject(i));
}
resultRows.add(resultRow);
}
// Release database resources and return.
resultSet.close();
return resultRows;
}
catch(SQLException e) {
throw new DataException("Unable to read table "
+ table, e);
}
}
// See the full listing in the "Sample Code" section for
// Data Accessor (9). You can reimplement the other
// public operations analogously.
}
Notice that ConcreteDataAccessor defines a ConnectionManager parameter in its constructor. This does not necessarily dictate any overall layer initialization strategy, but it does ensure that the client will assign a ConnectionManager implementation before ConcreteDataAccessor needs to invoke any database operations.
Active Domain Object Layer
The next layer is a set of active domain objects that is responsible for mapping itself to the physical data model. CustomerList maps itself to the contents of the CUSTOMERS table. Its constructor reads the contents of this table and populates a list of Customer objects. This code is similar to that previously shown in the "Sample Code" section for Active Domain Object (33), except it uses the data accessor layer rather than interacting with a physical database driver directly.
public class CustomerList
{
private List contents = new LinkedList();
/**
Constructs a CustomerList object that represents
every customer in the database.
*/
public CustomerList() throws DataException {
DataAccessor dataAccessor
= DataAccessorFactory.getDataAccessor();
List resultData = dataAccessor.read("CUSTOMERS",
null, null, null);
for(Iterator i = resultData.iterator();i.hasNext();) {
Row row = (Row)i.next();
int id
= ((Integer)row.getColumnValue("CUSTID"))
.intValue();
String lastName
= (String)row.getColumnValue("LAST");
String firstName
= (String)row.getColumnValue("FIRST");
String address
= (String)row.getColumnValue("ADDRESS");
String city
= (String)row.getColumnValue("CITY");
String state
= (String)row.getColumnValue("STATE");
String country
= (String)row.getColumnValue("COUNTRY");
String zip
= (String)row.getColumnValue("ZIP");
Customer customer = new Customer(id, lastName,
firstName, address, city, state, country,
zip);
contents.add(customer);
}
}
/**
Returns a customer list iterator.
*/
public Iterator iterator() {
return contents.iterator();
}
}
Recall that the active domain objects in the "Sample Code" section for Active Domain Object (33) used a ConnectionFactory in order to resolve physical database connections. This example takes the same approach except that it defines a DataAccessorFactory for resolving DataAccessor implementations:
public class DataAccessorFactory {
private static final ConnectionManager connectionManager
= new ConcreteConnectionManager();
public static DataAccessor getDataAccessor() {
return new ConcreteDataAccessor(connectionManager);
}
}
| 
