日期:2014-05-20  浏览次数:20854 次

数据库连接池的问题
项目框架为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;
    }
    
}