使用PreparedStatement为不同的数据库编写可移植的数据库存取方法（1）

服务端的Web application经常需要读取后端的数据库，一些设计良好、高效的数据库存取方法可以大大降低代码的可维护性，从而提升自身应用的复杂性，JDBC的PreparedStatement接口和它的setObject()方法可以帮助你实现

快速、通用的数据库访问方法，应用在任何数据库服务器上。

Java has become increasingly popular as a server-side programming language for database-powered Web applications. Those applications´ business logic-handling methods must work with data from backend databases. Therefore, you need a set of well-designed database Access methods. Those access methods should meet the following requirements:

They should be generic for any table schema. Users can supply the actual table schema information at runtime. Genericness lets you reuse the methods throughout the application to reduce code maintenance overhead and minimize chances for human errors. Genericness also lets you easily add new tables or change existing schema, therefore, making the application more expandable and flexible.

They should be portable across different database servers so the Web application can deploy independent of the backend. Java´s built-in, cross-operating system portability no longer suffices for Web applications.

They should be optimized for speed.

Java现已成为服务端web应用的主要编程语言，几乎所有的商业逻辑都离不开后端数据库的支持，因此，你需要一系列设计良好的数据库访问方法。这些方法需要满足以下一些要求：

对所有的表schema具有通用性，可以在运行时动态地应用某一种形式，这种通用性降低了方法在整个应用中的维护量并最小化人为失误。这种通用性也可以使你任意的增加新的表schema或改变现有的表schema，提高应用的扩展性

它们可以被应用于多种数据库服务器上从而使你的web应用可以独立于后端的数据库，Java的跨平台对web应用还不够，好的应用还需要有多数据库的适应性。还应调整他们的速度性能

In this tip, I discuss how to write generic, portable, and fast database access methods. To illustrate, I provide example code to access an imaginary database table called Article (see Article.sql), which has several fields with different types.

在本文中，我们讨论如何编写通用、可移植、高效的数据库访问方法，为了阐述清楚，我们用实例代码对一个假想数据库表Article进行操作（见Article.sql）,该表中有几个不同类型的字段

Article.sql

# NOTE: This is a schema in MySQL syntax

#

CREATE TABLE Article (

Article.ArticleID BIGINT(20) PRIMARY KEY,

Article.Title TEXT,

Article.Text MEDIUMTEXT,

Article.WordCount INT,

Article.SubmitDate DATETIME,

Article.Rating DECIMAL(2,1)

);

What´s wrong with raw SQL statements?

Access methods can generate raw SQL statements at runtime using database table information provided by the access methods´ caller method. The caller method knows the table schema and could generate the correct SQL text strings for each Java object it wants to store in the database. The access methods then assemble those strings into raw SQL statements and escape any illegal SQL characters.

This approach is simple but not intelligent. A lot of coding and runtime overhead results from the caller producing the correct SQL text string for each Java object. The solution is also slow. Every time the access method sends a raw SQL statement, it runs the overhead of parsing, compiling, and optimizing the statement. An even bigger problem: the raw SQL-based method cannot be cross-server portable. Different database servers have slightly different SQL syntax. For example, some databases expect YYYY-MM-DD type syntax for the SQL Date field while others might expect DD,MM,YYYY syntax. Databases can also have different SQL text escape requirements. For example, MySQL Server uses backslash (\) to escape illegal characters while Microsoft SQL server uses single quote (´). That means any raw SQL-based implementation must target a specific database server.

直接使用 SQL 的问题

通过调用者提供的数据库表信息,数据库访问方法可以生成一般的SQL statement,调用者清楚所要操作的数据库表信息,产生相应的SQL语句,然后数据库访问方法把这些语句转化成SQL statement,并除去一些逃逸字符

这种方法简单,但不聪明.对每一次数据库操作都要产生一个SQL语句，而且这种方法也很慢，每一次访问，都必须重复的进行SQL语句的解析、编译、调整.最大的问题还在于,SQL语句在不同的数据库中是不兼容的。比如一些数据库中的日期类型是YYYY-MM-DD，而另一些可能是DD,MM,YYYY.在逃逸符（escape characters）上各数据库也不尽相同，MySQL使用反斜杠(\)而Microsoft SQL server使用单引号(´).这意味着以纯SQL为实现的访问是面向特定的数据库的

To overcome the problems of the above raw SQL approach, you can use JDBC´s (Java Database Connectivity) PreparedStatement interface.

PreparedStatement for cross-server portability

A PreparedStatement can take a parameterized SQL template and compile it for maximum efficiency. When you need to run a query/update using this template, you only need to reset the parameter values using PreparedStatement.setXXXX() method, where XXXX is the parameter´s Java type. For example, setLong(1, articleID) resets the first parameter in the template to long type value articleID. JDBC´s PreparedStatement automatically generates the complete SQL statement for execution.

要克服如上所述的问题,你可以使用JDBC中的PreparedStatement接口

PreparedStatement使可移植成为可能

PreparedStatement 采用预编译SQL模板来提高效能.当你用它来运行查询/更新,你只需要为不同的参数执行reparedStatement.setXXXX()方法,就可以适应不同的查询,这里XXXX是参数的类型.例如,setLong(1, articleID)可以重置SQL中的第一个long型的值articleID。JDBC的PreparedStatement会自动更新SQL statement来执行查询

Because the JDBC driver generates the SQL statement according to the particular database server´s specifications, you don´t need a manual escape and the JDBC driver takes care of the database-specific SQL syntax. The application is portable across all database servers that have JDBC drivers. Also, the template´s precompilation greatly improves efficiency.

However, to use PreparedStatement´s setXXXX() methods, you must know the database table field´s type at compile time. That does not meet our "generic" requirement. "Java Tip 82: Build Data-Type-Independent JDBC Applications" gives a good discussion on how to convert generic type data from external sources to unknown SQL field types at runtime using the table metadata. But for Web applications, the situation is less complicated. The application knows the table schema at runtime. The Web application can also use the appropriate Java object type for data in each table field. For example, if a field is SQL Date type, the corresponding data in the Java application is probably already a java.sql.Date type object rather than a String containing the time information. This lets you use a simple solution to handle the runtime types.

由于JDBC driver可以按特定的数据库规范来生成SQL statement，所以你不需要手工添加逃逸符，应用程序可以在已有JDBC driver的数据库中移植，模板的预编译可以使效率提高

但是在使用PreparedStatement的setXXXX()方法时，你必须知道表字段的类型，这并不符合一般性需要，在文章 Build Data-Type-Independent JDBC Applications中有关于使用表的标签数据把外部未知的字段类型转化成一般类型的讨论。对于web应用来说，解决的方法相对简单些：在运行时，程序获知表的结构，使用相应的Java数据类型对应于每个字段。例如，如果有个字段是SQL 的Date类型，那应使用Java中的java.sql.Date类型而不是包含日期的String,这是一种简单的处理方法

setObject() method for generic types

This solution uses the PreparedStatement.setObject() method instead of setXXXX() methods. Method setObject() uses reflection to figure out a Java object´s type at runtime before converting it to an appropriate SQL type. The method converts Java to SQL types using a standard JDBC map. If no match is found on the map, the method throws an exception.

Unfortunately, you cannot use Java primitive types with the setObject() method. Instead, you must use the corresponding object wrapper types. For example, if you want to set long type variable articleID into the template´s first parameter, you need to use setObject(1, (new Long(articleID)). You can retrieve the query result data fields as Java objects from ResultSet, using the ResultSet.getObject() method.

（未完待续）