Commons DbUtils:JDBC使用组件
Scope of the Package(包的范围)
Example Usage(使用案例)
英文参见:https://commons.apache.org/proper/commons-dbutils/examples.html基本用法
// Create a ResultSetHandler implementation to convert the
// first row into an Object[].
ResultSetHandler<Object[]> h = new ResultSetHandler<Object[]>() {
public Object[] handle(ResultSet rs) throws SQLException {
if (!rs.next()) {
return null;
}
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
Object[] result = new Object[cols];
for (int i = 0; i < cols; i++) {
result[i] = rs.getObject(i + 1);
}
return result;
}
};
// Create a QueryRunner that will use connections from
// the given DataSource
QueryRunner run = new QueryRunner(dataSource);
// Execute the query and get the results back from the handler
Object[] result = run.query(
"SELECT * FROM Person WHERE name=?", h, "John Doe");
你可以在查询前使用 java.sql.Connection对象替代数据源对象。 请注意,你要负责关闭本例中的连接。
ResultSetHandler<Object[]> h = ... // Define a handler the same as above example
// No DataSource so we must handle Connections manually
QueryRunner run = new QueryRunner();
Connection conn = ... // open a connection
try{
Object[] result = run.query(
conn, "SELECT * FROM Person WHERE name=?", h, "John Doe");
// do something with the result
} finally {
// Use this helper method so we don't have to check for null
DbUtils.close(conn);
}
您不仅可以从数据库获取数据,还可以插入或更新数据。 下面的例子将首先把person数据插入到数据库中,然后再修改person的高度。
QueryRunner run = new QueryRunner( dataSource );
try
{
// Execute the SQL update statement and return the number of
// inserts that were made
int inserts = run.update( "INSERT INTO Person (name,height) VALUES (?,?)",
"John Doe", 1.82 );
// The line before uses varargs and autoboxing to simplify the code
// Now it's time to rise to the occation...
int updates = run.update( "UPDATE Person SET height=? WHERE name=?",
2.05, "John Doe" );
// So does the line above
}
catch(SQLException sqle) {
// Handle it
}
对于长时间运行的调用,您可以使用AsyncQueryRunner来异步地执行调用。 AsyncQueryRunner类具有与QueryRunner调用相同的方法; 但是,这些方法返回一个可调用的对象。
ExecutorCompletionService<Integer> executor =
new ExecutorCompletionService<Integer>( Executors.newCachedThreadPool() );
AsyncQueryRunner asyncRun = new AsyncQueryRunner( dataSource );
try
{
// Create a Callable for the update call
Callable<Integer> callable = asyncRun.update( "UPDATE Person SET height=? WHERE name=?",
2.05, "John Doe" );
// Submit the Callable to the executor
executor.submit( callable );
} catch(SQLException sqle) {
// Handle it
}
// Sometime later (or in another thread)
try
{
// Get the result of the update
Integer updates = executor.take().get();
} catch(InterruptedException ie) {
// Handle it
}
ResultSetHandler Implementations
在上面的例子中,我们实现了将ResultSet(结果集)的第一行转换为对象的ResultSetHandler的接口。这是一个相当通用的实现,可以跨多个项目重用。在识别DbUtils org.apache.commons.dbutils.handlers包中提供了一组ResultSetHandler实现执行常见的转换成数组,地图,和javabean。每个实现都有一个版本,它只转换第一行,而另一个实现转换结果集中的所有行。
我们将从一个示例开始,使用BeanHandler从ResultSet获取一行并将其转换为JavaBean。
QueryRunner run = new QueryRunner(dataSource);
// Use the BeanHandler implementation to convert the first
// ResultSet row into a Person JavaBean.
ResultSetHandler<Person> h = new BeanHandler<Person>(Person.class);
// Execute the SQL statement with one replacement parameter and
// return the results in a new Person object generated by the BeanHandler.
Person p = run.query(
"SELECT * FROM Person WHERE name=?", h, "John Doe");
QueryRunner run = new QueryRunner(dataSource);
// Use the BeanListHandler implementation to convert all
// ResultSet rows into a List of Person JavaBeans.
ResultSetHandler<List<Person>> h = new BeanListHandler<Person>(Person.class);
// Execute the SQL statement and return the results in a List of
// Person objects generated by the BeanListHandler.
List<Person> persons = run.query("SELECT * FROM Person", h);
Custom RowProcessor
每个提供的ResultSetHandler实现都接受一个行处理器来实际地将行转换成对象。 默认情况下,处理程序使用BasicRowProcessor实现,但是您可以实现一个自定义版本来插入。 可能最常见的定制是实现toBean()方法来处理定制的数据库数据类型问题。basicrow处理器使用一个BeanProcessor将ResultSet列转换为JavaBean属性。您可以对处理步骤进行子类化和覆盖,以处理特定于应用程序的数据类型映射。所提供的实现将数据类型转换委托给JDBC驱动程序。
BeanProcessor在BeanProcessor.toBean()javadoc中将列映射到bean属性。列名必须在敏感的情况下与bean的属性名称相匹配。例如,firstname列将通过调用它的setFirstName()方法存储在bean中。但是,许多数据库列名称包括不能使用的字符,或者在Java方法名中不常用的字符。您可以执行以下操作之一来将这些列映射到bean属性:
1、在SQL中别名列名,以便与Java名称匹配:[select social_sec# as socialSecurityNumber from person]。
2、子类BeanProcessor并覆盖mapcolumnstopropertie()方法,以除去这些不合法的字符。