C3P0 连接池 —— 基础使用 与 SpringMVC 中使用

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_19865749/article/details/75376657

使用的连接池类: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&amp;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&amp;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










展开阅读全文

Hibernate3中使用连接池c3p0遇到的问题

02-25

在实际的工作中使用Hibernate3,连接池是一个很重要的环节。在Hibernate3中可以使用第三方c3p0和Proxool。rn  我在不使c3p0时,对数据的增删改操作都测试通过。在此基础上,按文档的所述加上c3p0的配置后,可以查询,也可以添加记录,但是抛出了异常。rnrnProblem with checked-in Statement, discarding.rnjava.lang.NullPointerExceptionrn at oracle.jdbc.dbaccess.DBData.clearItem(DBData.java:431)rn at oracle.jdbc.dbaccess.DBDataSetImpl.clearItem(DBDataSetImpl.java:3528)rn at oracle.jdbc.driver.OraclePreparedStatement.clearParameters(OraclePreparedStatement.java:3401)rn at com.mchange.v2.c3p0.stmt.GooGooStatementCache.refreshStatement(GooGooStatementCache.java:460)rn at com.mchange.v2.c3p0.stmt.GooGooStatementCache.checkinStatement(GooGooStatementCache.java:139)rn at com.mchange.v2.c3p0.impl.C3P0PooledConnection$1WrapperStatementHelper.doClose(C3P0PooledConnection.java:511)rn at com.mchange.v2.c3p0.impl.C3P0PooledConnection$2.close(C3P0PooledConnection.java:570)rn at org.hibernate.jdbc.AbstractBatcher.closePreparedStatement(AbstractBatcher.java:369)rn at org.hibernate.jdbc.AbstractBatcher.closeStatement(AbstractBatcher.java:178)rn at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:158)rn at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:226)rn at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:136)rn at org.hibernate.event.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:271)rn at org.hibernate.event.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:24)rn at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:719)rn at sample.Hello.saveOrUpdate(Hello.java:43)rn at sample.Hello.main(Hello.java:28)rnrnrn程序代码为:rnrn  Hello_Bean bean = new Hello_Bean(233, "name", "address");rn  try rn Session session = HibernateUtil.CurrentSession();//功能为取得一个Sessionrn Transaction tx = session.beginTransaction();rn session.save(bean);rn session.flush();rn //session.saveOrUpdate(bean);rn log.info("Insert id = " + bean.getId());rn tx.commit();rn HibernateUtil.closeSession();rn rn catch (Exception e) rn e.printStackTrace();rn  rnrn配置为:rnrnrnrnrnrnrnrnrn oracle.jdbc.driver.OracleDriverrn jdbc:oracle:thin:@172.19.100.29:1521:xxpttestrn pzfrn pzf rn org.hibernate.dialect.OracleDialectrn rnrnrn 2 rn 5 rn 1800 rn 50rn rn rnrnrn rnrn rnrnrnrnrnrnrn敬请各位指点。 论坛

在Eclipse3中使用Hibernate3中使用连接池c3p0遇到的问题

09-01

我配置c3p0 pool时,运行以后出现如下提示,想问一下是我的培植出了问题还是怎么回事?哦,我把log4设置的Warn警告级别的信息才显示的,但是这个c3p0的信息是红色的(Eclipse3.1版本下运行的效果)是我配置错误?还是怎么回事啊?rnInitializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@edc3a2 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@1b1aa65 [ acquireIncrement -> 2, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, idleConnectionTestPeriod -> 3000, initialPoolSize -> 1, maxIdleTime -> 100, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 1, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@d6c16c [ description -> null, driverClass -> null, factoryClassLocation -> null, jdbcUrl -> jdbc:mysql://localhost:3306/xzk?useUnicode=true&characterEncoding=gb2312, properties -> user=******, password=****** ] , preferredTestQuery -> null, propertyCycle -> 300, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, usesTraditionalReflectiveProxies -> false ] , factoryClassLocation -> null, numHelperThreads -> 3, poolOwnerIdentityToken -> edc3a2 ] rnSave successful. 论坛

没有更多推荐了,返回首页