Spring boot JPA: Use library to Bulk load data for MySQL

Question

I'm fairly new to Spring boot.

I'm currently writing a small microservice which will bulk load data from a CSV file into a MySQL database table. I understand that there are many ways to achieve this, but one of the ways I was thinking was to use the MySQL LOAD DATA FILE command to parse the csv file and load the data into the database table.

I was wondering if anyone knows of a way that I can invoke the MySQL LOAD DATA command using the Spring boot JPA library ?

Any help/ advice is appreciated.

Thanks

I think this is a duplicate: stackoverflow.com/questions/10866454/… — geco17
– geco17, Commented Mar 10, 2018 at 13:55
Thanks @WilliamBurnham but this did not help with my solution. — rm12345
– rm12345, Commented Mar 12, 2018 at 14:15
I also got the same issue. I tried doing the following: @Query (value="LOAD DATA LOCAL INFILE 'C:/Users/tester/Documents/transaction_data.txt' INTO TABLE tbl_fin FIELDS TERMINATED BY ',' IGNORE 1 LINES", nativeQuery = true) public void bulkLoadData(); However I get the following error. java.sql.SQLException: ResultSet is from UPDATE. No Data. Looking this error up on google, forums have said that this is because the query is not returning data and it should be placed in a stored procedure but MySQL does not allow LOAD DATA commands in a stored procedure. Any thoughts? — rm12345
– rm12345, Commented Mar 12, 2018 at 14:22
I see you've already solved it, I was going to suggest when hibernate does something strange, I've had luck before using p6spy to intercept the queries (to at least see exactly what gets sent to the db). Check it out if you're not already aware. — geco17
– geco17, Commented Mar 12, 2018 at 20:13

rm12345 · Accepted Answer · 2018-03-12 14:43:39Z

4

Found a solution to the problem:

Add the @Modifying and @Transactional annotations to your method in the Repository. Here is an example:

@Repository
public interface ExpenseRepository extends CrudRepository<Expense, Integer>{

    @Modifying
    @Transactional
    @Query (value="LOAD DATA LOCAL INFILE 'C:/Users/Tester/Documents/transaction_data.txt' INTO TABLE tbl_fin FIELDS TERMINATED BY ',' IGNORE 1 LINES", nativeQuery = true)
    public void bulkLoadData();

}

answered Mar 12, 2018 at 14:43

rm12345

1,2894 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simon Auger · Accepted Answer · 2019-08-01 09:52:00Z

1

If you have the following error

java.sql.SQLSyntaxErrorException: The used command is not allowed with this MySQL version

Do not forget to allow load local file because it is disabled by default. Add to spring.datasource.url: &allowLoadLocalInfile=true and also enable it in your local MySQL Configuration with the command: SET GLOBAL local_infile = 1;

edited Aug 1, 2019 at 9:52

answered Jun 24, 2019 at 7:23

Simon Auger

215 bronze badges

Collectives™ on Stack Overflow

Spring boot JPA: Use library to Bulk load data for MySQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related