How to do bulk (multi row) inserts with JpaRepository?
cockroachdb, hibernate, kotlin, spring-boot, spring-data-jpa
Solution
To get a bulk insert with Spring Boot and Spring Data JPA you need only two things:
set the option `spring.jpa.properties.hibernate.jdbc.batch_size` to appropriate value you need (for example: 20).
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.
Problem
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 (?, ?) ```