CODEWITHSUNDEEP
javamysqloptimization
Takkun
Takkun

Reputation: 6361

Fastest way to insert thousands of rows into mysql database?

Can anyone suggest the fastest way to insert thousands/hundreds of thousands of rows into a mysql table? Right now I am using executeBatch, but I was wondering if there was a faster way to do it?

try {
        Connection con = dataSource.getConnection();
        Statement statement = con.createStatement();


        for(int i = 0; i < value; i++) {
            DateTime f = DateTime.now().minusDays(i % 28);
            String q1 = "INSERT INTO table1_test (txid, time, badgeid, event_type, sensorid, shift, unitid) VALUES ('"+i+"', '"+f.toString("yyyy-MM-dd HH:mm:ss")+"', 'NA', 'EN', '100', 'A', '1')";
            String q2 = "INSERT INTO table2_test (txid, time, badgeid, sensorid, shift, unitid) VALUES ('"+i+"', '"+f.toString("yyyy-MM-dd HH:mm:ss")+"', 'NA', '100', 'A', '1')";
            String q3 = "INSERT INTO table3_test (txid, badgeid, time, shift, compliant, sensorid, unitid) VALUES ('"+i+"', 'NA', '"+f.toString("yyyy-MM-dd HH:mm:ss")+"', 'A', 1, '100', '1')";
            statement.addBatch(q1);
            statement.addBatch(q2);
            statement.addBatch(q3);
        }
        statement.executeBatch();
        statement.close();
        con.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }

Upvotes: 0

Views: 780

Answers (1)

rduga
rduga

Reputation: 114

Try to use 'LOAD DATA INFILE' query type to import data into database. I think, it should be possible also with jdbc.

Upvotes: 4

Related Questions