Java數據庫連接池c3p0介紹

第Java數據庫連接池c3p0介紹詳細說明可參考官網文檔：/projects/c3p0/#configuration

2、原始連接操作

c3p0提供api來訪問原始連接中的非標準接口：

把連接轉成C3P0ProxyConnection

然后調用rawConnectionOperation方法

下面是獲取PostgreSQL

JDBC驅動中CopyManager對象的方法：

Connectionconnection=(C3P0ProxyConnection)c3p0DataSource.getConnection();

C3P0ProxyConnectioncastConnection=(C3P0ProxyConnection)connection;

Methodmethod=BaseConnection.class.getMethod("getCopyAPI",newClass[]{});

CopyManagercopyManager=(CopyManager)castConnection.rawConnectionOperation(method,C3P0ProxyConnection.RAW_CONNECTION,newObject[]{});

3、使用

3.1、直接使用

3.1.1、引入依賴

dependency

groupIdcom.mchange/groupId

artifactIdc3p0/artifactId

version/version

/dependency

3.1.2、使用例子

packagecom.abc.demo.general.dbpool;

importcom.mchange.v2.c3p0.ComboPooledDataSource;

importjava.beans.PropertyVetoException;

importjava.sql.Connection;

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.sql.Statement;

publicclassC3p0Case{

publicstaticvoidmain(String[]args)throwsPropertyVetoException{

ComboPooledDataSourcecomboPooledDataSource=newComboPooledDataSource();

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

comboPooledDataSource.setJdbcUrl("jdbc:mysql://1:3306/mydbuseUnicode=truecharacterEncoding=UTF-8");

comboPooledDataSource.setUser("root");

comboPooledDataSource.setPassword("123456");

comboPooledDataSource.setInitialPoolSize(2);

comboPooledDataSource.setMinPoolSize(2);

comboPooledDataSource.setMaxPoolSize(10);

comboPooledDataSource.setPreferredTestQuery("select1");

comboPooledDataSource.setIdleConnectionTestPeriod(60);

comboPooledDataSource.setTestConnectionOnCheckout(true);

comboPooledDataSource.setCheckoutTimeout(1000*30);

Connectionconnection=null;

Statementst=null;

ResultSetrs=null;

try{

connection=comboPooledDataSource.getConnection();

st=connection.createStatement();

rs=st.executeQuery("selectversion()");

if(rs.next()){

System.out.println(rs.getString(1));

}catch(SQLExceptione){

e.printStackTrace();

}finally{

close(connection);

//實際使用中一般是在應用啟動時初始化數據源，應用從數據源中獲取連接；并不會關閉數據源。

comboPooledDataSource.close();

privatestaticvoidclose(Connectionconnection){

if(connection!=null){

try{

connection.close();

}catch(SQLExceptione){

e.printStackTrace();

}

3.2、在SpringBoot中使用

3.1.1、引入依賴

parent

groupIdorg.springframework.boot/groupId

artifactIdspring-boot-starter-parent/artifactId

version2.3.12.RELEASE/version

relativePath/

/parent

dependencies

dependency

groupIdorg.springframework.boot/groupId

artifactIdspring-boot-starter-web/artifactId

/dependency

dependency

groupIdorg.springframework/groupId

artifactIdspring-jdbc/artifactId

/dependency

dependency

groupIdcom.mchange/groupId

artifactIdc3p0/artifactId

version/version

/dependency

dependency

groupIdmysql/groupId

artifactIdmysql-connector-java/artifactId

/dependency

/dependencies

3.1.2、單數據源

application.yml配置：

spring:

datasource:

c3p0:

driver-class:com.mysql.cj.jdbc.Driver

jdbc-url:jdbc:mysql://1:3306/myDbuseUnicode=truecharacterEncoding=UTF-8

user:root

password:123456

initial-pool-size:2

min-pool-size:2

max-pool-size:10

preferred-test-query:select1

idle-connection-test-period:60

test-connection-on-checkout:true

checkout-timeout:30000

數據源配置類：

packagecom.abc.demo.config;

importcom.mchange.v2.c3p0.ComboPooledDataSource;

importperties.ConfigurationProperties;

importorg.springframework.boot.jdbc.DataSourceBuilder;

importorg.springframework.context.annotation.Bean;

importorg.springframework.context.annotation.Configuration;

importjavax.sql.DataSource;

@Configuration

publicclassDataSourceConfig{

@Bean("dataSource")

@ConfigurationProperties(prefix="spring.datasource.c3p0")

publicDataSourcedataSource1(){

returnDataSourceBuilder.create().type(ComboPooledDataSource.class).build();

}

使用：

@Autowired

privateDataSourcedataSource;

3.1.3、多數據源

application.yml配置：

spring:

datasource:

c3p0:

db1:

driver-class:com.mysql.cj.jdbc.Driver

jdbc-url:jdbc:mysql://1:3306/myDbuseUnicode=truecharacterEncoding=UTF-8

user:root

password:InsYR0ot187!

initial-pool-size:2

min-pool-size:2

max-pool-size:10

preferred-test-query:select1

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://2:3306/myDbuseUnicode=truecharacterEncoding=UTF-8

user:root

password:InsYR0ot187!

initial-pool-size:2

min-pool-size:2

max-pool-size:10

preferred-test-query:select1

idle-connection-test-period:60

test-connection-on-checkout:true

checkout-timeout:30000

數據源配置類：

packagecom.abc.demo.config;

importcom.mchange.v2.c3p0.ComboPooledDataSource;

importperties.ConfigurationProperties;

importorg.springframework.boot.jdbc.DataSourceBuilder;

importorg.springframework.context.annotation.Bean;

importorg.springframework.context.annotation.Configuration;

importjavax.sql.DataSource;

@Configuration

publicclassDataSourceConfig{

@Bean("dataSource1")

@ConfigurationProperties(prefix="spring.datasource.c3p0.db1")

publicDataSourcedataSource1(){

returnDataSourceBuilder.create().type(ComboPooledDataSource.class).bui