Can hibernate's batch insert mechanism be used to insert multiple records using only 1 insert query

542 views Asked by At

I am inserting multiple objects of the same class using Hibernate. The problem is Hibernate generates 1 insert query for every object. Instead I need 1 query that would contain values for all the inserts.

I have my code for inserting/updating objects of Customer class in Customer table(MySql) something like this :

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
List<Customer> customers;//this list list will contain all the customers
int i=0;
while ( customers.hasNext() )  {
    Customer customer = customers.next();
    session.saveOrUpdate(customer);
    if ( i % 50 == 0 ) { //50, same as the JDBC batch size
        //flush a batch of inserts and release memory:
        session.flush();
        session.clear();
    }
    i++;
}
tx.commit();
session.close();

This above code is using Hibernate's batch insert/update mechanism.

The problem with this is if I have 5 customers then it produces 5 different insert queries(I could see it in the logs by setting show_sql to true in properties). Something like this :

INSERT INTO acct_doc_header (pk, name, age)
VALUES
(1,'Doe',10);

INSERT INTO acct_doc_header (pk, name, age)
VALUES
(2,'Eoe',10);

INSERT INTO acct_doc_header (pk, name, age)
VALUES
(3,'Foe',10);

INSERT INTO acct_doc_header (pk, name, age)
VALUES
(4,'Joe',10);

INSERT INTO acct_doc_header (pk, name, age)
VALUES
(5,'Koe',10);

Instead, I want a single insert query with all the statements inside it. Something like this:

INSERT INTO acct_doc_header (pk, name, age)
VALUES
(1,'Doe',10),
(2,'Eoe',10),
(3,'Foe',10),
(4,'Joe',10),
(5,'Koe',10);

Can this be achieved somehow with Hibernate? I think it should be supported by Hibernate. I have searched various articles but with no luck.

If it helps, I am using Hibernate 5.2.16 along with Spring 4.3.15 in my application.

0

There are 0 answers