【Java】数据库连接池

hjhcos

已于 2022-06-18 17:59:26 修改

阅读量1.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Java基础篇文章标签：数据库 java mysql

于 2022-03-16 23:17:30 首次发布

hjhcos

本文链接：https://blog.csdn.net/hjh_cos/article/details/123539153

Java基础篇同时被 2 个专栏收录

28 篇文章

订阅专栏

SQL

14 篇文章

订阅专栏

📚前言

为了解决传统连接数据库的问题：

需要频繁重新连接数据库，增加连接时间开支
不能控制连接数量，导致内存泄露使数据库崩溃

为了解决这个问题，可以使用连接池来约束连接数量以及频繁连接和断开数据库管理系统操作，从而提高程序速度和避免内存泄漏。

📕原理

预先在 ConnectionPool 中存入一定数量的 Connection 实例，当需要与 DBMS 建立连接时，只需从 ConnectionPool 中取出一个 Connection 实例，使用完之后再放进 ConnectionPool 中。

当 ConnectionPool 的 Connection 实例全部用完后，后续需要用到 Connection 实例的 Java 程序需要在等待队列里面等待某个 Connection 实例被释放。

📕流程

📚C3P0

官网：https://www.mchange.com/projects/c3p0/

JAR：https://mvnrepository.com/artifact/com.mchange/c3p0

C3P0 是一个开源的 JDBC 连接池，它实现了数据源与 JNDI 绑定，支持 JDBC3 规范和实现了 JDBC2 的标准扩展说明的 Connection 和 Statement 池的 DataSources 对象，其 0.9.5 版本的 C3P0 完全支持 JDBC4。

📕代码流程

创建数据源

ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

设置数据源

设置 DBMS 驱动

comboPooledDataSource.setDriverClass("com.mysql.jdbc.Driver");

设置 JDBC URL

comboPooledDataSource.setJdbcUrl("JDBC:mysql://localhost:3306/mysql");

设置用户名
```
comboPooledDataSource.setUser("root");
```

设置密码

comboPooledDataSource.setPassword("root");

设置初始连接池大小

comboPooledDataSource.setInitialPoolSize(10);

设置连接池最大大小

comboPooledDataSource.setMaxPoolSize(50);

创建连接池

C3P0PooledConnectionPool out = new C3P0PooledConnectionPool(
    this.cpds, 
    auth, 
    this.getMinPoolSize(userName),
    this.getMaxPoolSize(userName), 
    this.getInitialPoolSize(userName), 
    this.getAcquireIncrement(userName), 
    this.getAcquireRetryAttempts(userName), 
    this.getAcquireRetryDelay(userName), 
    this.getBreakAfterAcquireFailure(userName), 
    this.getCheckoutTimeout(userName), 
    this.getIdleConnectionTestPeriod(userName), 
    this.getMaxIdleTime(userName), 
    this.getMaxIdleTimeExcessConnections(userName), 
    this.getMaxConnectionAge(userName), 
    this.getPropertyCycle(userName), 
    this.getUnreturnedConnectionTimeout(userName), 
    this.getDebugUnreturnedConnectionStackTraces(userName), 
    this.getForceSynchronousCheckins(userName), 
    this.getTestConnectionOnCheckout(userName), 
    this.getTestConnectionOnCheckin(userName), 
    this.getMaxStatements(userName), 
    this.getMaxStatementsPerConnection(userName), 
    this.getConnectionTester(userName), 
    this.getConnectionCustomizer(userName), 
    realTestQuery, 
    this.rpfact, 
    this.taskRunner, 
    this.deferredStatementDestroyer, 
    this.parentDataSourceIdentityToken);

当获取连接时，如果连接池为空，其底层会创建一个连接池，当然也可以自己设置连接池，具体方法可以参考 C3P0 官网。

获取连接

Connection connection = comboPooledDataSource.getConnection();

释放连接
```
connection.close();
```
关闭数据源
```
comboPooledDataSource.close();
```

❌缺少 com/mchange/v2/ser/Indirector 类

ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

当使用上面的语句来创建 DataSource 时，出现了错误说是缺少 com/mchange/v2/ser/Indirector 类。

java.lang.NoClassDefFoundError: com/mchange/v2/ser/Indirector

	at JDBCDemo.JDBCPool.c3p0Pool(JDBCPool.java:20)
	...
	... 52 more

进程已结束，退出代码为 -1

似乎韩顺平老师并没有说明这个情况，因为没有加助手领资料，所有 C3P0 开发包是在 mvnrepository 下载的。

于是乎去 C3P0 官网查看相关教程，看能不能找到解决方法。

Just put the files lib/c3p0-0.9.5.5.jar and lib/mchange-commons-java-0.2.19.jar in your application’s effective CLASSPATH。

官网快速开始里面，第一句话说明创建 DataSource 不仅需要导入 c3p0-0.9.5.5.jar，还需要导入 mchange-commons-java-0.2.19.jar。

📚Druid

官网：https://druid.apache.org

JAR：https://mvnrepository.com/artifact/com.alibaba/druid

Apache Druid is an open-source data store designed for sub-second queries on real-time and historical data. It is primarily used for business intelligence (OLAP) queries on event data. Druid provides low latency (real-time) data ingestion, flexible data exploration, and fast data aggregation. Existing Druid deployments have scaled to trillions of events and petabytes of data. Druid is most commonly used to power user-facing analytic applications.

📕代码流程

创建数据源

DruidDataSource druidDataSource = new DruidDataSource();

设置数据源

druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
druidDataSource.setUrl("jdbc:mysql://localhost:3306/mysql");
druidDataSource.setUsername("root");
druidDataSource.setPassword("root");
druidDataSource.setInitialSize(10);
druidDataSource.setMaxActive(50);

创建连接池
```
while(this.poolingCount < this.initialSize) {
    try {
        PhysicalConnectionInfo pyConnectInfo = this.createPhysicalConnection();
        DruidConnectionHolder holder = new DruidConnectionHolder(this, pyConnectInfo);
        this.connections[this.poolingCount++] = holder;
    } catch (SQLException var18) {
        ...
    }
}
```
根据初始连接数来提前建立连接放入连接池，上面代码中本质代码只有三句。

this.createPhysicalConnection() 会与 DBMS 建立一个连接，并把连接与连接信息返回。

new DruidConnectionHolder(this, pyConnectInfo) 将连接与连接信息包装一下，然后放入连接池 this.connections 里面，当使用时去 this.connections 里面取出连接。

获取连接

Connection connection = druidDataSource.getConnection();

释放连接
```
connection.close();
```
关闭数据源
```
druidDataSource.close();
```

📕通过配置文件设置数据源

# druid.properties
#================== Druid 连接池 配置文件 start ===================
name = default
driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/mysql
username = root
password = root
initialSize = 5
minIdle = 3
maxActive = 50
maxWait = 3000
#================== Druid 连接池 配置文件  end  ===================

Properties properties = new Properties();
properties.load(new FileInputStream("src//JDBCDemo//druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
connection.close();

📕Druid 工具类

#================== Druid 连接池 配置文件 start ===================
name = default
driverClassName = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/mysql
username = root
password = root
initialSize = 5
minIdle = 3
maxActive = 50
maxWait = 3000
#================== Druid 连接池 配置文件  end  ===================

package JDBCDemo;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

@SuppressWarnings({"all"})
public class DruidConPoolUtil {
    private static DataSource ds;
    static {
        try {
            FileInputStream fileInputStream = new FileInputStream("src//JDBCDemo//druid.properties");
            Properties properties = new Properties();
            properties.load(fileInputStream);
            ds = DruidDataSourceFactory.createDataSource(properties);
            fileInputStream.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取连接
     * @return  {@code }
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 关闭结果集、statement、连接
     * @param resultSet
     * @param statement
     * @param connection
     */
    public static void close(ResultSet resultSet, Statement statement, Connection connection) throws SQLException {
        if(resultSet!=null)resultSet.close();
        if(statement!=null)statement.close();
        if(connection!=null)connection.close();
    }
}