Could not open Hibernate Session for transaction / Cannot open connection

hibernate spring-mvc spring-security sessionfactory
AndyAdamantium picture AndyAdamantium · Aug 7, 2013 · Viewed 79.3k times · Source

In my application, I have a module that is designed to search a database for users and display their information in a table inside a jsp. I just setup Spring Security inside my application. I was able to make a connection to the database from the login page, though for some reason none of my DAO's CRUD operations (in this case, the search) are working.

Thanks, and please let me know if I can provide any more information.

Here is most of my stack trace.

    org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
        org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:656)
        org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

...

org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
    org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:596)
    org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
    org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)
    org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    $Proxy8.searchEntity(Unknown Source)
    arlua.controller.SearchUserController.mySearchMethod(SearchUserController.java:35)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source

...

    org.hibernate.exception.GenericJDBCException: Cannot open connection
        org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
        org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
        org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
        org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
        org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
        org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:74)
        $Proxy14.getTransactionIsolation(Unknown Source)

...

    java.sql.SQLException: invalid arguments in call
        oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
        oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
        oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:236)
        oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
        oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
        oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
        oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
        java.sql.DriverManager.getConnection(Unknown Source)
        java.sql.DriverManager.getConnection(Unknown Source)
        org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:173)
        org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:164)
        org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:149)
        org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:119)
        org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)
        org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
        org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
        org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:74)
        $Proxy14.getTransactionIsolation(Unknown Source)
        org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:190)
        org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:508)
        org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
        org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)
        org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
        org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
        org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
        $Proxy8.searchEntity(Unknown Source)
        arlua.controller.SearchUserController.mySearchMethod(SearchUserController.java:35)
        sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        java.lang.reflect.Method.invoke(Unknown Source)
        org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
        org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426)
        org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414)
        org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
        org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
        org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
        org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:368)
        org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
        org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
        org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
        org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
        org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
        org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
        org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
        org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:177)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
        org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
        org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:187)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
        org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
        org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
        org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:380)
        org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:169)
        org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
        org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)

SearchUserController class

package arlua.controller;

import java.util.List;

import arlua.dao.TableEntityFetchDao;
import arlua.exception.ImproperUserSearchException;
import arlua.exception.SearchStringTooShortException;
import arlua.helper.BuildUserTables;
import arlua.service.SearchCriteria;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.SessionAttributes;
import org.springframework.web.servlet.ModelAndView;

@Controller
@SessionAttributes
public class SearchUserController {

    private @Autowired @Qualifier("userInfoDaoImpl") TableEntityFetchDao userInfoDao;

    List<?> searchList;
    String error;

    @RequestMapping(value = "/search_user", method = RequestMethod.POST)
    public String mySearchMethod(@ModelAttribute("search_criteria") SearchCriteria search){

        error = "";
        try{
            if(search.getInput() != null)
                searchList = userInfoDao.searchEntity(search.getInput());
        }
        catch(SearchStringTooShortException e1){
            error = "Search criteria is too short. Be more specific.";
        }
        catch(ImproperUserSearchException e2){
            error = "Specify either an Id or first and last name.";
        }

        return "redirect:search_user";
    }

    @RequestMapping("/search_user")
    public ModelAndView mySuccessMethod(){
        ModelAndView model = new ModelAndView("search_user");

        if(searchList != null){
            if(searchList.size() == 0)
                error = "Search returned no results.";
            else
                model.addObject("searchTable", BuildUserTables.buildSearch(searchList));
        }

        return model;
    }

}

UserInfoDaoImpl class

package arlua.dao.impl;

import arlua.dao.TableEntityFetchDao;
import arlua.exception.ImproperUserSearchException;
import arlua.exception.SearchStringTooShortException;
import arlua.tables.UserInfoTable;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;

public class UserInfoDaoImpl implements TableEntityFetchDao{

    @Autowired
    private SessionFactory sessionFactory;

    public void setSessionFactory(SessionFactory sessionFactory){
        this.sessionFactory = sessionFactory;
    }

    public void saveEntity(Object userInfo) {
        this.sessionFactory.getCurrentSession().save((UserInfoTable)userInfo);
    }

    public UserInfoTable getEntity(Object user_id) {
        return (UserInfoTable)this.sessionFactory.getCurrentSession().
            get(UserInfoTable.class, (String)user_id);
    }

    public void updateEntity(Object userInfo) {
        this.sessionFactory.getCurrentSession().update((UserInfoTable)userInfo);
    }

    public void deleteEntity(Object userInfo) {
        this.sessionFactory.getCurrentSession().delete((UserInfoTable)userInfo);
    }

    public List<?> getAllEntities() {
        return this.sessionFactory.getCurrentSession().createQuery
            ("FROM UserInfoTable").list();
    }

    public List<?> searchEntity(String search) throws SearchStringTooShortException, ImproperUserSearchException{
        //This search supports input of a user's seid OR a first and last name.
        if(search.length() < 3){
            throw new SearchStringTooShortException();
        }

        Session s = this.sessionFactory.getCurrentSession();
        List<?> searchList = new ArrayList();

        //Searches for similar users with matching SEIDs.
        if(search.matches("^[a-zA-Z0-9]*[0-9]+[a-zA-Z0-9]*$")){
            searchList = s.createQuery
                //("FROM UserInfoTable").list();
                ("FROM UserInfoTable WHERE UPPER(user_id) LIKE UPPER('%" + search + "%')").list();

        }
        //Searches for similar users with a matching first and last name.
        //If the search contains more than 2 arguments, an exception will be thrown.
        else if(search.length() >= 3){
            String[] searchParts = search.split(" ");
            if(searchParts.length != 2)
                throw new ImproperUserSearchException();
            searchList = s.createQuery
                ("FROM UserInfoTable WHERE UPPER(first_name) LIKE UPPER('%" + searchParts[0] + "%') " +
                        "AND UPPER(last_name) LIKE UPPER('%" + searchParts[1] + "%')").list();
        }
        else{
            throw new ImproperUserSearchException();
        }

        return searchList;
    }
}

application-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<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"
xmlns:context="http://www.springframework.org/schema/context"
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
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">

    <context:annotation-config />

    <bean id="serverDatasource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName">
            <value>oracle.jdbc.driver.OracleDriver</value>
        </property>
        <property name="url">
            <value>url</value>
        </property>
    </bean>

    <bean id="dataSource" class="org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter">
        <property name="targetDataSource" ref="serverDatasource"/>
        <property name="username"><value>unknown</value></property>
        <property name="password"><value>unknown</value></property>
    </bean>

    <!--
    <bean id="propertyPlaceholderConfigurer"
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="systemPropertiesModeName" value="SYSTEM_PROPERTIES_MODE_OVERRIDE" />
        <property name="ignoreUnresolvablePlaceholders" value="true"/>
    </bean>
    -->

    <bean id="mySessionFactory"
        class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
        <property name="mappingLocations">
            <list>
                <value>WEB-INF/mapping/user_info.hbm.xml</value>
                <value>WEB-INF/mapping/login.hbm.xml</value>
                <value>WEB-INF/mapping/linked_accounts.hbm.xml</value>
                <value>WEB-INF/mapping/application_instance.hbm.xml</value>
            </list>
        </property>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">org.hibernate.dialect.OracleDialect</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop>
            </props>
        </property>
        <property name="dataSource" ref="serverDatasource"/>
    </bean>

    <bean id = "userInfoDaoImpl" class="arlua.dao.impl.UserInfoDaoImpl">
        <property name="sessionFactory" ref="mySessionFactory"/>
    </bean>

    <bean id = "loginDaoImpl" class="arlua.dao.impl.LoginDaoImpl">
        <property name="sessionFactory" ref="mySessionFactory"/>
    </bean>

    <bean id = "linkedAccountsDaoImpl" class="arlua.dao.impl.LinkedAccountsDaoImpl">
        <property name="sessionFactory" ref="mySessionFactory"/>
    </bean>

    <bean id = "applicationInstanceDaoImpl" class="arlua.dao.impl.ApplicationInstanceDaoImpl">
        <property name="sessionFactory" ref="mySessionFactory"/>
    </bean>

    <bean id = "roleDaoImpl" class="arlua.dao.impl.RoleDaoImpl">
        <property name="sessionFactory" ref="mySessionFactory"/>
    </bean>

    <bean id = "applicationApprovalDaoImpl" class="arlua.dao.impl.ApplicationApprovalDaoImpl">
        <property name="sessionFactory" ref="mySessionFactory"/>
    </bean>

    <bean id = "userDaoImpl" class="arlua.dao.impl.UserDaoImpl"/>

    <!--  *************  TRANSACTION MANAGEMENT USING AOP **************** -->

    <bean id="myTransactionManager"
        class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory" ref="mySessionFactory"/>    
    </bean>

    <aop:config>
        <aop:pointcut id="allMethods" expression="execution(* arlua.dao.TableEntityFetchDao.*(..))"/>
        <aop:advisor advice-ref="txAdvice" pointcut-ref="allMethods"/>
    </aop:config>

    <tx:advice id="txAdvice" transaction-manager="myTransactionManager">
        <tx:attributes>
            <tx:method name="saveEntity"
                propagation = "REQUIRES_NEW"
                isolation = "READ_COMMITTED"
                rollback-for = "Exception"/>
            <tx:method name="updateEntity"
                propagation = "REQUIRES_NEW"
                isolation = "READ_COMMITTED"
                rollback-for = "Exception"/>
            <tx:method name="getEntity"
                propagation = "REQUIRES_NEW"
                isolation = "READ_COMMITTED"
                rollback-for = "Exception"/>
            <tx:method name="getAllEntities"
                propagation = "REQUIRES_NEW"
                isolation = "READ_COMMITTED"
                rollback-for = "Exception"/>
            <tx:method name="searchEntity"
                propagation = "REQUIRES_NEW"
                isolation = "READ_COMMITTED"
                rollback-for = "Exception"/>
        </tx:attributes>
    </tx:advice>

</beans>

Answer

Luca Basso Ricci picture Luca Basso Ricci · Aug 8, 2013

java.sql.SQLException: invalid arguments in call means problem while connect to database do to URL/auth issues: looking stacktrace your are not getting connection via UserCredentialsDataSourceAdapter (your id="datasource") but using DriverManagerDataSource (id="serverDatasource"): in few word your are trying to connect without using user/password.

In sessionFactory bean creating wire:
<property name="dataSource" ref="dataSource"/>
instead of:
<property name="dataSource" ref="serverDatasource"/>

Related questions

How to fix Hibernate LazyInitializationException: failed to lazily initialize a collection of roles, could not initialize proxy - no Session

In the custom AuthenticationProvider from my spring project, I am trying read the list of authorities of the logged user, but I am facing the following error: org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: com.horariolivre.…

Read More
How to solve the “failed to lazily initialize a collection of role” Hibernate exception

I have this problem: org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: mvc3.model.Topic.comments, no session or session was closed Here is the model: @Entity @Table(name = "T_TOPIC") public class Topic { @Id @GeneratedValue(strategy=…

Read More
could not extract ResultSet in hibernate

I have a problem with Hibernate. I try to parse to List but It throws an exception: HTTP Status 500 - could not extract ResultSet. When I debug, It fault at line query.list()... My sample code here @Entity @Table(name = "…

Read More