A Higher Level of Abstraction: Modeling RDBMS Operations as Java Objects
更高级的抽象：建模rdbms操作为java对象
Using the JdbcTemplate class solves most of the problems we saw with use of raw JDBC, but it's still arguably too low-level. Application code using the JdbcTemplate class still requires knowledge of JDBC Statements and ResultSets. The use of callbacks, although essential to move workflow within the framework, is conceptually complex (although the implementations of callback interfaces are usually very simple).
用jdbcTemplate类能解决我们用原生jdbc遇到的大部分问题，但是仍然有争议他是否是低级别。应用代码用jdbctemplate仍然要求jdbc语句和ResultSets的知识。回调的运用，即使必须在框架里移动工作流，在概念上也是复杂的（即使回调接口的实现通常非常简单）。
The com.interface21.jdbc.core package solves the most complex problems of using JDBC, but we want to present the solution more simply to application code. We need a higher level of abstraction, building on the functionality provided by the com.interface21.jdbc.core package.
com.interface21.jdbc.core包解决了用jdbc的复杂工作，但是我们想要呈现更简化的应用代码。我们需要一个更高级别的抽象，构建于com.interface21.jdbc.core提供的功能。
Implementation of the com.interface21.jdbc.object Package
com.interface21.jdbc.object 包的实现
The com.interface21.jdbc.object package conceals the com.interface21.jdbc.core package (on which it is built), offering a higher-level, JDO-like object abstraction. Application code doesn't need to implement callback methods. Instead, each query, or other RDBMS operation, is modeled as a reusable, threadsafe object. Once configured, each object can be run repeatedly, with different parameters, mapped onto SQL bind variables, supplied each time.
com.interface21.jdbc.object 包隐藏了com.interface21.jdbc.core 包（构建于它之上）,提供了更高级，类似jdo对象的抽象。应用代码不需要实现回调方法。取而代之的是，每个查询，或者其他的rdbms操作，建模于一个可重用的，线程安全的对象。一旦配置了，每个对象能被重复的运行，用不同的参数，映射到sql绑定变量，每次被提供。
An important difference from JDO is that because our RDBMS operation objects are classes, not interfaces, it is possible for application queries to subclass them. (Application code using JDO has to obtain instances of the Query interface from a PersistenceManager.) By using an individual subclass for each RDBMS operation we can completely conceal SQL from calling code. Objects representing RDBMS operations may implement interfaces that aren't tied to relational databases.
一个与jdo重要的不同是由于rdbms操作对象是类，不是接口，应用查询可能子类化他们。（应用代码用jdo不得不从PersistenceManager获得query接口的实例）。为每个rdbms操作用一个不同的子类我们能完全隐藏sql从调用代码中。对象呈现rdbms操作实现接口不关联关系数据库。
This approach can be used consistently for queries, updates, and stored procedures. The following UML class diagram illustrates the inheritance hierarchy among the framework classes, and the place of a hypothetical ApplicationSpecificQuery class in it. It also shows how these classes relate to the lower-level JDBC abstraction classes discussed above:
这个方法被用于持续的为查询，更新和存储过程。下面的uml类阐明了在框架类中的继承层次，假设ApplicationSpecificQuery 在。它显示了这些类如何关联到在上面讨论的低级jdbc 抽象类。
Let's look at each class in this inheritance hierarchy in detail. Due to the number of classes involved, it's impossible to provide a complete listing here. Please refer to the com.interface21.jdbc.object package under the /framework/src directory of the download as necessary while reading the description below.
让我们详细的查看每一个继承层次。由于设计一系列的类，不可能提供一个完全的列表。
The RdbmsOperation Base Class
RdbmsOperation is an abstract class and is the root of the class hierarchy. It holds a javax.sql.DataSource and SQL string as instance variables and allows bind parameters to be declared. (Queries, updates and stored procedures share the concept of bind variables.) Once it has been configured, an RdbmsOperation must be "compiled"—another idea borrowed from the JDO Query interface. The meaning of compilation will vary between subclasses, but it will at least validate that a DataSource and SQL have been supplied. After compilation, no more parameters can be added, but the operation can be executed repeatedly.
RdbmsOperation 是一个抽象类并且是这个类层次的根。它持有javax.sql.DataSource和sql字符串作为实例变量并且允许声明绑定参数。（查询，更新和存储过程都是属于绑定变量）一旦它被配置了，rdbmsoperation必须被编译-另一个思想从jdo查询接口中借用过来了。编译的意义将在子类的变化，但是至少确认一个数据源和提供的sql，完成编译后，没有更多的参数能被增加，但是操作可以被重复的执行。
RDBMS operations are JavaBeans. The SQL to execute and the DataSource to use are exposed as bean properties, and subclasses will normally expose their own configuration properties as bean properties.
rdbms操作是javabeans。执行的sql和datasource用于暴露做为bean 属性，子类通常暴露他们字节的配置属性 做为bean 属性。
Parameters are declared using the declareParameter(SqlParameter) method. The SqlParameter class is defined in the com.interface21.jdbc.core package, but is used principally by this package. The main purpose of parameter declaration is to specify each parameter's JDBC type, as enumerated in the java.sql.Types class. This ensures that the framework code can use the JDBC PreparedStatement set<Type>() methods rather than setObject() to set bind variables, which is necessary to ensure correct handling of null values and is potentially more efficient. Parameters to queries and updates don't require names (merely the correct ordering and types); stored procedures support output as well as input parameters, and thus require parameter names.
声明参数用declareParameter（SqlParameter）方法。SqlParameter类定义在com.interface21.jdbc.core包中，主要被用于这个包中。声明参数的主要目的是指定每个参数的jdbc类型，被枚举在java.sql.Types类中。这确保了框架代码能被用于jdbcPerparedStatement set<Type>()方法而不是setObject() 去绑定变量，必须确保正确的处理空值和更好的可移植性。查询和更新参数不要求名字（仅仅正确的排序和类型）；存储过程提供输出和输入变量，要求参数明。
Although the RdbmsOperation class ensures consistent concepts for queries, updates, and stored procedures, it doesn't know how to perform database operations. Subclasses must add methods to perform these.
即使rdbmsoperation类确保查询，更新和存储过程的一致的概念。它不知道如何去执行数据库操作。子类必须添加方法去执行这些。
Important
重要
An RdbmsOperation is a reusable Java object representing a SQL query, update, or stored procedure. RdbmsOperations may have parameters, declared before they are used, which correspond to SQL bind variables. Once configured and "compiled", an RdbmsOperation object can be executed repeatedly, with different parameter values each time.
RdbmsOperation 是一个重用的java object对象代表sql查询，更新或者存储过程。rdbmsoperation可能有参数，声明在他们被用之前，相当于绑定sql变量。一旦配置和编译了，RdbmsOperation 能被重复的执行，每次带不同的值。
Note
注意
The class hierarchy described here is a good example of appropriate use of concrete inheritance. In this case we want to inherit instance variables and implementation, as well as enable polymorphism. As parameterization is largely handled by the com.interface21.jdbc.core package, there is no need to use interface-based design to allow greater flexibility.
具体类继承的描述在这里是一个很好的例子。在这样的情况下我们想去继承实例变量和实现，以及支持多态性。由于参数化被com.interface21.jdbc.core 大量的处理，不需要用基于接口的设计去运行更大的扩展性。
The SqlOperation Class
SqlOperation is an abstract class that extends RdbmsOperation to serve as a superclass for SQL-based queries and updates (as opposed to stored procedures). Its compilation implementation checks that the number of bind variables expected in the SQL statement (that is, the number of ? characters) matches the number of SqlParameters declared, and configures a PreparedStatementCreatorFactory that can efficiently create PreparedStatementCreator objects for the SQL and parameters specified at configuration time. The SqlOperation class creates a JdbcTemplate that subclasses can use to perform database operations.
SqlOperation 是一个抽象类，继承了RdbmsOperation 做为基于sql打的查询和更新服务（与存储过程是相对的）。它的编译实现了检查一系列的绑定变量合法性在sql语句中符合一些列SqlParameters 声明的，配置了一个PreparedStatementCreatorFactory 能有效的创建一个PreparedStatementCreator 对象为sql和特定的参数在配置时间。SqlOperation 类创建了一个JdbcTemplate ，子类能用于去执行数据操作。
The SqlQuery Class
This is the superclass for all query objects, and uses the JdbcTemplate instance variable inherited from SqlOperation to execute queries given SQL and bind variables.
这是所有查询对象的超类，用JdbcTemplate 实例变量从sqloperation中继承去执行查询。
SqlQuery is abstract, using the Template Method design pattern to defer the extraction of results to subclasses, which are required to implement the following protected abstract method:
SqlQuery 是抽象的，利用模板方法模式延迟提取结果到子类中，要求实现下面声明的保护方法：
protected abstract ResultReader newResultReader(
int rowsExpected, Object[] parameters);
We discussed the ResultReader interface earlier: this is defined in the com.interface21.jdbc.core package and saves results from RowCallbackHandler callbacks in a List of objects, each representing the data from one row.
我们先前讨论了ResultReader 接口：被定义在com.interface21.jdbc.core 保存结果从RowCallbackHandler 回调在对象list中，每个呈现了一行数据。
Like the JDO Query interface, the SqlQuery class provides a number of convenient execute() methods with different arguments, such as execute(int), execute(int, int) and execute(String). As with the JDO Query, the execute(Object[] method—which all other execute() methods invoke—takes an array of Object as an argument. The arguments to execute() methods represent the dynamic values of the bind variables declared in the SqlQuery object. All execute() methods return a list of results, produced by the SqlQuery's ResultReader implementation.
像jdoquery接口，sqlquery类提供了一系列方便了execute() 方法用不同的参数，例如execute(int), execute(int, int) 和execute(String).
The SqlQuery class also exposes a number of convenient findObject() methods. These are analogous to single-object entity bean finders, such as findByPrimaryKey(); they raise an error if more than one object matches.
SqlQuery 类也暴露了一系列方便的findObject() 方法。这些类似与单对象实体bean查找者。例如findByPrimaryKey(); 如果超过一个对象符合的话，他们将抛出一个错误。
Subclasses can either rely on the inherited execute() or findObject() methods, or implement their own query methods with meaningful names. For example, a subclass method that required four parameters might take an object argument and conceal the work of invoking the generic superclass method:
子类也能依靠继承execute() or findObject() 方法，或者实现他们自己的查询方法用有意义的名字。例如，一个子类方法要求4个参数可能带一个对象参数，隐藏通用超类方法调用的工作。
public List purchasesByAddress (Address a) {
return execute (new Object[] {
a.getStreet(), a.getLine2() , a.getPostCode()
});
}
A subclass finder that returned a single Seat object might be implemented as follows, concealing the necessary type cast from code using it:
子类查找者返回了单个seat对象可能被下面的实现，隐藏必要的类型转换：
public Seat findSeat(int seatid) {
return (Seat) super .findObject (seatId);
}
A subclass method might also convert a List returned by an inherited execute() method to a typed array, as the subclass would know what application object represented each row of the query's result.
子类方法可能转换继承execute（）方法返回list到一个类型数组，子类将知道应用对象代表查询结果的没行。
The ManualExtractionSqlQuery Class
Application queries don't usually extend SqlQuery directly, but instead subclass the abstract ManualExtractionSqlQuery subclass of SqlQuery. The ManualExtractionSqlQuery class uses the Template Method design pattern to force subclasses to implement the following abstract method, which is invoked for each row of the ResultSet resulting from the query. However, this is much simpler for subclasses to implement than the SqlQuery newResultReader() method:
protected abstract Object extract (ResultSet rs, int rownum)
throws SQLException;
The implementation will look like that of the RowCallbackHandler interface we looked at above. The ManualExtractionSqlQuery class takes care of building a List from each returned object. The following implementation of the extract() method creates a hypothetical, application-specific Customer object from each row of a returned ResultSet:
protected Object extract (ResultSet rs, int rownum) throws SQLException {
customer cust = new Customer();
cust.setForename(rs.getstring("forename"));
cust.setid(rs.getint("id"));
return cust;
}
Note that subclasses of RdbmsOperation don't need to catch SQLExceptions. Any SQLException thrown will be handled by the JdcTeplate class, as discussed above, causing a generic data access exception to be thrown. Note also that subclass code can easily set multiple property values based on a single column value. We'll look at several complete subclasses of the ManualExtractionSqlQuery class below, when we look at using this framework.
You may be wondering why I didn't go the extra mile and do away with the "manual extraction" of an object from each row of the ResultSet. It would be possible to implement a ReflectionExtractionSqlQuery that used reflection to create a JavaBean and set bean properties from column values from each row of the ResultSet. This is appealing as it further reduces the amount of application code required.
I'm normally optimistic about use of reflection and I've implemented this approach several times. However, I'm not convinced that it adds any real value. It has the following disadvantages:
-
It makes the framework significantly more complex.
-
It arguably moves complexity, rather than eliminating it. For example, it will be necessary to use mappings controlling conversion from RDBMS column values to JavaBean properties. Even if mappings are held outside Java code, they will need to be created and maintained.
-
It doesn't allow for computed properties such as properties based on the value of several columns, without the introduction of great complexity in the framework.
-
It doesn't allow different object types to be used for different rows (for example, if a subclass is sometimes indicated).
This decision shows the Pareto Principle in action. The saving of a relatively few lines of code to extract values from a single row of a ResultSet (which are no longer very complex because there is no need to catch SQLExceptions) doesn't add sufficient value to justify the complexity involved.
The SqlFunction Class
SQL functions can be viewed as special cases of SQL queries, returning a single row. Thus, we can easily apply the same approach. The com.interface21.jdbc.object.SqlFunction class is a simple concrete class that extends ManualExtractionSqlQuery to enable queries whose results can be held in a java int to be run simply by providing SQL and declaring parameters. A SqlFunction object can be constructed as follows:
SqlFunction freeSeatsFunction = new SqlFunction(dataSource,
"SELECT count(seat_id) FROM available_seats WHERE performance_id = ?");
freeSeatsFunction.declareParameter(new SqlParameter(Types.NUMERIC));
freeSeatsFunction.compile()
This SqlFunction can then be used like this:
freeSeatsFunction.run(performanceId);
As with the SqlQuery class, the SqlFunction class provides a number of convenient run() methods with different arguments, as well as a generic form that takes an array of Object.
The SqlUpdate Class
Updates share many concepts with queries, such as SQL, bind variable declarations, and the use of a JdbcTemplate class to help implementation. Hence the SqlUpdate class extends SqlOperation, inheriting the JdbcTemplate helper and the validation logic that checks that bind variable declarations tally with the supplied SQL.
The SqlUpdate class is concrete, as there are no results to extract and no need for subclasses to implement custom extraction. It exposes a number of update() methods, each returning the number of rows affected by the update. As with query methods, all update() methods invoke a generic update method that takes an array of Object parameter values:
public int update(Object[] args)
throws InvalidDataAccessApiUsageException
The StoredProcedure Class
Our modeling naturally supports stored procedures as well as ordinary SQL queries and updates. The abstract StoredProcedure class extends RdbmsOperation, as it doesn't require a JdbcTemplate helper and, as the supplied SQL is merely the name of the stored procedure, it is impossible to validate it against bind variable declarations. (Only when the stored procedure is invoked at run time will an incorrect number of parameters cause a failure.)
Calling a stored procedure using JDBC directly involves creating an object of the Java.sql.CallableStatement interface and providing a call string. Call strings include placeholders like the ones used for JDBC prepared statements, and look like the following example, used in the sample application:
{call reserve_seats(?, ?, ?, ?)}
Once a CallableStatement object is created, invoking the stored procedure requires similar error handling to queries and updates. Stored procedures can return ResultSets, but more often we use output parameters. (The mechanics of getting stored procedures to return ResultSets varies between RDBMSs. It's quite complex in Oracle.)
The StoredProcedure class must be subclassed by application-specific classes. Each subclass effectively becomes a Java proxy for the stored procedure. The only major difference from the queries and updates we've seen is that a stored procedure can have input/output as well as input parameters. The StoredProcedure class automatically builds the call string and conceals the use of a CallableStatement and the necessary error handling. Input parameters are supplied and output parameters returned in java.util.Map objects.