2021-11-14 JDBC-ApacheDBUtils实现CRUD

commons-dbutils是Apache组织提供的一个开源JDBC工具类库，对JDBC封装。

runner.update (conn,sql,args)

runner.query(conn,sql,handler,args)

handler用来接收查询结果（接口为ResultSetHandler）

public class QueryRunnerTest {

    @Test
    public void testInsert()
    {
        Connection conn=null;
        try {
            QueryRunner runner=new QueryRunner();
            conn= JDBCUtils.getConnection();
            String sql="insert into customers(name,email,birth)values(?,?,?)";

            int insertCount= runner.update(conn,sql,"aaaa","asf","2000-10-1");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

    @Test

    public void testQuery1() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection();
            String sql="select id,name,email,birth from customers where id<?";
            BeanHandler<Customer> beanHandler=new BeanHandler<>(Customer.class);
//        Customer customer = runner.query(conn, sql, beanHandler, 23);
//        System.out.println(customer);

            BeanListHandler<Customer> handler=new BeanListHandler<>(Customer.class);
            List<Customer> customerList = runner.query(conn, sql, handler, 5);
            customerList.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);

        }
    }

@Test
    public void testQuery2() {
        Connection conn= null;
        try {
            QueryRunner runner=new QueryRunner();
            conn = JDBCUtils.getConnection();
            String sql="select count(*) from customers";
            ScalarHandler handler=new ScalarHandler();

            Long count= (Long) runner.query(conn, sql, handler);
            System.out.println(count);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);

        }
    }
}

自定义ResultSetHandler

       ResultSetHandler<Customer> handler=new ResultSetHandler<Customer>() {
                @Override
                public Customer handle(ResultSet resultSet) throws SQLException {
                    System.out.println("handle");
                    //return null;
                    
                    return new Customer(12,"cc","cc@163.com",2011-1-1);
                }
            };

关闭连接

        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(resultSet);