Hibernate batch insert multiple rows on MySQL

Hibernate batch insert refers to making Hibernate insert multiple records with one query. Some databases support this kind of queries, and some do not. MySQL supports batch inserts, but in order to make Hibernate batch inserts work you need to configure it properly.

A batch insert query looks like this:

INSERT INTO table (col1, col2) VALUES ('val1', 'val2'), ('val1', 'val2')[, ...]

This means that only one trip to the database is made to insert the values after the “VALUES” keyword into the table “table”. The MySQL batch insert limit is set by the max_allowed_packet parameter, meaning that you are limited only by the length of the query. Click this link if you want to know how to set the MySQL max_allowed_packet.

How to do Hibernate batch insert of multiple rows at once

So, let’s say we want to use Hibernate to batch insert multiple rows at once. Hibernate documentation describes this, but it’s incomplete.

Enable the Hibernate JDBC batch processing by setting the following property:

hibernate.jdbc.batch_size 50

The batch insert Java code looks like this:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

for ( int i=0; i<100000; i++ ) {
    Customer customer = new Customer(.....);
    session.save(customer);
    if ( i % 50 == 0 ) { //50, same as the JDBC batch size
        //flush a batch of inserts and release memory:
        session.flush();
        session.clear();
    }
}

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

Hibernate batch processing documentation is available here.

IMPORTANT!

In order for Hibernate batch insert to work on MySQL you need to make an extra setting in your connection string like this

jdbc:mysql://localhost:3306/database?rewriteBatchedStatements=true

This is because MySQL does not support batch form of prepare statement parameter bindings so you need to instruct the driver by the use of the rewriteBatchedStatements parameter to allow this.

That’s it, it’s all you need to do to activate Hibernate batch insert on MySQL.

John Negoita

View posts by John Negoita
I'm a Java programmer, been into programming since 1999 and having tons of fun with it.
[jetpack-related-posts]

2 Comments

  1. joyJuly 9, 2019

    How do i do it for oracle or MS Sql server ??

    Reply
    1. John NegoitaJuly 9, 2019

      I haven’t tried to do something similar with Hibernate on MSSQL, but I would expect to use the same hibernate.jdbc.batch_size and probably you need something like the useBulkCopyForBatchInsert parameter in the JDBC connection string.

      From the docs (https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-2017): “Beginning with Microsoft JDBC Driver 7.0 for SQL Server, this connection property can be enabled to make use of Bulk Copy API when performing batch insert operations using java.sql.PreparedStatement for performance improvement.”

      Please try it and let me know if it works

      Give it a try

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to top