Java Code Examples for java.sql.BatchUpdateException#getUpdateCounts()

@SuppressWarnings("serial")
@Test
public void testExecuteBatchSQL3() throws SQLException, TException {
  Statement statement = connection.createStatement();
  resp = RpcUtils.getStatus(Collections.singletonList(errorStatus));
  statement.addBatch("sql1");
  statement.addBatch("sql1");
  List<TSStatus> resExpected = new ArrayList<TSStatus>() {
    {
      add(RpcUtils.getStatus(TSStatusCode.SUCCESS_STATUS));
      add(RpcUtils.getStatus(TSStatusCode.SQL_PARSE_ERROR));
    }
  };
  resp.setSubStatus(resExpected);
  when(client.executeBatchStatement(any(TSExecuteBatchStatementReq.class))).thenReturn(resp);
  try {
    statement.executeBatch();
  } catch (BatchUpdateException e) {
    int[] result = e.getUpdateCounts();
    for (int i = 0; i < resExpected.size(); i++) {
      assertEquals(resExpected.get(i).code, result[i]);
    }
    return;
  }
  fail();
}
 

/**
 * helper method to evaluate negative tests where we expect a
 * batchExecuteException to be returned.
 * @exception SQLException     Thrown if the expected error occurs
 *                             We expect a BatchUpdateException, and
 *                             verify it is so.
 *
 * @param expectedError The sqlstate to look for.
 * @param stmt The Statement that contains the Batch to
 *                             be executed.
 * @param expectedUpdateCount The expectedUpdateCount array.
 */
protected void assertBatchExecuteError(
    String expectedError,
    Statement stmt,
    int[] expectedUpdateCount)
throws SQLException
{
    int[] updateCount;
    try {
        updateCount = stmt.executeBatch();
        fail("Expected batchExecute to fail");
    } catch (BatchUpdateException bue) {
        assertSQLState(expectedError, bue);
        updateCount = bue.getUpdateCounts();
        assertBatchUpdateCounts(expectedUpdateCount, updateCount);
    }
}
 

/**
 * helper method to evaluate negative tests where we expect a
 * batchExecuteException to be returned.
 * @exception SQLException     Thrown if the expected error occurs
 *                             We expect a BatchUpdateException, and
 *                             verify it is so.
 *
 * @param expectedError The sqlstate to look for.
 * @param stmt The Statement that contains the Batch to
 *                             be executed.
 * @param expectedUpdateCount The expectedUpdateCount array.
 */
protected void assertBatchExecuteError(
    String expectedError,
    Statement stmt,
    int[] expectedUpdateCount)
throws SQLException
{
    int[] updateCount;
    try {
        updateCount = stmt.executeBatch();
        fail("Expected batchExecute to fail");
    } catch (BatchUpdateException bue) {
        assertSQLState(expectedError, bue);
        updateCount = bue.getUpdateCounts();
        assertBatchUpdateCounts(expectedUpdateCount, updateCount);
    }
}
 

/**
 * @throws SQLException if failed.
 */
@Test
public void testErrorAmidstBatch() throws SQLException {
    formBatch(1, 2);
    formBatch(3, 1); // Duplicate key

    BatchUpdateException reason = (BatchUpdateException)
        GridTestUtils.assertThrows(log, new Callable<Object>() {
            @Override public Object call() throws Exception {
                return prepStmt.executeBatch();
            }
        },
        BatchUpdateException.class,
        "Failed to INSERT some keys because they are already in cache");

    // Check update counts in the exception.
    int[] counts = reason.getUpdateCounts();

    assertNotNull(counts);

    assertEquals(1, counts.length);
    assertEquals(2, counts[0]);
}
 

/**
 * Submits a batch of commands to the database for execution and if all commands execute successfully, returns an 
 * array of update counts. Fills stored records with number of updated records in database.
 * 
 * @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.
 * @throws SQLException
 */
public int[] executeBatch() throws SQLException{
	int[] updatedRecords = null;
	BatchUpdateException ex = null;
	try {
		updatedRecords = statement.executeBatch();
	} catch (BatchUpdateException e) {
		updatedRecords = e.getUpdateCounts();
		ex = e;
	}
	for (int i = 0; i < outRecords.size(); i++) {
		outRecords.get(i).getField(updatedNumberCloverFieldNumber).setValue(updatedRecords[i]);
	}
	if (ex != null) throw ex;
	return updatedRecords;
}
 

/** 
 * helper method to evaluate negative tests where we expect a 
 * batchExecuteException to be returned.
 * @exception SQLException     Thrown if the expected error occurs
 *                             We expect a BatchUpdateException, and
 *                             verify it is so.
 *
 * @param expectedError The sqlstate to look for.
 * @param stmt The Statement that contains the Batch to
 *                             be executed.
 * @param expectedUpdateCount The expectedUpdateCount array.
 */
protected void assertBatchExecuteError( 
    String expectedError,
    Statement stmt,
    int[] expectedUpdateCount) 
throws SQLException 
{
    int[] updateCount;    
    try {
        updateCount = stmt.executeBatch();
        fail("Expected batchExecute to fail");
    } catch (BatchUpdateException bue) {
        assertSQLState(expectedError, bue);
        updateCount = bue.getUpdateCounts();
        assertBatchUpdateCounts(expectedUpdateCount, updateCount);
    } 
}
 

protected FlushResult postProcessBatchFailure(List<DbOperation> operations, RuntimeException e) {
  BatchExecutorException batchExecutorException = ExceptionUtil.findBatchExecutorException(e);

  if (batchExecutorException == null) {
    // Unexpected exception
    throw e;
  }

  List<BatchResult> successfulBatches = batchExecutorException.getSuccessfulBatchResults();
  BatchUpdateException cause = batchExecutorException.getBatchUpdateException();

  Iterator<DbOperation> operationsIt = operations.iterator();
  List<DbOperation> failedOperations = new ArrayList<>();

  for (BatchResult successfulBatch : successfulBatches) {
    postProcessJdbcBatchResult(operationsIt, successfulBatch.getUpdateCounts(), null, failedOperations);
  }

  int[] failedBatchUpdateCounts = cause.getUpdateCounts();
  postProcessJdbcBatchResult(operationsIt, failedBatchUpdateCounts, e, failedOperations);

  List<DbOperation> remainingOperations = CollectionUtil.collectInList(operationsIt);
  return FlushResult.withFailuresAndRemaining(failedOperations, remainingOperations);
}
 

/**
 * @throws SQLException If failed.
 */
@Test
public void testHeterogeneousBatchException() throws Exception {
    stmt.addBatch("insert into Person (_key, id, firstName, lastName, age) values ('p0', 0, 'Name0', 'Lastname0', 10)");
    stmt.addBatch("insert into Person (_key, id, firstName, lastName, age) " +
        "values ('p1', 1, 'Name1', 'Lastname1', 20), ('p2', 2, 'Name2', 'Lastname2', 30)");
    stmt.addBatch("merge into Person (_key, id, firstName, lastName, age) values ('p3', 3, 'Name3', 'Lastname3', 40)");
    stmt.addBatch("update Person set id = 'FAIL' where age >= 30"); // Fail.
    stmt.addBatch("merge into Person (_key, id, firstName, lastName, age) values ('p0', 2, 'Name2', 'Lastname2', 50)");
    stmt.addBatch("delete from Person where FAIL <= 40"); // Fail.

    try {
        stmt.executeBatch();

        fail("BatchUpdateException must be thrown");
    }
    catch (BatchUpdateException e) {
        checkThereAreNotUsedConnections();

        int[] updCnts = e.getUpdateCounts();

        if (!e.getMessage().contains("Value conversion failed")) {
            log.error("Invalid exception: ", e);

            fail();
        }

        assertEquals("Invalid update counts size", 6, updCnts.length);
        assertArrayEquals("Invalid update count",
            new int[] {1, 2, 1, Statement.EXECUTE_FAILED, 1, Statement.EXECUTE_FAILED}, updCnts);
    }
}
 

/**
 * @throws SQLException If failed.
 */
@Test
public void testErrorAmidstBatch() throws SQLException {
    BatchUpdateException reason = (BatchUpdateException)
        GridTestUtils.assertThrows(log,
        new Callable<Object>() {
            @Override public Object call() throws Exception {
                try (Statement stmt = conn.createStatement()) {
                    stmt.addBatch("INSERT INTO Person(_key, id, firstName, lastName, age, data) " +
                        "VALUES ('p1', 0, 'J', 'W', 250, RAWTOHEX('W'))");

                    stmt.addBatch("UPDATE Person SET id = 3, firstName = 'Mike', lastName = 'Green', " +
                        "age = 40, data = RAWTOHEX('Green') WHERE _key = 'p3'");

                    stmt.addBatch("SELECT id FROM Person WHERE _key = 'p1'");

                    return stmt.executeBatch();
                }
            }
        },
        BatchUpdateException.class,
        "Given statement type does not match that declared by JDBC driver");

    // Check update counts in the exception.
    int[] counts = reason.getUpdateCounts();

    assertEquals(2, counts.length);
    assertEquals(1, counts[0]);
    assertEquals(0, counts[1]);
}
 

@Test
public void testExecuteBatch() throws Exception
{
  Connection connection = getConnection();
  Statement statement = connection.createStatement();

  connection.setAutoCommit(false);
  // mixed of ddl/dml in batch
  statement.addBatch("create or replace table test_batch(a string, b integer)");
  statement.addBatch("insert into test_batch values('str1', 1), ('str2', 2)");
  statement.addBatch("update test_batch set test_batch.b = src.b + 5 from " +
                     "(select 'str1' as a, 2 as b) src where test_batch.a = src.a");

  int[] rowCounts = statement.executeBatch();
  connection.commit();

  assertThat(rowCounts.length, is(3));
  assertThat(rowCounts[0], is(0));
  assertThat(rowCounts[1], is(2));
  assertThat(rowCounts[2], is(1));

  List<String> batchQueryIDs = statement.unwrap(SnowflakeStatement.class).getBatchQueryIDs();
  assertEquals(3, batchQueryIDs.size());
  assertEquals(statement.unwrap(SnowflakeStatement.class).getQueryID(), batchQueryIDs.get(2));

  ResultSet resultSet = statement.executeQuery("select * from test_batch order by b asc");
  resultSet.next();
  assertThat(resultSet.getInt("B"), is(2));
  resultSet.next();
  assertThat(resultSet.getInt("B"), is(7));
  statement.clearBatch();

  // one of the batch is query instead of ddl/dml
  // it should continuing processing
  try
  {
    statement.addBatch("insert into test_batch values('str3', 3)");
    statement.addBatch("select * from test_batch");
    statement.addBatch("select * from test_batch_not_exist");
    statement.addBatch("insert into test_batch values('str4', 4)");
    statement.executeBatch();
    fail();
  }
  catch (BatchUpdateException e)
  {
    rowCounts = e.getUpdateCounts();
    assertThat(e.getErrorCode(), is(
        ERROR_CODE_DOMAIN_OBJECT_DOES_NOT_EXIST));
    assertThat(rowCounts[0], is(1));
    assertThat(rowCounts[1], is(Statement.SUCCESS_NO_INFO));
    assertThat(rowCounts[2], is(Statement.EXECUTE_FAILED));
    assertThat(rowCounts[3], is(1));

    connection.rollback();
  }

  statement.clearBatch();

  statement.addBatch("put file://" + getFullPathFileInResource(TEST_DATA_FILE) + " @%test_batch auto_compress=false");
  File tempFolder = tmpFolder.newFolder("test_downloads_folder");
  statement.addBatch("get @%test_batch file://" + tempFolder);

  rowCounts = statement.executeBatch();
  assertThat(rowCounts.length, is(2));
  assertThat(rowCounts[0], is(Statement.SUCCESS_NO_INFO));
  assertThat(rowCounts[0], is(Statement.SUCCESS_NO_INFO));
  statement.clearBatch();

  statement.execute("drop table if exists test_batch");
  statement.close();
  connection.close();
}
 

/**
 * @throws SQLException If failed.
 */
@Test
public void testBatchException() throws Exception {
    final int BATCH_SIZE = 7;

    final int FAILED_IDX = 5;

    for (int idx = 0, i = 0; i < FAILED_IDX; ++i, idx += i) {
        stmt.addBatch("insert into Person (_key, id, firstName, lastName, age) values "
            + generateValues(idx, i + 1));
    }

    stmt.addBatch("select * from Person");

    stmt.addBatch("insert into Person (_key, id, firstName, lastName, age) values "
        + generateValues(100, 7));

    try {
        stmt.executeBatch();

        fail("BatchUpdateException must be thrown");
    }
    catch (BatchUpdateException e) {
        checkThereAreNotUsedConnections();

        int[] updCnts = e.getUpdateCounts();

        assertEquals("Invalid update counts size", BATCH_SIZE, updCnts.length);

        for (int i = 0; i < BATCH_SIZE; ++i)
            assertEquals("Invalid update count", i != FAILED_IDX ? i + 1 : Statement.EXECUTE_FAILED,
                updCnts[i]);

        if (!e.getMessage().contains("Given statement type does not match that declared by JDBC driver")) {
            log.error("Invalid exception: ", e);

            fail();
        }

        assertEquals("Invalid SQL state.", SqlStateCode.PARSING_EXCEPTION, e.getSQLState());
        assertEquals("Invalid error code.", IgniteQueryErrorCode.STMT_TYPE_MISMATCH, e.getErrorCode());
    }
}
 

/**
 * @throws SQLException If failed.
 */
@Test
public void testBatchParseException() throws Exception {
    final int BATCH_SIZE = 7;

    final int FAILED_IDX = 5;

    for (int idx = 0, i = 0; i < FAILED_IDX; ++i, idx += i) {
        stmt.addBatch("insert into Person (_key, id, firstName, lastName, age) values "
            + generateValues(idx, i + 1));
    }

    stmt.addBatch("insert into Person (_key, id, firstName, lastName, age) values (4444, 'fail', 1, 1, 1)");

    stmt.addBatch("insert into Person (_key, id, firstName, lastName, age) values "
        + generateValues(100, 7));

    try {
        stmt.executeBatch();

        fail("BatchUpdateException must be thrown");
    }
    catch (BatchUpdateException e) {
        checkThereAreNotUsedConnections();

        int[] updCnts = e.getUpdateCounts();

        assertEquals("Invalid update counts size", BATCH_SIZE, updCnts.length);

        for (int i = 0; i < BATCH_SIZE; ++i)
            assertEquals("Invalid update count: " + i, i != FAILED_IDX ? i + 1 : Statement.EXECUTE_FAILED,
                updCnts[i]);

        if (!e.getMessage().contains("Value conversion failed")) {
            log.error("Invalid exception: ", e);

            fail();
        }

        assertEquals("Invalid SQL state.", SqlStateCode.CONVERSION_FAILED, e.getSQLState());
        assertEquals("Invalid error code.", IgniteQueryErrorCode.CONVERSION_FAILED, e.getErrorCode());
    }
}
 

/**
 * @throws SQLException If failed.
 */
@Test
public void testBatchMergeParseException() throws Exception {
    final int BATCH_SIZE = 7;

    final int FAILED_IDX = 5;

    for (int idx = 0, i = 0; i < FAILED_IDX; ++i, idx += i) {
        stmt.addBatch("merge into Person (_key, id, firstName, lastName, age) values "
            + generateValues(idx, i + 1));
    }

    stmt.addBatch("merge into Person (_key, id, firstName, lastName, age) values (4444, 'FAIL', 1, 1, 1)");

    stmt.addBatch("merge into Person (_key, id, firstName, lastName, age) values "
        + generateValues(100, 7));

    try {
        stmt.executeBatch();

        fail("BatchUpdateException must be thrown");
    }
    catch (BatchUpdateException e) {
        checkThereAreNotUsedConnections();

        int[] updCnts = e.getUpdateCounts();

        assertEquals("Invalid update counts size", BATCH_SIZE, updCnts.length);

        for (int i = 0; i < BATCH_SIZE; ++i)
            assertEquals("Invalid update count: " + i, i != FAILED_IDX ? i + 1 : Statement.EXECUTE_FAILED,
                updCnts[i]);

        if (!e.getMessage().contains("Value conversion failed")) {
            log.error("Invalid exception: ", e);

            fail();
        }

        assertEquals("Invalid SQL state.", SqlStateCode.CONVERSION_FAILED, e.getSQLState());
        assertEquals("Invalid error code.", IgniteQueryErrorCode.CONVERSION_FAILED, e.getErrorCode());
    }
}
 

/**
 * @throws SQLException If failed.
 */
@Test
public void testBatchKeyDuplicatesException() throws Exception {
    final int BATCH_SIZE = 7;

    final int FAILED_IDX = 5;

    int idx = 0;

    for (int i = 0; i < FAILED_IDX; ++i, idx += i) {
        stmt.addBatch("insert into Person (_key, id, firstName, lastName, age) values "
            + generateValues(idx, i + 1));
    }

    stmt.addBatch("insert into Person (_key, id, firstName, lastName, age) values ('p0', 0, 'Name0', 'Lastname0', 20)");

    stmt.addBatch("insert into Person (_key, id, firstName, lastName, age) values "
        + generateValues(++idx, 7));

    try {
        stmt.executeBatch();

        fail("BatchUpdateException must be thrown");
    }
    catch (BatchUpdateException e) {
        checkThereAreNotUsedConnections();

        int[] updCnts = e.getUpdateCounts();

        assertEquals("Invalid update counts size", BATCH_SIZE, updCnts.length);

        for (int i = 0; i < BATCH_SIZE; ++i)
            assertEquals("Invalid update count: " + i, i != FAILED_IDX ? i + 1 : Statement.EXECUTE_FAILED,
                updCnts[i]);

        if (!e.getMessage().contains("Failed to INSERT some keys because they are already in cache [keys=[p0]")) {
            log.error("Invalid exception: ", e);

            fail();
        }

        assertEquals("Invalid SQL state.", SqlStateCode.CONSTRAINT_VIOLATION, e.getSQLState());
        assertEquals("Invalid error code.", IgniteQueryErrorCode.DUPLICATE_KEY, e.getErrorCode());
    }
}