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

Solution 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

Solution 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.

Solution 3

add logger org.hibernate.engine.jdbc.batch.internal.BatchingBatch in level debug. hibernate can generate batches with size 1 or 2 in case of wrong order of inserts. try to use hibernate.order_inserts=true hibernate.order_updates=true

Comments

[Mitesh]

I want to do like this link

[Mitesh]

Your code snippet works fine. But I think this code is hibernate specific code, I want to use jpa not specific to hibernate. It may be my jpa is provider one of the hibernate, eclipselink or openjpa.

[Sureshkumar Panneerselvan]

stackoverflow.com/questions/2773302/… kindly check this access.redhat.com/site/documentation/en-US/…

[Innokenty]

see this answer on why Hibernate is disabling batch updates in case of identity id generator and what you can do: stackoverflow.com/questions/27697810/…