jdbc中DBUtils提供的jar包（QueryRunner）实现CRUD操作

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,封装了针对于数据库的增删改查操作
导入jar包：

1.JDBCUtils

public class JDBCUtils {

    /**
     * 使用Druid数据库连接池技术
     *
     * @return
     * @throws SQLException
     */
    private static DataSource source1;
    /**
     * 静态代码块，类加载的时候执行
     * 把注册驱动程序的代码放在静态代码块中，避免多次获取连接对象时重复调用
     */
    static{
        try {
            Properties pros = new Properties();

            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");

            pros.load(is);

           source1 = DruidDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection3() throws SQLException{

        Connection conn = source1.getConnection();

        return conn;
    }

    /**
     *
     * @Description 使用dbutils.jar中提供的DbUtils工具类，实现资源的关闭
     */
    public static void closeResource(Connection conn, Statement ps, ResultSet rs){

        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
}

2.使用现成的jar中的QueryRunner测试增、删、改的操作:

/**
     * 测试插入
     */
    @Test
    public void testInsert() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection3();

            String sql = "insert into customers(name,email,birth) values(?,?,?)";

            int insertCount = runner.update(conn, sql, "吴世勋", "wsx@163.com", "1994-07-23");

            System.out.println("一共插入了" + insertCount + "条数据");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }

3.使用现成的jar中的QueryRunner测试查询,封装表中的一条记录的操作

  /**
     * 测试查询
     * BeanHander:是ResultSetHandler接口的实现类，用于封装表中的一条记录。
     */
    @Test
    public void testQuery1() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection3();

            String sql = "select id,name,email,birth from customers where id = ?";

            BeanHandler<Customer> handler = new BeanHandler<Customer>(Customer.class);
            Customer customer = runner.query(conn, sql, handler, 22);

            System.out.println(customer);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }

4.查询,返回表中的多条记录构成的集合

 /*
     * BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录构成的集合。
     */
    @Test
    public void testQuery2() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection3();

            String sql = "select id,name,email,birth from customers where id < ?";

            BeanListHandler<Customer> handler = new BeanListHandler<Customer>(Customer.class);
            List<Customer> customers = runner.query(conn, sql, handler, 22);

            customers.forEach(System.out :: println);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }

5.将字段及相应字段的值作为map中的key和value，返回一条记录

 /*
     * MapHander:是ResultSetHandler接口的实现类，对应表中的一条记录。
     * 将字段及相应字段的值作为map中的key和value
     */
    @Test
    public void testQuery3(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection3();

            String sql = "select id,name,email,birth from customers where id = ?";

            MapHandler handler = new MapHandler();
            Map<String, Object> customer = runner.query(conn, sql, handler, 22);

            System.out.println(customer);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }

6.MapListHander，返回多条记录

/*
     * MapListHander:是ResultSetHandler接口的实现类，对应表中的多条记录。
     * 将字段及相应字段的值作为map中的key和value。将这些map添加到List中
     */
    @Test
    public void testQuery4(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection3();

            String sql = "select id,name,email,birth from customers where id <= ? ";

            MapListHandler handler = new MapListHandler();
            List<Map<String, Object>> list = runner.query(conn, sql, handler, 22);
            list.forEach(System.out :: println);

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

7.特殊值查询

/*
     * ScalarHandler:用于查询特殊值
     */
    @Test
    public void testQuery5(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection3();

            String sql = "select count(*) from customers";

            ScalarHandler handler = new ScalarHandler();

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

            System.out.println("该表共有" + count + "条数据");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }
       @Test
    public void testQuery6(){

        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection3();

            String sql = "select max(birth) from customers";

            ScalarHandler handler = new ScalarHandler();

            Date birth = (Date) runner.query(conn, sql, handler);

            System.out.println("该表最小的人出生日期为：" + birth);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }

8.自定义ResultSetHandler的实现类

   /*
     * 自定义ResultSetHandler的实现类
     */
    @Test
    public void testQuery7(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();

            conn = JDBCUtils.getConnection3();

            String sql = "select id,name,email,birth from customers where id = ?";

            ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>(){

                @Override
                public Customer handle(ResultSet resultSet) throws SQLException {
                    //					System.out.println("handle");
//					return null;

//					return new Customer(12, "成龙", "Jacky@126.com", new Date(234324234324L));

                    if(resultSet.next()){
                        int id = resultSet.getInt("id");
                        String name = resultSet.getString("name");
                        String email = resultSet.getString("email");
                        Date birth = resultSet.getDate("birth");

                        Customer cust = new Customer(id,name,email,birth);

                        return cust;
                    }

                    return null;
                }
            };

            Customer customer = runner.query(conn, sql, handler,22);

            System.out.println(customer);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, null, null);
        }
    }