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

Z时代
2024-01-10
分类：问答

从服务层长时间调用saveAllmy方法时，Hibernate的跟踪日志记录显示每个实体发出单个SQL语句。JpaRepository``List<Entity>

我可以强迫它进行批量插入（即多行），而无需手动处理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

触发数据库INSERT：

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()回购的使用方法以及准备插入的实体列表。

工作示例在这里。

关于将插入语句转换为类似以下内容的方法：

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

如果使用IDENTITY标识符生成器，则Hibernate透明地在JDBC级别禁用插入批处理。

以上是如何使用JpaRepository进行批量（多行）插入？的全部内容，来源链接： utcz.com/qa/436303.html

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

回答：

其他人也看了：