gpt4 book ai didi

mysql - Spring Boot JPA saveAll() 插入数据库非常慢

转载 作者:行者123 更新时间:2023-11-29 15:20:33 26 4
gpt4 key购买 nike

我将 Spring Boot 与 Hibernate 结合使用,为一个简单的 Web 应用程序创建 RESTful API,我在其中读取 .csv 文件并将每一行插入到 mysql 数据库表中。我能够成功 这样做,但对于任何大型 csv 文件来说都需要很长时间。我知道最好对插入语句进行批处理以减少数量 交易,但我认为我不会用我现有的代码来实现这一点。这就是我目前正在做的事情(有效但非常缓慢):

CsvUploaderController.java

public class CsvUploaderController {

// CsvUploader Repository definition:
// @Repository
// public interface CsvUploaderRepository extends JpaRepository<CityObj, Integer>
@Autowired
CsvUploaderRepository csvUploaderRepository;

// gets called by front end with the .csv file data
@PutMapping("/upload_csv")
public List<CityObj> uploadCsv(@RequestBody Object data) {
// unpackCSV converts an arrayList of Objects to a List of CityObj
List<CityObj> unpackedCSV = unpackCSV((ArrayList<Object>) data);
csvUploaderRepository.deleteAll(); // delete all rows in table currently. Not sure if this is relevent to issue

// save all items as rows in my database
return csvUploaderRepository.saveAll(unpackedCSV); // this is where it takes forever to complete
}
...
}

application.properties:

spring.datasource.url=jdbc:mysql://url.to.my.database
spring.datasource.username=myusername
spring.datasource.password=weirdpassword
spring.datasource.hikari.maximumPoolSize = 5

spring.jpa.properties.hibernate.generate_statistics=true
spring.jpa.properties.hibernate.jdbc.batch_size=20 // I've tried playing around with different values. Hasnt helped
#spring.jpa.properties.hibernate.order_inserts=true // I've also tried using this but not sure what it does and didnt help

我做错了什么?如何提高 Blade 的性能?我对 saveAll() 的理解是否错误?我的问题与此处描述的非常相似:Spring boot 2 upgrade - spring boot data jpa saveAll() very slow

最佳答案

使用JdbcTemplate,速度更快。

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;

public int[] batchInsert(List<Book> books) {

return this.jdbcTemplate.batchUpdate(
"insert into books (name, price) values(?,?)",
new BatchPreparedStatementSetter() {

public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setString(1, books.get(i).getName());
ps.setBigDecimal(2, books.get(i).getPrice());
}

public int getBatchSize() {
return books.size();
}

});
}

关于mysql - Spring Boot JPA saveAll() 插入数据库非常慢,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/59401046/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com