Master SpringBoot JDBC Batch Operations: Code Samples & Best Practices

Environment: SpringBoot 3.2.3

1. Prepare Environment

Create database table:

CREATE TABLE `t_person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

JavaBean object:

public class Person {
  private Integer id;
  private Integer age;
  private String name;
  // getter, setter
}

2. JDBC Batch Operations

Implement BatchPreparedStatementSetter and pass it to JdbcTemplate.batchUpdate:

public int[] batchSave2(final List<Person> persons) {
  return this.jdbcTemplate.batchUpdate(
    "insert into t_person (age, name) values (?, ?)",
    new BatchPreparedStatementSetter() {
      public void setValues(PreparedStatement ps, int i) throws SQLException {
        Person p = persons.get(i);
        ps.setInt(1, p.getAge());
        ps.setString(2, p.getName());
      }
      public int getBatchSize() {
        return persons.size();
      }
    });
}

Case 2: Using NamedParameterJdbcTemplate with SqlParameterSourceUtils.createBatch:

public int[] batchSave(List<Person> persons) {
  NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
  return template.batchUpdate(
    "insert into t_person (age, name) values (:age, :name)",
    SqlParameterSourceUtils.createBatch(persons));
}

Case 3: Splitting a large batch into smaller batches:

public int[][] batchSave3(final Collection<Person> persons) {
  int[][] ret = jdbcTemplate.batchUpdate(
    "insert into t_person (age, name) values (?, ?)",
    persons,
    10,
    (PreparedStatement ps, Person p) -> {
      ps.setInt(1, p.getAge());
      ps.setString(2, p.getName());
    });
  return ret;
}

3. SimpleJdbcInsert Batch Inserts

SimpleJdbcInsert simplifies configuration by using database metadata.

Case 1:

public void saveSimpleJdbc(Person person) {
  SimpleJdbcInsert insertPerson = new SimpleJdbcInsert(dataSource)
    .withTableName("t_person");
  Map<String, Object> parameters = new HashMap<>(3);
  parameters.put("age", person.getAge());
  parameters.put("name", person.getName());
  insertPerson.execute(parameters);
}

Case 2: Retrieve generated key:

public Number saveSimpleJdbc2(Person person) {
  SimpleJdbcInsert insertPerson = new SimpleJdbcInsert(dataSource)
    .withTableName("t_person")
    .usingGeneratedKeyColumns("id");
  Map<String, Object> parameters = new HashMap<>(3);
  parameters.put("age", person.getAge());
  parameters.put("name", person.getName());
  Number key = insertPerson.executeAndReturnKey(parameters);
  return key;
}

Case 3: Insert only specific columns:

public Number saveSimpleJdbc3(Person person) {
  SimpleJdbcInsert insertPerson = new SimpleJdbcInsert(dataSource)
    .withTableName("t_person")
    .usingColumns("name")
    .usingGeneratedKeyColumns("id");
  Map<String, Object> parameters = new HashMap<>(3);
  parameters.put("name", person.getName());
  Number key = insertPerson.executeAndReturnKey(parameters);
  return key;
}

Case 4: BeanPropertySqlParameterSource mapping:

public Number saveSimpleJdbc4(Person person) {
  SimpleJdbcInsert insertPerson = new SimpleJdbcInsert(dataSource)
    .withTableName("t_person")
    .usingGeneratedKeyColumns("id");
  SqlParameterSource parameters = new BeanPropertySqlParameterSource(person);
  Number key = insertPerson.executeAndReturnKey(parameters);
  return key;
}

Case 5: MapSqlParameterSource with explicit column mapping:

public Number saveSimpleJdbc5(Person person) {
  SimpleJdbcInsert insertPerson = new SimpleJdbcInsert(dataSource)
    .withTableName("t_person")
    .usingGeneratedKeyColumns("id");
  SqlParameterSource parameters = new MapSqlParameterSource()
    .addValue("age_1", person.getAge())
    .addValue("name", person.getName());
  Number key = insertPerson.executeAndReturnKey(parameters);
  return key;
}

4. Modeling JDBC Operations as Java Objects

MappingSqlQuery allows reusable queries; subclass must implement mapRow to convert each ResultSet row to an object.

public class PersonMappingQuery extends MappingSqlQuery<Person> {
  public PersonMappingQuery(DataSource ds) {
    super(ds, "select * from t_person");
    compile();
  }

  @Override
  protected Person mapRow(ResultSet rs, int rowNumber) throws SQLException {
    Person person = new Person();
    person.setId(rs.getInt("id"));
    person.setAge(rs.getInt("age"));
    person.setName(rs.getString("name"));
    return person;
  }
}

Usage:

public void query() {
  PersonMappingQuery query = new PersonMappingQuery(dataSource);
  List<Person> persons = query.execute();
  System.out.println(persons);
}

Conditional query example:

public PersonMappingQuery(DataSource ds) {
  super(ds, "select * from t_person where id = ?");
  declareParameter(new SqlParameter("id", Types.INTEGER));
  compile();
}

public void query() {
  PersonMappingQuery query = new PersonMappingQuery(dataSource);
  System.out.println(query.findObject(2427));
}

End of article.