Insert batch and Return full object using NamedParameterJdbcTemplate

Question

I am trying to do batch insertion to a table and then read full objects back with their newly generated ids.

    private List<Customer> saveCustomer(List<Customer> customerList, Long shopId) {
        AtomicInteger index = new AtomicInteger();
        SqlParameterSource[] paramsArray = new MapSqlParameterSource[customerList.size()];


        for (Customer customer : customerList) {
            MapSqlParameterSource params = new MapSqlParameterSource();
            params.addValue("shop_id", shopId);
            params.addValue("customer_name", pallet.getName());
            params.addValue("email", pallet.getEmail());
            params.addValue("contact_number", pallet.getContactNumber());
            paramsArray[index.getAndIncrement()] = params;
        }

        String sql =
                "INSERT INTO \"Customer\" " +
                        "(shop_id, customer_name, email, contact_number) " +
                        "VALUES (:shop_id, :customer_name, :email, :contact_number) " +
                        "RETURNING id, shop_id, customer_name, email, contact_number ";

        return namedParameterJdbcTemplate.getJdbcOperations().query(sql, paramsArray, new CustomerRowMapper());

    }

However, this method gives me following error: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of org.springframework.jdbc.core.namedparam.MapSqlParameterSource. Use setObject() with an explicit Types value to specify the type to use. See stack trace below.

PreparedStatementCallback; bad SQL grammar [INSERT INTO "Customer" (shop_id, customer_name, email, contact_number) VALUES (:shop_id, :customer_name, :email, :contact_number) RETURNING id, shop_id, customer_name, email, contact_number ]; nested exception is org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of org.springframework.jdbc.core.namedparam.MapSqlParameterSource. Use setObject() with an explicit Types value to specify the type to use.
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO "Customer" (shop_id, customer_name, email, contact_number) VALUES (:shop_id, :customer_name, :email, :contact_number) RETURNING id, shop_id, customer_name, email, contact_number  ]; nested exception is org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of org.springframework.jdbc.core.namedparam.MapSqlParameterSource. Use setObject() with an explicit Types value to specify the type to use.
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:763)

Everything would be fine if I just wanted to do batch insertion without reading it back. Then I would use

namedParameterJdbcTemplate.batchUpdate(sql, paramsArray);

However, I also need to read inserted values back with their ids but not sure what namedParameterJdbcTemplate method I can use.

TLDR: I want to do batch insertion and then read inserted rows back using namedParameterJdbcTemplate but cannot find the right method for this. Does namedParameterJdbcTemplate provide batch insertion and selection in a single method?

Answer 1

As I can see from the methods of namedParameterJdbcTemplate you can't execute batch operation and waiting for something back. What you can do is to execute statement in 1 sql request. Just combine your values if your database supports such syntax:

INSERT INTO Customer (shop_id, customer_name, email, contact_number)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

Then just use JDBCTemplate.update with the GeneratedKeyHolder argument. This might help you: identity from sql insert via jdbctemplate

Answer 2

I got something to work.

private List<Customer> saveCustomer(List<Customer> customerList, Long shopId) {
    List<Object[]> batch = customers.stream()
        .map(customer -> new Object[] {shopId, customer.getName(), customer.getEmail(), customer.getContactNumber()})
        .toList();

    String sql = "INSERT INTO \"Customer\" " +
                 "(shop_id, customer_name, email, contact_number) " +
                 "values :batch" +
                 "RETURNING id, shop_id, customer_name, email, contact_number ";

    return namedParameterJdbcTemplate.query(sql,
        new MapSqlParameterSource("batch", batch),
        new CustomerRowMapper());
}

Would love to know if there's a better way

Note: each element of each Object[] is a parameter getting passed and there's a hard cap of 65535 parameters which can be passed at once

Edit:

I've made an improvement to this solution, if you instead convert each column from your pojo to a java.sql.Array you can do

insert into customer (store_id, customer_name, ...)
select * from unnest(:storeIds, :customer_names, ...)
returning ...

This solution is more performant doesn't run into the parameter cap