服务端的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.

使用setObject()方法 解决类型问题
这种方法是使用PreparedStatement.setObject()来代替setXXXX()，该方法先使用映射(reflection)m描述出Java对象类型,然后把它转化成合适的SQL类型.这种转化是利用JDBC map,如果在map中无法找到转化关系，那就会抛出异常

不过你不能在setObject()中使用Java基本类型，你必须使用它们相应的包装类，比如，设定在模板中第一个long型参数articleID，应使用setObject(1, (new Long(articleID))。使用ResultSet.getObject()来返回该字段结果

Put everything together
Class AccessMethods (see AccessMethods.java) puts together the database access approach I discussed earlier. In addition to type conversion, genericness also requires you to process other schema information, such as table field names at runtime. Example method getSQLList() creates correct SQL field name list substrings for INSERT/SELECT statements from an input array of field names.

AccessMethods.main() puts dummy data into the Article table and then retrieves the data. With the following four steps, you can create an access object for any database table:

Create a new AccessMethods instance using the correct JDBC driver, database connection, authentication, and table name information:

综合处理
Class AccessMethods（见AccessMethods.java），使用了我前面所说的把所有的数据访问放在一起。另外又加入了类型转换、不同数据库的一般性处理。范例方法getSQLList()利用传入的字段名数组创建一个SQL 字段名列表，作为INSERT/SELECT SQL语句的子串

AccessMethods.main()向Article表存入虚数据并返回这些数据，按如下的4个步骤，你可以为各种数据库表创建访问对象

创建一个AccessMethods实例，确认使用的JDBC driver、数据库连接、认证信息和表名信息 ：

AccessMethods acc = new AccessMethods( "org.gjt.mm.mysql.Driver",
"jdbc:mysql://localhost/JWTest",
"username",
"password",
"Article" );



Create an array of strings to hold the field names:
创建一个表名数组

String [] fieldnames = { "ArticleID", "Title", "Text",
"WordCount", "SubmitDate", "Rating" };



If you want to insert a data row into the table, you can first create an object array to hold field values. You should arrange the field values array in the same order as the field names array in Step 2. Then you can call method insert() and pass the field names and field value arrays as parameters:

如果你要向表中插入一行数据，你可以先创建一个字段值数组，要和字段名数组的对应

// Make up some data

fieldvalues_in[0] = new Long(0);

fieldvalues_in[1] = new String("Dummy Article");

fieldvalues_in[2] = new String("This is a dummy article");

fieldvalues_in[3] = new Integer(5);

fieldvalues_in[4] = new Timestamp ( (new java.util.Date()).getTime());

fieldvalues_in[5] = new BigDecimal(2.5);

// Store data in database
acc.insert( fieldnames, fieldvalues_in );



The insert() method compiles a SQL template for the SQL command INSERT from the field names array, sets the values in the template using the field values array, and then executes the generated SQL statement:

insert() 方法主要是对SQL模板编译、设置字段参数、执行SQl查询,以下是insert()的内容


// Prepare the template
String SQLstr = "INSERT INTO " + TABLENAME + " ( " +
getSQLList(fieldnames) + " ) VALUES ( " +
getSQLList(paras) + " ) ";
Connection conn = getConn();
PreparedStatement pstmt = conn.prepareStatement(SQLstr);
// set parameter values
for (int i = 0; i < fieldnames.length; i++) {
pstmt.setObject(i + 1, fieldvalues[i]);
}
// execute SQL statement
boolean succ = pstmt.execute();



If you want to retrieve a data row from the table, you can call method select() and pass to it the field names array and the desired row&acute;s primary key field name and value. The returned object array contains the row field values:

The select() method executes a SQL SELECT command to retrieve a row in a ResultSet object using the primary key name:value pair idFieldname and idValue:

如果你要从表中读取一行数据,使用select()方法,传入行主键名和值以及字段名数组,返回一个数组包含字段的值


// Retrieve data

fieldvalues_out = acc.select( fieldnames, "ArticleID", (new Long(0)));

select()方法内容:

// Prepared the template
String SQLstr = "SELECT " + getSQLList(fieldnames) + " FROM " +
TABLENAME + " WHERE " + idFieldname + " = ?";

Connection conn = getConn();
PreparedStatement pstmt = conn.prepareStatement(SQLstr);

// set parameter value
pstmt.setObject(1, idValue);

// execute SQL statement
ResultSet rs = pstmt.executeQuery();

// go to the first record
rs.next();
for ( int i = 0; i < fieldnames.length; i++) {
result[i] = rs.getObject(i+1);
}


I only implement the INSERT and SELECT SQL statements. You can implement more statements in a similar manner. For simplicity, I excluded the exception-handling code in the examples.


Besides simple types as I illustrated, my approach also works with complex data structures. For example, if you want to put a disk file&acute;s binary content into a SQL Blob field, you could define a wrapper class extending FileInputStream and implementing the Blob interface. Then you can pass the wrapper class to setObject(), which then writes the binary stream into the appropriate Blob field. The setObject() method also lets you use custom SQL types. You could just implement a corresponding Java type that implements the SQLData interface and then tell the connection object the new SQL-Java type mapping via the Connection.setTypeMap() method.

上例只是实现了INSERT 和 SELECT 的SQL查询,你可以实现更多的操作,简化起见,没有包含异常处理的代码

除了简单的数据类型,该方法还可以处理复杂的数据结构.比如,如果你想要把一个文件的二进制内容放入数据库的Blob 类型字段,你会定义一个继承FileInputStream、实现Blob接口的包装类，然后你可以把该包装类传入setObject()，可以实现这种类型的写入操作。setObject()还可以让你定制自定的SQL类型，你先编写一个相应的Java自定类型的实现并实现接口SQLData，然后使用Connection.setTypeMap()告诉connection对象新的类型匹配

Java-SQL exchange
Now you have a flexible yet powerful approach to accessing arbitrary database tables using JDBC&acute;s PreparedStatement interface and setObject() method. Using this tip&acute;s technique, you can develop complex class structures to help exchange data between SQL relational database tables and Java objects.

数据交换
现在你知道了使用JDBC的PreparedStatement和它的setObject()方法,使用它们还可以帮助你在关系数据库和Java对象间交换数据的代码

有关文章参考和原作者介绍,请参考原文:[英文原文]

译者注:在实际的使用中,很多应用服务器环境都有很多JDBC 的实现,通常在设定JDBC连接池或其他数据源时会设定使用的JDBC driver、数据库连接、认证信息,你不一定需要在AccessMethods.main()中去作,但编写一个AccessMethods类似的class来集中处理一些操作 ,比如getSQLlist这样的工作,可以大大减少你在逻辑代码中的工作量,也可以使代码变的更简洁