8.Apache-DBUtils实现CRUD操作

一、Apache-DBUtils简介

• commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能
• API介绍：
  org.apache.commons.dbutils.QueryRunner
  org.apache.commons.dbutils.ResultSetHandler
  工具类：org.apache.commons.dbutils.DbUtils

二、案例

1.使用QueryRunner测试添加数据的操作

package util_3;

import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;
import util_2.JDBCUtils;

import java.sql.Connection;

//commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，封装了针对于数据库的增删改查操作
public class QueryRunnerTest {
    @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, "马可波罗", "mkbl@qq.com", "1998-04-03");
            System.out.println("插入了" + insertCount + "条记录");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            util_1.JDBCUtils.closeResource(conn, null);
        }
    }
}

2.使用QueryRunner查询表中一条或多条记录的操作

/**
 * 测试查询返回一条记录
 * BeanHandler：是resultSetHandler接口的实现类，用于封装表中的一条记录
 *
 * @throws Exception
 */
@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, 23);
        System.out.println(customer);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        util_1.JDBCUtils.closeResource(conn, null);
    }
}

/**
 * 测试查询返回多条记录
 * BeanListHandler：是resultSetHandler接口的实现类，用于封装表中的多条记录构成的集合
 *
 * @throws Exception
 */
@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> listHandler = new BeanListHandler<Customer>(Customer.class);
        List<Customer> list = runner.query(conn, sql, listHandler, 23);
        list.forEach(System.out::println);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        util_1.JDBCUtils.closeResource(conn, null);
    }
}

/**
 * 测试查询返回一条记录
 * MapHandler：是resultSetHandler接口的实现类，对应表中的一条记录；将字段及相应字段的值作为map中的key和value
 *
 * @throws Exception
 */
@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> map = runner.query(conn, sql, handler, 23);
        System.out.println(map);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        util_1.JDBCUtils.closeResource(conn, null);
    }
}

/**
 * 测试查询返回多条记录
 * MapListHandler：是resultSetHandler接口的实现类，对应表中的多条记录；将字段及相应字段的值作为map中的key和value，将这些map添加到list中
 *
 * @throws Exception
 */
@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 listHandler = new MapListHandler();
        List<Map<String, Object>> mapList = runner.query(conn, sql, listHandler, 23);
        mapList.forEach(System.out::println);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        util_1.JDBCUtils.closeResource(conn, null);
    }
}

3.使用QueryRunner查询表中特殊值的操作

/**
 * ScalarHandler：用于查询特殊值
 */
@Test
public void testQuery5() {
    Connection conn = null;
    try {
        QueryRunner runner = new QueryRunner();
        conn = JDBCUtils.getConnection3();
        String sql = "select count(*) from customers";
        ScalarHandler scalarHandler = new ScalarHandler();
        Long count = (Long) runner.query(conn, sql, scalarHandler);
        System.out.println(count);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        util_1.JDBCUtils.closeResource(conn, null);
    }
}

4.自定义ResultSetHandler的实现类完成查询操作

/**
 * 自定义ResultSetHandler的实现类
 */
@Test
public void testQuery6() {
    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 {
                if (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    String email = resultSet.getString("email");
                    Date birth = resultSet.getDate("birth");
                    Customer customer = new Customer(id, name, email, birth);
                    return customer;
                }
                return null;
            }
        };
        Customer customer = runner.query(conn, sql, handler, 23);
        System.out.println(customer);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        util_1.JDBCUtils.closeResource(conn, null);
    }
}

5.使用dbutils.jar提供的DbUtils工具类，实现资源的关闭

/**
 * 使用dbutils.jar提供的DbUtils工具类，实现资源的关闭
 * @param conn 连接
 * @param ps Ststement
 * @param resultSet 结果集
 */
public static void closeResource1(Connection conn,Statement ps,ResultSet resultSet){
//        try {
//            DbUtils.close(conn);
//            DbUtils.close(ps);
//            DbUtils.close(resultSet);
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
    DbUtils.closeQuietly(conn);
    DbUtils.closeQuietly(ps);
    DbUtils.closeQuietly(resultSet);
}