批量插入spring jdbc模板识别失败的记录

问题描述

我正在使用spring jdbc模板将数据批量插入数据库.我可以进行肯定插入，但是可以坚持使用以识别失败记录，因为我有一种方案可以将失败记录插入错误表中，并且可以将肯定记录插入另一个表中.

I'm using spring jdbc template for bulk insert data to database. I'm able to do for positive insertion but stucking with to identify the failed records as I have a scenario to insert failed records into a error table, and positive records into another table.

找到下面正在尝试的代码.**

Find the below code which is am trying.**

final int batchSize = 5;
            int[] resultCount = null;
            for (int j = 0; j < bulkDataList.size(); j += batchSize) {
                final List<BulkTest> batchList = bulkDataList.subList(j, j + batchSize > bulkDataList.size() ? bulkDataList.size() : j + batchSize);
                try{
                     resultCount = jdbcTemplate.batchUpdate("INSERT INTO BULK_TEST VALUES (?, ?)",
                            new BatchPreparedStatementSetter() {
                                @Override
                                public void setValues(PreparedStatement ps, int i)
                                        throws SQLException {
                                    BulkTest bulkObj = batchList.get(i);  ps.setString(1, bulkObj.getRowNo());
                                    ps.setString(2, bulkObj.getName());
                                }
                                @Override
                                public int getBatchSize() {
                                    return batchList.size();
                                }
                            });
                }catch (Exception e) {
                    e.printStackTrace();
                    logger.info("Exception occured in batch update ==>");
                    if (e.getCause() instanceof BatchUpdateException) {
                        BatchUpdateException be = (BatchUpdateException) e.getCause();
                        int[] batchRes = be.getUpdateCounts();
                        if (batchRes != null && batchRes.length > 0) {
                            for (int index = 0; index < batchRes.length; index++) {
                                if (batchRes[index] == Statement.EXECUTE_FAILED) {
                                    logger.error("Error execution >>>>>>>>>>>"
                                            + index + " --- , codeFail : " + batchRes[index]
                                            + "---, line " + bulkDataList.get(index));
                                }
                            }
                        }
                    }
                }
            }
            logger.info("Final processed results ==>"+resultCount);
    }

**

1)在批量批处理更新操作中，如果获取空指针或任何数据库约束异常，则会抛出Exception类而不是BatchUpdateException.

1) In bulk batch update operation, if am getting null pointer or any db constraint exception it's throwing Exception class not BatchUpdateException.

我从BatchUpdateException知道我们可以获取失败的记录.

I know from BatchUpdateException we can get the failed records.

请共享代码以识别成功插入和失败的记录.

Please share the code to identify the successfully inserted and failed records.

推荐答案

下面的代码从批处理更新中给出失败的userId.有关更多详细信息，请参见jdbcTemplate.batchUpdate()的内部实现.

Below code gives failed userId from batch update. See internal implementation of jdbcTemplate.batchUpdate() for more details.

//usersResponse is list of userDetails.

Map<String, Object> failedBatchIndexMap = new HashMap<>();try {
this.jdbCtemplate.batchUpdate("insertQuery", batchValues.toArray(new Map[usersResponse.size()]));}catch (DataAccessException e) {
/*
 * batch updates count
 */
int[] updateCounts = ((BatchUpdateException) e.getCause()).getUpdateCounts();

int count = 0;

for(int i : updateCounts) {
    if (i == Statement.EXECUTE_FAILED) {
        /*
         * putting failed batch details to map
         */
        failedBatchIndexMap.put("userId",new MapSqlParameterSource(batchValues.toArray(new Map[usersResponse.size()])[count]).getValue("userId"));
        logger.error("::::::::::::: Inside batchProcess(), User id {} failed ", new MapSqlParameterSource(batchValues.toArray(new Map[usersResponse.size()])[count]).getValue("userId"));
    }
    count++;
}
}

这篇关于批量插入spring jdbc模板识别失败的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！