java.sql.BatchUpdateException Java Examples

private DataAccessException doTranslate(Throwable sourceThrowable, SQLException sqlException) {
  SQLException relevantSqlException;
  if (sqlException instanceof BatchUpdateException) {
    relevantSqlException = sqlException.getNextException();
  } else {
    relevantSqlException = sqlException;
  }

  DataAccessException translatedException;
  if (relevantSqlException instanceof PSQLException) {
    translatedException = doTranslate(sourceThrowable, (PSQLException) relevantSqlException);
  } else {
    translatedException = null;
  }
  return translatedException;
}
 

private void addContentReports(NewsletterReport newsletterReport, Connection conn) {
	PreparedStatement stat = null;
	try {
		stat = conn.prepareStatement(ADD_CONTENT_REPORT);
		int reportId = newsletterReport.getId();
		for (ContentReport contentReport : newsletterReport.getContentReports().values()) {
			stat.setInt(1, contentReport.getId());
			stat.setInt(2, reportId);
			stat.setString(3, contentReport.getContentId());
			stat.setString(4, contentReport.getTextBody());
			stat.setString(5, contentReport.getHtmlBody());
			stat.addBatch();
			stat.clearParameters();
		}
		stat.executeBatch();
	} catch (BatchUpdateException e) {
		this.processDaoException(e.getNextException(), "Error adding contents for sent newsletter", 
				"addContentReports");
	} catch (Throwable t) {
		this.processDaoException(t, "Error adding contents for sent newsletter", "addContentReports");
	} finally {
		closeDaoResources(null, stat);
	}
}
 

/**
 * De-Serialize a BatchUpdateException from JDBC 4.0 and make sure you can
 * read it back properly
 */
@Test
public void test13() throws Exception {
    String reason1 = "This was the error msg";
    String state1 = "user defined sqlState";
    String cause1 = "java.lang.Throwable: throw 1";
    int errorCode1 = 99999;
    Throwable t = new Throwable("throw 1");
    int[] uc1 = {1, 2, 21};
    long[] luc1 = {1, 2, 21};

    ObjectInputStream ois = new ObjectInputStream(
            new ByteArrayInputStream(SerializedBatchUpdateException.DATA));
    BatchUpdateException bue = (BatchUpdateException) ois.readObject();
    assertTrue(reason1.equals(bue.getMessage())
            && bue.getSQLState().equals(state1)
            && bue.getErrorCode() == errorCode1
            && cause1.equals(bue.getCause().toString())
            && Arrays.equals(bue.getLargeUpdateCounts(), luc1)
            && Arrays.equals(bue.getUpdateCounts(), uc1));
}
 

/**
 * Validate that the ordering of the returned Exceptions is correct
 * using traditional while loop
 */
@Test
public void test16() {
    BatchUpdateException ex = new BatchUpdateException("Exception 1", uc,  t1);
    BatchUpdateException ex1 = new BatchUpdateException("Exception 2", uc);
    BatchUpdateException ex2 = new BatchUpdateException("Exception 3", uc, t2);
    ex.setNextException(ex1);
    ex.setNextException(ex2);
    SQLException sqe = ex;
    int num = 0;
    while (sqe != null) {
        assertTrue(msgs[num++].equals(sqe.getMessage()));
        Throwable c = sqe.getCause();
        while (c != null) {
            assertTrue(msgs[num++].equals(c.getMessage()));
            c = c.getCause();
        }
        sqe = sqe.getNextException();
    }
}
 

@Test
public void testBatchUpdateWithBatchFailure() throws Exception {
	final String[] sql = {"A", "B", "C", "D"};
	given(this.statement.executeBatch()).willThrow(
			new BatchUpdateException(new int[] { 1, Statement.EXECUTE_FAILED, 1,
				Statement.EXECUTE_FAILED }));
	mockDatabaseMetaData(true);
	given(this.connection.createStatement()).willReturn(this.statement);

	JdbcTemplate template = new JdbcTemplate(this.dataSource, false);
	try {
		template.batchUpdate(sql);
	}
	catch (UncategorizedSQLException ex) {
		assertThat(ex.getSql(), equalTo("B; D"));
	}
}
 

/**
 * Verifies exception thrown if COPY is added into a packet.
 *
 * @throws SQLException If failed.
 */
@Test
public void testMultipleStatement() throws SQLException {
    GridTestUtils.assertThrows(log, new Callable<Object>() {
        @Override public Object call() throws Exception {
            stmt.addBatch(BASIC_SQL_COPY_STMT);

            stmt.addBatch("copy from '" + BULKLOAD_ONE_LINE_CSV_FILE + "' into " + TBL_NAME +
                " (_key, age, firstName, lastName)" +
                " format csv");

            stmt.addBatch("copy from '" + BULKLOAD_UTF8_CSV_FILE + "' into " + TBL_NAME +
                " (_key, age, firstName, lastName)" +
                " format csv");

            stmt.executeBatch();

            return null;
        }
    }, BatchUpdateException.class, "COPY command cannot be executed in batch mode.");
}
 

protected void addWorkContentRelationsRecord(Content content, Connection conn) throws ApsSystemException {
	PreparedStatement stat = null;
	try {
		stat = conn.prepareStatement(ADD_WORK_CONTENT_REL_RECORD);
		this.addCategoryRelationsRecord(content, false, stat);
		stat.executeBatch();
	} catch (BatchUpdateException e) {
		_logger.error("Error saving record into workcontentrelations {}", content.getId(), e.getNextException());
		throw new RuntimeException("Error saving record into workcontentrelations " + content.getId(), e);
	} catch (Throwable t) {
		_logger.error("Error saving record into workcontentrelations {}", content.getId(), t);
		throw new RuntimeException("Error saving record into workcontentrelations " + content.getId(), t);
	} finally {
		closeDaoResources(null, stat);
	}
}
 

@Test
public void test_numeric_types_arrays() throws Exception {
    try (Connection conn = DriverManager.getConnection(url(RW), properties)) {
        conn.createStatement().executeUpdate(
            "CREATE TABLE t (" +
            "   ints array(int)," +
            "   floats array(float)" +
            ") " +
            "WITH (number_of_replicas = 0)");

        PreparedStatement preparedStatement = conn.prepareStatement(
            "INSERT INTO t (ints, floats) VALUES (?, ?)");
        preparedStatement.setArray(1, conn.createArrayOf("int4", new Integer[]{10, 20}));
        preparedStatement.setArray(2, conn.createArrayOf("float4", new Float[]{1.2f, 3.5f}));
        preparedStatement.executeUpdate();
        conn.createStatement().execute("REFRESH TABLE t");

        ResultSet rs = conn.createStatement().executeQuery("SELECT ints, floats FROM t");
        assertThat(rs.next(), is(true));
        assertThat(rs.getArray(1).getArray(), is(new Integer[]{10, 20}));
        assertThat(rs.getArray(2).getArray(), is(new Float[]{1.2f, 3.5f}));
    } catch (BatchUpdateException e) {
        throw e.getNextException();
    }
}
 

public static HopDatabaseBatchException createHopDatabaseBatchException( String message, SQLException ex ) {
  HopDatabaseBatchException kdbe = new HopDatabaseBatchException( message, ex );
  if ( ex instanceof BatchUpdateException ) {
    kdbe.setUpdateCounts( ( (BatchUpdateException) ex ).getUpdateCounts() );
  } else {
    // Null update count forces rollback of batch
    kdbe.setUpdateCounts( null );
  }
  List<Exception> exceptions = new ArrayList<>();
  SQLException nextException = ex.getNextException();
  SQLException oldException = null;

  // This construction is specifically done for some JDBC drivers, these
  // drivers
  // always return the same exception on getNextException() (and thus go
  // into an infinite loop).
  // So it's not "equals" but != (comments from Sven Boden).
  while ( ( nextException != null ) && ( oldException != nextException ) ) {
    exceptions.add( nextException );
    oldException = nextException;
    nextException = nextException.getNextException();
  }
  kdbe.setExceptionsList( exceptions );
  return kdbe;
}
 

/**
 * De-Serialize a BatchUpdateException from JDBC 4.0 and make sure you can
 * read it back properly
 */
@Test
public void test13() throws Exception {
    String reason1 = "This was the error msg";
    String state1 = "user defined sqlState";
    String cause1 = "java.lang.Throwable: throw 1";
    int errorCode1 = 99999;
    Throwable t = new Throwable("throw 1");
    int[] uc1 = {1, 2, 21};
    long[] luc1 = {1, 2, 21};

    ObjectInputStream ois = new ObjectInputStream(
            new ByteArrayInputStream(SerializedBatchUpdateException.DATA));
    BatchUpdateException bue = (BatchUpdateException) ois.readObject();
    assertTrue(reason1.equals(bue.getMessage())
            && bue.getSQLState().equals(state1)
            && bue.getErrorCode() == errorCode1
            && cause1.equals(bue.getCause().toString())
            && Arrays.equals(bue.getLargeUpdateCounts(), luc1)
            && Arrays.equals(bue.getUpdateCounts(), uc1));
}
 

/**
 * Validate that the ordering of the returned Exceptions is correct
 * using traditional while loop
 */
@Test
public void test16() {
    BatchUpdateException ex = new BatchUpdateException("Exception 1", uc,  t1);
    BatchUpdateException ex1 = new BatchUpdateException("Exception 2", uc);
    BatchUpdateException ex2 = new BatchUpdateException("Exception 3", uc, t2);
    ex.setNextException(ex1);
    ex.setNextException(ex2);
    SQLException sqe = ex;
    int num = 0;
    while (sqe != null) {
        assertTrue(msgs[num++].equals(sqe.getMessage()));
        Throwable c = sqe.getCause();
        while (c != null) {
            assertTrue(msgs[num++].equals(c.getMessage()));
            c = c.getCause();
        }
        sqe = sqe.getNextException();
    }
}
 

private static void testDiffError(JdbcTemplate jdbcTemplate) {
  String[] sqls = {
      "delete from test_0.types_bak where id = 2125",
      "delete from test_0.types_bak where id = 2122",
      "insert into `test_0`.`types_bak` (`double`,`varchar`,`char`,`tinyint`,`id`,`text`,`decimal`,`bigint`,`timestamp`) values (0.6055158,'D5v','k',26,2125,'/>$Kf',19265911.19,1366022492355224397,'2017-12-01 22:30:24.0')",
      "insert into `test_0`.`types_bak` (`double`,`varchar`,`char`,`tinyint`,`id`,`text`,`decimal`,`bigint`,`timestamp`) values (0.6055158,'D5v','k',26,2125,'/>$Kf',19265911.19,1366022492355224397,'2017-12-01 22:30:24.0')",
      "insert into `test_0`.`types_bak` (`double`,`varchar`,`char`,`tinyint`,`id`,`text`,`decimal`,`bigint`,`timestamp`) values (0.47148514,'v[e|','6P{N(hb=8C6!t5oAfLv2',161,2122,'Qria3&&V',19265911.19,3128612873388751949,'2005-06-07 08:46:12.0')",
      "insert into `test_0`.`not_exists` (`double`) VALUES (1)",
      };
  try {
    jdbcTemplate.batchUpdate(sqls);
  } catch (DataAccessException e) {
    int[] updateCounts = ((BatchUpdateException) e.getCause()).getUpdateCounts();
    System.out.println(Arrays.toString(updateCounts));
    e.printStackTrace();
  }
}
 

/**
 * Serialize a BatchUpdateException with an Integer.MAX_VALUE + 1 and
 * validate you can read it back properly
 */
@Test
public void test14() throws Exception {
    int[] uc1 = {Integer.MAX_VALUE, Integer.MAX_VALUE + 1};
    long[] luc1 = {Integer.MAX_VALUE, Integer.MAX_VALUE + 1};
    BatchUpdateException be = new BatchUpdateException(reason, state, errorCode,
            luc1, t);
            BatchUpdateException bue
            = createSerializedException(be);
    assertTrue(reason.equals(bue.getMessage())
            && bue.getSQLState().equals(state)
            && cause.equals(bue.getCause().toString())
            && bue.getErrorCode() == errorCode
            && Arrays.equals(bue.getLargeUpdateCounts(), luc1)
            && Arrays.equals(bue.getUpdateCounts(), uc1));
}
 

/**
 * 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);
    }
}
 

/**
 * Validate that the ordering of the returned Exceptions is correct
 * using traditional while loop
 */
@Test
public void test16() {
    BatchUpdateException ex = new BatchUpdateException("Exception 1", uc,  t1);
    BatchUpdateException ex1 = new BatchUpdateException("Exception 2", uc);
    BatchUpdateException ex2 = new BatchUpdateException("Exception 3", uc, t2);
    ex.setNextException(ex1);
    ex.setNextException(ex2);
    SQLException sqe = ex;
    int num = 0;
    while (sqe != null) {
        assertTrue(msgs[num++].equals(sqe.getMessage()));
        Throwable c = sqe.getCause();
        while (c != null) {
            assertTrue(msgs[num++].equals(c.getMessage()));
            c = c.getCause();
        }
        sqe = sqe.getNextException();
    }
}
 

/**
 * Add a record in the table 'dataobjectrelations' for every resource, page,
 * other dataobject, role and category associated to the given dataobject).
 *
 * @param dataobject The current dataobject.
 * @param conn The connection to the database.
 * @throws ApsSystemException when connection error are detected.
 */
protected void addDataObjectRelationsRecord(DataObject dataobject, Connection conn) throws ApsSystemException {
    PreparedStatement stat = null;
    try {
        stat = conn.prepareStatement(ADD_DATAOBJECT_REL_RECORD);
        this.addCategoryRelationsRecord(dataobject, true, stat);
        this.addGroupRelationsRecord(dataobject, stat);
        EntityAttributeIterator attributeIter = new EntityAttributeIterator(dataobject);
        while (attributeIter.hasNext()) {
            AttributeInterface currAttribute = (AttributeInterface) attributeIter.next();
        }
        stat.executeBatch();
    } catch (BatchUpdateException e) {
        _logger.error("Error saving record into dataobjectrelations {}", dataobject.getId(), e.getNextException());
        throw new RuntimeException("Error saving record into dataobjectrelations " + dataobject.getId(), e.getNextException());
    } catch (Throwable t) {
        _logger.error("Error saving record into dataobjectrelations {}", dataobject.getId(), t);
        throw new RuntimeException("Error saving record into dataobjectrelations " + dataobject.getId(), t);
    } finally {
        closeDaoResources(null, stat);
    }
}
 

protected SQLException handleExceptionForBatch(int endOfBatchIndex, int numValuesPerBatch, long[] updateCounts, SQLException ex)
        throws BatchUpdateException, SQLException {
    for (int j = endOfBatchIndex; j > endOfBatchIndex - numValuesPerBatch; j--) {
        updateCounts[j] = EXECUTE_FAILED;
    }

    if (this.continueBatchOnError && !(ex instanceof MySQLTimeoutException) && !(ex instanceof MySQLStatementCancelledException)
            && !hasDeadlockOrTimeoutRolledBackTx(ex)) {
        return ex;
    } // else: throw the exception immediately

    long[] newUpdateCounts = new long[endOfBatchIndex];
    System.arraycopy(updateCounts, 0, newUpdateCounts, 0, endOfBatchIndex);

    throw SQLError.createBatchUpdateException(ex, newUpdateCounts, getExceptionInterceptor());
}
 

protected SQLException handleExceptionForBatch(int endOfBatchIndex, int numValuesPerBatch, long[] updateCounts, SQLException ex)
        throws BatchUpdateException, SQLException {
    for (int j = endOfBatchIndex; j > endOfBatchIndex - numValuesPerBatch; j--) {
        updateCounts[j] = EXECUTE_FAILED;
    }

    if (this.continueBatchOnError && !(ex instanceof MySQLTimeoutException) && !(ex instanceof MySQLStatementCancelledException)
            && !hasDeadlockOrTimeoutRolledBackTx(ex)) {
        return ex;
    } // else: throw the exception immediately

    long[] newUpdateCounts = new long[endOfBatchIndex];
    System.arraycopy(updateCounts, 0, newUpdateCounts, 0, endOfBatchIndex);

    throw SQLError.createBatchUpdateException(ex, newUpdateCounts, getExceptionInterceptor());
}
 

/**
 * De-Serialize a BatchUpdateException from JDBC 4.0 and make sure you can
 * read it back properly
 */
@Test
public void test13() throws Exception {
    String reason1 = "This was the error msg";
    String state1 = "user defined sqlState";
    String cause1 = "java.lang.Throwable: throw 1";
    int errorCode1 = 99999;
    Throwable t = new Throwable("throw 1");
    int[] uc1 = {1, 2, 21};
    long[] luc1 = {1, 2, 21};

    ObjectInputStream ois = new ObjectInputStream(
            new ByteArrayInputStream(SerializedBatchUpdateException.DATA));
    BatchUpdateException bue = (BatchUpdateException) ois.readObject();
    assertTrue(reason1.equals(bue.getMessage())
            && bue.getSQLState().equals(state1)
            && bue.getErrorCode() == errorCode1
            && cause1.equals(bue.getCause().toString())
            && Arrays.equals(bue.getLargeUpdateCounts(), luc1)
            && Arrays.equals(bue.getUpdateCounts(), uc1));
}
 

/**
 * Extract batching error from general exception.
 * @param e Exception
 * @param rowsAffected List containing the number of affected rows for every query in batch.
 * @param err Error tuple containing error code and error message.
 */
private static void extractBatchError(Exception e, List<Long> rowsAffected, IgniteBiTuple<Integer, String> err) {
    if (e instanceof IgniteSQLException) {
        BatchUpdateException batchCause = X.cause(e, BatchUpdateException.class);

        if (batchCause != null) {
            if (rowsAffected != null) {
                for (long cnt : batchCause.getLargeUpdateCounts())
                    rowsAffected.add(cnt);
            }

            err.set(batchCause.getErrorCode(), batchCause.getMessage());
        }
        else
            err.set(((IgniteSQLException)e).statusCode(), OdbcUtils.tryRetrieveH2ErrorMessage(e));
    }
    else
        err.set(IgniteQueryErrorCode.UNKNOWN, e.getMessage());
}
 

public void insertIntoTable(String tableName, List<Row> rows, boolean emptyStringToNull) {
	List<String> columns = rows.get(0).getFieldNames();
	String sql = "INSERT INTO " + tableName;
	sql = sql + " (" + StringUtilities.join(columns, ",") + ")";
	sql = sql + " VALUES (?";
	for (int i = 1; i < columns.size(); i++)
		sql = sql + ",?";
	sql = sql + ")";
	try {
		connection.setAutoCommit(false);
		PreparedStatement statement = connection.prepareStatement(sql);
		for (Row row : rows) {
			for (int i = 0; i < columns.size(); i++) {
				String value = row.get(columns.get(i));
				if (value == null)
					System.out.println(row.toString());
				if (value.length() == 0 && emptyStringToNull)
					value = null;
				if (dbType.equals(DbType.POSTGRESQL)) // PostgreSQL does not allow unspecified types
					statement.setObject(i + 1, value, Types.OTHER);
				else if (dbType.equals(DbType.ORACLE)) {
					statement.setString(i + 1, value);
				} else
					statement.setString(i + 1, value);
			}
			statement.addBatch();
		}
		statement.executeBatch();
		connection.commit();
		statement.close();
		connection.setAutoCommit(true);
		connection.clearWarnings();
	} catch (SQLException e) {
		e.printStackTrace();
		if (e instanceof BatchUpdateException) {
			System.err.println(((BatchUpdateException) e).getNextException().getMessage());
		}
	}
}
 

/**
 * Create BatchUpdateException with update counts
 */
@Test
public void test4() {
    BatchUpdateException ex = new BatchUpdateException(uc);
    assertTrue(ex.getMessage() == null
            && ex.getSQLState() == null
            && ex.getCause() == null
            && ex.getErrorCode() == 0
            && Arrays.equals(ex.getUpdateCounts(), uc)
            && Arrays.equals(ex.getLargeUpdateCounts(), luc)
    );
}
 

/**
 * Validate that a copy of the update counts array is made
 */
@Test
public void test10() {
    int[] uc1 = {1, 2};
    BatchUpdateException ex = new BatchUpdateException(uc1);
    assertTrue(Arrays.equals(ex.getUpdateCounts(), uc1));
    uc1[0] = 6689;
    assertFalse(Arrays.equals(ex.getUpdateCounts(), uc1));
}
 

/**
 * Create BatchUpdateException with message, SQLState, errorCode code
 * Throwable, and long [] update counts
 */
@Test
public void test9() {
    BatchUpdateException ex = new BatchUpdateException(reason, state, errorCode,
            luc, t);
    assertTrue(ex.getMessage().equals(reason)
            && ex.getSQLState().equals(state)
            && cause.equals(ex.getCause().toString())
            && ex.getErrorCode() == errorCode
            && Arrays.equals(ex.getUpdateCounts(), uc)
            && Arrays.equals(ex.getLargeUpdateCounts(), luc)
    );
}
 

/**
 * Create BatchUpdateException with message, SQLState, errorCode code
 * Throwable, and long [] update counts
 */
@Test
public void test9() {
    BatchUpdateException ex = new BatchUpdateException(reason, state, errorCode,
            luc, t);
    assertTrue(ex.getMessage().equals(reason)
            && ex.getSQLState().equals(state)
            && cause.equals(ex.getCause().toString())
            && ex.getErrorCode() == errorCode
            && Arrays.equals(ex.getUpdateCounts(), uc)
            && Arrays.equals(ex.getLargeUpdateCounts(), luc)
    );
}
 

/**
 * Create BatchUpdateException with message and update counts
 */
@Test
public void test3() {

    BatchUpdateException ex = new BatchUpdateException(reason, uc);
    assertTrue(ex.getMessage().equals(reason)
            && ex.getSQLState() == null
            && ex.getCause() == null
            && ex.getErrorCode() == 0
            && Arrays.equals(ex.getUpdateCounts(), uc)
            && Arrays.equals(ex.getLargeUpdateCounts(), luc)
    );
}
 

/**
 * Create BatchUpdateException with update counts
 */
@Test
public void test4() {
    BatchUpdateException ex = new BatchUpdateException(uc);
    assertTrue(ex.getMessage() == null
            && ex.getSQLState() == null
            && ex.getCause() == null
            && ex.getErrorCode() == 0
            && Arrays.equals(ex.getUpdateCounts(), uc)
            && Arrays.equals(ex.getLargeUpdateCounts(), luc)
    );
}
 

/**
 *
 */
@Test
public void testErrorNestedTxAutocommitOffBatched() {
    GridTestUtils.assertThrows(null, new Callable<Void>() {
        @Override public Void call() throws Exception {
            try (Connection c = c(false, NestedTxMode.ERROR)) {
                doNestedTxStart(c, true);
            }

            throw new AssertionError();
        }
    }, BatchUpdateException.class, "Transaction has already been started.");
}
 

/**
 * Create BatchUpdateException with message and update counts
 */
@Test
public void test3() {

    BatchUpdateException ex = new BatchUpdateException(reason, uc);
    assertTrue(ex.getMessage().equals(reason)
            && ex.getSQLState() == null
            && ex.getCause() == null
            && ex.getErrorCode() == 0
            && Arrays.equals(ex.getUpdateCounts(), uc)
            && Arrays.equals(ex.getLargeUpdateCounts(), luc)
    );
}
 

/**
 * Create BatchUpdateException with message, SQLState, errorCode code
 * Throwable, and update counts
 */
@Test
public void test8() {
    BatchUpdateException ex = new BatchUpdateException(reason, state, errorCode,
            uc, t);
    assertTrue(ex.getMessage().equals(reason)
            && ex.getSQLState().equals(state)
            && cause.equals(ex.getCause().toString())
            && ex.getErrorCode() == errorCode
            && Arrays.equals(ex.getUpdateCounts(), uc)
            && Arrays.equals(ex.getLargeUpdateCounts(), luc)
    );
}