第9章 Apache-Dbutils实现CRUD操作

1. 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

*jar包

2.使用QueryRunner添加数据

 public void testInsert(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtiles3.getConnection();
            String sql="insert into customers(name,email,birth)values(?,?,?)";
            int insertcount = runner.update(conn, sql, "翟天临", "zhaitianlin@163.com", "1995-01-23");
            System.out.println("添加了"+insertcount+"条记录");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtiles.closeResource(conn,null);
        }
    }

 3.使用QueryRunner查询操作

@Test
    public void testQuery1(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtiles3.getConnection();
            String sql="select * from customers where id=?";
            //BeanHandler:是ResultSetHandler接口的实现类，用于封装表中的一条记录
            BeanHandler<Customer> handler = new BeanHandler<Customer>(Customer.class);
            Customer customer = runner.query(conn, sql, handler, 25);
            System.out.println(customer);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtiles.closeResource(conn,null);
        }
    }
    @Test
    public void testQuery2(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtiles3.getConnection();
            String sql="select * from customers where id<?";
            //BeanListHandler:是ResultSetHandler接口的实现类，用于封装表中的多条记录
            BeanListHandler<Customer> listHandler = new BeanListHandler<>(Customer.class);
            List<Customer> customerList = runner.query(conn, sql, listHandler, 25);
            System.out.println(customerList);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtiles.closeResource(conn,null);
        }
    }

4.使用QueryRunner查询特殊值

public void testQuery3(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtiles3.getConnection();
            String sql="select count(1) from customers";
            ScalarHandler handler = new ScalarHandler();
            Object query = runner.query(conn, sql, handler);
            System.out.println(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtiles.closeResource(conn,null);
        }
    }

4.自定义ResultSetHandler

*使用ResultSetHandler的匿名实现类实现

public void testQuery4(){
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtiles3.getConnection();
            String sql="select * from customers where id<?";
            //自定义ResultSetHandler
            ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>(){
                //方法重写
                @Override
                public Customer handle(ResultSet resultSet) throws SQLException {
                    //resultSet就是结果集
                    //在重写方法中写入对结果集的操作
                    System.out.println("handler");
                    return null;
                }
            };
            Customer query = runner.query(conn, sql, handler, 25);
            System.out.println(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtiles.closeResource(conn,null);
        }
    }

5. 使用DbUtils工具类实现资源的关闭

package utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.DbUtils;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtiles3 {
    private static DataSource source;
    static {
        try {
            Properties pros=new Properties();
            InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
            pros.load(is);
            source= DruidDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return source.getConnection();
    }

    //使用DbUtils工具类实现资源的关闭
    public static void closeResource(Connection conn, Statement ps, ResultSet rs){
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }

}