项目框架为Struts2+hibernate+spring
数据库连接池用的是c3p0 配置如下
- XML code
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="user" value="${user}" /> <property name="password" value="${password}" /> <property name="jdbcUrl" value="${jdbcUrl}" /> <property name="driverClass" value="${driverClass}" /> <property name="maxPoolSize" value="${c3p0.maxPoolSize}"/>//100 <property name="minPoolSize" value="${c3p0.minPoolSize}"/>//10 <property name="initialPoolSize" value="${c3p0.initialPoolSize}"/>//10 <property name="maxIdleTime" value="${c3p0.maxIdleTime}"/> //100 <property name="acquireIncrement" value="${c3p0.acquireIncrement}"/>//5 <!-- <property name="acquireRetryAttempts" value="${c3p0.acquireRetryAttempts}"/> //5 --> <property name="acquireRetryDelay" value="${c3p0.acquireRetryDelay}"/>//10 <property name="autoCommitOnClose" value="${c3p0.autoCommitOnClose}"/>//true <property name="checkoutTimeout" value="${c3p0.checkoutTimeout}"/>//100 <property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}"/>//60 <!-- <property name="numHelperThreads" value="${c3p0.numHelperThreads}"/>//5 --> </bean>
数据库使用的是oracle10g (数据库设置时200个连接)
bug现象:
有时能正常获取数据库的连接,有时不能获取数据库的连接。不能获取数据库的连接时,我查看了数据库中的空闲的连接有很多。并且数据库中的连接的状态只有一个是active。连接发生异常信息为
- Java code
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Cannot open connection; uncategorized SQLException for SQL [???]; SQL state [null]; error code [0]; An attempt by a client to checkout a Connection has timed out.; nested exception is java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
------解决方案--------------------
楼主为何不在tomcat中的context.xml中设置连接池
<Resource
name="jdbc/bbs" //连接池名字 后面需要用到
auth="Container"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="30"
maxWait="10000"
driverClassName="com.mysql.jdbc.Driver"//按自己需要改
url="jdbc:mysql://127.0.0.1:3306/t_bbs"//t_bbs是数据库名
username="root"
password="root"
/>
然后
- Java code
import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;import javax.naming.Context;import javax.naming.InitialContext;import javax.naming.NamingException;import javax.sql.DataSource;public class ConnDB{ private static DataSource ds = null; static { Context context; try { context = new InitialContext(); ds = (DataSource) context.lookup("java:comp/env/jdbc/bbs");//连接池名字 } catch (NamingException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static Connection getConnection2() { if (ds != null) { try { return ds.getConnection(); } catch (SQLException e) { e.printStackTrace(); } } return null; } }