2

The followings works, but how can I collect multiple MapSqlParameterSource and insert them all in one batch?

new SimpleJdbcInsert(ds).withTableName(TABLENAME);

MapSqlParameterSource entry = new MapSqlParameterSource()
    .addValue("id", report.queryId, Types.INTEGER)
    .addValue("firstname", report.reportDate, Types.DATE)
    .addValue("age", report.completionRatio, Types.INTEGER);

insert.execute(entry);
membersound
  • 81,582
  • 193
  • 585
  • 1,120
  • @downvote: I don't know why this should not be a viable question. Especially, as seen below, there is exactly a solution to that question... – membersound Mar 13 '18 at 09:20

2 Answers2

2

Luckily SimpleJdbcInsert can take an array (not a list) of MapSqlParameterSource. So it's possible as follows:

List<MapSqlParameterSource> entries = new ArrayList<>();
entries.add(entry);

MapSqlParameterSource[] array = entries.toArray(new MapSqlParameterSource[entries.size()]);
insert.executeBatch(array);
membersound
  • 81,582
  • 193
  • 585
  • 1,120
1

There is a better way of doing it with SqlParameterSourceUtils

private final List<Map<String, Object>> records = new LinkedList<>();

final SimpleJdbcInsert statement = new SimpleJdbcInsert(dataSource)
        .withTableName("stats")
        .usingGeneratedKeyColumns("id")
        .usingColumns("document", "error", "run", "celex");

      statement.executeBatch(SqlParameterSourceUtils.createBatch(records));
Aleksandr Barmin
  • 71
  • 6
  • Note that executeBatch does not return id's. It returns 0/1 depending on insert-success. You can get id's but you have to use JDBC/JdbcTemplate. See https://stackoverflow.com/questions/7333524/how-can-i-insert-many-rows-into-a-mysql-table-and-return-the-new-ids/69365101#69365101 – Curtis Yallop Sep 28 '21 at 16:25