JDBC之数据源C3P0的使用

一：要使用C3P0首先需要引入C3P0的JAR:c3p0-0.9.1.2.jar

二：在src下增加C3P0的配置文件c3p0-config.xml。

注意：c3p0的xml文件不能随便写，因为c3p0在项目启动的时候会自动到src下读取c3p0-config.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:///web03</property>
      <property name="user">root</property>
      <property name="password">root</property>
   </default-config>

   <named-config name="oracle">
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql:///web03</property>
      <property name="user">root</property>
      <property name="password">root</property>
      <property name="minPoolSize">5</property>
      <property name="initialPoolSize">5</property>
   </named-config>
</c3p0-config>

三：编写C3P0的工具类，提供获取数据源DataSource，获取Connection方法，关闭资源方法

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class C3P0Utils {

    private C3P0Utils(){}

    public static DataSource ds = new ComboPooledDataSource();

    public static Connection getConnection(){
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void closeResource(ResultSet rs, PreparedStatement ps, Connection conn){
        try {
            if(rs != null){
                rs.close();
                rs = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(ps != null){
                    ps.close();
                    ps = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                try {
                    if(conn != null){
                        conn.close();
                        conn = null;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void closeResource(PreparedStatement ps,Connection conn){
        try {
            if(ps != null){
                ps.close();
                ps = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(conn != null){
                    conn.close();
                    conn = null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

四：使用C3P0

    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = C3P0Utils.getConnection();
        ps = conn.prepareStatement("update users set name = ? where id = ?");
        ps.setString(1,"您好");
        ps.setString(2,"22222222222");
        if(ps.executeUpdate()>0){
            System.out.println("执行成功");
        }else{
            System.out.println("执行失败");
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        C3P0Utils.closeResource(ps,conn);
    }