Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Could not open connection

java mysql hibernate spring-mvc maven-3

19,812

Solution 1

That's one heck of mistake that I have done.! I apologies. There is no issue from the code and the xml configuration file.

Initial Plan: I have MySql setup in a virtual machine and running under port 3306. plan is to run the application from tomcat instance that is running on my host machine. So, I have initiated port forwarding from host to VM with port 3307->3306 and a remote user 'raju' is created in MYSql to be able to connect from my host(tomcat running in host).

Mistake: (because of some un-explainable reasons). I have decided to run the application in a separate tomcat instance that is running on my Virtual machine. In that case both tomcat and MySQL will be running on the same machine.

1: so there is no need do the PORT FORWARDING I should directly use the port 3306.

2: I have granted permission to the current remote user ( 'raju' ) to have the ability to connect locally as

grant all privileges on rajuDB.* to 'raju'@'localhost' identified by 'XXXX';

Final Result: Now I am able to fetch the details from database.Every thing works as expected.

Solution 2

You should trust the error message:

org.hibernate.exception.JDBCConnectionException: Could not open connection

Just because you can connect to the DB using a mySql client application, doesn't mean that you can get to the DB over TCP. MySQL can connect over TCP port, or it could connect using pipe. Your mySql client is probably connecting to the DB using named pipe.

https://dev.mysql.com/doc/refman/5.7/en/connecting.html

19,812

Author by

Ghost Rider

Updated on June 04, 2022

Comments

Ghost Rider almost 2 years

I am new to Spring-MVC and Hibernate. Trying to create a test web application with Spring-MVC(4.0.3) , Hibernate(4.3.5) and using MySQL as back end.

There is no issue in connecting to the DB as I tried to fetch the data from the same db in a sample testJavaClass by using simple JDBC connection statements and I am able to fetch the records.

Error Log:

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.JDBCConnectionException: Could not open connection
 org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:973)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
javax.servlet.http.HttpServlet.service(HttpServlet.java:727)

root cause

 org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Could not open connection
org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:515)
org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:420)
org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:257)
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
com.sun.proxy.$Proxy130.listPersons(Unknown Source)
com.journeldev.spring.PersonController.listPersons(PersonController.java:29)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:606)
org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
javax.servlet.http.HttpServlet.service(HttpServlet.java:620)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
javax.servlet.http.HttpServlet.service(HttpServlet.java:727)

Here is my pom.xml:

   <project xmlns="http://maven.apache.org/POM/4.0.0"   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>org.raju.spring</groupId>
    <artifactId>spring-mvc-hibernate</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>

  <properties>
    <java-version>1.7</java-version>
    <org.springframework-version>4.0.3.RELEASE</org.springframework-version>
    <org.aspectj-version>1.7.4</org.aspectj-version>
    <org.slf4j-version>1.7.5</org.slf4j-version>
    <hibernate.version>4.3.5.Final</hibernate.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>${org.springframework-version}</version>
        <exclusions>
            <!-- Exclude Commons Logging in favor of SLF4j -->
            <exclusion>
                <groupId>commons-logging</groupId>
                <artifactId>commons-logging</artifactId>
             </exclusion>
        </exclusions>
    </dependency>

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-webmvc</artifactId>
        <version>${org.springframework-version}</version>
    </dependency>

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>${org.springframework-version}</version>
    </dependency>

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>${hibernate.version}</version>
    </dependency>

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
        <version>${hibernate.version}</version>
    </dependency>

    <!-- Apache Commons DBCP -->
    <dependency>
        <groupId>commons-dbcp</groupId>
        <artifactId>commons-dbcp</artifactId>
        <version>1.4</version>
    </dependency>

    <!-- Spring ORM -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-orm</artifactId>
        <version>${org.springframework-version}</version>
    </dependency>

    <!-- AspectJ -->
    <dependency>
        <groupId>org.aspectj</groupId>
        <artifactId>aspectjrt</artifactId>
        <version>${org.aspectj-version}</version>
    </dependency>

    <!-- Logging -->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>${org.slf4j-version}</version>
    </dependency>

    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>jcl-over-slf4j</artifactId>
        <version>${org.slf4j-version}</version>
        <scope>runtime</scope>
    </dependency>

    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>${org.slf4j-version}</version>
        <scope>runtime</scope>
    </dependency>

    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.15</version>
        <exclusions>
            <exclusion>
                <groupId>javax.mail</groupId>
                <artifactId>mail</artifactId>
            </exclusion>
            <exclusion>
                <groupId>javax.jms</groupId>
                <artifactId>jms</artifactId>
            </exclusion>
            <exclusion>
                <groupId>com.sun.jdmk</groupId>
                <artifactId>jmxtools</artifactId>
            </exclusion>
            <exclusion>
                <groupId>com.sun.jmx</groupId>
                <artifactId>jmxri</artifactId>
            </exclusion>
        </exclusions>
        <scope>runtime</scope>
    </dependency>

    <!-- @Inject -->
    <dependency>
        <groupId>javax.inject</groupId>
        <artifactId>javax.inject</artifactId>
        <version>1</version>
    </dependency>

    <!-- Servlet -->
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>servlet-api</artifactId>
        <version>2.5</version>
        <scope>provided</scope>
    </dependency>

    <dependency>
        <groupId>javax.servlet.jsp</groupId>
        <artifactId>jsp-api</artifactId>
        <version>2.1</version>
        <scope>provided</scope>
    </dependency>

    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>jstl</artifactId>
        <version>1.2</version>
    </dependency>

    <!-- Test -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.7</version>
        <scope>test</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version>
    </dependency>

</dependencies>

<build>
    <plugins>
        <plugin>
            <artifactId>maven-eclipse-plugin</artifactId>
                <version>2.9</version>
                <configuration>
                    <additionalProjectnatures>
                         <projectnature>org.springframework.ide.eclipse.core.springnature</projectnature>
                    </additionalProjectnatures>
                    <additionalBuildcommands>
                        <buildcommand>org.springframework.ide.eclipse.core.springbuilder</buildcommand>
                    </additionalBuildcommands>
                    <downloadSources>true</downloadSources>
                    <downloadJavadocs>true</downloadJavadocs>
                </configuration>
        </plugin>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>2.5.1</version>
            <configuration>
                <source>1.6</source>
                <target>1.6</target>
                <compilerArgument>-Xlint:all</compilerArgument>
                <showWarnings>true</showWarnings>
                <showDeprecation>true</showDeprecation>
            </configuration>
        </plugin>
        <plugin>
            <groupId>org.codehaus.mojo</groupId>
            <artifactId>exec-maven-plugin</artifactId>
            <version>1.2.1</version>
            <configuration>
                <mainClass>org.test.int1.Main</mainClass>
            </configuration>
        </plugin>
    </plugins>
    <finalName>${project.artifactId}</finalName>
   </build>
  </project>

Project structure in Eclipse:

servlet-context.xml: (updated as per the answer by @Maciej Kowalski and I still see the same issue)

 <?xml version="1.0" encoding="UTF-8"?>
 <beans:beans xmlns="http://www.springframework.org/schema/mvc"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:beans="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd
    http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd http://www.springframework.org/schema/aop
    http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">

<!-- DispatcherServlet Context: defines this servlet's request-processing 
    infrastructure -->

<!-- Enables the Spring MVC @Controller programming model -->
<annotation-driven />

<!-- Handles HTTP GET requests for /resources/** by efficiently serving 
    up static resources in the ${webappRoot}/resources directory -->
<resources mapping="/resources/**" location="/resources/" />

<!-- Resolves views selected for rendering by @Controllers to .jsp resources 
    in the /WEB-INF/views directory -->
<beans:bean
    class="org.springframework.web.servlet.view.InternalResourceViewResolver">
    <beans:property name="prefix" value="/WEB-INF/views/" />
    <beans:property name="suffix" value=".jsp" />
</beans:bean>

<beans:bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <beans:property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <beans:property name="url"
        value="jdbc:mysql://localhost:3307/rajuDB" />
    <beans:property name="username" value="raju" />
    <beans:property name="password" value="raju" />
</beans:bean>

<!-- Hibernate 4 SessionFactory Bean definition -->
<beans:bean id="hibernate4AnnotatedSessionFactory"
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <beans:property name="dataSource" ref="dataSource" />
    <beans:property name="annotatedClasses">
        <beans:list>
            <beans:value>com.journeldev.spring.model.Person</beans:value>
        </beans:list>
    </beans:property>
    <beans:property name="hibernateProperties">
        <beans:props>
            <beans:prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect
            </beans:prop>
            <beans:prop key="hibernate.show_sql">true</beans:prop>
        </beans:props>
    </beans:property>
</beans:bean>

<beans:bean id="personDAO" class="com.journeldev.spring.dao.PersonDAOImpl">
    <beans:property name="sessionFactory" ref="hibernate4AnnotatedSessionFactory" />
</beans:bean>
<beans:bean id="personService" class="com.journeldev.spring.service.PersonServiceImpl">
    <beans:property name="personDAO" ref="personDAO"></beans:property>
</beans:bean>

<!-- the transactional advice (what 'happens'; see the <aop:advisor/> bean below) -->

<context:component-scan base-package="com.journeldev.spring" />

<tx:annotation-driven transaction-manager="transactionManager"/>

<beans:bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <beans:property name="sessionFactory" ref="hibernate4AnnotatedSessionFactory" />
 </beans:bean>

 <tx:advice id="txAdvice" transaction-manager="transactionManager">
    <!-- the transactional semantics... -->
    <tx:attributes>
        <!-- all methods starting with 'get' are read-only -->
        <tx:method name="get*" read-only="true"/>
        <!-- other methods use the default transaction settings (see below) -->
        <tx:method name="*"/>
    </tx:attributes>
 </tx:advice>

 <!-- ensure that the above transactional advice runs for any execution
    of an operation defined by the FooService interface -->
 <aop:config>
    <aop:pointcut id="personServiceOperation" expression="execution(* com.journeldev.spring.service.PersonServiceImpl.*(..))"/>
    <aop:advisor advice-ref="txAdvice" pointcut-ref="personServiceOperation"/>
 </aop:config>
 </beans:beans>

I have checked a lot of other threads on the internet and tried to change my servlet-context.xml file but nothing solved my issue.

Ex: tried to add extra parameters as per this StackOverflow Thread as below

    <beans:property name="initialSize" value="7"/>
    <beans:property name="maxActive" value="7"/>
    <beans:property name="maxWait" value="10000"/>
    <beans:property name="validationQuery" value="SELECT 1" />
    <beans:property name="validationQueryTimeout" value="34000" />
    <beans:property name="testOnBorrow" value="true" />

Result: issue still remains same:

Ex 2: tried to change data source as per this StackOverflow Thread as below

<beans:bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <beans:property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <beans:property name="url"
        value="jdbc:mysql://localhost:3307/rajuDB" />
    <beans:property name="username" value="raju" />
    <beans:property name="password" value="raju" />
</beans:bean>

result: issue remains same

Update as per comment from @James Jithin :19-Feb-2017

try{
        Class.forName("com.mysql.jdbc.Driver");
        Connection  conn=DriverManager.getConnection("jdbc:mysql://localhost:3307/rajuDB", "raju", "raju");
        Statement state=conn.createStatement();
        ResultSet rs=state.executeQuery("select * from Person");

        if(rs!=null && rs.next())
        {
            System.out.println("conn connected");
        }
        else
        {
            System.out.println("not connected");
        }
    }
    catch(Exception e){

    }