c3p0的简单使用

源代码：

package com.zhangxin9727.C3P0Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.junit.Test;

import java.sql.*;

//连接池测试类
public class Demo {
    @Test
    //手动设置连接池
    public void test1() {
        //创建连接池
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            //设置连接池参数
            dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
            dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/jdbctest?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT%2B8");
            dataSource.setUser("root");
            dataSource.setPassword("mypassword");
            dataSource.setMaxPoolSize(10);
            dataSource.setInitialPoolSize(1);
            //获得连接
            conn = dataSource.getConnection();
            //编写sql
            String sql = "SELECT * FROM user";
            //预编译sql
            pstmt = conn.prepareStatement(sql);
            //执行sql
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getInt("uid") + "\t" + rs.getString("username") + "\t" + rs.getString("password") + "\t" + rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                rs = null;
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                pstmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }

    @Test
    //使用配置文件设置连接池
    public void test2(){
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try{
            ComboPooledDataSource dataSource=new ComboPooledDataSource();
            conn = dataSource.getConnection();
            String sql = "SELECT * FROM user";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getInt("uid") + "\t" + rs.getString("username") + "\t" + rs.getString("password") + "\t" + rs.getString("name"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                rs = null;
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                pstmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }
}

c3p0配置文件：

<?xml version="1.0" encoding="UTF-8" ?>

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbctest?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF8&amp;serverTimezone=GMT%2B8</property>
        <property name="user">root</property>
        <property name="password">mypassword</property>
        <property name="initialPoolSize">1</property>
        <property name="maxPoolSize">10</property>
    </default-config>
</c3p0-config>

数据库：

CREATE DATABASE jdbctest;
USE jdbctest;
CREATE TABLE user(
    uid INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20),
    password VARCHAR(20),
    name VARCHAR(20)
);
INSERT user VALUES (NULL, 'aaa', '111', 'Alice'), (NULL, 'bbb', '222', 'Bob'), (NULL,'ccc', '333', 'Cidy');

依赖包：

<dependencies>
  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
  </dependency>
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.14</version>
  </dependency>
  <dependency>
    <groupId>c3p0</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.1.2</version>
  </dependency>
</dependencies>