使用c3p0创建连接池

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

c3p0-0.9.2-pre1.jarmchange-commons-0.2.jarc3p0-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配置文件，此xmlpublic 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 blocke.printStackTrace();}rs = null;}if(st!=null){try {st.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}st = null;}if(conn!=null){try {conn.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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;@Testpublic 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);}}}