我正在使用Spring Boot和Hibernate创建RESTful API,用于为一个简单的Web应用程序读取.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
我做错什么了吗?我如何提高数据插入的性能?我是否错误地理解了saveAll()方法?我的问题与这里描述的非常相似:Spring boot 2升级 - spring boot data jpa saveAll()非常缓慢
JdbcTemplate
在不先将它们转换为对象的情况下执行批量插入。 - Jens Schauder