环境：oracle
使用PreparedStatement的executeBatch方法，如果DML操作成功，返回值[-2,-2,...]an array of update counts containing one element for each command in the batch.int java.sql.Statement.SUCCESS_NO_INFO = -2 [0xfffffffe]The constant indicating that a batch statement executed successfully but that no count of the number of rows it affected is available
int java.sql.Statement.EXECUTE_FAILED = -3 [0xfffffffd]The constant indicating that an error occured while executing a batch statement.
在执行executeBatch()时报错：
(1)使用class12.jar，返回array中的值为[-3,-3,...]；(2)使用ojdbc6.jar,返回array为空数组[]使用class12.jar或ojdbc6.jar,在遇到出错DML语句前执行的sql都会入库，即使设定Connection的auto-commit mode为false。如果期望在执行executeBatch()失败后回退，可以在catch中执行java.sql.Connection.rollback().使用rollback()需要设置java.sql.Connection.setAutoCommit(false);在操作最后执行提交操作java.sql.Connection.commit();void rollback() throws
- Undoes all changes made in the current transaction and releases any database locks currently held by this
Connectionobject. This method should be used only when auto-commit mode has been disabled. -
- Throws:
-
- if a database access error occurs, this method is called while participating in a distributed transaction, this method is called on a closed connection or thisConnectionobject is in auto-commit mode See Also:
-
void java.sql.Connection.setAutoCommit(boolean autoCommit) throws SQLExceptionSets this connection's auto-commit mode to the given state. If a connection is in auto-commit mode,
then all its SQL statements will be executed and committed as individual transactions. Otherwise,
its SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback.
By default, new connections are in auto-commit mode. The commit occurs when the statement completes. The time when the statement completes depends on the type of SQL Statement: For DML statements, such as Insert, Update or Delete, and DDL statements, the statement is complete as soon as it has finished executing. For Select statements, the statement is complete when the associated result set is closed. For CallableStatement objects or for statements that return multiple results, the statement is complete
when all of the associated result sets have been closed, and all update counts and output parameters have been retrieved. NOTE: If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed.
If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op.Parameters:autoCommit true to enable auto-commit mode; false to disable itThrows:SQLException - if a database access error occurs, setAutoCommit(true) is called while participating in a distributed transaction,
or this method is called on a closed connection
int[] java.sql.Statement.executeBatch() throws SQLExceptionSubmits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts. 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. 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 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.Returns: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.Throws:SQLException - if a database access error occurs, this method is called on a closed Statement or the driver does not support batch statements. Throws BatchUpdateException (a subclass of SQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.
-- Create tablecreate table TB_PERSON( id NUMBER(20) not null, name VARCHAR2(45), english_name VARCHAR2(45), age NUMBER(3), sex VARCHAR2(45), birthday DATE, memo VARCHAR2(100), create_time DATE default sysdate);-- Add comments to the table comment on table TB_PERSON is '用户信息';-- Add comments to the columns comment on column TB_PERSON.id is 'id';comment on column TB_PERSON.name is '姓名';comment on column TB_PERSON.english_name is '英文名';comment on column TB_PERSON.age is '年龄';comment on column TB_PERSON.sex is '性别';comment on column TB_PERSON.birthday is '出生日期';comment on column TB_PERSON.memo is '备注';comment on column TB_PERSON.create_time is '修改时间';-- Create/Recreate primary, unique and foreign key constraints alter table TB_PERSON add primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255;
--drop sequence seq_tb_person_id create sequence seq_tb_person_idminvalue 1 maxvalue 999999999 increment by 1 start with 1;
create or replacetrigger trg_tb_person_id before insert on tb_person for each row begin select seq_tb_person_id.nextval into :new.id from dual; end;
http://www.cnblogs.com/xwdreamer/archive/2012/06/08/2542277.html
code:package sql.batch;import java.sql.BatchUpdateException;import java.sql.Connection;import java.sql.Date;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;import java.sql.Statement;/*2015-7-17*/public class BatchProcessor { private String dirver; private String url; private String user; private String password; public BatchProcessor(String dirver, String url, String user, String password) { super(); this.dirver = dirver; this.url = url; this.user = user; this.password = password; } public void batch() throws ClassNotFoundException { System.out.println("Statement.SUCCESS_NO_INFO:" + Statement.SUCCESS_NO_INFO); System.out.println("Statement.EXECUTE_FAILED:" + Statement.EXECUTE_FAILED); Class.forName(dirver); Connection conn = null; PreparedStatement preStmt = null; try { conn = DriverManager.getConnection(url, user, password); preStmt = conn.prepareStatement("truncate table tb_person"); int truncateResult = preStmt.executeUpdate(); System.out.println("Result:" + truncateResult); conn.setAutoCommit(false); String sql = "insert into tb_person(name,english_name,age,sex,birthday,memo) values(?,?,?,?,?,?)"; preStmt = conn.prepareStatement(sql); for (int i = 0; i < 5; i++) { preStmt.setString(1, "Name" + i); preStmt.setString(2, "English_name" + i); if (i == 3) { preStmt.setString(3, "test"); } else { preStmt.setInt(3, 25 + i); } // preStmt.setInt(3, 25 + i); preStmt.setString(4, (i / 2 == 0 ? "男" : "女")); preStmt.setDate(5, new Date(System.currentTimeMillis())); preStmt.setString(6, "memo" + i); preStmt.addBatch(); } int[] result = preStmt.executeBatch(); conn.commit(); for (int i : result) { System.out.println("影响的行数" + i); } } catch (SQLException e) { try { // 如果出错，则此次executeBatch()的所有数据都不入库 conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } // 如果使用ojdbc6.jar，下列代码不需要，因为返回[] if (e instanceof BatchUpdateException) { BatchUpdateException bue = (BatchUpdateException) e; int[] updateCounts = bue.getUpdateCounts(); System.out.println("getUpdateCounts():" + updateCounts.length); for (int updateCount : updateCounts) { System.out.println("影响的行数：" + updateCount); } } System.err.println("Error:" + e); } finally { if (preStmt != null) { try { preStmt.clearBatch(); preStmt.clearParameters(); preStmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.setAutoCommit(true); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }}
package sql.batch;/*2015-7-17*/public class OracleBatchProcessor extends BatchProcessor { public OracleBatchProcessor() { // oracle.jdbc.driver.OracleDriver super("oracle.jdbc.OracleDriver", "jdbc:oracle:thin:@127.0.0.1:1521:instance1", "hr", "password"); }}
package sql.batch;/*2015-7-17*/public class Test { public static void main(String[] args) throws ClassNotFoundException { OracleBatchProcessor processor = new OracleBatchProcessor(); processor.batch(); }} Statement.SUCCESS_NO_INFO:-2Statement.EXECUTE_FAILED:-3Result:0影响的行数-2影响的行数-2影响的行数-2影响的行数-2影响的行数-2
Tips:
mysql在使用InnoDB引擎时支持事务http://www.cnblogs.com/zhangjun516/archive/2013/03/19/2968997.html