dbutils开源项目用法

dbutils开源项目用法

dbutils开源项目用法： 

项目地址：http://commons.apache.org/dbutils/ 

最新版本为 1.1 

英文文档中这样写到： 

DbUtils is a very small library of classes so it won't take long to go through the javadocs for each class. 

The core classes/interfaces in DbUtils are QueryRunner and ResultSetHandler. 

You don't need to know about any other DbUtils classes to benefit from using the library. 

大概意思是： DbUtils 是一个非常小的类库 因此你不需要花费太长的时间去研究javadocs 

它的核心接口是 QueryRunner 和 ResultSetHandler 

你不需要关心其他DbUtils的类如何使用 

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

QueryRunner 

构造 QueryRunner ： 

QueryRunner() 



QueryRunner(javax.sql.DataSource ds) 



有两种方法来构造QueryRunner 一种是无参数的 一种是有DataSource类型参数的构造方法 

这里使用DataSource 来构造它 使用连接池来构造它的好处就是 我们无需管Connection 对象的创建与关闭了 

后面调用QueryRunner对象的方法也无需传入Connection对象了 

那我们就要用到另外一个开源数据库连接池组件dbcp 

以下为dbcp的用法： 

/** 

* 

* 

* dbcp连接池的用法 

*/ 

public class DBUtils { 

private static Properties properties; 

private static DataSource dataSource; 

private static Connection conn; 

static { 

try { 

properties = new Properties(); 

//载入dbcp的配置文件 

properties.load(DBUtils.class. 

.getResourceAsStream("/dbcpconfig.properties")); 

//创建个BasicDataSourceFactory对象用于创建连接池对象 

BasicDataSourceFactory b = new BasicDataSourceFactory(); 

//把properties对象设置给BasicDataSourceFactory 

dataSource = b.createDataSource(properties); 

} catch (Exception e) { 

e.printStackTrace(); 

} 

} 

public static DataSource getdataSource() { 

return dataSource; 

} 

} 

注意：需要导入dbcp连接池的jar commons-dbcp-1.2.1.jar 和所依赖的 commons-pool-1.2.jar 

commons-collections-3.1.jar 

// 这样我们就可以构造个queryRunner对象了 

QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource()); 



QueryRunner 主要方法 ： 

//更新一列 

queryRunner.update("update Users set username=?,password=? where id = ?", new String[]{"11","22","1"}); 

//删除一列 

　　　　　　queryRunner.update("delete from Users where id =?", 1); 

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

如果要 使用 QueryRunner的查询方法 也就是query()方法的话 那么就需要有对象来接收查询出来的ResultSet 

用什么来接收呢？ 要用到 实现ResultSetHandler 接口的类 

1. BeanHandler 返回个entitybean 对象 

//指定返回哪个 entitybean 

ResultSetHandler rsh = new BeanHandler(Users.class); 

QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource()); 

Users users = (Users) queryRunner.query("select * from Users where id=?",2, rsh); 

System.out.println(users.getUsername()); 

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

2. BeanListHandler 返回个List List里装的是entitybean 对象 

QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource()); 

ResultSetHandler rsh = new BeanListHandler(Users.class); 

List<Users> list = (List) queryRunner.query( 

"select * from Users ", rsh); 

for(Users user:list){ 

System.out.println(user.getUsername()); 

} 

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

3. ArrayListHandler 返回List 对象 list里装的为Object数组 

QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource()); 

// 使用ArrayListHandler 实现类来存放结果会返回ArrayList对象 

ResultSetHandler rsh = new ArrayListHandler(); 

//list里装的是对象数组 

List<Object[]> list = (List) queryRunner.query( 

"select * from Users ", rsh); 

for(Object[] user:list) { 

for(Object obj:user) 

{ 

System.out.println(obj); 

} 

} 

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

4. ColumnListHandler 返回List 对象 



注意: 构造 ColumnListHandler() 对象时候如果 是无参数的话 



像这样 ResultSetHandler rsh = new ColumnListHandler(); 



查询出来的List 里放的类型为主键的类型 值为主键的值; 



ResultSetHandler rsh = new ColumnListHandler(); 

List<Integer> list = (List) queryRunner.query( 

"select * from Users ", rsh); 

//System.out.println(list.get(0).getClass().getSimpleName()); 

for(Integer in:list){ 

System.out.println(in); 

} 

构造 ColumnListHandler() 对象时候如果 是有参数的话 



像这样 ResultSetHandler rsh = new ColumnListHandler(主键值); 



那么查询出来的List 里放的是就是这个主键对应的一行的记录; 



ResultSetHandler rsh = new ColumnListHandler(2); 

List<String> list = (List) queryRunner.query( 

"select * from Users ", rsh); 

for(String in:list){ 

System.out.println(in); 

} 

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

5. KeyedHandler 根据键返回Map 



ResultSetHandler rsh = new KeyedHandler(); 

//map里装的key为主键的值 value 为主键对应的那行记录名称和记录 

Map map = (Map) queryRunner.query( 

"select id,username,password from Users ", rsh); 

//根据主键值出去那一列 

// Map t = (Map) map.get(2); 

System.out.println(map.get(2)); 

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

6. MapHandler 根据键返回Map 

QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource()); 

ResultSetHandler rsh = new MapHandler(); 

Map map = (Map) queryRunner.query( 

"select * from Users ", rsh); 

System.out.println(map); 

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

7. ScalarHandler 根据键返回第一行记录 

QueryRunner queryRunner = new QueryRunner(DBUtils.getdataSource()); 

// 这样就会返回第一行记录名字为username的值 

ResultSetHandler rsh = new ScalarHandler("username"); 

　//这的类型根据表里数据的类型而定 如果上面 ResultSetHandler rsh = new ScalarHandler("username"); 

//取出来的类型为int的话 那么这里也必须由int 来接收 

String result = (String) queryRunner.query("select * from Users where id=99 ", 

rsh); 

System.out.println(result);