使用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); } } }