I have a batch process which does Bulk UPDATE statement.
After implementing batch support using Spring JDBC 4.1.6 and Oracle Jdbc Driver (ojdbc7 and ucp), the number of records affected for individual update requests (in the batch) are always retrieved as -2 (Statement.SUCCESS_NO_INFO).
Is there any way to know the rows affected for individual cases, as I have to retry the same argument with an INSERT statement after this. Technically trying to develop this as an UPSERT implementation
I tried this batch update in Three Different Ways, and in all three methods the results are same -- (It just tells me Statement.SUCCESS_NO_INFO (-2) )
Method 1 -- Direct UCP Connection and PreparedStatement
connectionPoolMgr.startConnectionPool("mgr_pool");
Connection connection = pds.getConnection();
PreparedStatement pstmt = connection.prepareStatement(dmlSQL);
pstmt.setInt(1, pkId);
pstmt.setInt(2, idx * 10);
pstmt.addBatch();
// EVERY ELEMENT IN THIS ARRAY IS ALWAYS returned as -2
int updatedRows[] = pstmt.executeBatch();
Method 2 - Spring JdbcTemplate and batchUpdate()
MapSqlParameterSource[] paramsArray = getSqlParameterList().toArray(new MapSqlParameterSource[0]);
// EVERY ELEMENT IN THIS ARRAY IS ALWAYS returned as -2
int[] batchUpdateResult = getNamedParameterJdbcTemplate().batchUpdate(sqlStatement, paramsArray);
Method 3 - Spring BatchSqlUpdate implementation
BatchInsert batchInsert = new BatchInsert(dataSource);
for (int i = 0; i < count; i++) {
MapSqlParameterSource param = new MapSqlParameterSource();
param.addValue("ID", i + 100);
param.addValue("FIRST_NAME", "Name" + i);
batchInsert.updateByNamedParam(param.getValues());
}
batchInsert.flush();
int rowsAffected[] = batchInsert.getRowsAffected();
class BatchInsert extends BatchSqlUpdate {
private static final String SQL = "UPDATE t_customer_test SET first_name = :FIRST_NAME) WHERE id = :ID";
BatchInsert(DataSource dataSource) {
super(dataSource, SQL);
declareParameter(new SqlParameter(Types.VARCHAR));
declareParameter(new SqlParameter(Types.INTEGER));
setBatchSize(100);
compile();
}
}