java中几种常用连接池的基本使用

使用数据库连接池主要是避免多次重复的资源申请、释放造成GC频繁，资源消耗。这里主要是关于java中dbcp2、alibaba druid、HikariCP这三种常用连接池的基本使用。其他一些不常用的像c3p0、Vibur等就不做介绍了。

数据库基础连接

关于JDBC(Java Database Connectivity)不做介绍，其示例如下：

// db.properties
driver=com.mysql.jdbc.Driver
url=jdbc\:mysql\://localhost\:3306/test
username=root
password=root

// JDBCUtils
public class JDBCUtil {
    static Properties pros = null;

    static {
        pros = new Properties();
        try {
            pros.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() {
        try {
            Class.forName(pros.getProperty("driver"));
            return DriverManager.getConnection(pros.getProperty("url"),
                    pros.getProperty("username"), pros.getProperty("password"));
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
    public static void close(Statement ps, Connection conn) {
        try {
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

// test
Connection conn = null;
PreparedStatement ps = null;
try {
    //加载数据库驱动并获取数据库连接
    conn = JDBCUtil.getConnection();
    //操作数据库
    String sql = "select * from user where dept_id = ?";
    assert conn != null;
    ps = conn.prepareStatement(sql);
    ps.setInt(1, 1);

    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
        System.out.println(rs.getString("username") + " " + rs.getInt("id"));
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JDBCUtil.close(ps, conn);
}

1. alibaba druid

性能较好，功能全面，使用最广泛。Spring整合druid过程如下：

1. pom.xml中添加mysql和druid依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.38</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.3</version>
</dependency>

2. application.yml中配置基本信息

spring:
    datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        druid:
            first:
                url: jdbc:mysql://localhost:3306/test1_system?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
                username: root
                password: root
            second:
                url: jdbc:mysql://localhost:3306/test2_system?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
                username: root
                password: root
            initial-size: 30
            max-active: 100
            min-idle: 20
            max-wait: 60000
            pool-prepared-statements: true
            max-pool-prepared-statement-per-connection-size: 20
            min-evictable-idle-time-millis: 300000
            validation-query: SELECT 1 FROM DUAL
            test-while-idle: true
            test-on-borrow: false
            test-on-return: false
            stat-view-servlet:
                enabled: true
                url-pattern: /druid/*
            filter:
                stat:
                    log-slow-sql: true
                    slow-sql-millis: 1000
                    merge-sql: true
                wall:
                    config:
                        multi-statement-allow: true

3. 打开浏览器，输入：http://localhost:8080/druid/datasource.html查看监控中心，效果如下：
   
4. 结合Mybatis或Spring Data JPA等进行数据库的CURD操作就比较简单了

2. HikariCP

性能最好，身形小巧。其GitHub地址为：https://github.com/brettwooldridge/HikariCP。Microbenchmarks基准测试图如下

单例模式示例如下：

public class DataSource {
 
    private static HikariConfig config = new HikariConfig();
    private static HikariDataSource ds;
 
    static {
        config.setJdbcUrl( "jdbc_url" );
        config.setUsername( "database_username" );
        config.setPassword( "database_password" );
        config.addDataSourceProperty( "cachePrepStmts" , "true" );
        config.addDataSourceProperty( "prepStmtCacheSize" , "250" );
        config.addDataSourceProperty( "prepStmtCacheSqlLimit" , "2048" );
        ds = new HikariDataSource( config );
    }
 
    private DataSource() {}
 
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
}

Connection con = HikariCPDataSource.getConnection();

Spring整合HikariCP过程如下：

1. pom.xml中添加mysql和HikariCP依赖

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>2.7.7</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.38</version>
</dependency>

2. application.yml中配置基本信息

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test_system?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
    hikari:
      maximum-pool-size: 200
      minimum-idle: 20
      connection-timeout: 60000
      idle-timeout: 60000
  # jackson时间格式化
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
  servlet:
    multipart:
      enabled: true
      max-file-size: 10MB
      max-request-size: 10MB

  jmx:
    enabled: false

  # redis
  redis:
    open: true
    database: 0
    host: localhost
    port: 6379
    password:    # 密码默认为空
    timeout: 6000ms   # 连接超时时长
    jedis:
      pool:
        max-active: 1000  # 连接池最大连接数
        max-wait: -1      # 负值表示没有限制
        max-idle: 10
        min-idle: 5 
# mybatis
mybatis:
  config-location: classpath:mybatis.xml
  mapper-locations: classpath:mapper/**/*.xml

3. 结合Mybatis或Spring Data JPA等进行数据库的CURD操作就比较简单了

3. dbcp/dbcp2

DBCP(DataBase connection pool)数据库连接池是apache的一个项目，dbcp2需要JDK7以上版本。其地址为：http://commons.apache.org/proper/commons-dbcp/。
dbcp2的简单示例如下：

//db.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test_system?useUnicode=true&characterEncoding=utf-8
username=root
password=root
initialSize=50
maxTotal=50
maxIdle=35
minIdle=5
maxWaitMillis=1000
removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true
removeAbandonedTimeout=100

//DBCPUtil 
 public class DBCPUtil {
     private static Properties properties = new Properties();
     private static DataSource dataSource;
     static{
        try{
           FileInputStream is = new FileInputStream("src/main/resources/db.properties");  
           properties.load(is);
           //获取数据源对象
           dataSource = BasicDataSourceFactory.createDataSource(properties);
        }catch(IOException e){
           e.printStackTrace();
        }
     }
     //从连接池中获取一个连接
     public static Connection getConnection(){
        Connection connection = null;
        try{
             connection = dataSource.getConnection();
             connection.setAutoCommit(false);
         }catch(SQLException e){
             e.printStackTrace();
         }
         return connection;
     }
 }

 Connection con = DBCPUtil.getConnection();

Spring整合dbcp2过程如下：

1. pom.xml中添加mysql和dbcp2依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.38</version>
</dependency>
<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-dbcp2</artifactId>
  <version>2.0.1</version>
</dependency>

2. application.yml中配置基本信息

spring:
    datasource:
        type: org.apache.commons.dbcp2.BasicDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/test_system?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: root
        dbcp2:
            max-wait-millis: 10000
            min-idle: 10
            initial-size: 20
            validation-query: SELECT x

3. 结合Mybatis或Spring Data JPA等进行数据库的CURD操作就比较简单了

注意1：在多线程环境或者是多数据源环境中，针对并发问题，可灵活使用synchronized等关键字。对于事务的管理、连接池的维护等都需要注意。