how to make HQL that will generate SQL to insert multiple values in one statement?

Question

I need hibernate to generate sql like this INSERT INTO table_a (a_id, a_name) VALUES (5, 'a5'),(6, 'a6');.

With sql like that you can add 2 row with 1 statement. I can get

a_id, a_name

------------------
5     a5
6     a6

When in hibernate, when you save set of one to many relationship, hibernate will insert with multiple insert statement. This will cause if you insert 1000 row to 1 table with HQL will resulted in something like this :

INSERT INTO scoring.table_a (`a_id`, `a_name`) VALUES (1, 'a');
INSERT INTO scoring.table_a (`a_id`, `a_name`) VALUES (2, 'a');
....
...
..
INSERT INTO scoring.table_a (`a_id`, `a_name`) VALUES (1000, 'a');

And the elapsed time is :

Executed 1,000 queries; elapsed time (seconds) - Total: 0.78, SQL query: 0.78, Building output: 0

And when i test with the same values, use SQL INSERT INTO table_a (a_id, a_name) VALUES (5, 'a'),(6, 'a'),(),...,...,(1000, 'a'); will resulted in elapsed time like this :

Query 1 of 1, Rows read: 0, Elapsed time (seconds) - Total: 0.02, SQL query: 0.02, Building output: 0

The result of my test is, 1 statement of 1000 value(0.02s) will be about 39 times faster than 1000 insert statement with each have 1 value(0.78s) like hibernate do. So is there a way to make HQL that generate SQL like that for insert or maybe update also. Or this is means we must Override the hibernate dialect?

Thanks for the any hint

Arya · Answer 1 · 2013-08-23T15:02:36.440

2

HQL supports only the INSERT INTO ……… SELECT ……… ; there is no chance to write INSERT INTO ……… VALUES, i mean while writing the insert query, we need to select values from other table, we can’t insert our own values manually. (see documentation and this)

edited Aug 23 '13 at 15:02

answered Aug 23 '13 at 14:52

Arya

2,809
5
34
56

score 0 · Answer 2 · edited May 23 '17 at 12:16

0

You may set hibernate.jdbc.batch_size property to a non-zero value. It will allow Hibernate to use batch INSERT. Take a look at a official documentation.

The following code is an example how to use JDBC batch inserts through Hibernate:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
for ( int i=0; i < 10000; i++ ) {
    RecordA record = new RecordA(.....);
    session.save(record);
    if ( i % BATCH_SIZE == 0 ) { // BATCH_SIZE is your choice, but equal to property
        //flush a batch of inserts and release memory:
        session.flush();
        session.clear();
    }
}
tx.commit();
session.close();

It's also discussed here: Hibernate batch size confusion

edited May 23 '17 at 12:16

Community

1
1

answered Aug 15 '13 at 11:29

Merlinio

36
1
5

1

As far as I know, batch_size setting is a matter of memory optimization to minimize roundtrip without having memory leak. **the insert is still in multiple statement in one transaction**. so the batch_size is not the solution here. But thanks anyway.. – Angga Aug 15 '13 at 11:45
As noted in [Hibernate batch size confusion](http://stackoverflow.com/questions/6687422/hibernate-batch-size-confusion), the `hibernate.jdbc.batch_size` allows to use JDBC2 API feature that merges the **consecutive** INSERT statements with **identical signature** (table & cols) into one statement with multiple **if primary key is not** `GenerationType.Identity`. – Merlinio Aug 27 '13 at 13:03
yes, and **consecutive INSERT statements** is different with **single INSERT statement**. and as i said before in my comment, **the insert is still in multiple statement in one transaction**. – Angga Aug 28 '13 at 07:17

score 0 · Answer 3 · answered Jan 11 '22 at 04:11

There is a very nice chapter about batch processing in the Hibernate docs.

Set the property

hibernate.jdbc.batch_size 20

Then use this code

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:
        session.flush();
        session.clear();
    }
}

tx.commit();
session.close();

Channge the customer objects from above code according to your need.

how to make HQL that will generate SQL to insert multiple values in one statement?

3 Answers3