1.什么是C3P0
C3P0是开源的JDBC连接池,它实现了数据源与JNDI的绑定,支持JDBC3规范和JDBC2的标准扩展,目前使用它的开源项目有spring和hibernate等等。
2.准备工作
需要导入jar包:
核心的类为:
com.mchange.v2.c3p0.ComboPooledDataSource
至于操作数据库与jdbc大致相同
3.硬编码方式实现C3P0连接池
public class App_C3P0 {
//硬编码方式
@Test
public void testUsingHardCoded() throws Exception{
ComboPooledDataSource dataSource=new ComboPooledDataSource();
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/basicjdbc?characterEncoding=utf-8");
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setUser("root");
dataSource.setPassword(null);
dataSource.setInitialPoolSize(3);
dataSource.setMaxPoolSize(6);
dataSource.setMaxIdleTime(1000);
Connection conn=dataSource.getConnection();
PreparedStatement pstmt=conn.prepareStatement("select * from myuser");
ResultSet rs=pstmt.executeQuery();
while(rs.next()){
Integer id=rs.getInt("id");
String username=rs.getString("username");
String gender=rs.getString("gender");
System.out.println(id+" ,"+username+" ,"+gender);
}
rs.close();
pstmt.close();
conn.close();
}
}
结果:
3 ,Bryant3 ,男
4 ,Bryant4 ,男
5 ,Bryant5 ,男
6 ,Bryant6 ,男
7 ,Bryant7 ,男
8 ,Bryant8 ,男
9 ,Bryant9 ,男
10 ,张三 ,男
11 ,李四 ,女
12 ,王五 ,男
4.XML配置方式实现C3P0连接池
C3P0的配置文件:
<c3p0-config>
<!-- 默认配置 -->
<default-config>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/basicjdbc?characterEncoding=utf-8</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password"></property>
<property name="initialPoolSize">3</property>
<property name="maxPoolSize">6</property>
<property name="maxIdleTime">1000</property>
</default-config>
<!-- 以下的配置用于个人需要再配置,在<span style="font-size:14px;">ComboPooledDataSource</span>构造器内传入连接配置的名称,此处是my_config -->
<name-config name="my_config">
<property name="jdbcUrl">jdbc:mysql://localhost:3306/basicjdbc?characterEncoding=utf-8</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password"></property>
<property name="initialPoolSize">3</property>
<property name="maxPoolSize">6</property>
<property name="maxIdleTime">1000</property>
</name-config>
</c3p0-config>
加载顺序:
C3P0自动加载src下的C3P0的配置文件 "c3p0-config.xml"。
实现:
public class App_C3P0 {
//xml配置方式
@Test
public void testUsingXML() throws Exception{
ComboPooledDataSource dataSource=new ComboPooledDataSource();
Connection conn=dataSource.getConnection();
PreparedStatement pstmt=conn.prepareStatement("select * from myuser");
ResultSet rs=pstmt.executeQuery();
while(rs.next()){
Integer id=rs.getInt("id");
String username=rs.getString("username");
String gender=rs.getString("gender");
System.out.println(id+" ,"+username+" ,"+gender);
}
rs.close();
pstmt.close();
conn.close();
}
}
结果:
3 ,Bryant3 ,男
4 ,Bryant4 ,男
5 ,Bryant5 ,男
6 ,Bryant6 ,男
7 ,Bryant7 ,男
8 ,Bryant8 ,男
9 ,Bryant9 ,男
10 ,张三 ,男
11 ,李四 ,女
12 ,王五 ,男
5.实现C3P0工具类
public class ConnectionPoolUtils {
private static DataSource dataSource=null;
static{
dataSource=new ComboPooledDataSource();
}
public static Connection getConnection(){
try{
return dataSource.getConnection();
}catch(SQLException e){
throw new RuntimeException(e);
}
}
//释放对象的连接
public static void close(Connection conn,Statement stmt,ResultSet rs){
if(rs !=null){
try{
rs.close();
}catch(SQLException e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(stmt !=null){
try{
stmt.close();
}catch(SQLException e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn !=null){
try{
conn.close();
}catch(SQLException e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}