Batch loading to the database – Part 2 – SimpleJdbcInsert

As mentioned in my last post, we will now have a a method foe insertion of a delimited file into a database that does not involve our DBA’s.

In Spring 2.5, the SimpleJdbcInsert class was added making this task especially easy, and the added bonus here is we see the elegance of Groovy when combined with this class.

Here is the code.

   1: class DBInsert {   

   2:     

   3: DataSource ds;   

   4: SimpleJdbcInsert jdbcInserter;   

   5: int numRowsPerBatchInsert = 500;   

   6: int numRowsProcessed = 0;   

   7: List fieldNames = ['Column1', 'Column2', 'Column3']   

   8: List<Map<String, Object>> rowsToInsert = [];   

   9: java.sql.Date today;   

  10: final static String SchemaName = "myschema";   

  11:     

  12: public void init() {   

  13:   jdbcInserter = new SimpleJdbcInsert(ds).withSchemaName(SchemaName).withTableName("myTable");   

  14:   today = new java.sql.Date(cal.getTimeInMillis())   

  15: }   

  16:     

  17: /**   

  18:  * We assume that the row is delimited by commas.   

  19:  * This can be refactored to take some other delimiter as second parameter   

  20:  */   

  21: public void insertRow(String s) {   

  22:   if (numRowsProcessed++ > 0) {   

  23:     def rowAsList = s.split(",")   

  24:     

  25:     def iterator = rowAsList.iterator()   

  26:     Map<String, Object> insertMap = ['eff_dte': today]   

  27:     fieldNames.collect {   

  28:       insertMap[it] = iterator.next().trim()   

  29:     }   

  30:     rowsToInsert += insertMap;   

  31:     if (numRowsProcessed % numRowsPerBatchInsert == 0) {   

  32:       completeInsert();   

  33:     }   

  34:   }   

  35: }   

  36:     

  37: public void completeInsert() {   

  38:   jdbcInserter.executeBatch((Map<String, Object>[]) rowsToInsert.toArray());   

  39:   rowsToInsert.clear();   

  40: }   

  41: }

Right off the bat, one nice thing we have in groovy is the removal of the need for getters and setters, automatically making the file smaller. But beside that, lines 26-29 have the concise, yet very readable Groovy closure that takes the column names and creates a map between them and values of the specific line. And this little class gives us what we need with barely any parsing and NO sql.