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

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

使用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´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´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´s PreparedStatement interface and setObject() method. Using this tip´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这样的工作,可以大大减少你在逻辑代码中的工作量,也可以使代码变的更简洁。

（全文完）

原文转自：http://www.ltesting.net