title: How to handle Connection pool size established by EntityManger through a Spring Application
body: <p>We have a web application implementing <code>Spring MVC 3.2</code> using <code>JPA</code> as a framework for ORM. Now the problem is that <code>EntityManager</code> 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. </p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>DB type: <code>MySQL</code>  </p>
  
  <p>My configuration for JPA is :</p>
  
  <pre><code>&lt;tx:annotation-driven transaction-manager="transactionManager" /&gt;
  &lt;bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"&gt;
      &lt;property name="entityManagerFactory" ref="entityManagerFactory" /&gt;
  &lt;/bean&gt;
  &lt;bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" /&gt;
  
  &lt;bean id="entityManagerFactory"
      class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"&gt;
      &lt;property name="packagesToScan" value="com.reppify" /&gt;
      &lt;property name="jpaPropertyMap" ref="jpaPropertyMap" /&gt;
      &lt;property name="dataSource" ref="dataSourceLocal" /&gt;
      &lt;property name="persistenceUnitName" value="cron-jpa" /&gt;
      &lt;property name="jpaVendorAdapter"&gt;
          &lt;bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" /&gt;
      &lt;/property&gt;
  &lt;/bean&gt;
  
  &lt;bean id="dataSourceLocal"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource"&gt;
      &lt;property name="driverClassName" value="${database.driver}" /&gt;
      &lt;property name="url" value="${database.url}" /&gt;
      &lt;property name="username" value="${database.user}" /&gt;
      &lt;property name="password" value="${database.password}" /&gt;
  &lt;/bean&gt;  
  </code></pre>
  
  <p>We are using <code>hibernate-jpa-api-2.0</code> jar as a dependency to the project.<br>
  And my JAVA Base DAO implementation for injecting <code>EntityManager</code> looks like: </p>
  
  <p>protected EntityManager entityManager;</p>
  
  <pre><code>@PersistenceContext
  public void setEntityManager(EntityManager entityManager) {
      this.entityManager = entityManager;
  }
  </code></pre>
  
  <p>Please suggest us an optimum solution for the same.</p>