分析DbUtils: JDBC Utility Component Examples(QueryRunner and ResultSetHandler)

// 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 DataSourceQueryRunner run = new QueryRunner(dataSource);// Execute the query and get the results back from the handlerObject[] result = run.query(    "SELECT * FROM Person WHERE name=?", h, "John Doe");

你也可以使用java.sql执行之前的查询。连接对象,而不是数据源。注意,您在本例中负责关闭连接。

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);  
}

你不仅可以从数据库获取数据,还可以插入或更新的数据。下面的例子将首先将一个人插入到数据库之后,改变人的高度。

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类有相同的方法QueryRunnercalls;然而,返回一个可调用的方法。

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
在上面的示例中我们实现了ResultSetHandler接口将第一行的ResultSet对象[]。这是一个相当通用的实现,可以跨多个项目重用。在识别DbUtils org.apache.commons.dbutils.handlers包提供了一组ResultSetHandler实现执行常见的转换成数组,地图,和javabean。有一个版本的每个实现,将第一行,另一个在theResultSet转换所有行。


我们将从一个示例开始使用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"); 


 
 
这次我们将使用从ResultSet BeanListHandler获取所有行,把它们变成javabean的列表。
 
 

  
  

  
  

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);