Java数据库连接池c3p0介绍

<dependency>

    <groupId>com.mchange</groupId>

    <artifactId>c3p0</artifactId>

    <version>0.9.5.5</version>

</dependency>

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

            }

        }

    }

}

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

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;

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;