C3P0连接池

步骤：

1、引入jar包

https://sourceforge.net/projects/c3p0/postdownload

2、配置xml文件

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

    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://192.168.0.207:3306/mydb</property>
        <property name="user">root</property>
        <property name="password">Console.Write21</property>
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <named-config name="mydb">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://192.168.0.207:3306/mydb</property>
        <property name="user">root</property>
        <property name="password">Console.Write21</property>
    </named-config>


</c3p0-config>

 

3、（可选）配置工具类

package cn.sasa.utils;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {
    private static DataSource dataSource = new ComboPooledDataSource();
    
    public static DataSource getDataSource() {
        return dataSource;
    }
    
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

 

4、测试

package cn.sasa.c3p0;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.sql.DataSource;

import org.junit.jupiter.api.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import cn.sasa.utils.C3P0Utils;
import cn.sasa.utils.DBUtils;

public class C3P0Test {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstate = null;
        ResultSet rs = null;
        try {
            conn = C3P0Utils.getConnection();
            String sql = "select * from user where name=?";
            pstate = conn.prepareStatement(sql);
            pstate.setString(1, "test");
            rs = pstate.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.release(rs, pstate, conn);
        }
    }

    @Test
    public void testC3P0() {

        Connection conn = null;
        PreparedStatement pstate = null;
        ResultSet rs = null;
        // 连接池
        DataSource dataSource = new ComboPooledDataSource();
        try {
            conn = dataSource.getConnection();
            String sql = "select * from user where name=?";
            pstate = conn.prepareStatement(sql);
            pstate.setString(1, "test");
            rs = pstate.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("name"));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.release(rs, pstate, conn);
        }
    }

}

 

工具类：

package cn.sasa.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;

public class DBUtils {
    private static Connection conn = null;
    private static String driver = null;
    private static String url = null;
    private static String user = null;
    private static String pwd = null;
    //注册驱动
    static {
        try {
            ResourceBundle bundle = ResourceBundle.getBundle("database");
            driver = bundle.getString("driver");
            url = bundle.getString("url");
            user = bundle.getString("user");
            pwd = bundle.getString("pwd");
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, pwd);
        }catch(Exception e) {
            throw new RuntimeException(e);
        }
    }
    
    //获取连接对象
    public static Connection getConnection() {
        return conn;
    }
    
    //销毁资源 查询
    public static void release(ResultSet rs, PreparedStatement state, Connection conn ) {
        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    
    //销毁资源 增删改
    public static void release(PreparedStatement state, Connection conn) {
        if(state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}