How do i get success and failure count when PreparedStatement batch excecution failes
I am trying to insert 50 rows in employee table.
data something like this:
values(1,'testCity1','testPhone1'),(2,'testCity2','testPhone2'),(3,'testCity3','testPhone3'),......,(50,'testCity50','testPhone50');
Employee table is holding one record before insertion i.e. (13,'testCity13','testphone13');
I am using batch size of 10 , My first 10 rows get inserted successfully but second batch is failed for id : 13 .
I am trying to get failure and success count Using BatchUpdateException .But When batch fails it goes to Exception block directly so I am unable to get BatchUpdateException object.
Please help me out, How can i get BatchUpdateException object such that i can use its methods to calculate success or failure count. Or there is any other way to find out success or failure count .
Below Scenario I am trying :
String sql = "insert into employee (id, city, phone) values (?, ?, ?)";
Connection connection = new getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
final int batchSize = 10;
int count = 0;
int successCount = 0;
int failCount = 0;
int notAavailable = 0;
try {
for (Employee employee: employees) {
ps.setString(1, employee.getId());
ps.setString(2, employee.getCity());
ps.setString(3, employee.getPhone());
ps.addBatch();
if(++count % batchSize == 0) {
ps.executeBatch();
}
}
ps.executeBatch(); // insert remaining records
} catch (BatchUpdateException buex) {
buex.printStackTrace();
LogUtil.error(buex);
int[] updateCounts = buex.getUpdateCounts();
for (int i = 0; i < updateCounts.length; i++) {
if (updateCounts[i] >= 0) {
successCount++;
} else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
notAavailable++;
}
if (updateCounts[i] == Statement.EXECUTE_FAILED) {
failCount++;
LogUtil.debug("Failed to execute record at:" +i);
}
}
} catch(Exception ex){
ex.printStackTrace();
}
finally {
LogUtil.info("Number of affected rows before Batch Error :: " + successCount);
LogUtil.info("Number of affected rows not available:" + notAavailable);
LogUtil.info("Failed Count in Batch because of Error:" + failCount);
ps.close();
connection.close();
}
Exception which i got is :
org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 DSMYSQL20160524121500218: 1062 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ?, ?] SQL: INSERT INTO `employee` (id, city, phone) values (?, ?, ?)]
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:703)
at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)
at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:542)
at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)
at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)
at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)
Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 DSMYSQL20160524121500218: 1062 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ? , ?] SQL: INSERT INTO 'employee` (id, city, phone) values (?, ?, ?)]
at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401)
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:161)
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:374)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:470)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
... 1 more
Caused by: org.teiid.core.TeiidException: 1062 Remote org.teiid.translator.jdbc.JDBCExecutionException: 1062 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [?, ? ,?] SQL: INSERT INTO `employee ` (id, city, phone) values (?, ?, ?)]
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:276)
at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:84)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:394)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:356)
at sun.reflect.GeneratedMethodAccessor110.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
at com.sun.proxy.$Proxy57.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:142)
... 17 more
Caused by: java.sql.SQLException: Remote java.sql.BatchUpdateException: Duplicate entry '13' for key 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1160)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1773)
at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1257)
at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:959)
at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1174)
at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:240)
... 27 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: Remote com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '13' for key 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:934)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1751)
... 31 more
