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.