Batching in MYSQL

In a scenario where you have to do multiple inserts in database with the same sql statement but with different data, batching them in a single SQL query to database saves of time exponentially.

In mysql using executeBatch() solves problem of batching. In case if you use JDBCTemplate then use batchUpdate() which internally calls executeBatch only.
Here is a example code

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
 
public class MySqlUpdate {
 
private static Connection getConnection() throws Exception {
String driver = "org.gjt.mm.mysql.Driver";
String url = "jdbc:mysql://localhost/test";
String usrname = "root";
String passwd = "";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, usrname, passwd);
return conn;
}
 
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConnection();
conn.setAutoCommit(false);
String query = "insert into test(name, desg) values(?, ?)";
ps = conn.prepareStatement(query);
ps.setString(1, "ABC");
ps.setString(2,"Software Engineer");
ps.addBatch();
 
ps.setString(1, "XYZ");
ps.setString(2,"Sr. Software Engineer");
ps.addBatch();
 
ps.setString(1, "PQR");
ps.setString(2,"Team Leader");
ps.addBatch();
 
int[] updateCounts = ps.executeBatch();
System.out.println("Length:" + updateCounts.length);
conn.commit();
} catch (BatchUpdateException e) {
try {
conn.rollback();
} catch (Exception e2) {
e.printStackTrace();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pstmt.close();
conn.close();
}
}
}  

But this does not solve our problem.Doing this will only send all the insert queries in a together to the database and executing them one by one as independent queries.This will only save us the time in roundtrip to database.

To get the real benefit from the batching we should set rewriteBatchStatements=true in the database connection url we use to make connection .
As shown here
"jdbc:mysql://localhost/db?user=root&rewriteBatchStatements=true"

This will activate the batch mode and now multiple queries will be executed as a single query.But this works only when the number of queries is more then 3.

you must be careful about writing the query statement as well as any extra/ less space will make batch ineffective. So be sure that the query syntax is perfect. For example

"insert IGNORE into rosters (owner_jid,jid,name) values (?,?,?)" works but

"insert IGNORE into rosters(owner_jid,jid,name) values (?,?,?)" or
"insert IGNORE into rosters (owner_jid,jid,name)values (?,?,?)" or
"insert IGNORE into rosters (owner_jid,jid,name) values(?,?,?)" does not work.

If you find out the reason why it does not work with irregular spaces, please comment.