Batch insertion using hibernate native queries

Question

Below is the code executing insert query in iterations :

session = sessionFactory.getCurrentSession();

        for (SampleBO items : listCTN) {    
            try {    
                query = session
                        .createSQLQuery("insert into xyx values(.......) where items="+items.getItem());

                OBJLOG.info("query executed is" + query);               
                query.executeUpdate();
                result = "success";
            }

Here queries are executing one after the other. How can I execute queries in batches?

Answer 1

First of all, if you want to use correct JDBC statements batching in hibernate, you have to set the batch size parameter in hibernate's configuration. Per Hibernate's documentation, "recommended values between 5 and 30":

hibernate.jdbc.batch_size=30

Then, in your loop, you would have to flush the session every X inserts (X should match the number set for the batch size). It should look like this:

int count = 0;
for (SampleBO items : listCTN) {      
    query = session
            .createSQLQuery("insert into xyx values(.......) where items="+items.getItem());
    OBJLOG.info("query executed is" + query);               
    query.executeUpdate();
    result = "success";

    if (++count % 30 == 0){
        session.flush();
        session.clear();
    }
}
//optional - I've seen issues in hibernate caused by not flushing data for the last iteration, for example -  the last 10 inserts.
session.flush();
session.clear();

See also:

https://www.tutorialspoint.com/hibernate/hibernate_batch_processing.htm https://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch15.html