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 {@Testpublic 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);}@Testpublic void delete() throws Exception{QueryRunner qr =new QueryRunner(JdbcUtils.getDataSource());String sql ="delete from account where id=?";qr.update(sql, 85);}@Testpublic 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);}@Testpublic 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());}@Testpublic 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);}//批处理@Testpublic 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);}}

测试各个结果集处理器：

@Testpublic 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);}@Testpublic 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);}@Testpublic 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());}}}