hibernate - 如何使用 JpaRepository 进行批量(多行)插入?
问题描述
当从服务层调用saveAll
my 的方法时JpaRepository
,List<Entity>
Hibernate 的跟踪日志显示每个实体发出单个 SQL 语句。
我可以强制它进行批量插入(即多行)而不需要手动摆弄EntityManger
事务等甚至原始 SQL 语句字符串吗?
对于多行插入,我的意思不仅仅是从以下过渡:
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
至:
start transaction
INSERT INTO table VALUES (1, 2)
INSERT INTO table VALUES (3, 4)
INSERT INTO table VALUES (5, 6)
end transaction
而是:
start transaction
INSERT INTO table VALUES (1, 2), (3, 4), (5, 6)
end transaction
在 PROD 中,我使用 CockroachDB,性能差异很大。
下面是一个重现问题的最小示例(为简单起见,H2)。
./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")
}
跑:
./gradlew bootRun
触发数据库插入:
curl http://localhost:8080/test_trigger
日志输出:
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 (?, ?)
解决方案
要使用 Sring Boot 和 Spring Data JPA 进行批量插入,您只需要两件事:
将选项设置为
spring.jpa.properties.hibernate.jdbc.batch_size
您需要的适当值(例如:20)。使用
saveAll()
你的 repo 方法和准备插入的实体列表。
工作示例在这里。
关于将插入语句转换为如下内容:
INSERT INTO table VALUES (1, 2), (3, 4), (5, 6)
这在 PostgreSQL 中可用:您可以reWriteBatchedInserts
在 jdbc 连接字符串中将选项设置为 true:
jdbc:postgresql://localhost:5432/db?reWriteBatchedInserts=true
然后 jdbc 驱动程序将进行此转换。
您可以在此处找到有关批处理的其他信息。
更新
Kotlin 中的演示项目:sb-kotlin-batch-insert-demo
更新
推荐阅读
- sql - GBQ中的窗口函数按顺序排序行为规范
- javascript - reactjs中返回未定义值的函数
- sql - 将 JSON 函数与 PostgreSQL 子查询一起使用
- reactjs - 在 react native 中,当切换屏幕时,默认屏幕也会被执行
- python - TypeError: __array__() 接受 1 个位置参数,但给出了 2 个(图像分类 Keras)
- optimization - 优化求解器在不同的编程语言中会改变其性能?
- mysql - Sequelize where 条件不查询正确的 DB 列
- javascript - 生成器的过滤器和映射函数
- python - 如何使我的 point_in_polygon 函数更 Pythonic?
- assembly - 6502 通过 TXA 和 TAX 使用 CMP BNE 循环