title: Connection Reset using Spring + Hibernate
body: <p>I am using Spring + Hibernate on my JavaEE project.</p>
  
  <p>In this project the user can upload an XLS file which I should import to my database. Before importing I have to validate this file checking its integrity with the other entities on my database. So I have more or less the following:</p>
  
  <pre><code>// The importer
  @Component("importer")
  public class Importer {
  
      @Autowired
      FirstDAO firstDao;
  
      @Autowired
      SecondDAO secondDao;
  
      // Read the file and open it (65.000 lines for example)
  
      public void validate() {
  
          foreach line in the file {
              firstDAO.has(line[col1]);
              secondDao.has(line[col2]);
          }
  
          // It stores the valid objects in a List and persist them at the end 
  
      }
  }
  
  // The DAO
  @Repository
  public class FirstDao {
  
      @PersistenceContext
      protected EntityManager entityManager;
  
      @Transactional(propagation = Propagation.NOT_SUPPORTED)
      public boolean has(String name) {
  
           List&lt;Object&gt; result = entityManager.createQuery( from FIRST_TABLE where name = :name)
           .setParameter("name", name)
           .getResultList();
  
           if (result.size &gt; 0) return true;
           else return false;
      }
  
  }
  
  // The PersistenceContext/Hibernate configuration
  
  &lt;!-- Data Source --&gt;
  &lt;jee:jndi-lookup id="myDS" jndi-name="jdbc/my-DS" cache="true" proxy-interface="javax.sql.DataSource" /&gt;
  
  &lt;!-- Entity Manager --&gt;
  &lt;bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"&gt;
      &lt;property value="classpath:META-INF/my_persistence.xml" name="persistenceXmlLocation"/&gt;
      &lt;property name="dataSource" ref="myDS"/&gt;
      &lt;property name="persistenceUnitName" value="myPersistenceUnit" /&gt;
      &lt;!--
      &lt;property name="loadTimeWeaver"&gt;
          &lt;bean class="org.springframework.instrument.classloading.InstrumentationLoadTimeWeaver"/&gt;
      &lt;/property&gt;
      --&gt;
      &lt;property name="jpaVendorAdapter"&gt;
          &lt;bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"&gt;
              &lt;property name="database" value="ORACLE" /&gt;
              &lt;property name="showSql" value="false" /&gt;
          &lt;/bean&gt;
      &lt;/property&gt;
  &lt;/bean&gt;
  </code></pre>
  
  <p>After logging the application I have noticed:</p>
  
  <ul>
  <li>For each query (has method on my DAO) a connection is opened and closed with my Database.</li>
  <li>The memory on the server is being flooded (probably memory leak).</li>
  <li>After a lot of opening and closing connections I have a connection reset from the Database. Don't know why. And if I still keep requesting coonections, the Datasource is suspended.</li>
  </ul>
  
  <p>I have read somethings about <code>entityManager</code> but I still don't know if I am doing it right, so:</p>
  
  <ul>
  <li>Is it right to execute the validation in a for loop that way? (One connection for each item, meaning 130.000 connections open and closed in a 65000 lines file)</li>
  <li>I have read about <code>Stateless Persistence Context</code> for the entityManager. I suspect the memory leak may be there. Maybe Hibernate is kepting a lot of objects in the PersistenceContext. How do I tell Entity Manager to not cache those guys when validating?</li>
  </ul>
  
  <p>Thanks in advance.</p>