This chapter is from the book
This chapter is from the book
This chapter is from the book
This chapter is from the book
This code example illustrates a data accessor abstraction that defines logical database access operations for reading, inserting, updating, and deleting data.
Notice that this interface's callers do not need to manage database resources, issue SQL statements, or make direct JDBC calls.
The logical operations define the data they are accessing, but do not disclose any underlying implementation.
public interface DataAccessor {
/**
Reads data from a table.
@param table The table.
@param columns The columns to read, or null to read
all the columns in the table.
@param selectionRow A set of filter columns and values
used to subset the rows, or null to
read all the rows in the table.
@param sortColumns The columns to sort, or null to read
without sorting.
@return The list of rows.
**/
List read(String table,
String[] columns,
Row selectionRow,
String[] sortColumns) throws DataException;
/**
Inserts data into a table.
@param table The table.
@param rows The rows to insert.
**/
void insert(String table, List rows) throws DataException;
/**
Updates data in a table.
@param table The table.
@param selectionRow A set of filter columns and values
used to subset the rows, or null to
update all of the rows in the table.
@param updateRow A set of update columns and values.
**/
void update(String table,
Row selectionRow,
Row updateRow) throws DataException;
/**
Deletes data from a table.
@param table The table.
@param selectionRow A set of filter columns and values
used to subset the rows, or null to
delete all of the rows in the table.
**/
void delete(String table, Row selectionRow)
throws DataException;
}
ConcreteDataAccessor is a DataAccessor implementation that operates in terms of multiple JDBC connections. This class is responsible for:
-
Managing database resources such as connections, statement handles, and result sets.
-
Generating and issuing SQL statements.
-
Resolving qualified table names and physical database connections. For the sake of this example, suppose that this application accesses accounting and payroll data on different systems. ConcreteDataAccessor maps logical operations to databases based on the format of the table name provided by the client. This rule is an arbitrary constraint that is part of this particular application's design. However, you can implement more robust, directory-based distribution mechanisms the same way.
As you read through this example, consider some other data access details that you could implement within a concrete data accessor implementation, such as customized data conversion, user-based authorization, and logical operation logging.
public class ConcreteDataAccessor
implements DataAccessor {
private Connection accountingConnection;
private Connection payrollConnection;
private Connection otherConnection;
/**
Constructs a ConcreteDataAccessor object.
*/
public ConcreteDataAccessor() throws DataException {
try {
accountingConnection
= DriverManager.getConnection(...);
payrollConnection
= DriverManager.getConnection(...);
otherConnection
= DriverManager.getConnection(...);
}
catch(SQLException e) {
throw new DataException(
"Unable to construct DataAccessor", e);
}
}
/**
Reads data from a table.
@param table The table.
@param columns The columns to read, or null to read
all the columns in the table.
@param selectionRow A set of filter columns and values
used to subset the rows, or null to
read all the rows in the table.
@param sortColumns The columns to sort, or null to read
without sorting.
@return The list of rows.
**/
public List read(String table,
String[] columns,
Row selectionRow,
String[] sortColumns)
throws DataException {
try {
// Generate the SQL SELECT statement based on
// the caller's 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");
}
}
// Resolve the appropriate connection for this
// table.
Connection connection = resolveConnection(table);
synchronized(connection) {
// Execute the query.
Statement statement
= connection.createStatement();
ResultSet resultSet
= statement.executeQuery(
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();
statement.close();
return resultRows;
}
}
catch(SQLException e) {
throw new DataException("Unable to read table "
+ table, e);
}
}
/**
Inserts data into a table.
@param table The table.
@param rows The rows to insert.
**/
public void insert(String table,
List rows) throws DataException {
try {
for(Iterator i = rows.iterator(); i.hasNext(); ) {
Row row = (Row)i.next();
// Generate the SQL INSERT statement based on
// the caller's input.
StringBuffer buffer = new StringBuffer();
buffer.append("INSERT INTO ");
buffer.append(resolveQualifiedTable(table));
// List the column names.
buffer.append(" (");
boolean firstColumn = true;
for(Iterator j = row.columns(); j.hasNext();){
if (!firstColumn)
buffer.append(", ");
else
firstColumn = false;
buffer.append(j.next());
}
// List the column values.
buffer.append(") VALUES (");
firstColumn = true;
for(Iterator j = row.columns(); j.hasNext();){
if (!firstColumn)
buffer.append(", ");
else
firstColumn = false;
String column = (String)j.next();
Object columnValue
= row.getColumnValue(column);
buffer.append(
generateLiteralValue(columnValue));
}
// Resolve the appropriate connection for
// this table.
Connection connection
= resolveConnection(table);
synchronized(connection) {
// Execute the insert.
Statement statement
= connection.createStatement();
statement.executeUpdate(
buffer.toString());
statement.close();
}
}
}
catch(SQLException e) {
throw new DataException(
"Unable to insert into table " + table, e);
}
}
/**
Updates data in a table.
@param table The table.
@param selectionRow A set of filter columns and values
used to subset the rows, or null to
update all the rows in the table.
@param updateRow A set of update columns and values.
**/
public void update(String table,
Row selectionRow,
Row updateRow) throws DataException {
try {
// Generate the SQL UPDATE statement based on the
// caller's input.
StringBuffer buffer = new StringBuffer();
buffer.append("UPDATE ");
buffer.append(resolveQualifiedTable(table));
// Generate the SET clause.
buffer.append(" SET ");
boolean firstColumn = true;
for(Iterator i=updateRow.columns(); i.hasNext();){
if (!firstColumn)
buffer.append(", ");
else
firstColumn = false;
String column = (String)i.next();
buffer.append(column);
buffer.append(" = ");
Object columnValue
= updateRow.getColumnValue(column);
buffer.append(
generateLiteralValue(columnValue));
}
// Generate the WHERE clause if the caller
// specified a selection row.
if (selectionRow != null) {
buffer.append(
generateWhereClause(selectionRow));
}
// Resolve the appropriate connection for this
// table.
Connection connection = resolveConnection(table);
synchronized(connection) {
// Execute the update.
Statement statement
= connection.createStatement();
statement.executeUpdate(
buffer.toString());
statement.close();
}
}
catch(SQLException e) {
throw new DataException(
"Unable to update table " + table, e);
}
}
/**
Deletes data from a table.
@param table The table.
@param selectionRow A set of filter columns and values
used to subset the rows, or null to
delete all the rows from the table.
**/
public void delete(String table,
Row selectionRow) throws DataException{
try {
// Generate the SQL DELETE statement based on the
// caller's input.
StringBuffer buffer = new StringBuffer();
buffer.append("DELETE FROM ");
buffer.append(resolveQualifiedTable(table));
// Generate the WHERE clause if the caller
// specified a selection row.
if (selectionRow != null) {
buffer.append(
generateWhereClause(selectionRow));
}
// Resolve the appropriate connection for
// this table.
Connection connection = resolveConnection(table);
synchronized(connection) {
// Execute the delete.
Statement statement
= connection.createStatement();
statement.executeUpdate(buffer.toString());
statement.close();
}
}
catch(SQLException e) {
throw new DataException(
"Unable to delete from table " + table, e);
}
}
/**
Resolves the connection based on the table name.
*/
private Connection resolveConnection(String table) {
// These are just arbitrary rules for the sake
// of this example.
if (table.startsWith("A"))
return accountingConnection;
else if (table.startsWith("P"))
return payrollConnection;
else
return otherConnection;
}
/**
Resolves the qualified table name.
*/
private String resolveQualifiedTable(String table) {
// These are just arbitrary rules for the sake
// of this example.
if (table.startsWith("A"))
return "ACCTDATA." + table;
else if (table.startsWith("P"))
return "PAYROLL." + table;
else
return table;
}
/**
Generates a SQL literal string.
*/
private String generateLiteralValue(Object literalValue) {
StringBuffer buffer = new StringBuffer();
if (!(literalValue instanceof Number))
buffer.append("'");
buffer.append(literalValue);
if (!(literalValue instanceof Number))
buffer.append("'");
return buffer.toString();
}
/**
Generates a SQL WHERE clause based on a selection row.
*/
private String generateWhereClause(Row selectionRow) {
StringBuffer buffer = new StringBuffer();
buffer.append(" WHERE ");
boolean firstColumn = true;
for(Iterator i=selectionRow.columns(); i.hasNext();){
if (!firstColumn)
buffer.append(" AND ");
else
firstColumn = false;
String column = (String)i.next();
buffer.append(column);
buffer.append(" = ");
Object columnValue
= selectionRow.getColumnValue(column);
buffer.append(
generateLiteralValue(columnValue));
}
return buffer.toString();
}
}
Row is a simple helper class that DataAccessor uses to represent logical input and output data. Keep in mind that if you had chosen to return a java.sql.ResultSet instead, you would have immediately coupled your application directly to JDBC technology, preventing future, transparent moves to non-JDBC databases in the future.
public class Row {
private Map contents = new HashMap();
public Row() {
}
public Row(String column, Object columnValue) {
contents.put(column, columnValue);
}
public void addColumn(String column, Object columnValue) {
contents.put(column, columnValue);
}
public Object getColumnValue(String column) {
return contents.get(column);
}
public Iterator columns() {
return contents.keySet().iterator();
}
}
DataException represents any exception that is thrown within the context of a data accessor implementation. This exception class wraps concrete exceptions like java.sql.SQLExceptions. This is another step toward decoupling consuming application code from the ConcreteDataAccessor's underlying JDBC implementation.
public class DataException
extends Exception {
DataException(String message, Throwable cause) {
super(message, cause);
}
}
Next is an example of a client that uses a data accessor abstraction to implement the employee expense reimbursement check process described in the “Context” section. Notice how there is no JDBC or SQL code. Instead, the example does all its data access using the logical operations provided by the data accessor abstraction.
// Get a list of employees that need to be
// reimbursed for expenses.
List employeeRows
= dataAccessor.read("P_EMPLOYEES",
null,
new Row("EXPENSE_FLAG", "Reimburse"),
null);
for(Iterator i = employeeRows.iterator(); i.hasNext(); ) {
Row employeeRow = (Row)i.next();
Integer employeeID
= (Integer)employeeRow.getColumnValue("EMPLOYEE_ID");
Row employeeSelectionRow
= new Row("EMPLOYEE_ID", employeeID);
// Get a list of expense records for the employee.
List expenseRows
= dataAccessor.read("A_EXPENSES",
new String[] { "AMOUNT" },
employeeSelectionRow,
null);
// Total the expense records.
long totalExpense = 0;
for(Iterator j = expenseRows.iterator(); j.hasNext(); ) {
Row expenseRow = (Row)j.next();
long amount
= ((Long)expenseRow.getColumnValue("AMOUNT"))
.longValue();
totalExpense += amount;
}
// Issue the employee a check for the sum.
issueEmployeeCheck(employeeID, totalExpense);
// Update the employee's expense status to none.
dataAccessor.update("P_EMPLOYEES",
employeeSelectionRow,
new Row("EXPENSE_FLAG", "None"));
// Delete all the employee's expense records.
dataAccessor.delete("A_EXPENSES",
employeeSelectionRow);
}