Apache Commons DbUtils

搜ijsio

　　Commons DbUtils包含一组的JDBC辅助类，用来简化JDBC操作。
　　下面用一些简单的例子来演示几个主要类的用法：
　　BasicRowProcessor可以用来处理查询结果集（ResultSet），它可以将ResultSet的一行装换为数组、javabean或Map（键值分别是字段名称和字段值），也可以将整个ResultSet装换为一组javabean。
　　下面一个例子演示BasicRowProcessor的用法：

package com.test.dbutils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import com.test.bean.Person;
import com.test.utils.DatabaseUtils;
/**
* {@link org.apache.commons.dbutils.BasicRowProcessor}
*/
public class TestBasicRowProcessor {
private static BasicRowProcessor processor = new BasicRowProcessor();
public static void main(String[] args) throws Exception {
DatabaseUtils.prepare();
prepareData();
Connection connection = DatabaseUtils.getConnection();
String sql = "select * from person";
ResultSet resultSet = connection.createStatement().executeQuery(sql);
/**
* {@link org.apache.commons.dbutils.BasicRowProcessor#toArray(ResultSet)}
*/
System.out.println("toArray()");
testToArray(resultSet);
System.out.println();
/**
* {@link org.apache.commons.dbutils.BasicRowProcessor#toBean(ResultSet, Class)}
*/
resultSet.beforeFirst();
System.out.println("toBean()");
testToBean(resultSet);
System.out.println();
/**
* {@link org.apache.commons.dbutils.BasicRowProcessor#toBeanList(ResultSet, Class)}
*/
resultSet.beforeFirst();
System.out.println("toBeanList()");
testToBeanList(resultSet);
System.out.println();
/**
* {@link org.apache.commons.dbutils.BasicRowProcessor#toMap(ResultSet)}
*/
resultSet.beforeFirst();
System.out.println("toMap()");
testToMap(resultSet);
System.out.println();
DbUtils.close(connection);
}
/**
* @throws Exception
*/
private static void prepareData() throws SQLException {
Connection connection = DatabaseUtils.getConnection();
connection.setAutoCommit(false);
String sql = "insert into person(id, name, age) values(?, ?, ?)";
Object[][] params = new Object[][] { { "id1", "name1", 20 },
{ "id2", "name2", 25 }, { "id3", "name3", 30 } };
try {
QueryRunner runner = new QueryRunner();
runner.batch(connection, sql, params);
DbUtils.commitAndClose(connection);
} catch (SQLException e) {
DbUtils.rollbackAndClose(connection);
throw e;
}
}
/**
* {@link org.apache.commons.dbutils.BasicRowProcessor#toArray(ResultSet)}
*
* @throws Exception
*/
private static void testToArray(ResultSet resultSet) throws SQLException {
while (resultSet.next()) {
System.out.println(Arrays.toString(processor.toArray(resultSet)));
}
}
/**
* {@link org.apache.commons.dbutils.BasicRowProcessor#toBean(ResultSet, Class)}
*
* @throws Exception
*/
private static void testToBean(ResultSet resultSet) throws SQLException {
while (resultSet.next()) {
System.out.println(processor.toBean(resultSet, Person.class));
}
}
/**
* {@link org.apache.commons.dbutils.BasicRowProcessor#toBeanList(ResultSet, Class)}
*
* @throws Exception
*/
private static void testToBeanList(ResultSet resultSet) throws SQLException {
List<Person> list = processor.toBeanList(resultSet, Person.class);
System.out.println(Arrays.toString(list.toArray()));
}
/**
* {@link org.apache.commons.dbutils.BasicRowProcessor#toMap(ResultSet)}
*
* @throws Exception
*/
private static void testToMap(ResultSet resultSet) throws SQLException {
while (resultSet.next()) {
Map<String, Object> map = processor.toMap(resultSet);
System.out.println(map.toString());
}
}
}

　　另一个重要的类是QueryRunner，这个类提供了一组JDBC操作辅助方法。使用QueryRunner，我们在执行sql命令时就不需要显示地创建和关闭Connection、Statement、ResultSet等对象，只需要提供sql命令、参数（和ResultSetHandler的实现类来处理查询时返回的结果集）给相应的方法，用起来非常方便。
　　在org.apache.commons.dbutils.handlers包中有多个ResultSetHandler的实现类，用来对查询返回的ResultSet进行处理。
　　下面用一个例子演示QueryRunner的和一些ResultSetHandler实现类的用法：

package com.test.dbutils;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.test.bean.Person;
import com.test.utils.DatabaseUtils;
/**
* {@link org.apache.commons.dbutils.QueryRunner}
*/
public class TestQueryRunner {
private static QueryRunner runner = new QueryRunner();
public static void main(String[] args) throws Exception {
DatabaseUtils.prepare();
testBatch();
testUpdate();
testQuery();
}
/**
* {@link org.apache.commons.dbutils.QueryRunner#batch(Connection, String, Object[][])}
*
* @throws Exception
*/
private static void testBatch() throws SQLException {
Connection connection = DatabaseUtils.getConnection();
connection.setAutoCommit(false);
String sql = "insert into person(id, name, age) values(?, ?, ?)";
Object[][] params = new Object[][] { { "id1", "name1", 20 },
{ "id2", "name2", 25 } };
try {
runner.batch(connection, sql, params);
DbUtils.commitAndClose(connection);
} catch (SQLException e) {
DbUtils.rollbackAndClose(connection);
throw e;
}
}
/**
* {@link org.apache.commons.dbutils.QueryRunner#update(Connection, String, Object...)}
*
* @throws Exception
*/
private static void testUpdate() throws SQLException {
Connection connection = DatabaseUtils.getConnection();
connection.setAutoCommit(false);
String sql = "update person set name = ?, age = ? where id = ?";
Object[] params = new Object[] { "name3", 30, "id2" };
try {
runner.update(connection, sql, params);
DbUtils.commitAndClose(connection);
} catch (SQLException e) {
DbUtils.rollbackAndClose(connection);
throw e;
}
}
/**
* {@link org.apache.commons.dbutils.QueryRunner#query(Connection, String, ResultSetHandler)}
*
* @throws Exception
*/
private static void testQuery() throws SQLException {
Connection connection = DatabaseUtils.getConnection();
String sql = "select * from person";
/**
* {@link org.apache.commons.dbutils.handlers.ArrayHandler }
*/
Object[] array = runner.query(connection, sql, new ArrayHandler());
print(ArrayHandler.class, Arrays.toString(array));
/**
* {@link org.apache.commons.dbutils.handlers.ArrayListHandler }
*/
List<Object[]> arrayList = runner.query(connection, sql,
new ArrayListHandler());
print(ArrayListHandler.class, toString(arrayList));
/**
* {@link org.apache.commons.dbutils.handlers.BeanHandler }
*/
Person person = runner.query(connection, sql, new BeanHandler<Person>(
Person.class));
print(BeanHandler.class, person.toString());
/**
* {@link org.apache.commons.dbutils.handlers.BeanListHandler }
*/
List<Person> personList = runner.query(connection, sql,
new BeanListHandler<Person>(Person.class));
print(BeanListHandler.class, Arrays.toString(personList.toArray()));
/**
* {@link org.apache.commons.dbutils.handlers.ColumnListHandler }
*/
List<Object> list = runner.query(connection, sql,
new ColumnListHandler("name"));
print(ColumnListHandler.class, Arrays.toString(list.toArray()));
/**
* {@link org.apache.commons.dbutils.handlers.KeyedHandler }
*/
Map<Object, Map<String, Object>> keyedMap = runner.query(connection,
sql, new KeyedHandler());
print(KeyedHandler.class, keyedMap.toString());
/**
* {@link org.apache.commons.dbutils.handlers.MapHandler }
*/
Map<String, Object> map = runner.query(connection, sql,
new MapHandler());
print(MapHandler.class, map.toString());
/**
* {@link org.apache.commons.dbutils.handlers.MapListHandler }
*/
List<Map<String, Object>> mapList = runner.query(connection, sql,
new MapListHandler());
print(MapListHandler.class, Arrays.toString(mapList.toArray()));
/**
* {@link org.apache.commons.dbutils.handlers.ScalarHandler }
*/
Object object = runner
.query(connection, sql, new ScalarHandler("name"));
print(ScalarHandler.class, object.toString());
DbUtils.close(connection);
}
public static void print(Class<?> clazz, String str) {
System.out.println(clazz.getName());
System.out.println(str);
System.out.println();
}
private static String toString(List<Object[]> arrayList) {
String[] rows = new String[arrayList.size()];
for (int i = 0; i != arrayList.size(); ++i) {
rows = Arrays.toString(arrayList.get(i));
}
return Arrays.toString(rows);
}
}
　　还有两个很有用的类：QueryLoader和DbUtils。QueryLoader用来将properties文件中的sql加载到内存中（保存到一个Map中），然后可以通过键值获得对应的sql；DbUtils中提供了一组静态方法，用于完成关闭Connection、提交或回滚事务、打印异常等工作。