Update Batch
来源:互联网 发布:张北云计算基地 编辑:程序博客网 时间:2024/05/07 21:33
Note:
■Oracle recommends that you use JDBC standard features when
possible. This recommendation applies to update batching as well.
Oracle update batching is retained primarily for backwards
compatibility.
■For both standard update batching and Oracle update batching,
Oracle recommends you to keep the batch sizes in the general
range of 50 to 100. This is because though the drivers support
larger batches, they in turn result in a large memory footprint
with no corresponding increase in performance. Very large
batches usually result in a decline in performance compared to
smaller batches.
As implemented by Oracle, update batching is intended for use with prepared
statements, when you are repeating the same statement with different bind variables.
Be aware of the following:
...
PreparedStatement pstmt =
conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");
pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();
pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();
...
Because a batch is associated with a single prepared statement object, you can batch
only repeated runs of a single prepared statement, as in this example.
...
PreparedStatement pstmt =
conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");
pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();
pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();
int[] updateCounts = pstmt.executeBatch();
...
Starting from Oracle Database 11g Release 1 (11.1), the executeBatch method has
been improved so that when an error occurs in the middle of the batch execution, the
BatchUpdateExecution exception that is thrown contains the position of the error
in the batch. The BatchUpdateExecution.getUpdateCounts method returns an
array of int containing the update counts for the updates that were executed
successfully before this error occurred. So if an error occurs in the 5th element of the
batch, then the size of the array returned is 4 and each value is
Statement.SUCCESS_NO_INFO.
conn.setAutoCommit(false);
PreparedStatement pstmt =
conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");
pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();
pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();
int[] updateCounts = pstmt.executeBatch();
conn.commit();
pstmt.close();
...
You can process the update counts array to determine if the batch processed successfully.
Update Counts in the Oracle Implementation of Standard Batching
If a statement batch is processed successfully, then the integer array, or update counts
array, returned by the statement executeBatch call will always have one element for
each operation in the batch. In the Oracle implementation of standard update
batching, the values of the array elements are as follows:
■For a prepared statement batch, it is not possible to know the number of rows
affected in the database by each individual statement in the batch. Therefore, all
array elements have a value of -2. According to the JDBC 2.0 specification, a value
of -2 indicates that the operation was successful but the number of rows affected
is unknown.
1 conn.setAutoCommit(false);
2 String sqlInsert="insert into friend(name) values(?)";
3 PreparedStatement pstmt=conn.prepareStatement(sqlInsert);
4 try {
5 pstmt.setNString(1, "friend1");
6 pstmt.addBatch();
7 pstmt.setNString(1, "friend2");
8 pstmt.addBatch();
9 pstmt.setNString(1, "friend3");
10 pstmt.addBatch();
11 int[] result = pstmt.executeBatch();
12 conn.commit();
13 } catch (BatchUpdateException e) {
14 //because it is impossible to know which operation failed.
15 //so there is no choice but to roll back the total batch update.
16 conn.rollback();
17 pstmt.clearBatch();
18 }
19 pstmt.close();
20 conn.close();
Of course, it would be better to put pstmt.close and conn.close into finally clause.
- Update Batch
- batch update For Sql
- Batch update returned unexpected row
- JDBC PreparedStatement example - update batch
- 批处理更新(Batch Update)流程
- JDBC(PreparedStatement)--Batch Update Record
- Duplicate entry 和 Batch update
- Batch update returned unexpected row
- Could not execute JDBC batch update
- 再说Could not execute JDBC batch update
- Batch update table name using *sp_rename*
- SQLGrammarException: Could not execute JDBC batch update
- Could not execute JDBC batch update....
- Could not execute JDBC batch update问题
- Could not execute JDBC batch update
- Could not execute JDBC batch update
- Could not execute JDBC batch update
- Could not execute JDBC batch update
- ruby升级 非rvm
- UVa10010 Where's Waldorf?
- Oracle入门教程
- poj 1330 LCA最近公共祖先
- Java动态类加载
- Update Batch
- 为什么析构函数要声明成virtual
- Window7 64位旗舰版 安装Oracle 11gR2 安装图解
- UVa10106 Product
- java反射详解
- 链表的顺序插入 先分析有几种类型再编写 思路要清晰
- 浅谈数据库设计技巧
- android之Widget开发详解实例一
- 游戏数学