处理Spring JdbcTemplate batchUpdate时出错 [英] Error Handling Spring JdbcTemplate batchUpdate

问题描述

我正在使用batchUpdate更新表中的数千行。我的要求是：

I am trying to update thousands of rows in a table using batchUpdate. My requirements are:

1）假设一批中有1000条记录。记录号235造成错误。如何找出哪个记录​​导致错误。

1) Assume there are 1000 records in a batch. Record No 235 caused an error. How do I find out which record caused the error.

2）假设记录600没有导致更新（原因可能是没有匹配where子句的记录）。如何找到没有导致更新的记录。

2) Assume that record 600 did not result in an update (reason could be no record matching the where clause). How can I find out records that did not result in an update.

3）在上述两种情况下，如何继续处理剩余的记录。

3) In both scenarios above how can I continue processing the remaining records.

推荐答案

长时间搜索和调试后唯一的解决方法是去BatchUpdateException类，
找到负数元素从MAP推导出错误的插入值。

The only solution after long search and debug is to go to BatchUpdateException class and find the negative element and deduce the value of the insertion that is in error from the MAP.

import java.sql.BatchUpdateException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;


import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;


@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
@Repository("dao_")
public class YouDao extends CommunDao implements IyouDao {

    public void bulkInsert(final List<Map<String, String>> map)
            throws BusinessException { 
        try {

            String sql = " insert into  your_table " + "(  aa,bb  )"
                    + "values " + "(  ?,? )";
            BatchPreparedStatementSetter batchPreparedStatementSetter = new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i)
                        throws SQLException {
                    Map<String, String> bean = map.get(i);

                    ps.setString(1, bean.get("aa"));
                    ps.setString(2, bean.get("bb")); 
                    //..
                    //..

                }

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

             getJdbcTemplate().batchUpdate(sql, batchPreparedStatementSetter);

        }

        catch (Exception e) {
            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 " + map.get(index));
                        }
                    }
                }
            }  
            throw new BusinessException(e);
        }

    }

}

这篇关于处理Spring JdbcTemplate batchUpdate时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！