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

lztngnrs 于 2022-11-04 发布在 PostgreSQL

关注(0)|答案(2)|浏览(302)

我尝试对表进行批插入，然后用新生成的id读回完整的对象。

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: 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。请参阅下面的堆栈跟踪。

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);

但是，我还需要用它们的id读取插入的值，但不确定可以使用什么namedParameterJdbcTemplate方法。
TLDR：我想使用namedParameterJdbcTemplate进行批量插入，然后读取插入的行，但找不到正确的方法。namedParameterJdbcTemplate是否在一个方法中提供批量插入和选择？

postgresql

来源：https://stackoverflow.com/questions/64982766/insert-batch-and-return-full-object-using-namedparameterjdbctemplate

2条答案

按热度按时间

9jyewag01#

我还有事要做。

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());
}

我很想知道有没有更好的方法

**注意：**每个Object[]的每个元素都是要传递的参数，并且一次可以传递65535个参数的硬上限

赞(0）回复(0）举报 2022-11-04

iibxawm42#

正如我从namedParameterJdbcTemplate的方法中看到的，你不能执行批处理操作并等待返回。你能做的是在一个sql请求中执行语句。如果你的数据库支持这样的语法，只需合并你的值：

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

然后使用带有GeneratedKeyHolder参数的JDBCTemplate.update，这可能会对您有所帮助：identity from sql insert via jdbctemplate

赞(0）回复(0）举报 2022-11-04

我来回答

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

2条答案

相关问题

热门标签

最新问答