Database connections are not closing

Question

I have been up-to resolving this issue for 2 days, have been through several posts, but remain unsuccessful to resolve this issue. Your usual support is required.

My connections to database are not closing which cause problem when the connections exceeds the defined connections pool size. My hibernate & spring not releasing the connection or using the single connections for transaction.

servlet-context.xml

<?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"
    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">


    <context:component-scan base-package="com.dove.controller" />


    <!-- 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/" />

    <!-- Data Source -->
    <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:3306/dovecampaign" />
        <beans:property name="username" value="root" />
        <beans:property name="password" value="admin" />
    </beans:bean>

    <!-- Hibernate 4 SessionFactory Bean definition -->
    <beans:bean id="sessionFactory"
        class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
        <beans:property name="dataSource" ref="dataSource" />
        <beans:property name="annotatedClasses">
            <beans:list>
                <beans:value>com.dove.dao.model.User</beans:value>
                <beans:value>com.dove.dao.model.Media</beans:value>
                <beans:value>com.dove.dao.model.Votes</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:prop key="hibernate.hbm2ddl.auto">update</beans:prop>
                <beans:prop key="hibernate.current_session_context_class">thread</beans:prop>
                <beans:prop key="hibernate.connection.pool_size">10</beans:prop>
                <beans:prop key="hibernate.connection.release_mode">auto</beans:prop>
                <!-- <beans:prop key="hibernate.current_session_context_class">jta</beans:prop> -->
                <beans:prop key="connection.release_mode">after_statement</beans:prop>
                <beans:prop key="transaction.auto_close_session">true</beans:prop>
            </beans:props>
        </beans:property>
    </beans:bean>

    <!-- Spring hibernate transaction manager -->
    <beans:bean id="transactionManager"
        class="org.springframework.orm.hibernate4.HibernateTransactionManager">
        <beans:property name="sessionFactory" ref="sessionFactory"></beans:property>
    </beans:bean>

    <!-- File Upload Multipart Resolver -->
    <beans:bean id="multipartResolver"
        class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <!-- setting maximum upload size -->
        <beans:property name="maxUploadSize" value="200000000" />
    </beans:bean>

    <!-- Classes Mapping -->
    <beans:bean id="uploadController" class="com.dove.controller.UploadController">
        <beans:property name="userService" ref="userService"></beans:property>
        <beans:property name="votesService" ref="votesService"></beans:property>
    </beans:bean>
    <beans:bean id="userDao" class="com.dove.dao.manager.users.UserDaoImpl">
    </beans:bean>
    <beans:bean id="userService" class="com.dove.service.users.UserServiceImpl">
        <beans:property name="userDao" ref="userDao"></beans:property>
    </beans:bean>
    <beans:bean id="votesDao" class="com.dove.dao.manager.votes.VotesDaoImpl">
    </beans:bean>
    <beans:bean id="votesService" class="com.dove.service.votes.VotesServiceImpl">
        <beans:property name="votesDao" ref="votesDao"></beans:property>
    </beans:bean>

    <!-- Resolves views selected for rendering by @Controllers to .jsp resources 
        in the /WEB-INF/views directory -->
    <beans:bean id="tilesConfigurer"
        class="org.springframework.web.servlet.view.tiles2.TilesConfigurer">
        <beans:property name="definitions">
            <beans:list>
                <beans:value>/WEB-INF/tiles.xml</beans:value>
            </beans:list>
        </beans:property>
    </beans:bean>
    <beans:bean id="tilesViewResolver"
        class="org.springframework.web.servlet.view.UrlBasedViewResolver">
        <beans:property name="viewClass">
            <beans:value>
                org.springframework.web.servlet.view.tiles2.TilesView
            </beans:value>
        </beans:property>
        <beans:property name="order" value="0" />
    </beans:bean>

    <context:component-scan base-package="com.frameworkonly.tilesapp" />
</beans:beans>

GenericDaoImpl.java

[![package com.dove.dao.manager;

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.Collections;
import java.util.List;

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

@SuppressWarnings("unchecked")
@Repository
public abstract class GenericDaoImpl<E, K extends Serializable> implements GenericDao<E, K> {

    @Autowired
    private SessionFactory sessionFactory;

    public Session getSession() {
        return sessionFactory.getCurrentSession();
    }

    protected Class<? extends E> daoType;



    public GenericDaoImpl() {
        Type t = getClass().getGenericSuperclass();
        ParameterizedType pt = (ParameterizedType) t;
        daoType = (Class) pt.getActualTypeArguments()\[0\];
    }

    protected Session currentSession() {
        return sessionFactory.getCurrentSession();
    }

    public void add(E entity) {
        if(!currentSession().getTransaction().isActive()){
            currentSession().beginTransaction();
        }
        currentSession().save(entity);
        currentSession().getTransaction().commit();

    }

    public void saveOrUpdate(E entity) {
        if(!currentSession().getTransaction().isActive()){
            currentSession().beginTransaction();
        }
        currentSession().saveOrUpdate(entity);
    }

    public void update(E entity) {
//      currentSession().beginTransaction();
        if(!currentSession().getTransaction().isActive()){
            currentSession().beginTransaction();
        }
        currentSession().update(entity);    
        currentSession().getTransaction().commit();
    }

    public void remove(E entity) {
        if(!currentSession().getTransaction().isActive()){
            currentSession().beginTransaction();
        }
        currentSession().delete(entity);
    }

    public E find(K key) {
        if(!currentSession().getTransaction().isActive()){
            currentSession().beginTransaction();
        }
        return (E) currentSession().get(daoType, key);
    }

    public List<E> getAll() {
        if(!currentSession().getTransaction().isActive()){
            currentSession().beginTransaction();
        }
        List<E> list = currentSession().createCriteria(daoType).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();
        currentSession().getTransaction().commit();
        return list;
    }

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/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.cimple.dove</groupId>
    <artifactId>dovecampaign</artifactId>
    <packaging>war</packaging>
    <version>0.0.1-SNAPSHOT</version>
    <name>dovecampaign Maven Webapp</name>
    <url>http://maven.apache.org</url>
    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
        <!-- Spring Framework -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>3.2.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>3.2.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>3.2.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>3.2.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>3.2.0.RELEASE</version>
            <exclusions>
                <exclusion>
                    <groupId>commons-logging</groupId>
                    <artifactId>commons-logging</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>3.2.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>3.2.0.RELEASE</version>
        </dependency>
        <!-- Need this for json to/from object -->
        <!-- <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> 
            <version>2.1.0</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> 
            <artifactId>jackson-core</artifactId> <version>2.1.0</version> n </dependency> -->
        <!-- Logging -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.5.11</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jcl-over-slf4j</artifactId>
            <version>1.5.11</version>
            <scope>runtime</scope>
        </dependency>
        <dependency> 
            <groupId>org.slf4j</groupId> 
            <artifactId>slf4j-log4j12</artifactId> 
            <version>1.5.11</version> 
            <scope>runtime</scope> 
        </dependency> 
        <dependency> 
            <groupId>log4j</groupId> 
            <artifactId>log4j</artifactId> 
            <version>1.2.16</version> 
            <scope>runtime</scope> 
        </dependency>
        <!-- Apache tiles -->
        <dependency>
            <groupId>org.apache.tiles</groupId>
            <artifactId>tiles-api</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.tiles</groupId>
            <artifactId>tiles-core</artifactId>
            <version>2.2.2</version>
            <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-jdk14</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.apache.tiles</groupId>
            <artifactId>tiles-jsp</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.tiles</groupId>
            <artifactId>tiles-servlet</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.tiles</groupId>
            <artifactId>tiles-template</artifactId>
            <version>2.2.2</version>
        </dependency>
        <!-- jstl Servlet -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet.jsp.jstl</groupId>
            <artifactId>jstl-api</artifactId>
            <version>1.2</version>
            <scope>provided</scope>
        </dependency>
        <!-- Joda Time -->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.4</version>
        </dependency>
        <!-- Jackson -->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.0.0</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-core</artifactId>
            <version>2.0.0-RC3</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-annotations</artifactId>
            <version>2.0.0</version>
        </dependency>
        <!-- HTTP Client -->
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient-osgi</artifactId>
            <version>4.3</version>
        </dependency>
        <!-- Amazon AWS -->
        <!-- <dependency>
            <groupId>com.amazonaws</groupId>
            <artifactId>aws-java-sdk-sns</artifactId>
            <version>1.10.66</version>
        </dependency>
        <dependency>
            <groupId>com.amazonaws</groupId>
            <artifactId>aws-java-sdk-s3</artifactId>
            <version>1.10.66</version>
        </dependency> -->
        <dependency>
          <groupId>com.amazonaws</groupId>
          <artifactId>aws-java-sdk</artifactId>
          <version>1.4.3</version>
        </dependency>
        <!-- http client for Amazon AWS -->
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient</artifactId>
            <version>4.2.2</version>
        </dependency>
        <!-- http core for Amazon AWS -->
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpcore</artifactId>
            <version>4.2.2</version>
        </dependency>
        <!-- httpmime for Amazon AWS -->
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpmime</artifactId>
            <version>4.2.2</version>
        </dependency>
        <!-- httpclient cache for Amazon AWS -->
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient-cache</artifactId>
            <version>4.2.2</version>
        </dependency>
        <!-- Hibernate -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>4.1.9.Final</version>
        </dependency>
        <!-- MySql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.35</version>
        </dependency>
        <!-- Apache Common DBCP -->
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.2.2</version>
        </dependency>
        <!-- Apache Common Fileupload -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3</version>
        </dependency>
        <!-- Apache Common Codec -->
        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
            <version>1.10</version>
        </dependency>
        <!-- Email utility - Custom dependency -->
        <dependency>
             <groupId>com.cimple</groupId>
             <artifactId>EmailUtility</artifactId>
             <version>1.0.0</version>
        </dependency>
    </dependencies>
    <build>
        <!-- <pluginManagement> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> 
            <artifactId>maven-compiler-plugin</artifactId> <version>3.0</version> <configuration> 
            <source>1.6</source> <target>1.6</target> </configuration> </plugin> <plugin> 
            <groupId>org.jboss.as.plugins</groupId> <artifactId>jboss-as-maven-plugin</artifactId> 
            <version>5.1.0.GA</version> <executions> <execution> <phase>install</phase> 
            <goals> <goal>deploy</goal> </goals> </execution> </executions> </plugin> 
            </plugins> </pluginManagement> -->
        <finalName>dovecampaign</finalName>
    </build>
</project>

i did add currentSession().close statement under each method after commit but i didn't work. Even concurrent hits breeds exception like session is closed! — Ahsan Iqbal, May 05 '16 at 07:18
If you add close session in each method and you expect the calling class to perform two calls, then they'll be executed in different sessions. Your calling class is the one who knows about what has to be executed in each transaction. See Spring's `@Transactional` utilities. But what you've got right now is a extremelly unadvisable design, as it manages all the calls through a single session. — Aritz, May 05 '16 at 07:26
Remove `current_session_context` from your configuration, that is in terfering with proper transaction and thus resource management. Also your `hibernate.connection` properties are useless as you are injecting a `DataSource` for which you should use an actual connection pool and not a `DriverManagerDataSource` (that isn't a pool). Also you shouldn't mess around with starting transactions yourself `@Transactional` will do that for you (now I suspect it didn't work because of your wrong configuration). — M. Deinum, May 05 '16 at 07:26
@M. Deinum: I appreciate your support. I wasn't starting transactions early explicitly; but expecting `@Transnational` to do that. I did this because i was facing exceptions like no active transaction found. That's why i did this explicitly. Notwithstanding, i had performed the changes you asked to do, but didn't work. — Ahsan Iqbal, May 06 '16 at 07:40
@XtremeBiker Will you please guide me or let me know how the my application structure is extremely non-advisable So, i could do changes if needed. Thanks. :) — Ahsan Iqbal, May 06 '16 at 09:12
Basically, don't use a single session for every operation you make. Sessions are not intended to be thread safe: http://stackoverflow.com/questions/3777794/is-hibernates-session-thread-safe — Aritz, May 06 '16 at 09:19
You should use a single session as long as it is in the same transaction it doesn't matter. Only slapping `@Transactional` on there isn't going to help, not without `` to actually enable annotation driven transactions. — M. Deinum, May 08 '16 at 08:07

score 0 · Answer 1 · answered May 05 '16 at 07:11

0

 after comit
 use currentSession().close();

answered May 05 '16 at 07:11

Avinash Mendse

57
3

i did add currentSession().close statement under each method after commit but i didn't work. Even concurrent hits breeds exception like session is closed! – Ahsan Iqbal May 05 '16 at 07:18

Database connections are not closing

1 Answers1