如何使用JpaRepository进行批量(多行)插入? [英] How to do bulk (multi row) inserts with JpaRepository?
问题描述
从服务层调用带有较长List<Entity>的JpaRepository的saveAll方法时，Hibernate的跟踪日志记录将显示每个实体发出单个SQL语句.
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.
我可以强迫它执行批量插入(即多行)，而无需手动处理EntityManger，事务等，甚至是原始SQL语句字符串吗?
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
收件人:
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，并且性能上的差异非常明显.
In PROD I'm using CockroachDB, and the difference in performance is significant.
下面是一个重现问题的最小示例(为简单起见，H2).
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
触发数据库插入:
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获得批量插入，您只需要两件事:
To get a bulk insert with Sring Boot and Spring Data JPA you need only two things:
-
将选项
spring.jpa.properties.hibernate.jdbc.batch_size设置为所需的适当值(例如:20).
set the option
spring.jpa.properties.hibernate.jdbc.batch_sizeto appropriate value you need (for example: 20).
将回购的saveAll()方法与准备插入的实体列表一起使用.
use saveAll() method of your repo with the list of entities prepared for inserting.
工作示例是此处.
关于将insert语句转换为类似这样的内容:
Regarding the transformation of the insert statement into something like this:
INSERT INTO table VALUES (1, 2), (3, 4), (5, 6)
此类在PostgreSQL中可用:您可以在jdbc连接字符串中将选项reWriteBatchedInserts设置为true:
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
然后jdbc驱动程序将此转换.
then jdbc driver will do this transformation.
有关批处理的其他信息，您可以在此处.
Additional info about batching you can find here.
已更新
科特林的演示项目: sb-kotlin-batch-insert-demo
已更新
Hibernate disables insert batching at the JDBC level transparently if you use an
IDENTITYidentifier generator.
这篇关于如何使用JpaRepository进行批量(多行)插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
