CODEWITHSUNDEEP
javamysqlsql
Varuzhan Stepanyan
Varuzhan Stepanyan

Reputation: 97

Java SQL 1 million rows

I must add 1.000.000 rows in mySQL with Java, but the process is quite long․ I have this code:

for(int number=45000000;number<46000000;number++){

query="insert into free(number) values ("+"'"+number+"');";
try {
    stmt.executeUpdate(query);
    System.out.println(number +" added");
} catch (SQLException e) {
    e.printStackTrace();
}

How to accelerate the process? (40 min = 100.000 rows) Any ideas?

Upvotes: 2

Views: 1636

Answers (1)

Elliott Frisch
Elliott Frisch

Reputation: 201429

You could use a PreparedStatement and batching. Something like,

String query = "insert into free(number) values (?)";
PreparedStatement ps = null;
try {
    ps = conn.prepareStatement(query);
    for (int number = 45000000; number < 46000000; number++) {
        ps.setInt(1, number);
        ps.addBatch();
        if ((number + 1) % 100 == 0) { // <-- this will add 100 rows at a time.
            ps.executeBatch();
        }
    }
    ps.executeBatch();
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    if (ps != null) {
        try {
            ps.close();
        } catch (SQLException e) {
        }
    }
}

Upvotes: 2

Related Questions