使用的连接池类:com.mchange.v2.c3p0.ComboPooledDataSource
一、编程式使用
示例:
package com.test;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class TestC3P0 {
private static ComboPooledDataSource ds;
public static void main(String[] args){
ds =new ComboPooledDataSource();
try {
ds.setDriverClass("com.mysql.jdbc.Driver");
} catch (PropertyVetoException e1) {
System.out.println("驱动加载失败");
e1.printStackTrace();
}
ds.setJdbcUrl("jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8");
ds.setUser("root");
ds.setPassword("123456");
ds.setMinPoolSize(5);
ds.setMaxPoolSize(20);
ds.setInitialPoolSize(12);
ds.setCheckoutTimeout(300);
ds.setMaxIdleTime(60);
ds.setAcquireIncrement(5);
ds.setIdleConnectionTestPeriod(10);
ds.setTestConnectionOnCheckin(true);
Connection conn;
try {
System.out.println("开始连接");
conn= (Connection) ds.getConnection();
} catch (SQLException e) {
System.out.println("连接失败");
throw new RuntimeException(e);
}
PreparedStatement stat;
try {
stat=(PreparedStatement) conn.prepareStatement("UPDATE UI_FIELD SET NAME=? WHERE UI_FIELD_ID =?");
stat.setString(1, "newName");
stat.setString(2, "01001");
stat.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
注:Connection 使用java.sql 包中的,否则可能出错。
遇到错误:
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
解决方法:
改变下 ds.setAcquireIncrement(5) 的参数值(默认为3)。各参数含义参考:http://blog.csdn.net/xb12369/article/details/41517409
二、SpringMVC 中使用
在Spring 中,事务的实体管理工厂或事务管理器中需要设置dataSource 属性,此属性可以是连接池对象,或是JNDI 查找获得的数据源。
1、spring-context.xml 中配置事务管理器与数据源:
<!-- 事务管理器 -->
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="entityManagerFactory" ref="emf" />
</bean>
<!-- 启用@Trasactinal 注解 -->
<tx:annotation-driven transaction-manager="transactionManager"/>
<!-- 此处使用了Hibernate 的JPA,一般都会启用@PersistenceContext 注解。负责实例化此注解作用的EntityManager -->
<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor"/>
<!-- 实体管理工厂。此bean会扫描类路径下的persistence.xml,完成对象与数据库表的字段映射 -->
<bean id="emf" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 数据源,此处采用c3p0连接池。至此数据源已经指定 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver" />
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8" />
<property name="user" value="root" />
<property name="password" value="123456" />
<property name="minPoolSize" value="5" />
<property name="maxPoolSize" value="20" />
<property name="initialPoolSize" value="12" />
<property name="checkoutTimeout" value="300"/>
<property name="maxIdleTime" value="60" />
<property name="acquireIncrement" value="5" />
<property name="idleConnectionTestPeriod" value="10" />
<property name="testConnectionOnCheckin" value="true" />
</bean>
persistence.xml 配置:
参考:日志
2、可将配置信息移出至.properties 文件:
上面的c3p0配置信息直接配置在bean 定义中,不便于修改,可将连接与配置信息写在properties 文件中,然后通过下面类读取该属性文件:org.springframework.beans.factory.config.PropertyPlaceholderConfigurer编写 myDataSource.properties:
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=UTF-8
username=root
password=123456
c3p0.minPoolSize = 5
c3p0.maxPoolSize = 20
c3p0.initialPoolSize = 12
c3p0.checkoutTimeout = 3000
c3p0.maxIdleTime = 60
c3p0.acquireIncrement = 6
c3p0.idleConnectionTestPeriod = 10
c3p0.testConnectionOnCheckin = true
配置c3p0与 PropertyPlaceholderConfigurer 的bean:<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:META-INF/myDataSource.properties</value>
</list>
</property>
</bean>
<!-- c3p0 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<property name="driverClass" value="${driverClassName}" />
<property name="jdbcUrl" value="${url}" />
<property name="user" value="${username}" />
<property name="password" value="${password}"/>
<!-- C3P0 Connection Pool -->
<property name="minPoolSize" value="${c3p0.minPoolSize}" />
<property name="maxPoolSize" value="${c3p0.maxPoolSize}" />
<property name="initialPoolSize" value="${c3p0.initialPoolSize}" />
<property name="checkoutTimeout" value="${c3p0.checkoutTimeout}"/>
<property name="maxIdleTime" value="${c3p0.maxIdleTime}" />
<property name="acquireIncrement" value="${c3p0.acquireIncrement}" />
<property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}" />
<property name="testConnectionOnCheckin" value="${c3p0.testConnectionOnCheckin}" />
</bean>
3、org.springframework.beans.factory.config.PropertyPlaceholderConfigurer 加载properties 文件
查看另一篇日志:http://blog.csdn.net/qq_19865749/article/details/75411096