Microsoft word - data_access.doc

Data Access Patterns: Database Interactions in Object-Oriented
Applications
By Clifton Nock
Publisher Addison Wesley Pub Date September 15, 2003 ISBN Decoupling Patterns
The previous chapter introduced the differences between application and middleware code. Developing application code requires extensive domain knowledge and experience with the business objects and processes that the software is supposed to model. On the other hand, middleware code consists of more technical details, for which programmers who are used to working with system-level resources and libraries are more qualified. Even small application development groups can benefit by dividing software into layers that separate application and middleware code. With a clean separation, application code deals exclusively with business objects and logic while middleware code handles system and database details. Defining the exact nature of this separation requires you to consider the types of maintenance and enhancements that you expect to address in future releases. Ask these questions as you evaluate a What features were dropped to meet schedules?— Even if you do not have time to implement these features, you should consider building isolated placeholders that What additional features do you expect your customers to request?— This is hard to predict and requires research and imagination. Consider meeting with potential customers for brainstorming sessions or usability testing. They can decide how to use your application in their environment and can help you determine what features are important. Do you expect the underlying data model to change?— Will you be modifying the data model in future releases? Do you need to adapt to variations in the data model for Do you expect to support new database platforms?— As you expand your customer base, you may be forced to support additional database platforms that partnerships and Do you plan to take advantage of advances in database technology?— If it is important for your application to utilize cutting-edge database technology, then consider which components need to adapt when something new comes along. Are you dependent on third-party components?— When you find defects in your code, you have complete control of the debugging and repair process. However, if you identify a defect in a third-party component on which you depend, you have to wait for a fix from its vendor. In addition, it is not uncommon for professional partnerships to dissolve. In these cases, you may find yourself incorporating some competing technology in place of Do you expect performance problems to occur?— Many best practices recommend designing your software with a focus on structure and maintainability first, and fixing performance problems later. Even when you follow this good advice, you can still predict Decoupling patterns describe strategies for accommodating the issues that you raise when you answer these questions. The primary, common goal of these patterns is to decouple orthogonal software components. The extent to which you decouple components depends on how much you expect them to vary independently. For instance, if you expect to support additional database platforms in future releases, then it is a good idea to isolate all the code that is specific to a particular database in a separate, swappable component. Another common example of decoupling is defining the line between applications and middleware. If you plan to expand your application or build new, similar applications, it is beneficial to decouple them from middleware code. This allows you to reuse the same middleware components and develop new applications more quickly. The Data Model and Data Access
An application's data model is the static structure of its data. It encompasses one or more tables, any associated indices, views, and triggers, plus any referential integrity features defined between tables. The term "data model" also refers to an application's understanding of this static structure, whether it is hardwired or discovered dynamically using metadata. In most cases, you define a data model with data that is intended to be used exclusively by your applications. From your customer's perspective, you encapsulate the specifics of the data model within your applications. This grants you leeway to change or add to the data model, since you can change the applications at the same time. In some cases, you may publish your data model so that customers or consultants can integrate your applications' data into those from other vendors. A common example of this is a generic reporting tool that analyzes arbitrary data models and graphically summarizes their data. Another scenario is when a customer integrates your applications with others using an enterprise application integration (EAI) framework. If you support or encourage these scenarios, you are more limited to the types of changes that you can make to your applications' data models. For instance, you can add columns to a table, but you cannot remove columns without the risk of A final scenario is when your customers define their own data models. You can design your application to work explicitly with their legacy data. This degree of agility can be a significant selling feature, but your application must readily adapt to a variety of table configurations. In contrast to its data model, an application's data access refers to its dynamic mechanism for reading and writing data. Data access code involves implementing direct database operations You can choose to combine the notions of the data model and data access in your application. Combining them results in one or more cohesive database components that can take advantage of their data model knowledge to optimize data access operations. For example, this can allow a database component to form queries that explicitly take advantage of known table indices, an optimization that substantially improves query performance. On the other hand, separating an application's data model and data access enables you to change or more readily adapt to changes Domain Objects and Relational Data
A primary benefit of object-oriented programming is the ability to model your application domain directly in software. Writing application code that manipulates Customer and Account objects is more straightforward and less error-prone than computing offsets, passing large structures, and True domain objects model application concepts, not necessarily those imposed by the data model. This means that you should not always define objects based on the layout of tables and columns. Doing so binds your domain objects to the data model and forces applications to understand these details. Suppose you have CUSTOMERS and EMPLOYEES tables that, for historical reasons, include similar but not identical address columns. The column names and types may be inconsistent in the data model, but you can define Customer and Employee domain objects that expose the same Address object type. This consistency can lead to common address processing code that handles both customer and employee addresses identically. Well-defined domain objects lead to cleaner application code, but present a problem for middleware. Object-oriented programming and relational databases are significantly different paradigms. Any software that utilizes both of these concepts must translate between them at some point. Mapping relational data to domain objects requires you to process query results and create analogous objects based on the application's data. Conversely, the other direction requires you to generate database operations that make changes to persistent relational data that corresponds to domain object changes. Figure P1.1 illustrates a mapping that translates between Customer, Employee, and Address domain objects and the corresponding relational data: Figure P1.1. A domain object mapping translates between domain objects and
relational data.
Object-oriented databases that serialize and store objects directly in their run-time form provide one solution. They eliminate the need for extra domain object mapping altogether. However, object-oriented databases are not commonly used in enterprise software because they make it harder to integrate with other products that depend heavily on relational data models. In addition, most major database vendors do not provide object-oriented database engines. Many applications therefore provide their own domain object mapping, intentionally or not. Even the most basic applications tend to tackle this problem on their own. In its simplest form, solving this problem requires an application to issue explicit database operations and manipulate corresponding domain objects with brute force. This strategy gets unwieldy when many tables and objects are involved. As the magnitude of the mapping problems increases, you can address the problems with increasingly robust designs. One solution is to define a set of common operations or a framework that populates and persists domain objects in a uniform, consistent manner. Part 3, "Input/Output Patterns," describes some patterns that can help you build these types of structures. You can also consider building or buying a full-fledged object/relational mapping tool. Object/relational mapping formalizes the process of mapping domain objects to and from relational data. Object/Relational Map (53) describes this strategy in detail. Decoupling Patterns
Decoupling patterns define how application code relates to its data model and data access code. As you decide on an application architecture, you need to consider how much cohesion you want between orthogonal components based on how much you expect them to vary independently. Decoupling components also makes it easier to build and maintain them concurrently. Another essential aspect of applying a decoupling pattern is defining the data access abstraction that it exposes to the rest of the system. This abstraction must be sufficiently versatile to expose the appropriate level of data access capabilities. On the other hand, it must also be broad enough to make it feasible to plug in alternate data sources and algorithms if required. This part of the book contains chapters for each of the following patterns: Data Accessor (9)— Encapsulates physical data access details in a single component,
exposing only logical operations. Application code maintains knowledge about the underlying data model, but is decoupled from data access responsibilities. Active Domain Object (33)— Encapsulates the data model and data access details within
relevant domain object implementations. Active domain objects relieve application code Object/Relational Map (53)— Encapsulate the mapping between domain objects and
relational data in a single component. An object/relational map decouples both application code and domain objects from the underlying data model and data access Layers (75)— Stack orthogonal application features that address data access issues with
These patterns differ in the level of database abstraction they expose to application code as well as the organization of data model awareness and data access within the architecture. These differences do not make them mutually exclusive. Layers (75) describes some examples of how you might combine these patterns to build a solution that completely separates an application's data model, data access, and domain object mapping. Extensive decoupling allows you to vary each of these components independently and leads to agile and adaptable software designs. Data Accessor
Description
Encapsulates physical data access details in a single component, exposing only logical operations. Application code maintains knowledge about the underlying data model, but is decoupled from Developing enterprise software requires a rich mix of programming and business experience. Application logic must accurately reflect business processes within its domain as well as utilize data access and system resources efficiently. Take an employee payroll system as an example. Consider a simple batch process that issues reimbursement checks for employee expenses. This process requires the following database 1. Get a list of all employees for whom expense reimbursements are due. 2. For each employee in this list, get a list of active expenses reported. 3. Issue the employee a check for the total. 5. Delete the employee's active expense records. The application logic for this process is straightforward. It does not stray much from the steps listed here. However, its code has the potential to be the opposite. The database code for each of these steps requires multiple physical database operations and management of the corresponding resources. If you mix this code within the application logic, it quickly becomes convoluted. The following code block illustrates this phenomenon. It implements the employee expense reimbursement process using Java, JDBC, and SQL. Notice the mix of database, technology, and = DriverManager.getConnection(.); // Get a list of employees that need to be // reimbursed for expenses. PreparedStatement employeesStatement = connection.prepareStatement( "SELECT EMPLOYEE_ID FROM P_EMPLOYEES " + "WHERE EXPENSE_FLAG = ?"); employeesStatement.setString(1, "Reimburse"); ResultSet employeesResultSet = employeesStatement.executeQuery(); while(employeesResultSet.next()) { int employeeID = employeesResultSet.getInt(1); // Get a list of expense records for the employee. PreparedStatement expensesStatement = connection.prepareStatement( "SELECT AMOUNT FROM A_EXPENSES " + "WHERE EMPLOYEE_ID = ?"); expensesStatement.setInt(1, employeeID); ResultSet expensesResultSet = expensesStatement.executeQuery(); // Total the expense records. long totalExpense = 0; while(expensesResultSet.next()) { long amount = expensesResultSet.getLong(1); totalExpense += amount; } // Issue the employee a check for the sum. issueEmployeeCheck(employeeID, totalExpense); // Update the employee's expense status to none. PreparedStatement updateExpenseStatus = connection.prepareStatement( "UPDATE P_EMPLOYEES SET EXPENSE_FLAG = ? " + "WHERE EMPLOYEE_ID = ?"); updateExpenseStatus.setString(1, "None"); updateExpenseStatus.setInt(2, employeeID); updateExpenseStatus.executeUpdate(); updateExpenseStatus.close(); // Delete all of the employee's expense records. PreparedStatement deleteExpenseRecords = connection.prepareStatement( "DELETE FROM A_EXPENSES WHERE EMPLOYEE_ID = ?"); deleteExpenseRecords.setInt(1, employeeID); deleteExpenseRecords.executeUpdate(); deleteExpenseRecords.close(); expensesStatement.close(); expensesResultSet.close(); } employeesResultSet.close(); employeesStatement.close(); Now, scale this implementation style to an entire suite of applications. Having database access code sprinkled throughout application logic makes it especially hard to maintain. One reason is that developers who support and enhance this code must be intimately familiar with both the application logic and data access details. Bigger problems arise when you need to support additional database platforms or incorporate optimizations such as a connection pool. With data access code spread throughout an entire product, these enhancements become major engineering projects that span a majority of the product's source files. The Data Accessor pattern addresses this problem. Its primary objective is to build an abstraction that hides low-level data access details from the rest of the application code. This abstraction exposes only high-level, logical operations. With a robust abstraction in place, application code focuses on operations from the domain point of view. This focus results in clean, maintainable application logic. Figure 1.1 illustrates how the data accessor abstraction and implementation decouple the application logic from the physical database driver: Figure 1.1. The Data Accessor pattern decouples application logic from the
physical data access implementation by defining an abstraction that exposes only
logical operations to the application code.
The data accessor implementation handles all the physical data access details on behalf of the application code. This isolation makes it possible to fix database access defects and incorporate new features in a single component and affect the entire system's operation. The logical operations that you expose depend on your application's data access requirements. In the employee expense process described earlier, it might be helpful to define logical read and write operations in terms of table and column names without requiring the application code to issue SQL statements or directly manage prepared statements or result sets. The "Sample Code" section in this chapter contains an example of some simple logical database operations. You can also use a data accessor to hide a database's semantic details as well as constraints that your system's architecture imposes. Here are some ideas for encapsulating physical data access Expose logical operations; encapsulate physical operations— The data accessor abstraction can expose logical database operations such as read, insert, update, and delete, instead of requiring application code to issue SQL statements or something at a similar, lower level. The data accessor implementation generates efficient SQL statements on the application's behalf. This is beneficial because it saves application developers from learning the intricacies of SQL and also allows you to change your strategies for issuing these operations without affecting application code. Expose logical resources; encapsulate physical resources— The more details you hide from application code, the more you are at liberty to change. One example of this is database resource management. If you let applications manage their own database connections, it is hard to incorporate enhancements like connection pooling, statement You may find it convenient to provide logical connection handles to applications. Applications can use these handles to associate operations with physical connection pools and physical connection mapping strategies. The data accessor implementation is responsible for resolving exact table locations and physical connections at runtime. This is especially convenient when data is distributed across multiple databases. Normalize and format data— The physical format of data is not necessarily the most convenient form for applications to work with, especially if the format varies across multiple database platforms. For example, databases often store and return binary large object (BLOB) data as an array or stream of raw bytes. The data accessor implementation can be responsible for deserializing these bytes and handing an object representation to Encapsulate platform details— Business relationships change frequently. If your company initiates a new partnership that requires your application to support additional database products, encapsulating any database platform details within a data accessor implementation facilitates the enhancements. If you take this as far as to hide any particular technology, such as SQL, then you can more readily support non-SQL databases as well, all without extensive application code changes. Encapsulate optimization details— Application behavior should not directly depend on optimizations like pools and caches because that hinders your ability to change these optimizations in the future. If you only allow application code to allocate logical resources and issue logical operations, then you retain the freedom to implement these logical operations within the data accessor implementation with whatever optimized The Data Accessor pattern makes application code more amenable to enhancement and optimization. In addition, it defines a clear separation between application domain code and data access details. Besides the maintenance issues described throughout this chapter, this separation benefits engineering teams as well, since you can divide the development of different components among multiple programmers with diverse skills and experience. Applicability
You want to hide physical data access complexity and platform issues from application logic. Doing so keeps application logic cleaner and more focused on the business objects You want to manage additional semantics over and above those that the underlying physical database driver provides. Database drivers do not normally handle data distribution or application-level locking mechanisms because the implementation of these features depends heavily on an application's topological and semantic architecture. You want to define multiple data access implementations and choose between them at runtime. Different implementations might accommodate multiple database platforms or even completely new database technology, such as extensible markup language (XML) Structure
Figure 1.2 illustrates the static structure of the Data Accessor pattern. The DataAccessor interface defines the data access abstraction in terms of logical operations that the application code uses. You must define these operations to be extensive enough so that applications can do useful work without forcing applications to use unnatural constructs or workarounds. You can tailor the exact logical operation semantics to keep application code as straightforward as possible. You must also be careful not to expose any physical semantics in these logical operations. Doing so enables application code to depend on exposed physical features, making it difficult to change later. Also be mindful that you do not need to define the entire set of logical operations in a single interface as Figure 1.2 shows. It is common to separate logical query, update, result set, and transaction Figure 1.2. The static structure of the Data Accessor pattern.
ConcreteDataAccessor provides the implementation of logical operations in terms of physical database operations. This class depends directly on specific database technology. You may define more than one concrete implementation if you need to support different physical database Notice that this pattern encapsulates an application's data access, but it does not encapsulate its Interactions
Figure 1.3 portrays what happens when an application invokes operationA on a ConcreteDataAccessor. The ConcreteDataAccessor implements the logical operation in terms of one or more physical operations. It is also likely to interpret or convert the input and output data as well as handle physical resource management on the application's behalf. Figure 1.3. An application invokes operationA on a ConcreteDataAccessor.
Consequences
The Data Accessor pattern has the following consequences: Benefits
Clean application code— Application code that is replete with data access details is difficult to read and maintain. Application logic tends to become obscured by the many calls necessary to do even simple database calls. When an application uses a well- designed data accessor abstraction that exposes logical database operations, its code can Adoption of new database features or platforms— When physical data access code is spread throughout a system, it is hard to add support for new features or platforms because it involves searching the entire code base and replacing or adding new calls where necessary. This process is tedious and error-prone. When a data accessor implementation encapsulates physical data access code, you only have one isolated Incorporation of optimization strategies— Data access code usually is a primary analysis focal point when tuning an application's performance. Data access code is a common bottleneck source and simple optimizations often have significant effects. When data access code is spread across a system, it requires much more effort to apply and measure optimizations because you must repeat their implementations multiple times. When you encapsulate all physical data access code within a data accessor implementation, you can incorporate an optimization strategy once and it immediately applies across the entire Swappable physical data access implementations— You can swap among multiple data accessor implementations without changing application code. This enables you to conveniently support multiple, diverse database platforms and technologies. Drawback
Limits application control of data access— Application code is limited to the logical operations defined by a data accessor abstraction. When a data accessor abstraction is not well-designed or versatile enough for an application's data access requirements, the application code may resort to unnatural or awkward workarounds that ultimately lead to Strategies
Consider these strategies when designing a data accessor abstraction: Define versatile logical operations— Keep in mind that while a data accessor abstraction hides many data access details from application code, the logical operations that it defines must not unnecessarily limit it. A truly useful data accessor abstraction exposes simple, common database operations, but also allows versatility. If the abstraction does not provide all the logical database operations that applications require, then applications need to use the operations that it does define in unexpected or inefficient combinations. This can lead to more convoluted code than if it handles data access directly. For example, the data accessor abstraction defined in the "Sample Code" section allows the client to designate a selection row when it issues a read operation. A selection row is roughly analogous to a partial or full primary key value. This feature works fine for the sample client application, but suppose another application needed to read a range of employee identifiers. The abstraction does not expose any support for this type of query, so the application would need to read all records and explicitly filter them. In this case, the data accessor abstraction's semantics have caused the application code to be less One strategy for avoiding this scenario is to research application use cases before designing a data accessor abstraction. Writing prototype or hypothetical application code helps you understand what features application developers need and what semantics keep On the other hand, be wary of designing data accessor abstractions too heavily. Adding operations for speculated scenarios may impose a significant development burden and unnecessary complexity on data accessor implementations. Incorporate enhancement and optimization points— A common development tradeoff is to remove features to meet schedule. While architects and developers can plan full- featured, highly scalable applications, they may not be successful if they take years to develop. The Data Accessor pattern enables you to design applications so that you can readily incorporate additional database features and optimizations in subsequent product releases. Even if you do not have enough development resources to incorporate all of an application's desired data access features, you should consider them and determine where A common example involves database platform support. In the first product release, you can deliver a data accessor implementation that supports a single database product using SQL access. If you hide all platform and SQL details within the data accessor implementation, you can add other platforms and database support in later releases without requiring changes to application code. You can also approach optimizations such as connection pools with a similar strategy. Hiding connection management within a data accessor implementation enables you to conveniently integrate optimizations like these in a single component, again affecting the entire system's performance characteristics without requiring any application code to Guard against inefficient application usage— It is common for application code to employ physical database access code inefficiently. This can have significant negative effects on overall system performance. One example is an application that prepares the same statement multiple times. This application's data access code is likely to be measurably slower than the code of an application that reuses statement handles where possible. However, recycling statement handles adds complexity to application code, making them good candidates for encapsulation within a data accessor implementation. Design your data accessor abstraction so that it is impossible or improbable that application usage will have a significant effect on performance or storage overhead. For example, if you expose the notion of a database connection, make it a logical connection that does not directly incur physical database connection overhead. This way, if an application opens and closes logical connections repeatedly, it will not affect overall The data accessor abstraction offers a point where you can bridge the gap between a robust, application-friendly interface and a highly optimized, full-featured To completely insulate applications from changes to a particular data accessor implementation, you should minimize all direct references to it. Instead, write application code exclusively in terms However, you need to instantiate data accessor implementation objects at some point. These are three alternatives for centralizing this instantiation so that it is easier to alter in the future: Singleton data accessor implementation— Define a global, singleton instance of a data accessor implementation that any application code can access. The singleton instance initializes itself only once and isolates its initialization details. Initialization and parameter passing— You can instantiate a single data accessor implementation object in your application's initialization code and pass it to any other application code that needs it. This strategy isolates initialization code to a single component, but it requires that many of the application classes' constructors and Data accessor factory— You can define a globally accessible factory class to instantiate new data accessor implementation instances. You still encapsulate the data accessor initialization within a single module, the factory returns new data accessor implementation instances whenever an application requests them. Sample Code
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 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. 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. 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); } Related Patterns and Technology
Data Accessor is also known as Data Access Object [Alur 2001] and Logical Connection. A data accessor implementation is an instance of Adapter [Gamma 1995] since it adapts an abstraction that is convenient for application usage to a particular physical database Singleton [Gamma 1995] and Abstract Factory [Gamma 1995] describe strategies for isolating a data accessor instantiation within a single component that is conveniently [Marinescu 2002] and [Matena 2003] describe an alternate approach to encapsulating physical data access operations called Data Access Command Beans. Data Access Command Beans define logical database operations using Command [Gamma 1995] Consider using Data Accessor to abstract the data access portion of an Active Domain Object (33) or Object/Relational Map (53). One or more data accessor abstractions can make up layers, as described in Layers (75). You can also define multiple data accessor abstractions for different layers. Each abstraction might address a different aspect or level of data access functionality.

Source: http://www.webkursi.lv/praktdarbi/data_access.pdf