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
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();
}
}
Does this mean there is no way to configure JPA or Hibernate to trigger jdbc batching?
ReplyDelete