Hibernate JDBC Batch size is not working

Question

I'm using SpringFramework 3 and Hibernate 4 and MySQL 5 with jpa. My test code looks like...

@Repository
public class TestRepositoryImpl implements TestRepository {

  @PersistenceContext
  private EntityManager em;

  @Override
  @Transactional
  public void insertBulk() {
     Item it;
     for(int i= 0; i<1000;i++) {
        it = new Item();
        it.setPrice(Math.random()*100);
        em.persist(it);
     }
  }
}

My spring configuration

 <bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="application" />
</bean>

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>

<tx:annotation-driven transaction-manager="transactionManager" />

my persistence.xml

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
version="1.0">

<persistence-unit name="application" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <class>com.springapp.test.domain.Item</class>
    <class>com.springapp.test.domain.Order</class>
    <exclude-unlisted-classes>true</exclude-unlisted-classes>
    <properties>
        <property name="hibernate.show_sql" value="true" />
        <property name="hibernate.format_sql" value="false" />
        <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
        <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/testdb" />
        <property name="hibernate.connection.username" value="root" />
        <property name="hibernate.connection.password" value="" />
        <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />
        <property name="hibernate.hbm2ddl.auto" value="update" />
        <property name="hibernate.jdbc.batch_size" value="20" />
    </properties>
</persistence-unit>

</persistence>

When I call run my code it will fires insert query 1000 times instead of firing 50 insert query. What is the issue? Please help me to batch insert in jpa using hibernate

Answer 1

Please note that Hibernate would disable insert batching at the JDBC level transparently if the primary key of the inserting table isGenerationType.Identity.

save() only one record and then flush(), so there is only one appending INSERT SQL to be processed for every flush. That's why Hibernate cannot help you to batch inserting as there is only one INSERT SQL to be processed. You should save() up to the certain amount of records before calling flush() instead of calling flush() for every save().

The best practise of batch inserting is something like this:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
for  ( int i=0; i<888888; i++ ) {
  TableA record = new TableA();
    record.setXXXX();
    session.save(record)
    if ( i % 50 == 0 ) { //50, same as the JDBC batch size
        //flush a batch of inserts and release memory:
        session.flush();
        session.clear();
    }
}
tx.commit();
session.close();

You save and flush the records batch by batch. In the end of each batch you should clear the persistence context to release some memory to prevent memory exhaustion as every persistent object is placed into the first level cache (your JVM's memory). You could also disable the second-level cache to reduce the unnecessary overhead.

Kindly check this link http://docs.jboss.org/hibernate/orm/3.5/reference/en/html/batch.html

Answer 2

You have misunderstood "batch size". Batch size means send "batch size" number of queries together in one go instead of sending each query as the code fires the query. Therefore in this case there will be 1000 insert queries, sent 50 times with 20 insert queries in each batch.