org.hibernate.exception.GenericJDBCException: Cannot open connection

spring hibernate struts2 struts

34,685

The DriverManagerDataSource isn't meant to be used in production, in production you want to use a connection pool, the DriverManagerDataSource isn't a connection pool, I suggest using something like Tomcat JDBC or Commons DBCP as the connection pool.

Your hibernate.c3p0 settings are useless, as you are configuring and injecting the datasource with Spring, so those properties are ignored.

Finally you DAO code (might be) flawed, you shouldn't catch and swallow the Exception as this might break proper tx-management.

A final, unrelated, tip your Log4jConfigListener should be configured before the ContextLoaderListener if you want Log4j to properly initialize. Although I doubt you need it as log4j.xml is on yuor classpath so probably Log4J will be already initiliazed.

34,685

Author by

user3605072

Updated on March 01, 2020

Comments

user3605072 about 4 years

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:

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.

Following are all the files 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

<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

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

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.

user3605072 almost 10 years

Thank you. Replacing DriverManagerDataSource with a connection pool seems to have fixed the problem. Exception block in DAO layer was just for debugging purposes while I was struggling with the errors. I dont plan to have that once I am done. What threw me off was I could simulate one or two users with continuous request without any problems. Thanks again.