executeBatch()詳解

JDBC提供了數(shù)據(jù)庫batch處理的能力，在數(shù)據(jù)大批量操作(新增、刪除等)的情況下可以大幅度提升系統(tǒng)的性能。

// 關(guān)閉自動(dòng)執(zhí)行

con.setAutoCommit(false);

Statement stmt = con.createStatement();

stmt.addBatch(“INSERT INTO employees VALUES (1000, ‘Joe Jones’)”);

stmt.addBatch(“INSERT INTO departments VALUES (260, ‘Shoe’)”);

stmt.addBatch(“INSERT INTO emp_dept VALUES (1000, 260)”);

// 提交一批要執(zhí)行的更新命令

int[] updateCounts = stmt.executeBatch();

本例中禁用了自動(dòng)執(zhí)行模式，從而在調(diào)用 Statement.executeBatch() 時(shí)可以防止 JDBC 執(zhí)行事務(wù)處理。禁用自動(dòng)執(zhí)行使得應(yīng)用程序能夠在發(fā)生錯(cuò)誤及批處理中的某些命令不能執(zhí)行時(shí)決定是否執(zhí)行事務(wù)處理。因此，當(dāng)進(jìn)行批處理更新時(shí)，通常應(yīng)該關(guān)閉自動(dòng)執(zhí)行。

在JDBC 2.0 中，Statement 對象能夠記住可以一起提交執(zhí)行的命令列表。創(chuàng)建語句時(shí)，與它關(guān)聯(lián)的命令列表為空。Statement.addBatch() 方法為調(diào)用語句的命令列表添加一個(gè)元素。如果批處理中包含有試圖返回結(jié)果集的命令，則當(dāng)調(diào)用 Statement. executeBatch() 時(shí)，將拋出 SQLException。只有 DDL 和 DML 命令(它們只返回簡單的更新計(jì)數(shù))才能作為批處理的一部分來執(zhí)行。如果應(yīng)用程序決定不提交已經(jīng)為某語句構(gòu)

1

2

3

造的命令批處理，則可以調(diào)用方法 Statement.clearBatch()(以上沒有顯示)來重新設(shè)置批處理。

Statement.executeBatch() 方法將把命令批處理提交給基本 DBMS 來執(zhí)行。命令的執(zhí)行將依照在批處理中的添加順序來進(jìn)行。ExecuteBatch() 為執(zhí)行的命令返回更新計(jì)數(shù)數(shù)組。數(shù)組中對應(yīng)于批處理中的每個(gè)命令都包含了一項(xiàng)，而數(shù)組中各元素依據(jù)命令的執(zhí)行順序(這還是和命令的最初添加順序相同)來排序。調(diào)用executeBatch() 將關(guān)閉發(fā)出調(diào)用的 Statement 對象的當(dāng)前結(jié)果集(如果有一個(gè)結(jié)果集是打開的)。executeBatch() 返回后，將重新將語句的內(nèi)部批處理命令列表設(shè)置為空。

如果批處理中的某個(gè)命令無法正確執(zhí)行，則 ExecuteBatch() 將拋出BatchUpdateException?？梢哉{(diào)用BatchUpdateException.getUpdateCounts() 方法來為批處理中成功執(zhí)行的命令返回更新計(jì)數(shù)的整型數(shù)組。因?yàn)楫?dāng)有第一個(gè)命令返回錯(cuò)誤時(shí)，Statement.executeBatch() 就中止，而且這些命令是依據(jù)它們在批處理中的添加順序而執(zhí)行的。所以如果 BatchUpdateException.getUpdateCounts() 所返回的數(shù)組包含 N 個(gè)元素，這就意味著在調(diào)用 executeBatch() 時(shí)批處理中的前 N 個(gè)命令被成功執(zhí)行。用PreparedStatement 可以象下面這樣寫代碼：

// 關(guān)閉自動(dòng)執(zhí)行

con.setAutoCommit(false);

PreparedStatement stmt = con.prepareStatement(“INSERT INTO employees VALUES (?, ?)”);

stmt.setInt(1, 2000);

stmt.setString(2, “Kelly Kaufmann”);

stmt.addBatch();

// 提交要執(zhí)行的批處理

int[] updateCounts = stmt.executeBatch();

========================================

PrepareStatement 也是接口

PrepareStatement extends Statement

PrepareStatement 本身沒有 int[] executeBatch() throws SQLException 方法

而是繼承了Statement的方法，且它們都是接口沒有實(shí)際實(shí)現(xiàn)方法，但Statement

接口對executeBatch()方法做了規(guī)范

/**

* Submits a batch of commands to the database for execution and

* if all commands execute successfully, returns an array of update counts.

每次提交一批命令到數(shù)據(jù)庫中執(zhí)行，如果所有的命令都成功執(zhí)行了，那么返回一個(gè)

數(shù)組，這個(gè)數(shù)組是說明每條命令所影響的行數(shù)

* The int elements of the array that is returned are ordered

* to correspond to the commands in the batch, which are ordered

* according to the order in which they were added to the batch.

返回的數(shù)組中每個(gè)整型值都是排過序的，它們的順序和批量處理中的命令們是一致的，

命令的順序是按照它們被加到批處理中的順序一致。

* The elements in the array returned by the method executeBatch

* may be one of the following:

executeBatch方法返回的數(shù)組中的元素可能是下面幾種情形之一：

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

一個(gè)大于或等于零的數(shù)字，簡單說來命令成功執(zhí)行后就返回它所影響到的行的數(shù)目

A value of SUCCESS_NO_INFO – indicates that the command was

* processed successfully but that the number of rows affected is

* unknown

* The constant indicating that a batch statement executed successfully

* but that no count of the number of rows it affected is available.

int SUCCESS_NO_INFO = -2;

常量SUCCESS_NO_INFO代表的值=-2,也就是說命令執(zhí)行成功了但命令影響到的行數(shù)

無法統(tǒng)計(jì)，是未知的，只能返回SUCCESS_NO_INFO來說明命令執(zhí)行情況。

* If one of the commands in a batch update fails to execute properly,

* this method throws aBatchUpdateException, and a JDBC

* driver may or may not continue to process the remaining commands in

* the batch.

如果批量處理時(shí)其中一個(gè)命令執(zhí)行失敗，則會拋出一個(gè)異常BatchUpdateException

JDBC驅(qū)動(dòng)可能會停止剩余的命令，也可能繼續(xù)執(zhí)行剩余的命令。

* However, the driver's behavior must be consistent with a

* particular DBMS, either always continuing to process commands or never

* continuing to process commands.

不管怎樣，驅(qū)動(dòng)要怎么做取決于數(shù)據(jù)庫管理系統(tǒng)的細(xì)節(jié)，總是執(zhí)行或總是不執(zhí)行兩者其一。

* 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:

發(fā)生失敗后如果驅(qū)動(dòng)繼續(xù)執(zhí)行，通過BatchUpdateException.getUpdateCounts()方法返回

的數(shù)組應(yīng)該包括批處理中有的那些命令的結(jié)果，并且至少有一個(gè)元素的值是下面的情況：

A value ofEXECUTE_FAILED-- indicates that the command failed

* to execute successfully and occurs only if a driver continues to

* process commands after a command fails

int EXECUTE_FAILED = -3;

指示命令沒有成功執(zhí)行的常量值EXECUTE_FAILED，并且只有在命令出錯(cuò)后驅(qū)動(dòng)繼續(xù)執(zhí)行的情況下才會出現(xiàn)，

如果出錯(cuò)后不再執(zhí)行，則返回的結(jié)果中沒有錯(cuò)誤信息只有那些被成功執(zhí)行后的結(jié)果。

* 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 aBatchUpdateExceptionobejct has been thrown.

驅(qū)動(dòng)不實(shí)現(xiàn)此方法，可能會出現(xiàn)的實(shí)現(xiàn)和返回值在Java 2 SDK，Standard Edition,

version 1.3 ，以適應(yīng)批處理時(shí)拋出BatchUpdateException 異常后是繼續(xù)執(zhí)行還是

終止執(zhí)行的選項(xiàng)。

* @return an array of update counts containing one element for each

* command in the batch. The elements of the array are ordered according

* to the order in which commands were added to the batch.

返回一個(gè)和添加命令時(shí)的順序一樣的數(shù)組結(jié)果

* @exception SQLException if a database access error occurs or the

* driver does not support batch statements. Throws {@link BatchUpdateException}

* (a subclass ofSQLException) if one of the commands sent to the

* database fails to execute properly or attempts to return a result set.

* @since 1.3

*/

如果數(shù)據(jù)庫訪問異?；蝌?qū)動(dòng)不支持批處理命令，或者如果一個(gè)命令發(fā)送到數(shù)據(jù)庫時(shí)失敗或嘗試取得結(jié)果，即使失敗，都會拋一個(gè)異常BatchUpdateException 它是SQLException的子類。