使用NamedParameterJdbcTemplate插入批处理并返回完整对象

Question

我试图批量插入一个表，然后用新生成的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());

    }

然而，这个方法给了我以下错误：org。postgresql。util。PSQLException：无法推断用于org实例的SQL类型。springframework。jdbc。果心名字叫帕兰。MapSqlParameterSource。使用带有显式类型值的setObject（）指定要使用的类型。请参阅下面的堆栈跟踪。

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)

如果我只想批量插入而不回读，一切都会好起来的。那我就用

namedParameterJdbcTemplate.batchUpdate(sql, paramsArray);

但是，我还需要用插入的值的ids读回它们，但不确定我可以使用什么namedParameterJdbcTemplate方法。

TLDR：我想批量插入，然后使用namedParameterJdbcTemplate读取插入的行，但找不到正确的方法。namedParameterJdbcTemplate是否在单个方法中提供批量插入和选择？

Answer 1

从namedParameter JdbcTemboard的方法中可以看出，您不能执行批处理操作并等待返回。您可以做的是在1个sql请求中执行语句。如果您的数据库支持这样的语法，只需组合您的值：

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

然后只需使用jdbc模板。使用GeneratedKeyHolder参数更新。这可能会帮助您：通过jdbctemplate从sql插入标识