英文:
Spring data jpa batch execute all inserts operations
问题
我需要在MySQL中插入大量数据（大约10万条左右），然后我尝试使用Spring Data Jpa进行批量插入，为此我使用了一个包含30条记录的简单示例。
首先，我移除了我的实体上的 @GeneratedValue 并实现了 Persistable，以避免在插入之前进行选择：
@Entity
public class User implements Persistable {
@Id
private Integer id;
// 在这里添加属性...
然后，在我的 application.yml 文件中：
spring:
jpa:
properties:
hibernate.jdbc.batch_size: 30
hibernate.generate_statistics: true
show-sql: true
hibernate:
ddl-auto: validate
datasource:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/db?cachePrepStmts=true&reWriteBatchedInserts=true
// 用户名和密码
我有一个简单的存储库：
public interface UserRepository extends JpaRepository<User, Integer> { }
以及插入方法：
public void process() {
List<User> users = new ArrayList<>();
for (int i = 1; i <= 30; i++) {
User user = new User();
user.setId(i);
// 设置属性...
users.add(user);
if (i % 30 == 0) {
userRepository.saveAll(users);
users.clear();
}
}
}
然后我认为正确的是进行1次批处理操作，但是我得到了29个语句：
1745893 纳秒用于获取1个JDBC连接；
0 纳秒用于释放0个JDBC连接；
3524622 纳秒用于准备30个JDBC语句；
68290171 纳秒用于执行29个JDBC语句；
215125391 纳秒用于执行1个JDBC批处理；
0 纳秒用于执行0个L2C放置操作；
0 纳秒用于执行0个L2C命中操作；
0 纳秒用于执行0个L2C未命中操作；
240389888 纳秒用于执行1次刷新操作（刷新了29个实体和29个集合）；
0 纳秒用于执行0次部分刷新操作（刷新了0个实体和0个集合）。
有什么建议吗？谢谢！
英文:
I need to insert a lot of data in a mysql(Something around of 100k) then I'm trying to use Spring Data Jpa to insert in batch, for that I'm using a simple example with 30 records.
The first thing was remove @GeneratedValue os my entity end implements Persistable to no need a select before a insert:
@Entity
public class User implements Persistable {
@Id
private Integer id;
// properties here...
Then, on my application.yml:
spring:
jpa:
properties:
hibernate.jdbc.batch_size: 30
hibernate.generate_statistics: true
show-sql: true
hibernate:
ddl-auto: validate
datasource:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/db?cachePrepStmts=true&reWriteBatchedInserts=true
// user and password
I have a simple repository:
public interface UserRepository extends JpaRepository<User, Integer> { }
and the insert method:
public void process() {
List<User> users = new ArrayList<>();
for (int i = 1 ; i <= 30; i++) {
User user = new User();
user.setId(i);
// set properties
users.add(user);
if(i % 30 == 0) {
userRepository.saveAll(users);
users.clear();
}
}
}
Then I think the correct is 1 batch operation only, but I had 29 statements:
1745893 nanoseconds spent acquiring 1 JDBC connections;
0 nanoseconds spent releasing 0 JDBC connections;
3524622 nanoseconds spent preparing 30 JDBC statements;
68290171 nanoseconds spent executing 29 JDBC statements;
215125391 nanoseconds spent executing 1 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
240389888 nanoseconds spent executing 1 flushes (flushing a total of 29 entities and 29 collections);
0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
Any ideias?
thanks!
答案1
得分: 1
spring.jpa.properties.hibernate.order_inserts=true
英文:
Test next property:
spring.jpa.properties.hibernate.order_inserts=true
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论