Wednesday, April 14, 2010

Database BatchInsert - comparing JPA, Hibernate & Jdbc using Spring

I was trying batch inserting operation using JPA. I'm using Hibernate implementation under JPA.

Option1. Understood the vanilla JPA don't have batchInsert. But, according to Hibernate documentation, it supports insert but in specific format viz. 'INSERT INTO ... SELECT ... form is supported; not the INSERT INTO ... VALUES ... form.'
So, I tried the following:
public int batchInsertMessageList(List customerList) {
final String jpaQuery = "insert into Customer (tm.getCustomerId(),tm.getAddress()) " +
"select :tm tm from dual";
final Object[] customerArray = customerList.toArray();
Object ret = getJpaTemplate().execute( new JpaCallback() {
           public Object doInJpa(EntityManager em) throws PersistenceException {
               Query query = em.createQuery(jpaQuery);
               if (customerArray != null && customerArray.length > 0) {
                   for (Object obj : customerArray) {
                       //query.setParameter(parameterIndex++, obj);
                    query.setParameter("tm", obj);
                   }
               }
               return query.executeUpdate();
           }
       });
return (Integer) ret;
}


I'm using Hibernate jars in version 3.3. Unfortunately, it is giving following exception:
query must begin with SELECT or FROM: insert [insert into Customer (tm) select :tm tm from dual]; nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: query must begin with SELECT or FROM: insert [insert into Customer (tm) select :tm tm from dual]

If ever there is an error in my query, I cannot solve it to make work, as Hibernate says it don't support.

Option2:
I'm going for option2, directly updating using getJdbcTemplate(). This would deliver better performance :) but my deviation from JPA :(


Option3:
Using Hibernate directly as below:
Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

for ( int i=0; i<100000; i++ ) {
   Customer customer = new Customer(.....);
   session.save(customer);
   if ( i % 20 == 0 ) { //20, same as the JDBC batch size
       //flush a batch of inserts and release memory: to avoid OutOfMemoryException
       session.flush();
       session.clear();
   }
}

1 comment:

  1. Does this mean there is no way to configure JPA or Hibernate to trigger jdbc batching?

    ReplyDelete