如何在PostgreSQL中使用JDBCTemplate batchUpdate捕获错误记录?

问题描述

我正在使用Spring JDBCTemplate和BatchPreparedStatementSetter在postgreSql DB上执行批处理更新.我想捕获错误的记录，并经过几篇文章后，发现捕获BatchUpdateException然后寻找"Statement.EXECUTE_FAILED"可以帮助我识别错误的记录.但是，当我按如下所示实现它时，我再也没有遇到batchUpdate异常.

I am using Spring JDBCTemplate and BatchPreparedStatementSetter to perform batch Update on a postgreSql DB. I wanted to capture the erroneous records and after going through some posts, found out that catching the BatchUpdateException and then looking for 'Statement.EXECUTE_FAILED' could help me identify the records that were erroneous. However, when I implement it as below, I never get a batchUpdate exception.

在这里，我尝试输入相同的ID"120"反复，这样我得到一个独特的约束违例，以强制在数据库级别上出现异常.

Here I am trying to enter the same id "120" repeatedly so that I get a unique constraint violation to force an exception at db level.

@Override
@Transactional
public void batchInsertTenantPayloads(List<Payload> payloadMessages) {

    try {
        jdbcTemplate.batchUpdate(DBQueryConstants.INSERT_INTO_MESSAGE_METERING_PAYLOAD, new BatchPreparedStatementSetter() {

            @Override
            public int getBatchSize() {
                return payloadMessages.size();
            }

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {

                ps.setLong(1, 120);
                ps.setString(2, payloadMessages.get(i).getId());
                ps.setDate(3, Date.valueOf(payloadMessages.get(i).getTs().toLocalDate()));
                ps.setString(4, payloadMessages.get(i).getContent().getId());
                ps.setInt(5, payloadMessages.get(i).getStatus().ordinal());
                ps.setString(6, MyUtils.toJSON(payloadMessages.get(i)));
            }
        });

    } catch (Exception e) {
        if (e.getCause() instanceof BatchUpdateException) {
            LOGGER.info("Batch exception occurred");
            BatchUpdateException be = (BatchUpdateException) e.getCause();
            int[] batchUpdateCounts = be.getUpdateCounts();
            for (int index = 0; index < batchUpdateCounts.length; index++) {
                if (batchUpdateCounts[index] == Statement.EXECUTE_FAILED) {
                    LOGGER.error(
                            "[MMC] batch update Error execution >>>>>>>>>>>" + index + " --- , codeFail : " + batchUpdateCounts[index]
                                    + "---, line " + payloadMessages.get(index));
                }
            }

        }

    }

}

挑战在于，我永远不会进入BatchUpdateException块.相反，我得到一个

The challenge is that I never enter the BatchUpdateException block.Instead I get an

嵌套的异常是

我可以做些什么不同，以便我可以捕获batchUpdate异常并仅获取错误行并提交正确的行.

What can I do differently so that I can capture the batchUpdate exception and get just the error rows and commit the good ones.

我的批处理大小是500.Postgre版本是9.6，我正在使用带有spring jdbc 5.1.3的spring 2.0.

My batch size is 500. Postgre version is 9.6 and I am using spring 2.0 with spring jdbc 5.1.3.

stacktrace看起来像这样

The stacktrace looks something like this

org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [insert into message_metering_payload(id,tenant_name,source_dt,exchange_id,status,payload)values(?,?,?,?,?,?)]; ERROR: duplicate key value violates unique constraint "message_metering_payload_pkey"
  Detail: Key (id)=(120) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "message_metering_payload_pkey"
  Detail: Key (id)=(120) already exists.
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:242)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1444)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:632)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:646)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:936)

推荐答案

您未获得BatchUpdateException，因为您可能会使用jdbcTemplate中的rel =" nofollow noreferrer> SQLErrorCodeSQLExceptionTranslator ，用于处理BatchUpdateException s 在一种特殊的方式:

You do not get BatchUpdateException, because you might use SQLErrorCodeSQLExceptionTranslator in jdbcTemplate, which handles BatchUpdateExceptions in a special way:

if (sqlEx instanceof BatchUpdateException && sqlEx.getNextException() != null) {
    SQLException nestedSqlEx = sqlEx.getNextException();
    if (nestedSqlEx.getErrorCode() > 0 || nestedSqlEx.getSQLState() != null) {
        sqlEx = nestedSqlEx;
    }
}

有一个问题:

• jdbcTemplate.batchUpdate不返回BatchUpdateException的计数器

如果使用 SQLStateSQLExceptionTranslator :

jdbcTemplate.setExceptionTranslator(new SQLStateSQLExceptionTranslator());

然后您将获得BatchUpdateException作为cause:

try {
    // ...
} catch (DataAccessException e) {
    Throwable cause = e.getCause();
    logger.info("cause instanceof BatchUpdateException = {}", cause instanceof BatchUpdateException);
 }

但是请注意，如果使用Postgresql jdbc驱动程序，则BatchUpdateException#getUpdateCounts()将包含 EXECUTE_FAILED ，尽管实际上可以成功插入某些行.

But note that in case of postgresql jdbc driver BatchUpdateException#getUpdateCounts() will contain EXECUTE_FAILED only, despite the fact that some row could be inserted successfully.

请参见此问题

这篇关于如何在PostgreSQL中使用JDBCTemplate batchUpdate捕获错误记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！