Spring boot - Jdbc batch insert with returning ids

Question

Assume we have a table users

id | name
_________
1  | name1
2  | name2

I can insert a record into users table and get inserted id using the following ways.

tried inserting record using SimpleJdbcInsert and getting the inserted id using executeAndReturnId() method.
also tried inserting and getting the inserted id by using the returning clause and using queryForObject()

query = INSERT INTO users(name) VALUES('name3') RETURNING id;
insertedId = jdbcTemplate.queryForObject(query, Long.class);

For Batch Insert:

query = INSERT INTO users(name) VALUES(?) RETURNING id;
List<Object[]> objects = new ArrayList<Object[]>(new Object[]{'name3'}, new Object[]{'name4'});
int[] rowsAffected = jdbcTemplate.batchUpdate(query, objects); // this only return affected rows in array

How to get the all inserted ID's using batch inserting?

tried already asked question also..not helpful :(

Somebody seemed to have figured out a workaround for your scenario here https://stackoverflow.com/a/74296674/3794466 but haven't tried it yet myself... — Bernie Lenz, Dec 08 '22 at 03:18

score 0 · Answer 1 · edited Dec 10 '19 at 23:19

0

Try using int[] instead of int[][]

edited Dec 10 '19 at 23:19

Dharman

30,962
25
85
135

answered Dec 10 '19 at 19:01

Harun Cetin

81
8

for that, we need to use another batchUpdate() declaration which contains ParameterizedPreparedStatementSetter. it is used to get batch-wise rows affected count. – jagadesh Dec 10 '19 at 19:10

score 0 · Answer 2 · answered Jul 28 '23 at 16:27

The underlying issue here is that JDBC distinguishes between updates (SQL statements which return just the number of affected rows) and queries (SQL statements returning a set of rows, which end up in a ResultSet), and allows batching (i.e. sending multiple statements to the server at once) only for updates, not for queries.

INSERT ... RETURNING ... belongs to the "query" category, and thus can't be batched.

A possible workaround is mentioned in this answer to "Insert batch and Return full object using NamedParameterJdbcTemplate" – it's basically constructing a new SQL statement with many lines in the VALUES clause for each call. If you are calling this with batches of many different sizes, your DB optimizer will see many different statements, and might fail to optimize it properly (or at least, has to compile it again each time).

I found this old post Batching Select Statement with JDBC, which elaborates several options here, and suggests an approach named there "select batching", where prepared statements of several sizes are set up, and then sent several of them to the DB with the data.

Because I just also had this problem, I've taken this approach, used spring's NamedParameterJdbcTemplate and build a generic class which puts together these queries based on some template strings.

Full code is in this PR I just opened (to my library which needs it).

It can then be used like this (using the example from the question):

QueryStatementBatcher<Integer> batcher = new QueryStatementBatcher<>(
        "INSERT INTO users (name) VALUES ", "(:name#)", " RETURNING id",
        (row, n) -> row.getInt("id"));

Stream<MapSqlParameterSource> inputs =
    Stream.of("Paŭlo Ebermann", "jagadesh", "Harun Cetin")
          .map(name -> new MapSqlParameterSource()
                          .addValue("name#", name));

List<Integer> generatedIds =
   batcher.queryForStream(jdbcTemplate, inputs)
          .collect(toList());

Of course, you'd create the batcher only once and reuse it (it doesn't have mutable state, so it is thread-safe, and computing the templates is a bit of effort at the beginning).

Spring boot - Jdbc batch insert with returning ids

2 Answers2