『讨论』『讨论』executeBatch 如何得到是哪些语句出错

软件环境：
软件环境：
win2000,jdk1.4.2, oracle9.2/10g ,class12.jar/ojdbc14.jar

java code

    Connection conn = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    int[] arr = null;
    int[] arrEx = null;
    boolean success = true;
    try {

      Class.forName("oracle.jdbc.driver.OracleDriver");;
      conn = DriverManager.getConnection(
          "jdbc:oracle:thin:@localhost:1521:ora92", "test", "test");;
      DatabaseMetaData dbmd = conn.getMetaData();;
      System.out.println(dbmd.supportsBatchUpdates(););;
      System.out.println(conn.getAutoCommit(););;
//      conn.setAutoCommit(false);;
      pst = conn.prepareStatement("insert into temp values(?);");;
      int temp = 0;
      for (int i = 90; i < 120; i++); {
        temp = i;
        if (temp > 110); {
          temp -= 100;
        }
        pst.setInt(1, temp);;
        pst.addBatch();;
      }
      arr = pst.executeBatch();;
    }
    catch (ClassNotFoundException e); {
      System.out.println(e);;
      success = false;
    }
    catch (BatchUpdateException e); {
      System.out.println(arr == null);;
      success = false;
      arrEx = e.getUpdateCounts();;
      System.out.println(arrEx.length);;
      for (int i = 0; i < arrEx.length; i++); {
        System.out.println(arrEx[i]);;
      }

      System.out.println(e);;
    }
    catch (SQLException e); {
      success = false;
      System.out.println(e);;
    }
    finally {

//      try {
//        if(success);{
//          conn.commit();;
//        }else{
//          conn.rollback();;
//        }
//      }
//      catch (SQLException se); {
//        System.out.println(se);;
//
//      }
      if (rs != null); {
        try {
          rs.close();;
          rs = null;
        }
        catch (Exception e); {}
      }
      if (pst != null); {
        try {
          pst.close();;
          pst = null;
        }
        catch (Exception e); {}
      }
      if (conn != null); {
        try {
          conn.close();;
          conn = null;
        }
        catch (Exception e); {}
      }
    }

table script

CREATE TABLE TEMP
(
  id NUMBER(2);                            DEFAULT NULL
);
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );
LOGGING 
NOCACHE
NOPARALLEL;

配置文件：
nothing
错误提示信息：
true
true
true
30
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
-3
java.sql.BatchUpdateException: ORA-01438: 值大于此列指定的允许精确度
你的分析：
jdbc doc：executeBatch() 则返回 int 阵列，该 阵列为每个已执行的 SQL 语句提供完成或错误信息。
BatchUpdateException， 它是 SQLException 的子类。它唯一的额外方法是 getUpdateCounts()， 该方法允许程序员获取供报告用的更新计数阵列。

无论用9i/10g,class12还是ojdbc14,conn.setAutoCommit无论是false，true  都得到相同的结果，与jdbc doc不一致。
executeBatch时 如何得到是哪些语句出错呢

找出来有什么用哪

下次就是另外一个地方出错了

明显是你存的数字大于99了吧

找出位置之后，怎么处理就看个人需要了，可以将错误的语句抛弃并log，将其他未commit成功的在执行一遍。

问题在于如何找出哪些或者哪个出错了

Batch执行完会返回一个int型的值，这个值就是执行的SQL语句数量，简单地说也就是批处理SQL语句组的下标值（当然，我忘记是0开始还是1开始）.

不应该依赖这个返回值，不同JDBC驱动，它的返回值是不同的，如果你需要针对多种JDBC的话。
该返回值完全依赖数据库JDBC的实现。

我记得ORACLE 8.1.7的JDBC，它总是返回-2表示成功，这个记忆有点模糊乐，不知道是不是乐，不过，它的返回值和JDK DOC里面的描述肯定是不同的，这个我做过很多测试

是这样啊？这个我倒是不敢确定了，楼主还是认真看看先。
   发现规范原来是拿来被违背的，唉..........

javadoc是这样说的：A driver is not required to implement this method. The possible implementations and return values have been modified in the Java 2 SDK, Standard Edition, version 1.3 to accommodate the option of continuing to proccess commands in a batch update after a BatchUpdateException obejct has been thrown.
在jdk里没有看到accommodate the option 的api？

The elements in the array returned by the method executeBatch may be one of the following:
A number greater than or equal to zero -- indicates that the command was processed successfully and is an update count giving the number of rows in the database that were affected by the command's execution
A value of SUCCESS_NO_INFO -- indicates that the command was processed successfully but that the number of rows affected is unknown
If one of the commands in a batch update fails to execute properly, this method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch. However, the driver's behavior must be consistent with a particular DBMS, either always continuing to process commands or never continuing to process commands. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will contain as many elements as there are commands in the batch, and at least one of the elements will be the following:
A value of EXECUTE_FAILED -- indicates that the command failed to execute successfully and occurs only if a driver continues to process commands after a command fails

这里的The elements in the array may be one of the following 是指array里所有的elements 是同一个one of the following，还是array里的elements 可以是不同的one of the following？
实际测试情况是对于oracle：所有的commands都成功的话，arr里的elements都是SUCCESS_NO_INFO，有commands失败的话，arrEx里elements都是EXECUTE_FAILED 。

嗯，查乐下SUCCESS_NO_INFO==-2
看来以前看文档还是不够仔细。
blush

不过，如果按照你的说法，要做到将出错的去除，对的还是能更新数据库，那么不用批处理其实也还好，因为就本身来说，SQL的执行计划在数据库端会有缓存，另外一种是，set完一组参数后直接execute，然后接着set下一组参数继续execute也是可以的。

这样的话，在execute语句上加try catch来截获异常，但是能继续执行就Ok乐。

刚查乐下oracle的jdbc doc：
public int[] executeBatch()
                   throws java.sql.SQLExceptionSubmit a batch of commands to the database for execution.
Commands are executed in the order in which they were added to the batch. This method closes the calling Statement object's current set if one is open. The statement's internal list of batch commands is reset to empty once this method returns.

Returns:
an array of update counts containing one element for each command in the batch. The array is ordered according to the order in which commands were inserted into the batch.
Throws:
oracle.jdbc2.BatchUpdateException - if any of the commands in the batch failed to execute property. (For example, the batch will fail if it contained a command that returns a result set.) executeBatch() will stop when the first command returns an error. The update counts for the executed commands can be obtained by invoking the method BatchUpdateException.getUpdateCounts().
Since:
8.1.6 (JDBC 2.0).


依赖这个的话，还是会依赖数据库的。
还是认为这个返回的数组没什么用处

我们现在把所有的sql和sql执行生效行数输出

如果有exception,看一下SQL一般也能找到

这个只对开发人员有点用,可以记下来,以后大家提的多了,可以格外把
出错的sql指出来阿,厚厚