Introduction to Java Database Connection Pool c3p0
- 2021-12-13 08:16:31
- OfStack
Foreword:
c3p0 is an open source database connection pool, which implements JDBC 3 specification. This paper mainly introduces the basic use of c3p0. The software versions used in this paper are Java 1.8. 0_191, c3p0 0.9. 5.5, Spring Boot 2.3. 12. RELEASE.
1. Configuration parameters
1.1. Basic Configuration
Configure
参数 | 默认值 | 描述 |
driverClass | null | 驱动类名称 |
jdbcUrl | null | jdbc 连接 url |
user | null | 用户名 |
password | null | 密码 |
1.2. Connection pool size
参数 | 默认值 | 描述 |
acquireIncrement | 3 | 连接池中的连接耗尽时,1次创建的连接个数 |
initialPoolSize | 3 | 初始连接池大小,介于 minPoolSize 和 maxPoolSize 之间 |
maxPoolSize | 15 | 最大连接数 |
minPoolSize | 3 | 最小连接数 |
1.3. Connection pool size and connection lifetime
参数 | 默认值 | 描述 |
maxConnectionAge | 0 | 连接存活的最长时间(秒),0 表示没有限制。正在使用的连接不会不受此限制。 |
maxIdleTime | 0 | 空闲连接数最大存活时间,0 表示永不过期 |
maxIdleTimeExcessConnections | 0 | 当连接数大于 minPoolSize 时,空闲连接数最大存活时间,0 表示永不过期 |
1.4. Connection Test
参数 | 默认值 | 描述 |
automaticTestTable | null | 测试的表名;如果设置了,c3p0 将使用该表名创建1个空表,并使用其来测试连接,preferredTestQuery 参数将被忽略。 |
connectionTesterClassName | com.mchange.v2.c3p0.impl.DefaultConnectionTester | 连接测试的类名,需实现 com.mchange.v2.c3p0.ConnectionTester 或 com.mchange.v2.c3p0.QueryConnectionTester 接口。 |
idleConnectionTestPeriod | 0 | 空闲连接测试的间隔(秒)。 |
preferredTestQuery | null | 连接测试的语句;如果不设置,将使用 DatabaseMetaData 的 getTables 方法来测试,这可能时比较慢的。 |
testConnectionOnCheckin | false | 连接返回连接池时,是否测试 |
testConnectionOnCheckout | false | 从连接池获取连接时,是否测试 |
1.5. Precompile pool
参数 | 默认值 | 描述 |
maxStatements | 0 | 缓存总体预编译语句的最大数量 |
maxStatementsPerConnection | 0 | 缓存每个连接中预编译语句的最大数量 |
statementCacheNumDeferredCloseThreads | 0 | 清理 statement 缓存的线程数,如果需要设置,应设置为 1。1些数据库,特别是 oracle 会在连接使用时关闭 statement,数据库无法很好的处理这种情况,进而导致死锁。清理的线程会在连接 |
maxStatements
And
maxStatementsPerConnection
If both are 0, the precompiled statement is not cached. If
maxStatements=0 and maxStatementsPerConnection>0
,
maxStatementsPerConnection
It works without limiting the total number of caches; If
maxStatements>0 and maxStatementsPerConnection=0
,
maxStatements
Does not limit the number of caches for a single connection.
1.6. Recovery of database interruption
参数 | 默认值 | 描述 |
acquireRetryAttempts | 30 | 获取连接失败时的重试次数 |
acquireRetryDelay | 1000 | 连接获取重试的时间间隔(毫秒) |
breakAfterAcquireFailure | false | 尝试获取连接失败时,是否声明连接池断开并永久关闭 |
1.7. Custom Connection Lifecycle Management
参数 | 默认值 | 描述 |
connectionCustomizerClassName | null | 连接生命周期管理的自定义类,需实现 com.mchange.v2.c3p0.ConnectionCustomizer 接口 |
1.8. Handling Uncommitted Transactions
参数 | 默认值 | 描述 |
autoCommitOnClose | false | 连接在返回连接池时是否自动提交事务。true,提交事务;false,回滚事务 |
forceIgnoreUnresolvedTransactions | false | 连接在返回连接池时,是否强制不处理事务;强烈不推荐设置为 true。 |
1.9. Debugging
参数 | 默认值 | 描述 |
debugUnreturnedConnectionStackTraces | false | 是否记录活动连接的堆栈信息;如果设为 true,且 unreturnedConnectionTimeout>0,当连接借出时间 > unreturnedConnectionTimeout 时,就会打印连接的堆栈信息,并删除该连接。 |
unreturnedConnectionTimeout | 0 | 连接未返回连接池的超时时间(秒) |
These two parameters can be used to help detect connection leaks.
1.10. Avoid Hot Deployment Memory Leaks
参数 | 默认值 | 描述 |
contextClassLoaderSource | caller | 用于生成 c3p0 线程的类加载器来源,为 caller, library 或 none。caller 表示来源于连接池的调用者;library 表示来源于 c3p0 本身;none 表示使用系统类加载器 |
privilegeSpawnedThreads | false | 生成 c3p0 线程时是否使用 c3p0 库中的 AccessControlContext;默认(false)使用连接池调用者的 AccessControlContext。 |
When applying hot deployment and canceling deployment, connection pool may prevent garbage collection and lead to memory leakage; These two parameters are mainly used to handle this situation.
1.11. Other configurations
参数 | 默认值 | 描述 |
acquireIncrement | 3 | 连接池中的连接耗尽时,1次创建的连接个数 |
initialPoolSize | 3 | 初始连接池大小,介于 minPoolSize 和 maxPoolSize 之间 |
maxPoolSize | 15 | 最大连接数 |
minPoolSize | 3 | 最小连接数 |
For details, please refer to the official website document: https://www.mchange.com/projects/c3p0/# configuration
2. Original Connection Operation
c3p0 provides api to access non-standard interfaces in the original connection:
Turn the connection intoC3P0ProxyConnection
And then call
rawConnectionOperation
Method
Here's how to get the
PostgreSQL JDBC
Driving
CopyManager
Object's method:
Connection connection = (C3P0ProxyConnection) c3p0DataSource.getConnection();
C3P0ProxyConnection castConnection = (C3P0ProxyConnection) connection;
Method method = BaseConnection.class.getMethod("getCopyAPI", new Class[]{});
CopyManager copyManager = (CopyManager) castConnection.rawConnectionOperation(method, C3P0ProxyConnection.RAW_CONNECTION, new Object[]{});
3. Use
3.1. Direct use
3.1. 1, Introducing dependencies
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
3.1. 2. Use examples
package com.abc.demo.general.dbpool;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class C3p0Case {
public static void main(String[] args) throws PropertyVetoException {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
comboPooledDataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
comboPooledDataSource.setJdbcUrl("jdbc:mysql://10.40.9.11:3306/mydb?useUnicode=true&characterEncoding=UTF-8");
comboPooledDataSource.setUser("root");
comboPooledDataSource.setPassword("123456");
comboPooledDataSource.setInitialPoolSize(2);
comboPooledDataSource.setMinPoolSize(2);
comboPooledDataSource.setMaxPoolSize(10);
comboPooledDataSource.setPreferredTestQuery("select 1");
comboPooledDataSource.setIdleConnectionTestPeriod(60);
comboPooledDataSource.setTestConnectionOnCheckout(true);
comboPooledDataSource.setCheckoutTimeout(1000 * 30);
Connection connection = null;
Statement st = null;
ResultSet rs = null;
try {
connection = comboPooledDataSource.getConnection();
st = connection.createStatement();
rs = st.executeQuery("select version()");
if (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(connection);
}
// In actual use 1 Generally, the data source is initialized when the application starts, and the application obtains the connection from the data source; The data source is not closed.
comboPooledDataSource.close();
}
private static void close(Connection connection) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.2. Used in SpringBoot
3.1. 1, Introducing dependencies
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.12.RELEASE</version>
<relativePath />
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.5</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
3.1. 2, single data source
application. yml configuration:
spring:
datasource:
c3p0:
driver-class: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://10.40.9.11:3306/myDb?useUnicode=true&characterEncoding=UTF-8
user: root
password: 123456
initial-pool-size: 2
min-pool-size: 2
max-pool-size: 10
preferred-test-query: select 1
idle-connection-test-period: 60
test-connection-on-checkout: true
checkout-timeout: 30000
Data source configuration class:
package com.abc.demo.config;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
@Bean("dataSource")
@ConfigurationProperties(prefix = "spring.datasource.c3p0")
public DataSource dataSource1() {
return DataSourceBuilder.create().type(ComboPooledDataSource.class).build();
}
}
Use:
@Autowired
private DataSource dataSource;
3.1. 3. Multiple data sources
application. yml configuration:
spring:
datasource:
c3p0:
db1:
driver-class: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://10.40.9.11:3306/myDb?useUnicode=true&characterEncoding=UTF-8
user: root
password: InsYR0ot187!
initial-pool-size: 2
min-pool-size: 2
max-pool-size: 10
preferred-test-query: select 1
idle-connection-test-period: 60
test-connection-on-checkout: true
checkout-timeout: 30000
db2:
driver-class: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://10.40.9.12:3306/myDb?useUnicode=true&characterEncoding=UTF-8
user: root
password: InsYR0ot187!
initial-pool-size: 2
min-pool-size: 2
max-pool-size: 10
preferred-test-query: select 1
idle-connection-test-period: 60
test-connection-on-checkout: true
checkout-timeout: 30000
Data source configuration class:
package com.abc.demo.config;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
@Configuration
public class DataSourceConfig {
@Bean("dataSource1")
@ConfigurationProperties(prefix = "spring.datasource.c3p0.db1")
public DataSource dataSource1() {
return DataSourceBuilder.create().type(ComboPooledDataSource.class).build();
}
@Bean("dataSource2")
@ConfigurationProperties(prefix = "spring.datasource.c3p0.db2")
public DataSource dataSource2() {
return DataSourceBuilder.create().type(ComboPooledDataSource.class).build();
}
}
Use:
@Autowired
@Qualifier("dataSource1")
private DataSource dataSource1;
@Autowired
@Qualifier("dataSource2")
private DataSource dataSource2;