Introduction to Java Database Connection Pool c3p0

2021-12-13 08:16:31
OfStack

Directory 1, Configuration Parameter 1.1, Basic Configuration 1.2, connection pool size 1.3, Connection pool size and connection lifetime 1.4, connection test 1.5, precompile pool 1.6, database interrupt recovery 1.7, custom connection lifecycle management 1.8, handling uncommitted transactions 1.9, debugging 1.10, avoiding hot deployment memory leaks 1.11, other configuration 2, original connection operation 3, use 3.1, direct use 3.1. 1, introduce dependency 3.1. 2, use example 3.2, use 3.1. 1 in SpringBoot, introduce dependency 3.1. 2, single data source 3.1. 3, multiple data sources

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 into C3P0ProxyConnection 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;