A Generic JDBC Abstraction Framework-优快云博客

一个通用jdbc抽象框架

It's not enough to understand the JDBC API and the issues in using it correctly. Using JDBC directly is simply too much effort, and too error-prone. We need not just understanding, but a code framework that makes JDBC easier to use.

那是不够的仅仅用它正确的去理解jdbc api和他的问题。直接用jdbc太复杂了。我们需要的不仅仅是理解，是要一个框架去使jdbc更容易被用。

In this section we'll look at one such solution: a generic JDBC abstraction framework, which we'll use in the sample application, and which can significantly simplify application code and minimize the likelihood of making errors.

在这节中我们将展望这样一个问题：一个通用jdbc抽象框架，将被用在这个简单的应用中，能有效的简化应用程序代码和最小化制造错误的可能。

Important

重要

The JDBC API is too low-level for using it directly to be a viable option. When using JDBC, always use helper classes to simplify application code. However, don't try to write your own O/R mapping layer. If you need O/R mapping, use an existing solution.

jdbc api太低级了，直接用他将是一个最后的选项：当用jdbc时，总是用帮助类去简化应用代码。然而，不要试着写你自己的o/r映射层。如果你需要o/r映射，用一个存在的解决方法。

In this section, we'll examine the implementation and usage of the JDBC abstraction framework used in the sample application, which is included in the framework code in the download accompanying this book. This is a generic framework, which you can use in your own applications to simplify use of JDBC and lessen the likelihood of JDBC-related errors.

在这节中，我们将调试这个jdbc抽象框架的实现和用法在这个简单应用中的。它已经包含在这本书中的框架代码中了。这是一个通用框架，你能把它用在你自己的应用中去简化jdbc的用法并且减少jdbc的错误。

Motivation

动机

Writing low-level JDBC code is painful. The problem is not the SQL, which is usually easy to author and understand, and accounts for little of the total code volume, but the incidentals of using JDBC. The JDBC API is not particularly elegant and is too low-level for use in application code.

写低级jdbc 代码是疼痛的。问题不在sql上，这通常容易去创作和理解，占了少量比例的代码周期，除了附带使用jdbc。jdbc api并不是特别的优雅，它太低级去写应用代码。

As we've seen, using the JDBC API even to perform very simple tasks is relatively complex and error-prone. Take the following example, which queries the IDs of available seats in our ticketing system:

正如我们看见的，用jdbc api可以执行非常简单的任务相当复杂且容易出错。看下面的例子，查询了可用的座位id在我们的票务系统中：

public List getAvailableSeatlds(DataSource ds, int performanceld,
int seatType) throws ApplicationException {

   String sql = "SELECT seat_id AS id FROM available_seats " +
"WHERE performance_id = ? AND price_band_id = ?";                                                         
   List seatlds = new LinkedList();

   Connection con = null;
   PreparedStatement ps = null;
   ResultSet rs = null;
try {
                                                                                                                    
     rs.close();
     ps.close();
   }
catch (SQLException ex) {
throw new ApplicationException ("Couldn't run query [" + sql + "]", ex);
   }
finally {
try {
if (con != null)
         con.close();
     }
catch (SQLException ex) {
// Log and ignore
     }
   }
return seatlds;
 }

Although the SQL is trivial, because of the use of a view in the database to conceal the outer join required, executing this query requires 30-odd lines of JDBC code, few of which actually do anything. (I've highlighted the functional lines of code.) Such a predominance of "plumbing" code that doesn't deliver functionality indicates a poor implementation approach. An approach that makes very simple things so hard is unworkable. If we use this approach every time we need to perform a query, we'll end up with reams of code and the potential for many errors. Important operations like closing the connection, which are incidental to the data retrieval operation, dominate the listing. Note that closing the connection requires a second try…catch block in the finally block of the outer try…catch. Whenever I see a nested try…catch, I feel a powerful desire to refactor that code into a framework class.

即使sql是不重要的，由于使用了视图从而隐瞒了外联结的要求，执行这个查询需要30行jdbc代码，他们中的大多数实际上没做什么。（我高亮了有用的代码）。这样一个卓越的管道代码却没有交付功能，显示了一个贫乏的实现方法。这样一个途径使非常简单的事情变得不切实际。如果我们每次用这种途径去执行一个查询，我们将最终得到大量的代码和潜在的许多错误。重要的操作如关闭连接，和附带的数据查询操作，控制列表。注意关闭连接要求第二个try…catch块的外面，在finally块中。无论合适我看见一个嵌套的try..catch，我就极其渴望同重构代码到框架类中。

Note

注意

I haven't included code to obtain the DataSource, which would use JNDI in a J2EE application. I assume that this is accomplished elsewhere using a suitable helper class to avoid JNDI access further bloating the code.

我没有包含获得dataSource连接的代码，在应用程序中将用jndi。我假定这在其他的合适的帮助类中被完成，以避免进一步的访问使代码膨胀。

A simple abstraction framework can make this query much simpler, while still using the JDBC API under the covers. The following code defines a reusable, threadsafe query object extending a framework class, which we'll look at in detail later:

一个简单的抽象框架能使查询变得更简答，但是任然在底层使用jdbc api。下面的代码定义了一个可重用的，线程安全的查询对象继承了一个框架类，我们将详细的探索在不久：

class AvailabilityQuery extends
    com.interface21.jdbc.object.ManualExtractionSqlQuery {

public AvailabilityQuery (DataSource ds) {
super (ds, "SELECT seat_id AS id FROM available_seats WHERE " +
"performance_id = ? AND price_band_id = ?");
    declareParameter(new SqlParameter (Types. NUMERIC));
    declareParameter(new SqlParameter (Types. NUMERIC));
    compile();
  }

protected Object extract (ResultSet rs, int rownum) throws SQLException {
return new Integer (rs.getlnt("id"));
  }
}

This takes one third of the code of the first version, and every line does something. The troublesome error handling is left to the superclass. We need only to write code to provide the SQL query, provide the bind parameters (after specifying their JDBC types) and extract the results. In other words, we only need to implement application functionality, not plumbing. The framework superclass provides a number of execute methods, one of which we can run like this:

这只占乐乐第一个版本的三分之一的代码，每一行做了一些事。令人讨厌的错误处理被留给了父类。我们需要仅仅写代码提供sql查询，提供绑定参数（在指定他们的jdbc类型后），提供了一系列的执行方法，其中的一个我们可以像这样运行：

AvailabilityQuery availabilityQuery = new AvailabilityQuery (ds);
List 1 = availabilityQuery. execute(1, 1);

Once constructed, we can reuse this query object. The execute() method throws a runtime exception, not a checked exception, meaning that we need only catch it if it's recoverable. The resemblance to the JDO Query interface, which we've just discussed, is intentional. The JDO API is easy to use and already familiar to a growing number of developers, hence it makes sense to apply the same principles to JDBC. The key difference, of course, is that our JDBC query doesn't return mapped objects; changes to the returned objects will have no effect on the database.

一旦构造了，我们能重用这个查询对象。这个execcute()方法抛出了一个运行时异常，不是一个检查时异常，是特意这样的。jdo api容易去用，对于一系列正在增长的开发者熟悉了，因此它是有意义的去应用这个相同的原则到jdbc中。关键的不同点，当然，是我们的jdbc查询不返回映射对象；改变返回对象对我们的数据库没有影响。

In the remainder of this section we'll look at how this abstraction framework is implemented and how it can be used to simplify application code.

在剩下的章节中，我们将深入这个框架是如何被实现的，他如何能被用于简化应用代码。

Aims

目标

Remember the Pareto Principle (the 80:20 rule)? The best results can be achieved with JDBC abstraction by not trying to achieve too much.

还记得帕累托原理嘛（2，8法则）?用jdbc抽象完成最好的结果不是试着去完成太多。

The following areas account for most developer effort using the JDBC API directly and are most error-prone. Our abstraction should try to address each:

下面的区域解释了大多开发者费力用jdbc api直接的，大部分易于出错。我么的抽象必须着手与每个：

Too much code - Queries, for example, must acquire a Connection, a Statement and a ResultSet, and iterate over the ResultSet.
太多的代码--查询，例如，必须要求一个连接，一个statement和一个结果集，遍历这个结果集。
Correct cleanup in the event of errors (ensuring that connections and statements are closed, as we discussed above). Most competent Java developers have cleaned up a lot of broken try…catch blocks around JDBC code, after seeing the number of open connections gradually mount in server-side applications.
在错误事件发生后正确的清理（确保连接和statement被关系，正如我们上面讨论的）。大部分有能力的开发者清理了大量的try..catch块围绕这jdbc代码，在看到一系列的打开的连接逐渐的咋服务器应用上增加。
Dealing with SQLExceptions - These are checked exceptions, which application code shouldn't really be forced to catch, as catching them requires knowledge of JDBC.
处理sqlExceptions--这些是检查时异常，应用代码不应该正真的被迫去捕获，捕获他们要求了解jdbc。
Making it easier for user code to establish the cause of exceptions, without needing to examine SQLState or vendor codes.
是它更容易为用户编码去建立异常的原因，不需要检查sqlstate和厂商代码。

Surprisingly, there don't seem to be many libraries or frameworks that address these problems. Most are much more ambitious (aiming at some form of O/R mapping, for example), meaning they become complex to implement and to use. Hence, I've developed a framework for the sample application, which we'll talk about now and which can be used in any application using JDBC (these classes are actually the most recent in a line of such classes I've developed for various clients).

另人惊讶的是，这而视乎没有许多库或着框架去致力于这些问题。他们中的大部分有更大的抱负（瞄准一些o/r映射的形式，例如）,同时意味着他们变的更复杂去实现和利用。因此，我开发了一个框架为这个简单的应用，我们现在讨论的就是，能被用于任何在用到jdbc的应用中（这些类实际上大部分是我最近开发的为不同的客户。）

This isn't the only way to address the challenges of working with JDBC, but it's simple and highly effective.

这不是仅仅的方式去解决用jdbc工作的挑战，但是它是简单和高效的

Another key problem—the danger of SQL being sprinkled around the application—is best addressed with the DAO pattern, which we've already discussed. In this section, we'll talk about implementing data access objects, not their place in overall application architecture.

另一个关键的问题-sql的危险被遍布在整个应用中--最好致力于dao模式，我们已经讨论过了，在这个章节中，我们将讨论实现数据访问对象，在整个应用中。

转载于:https://www.cnblogs.com/sqtds/archive/2012/12/03/2800527.html