2

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.

DockYard
  • 989
  • 2
  • 12
  • 29
  • AFAIK that is not possible, but it is also not a drawback (besides some readability). performace wise there is no (real) difference, as the most time is used to create/commit/rollback transactions – XtremeBaumer Jul 19 '19 at 11:28
  • @XtremeBaumer actually I was trying out both the approaches and I could see for more 500 records, there's a difference in performance. While it took around 7s for separate inserts, it took only 0.6s for a single insert query. Considerding the size of my application, this difference of 6-7 s is pretty huge for me – DockYard Jul 19 '19 at 11:33
  • Hibernate is not the best tool to perform a mass data migration. I'll have to step down to plain *JDBC batch insert* to speed up substantially. This will basically do what you intend - one roundtrip to insert the *batch_size* of rows. – Marmite Bomber Jul 19 '19 at 11:42
  • @DockYard have you checked the number of transactions used for each approach? – XtremeBaumer Jul 19 '19 at 11:46
  • *I think it should be supported by Hibernate*. To meet the JPA spec the ID must be available after a new entity is made persistent. Depending on the database, the generated key can be returned as response to the insert statement. This will obviously not work in the case of multi value insert. – Alan Hay Jul 19 '19 at 13:03
  • @AlanHaycould you help with some code please ? – DockYard Jul 19 '19 at 16:10
  • @XtremeBaumer No mate. I used a normal SQL interface to check the timing of the queries. In this case how do I check the number of transactions used? – DockYard Jul 19 '19 at 16:11
  • Does your Hibernate configuration have a way that you can enable [rewriteBatchedStatements=true](https://stackoverflow.com/a/26313288/2144390)? – Gord Thompson Jul 19 '19 at 18:22
  • @gord No, write now, it's without any extra params. Can adding these params achive my aim? – DockYard Jul 19 '19 at 19:42
  • Perhaps [this question](https://stackoverflow.com/q/30352212/2144390) might shed some light. – Gord Thompson Jul 19 '19 at 20:18

0 Answers0