ApacheDBUtils框架学习

ApacheDBUtils框架学习

一、commons-dbutils简介　

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，能极大简化jdbc操作。

commons-dbutilsAPI介绍：

• org.apache.commons.dbutils.QueryRunner
• org.apache.commons.dbutils.ResultSetHandler

工具类：

• org.apache.commons.dbutils.DbUtils

二、QueryRunner类的使用

2.1 使用QueryRunner实现对数据库的增删查改

public class TestApacheDBUtils {
​
    //使用QueryRunner实现数据增加
    public static void testQueryAdd() throws Exception{
        //使用C3P0连接池获去数据源及数据库连接
        Connection connection = DataSourceUtils.getDataSourceByC3P0XML().getConnection();
        QueryRunner runner = new QueryRunner();
        runner.update(connection,"insert into student(sno,sname,sage,saddress) values(5,'乔巴',21,'樱花国') ");
    }
​
    public static void testQueryDelete() throws Exception{
        Connection connection = DataSourceUtils.getDataSourceByC3P0XML().getConnection();
        QueryRunner runner = new QueryRunner();
        runner.update(connection,"delete from student where sno = 5");
    }
​
    public static void testQueryUpdate() throws Exception{
        Connection connection = DataSourceUtils.getDataSourceByC3P0XML().getConnection();
        QueryRunner runner = new QueryRunner();
        Object[] objects = new Object[]{"乔巴",22,"樱花国",4};
        runner.update(connection,"update student set sname = ?,sage = ?,saddress = ? where sno = ?",objects);
    }
​
    public static void main(String[] args) throws Exception{
  
        testQueryAdd();
        testQueryDelete();
        testQueryUpdate();
    }
​
}
 

 

三、ResultSetHandler接口使用

该接口用于处理java.sql.ResultSet，将数据按要求转换为另一种形式。

3.1、ResultSetHandler接口的实现类

• ArrayHandler：把结果集中的第一行数据转成对象数组。
• ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
• BeanHandler：将结果集中的第一行数据封装到一个对应的JavaBean实例中。
• BeanListHandler：将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
• ColumnListHandler：将结果集中某一列的数据存放到List中。
• KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
• MapHandler：将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
• MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List

3.2、测试ResultSetHandler接口

public class TestApacheDBUtils {
​
​
    public static void testArrayHander() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceByC3P0XML());
        Object[] students = runner.query("select *from student where sno > 1",new ArrayHandler());
        System.out.println(students[0]+","+students[1]+","+students[2]+","+students[3]);
    }
​
    public static void testArrayListHander() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceByC3P0XML());
        List<Object[]> students = (List<Object[]>) runner.query("select *from student where sno > 1",new ArrayListHandler());
        for(Object[] student:students){
            System.out.println(student[0]+","+student[1]+","+student[2]+","+student[3]);
        }
    }
​
    public static void testBeanHander() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceByC3P0XML());
        Student student = (Student) runner.query("select *from student where sno > 1",new BeanHandler<Student>(Student.class));
        System.out.println(student.getSno()+","+student.getSname()+","+student.getSage()+","+student.getSaddress());
    }
​
    public static void testBeanListHander() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceByC3P0XML());
        List<Student> students = runner.query("select *from student where sno > 1",new BeanListHandler<Student>(Student.class));
        for(Student student:students ){
            System.out.println(student.getSno()+","+student.getSname()+","+student.getSage()+","+student.getSaddress());
        }
​
    }
​
    public static void testBeanMapHander() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceByC3P0XML());
        Map<Integer,Student> students = runner.query("select *from student where sno > 1",new BeanMapHandler<Integer,Student>(Student.class,"sno"));
​
        Student student = students.get(2);
        System.out.println(student.getSname()+","+student.getSage()+","+student.getSaddress());
​
    }
​
    public static void testMapHander() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceByC3P0XML());
        Map<String, Object> students = runner.query("select *from student where sno > 1",new MapHandler());
​
        System.out.println(students);
    }
​
    public static void testMapListHander() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceByC3P0XML());
        List<Map<String, Object>> students = runner.query("select *from student where sno > 1",new MapListHandler());
        System.out.println(students);
    }
​
​
    public static void testKeyedHander() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceByC3P0XML());
        Map<String,Map<String, Object>> studentsName = runner.query("select *from student where sno > 1",new KeyedHandler<String>("sno"));
​
        System.out.println(studentsName);
    }
​
    public static void testColumnListHander() throws Exception{
        QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSourceByC3P0XML());
        List<String> students = runner.query("select *from student where sno > 1",new ColumnListHandler<String>("sname"));
​
        System.out.println(students);
    }
​
​
​
    public static void main(String[] args) throws Exception{
        testArrayHander();
        testArrayHander();
        testBeanHander();
        testBeanListHander();
        testBeanMapHander();
        testMapHander();
        testMapListHander();
        testKeyedHander();
        testColumnListHander();
       
    }
​
}