java - 如何在批处理中获取包含异常的精确sql查询

Question

我想执行以下操作:

(从 PROCESSING DB 中提取的行传输到 PRESENTATION DB)

    PreparedStatement stmt;
    Statement st;
    Connection conn = ConnectionManager.getInstance().getConnection(ConnectionManager.PRESENTATION_DB);
    try {
        conn.setAutoCommit(false);
        stmt = conn.prepareStatement(
               "insert into customer (name,category,age) values (?,?,?)");  
                    stmt.clearParameters();
                    stmt.setString(1,"name2");
                    stmt.setString(2,"cat2");
                    stmt.setInt(3,25);
                    stmt.addBatch();
                    stmt.clearParameters();
                    stmt.setString(1,"name3");
                    stmt.setString(2,"cat3");
                    stmt.setInt(3,25);
                    stmt.addBatch();
                    stmt.clearParameters();
                    stmt.setString(1,"name4");
                    stmt.setString(2,"cat2");
                    stmt.setInt(3,25);
                    stmt.addBatch();
                    stmt.clearParameters();
                    stmt.setString(1,"name4");
                    stmt.setString(2,"cat2");
                    //stmt.setInt(3,25);

                    //this is parameter with false input
                    stmt.setString(3,"null"); 

                    stmt.addBatch();
                    stmt.clearParameters();
                    stmt.setString(1,"name5");
                    stmt.setString(2,"cat5");
                    stmt.setInt(3,25);
                    stmt.addBatch();
                    stmt.clearParameters();

        int [] updateCounts = stmt.executeBatch();
        conn.commit();
        conn.setAutoCommit(true);

                    stmt.close();
        conn.close();
    } 
    catch(BatchUpdateException b) {
                    System.err.println("-----BatchUpdateException-----");
                    System.err.println("SQLState:  " + b.getSQLState());
                    System.err.println("Message:  " + b.getMessage());
                    System.err.println("Vendor:  " + b.getErrorCode());
                    System.err.print("Update counts:  ");
                    int [] updateCounts = b.getUpdateCounts();
                    for (int i = 0; i < updateCounts.length; i++) {
                            System.err.print(updateCounts[i] + "   ");
                    }
                    System.err.println("");
            }
    catch (Exception e) {
        e.printStackTrace();
    }

如何识别导致异常的行，以便能够在没有该特定行的情况下重新执行批处理？ (通过更新行的字段 hasError)。

目前我没有批量保存对象，因此如果导致错误，我可以标记该行并跳过该行并继续处理(即保存/传输和删除)另一行。

谢谢!

更新:

好的，我使用以下代码来跟踪行(如果任何行导致错误):

        public static void processUpdateCounts(int[] updateCounts) {
            for (int i=0; i<updateCounts.length; i++) {
                if (updateCounts[i] >= 0) {
                    // Successfully executed; the number represents number of affected rows
                    logger.info("Successfully executed: number of affected rows: "+updateCounts[i]);
                } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
                    // Successfully executed; number of affected rows not available
                    logger.info("Successfully executed: number of affected rows not available: "+updateCounts[i]);
                } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                    logger.info("Failed to execute: "+updateCounts[i]);
                    // Failed to execute
                }
            }
        }

并添加了一行:

        processUpdateCounts(updateCounts);

所以我可以看到有或没有任何错误的更改，但 BatchUpdateException 似乎不起作用？

Answer 1

引用 BatchUpdateException 的 javadoc:

The order of elements in an array of update counts corresponds to the order in which commands were added to the batch.

After a command in a batch update fails to execute properly and a BatchUpdateException is thrown, the driver may or may not continue to process the remaining commands in the batch. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will have an element for every command in the batch rather than only elements for the commands that executed successfully before the error. In the case where the driver continues processing commands, the array element for any command that failed is Statement.EXECUTE_FAILED.