commons-dbutils-1.4.jar介绍

commons-dbutils-1.4.jar介绍

(2012-03-25 12:01:43)

转载▼

标签： dbutils it

分类： J2EE框架

http://tompig.iteye.com/blog/1450756   (封装方法)

http://www.docin.com/p-582766682.html   (用法说明)

http://blog.youkuaiyun.com/wei_wei_hnu/article/details/6013898

 

1、------------------------------------
包org.apache.commons.dbutils 
DbUtils是一个为简化JDBC操作的小类库.

接口摘要
ResultSetHandler 将ResultSet转换为别的对象的工具.
RowProcessor 将ResultSet行转换为别的对象的工具.

类摘要
BasicRowProcessor RowProcessor接口的基本实现类. 
BeanProcessor BeanProcessor匹配列明到Bean属性名,并转换结果集列到Bean对象的属性中.
DbUtils 一个JDBC辅助工具集合. 
ProxyFactory 产生JDBC接口的代理实现. 
QueryLoader 属性文件加载器,主要用于加载属性文件中的 SQL 到内存中. 
QueryRunner 使用可插拔的策略执行SQL查询并处理结果集.
ResultSetIterator 包装结果集为一个迭代器. 

2、------------------------------------
包org.apache.commons.dbutils.handlers 
ResultSetHandler接口的实现类

类摘要
AbstractListHandler 将ResultSet转为List的抽象类
ArrayHandler 将ResultSet转为一个Object[]的ResultSetHandler实现类
ArrayListHandler 将ResultSet转换为List<Object[]>的ResultSetHandler实现类
BeanHandler 将ResultSet行转换为一个JavaBean的ResultSetHandler实现类
BeanListHandler 将ResultSet转换为List<JavaBean>的ResultSetHandler实现类
ColumnListHandler 将ResultSet的一个列转换为List<Object>的ResultSetHandler实现类
KeyedHandler 将ResultSet转换为Map<Map>的ResultSetHandler实现类
MapHandler 将ResultSet的首行转换为一个Map的ResultSetHandler实现类

MapListHandler 将ResultSet转换为List<Map>的ResultSetHandler实现类
ScalarHandler 将ResultSet的一个列到一个对象

--------------------------------------------------------------------------------------------------

http://dunedain.iteye.com/blog/901534

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;

 

 

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

 

 

System.out.println("toArray()");

testToArray(resultSet);

System.out.println();

 

 

resultSet.beforeFirst();

System.out.println("toBean()");

testToBean(resultSet);

System.out.println();

 

 

resultSet.beforeFirst();

System.out.println("toBeanList()");

testToBeanList(resultSet);

System.out.println();

 

 

resultSet.beforeFirst();

System.out.println("toMap()");

testToMap(resultSet);

System.out.println();

 

DbUtils.close(connection);

}

 

 

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;

}

}

 

 

private static void testToArray(ResultSet resultSet) throws SQLException {

while (resultSet.next()) {

System.out.println(Arrays.toString(processor.toArray(resultSet)));

}

}

 

 

private static void testToBean(ResultSet resultSet) throws SQLException {

while (resultSet.next()) {

System.out.println(processor.toBean(resultSet, Person.class));

}

}

 

 

private static void testToBeanList(ResultSet resultSet) throws SQLException {

List<Person> list = processor.toBeanList(resultSet, Person.class);

System.out.println(Arrays.toString(list.toArray()));

}

 

 

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;

 

 

public class TestQueryRunner {

private static QueryRunner runner = new QueryRunner();

 

public static void main(String[] args) throws Exception {

DatabaseUtils.prepare();

testBatch();

testUpdate();

testQuery();

}

 

 

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;

}

}

 

 

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;

}

}

 

 

private static void testQuery() throws SQLException {

Connection connection = DatabaseUtils.getConnection();

String sql = "select * from person";

 

 

Object[] array = runner.query(connection, sql, new ArrayHandler());

print(ArrayHandler.class, Arrays.toString(array));

 

 

List<Object[]> arrayList = runner.query(connection, sql,

new ArrayListHandler());

print(ArrayListHandler.class, toString(arrayList));

 

 

Person person = runner.query(connection, sql, new BeanHandler<Person>(

Person.class));

print(BeanHandler.class, person.toString());

 

 

List<Person> personList = runner.query(connection, sql,

new BeanListHandler<Person>(Person.class));

print(BeanListHandler.class, Arrays.toString(personList.toArray()));

 

 

List<Object> list = runner.query(connection, sql,

new ColumnListHandler("name"));

print(ColumnListHandler.class, Arrays.toString(list.toArray()));

 

 

Map<Object, Map<String, Object>> keyedMap = runner.query(connection,

sql, new KeyedHandler());

print(KeyedHandler.class, keyedMap.toString());

 

 

Map<String, Object> map = runner.query(connection, sql,

new MapHandler());

print(MapHandler.class, map.toString());

 

 

List<Map<String, Object>> mapList = runner.query(connection, sql,

new MapListHandler());

print(MapListHandler.class, Arrays.toString(mapList.toArray()));

 

 

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[i] = Arrays.toString(arrayList.get(i));

}

return Arrays.toString(rows);

}

}

还有两个很有用的类：QueryLoader和DbUtils。QueryLoader用来将properties文件中的sql加载到内存中（保存到一个Map中），然后可以通过键值获得对应的sql；DbUtils中提供了一组静态方法，用于完成关闭Connection、提交或回滚事务、打印异常等工作。

------------------------------------------------------------------------------------------------------------------------------------------

http://550516671-qq-com.iteye.com/blog/832052

 

需要的jar包，第一要相应的数据库连接的jar包，第二就是commons-dbutils-1.3.jar                  

先看List例子：

package www.libinxuan.dbutils;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

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.handlers.BeanListHandler;

 

public class BeanListExample {

public static void main(String[] args){

DbUtils.loadDriver("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/usertable" ;

String sql = "select * from user";

try {

Connection conn = DriverManager.getConnection(url,"root","root");

QueryRunner qr = new QueryRunner();

List results = (List)qr.query(conn, sql, new BeanListHandler<User>(User.class));

DbUtils.close(conn);

User user = null;

for(int i=0; i<results.size(); i++){

user = (User)results.get(i);

System.out.println("用户名： " + user.getUsername());

System.out.println("密码： " + user.getPassword());

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

Map的例子：

package www.libinxuan.dbutils;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

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.handlers.MapListHandler;

 

public class MapListExample {

public static void main(String[] args){

DbUtils.loadDriver("com.mysql.jdbc.Driver");

String url = "jdbc:mysql://localhost:3306/usertable" ;

String sql = "select * from user";

try {

Connection conn = DriverManager.getConnection(url,"root","root");

QueryRunner qr = new QueryRunner();

List results = (List)qr.query(conn, sql, new MapListHandler());

DbUtils.close(conn);

for(int i=0; i<results.size(); i++){

Map map = (Map)results.get(i);

System.out.println("用户名： " + map.get("username"));

System.out.println("密码： " + map.get("password"));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

User类的例子：

package www.libinxuan.dbutils;

 

public class User {

private String username;

private String password;

public String getUsername() {

return username;

}

public void setUsername(String username) {

this.username = username;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

}