本文共 7362 字，大约阅读时间需要 24 分钟。

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

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
   
     handler = new BeanHandler
    
     (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
   
     handler = new BeanListHandler
    
     (Customer.class);            List
     
       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
   
     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

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
   
     handler = new ResultSetHandler
    
     (){
                   @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);        }    }
    
   

转载地址：http://wauki.baihongyu.com/