dbutils工具

commons=dbutils是对JDBC的简单封装
API介绍：
• org.apache.commons.dbutils.QueryRunner
• org.apache.commons.dbutils.ResultSetHandler
• 工具类
• org.apache.commons.dbutils.DbUtils、。

dbutils 核心

• QueryRunner 类
  • query 用于执行select
  • update 用于执行update delete insert
  • batch 用于执行批处理
• ResultSetHandler 接口，定义结果集的封装
  • handle(java.sql.ResultSet rs)
  • 提供9个实现类
• Dbutils 提供如关闭连接、装载、rollback,commit JDBC驱动程序等常规工作的工具类，静态的方法。

QueryRunner 类

• 初始化
  • new QueryRunner(); 事务是手动控制
  • new QueryRunner(DataSource ds); 事务是自动事务，简言之，一条sql一条事务
• 三个核心方法

public class QueryRunnerTest {
    //使用无参数的
    public void fun1() throws Exception{
        String sql = "select * from account";
        QueryRunner runner = new QueryRunner();//事务手动控制
        Connection con = DataSourceUtils.getConnection();
        runner.query(con,sql,new BeanListHandler(Account.class));
    
    }
    
    //使用有参数的
    public void fun2() throws Exception{
        String sql = "select * from account";
        QueryRunner runner = 
                new QueryRunner(DataSourceUtils.getDataSource());
        runner.query(sql,new BeanListHandler(Account.class));
    
    }
}

ResultSetHandler 的9 个实现类

• ArrayHandler 和 ArrayListHandler 将数据表的每行记录保存Object[] 中
• BeanHandler 和 BeanListHandler 将数据表每行记录 保存JavaBean对象中封装javabean属性时，必须保证数据表列名与 javabean属性名一致，否则无法封装
• MapHandler和 MapListHandler 将结果每行记录保存到一个Map集合，key是列名，value是值
• ColumnListHandler 查询结果集中指定一列数据
• KeyedHandler(name) 结果集每行数据封装map，再将map存入另一个map 作为value，指定一列作为key
• ScalarHandler 进行单值查询 select count(*) from account;

public class ResultSetHandlerTest {
    //ArrayHandler
    public void fun1() throws SQLException{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        //返回一个数组
        Object[] obj = (Object[]) runner.query("select * from account", new ArrayHandler());
        System.out.println(obj);
    }
    
    //ArrayListHandler
    public void fun2() throws SQLException{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        //返回一个数组
        List<Object[]> obj = (List<Object[]>) runner.query("select * from account", new ArrayListHandler());
        System.out.println(obj);
    }
    
    //BeanHandler 将结果集中第一条记录直接封装到一个javabean中
    public void fun3() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        Account obj = runner.query("select * from account", 
                new BeanHandler<Account>(Account.class));
        
        System.out.println(obj);
    }
    
    //BeanListHandler 将结果集中每一条记录封装到javabean中，再到list集合
    public void fun4() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        List<Account> obj = runner.query("select * from account", 
                new BeanListHandler<Account>(Account.class));
    }
    
    //ColumnListHandler 查询结果集中指定列的内容封装到list集合
    public void fun5() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        Object obj = runner.query("select * from account", 
                new ColumnListHandler("name"));
    }
    
    //MapHandler 将结果集中第一条记录封装到map集合中，key是字段名称，value是指
    public void fun6() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        Map<String, Object> obj = runner.query("select * from account", 
                new MapHandler());
    }
    
    //MapListHandler  把所有结果封装到map中，再封装为List
    public void fun7() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        List<Map<String, Object>> obj = runner.query("select * from account", 
                new MapListHandler());
    }
    
    //KeyedHandler(name) 结果集每行数据封装map，再将map存入另一个map 作为value，指定一列作为key
    public void fun8() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        Map<Object,Map<String,Object>> obj = runner.query("select * from account", 
                new KeyedHandler());
    }
    
    //ScalarHandler 进行单值查询 select count(*) from account,返回一个long类型数据
    public void fun9() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
        long obj = (Long) runner.query("select count(*) from account", 
                new ScalarHandler());
    }
}