DBUtils框架简化JDBC开发

 commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。

下面是DBUtils对数据的crud示例：

package cn.test.jdbc;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import cn.itcast.Utils.JdbcUtils;
import cn.itcast.domain.Account;
public class Demo1 {
	
	@Test
	public void add() throws Exception{
		QueryRunner qr =new QueryRunner(JdbcUtils.getDataSource());//从连接池中获取连接
		String sql ="insert into account(name,money)  values(?,?)";
		Object params[]={"ddd",1000};
		qr.update(sql, params);
	}
	
	@Test
	public void delete() throws Exception{
		QueryRunner qr =new QueryRunner(JdbcUtils.getDataSource());
		String sql ="delete from account where id=?";
		qr.update(sql, 85);
	}
	
	@Test
	public void update() throws Exception{
		QueryRunner qr =new QueryRunner(JdbcUtils.getDataSource());
		String sql ="update  account set money=? where id=?";
		Object params[]={10,3};
		qr.update(sql,params);
	}
	
	@Test
	public void find() throws Exception{
		QueryRunner qr =new QueryRunner(JdbcUtils.getDataSource());
		String sql ="select * from account where id=?";
		//将查询到的结果封装到实体中
		Account a=(Account) qr.query(sql,66, new BeanHandler(Account.class)); 
		System.out.println(a.getMoney());
	}
	
	@Test
	public void getAll() throws Exception{
		QueryRunner qr =new QueryRunner(JdbcUtils.getDataSource());
		String sql ="select * from account";
		//将查询到的结果封装到集合中
		List list=(List) qr.query(sql, new BeanListHandler(Account.class));
		System.out.println(list);
	}
	
	//批处理
	@Test
	public void batch() throws Exception{
		QueryRunner qr =new QueryRunner(JdbcUtils.getDataSource());
		String sql ="insert into account(name,money)  values(?,?)";
		Object parame[][]=new Object [20][];
		for(int i=0;i<20;i++){
			parame[i]=new Object[]{"aa"+i,1000};
		}
		qr.batch(sql, parame);
	}
}

测试各个结果集处理器：

	@Test
	public void test1() throws Exception{
		QueryRunner qr =new QueryRunner(JdbcUtils.getDataSource());
		String sql ="select * from account";
		//获取结果集的第一条数据
		Object result []=(Object[])qr.query(sql,new ArrayHandler());
		System.out.println(result);
	}
	@Test
	public void test2() throws Exception{
		QueryRunner qr =new QueryRunner(JdbcUtils.getDataSource());
		String sql ="select * from account";
		//获取name列的所有集合
		List result =(List)qr.query(sql,new ColumnListHandler("name"));
		System.out.println(result);
	}
	
	@Test
	public void test3() throws Exception{
		QueryRunner qr =new QueryRunner(JdbcUtils.getDataSource());
		String sql ="select * from account";
		//将结果集中的每一行数据都封装一个map中，再将这些map存到一个map中,其key是列名
		Map<Integer,Map<String,Object>> map =(Map) qr.query(sql,new KeyedHandler("id"));
		Set<Entry<Integer, Map<String, Object>>>  set=map.entrySet();
		for(Map.Entry<Integer,Map<String,Object>> entry:set){
			Set<Entry<String, Object>>  innerset=entry.getValue().entrySet();
			for(Map.Entry<String,Object>innerse:innerset){
				System.out.println(innerse.getKey()+"="+innerse.getValue());
			}
		}
	}