使用c3p0创建连接池

使用C3P0创建连接池需要的jar包：

c3p0-0.9.2-pre1.jar
mchange-commons-0.2.jar
c3p0-oracle-thin-extras-0.9.2-pre1.jar //oracle 数据库还需要此jar包

使用此C3P0创建连接池有两种方式，一个是不基于配置文件的，直接在代码中写死。另外一中是基于配置文件的方法，配置文件，可以是xml文件(c3p0-config.xml)，properties文件(c3p0.properties)，这些文件必须在classloader resource 目录下。即SRC目录下，或者是指定系统属性(System properties)。示例如下：

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 JdbcC3p0Pools {
//方法一;通过代码中直接写死，不灵活
public static Connection getConnection() throws SQLException {
try {
ComboPooledDataSource ds = new ComboPooledDataSource();
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl("jdbc:mysql://localhost:3306/jdbc");
ds.setUser("root");
ds.setPassword("850828");
ds.setMaxPoolSize(40);
ds.setMinPoolSize(10);
ds.setInitialPoolSize(20);

Connection conn = ds.getConnection();
return conn;
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}

}
//使用xml配置文件，此xml
public static Connection getConnection2() throws SQLException{
ComboPooledDataSource ds = new ComboPooledDataSource("wl");
return ds.getConnection();
}
public static void release(Connection conn, Statement st, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
rs = null;
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
st = null;
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
conn = null;
}
}
}

XML配置很灵活，可以为多个应用进行数据配置。在获取连接时，可以指定特定的配置。同时支持一个默认配置，要是没找到指定配置，将使用默认配置。

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://localhost:3306/jdbc</property>
<property name="user">root</property>
<property name="password">root</property>

<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!--mysql使用此配置 -->
<named-config name="mysql">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/day16</property>
<property name="user">root</property>
<property name="password">root</property>

<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>

<!-- orcale 配置-->
<named-config name="oracle">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc</property>
<property name="user">root</property>
<property name="password">root</property>

<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
</c3p0-config>

//示例：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import cn.itcast.utils.JdbcC3p0Pools;

public class Demo5 {

private Connection conn = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
private String sql = null;


@Test
public void query(){
try{ 
// conn = JdbcC3p0Pools.getConnection();
conn = JdbcC3p0Pools.getConnection2();

sql = "select name, money from account";
ps = conn.prepareStatement(sql);

rs = ps.executeQuery();
while(rs.next()){
System.out.print("name:"+rs.getString("name"));
System.out.println("--money:"+rs.getFloat("money"));
}

}catch(Exception e){
e.printStackTrace();
}
finally{
JdbcC3p0Pools.release(conn, ps, rs);
}
}
}