CODEWITHSUNDEEP
javamysqlsql
membersound
membersound

Reputation: 86657

How to batch insert mysql LOAD DATA?

I have a list of absolute filenames (csv files). I want to batch insert them using LOAD DATA LOCAL INFILE.

Problem: I'm not able to collect the sql statements in batches before executing them as batch:

PreparedStatement ps = con.prepareStatement("LOAD DATA LOCAL INFILE ? INTO TABLE mytable");

for (String filename : filenames) {
    ps.setString(1, filename);
    ps.addBatch();
}

ps.executeBatch();
ps.commit();

Result:

Error during commit of 1 files: Parameter index out of range (1 > number of parameters, which is 0)

The sql wildcard parameter is not recognized. But how can I then get the absolute filename it the statement, if not with a ? wildcard?

Upvotes: 0

Views: 435

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

I don't think prepared statements support LOAD DATA as you have in mind. As far as I know, only literal values in queries may be represented by parameters. But you may use ordinary string concatenation here:

String sql1 = "LOAD DATA LOCAL INFILE '";
String sql2 = "' INTO TABLE mytable";

Statement s = con.createStatement();    // use a regular statement
for (String filename : filenames) {
    String query = sql1 + filename + sql2
    s.addBatch(query);
}

s.executeBatch();
s.commit();

Upvotes: 2

Related Questions