Java 資料庫連接池介紹(2)--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 來訪問原始連接中的非標準介面：
1、把連接轉成 C3P0ProxyConnection
2、然后呼叫 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/egmp?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;

標籤：其他

上一篇：與 Python 之父聊天：更快的 Python！

下一篇：雷軍做程式員時寫的博客，太牛了。。