spring org.hibernate.exception.GenericJDBCException：无法打开连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23485056/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
org.hibernate.exception.GenericJDBCException: Cannot open connection
提问by user3605072
I have been struggling with this problem for a while now. I have a web based application using Struts2, spring and Hibernate. I use Spring to wire struts action and business and dao layer together. I am using JMeter to load test the application. The application works well when I simulate 1 user repeatedly sending a get request, there are no issues. But when I add a couple of more users, after a while I get the following error:
我一直在努力解决这个问题。我有一个使用 Struts2、spring 和 Hibernate 的基于 Web 的应用程序。我使用 Spring 将 struts action 和 business 和 dao 层连接在一起。我正在使用 JMeter 对应用程序进行负载测试。当我模拟 1 个用户重复发送 get 请求时，该应用程序运行良好，没有问题。但是当我添加更多用户时，一段时间后我收到以下错误：
Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:160)
at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:81)
at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1473)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:510)
... 104 more
Caused by: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
The Connection descriptor used by the client was:
192.168.1.118:1521:BAADB
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:261)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:154)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:173)
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:164)
at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:149)
at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:119)
at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
... 109 more
So, I assumed its a connection leak, but shouldnt I get the same error when I simulate a single user continuously sending request (the only difference will be, I will get the error a little later). I enabled Hibernate stats to check for number of open session before I execute any dao request. All the session are closed after a request is processed. I am using OpenSessionInViewFilter, so there a session per request.
所以，我假设它是连接泄漏，但是当我模拟单个用户连续发送请求时，我不应该得到同样的错误（唯一的区别是，我稍后会得到错误）。在执行任何 dao 请求之前，我启用了 Hibernate stats 来检查打开会话的数量。处理完请求后，所有会话都将关闭。我正在使用 OpenSessionInViewFilter，因此每个请求都有一个会话。
Following are all the files web.xml:
以下是所有文件 web.xml：
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app
PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
"http://java.sun.com/j2ee/dtds/web-app_2_2.dtd">
<web-app>
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>
/WEB-INF/applicationContext-security.xml
/WEB-INF/spring-database.xml
/WEB-INF/application-action.xml
/WEB-INF/newbeans.xml
</param-value>
</context-param>
<filter> <!-- Get spring to keep the session open for the whole request, so Hibernates lazy loads work -->
<filter-name>openSessionInViewFilter</filter-name>
<filter-class>org.springframework.orm.hibernate3.support.OpenSessionInViewFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>openSessionInViewFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<filter>
<filter-name>springSecurityFilterChain</filter-name>
<filter-class>org.springframework.web.filter.DelegatingFilterProxy</filter-class>
</filter>
<filter-mapping>
<filter-name>springSecurityFilterChain</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!-- - Loads the root application context of this web app at startup. -
The application context is then available via - WebApplicationContextUtils.getWebApplicationContext(servletContext). -->
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<listener>
<listener-class>
net.sf.navigator.menu.MenuContextListener</listener-class>
</listener>
<context-param>
<param-name>log4jConfigLocation</param-name>
<param-value>/WEB-INF/classes/log4j.properties</param-value>
</context-param>
<listener>
<listener-class>org.springframework.web.util.Log4jConfigListener</listener-class>
</listener>
<filter>
<filter-name>struts2</filter-name>
<filter-class>org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>struts2</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>
Spring and Hibernate file
Spring 和 Hibernate 文件
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">
<tx:annotation-driven/>
<bean id="myDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@192.168.1.118:1521:BAADB" />
<property name="username" value="htwork" />
<property name="password" value="*****" />
</bean>
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
<property name="dataSource" ref="myDataSource" />
<property name="annotatedClasses">
<list>
<value>com.tda.ht.bean.SerialNumbers</value>
<value>com.tda.ht.bean.Bunos</value>
<value>com.tda.ht.bean.Tms</value>
<value>com.tda.ht.bean.Custodian</value>
<value>com.tda.ht.bean.CMISConfEntry</value>
<value>com.tda.ht.bean.HeloTrackCurrentBunoConf</value>
<value>com.tda.ht.bean.RepairBean</value>
<value>com.tda.ht.bean.TDIncorporation</value>
<value>com.tda.ht.bean.Miscellaneous</value>
<value>com.tda.ht.bean.Inspection</value>
<value>com.tda.ht.bean.HTComponentHistory</value>
<value>com.tda.ht.bean.Component</value>
<value>com.tda.baa.dao.TransferRequest</value>
<value>com.tda.baa.dao.UsersTable</value>
<value>com.tda.baa.dao.UserRoles</value>
<value>com.tda.ht.bean.EHRCurrentBunoConf</value>
<value>com.tda.ht.bean.SRCCurrentBunoConf</value>
<value>com.tda.ht.bean.ASRCurrentBunoConf</value>
<value>com.tda.ht.bean.ASRSubCurrentBunoConf</value>
<value>com.tda.ht.bean.ASRTree</value>
<value>com.tda.ht.bean.PMICTree</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.generate_statistics">true</prop>
<prop key="hibernate.c3p0.min_size">5</prop>
<prop key="hibernate.c3p0.max_size">20</prop>
<prop key="hibernate.c3p0.timeout">300</prop>
<prop key="hibernate.c3p0.max_statements">50</prop>
<prop key="hibernate.c3p0.idle_test_period">3000</prop>
</props>
</property>
</bean>
<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory"/>
</bean>
<bean id="componentDAO" class="com.tda.ht.dao.component.ComponentDAOImpl">
<property name="sessionFactory" ref="sessionFactory"></property>
</bean>
<bean id="componentBusiness" class="com.tda.ht.business.component.ComponentBusinessImpl">
<property name="iCompDAO" ref="componentDAO"></property>
</bean>
</beans>
Action method call for the request componentBusiness - this is managed by spring
请求组件业务的Action方法调用——这个由spring管理
public String displayHeader() {
Component c = componentBusiness.getComponentByPartNumber(partNumber);
return SUCCESS;
}
Business Layer
业务层
package com.tda.ht.business.component;
import java.util.List;
import org.springframework.transaction.annotation.Transactional;
import com.tda.ht.bean.Component;
import com.tda.ht.dao.component.IComponentDAO;
public class ComponentBusinessImpl implements IComponentBusiness {
private IComponentDAO iCompDAO;
//service layer method, transactions handled by spring
@Transactional(readOnly = true)
public Component getComponentByPartNumber(String partNumber) {
// TODO Auto-generated method stub
return iCompDAO.getComponentByPartNumber(partNumber);
}
}
DAO layer
DAO层
public class ComponentDAOImpl implements IComponentDAO {
SessionFactory sessionFactory;
protected static org.apache.log4j.Logger log = Logger
.getLogger(ComponentDAOImpl.class);
//dao method called from service layer
@Override
public Component getComponentByPartNumber(String partNumber) {
String[] arguments = { partNumber };
long open = sessionFactory.getStatistics().getSessionOpenCount();
long close = sessionFactory.getStatistics().getSessionCloseCount();
log.error("Open " + open + " Close " + close);
Session s = sessionFactory.getCurrentSession();
log.error(" SESSION IS " + s.hashCode());
try {
Query q = s.createQuery("Select entry from Component as entry where entry.partNumber = ?");
q.setParameter(0, partNumber);
List<Component> list = q.list();
if (list.size() > 0)
return list.get(0);
return null;
}catch (Exception e) {
e.printStackTrace();
}finally {
//s.close();
}
return null;
}
}
In the dao layer, I gather stats about open and close sessions, the difference is never more than 4 since I have simulated 4 user. Looks like all the sessions are being closed. When the request is processed. If I don't use spring and manage Hibernate on my own with my own SessionFactory, opening and closing sessions, everything works and there are no errors. I am confused since I dont see any Sessions being leaked, any pointers will be greatly appreciated.
在 dao 层，我收集有关打开和关闭会话的统计信息，由于我模拟了 4 个用户，因此差异永远不会超过 4。看起来所有会话都将关闭。当请求被处理时。如果我不使用 spring 并使用我自己的 SessionFactory 自行管理 Hibernate，打开和关闭会话，一切正常并且没有错误。我很困惑，因为我没有看到任何会话被泄露，任何指针将不胜感激。
回答by M. Deinum
The DriverManagerDataSourceisn't meant to be used in production, in production you want to use a connection pool, the DriverManagerDataSourceisn't a connection pool, I suggest using something like Tomcat JDBC or Commons DBCP as the connection pool.
该DriverManagerDataSource并不意味着在生产中使用，在生产中要使用连接池，DriverManagerDataSource是不是一个连接池，我建议使用类似Tomcat的JDBC或下议院DBCP作为连接池。
Your hibernate.c3p0settings are useless, as you are configuring and injecting the datasource with Spring, so those properties are ignored.
您的hibernate.c3p0设置没有用，因为您正在使用 Spring 配置和注入数据源，因此这些属性将被忽略。
Finally you DAO code (might be) flawed, you shouldn't catch and swallow the Exceptionas this might break proper tx-management.
最后，你的 DAO 代码（可能）有缺陷，你不应该抓住并吞下它，Exception因为这可能会破坏适当的 tx 管理。
A final, unrelated, tip your Log4jConfigListenershould be configured before the ContextLoaderListenerif you want Log4j to properly initialize. Although I doubt you need it as log4j.xmlis on yuor classpath so probably Log4J will be already initiliazed.
最后一个不相关的提示Log4jConfigListener，ContextLoaderListener如果您希望 Log4j 正确初始化，您应该在之前进行配置。尽管我怀疑您log4j.xml在类路径上是否需要它，因此可能 Log4J 已经初始化。
