Use Java to insert large amount of data


Enjoyed this post? Tell others about it! DeliciousDelicious | twitterTwitter | redditReddit | diggDigg

Sometimes you need a fast way to insert large amount of data in a mysql database. One way to do that is to use a PreparedStatement and addBatch. But there can be a pittfall if you don't controll your database commits. Here's a example :
Class.forName("com.mysql.jdbc.Driver");
  
Connection con = DriverManager.getConnection("jdbc:mysql://" +  host + ":" + port + "/" + db, user, password);
String insertSQL = "INSERT INTO users (login, email, passwort_reset) VALUES (?,?,?)";
	
PreparedStatement insertStatement = con. prepareStatement(insertSQL);

for (User user : users)
{
  insertStatement.setString(1, user.name);
  insertStatement.setString(2, user.mail);
  insertStatement.setBoolean(3, true);
  insertStatement.addBatch();
}

con.setAutoCommit(false);
insertStatement.executeBatch();
con.commit();
con.setAutoCommit(true);
The reason why I disable in line 16 the autocommit is that I call in Line 17 executeBatch and hope that all my Data will insertet in the table. But mysql commits every inserts. And in that case the imports would be extremly slow. I have made some benchmark test by myself and saw an performance by 20000 inserts normally it would take 10 - 12 secs. and with disabled autocommit 3-5 secs. That's huge performance improvement. After you inserts don't forget to commit (line 18) so that you can unlock the users table.
Enjoyed this post? Tell others about it! DeliciousDelicious | twitterTwitter | redditReddit | diggDigg

blog comments powered by Disqus