How to do bulk (multi row) inserts with JpaRepository?

Question

When calling the saveAll method of my JpaRepository with a long List<Entity> from the service layer, trace logging of Hibernate shows single SQL statements being issued per entity.

Can I force it to do a bulk insert (i.e. multi-row) without needing to manually fiddle with EntityManger, transactions etc. or even raw SQL statement strings?

With multi-row insert I mean not just transitioning from:

start transaction INSERT INTO table VALUES (1, 2) end transaction start transaction INSERT INTO table VALUES (3, 4) end transaction start transaction INSERT INTO table VALUES (5, 6) end transaction 

to:

start transaction INSERT INTO table VALUES (1, 2) INSERT INTO table VALUES (3, 4) INSERT INTO table VALUES (5, 6) end transaction 

but instead to:

start transaction INSERT INTO table VALUES (1, 2), (3, 4), (5, 6) end transaction 

In PROD I'm using CockroachDB, and the difference in performance is significant.

Below is a minimal example that reproduces the problem (H2 for simplicity).

---

./src/main/kotlin/ThingService.kt:

package things  import org.springframework.boot.autoconfigure.SpringBootApplication import org.springframework.boot.runApplication import org.springframework.web.bind.annotation.RestController import org.springframework.web.bind.annotation.GetMapping import org.springframework.data.jpa.repository.JpaRepository import javax.persistence.Entity import javax.persistence.Id import javax.persistence.GeneratedValue  interface ThingRepository : JpaRepository<Thing, Long> { }  @RestController class ThingController(private val repository: ThingRepository) {     @GetMapping("/test_trigger")     fun trigger() {         val things: MutableList<Thing> = mutableListOf()         for (i in 3000..3013) {             things.add(Thing(i))         }         repository.saveAll(things)     } }  @Entity data class Thing (     var value: Int,     @Id     @GeneratedValue     var id: Long = -1 )  @SpringBootApplication class Application { }  fun main(args: Array<String>) {     runApplication<Application>(*args) } 

---

./src/main/resources/application.properties:

jdbc.driverClassName = org.h2.Driver jdbc.url = jdbc:h2:mem:db jdbc.username = sa jdbc.password = sa  hibernate.dialect=org.hibernate.dialect.H2Dialect hibernate.hbm2ddl.auto=create  spring.jpa.generate-ddl = true spring.jpa.show-sql = true  spring.jpa.properties.hibernate.jdbc.batch_size = 10 spring.jpa.properties.hibernate.order_inserts = true spring.jpa.properties.hibernate.order_updates = true spring.jpa.properties.hibernate.jdbc.batch_versioned_data = true 

---

./build.gradle.kts:

import org.jetbrains.kotlin.gradle.tasks.KotlinCompile  plugins {     val kotlinVersion = "1.2.30"     id("org.springframework.boot") version "2.0.2.RELEASE"     id("org.jetbrains.kotlin.jvm") version kotlinVersion     id("org.jetbrains.kotlin.plugin.spring") version kotlinVersion     id("org.jetbrains.kotlin.plugin.jpa") version kotlinVersion     id("io.spring.dependency-management") version "1.0.5.RELEASE" }  version = "1.0.0-SNAPSHOT"  tasks.withType<KotlinCompile> {     kotlinOptions {         jvmTarget = "1.8"         freeCompilerArgs = listOf("-Xjsr305=strict")     } }  repositories {     mavenCentral() }  dependencies {     compile("org.springframework.boot:spring-boot-starter-web")     compile("org.springframework.boot:spring-boot-starter-data-jpa")     compile("org.jetbrains.kotlin:kotlin-stdlib-jdk8")     compile("org.jetbrains.kotlin:kotlin-reflect")     compile("org.hibernate:hibernate-core")     compile("com.h2database:h2") } 

---

Run:

./gradlew bootRun 

Trigger DB INSERTs:

curl http://localhost:8080/test_trigger 

Log output:

Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: select thing0_.id as id1_0_0_, thing0_.value as value2_0_0_ from thing thing0_ where thing0_.id=? Hibernate: call next value for hibernate_sequence Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) Hibernate: insert into thing (value, id) values (?, ?) 

Answer 1

To get a bulk insert with Sring Boot and Spring Data JPA you need only two things:

1. set the option spring.jpa.properties.hibernate.jdbc.batch_size to appropriate value you need (for example: 20).

2. use saveAll() method of your repo with the list of entities prepared for inserting.

Working example is here.

Regarding the transformation of the insert statement into something like this:

INSERT INTO table VALUES (1, 2), (3, 4), (5, 6) 

the such is available in PostgreSQL: you can set the option reWriteBatchedInserts to true in jdbc connection string:

jdbc:postgresql://localhost:5432/db?reWriteBatchedInserts=true 

then jdbc driver will do this transformation.

Additional info about batching you can find here.

UPDATED

Demo project in Kotlin: sb-kotlin-batch-insert-demo

UPDATED

Hibernate disables insert batching at the JDBC level transparently if you use an IDENTITY identifier generator.