【后端开发】Druid数据库连接池，DbUtils.QueryRunner和DbUtils.closeQuietly的使用实例

其实JDBC的操作在Druid和DbUtils的包里面都被封装好了

所以在建立连接的时候可以使用德鲁伊数据库连接池

数据库的CRUD操作可以用QueryRunner实现

连接的关闭可以使用closeQuietly实现

package com.cls1277.dbutils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.cls1277.preparedstatement.customer.Customer;
import com.cls1277.utils.JDBCutils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Properties;

public class QueryRunnerTest {

    // 德鲁伊数据连接池
    public static Connection getConnection_Druid() throws Exception {
        Properties pros = new Properties();
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        pros.load(is);
        DataSource source = DruidDataSourceFactory.createDataSource(pros);
        Connection conn = source.getConnection();
        return conn;
    }

    @Test
    public void testInsert() throws Exception {
        QueryRunner runner = new QueryRunner();
        Connection conn = JDBCutils.getConnection_Druid();
        String sql = "insert into customers(name,email,birth)values(?,?,?)";
        runner.update(conn, sql, "p", "p@qq.com", java.sql.Date.valueOf("1900-01-01"));
        System.out.println("over");
    }

    //使用BeanHandler：查询一个记录，是ResultSetHandler接口的实现类
    @Test
    public void testQuery1() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCutils.getConnection_Druid();
            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, 1);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResource(conn, null);
        }
    }

    //BeanListHandler：查询多个记录
    @Test
    public void testQuery2() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCutils.getConnection_Druid();
            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, 20);
            for(Customer customer:customers) {
                System.out.println(customer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCutils.closeResource(conn, null);
        }
    }

    //ScalarHandler
    @Test
    public void testQuery3() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCutils.getConnection_Druid();
            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);
        }
    }

    //DbUtils提供的关闭方法
    public static void closeResource_DbUtils(Connection conn, PreparedStatement ps, ResultSet rs) {
//        try {
//            DbUtils.close(conn);
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }
//        try {
//            DbUtils.close(ps);
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }
//        try {
//            DbUtils.close(rs);
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);

    }
}