title: No matter what, I can't batch MySQL INSERT statements in Hibernate
body: <p>I'm currently facing the well-known and common Hibernate insert batch problem.</p>
  
  <p>I need to save batches 5 millions of rows long. I'm first trying with a much lighter payload. Since I have to insert entities of only 2 types (first all records of type A, then all records of type B, all pointing to common type C <code>ManyToOne</code> parent), I would like to take the most advantage from JDBC batch insert.</p>
  
  <p>I have already read lots of documentation, but none that I have tried worked.</p>
  
  <ul>
  <li><strong>I know</strong> that in order to use batch inserts I must not use an entity generator. So I removed the <code>AUTO_INCREMENT</code> ID and I'm setting the ID with a trick: <code>SELECT MAX(ID) FROM ENTITIES</code> and increment every time.</li>
  <li><strong>I know</strong> that I must flush the session regularly. I'll post code ahead, but anyway I perform a transaction every 500 elements.</li>
  <li><strong>I know</strong> that I have to set <code>hibernate.jdbc.batch_size</code> consistent with my application's bulk size, so I set it in the <code>LocalSessionFactoryBean</code> (Spring ORM integration)</li>
  <li><strong>I know</strong> I have to enable rewriting batched statements in connection URL.</li>
  </ul>
  
  <p>Here are my entities</p>
  
  <p>Common parent entity. This gets inserted first in a single transaction. I don't care about auto increment column <strong>here</strong>. Only <strong>one</strong> record per batch job</p>
  
  <pre><code>@Entity
  @Table(...)
  @SequenceGenerator(...)
  public class Deal
  {
  
      @Id
      @Column(
              name = "DEAL_ID",
              nullable = false)
      @GeneratedValue(
              strategy = GenerationType.AUTO)
      protected Long id;
  
      ................
  }
  </code></pre>
  
  <p>One of the children (let's say 2.5M records per batch)</p>
  
  <pre><code>@Entity
  @Table(
          name = "TA_LOANS")
  public class Loan
  {
  
      @Id
      @Column(
              name = "LOAN_ID",
              nullable = false)
      protected Long id;
  
      @ManyToOne(
              optional = false,
              targetEntity = Deal.class,
              fetch = FetchType.LAZY)
      @JoinColumn(
              name = "DEAL_ID",
              nullable = false)
      protected Deal deal;
  
  
      .............
  }
  </code></pre>
  
  <p>The other children type. Let's say the other 2.5M records</p>
  
  <pre><code>@Entity
  @Table(
          name = "TA_BONDS")
  public class Bond
  {
  
      @Id
      @Column(
              name = "BOND_ID")
  
      @ManyToOne(
              fetch = FetchType.LAZY,
              optional = false,
              targetEntity = Deal.class)
      @JoinColumn(
              name = "DEAL_ID",
              nullable = false,
              updatable = false)
      protected Deal deal;
  
  }
  </code></pre>
  
  <p>Simplified code that inserts records</p>
  
  <pre><code>    long loanIdCounter = loanDao.getMaxId(), bondIdCounter = bondDao.getMaxId(); //Perform SELECT MAX(ID)
  
      Deal deal = null;
  
      List&lt;Bond&gt; bondList = new ArrayList&lt;Bond&gt;(COMMIT_BATCH_SIZE); //500 constant value
      List&lt;Loan&gt; loanList = new ArrayList&lt;Loan&gt;(COMMIT_BATCH_SIZE);
  
      for (String msg: inputStreamReader)
      {
          log.debug(msg.toString());
  
          if (this is a deal)
          {
              Deal deal = parseDeal(msg.getMessage());
  
              deal = dealManager.persist(holder.deal); //Called in a separate transaction using Spring annotation @Transaction(REQUIRES_NEW)
  
          }
          else if (this is a loan)
          {
  
              Loan loan = parseLoan(msg.getMessage());
              loan.setId(++loanIdCounter);
              loan.setDeal(deal);
  
              loanList.add(loan);
  
              if (loanList.size() == COMMIT_BATCH_SIZE)
              {
                  loanManager.bulkInsert(loanList); //Perform a bulk insert in a single transaction, not annotated but handled manually this time
                  loanList.clear();
              }
          }
          else if (this is a bond)
          {
              Bond bond = parseBond(msg.getMessage());
              bond.setId(++bondIdCounter);
              bond.setDeal(deal);
  
              bondList.add(bond);
  
  
  
              if (bondList.size() == COMMIT_BATCH_SIZE) //As above
              {
                  bondManager.bulkInsert(bondList);
                  bondList.clear();
  
              }
          }
      }
  
      if (!bondList.isEmpty())
          bondManager.bulkInsert(bondList);
      if (!loanList.isEmpty())
          loanManager.bulkInsert(loanList);
      //Flush remaining items, not important
  </code></pre>
  
  <p>Implementation of <code>bulkInsert</code>:</p>
  
  <pre><code>@Override
  public void bulkInsert(Collection&lt;Bond&gt; bonds)
  {
      // StatelessSession session = sessionFactory.openStatelessSession();
      Session session = sessionFactory.openSession();
      try
      {
          Transaction t = session.beginTransaction();
          try
          {
              for (Bond bond : bonds)
                  // session.persist(bond);
                  // session.insert(bond);
                  session.save(bond);
          }
          catch (RuntimeException ex)
          {
              t.rollback();
          }
          finally
          {
              t.commit();
          }
      }
      finally
      {
          session.close();
      }
  
  }
  </code></pre>
  
  <p>As you can see from comments, I have tried several combinations of stateful/stateless <code>session</code>. None worked.</p>
  
  <p>My <code>dataSource</code> is a <a href="http://www.mchange.com/projects/c3p0/apidocs/com/mchange/v2/c3p0/ComboPooledDataSource.html"><code>ComboPooledDataSource</code></a> with following URL</p>
  
  <pre><code>&lt;b:property name="jdbcUrl" value="jdbc:mysql://server:3306/db?autoReconnect=true&amp;amp;rewriteBatchedStatements=true" /&gt;
  </code></pre>
  
  <p>My <code>SessionFactory</code></p>
  
  <pre><code>&lt;b:bean id="sessionFactory" class="class.that.extends.org.springframework.orm.hibernate3.LocalSessionFactoryBean" lazy-init="false" depends-on="dataSource"&gt;
          &lt;b:property name="dataSource" ref="phoenixDataSource" /&gt;
          &lt;b:property name="hibernateProperties"&gt;
              &lt;b:props&gt;
                  &lt;b:prop key="hibernate.dialect"&gt;${hibernate.dialect}&lt;/b:prop&gt; &lt;!-- MySQL5InnoDb--&gt;
                  &lt;b:prop key="hibernate.show_sql"&gt;${hibernate.showSQL}&lt;/b:prop&gt;
                  &lt;b:prop key="hibernate.jdbc.batch_size"&gt;500&lt;/b:prop&gt;
                  &lt;b:prop key="hibernate.jdbc.use_scrollable_resultset"&gt;false&lt;/b:prop&gt;
                  &lt;b:prop key="hibernate.cache.use_second_level_cache"&gt;false&lt;/b:prop&gt;
                  &lt;b:prop key="hibernate.cache.provider_class"&gt;org.hibernate.cache.EhCacheProvider&lt;/b:prop&gt;
                  &lt;b:prop key="hibernate.cache.use_query_cache"&gt;false&lt;/b:prop&gt;
                  &lt;b:prop key="hibernate.validator.apply_to_ddl"&gt;false&lt;/b:prop&gt;
                  &lt;b:prop key="hibernate.validator.autoregister_listeners"&gt;false&lt;/b:prop&gt;
                  &lt;b:prop key="hibernate.order_inserts"&gt;true&lt;/b:prop&gt;
                  &lt;b:prop key="hibernate.order_updates"&gt;true&lt;/b:prop&gt;
              &lt;/b:props&gt;
          &lt;/b:property&gt;
  &lt;/b:bean&gt;
  </code></pre>
  
  <p>Even if my project-wide class extends <code>LocalSessionFactoryBean</code>, it <strong>does not</strong> override its methods (only adds few project-wide methods)</p>
  
  <p>I'm getting mad since a few days. I read a few articles and none helped me enable batch inserts. I run all of my code from JUnit tests instrumented with Spring context (so I can <code>@Autowire</code> my classes). All of my attempts only produce a lots of separate <code>INSERT</code> statements</p>
  
  <ul>
  <li><a href="https://stackoverflow.com/questions/12011343/how-do-you-enable-batch-inserts-in-hibernate">https://stackoverflow.com/questions/12011343/how-do-you-enable-batch-inserts-in-hibernate</a></li>
  <li><a href="https://stackoverflow.com/questions/3469364/faster-way-to-batch-saves-with-hibernate">https://stackoverflow.com/questions/3469364/faster-way-to-batch-saves-with-hibernate</a></li>
  <li><a href="https://forum.hibernate.org/viewtopic.php?p=2374413">https://forum.hibernate.org/viewtopic.php?p=2374413</a></li>
  <li><a href="https://stackoverflow.com/questions/3026968/high-performance-hibernate-insert">https://stackoverflow.com/questions/3026968/high-performance-hibernate-insert</a></li>
  </ul>
  
  <p>What am I missing?</p>