Java 数据库连接池c3p0 介绍

前言:

c3p0 是一个开源的数据库连接池，实现了 JDBC 3 规范；本文主要介绍 c3p0 的基本使用，文中使用到的软件版本：Java 1.8.0_191、c3p0 0.9.5.5、Spring Boot 2.3.12.RELEASE。

1、配置参数

1.1、基础配置

配置

1.2、连接池大小

1.3、连接池大小和连接存活时间

1.4、连接测试

1.5、预编译池

maxStatements 和 maxStatementsPerConnection 如果都为 0，将不缓存预编译语句。如果 maxStatements=0 and maxStatementsPerConnection>0，maxStatementsPerConnection 起作用，不限制总的缓存数量；如果 maxStatements>0 and maxStatementsPerConnection=0，maxStatements 起作用，不限单个连接的缓存数量。

1.6、数据库中断的恢复

1.7、自定义连接生命周期管理

1.8、处理未提交的事务

1.9、调试

这两个参数可用于帮助发现连接泄露。

1.10、避免热部署内存泄露

在应用热部署、取消部署时，连接池可能会阻止垃圾回收进而导致内存泄露； 这两个参数主要用于处理这种情况。

1.11、其它配置

详细说明可参考官网文档：https://www.mchange.com/projects/c3p0/#configuration

2、原始连接操作

c3p0 提供 api 来访问原始连接中的非标准接口：

• 把连接转成 C3P0ProxyConnection

• 然后调用 rawConnectionOperation 方法

下面是获取 PostgreSQL  JDBC 驱动中 CopyManager 对象的方法：

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、使用

3.1、直接使用

3.1.1、引入依赖

<dependency>

<groupId>com.mchange</groupId>

<artifactId>c3p0</artifactId>

<version>0.9.5.5</version>

</dependency>

3.1.2、使用例子

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);

}

//实际使用中一般是在应用启动时初始化数据源，应用从数据源中获取连接；并不会关闭数据源。

comboPooledDataSource.close();

}

private static void close(Connection connection) {

if (connection != null) {

try {

connection.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}

3.2、在 SpringBoot 中使用

3.1.1、引入依赖

<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、单数据源

application.yml 配置：

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

数据源配置类：

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();

}

}

使用：

@Autowired

private DataSource dataSource;

3.1.3、多数据源

application.yml 配置：

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

数据源配置类：

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();

}

}

使用：

@Autowired

@Qualifier("dataSource1")

private DataSource dataSource1;

@Autowired

@Qualifier("dataSource2")

private DataSource dataSource2;

来源：https://www.cnblogs.com/wuyongyin/p/15459526.html