Spring data jpa, how to write custom query for inserting multiple records in same table?

Question

Following is my table schema

CREATE TABLE Animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM;

In mysql I can directly insert multiple records in one insert sql query. Like following.

INSERT INTO animals (name) VALUES('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich');

However, how can I achieve the same thing in spring data jpa.

Right now I am using CrudRepository's Iterable save(Iterable entities); and I am eneded up with 6 insert statements

insert into Animals (name) values (?) insert into Animals (name) values (?) insert into Animals (name) values (?) insert into Animals (name) values (?) insert into Animals (name) values (?) insert into Animals (name) values (?)

How do I restrict to One insert query ? Any answer will be helpful regarding spring data jpa, hql or jpql .

Vlad Mihalcea · Accepted Answer · 2018-01-06 09:21:40Z

Assuming that you are using hibernate you need to tweak a couple of settings. You need to enable batching by putting a value in the hibernate.jdbc.batch_size property. However that might not cut it as depending on the number of different statements you also might need to order them to be able to batch them. For this you need to set the hibernate.order_inserts and hibernate.order_updates to true.

If you are using versions or timestamps to limit concurrent modification you might also need to enable hibernate.jdbc.batch_versioned_data

So all in all you probably need to add something like this to your configuration or persistence.xml

properties.put("hibernate.jdbc.batch_size", "25"); properties.put("hibernate.order_inserts", "true"); properties.put("hibernate.order_updates", "true"); properties.put("hibernate.jdbc.batch_versioned_data", "true");

A blog post explaining a bit more can be found here.

However if you are using MySQL it might not even work (yet) as MySQL requires an additional setting on the datasource to enable the usage of the addBatch for JDBC. You need to set the rewriteBatchedStatements to true to enable this feature for MySQL. Depending on your datasource you can add this to the connection string or as a seperate property.

Collectives™ on Stack Overflow

Spring data jpa, how to write custom query for inserting multiple records in same table?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related