处理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.

推荐答案

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时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！