springboot执行批量插入_如何使用JpaRepository进行批量（多行）插入？

最新推荐文章于 2023-05-19 14:39:45 发布

bannxinn

最新推荐文章于 2023-05-19 14:39:45 发布

阅读量1.4k

点赞数

文章标签： springboot执行批量插入

本文链接：https://blog.csdn.net/weixin_29626189/article/details/112843504

版权

When calling the saveAll method of my JpaRepository with a long List 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 {

}

@RestController

class ThingController(private val repository: ThingRepository) {

@GetMapping("/test_trigger")

fun trigger() {

val things: MutableList = 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) {

runApplication(*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 {

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: