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.
How do i do it for oracle or MS Sql server ??
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 theuseBulkCopyForBatchInsert
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