Spring Boot JPA批量插入 [英] Spring Boot JPA Bulk insert
问题描述
我有3个实体Parent，Child，SubChild.父母是孩子的父母，孩子是子孩子的父母.我需要插入约700个父对象.父级可以有50个子级对象.子级可以有50个子项对象.
我尝试了普通的repository.save(ListOfObjects)，大约需要4分钟.
I have 3 Entities Parent,Child,SubChild. Parent is a parent of Child and Child is a parent of SubChild. I need to insert around 700 objects of Parent. Parent can have 50 Objects of Child. Child can have 50 objects of SubChild.
I tried normal repository.save(ListOfObjects) it takes approx 4mins.
然后我尝试根据批次大小(500)使用实体管理器的persist，flush和clear.这也花费了大约4分钟.
性能没有太大差异.请提出一种有效地插入大量数据的最佳方法.
Then I tried using entity manager's persist, flush and clear based on batch size(500). This also took approx 4 mins.
There wasn't much difference in performance. Please suggest a best way to insert such a high amount of data efficiently.
父母
@Entity
public class Parent {
@Id @GeneratedValue(strategy= GenerationType.AUTO)
private Long parentId;
private String aaa;
private String bbb;
private String ccc;
@Version
private Long version;
@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "parent", fetch = FetchType.LAZY)
@JoinColumnsOrFormulas({
@JoinColumnOrFormula(column=@JoinColumn(name="parentId",referencedColumnName="parentId",nullable=false))})
private List<Child> childs = new ArrayList<>();
public Long getParentId() {
return parentId;
}
public void setParentId(Long parentId) {
this.parentId = parentId;
}
public String getAaa() {
return aaa;
}
public void setAaa(String aaa) {
this.aaa = aaa;
}
public String getBbb() {
return bbb;
}
public void setBbb(String bbb) {
this.bbb = bbb;
}
public String getCcc() {
return ccc;
}
public void setCcc(String ccc) {
this.ccc = ccc;
}
public Long getVersion() {
return version;
}
public void setVersion(Long version) {
this.version = version;
}
public List<Child> getChilds() {
return childs;
}
public void setChilds(List<Child> childs) {
this.childs = childs;
}
}
孩子
@Entity
public class Child {
@Id @GeneratedValue(strategy= GenerationType.AUTO)
private Long childId;
private String ddd;
private String ccc;
private Integer eee;
@OneToMany(cascade = CascadeType.ALL,orphanRemoval = true, mappedBy = "child", fetch = FetchType.LAZY)
@JoinColumnsOrFormulas({
@JoinColumnOrFormula(column = @JoinColumn(name = "childId", referencedColumnName = "childId", nullable = false)) })
private List<SubChild> subChilds = new ArrayList<>();
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumnsOrFormulas({
@JoinColumnOrFormula(column= @JoinColumn( name="parentId",referencedColumnName="parentId",nullable=false))
})
private Parent parent;
public Long getChildId() {
return childId;
}
public void setChildId(Long childId) {
this.childId = childId;
}
public String getDdd() {
return ddd;
}
public void setDdd(String ddd) {
this.ddd = ddd;
}
public String getCcc() {
return ccc;
}
public void setCcc(String ccc) {
this.ccc = ccc;
}
public Integer getEee() {
return eee;
}
public void setEee(Integer eee) {
this.eee = eee;
}
public List<SubChild> getSubChilds() {
return subChilds;
}
public void setSubChilds(List<SubChild> subChilds) {
this.subChilds = subChilds;
}
public Parent getParent() {
return parent;
}
public void setParent(Parent parent) {
this.parent = parent;
}
}
SubChild
@Entity
public class SubChild {
@Id @GeneratedValue(strategy= GenerationType.AUTO)
private Long subChildId;
private String fff;
private String ggg;
private Integer hhh;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumnsOrFormulas({
@JoinColumnOrFormula(column= @JoinColumn( name="childId",referencedColumnName="childId",nullable=false))
})
private Child child;
public Long getSubChildId() {
return subChildId;
}
public void setSubChildId(Long subChildId) {
this.subChildId = subChildId;
}
public String getFff() {
return fff;
}
public void setFff(String fff) {
this.fff = fff;
}
public String getGgg() {
return ggg;
}
public void setGgg(String ggg) {
this.ggg = ggg;
}
public Integer getHhh() {
return hhh;
}
public void setHhh(Integer hhh) {
this.hhh = hhh;
}
public Child getChild() {
return child;
}
public void setChild(Child child) {
this.child = child;
}
}
用于持久保留父实体列表的存储库方法
Repository method used for persisting the list of Parent Entity
@Value("${spring.jpa.hibernate.jdbc.batch_size}")
private int batchSize;
public <T extends Parent> Collection<T> bulkSave(Collection<T> entities) {
final List<T> savedEntities = new ArrayList<T>(entities.size());
int i = 0;
for (T t : entities) {
savedEntities.add(persistOrMerge(t));
i++;
if (i % batchSize == 0) {
// Flush a batch of inserts and release memory.
entityManager.flush();
entityManager.clear();
}
}
return savedEntities;
}
private <T extends Parent> T persistOrMerge(T t) {
if (t.getTimeSlotId() == null) {
entityManager.persist(t);
return t;
} else {
return entityManager.merge(t);
}
}
application.yml
application.yml
spring:
application:
name: sample-service
jpa:
database: MYSQL
show-sql: true
hibernate:
ddl-auto: update
dialect: org.hibernate.dialect.MySQL5Dialect
naming_strategy: org.hibernate.cfg.ImprovedNamingStrategy
jdbc:
batch_size: 100
jackson:
date-format: dd/MM/yyyy
thymeleaf:
cache: false
spring.datasource.url : jdbc:mysql://${dbhost}/sample?createDatabaseIfNotExist=true
spring.datasource.username : root
spring.datasource.password : root
spring.datasource.driver-class-name : com.mysql.cj.jdbc.Driver
推荐答案
要启用批量插入，您需要配置中具有的batch_size属性.
To enable batch insert you need the batch_size property which you have in your configuration.
此外，由于jdbc批处理只能定位一个表，因此您需要spring.jpa.hibernate.order_inserts=true属性来对父级和子级之间的插入进行排序，否则该语句是无序的，并且您将看到部分批处理(新的批处理在任何时候以不同的方式插入表被称为)
Also since a jdbc batch can target one table only you need the spring.jpa.hibernate.order_inserts=true property to order the insert between parent and child or else the statement are unordered and you will see a partial batch (new batch anytime an insert in a different table is called)
这篇关于Spring Boot JPA批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
