JDBC知识学习——DBUtils的使用学习

DBUtils工具类

注意：需要导入commons-dbutils-1.6.jar文件
代码示例如下：

public class DBUtilsTest {
    QueryRunner queryRunner=new QueryRunner();
    /*
    * ScalarHandler:把结果集转为一个数值返回
    * */
    @Test
    public void testScalarHandler(){
        Connection connection=null;
        try {
            connection=JDBCTools.getConnectionPool();
            String sql="select count(id) from user";
            Object rs=queryRunner.query(connection,sql,
                    new ScalarHandler<>());
            System.out.println(rs);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null,null,connection);
        }
    }
    /*
    * MapListHandler:将结果集转为一个map的list
    * Map对应查询的一条记录
    * */
    @Test
    public void testMapListHandler(){
        Connection connection=null;
        try {
            connection=JDBCTools.getConnectionPool();
            String sql="select id,name,pwd,sex,home,info from user";
            List<Map<String,Object>> rs=queryRunner.query(connection,sql,
                    new MapListHandler());
            System.out.println(rs);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null,null,connection);
        }
    }
    /*
    * MapHandler:返回sql对应的第一条记录的map对象
    * 键：sql查询的列名，值：列名的值
    * */
@Test
public void testMapHandler(){
    Connection connection=null;
    try {
        connection=JDBCTools.getConnectionPool();
        String sql="select id,name,pwd,sex,home,info from user";
        Map<String,Object> rs=queryRunner.query(connection,sql,
                new MapHandler());
        System.out.println(rs);
    }catch (Exception e){
        e.printStackTrace();
    }finally {
        JDBCTools.releaseSource(null,null,connection);
    }
}
    /**
    *@ClassName DBUtilsTest
    *@Description BeanListHandler：把结果集转为一个集合
    *@Param []
    *@Return void
    *@Date 2020/2/18 12:02
    *@Author Roy
    */
    @Test
    public  void testBeanListHandler(){
        Connection connection=null;
        try {
            connection=JDBCTools.getConnectionPool();
            String sql="select id,name,pwd,sex,home,info from user";
            List<User> users=queryRunner.query(connection,sql,
                    new BeanListHandler<>(User.class));
            System.out.println(users);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null,null,connection);
        }
    }
    /**
    *@ClassName DBUtilsTest
    *@Description BeanHandler：把结果集的第一条记录转为创建Bean Handler
     * 对象时传入的class参数对应的对象。
    *@Param []
    *@Return void
    *@Date 2020/2/18 11:57
    *@Author Roy
    */
    @Test
            public void testBeanHandler(){
        Connection connection=null;
        try {
            connection=JDBCTools.getConnectionPool();
            String sql="select id,name,pwd,sex,home,info from user where id=?";
           User user=queryRunner.query(connection,sql,
                   new BeanHandler<>(User.class),7);
            System.out.println(user);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null,null,connection);
        }
    }
//定义需要传入的的Handler
    class  myResultSetHandler implements ResultSetHandler{
            List<User> users=new ArrayList<>();
        @Override
        public Object handle(ResultSet resultSet) throws SQLException {
            while (resultSet.next()){
                int id=resultSet.getInt(1);
                String name=resultSet.getString(2);
                String pwd=resultSet.getString(3);
                String sex=resultSet.getString(4);
                String home=resultSet.getString(5);
                String info=resultSet.getString(6);
                User user=new User(id,name,pwd,sex,home,info);
                users.add(user);
            }
            return users;
        }
    }
    /**
    *@ClassName DBUtilsTest
    *@Description QueryRunner的query方法返回值取决于ResultSetHandler参数的
     * handle方法的返回值
    *@Param []
    *@Return void
    *@Date 2020/2/18 11:41
    *@Author Roy
    */
    @Test
    public void testQuery(){
        Connection connection=null;
        try {
                connection=JDBCTools.getConnectionPool();
                String sql="select id,name,pwd,sex,home,info from user";
                Object obj=queryRunner.query(connection,sql,new myResultSetHandler());
            System.out.println(obj);
        }catch (Exception e){
e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null,null,connection);
        }
    }
    /*
    *DBUtils类的update方法可用于insert、delete和update
    * */
    @Test
    public void testQueryRunnerUpdate(){
        //创建QueryRunner的实现类
        QueryRunner queryRunner=new QueryRunner();
        String sql="delete from user where  id in (?,?)";
        Connection connection=null;
        try{
                connection=JDBCTools.getConnectionPool();
        //使用update方法
                queryRunner.update(connection,sql,11,12);
            System.out.println("sql执行成功");
        }catch (Exception e){
        e.printStackTrace();
        }finally {
            JDBCTools.releaseSource(null,null,connection);
        }
    }
}