java - 处理来自 BatchUpdateException 的多个错误

Question

我正在使用批处理在数据库中插入大量记录。如果其中任何一个失败，我只会在更新计数数组中获得记录号(和最后一条异常消息)。

是否有可能得到所有错误的原因？

我正在使用 Java7 和 Mysql 连接器 (5.1.35)。我还将自动提交设置为 false。

Answer 1

是否有可能得到所有错误的原因？

是的，这是可能的。

BatchUpdateException is thrown when an error occurs during a batch update operation. It provides (among others) the update counts for all commands that were executed successfully during the batch update.

The BatchUpdateException class is a subclass of SQLException, which represents an exception that provides information on a database access error or other errors.

捕获 BatchUpdateException

BatchUpdateException 应该被 last catch 子句 捕获，因为它是 SQLException 的子类。您可以在它到达 SQLException 之前手动捕​​获它。

下面是示例代码

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class CatchExceptionExample {
    
    private static final String HOST = "jdbc:mysql://localhost/";
    private static final String DB = "db_test";
    private static final String USER = "root";
    private static final String PASSWORD = "bitnami";
    
    public static void main(String[] args) {
        String sqlQuery = "INSERT INTO `tbl` (`id`, `name`, `surname`, `age`) VALUES (?,?,?,?)";
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection(HOST+DB,USER,PASSWORD);
            PreparedStatement ps = conn.prepareStatement(sqlQuery);
            
            ps.setInt(1,6);
            ps.setString(2, "Name1");
            ps.setString(3, "Surname1");
            ps.setInt(4, 1);
            ps.addBatch();
            
            ps.setInt(1,1);
            ps.setString(2, "Name1");
            ps.setString(3, "Surname1");
            ps.setInt(4, 1);
            ps.addBatch();
            
            int[] affectedRecords = ps.executeBatch();
            
        } catch (BatchUpdateException ex) {
            int[] updateCount = ex.getUpdateCounts();
            
            int count = 1;
            for (int i : updateCount) {
                if  (i == Statement.EXECUTE_FAILED) {
                    System.out.println("Error on request " + count +": Execute failed");
                } else {
                    System.out.println("Request " + count +": OK");
                }
                count++;
                
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

有关详细级别的说明，请访问 How to resolve Batch Update Exception关联。也不要忘记阅读本文的更多关于 BatchUpdateException 部分。