Java如何创建数据库连接池？

本示例说明如何使用Apache Commons DBCP库创建连接池实现。

package org.nhooo.example.commons.dbcp;

import org.apache.commons.dbcp2.*;
import org.apache.commons.pool2.impl.GenericObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class ConnectionPoolExample {
    private static final String URL = "jdbc:mysql://localhost/nhooo";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "";

    private GenericObjectPool<PoolableConnection> connectionPool = null;

    public DataSource setUp() {
        // 创建一个连接工厂对象，该对象将由
        //创建连接对象的池。我们通过了
        // JDBC URL信息，用户名和密码。
        ConnectionFactory cf = new DriverManagerConnectionFactory(
            ConnectionPoolExample.URL,
            ConnectionPoolExample.USERNAME,
            ConnectionPoolExample.PASSWORD);

        // 创建一个PoolableConnectionFactory，它将包装
        // ConnectionFactory创建的要添加的连接对象
        // 对象池功能。
        PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf, null);
        pcf.setValidationQuery("SELECT 1");

        // 创建一个GenericObjectPool实例，其中包含
        // 连接对象池。
        GenericObjectPoolConfig<PoolableConnection> config = new GenericObjectPoolConfig<>();
        config.setTestOnBorrow(true);
        config.setMaxTotal(10);
        connectionPool = new GenericObjectPool<>(pcf, config);
        pcf.setPool(connectionPool);

        return new PoolingDataSource<>(connectionPool);
    }

    private GenericObjectPool<PoolableConnection> getConnectionPool() {
        return connectionPool;
    }

    public static void main(String[] args) throws Exception {
        ConnectionPoolExample demo = new ConnectionPoolExample();
        DataSource dataSource = demo.setUp();
        demo.printStatus();

        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement("SELECT * FROM authors")) {
            demo.printStatus();

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println("Name: " + rs.getString("name"));
            }
        }

        demo.printStatus();
    }

    /**
     * Prints connection pool status.
     */
    private void printStatus() {
        System.out.println("Max   : " + getConnectionPool().getNumActive() + "; " +
            "Active: " + getConnectionPool().getNumActive() + "; " +
            "Idle  : " + getConnectionPool().getNumIdle());
    }
}

该代码显示以下状态作为输出示例：

Max   : 0; Active: 0; Idle  : 0
Max   : 1; Active: 1; Idle  : 0
Name: Raoul-Gabriel Urma
Name: Mario Fusco
Name: Alan Mycroft
Max   : 0; Active: 0; Idle  : 1

Maven依赖

<!-- https://search.maven.org/remotecontent?filepath=org/apache/commons/commons-dbcp2/2.6.0/commons-dbcp2-2.6.0.jar -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-dbcp2</artifactId>
    <version>2.6.0</version>
</dependency>
<!-- https://search.maven.org/remotecontent?filepath=org/apache/commons/commons-pool2/2.6.2/commons-pool2-2.6.2.jar -->
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-pool2</artifactId>
    <version>2.6.2</version>
</dependency>
<!-- https://search.maven.org/remotecontent?filepath=mysql/mysql-connector-java/8.0.17/mysql-connector-java-8.0.17.jar -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.17</version>
</dependency>