Batch Insert with jdbcTemplate and BatchPreparedStatementSetter

[oakdeveloper]

Hi,

I have a question on batch operations using JdbcTemplate and BatchPreparedStatementSetter. In the code given here, all the 2000 records are inserted in a single batch operation. I don't want to insert all the 2000 records at one go. Instead, I want to insert in batches of 10 records. How do I achieve that? I'm brand new to Spring Framework. Please help me..

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    final int count = 2000;
    final List<String> firstNames = new ArrayList<String>(count);
    final List<String> lastNames = new ArrayList<String>(count);
    for (int i = 0; i < count; i++) {
      firstNames.add("First Name " + i);
      lastNames.add("Last Name " + i);
    }
    jdbcTemplate
        .batchUpdate(
            "insert into customer (id, first_name, last_name, last_login, comments) values (?, ?, ?, ?, ?)",
            new BatchPreparedStatementSetter() {
              public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setLong(1, i + 10);
                ps.setString(2, firstNames.get(i));
                ps.setString(3, lastNames.get(i));
                ps.setNull(4, Types.TIMESTAMP);
                ps.setNull(5, Types.CLOB);
              }
              public int getBatchSize() {
                return count;
              }
            });

[esammer]

I believe a call to batchUpdate() corresponds to exactly one batch. If you want to chunk your batches into something smaller than the set size, you'd have to loop over records and issue a batchUpdate() for each group of 10. In that case, your getBatchSize() becomes <=10 with the final iteration probably being less than 10.

Hope that helps.