Java – Batch Upload to Database

Usually I/O operations are costly. When I  tried to upload my huge csv file of 1.8 lakhs records one by one,  into a MySQL table , it took almost more than 30 minutes. And obviously it was not an acceptable result. So I had to resort  to batch uploading. On writing data as batches of 1000 records, everything was over in 30-40 seconds.

Lets see the code in detail.

The important thing to remember is that we need to turn off  “auto commit” mode. This means that if this mode is enable, every time record is pushed DB memory, it would automatically get written into tables, nullifying the effects of batch upload. At the same we have to enable it just before writing the records into table once enough number of records are pushed into DB memory or cache, using the commit().

In the the following example, records are read from a csv file named input.csv and its first three fields are written into tables called “batch” in the DB test. MySQL was DB of my choice.

At first auto commit mode is turned off by calling setAutoCommit(false) on DB connection object. Each record will be read and pushed them into DB cache using the addBatch(); 

When the we have 1000 records in the cache ie count variables becomes multiples of 1000, we need to write them into DB . For that we call following method, executeBatch(); Since we have disabled the auto commit mode, we need to enable that as well by calling commit()  on connection object in order to get this data written into the DB.

Gitub link here

BatcUpload.java

public class BatchUpload {

public static void main(String[] args) throws IOException, SQLException {

String line = “”;
String delimiter = “,”;
int count = 1;
DBConnector.createConnection();
Connection dbConn = DBConnector.getDBConnection();
System.out.println(dbConn);
PreparedStatement ps = DBConnector.getPSInstance();
dbConn.setAutoCommit(false);
String inputFile = “input.csv”;
BufferedReader br = new BufferedReader(new FileReader(inputFile));
while ((line = br.readLine()) != null) {
String[] entities = line.split(delimiter);
try {
ps.setString(1,entities[0]);
ps.setString(2,entities[1]);
ps.setString(3,entities[2]);

ps.addBatch();
if(count%1000==0){
ps.executeBatch();
dbConn.commit();
}
count++;

System.out.println(“Records are inserted into DBUSER table!”);

} catch (SQLException e) {

System.out.println(e.getMessage());

}

}

/*** To write the remaining records into DB*/

ps.executeBatch();
dbConn.commit();
dbConn.close();

}
}

DBConnector.java 

public class DBConnector {
private static final String DB_DRIVER = “com.mysql.jdbc.DRIVER”;
private static final String DB_CONNECTION = “jdbc:mysql://localhost:3306/test”;
private static final String DB_USER = “root”;
private static final String DB_PASSWORD = “root”;

private static Connection conn;
private static PreparedStatement ps;

public static void createConnection() {

// conn = null;
System.out.println(“asdasdfafds”);

try {

Class.forName(DB_DRIVER);

} catch(ClassNotFoundException cnf){
System.out.println(“Driver could not be loaded: ” + cnf);
}

try{
conn = DriverManager.getConnection(DB_CONNECTION, DB_USER,DB_PASSWORD);
String query = “INSERT INTO batch”
+ “(userID, username, address) VALUES”
+ “(?,?,?)”;

ps = conn.prepareStatement(query);
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

public static Connection getDBConnection() {

return conn;

}

public static PreparedStatement getPSInstance() {
return ps;

}
}