How to handle Connection pool size established by EntityManger through a Spring Application

Question

We have a web application implementing Spring MVC 3.2 using JPA as a framework for ORM. Now the problem is that EntityManager is creating a lot of open connections with the database. We want to handle it in such a way that for every query a connection should be established and closed after completion.

As per the spring implementation EntityManager is created once. But the problem here is we in some way want to handle the client connections that EntityManager is creating for querying the database.

Whenever the query is completed in the database, that connection goes into sleep, instead we want to close it once the query returns the result.

DB type: MySQL

My configuration for JPA is :

<tx:annotation-driven transaction-manager="transactionManager" />
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>
<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />

<bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="packagesToScan" value="com.reppify" />
    <property name="jpaPropertyMap" ref="jpaPropertyMap" />
    <property name="dataSource" ref="dataSourceLocal" />
    <property name="persistenceUnitName" value="cron-jpa" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
    </property>
</bean>

<bean id="dataSourceLocal"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${database.driver}" />
    <property name="url" value="${database.url}" />
    <property name="username" value="${database.user}" />
    <property name="password" value="${database.password}" />
</bean>

We are using hibernate-jpa-api-2.0 jar as a dependency to the project.
And my JAVA Base DAO implementation for injecting EntityManager looks like:

protected EntityManager entityManager;

@PersistenceContext
public void setEntityManager(EntityManager entityManager) {
    this.entityManager = entityManager;
}

Please suggest us an optimum solution for the same.

An EntityManager creates a single connection, and will close it if you close the EntityManager. All depends on your JPA implementation also, and you don't mention what you're using ... — Neil Stockton, Nov 27 '14 at 09:16
@SergiAlmar I have added the datasource bean definition as well. — Shikhar Srivastava, Nov 27 '14 at 09:29
and is the EntityManager being closed somehow? looked in the log? — Neil Stockton, Nov 27 '14 at 09:47
Spring manages resources for you so you don't introduce memory leaks and you do everything properly, my guess here is that's a problem caused for not using a connection pool — Sergi Almar, Nov 27 '14 at 10:00
@NeilStockton No the connection never closed until the project's context itself destroyed. Checked the logs and revisited the codebase also. — Shikhar Srivastava, Nov 27 '14 at 11:56
I'm talking about the EntityManager, not the Connection. The log tells you where the EM is closed. — Neil Stockton, Nov 27 '14 at 11:57
@NeilStockton Sorry I meant EntityManager only. Sorry for th typo. — Shikhar Srivastava, Nov 27 '14 at 12:05
So if you aren't closing the EntityManager then you are leaking resources all over the place and will run out of memory as well as connections. — Neil Stockton, Nov 27 '14 at 13:31
https://stackoverflow.com/questions/5117191/spring-jdbc-connection-pool-best-practices — user666, Jul 22 '20 at 08:28

score 7 · Answer 1 · answered Nov 27 '14 at 09:40

7

DriverManagerDataSource is not a connections pool, it creates a new connection on every call. This class is useful for testing but you shouldn't use it in production, choose a connection pool instead. There are many connection pools to choose from:

answered Nov 27 '14 at 09:40

Sergi Almar

8,054
3
32
30

Looking into the options provided. Also found out following link for further exploration. http://docs.spring.io/spring/docs/2.5.6/api/org/springframework/jdbc/datasource/DriverManagerDataSource.html – Shikhar Srivastava Nov 27 '14 at 12:10

How to handle Connection pool size established by EntityManger through a Spring Application

1 Answers1