数据库连接池 和 DBUtils实现类

数据库连接池技术

• 数据库连接池的基本思想：就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。

• 数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。

• 数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

数据库连接池技术的优点

1. 资源重用

由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销。在减少系统消耗的基础上，另一方面也增加了系统运行环境的平稳性。

2. 更快的系统反应速度

数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接，避免了数据库连接初始化和释放过程的时间开销，从而减少了系统的响应时间

3. 新的资源分配手段

对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置，实现某一应用最大可用数据库连接数的限制，避免某一应用独占所有的数据库资源

4. 统一的连接管理，避免数据库连接泄漏

在较为完善的数据库连接池实现中，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露

Druid（德鲁伊）数据库连接池

Druid是阿里巴巴开源平台上一个数据库连接池实现，它结合了C3P0、DBCP、Proxool等DB池的优点，同时加入了日志监控，可以很好的监控DB池连接和SQL的执行情况，可以说是针对监控而生的DB连接池，可以说是目前最好的连接池之一。

使用数据库连接池获取连接：

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.util.Properties;

public class DruidJDBCUtils {
    private static DataSource dataSource = null;

    static {
        Properties pros = new Properties();
        try {
            pros.load(ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws Exception {
        return dataSource.getConnection();
    }

}

其中，src下的配置文件为：【druid.properties】

url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&rewriteBatchedStatements=true
username=root
password=
driverClassName=com.mysql.cj.jdbc.Driver

initialSize=10
maxActive=20
maxWait=1000
filters=wall

• 详细配置参数：

配置	缺省	说明
name		配置这个属性的意义在于，如果存在多个数据源，监控的时候可以通过名字来区分开来。 如果没有配置，将会生成一个名字，格式是：”DataSource-” + System.identityHashCode(this)
url		连接数据库的url，不同数据库不一样。
username		连接数据库的用户名
password		连接数据库的密码。如果你不希望密码直接写在配置文件中，可以使用ConfigFilter。详细看这里：https://github.com/alibaba/druid/wiki/使用ConfigFilter
driverClassName		根据url自动识别 这一项可配可不配，如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName(建议配置下)
initialSize	0	初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
maxActive	8	最大连接池数量
maxIdle	8	已经不再使用，配置了也没效果
minIdle		最小连接池数量
maxWait		获取连接时最大等待时间，单位毫秒。配置了maxWait之后，缺省启用公平锁，并发效率会有所下降，如果需要可以通过配置useUnfairLock属性为true使用非公平锁。
poolPreparedStatements	false	是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
maxOpenPreparedStatements	-1	要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
validationQuery		用来检测连接是否有效的sql，要求是一个查询语句。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会其作用。
testOnBorrow	true	申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
testOnReturn	false	归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
testWhileIdle	false	建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
timeBetweenEvictionRunsMillis		有两个含义： 1)Destroy线程会检测连接的间隔时间2)testWhileIdle的判断依据，详细看testWhileIdle属性的说明
numTestsPerEvictionRun		不再使用，一个DruidDataSource只支持一个EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls		物理连接初始化的时候执行的sql
exceptionSorter		根据dbType自动识别 当数据库抛出一些不可恢复的异常时，抛弃连接
filters		属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 监控统计用的filter:stat日志用的filter:log4j防御sql注入的filter:wall
proxyFilters		类型是List，如果同时配置了filters和proxyFilters，是组合关系，并非替换关系

---

Apache-DBUtils实现CRUD操作

DbUtils

DbUtils ：提供如关闭连接、装载JDBC驱动程序等常规工作的工具类，里面的所有方法都是静态的。主要方法如下：

• public static void close(…) throws java.sql.SQLException：　DbUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet。
• public static void closeQuietly(…): 这一类方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLEeception。
• public static void commitAndClose(Connection conn)throws SQLException： 用来提交连接的事务，然后关闭连接
• public static void commitAndCloseQuietly(Connection conn)： 用来提交连接，然后关闭连接，并且在关闭连接时不抛出SQL异常。
• public static void rollback(Connection conn)throws SQLException：允许conn为null，因为方法内部做了判断
• public static void rollbackAndClose(Connection conn)throws SQLException
• rollbackAndCloseQuietly(Connection)
• public static boolean loadDriver(java.lang.String driverClassName)：这一方装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException。

QueryRunner类

• 该类简单化了SQL查询，它与ResultSetHandler组合在一起使用可以完成大部分的数据库操作，能够大大减少编码量。

• QueryRunner类提供了两个构造器：

  • 默认的构造器
  • 需要一个 javax.sql.DataSource 来作参数的构造器

• QueryRunner类的主要方法：

  • 更新
    • public int update(Connection conn, String sql, Object... params) throws SQLException:用来执行一个更新（插入、更新或删除）操作。
    • …
  • 插入
    • public <T> T insert(Connection conn,String sql,ResultSetHandler<T> rsh, Object... params) throws SQLException：只支持INSERT语句，其中 rsh - The handler used to create the result object from the ResultSet of auto-generated keys. 返回值: An object generated by the handler.即自动生成的键值
    • …
  • 批处理
    • public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException： INSERT, UPDATE, or DELETE语句
    • public <T> T insertBatch(Connection conn,String sql,ResultSetHandler<T> rsh,Object[][] params)throws SQLException：只支持INSERT语句
    • …
  • 查询
    • public Object query(Connection conn, String sql, ResultSetHandler rsh,Object... params) throws SQLException：执行一个查询操作，在这个查询中，对象数组中的每个元素值被用来作为查询语句的置换参数。该方法会自行处理 PreparedStatement 和 ResultSet 的创建和关闭。
    • …

  @Test
  public void testInsert() {
      Connection conn = null;
      try {
          QueryRunner queryRunner = new QueryRunner();
          conn = DruidJDBCUtils.getConnection(); // 使用Druid连接池获取连接
          String sql = "insert into customers(name, email, birth) value(?, ?, ?)";
          int insertCount = queryRunner.update(conn, sql, "老王", "laowang@126.com", "1980-03-23");
          System.out.println(insertCount);
      } catch (Exception e) {
          e.printStackTrace();
      } finally {
          DbUtils.closeQuietly(conn);
      }
  }

ResultSetHandler接口及实现类

• 该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式。

• ResultSetHandler 接口提供了一个单独的方法：Object handle (java.sql.ResultSet .rs)。

• 接口的主要实现类：

  • ArrayHandler：把结果集中的第一行数据转成对象数组。
  • ArrayListHandler：把结果集中的每一行数据都转成一个数组，再存放到List中。
  • **BeanHandler：**将结果集中的第一行数据封装到一个对应的JavaBean实例中。
  • **BeanListHandler：**将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里。
  • ColumnListHandler：将结果集中某一列的数据存放到List中。
  • KeyedHandler(name)：将结果集中的每一行数据都封装到一个Map里，再把这些map再存到一个map里，其key为指定的key。
  • **MapHandler：**将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。
  • **MapListHandler：**将结果集中的每一行数据都封装到一个Map里，然后再存放到List
  • **ScalarHandler：**查询单个值对象

代码演示

@Test
public void testQuery() { // 查询一条记录
    Connection conn = null;
    try {
        conn = DruidJDBCUtils.getConnection();

        QueryRunner queryRunner = new QueryRunner();

        BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
        String sql = "select id, name, email, birth from customers where id = ?";
        Customer customer = queryRunner.query(conn, sql, handler, 18);

        System.out.println(customer);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(conn);
    }
}

@Test
public void testQueryList() {
    Connection conn = null;
    try {
        conn = DruidJDBCUtils.getConnection();
        
        QueryRunner runner = new QueryRunner();
        
        String sql = "select id, name, email, birth from customers where id <= ?";
        BeanListHandler<Customer> listHandler = new BeanListHandler<>(Customer.class);
        List<Customer> list = runner.query(conn, sql, listHandler, 18);
        
        list.forEach(System.out::println);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(conn);
    }
}

@Test
public void testQuery2() { // 自定义ResultSetHandler的实现类
    Connection conn = null;
    try {
        conn = DruidJDBCUtils.getConnection();
        QueryRunner runner = new QueryRunner();
        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(1);
                        String name = resultSet.getString(2);
                        String email = resultSet.getString(3);
                        Date birth = resultSet.getDate(4);
                        return new Customer(id, name, email, birth);
                    }
                    return null;
                }
            }
        };

        Customer customer = runner.query(conn, sql, handler, 18);
        System.out.println(customer);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        DbUtils.closeQuietly(conn);
    }
}

/*
 * 如何查询类似于最大的，最小的，平均的，总和，个数相关的数据，
 * 使用ScalarHandler
 * 
 */
@Test
public void testQueryValue() throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = DruidJDBCUtils.getConnection();
		
	// 测试一：
//	String sql = "select count(*) from customers where id < ?";
//	ScalarHandler handler = new ScalarHandler();
//	long count = (long) runner.query(conn, sql, handler, 20);
//	System.out.println(count);
		
	// 测试二：
	String sql = "select max(birth) from customers";
	ScalarHandler handler = new ScalarHandler();
	Date birth = (Date) runner.query(conn, sql, handler);
	System.out.println(birth);
		
	DbUtils.closeQuietly(conn);
}