batchupdate.java

derby database source code.good for you.

		} catch (SQLException sqle) {
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "22003");
      if (sqle instanceof BatchUpdateException) {
        updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
        if (updateCount.length != 1) {
          System.out.println("ERROR: Overflow is second statement in the batch, so there should have been only 1 update count");
          passed = false;
        }
        for (int i=0; i<updateCount.length; i++) {
          if (updateCount[i] != 1) {
            System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]);
            passed = false;
          }
        }
      }
		}

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 1) {
      System.out.println("There should been 1 row in the table, but found " + rs.getInt(1) + " rows");
      passed = false;
    }
    rs.close();

		try
		{
      //trying select as the last statement
      System.out.println("Negative Prepared Stat: testing overflow as last set of values");
      pStmt = conn.prepareStatement("update t1 set c1=(? + 1)");
      pStmt.setInt(1, 1);
      pStmt.addBatch();
      pStmt.setInt(1, 1);
      pStmt.addBatch();
      pStmt.setInt(1, java.lang.Integer.MAX_VALUE);
      pStmt.addBatch();
      updateCount = pStmt.executeBatch();
			passed = false;
		}
		catch (SQLException sqle) {
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "22003");
      if (sqle instanceof BatchUpdateException) {
        updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
        if (updateCount.length != 2) {
          System.out.println("ERROR: Overflow is last statement in the batch, so there should have been only 2 update count");
          passed = false;
        }
        for (int i=0; i<updateCount.length; i++) {
          if (updateCount[i] != 1) {
            System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]);
            passed = false;
          }
        }
      }
		}

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 1) {
      System.out.println("There should been 1 row in the table, but found " + rs.getInt(1) + " rows");
      passed = false;
    }
    rs.close();
    pStmt.close();

    stmt.executeUpdate("delete from t1");
    conn.commit();
    return(passed);
  }

  //Below trying various placements of overflow update statement in the batch, ie
  //as 1st stmt, nth stat and last stmt
  static boolean runStatementWithErrorsBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[] = null;
    ResultSet rs;

    stmt.executeUpdate("insert into t1 values(1)");

		try
		{
      //trying select as the first statement
      System.out.println("Negative Statement: statement testing overflow error as first stat in the batch");
      stmt.addBatch("update t1 set c1=2147483647 + 1");
      stmt.addBatch("insert into t1 values(1)");
      updateCount = stmt.executeBatch();
			passed = false;
		} catch (SQLException sqle) {
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "22003");
      if (sqle instanceof BatchUpdateException) {
        updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
        if (updateCount != null) {
          if (updateCount.length != 0) {
            System.out.println("ERROR: Overflow is first statement in the batch, so there shouldn't have been any update count");
            passed = false;
          }
        } 
      }
		}

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 1) {
      System.out.println("ERROR: There should been 1 row in the table, but found " + rs.getInt(1) + " rows");
      passed = false;
    }
    rs.close();

		try
		{
      //trying select as the nth statement
      System.out.println("Negative Statement: statement testing overflow error as nth stat in the batch");
      stmt.addBatch("insert into t1 values(1)");
      stmt.addBatch("update t1 set c1=2147483647 + 1");
      stmt.addBatch("insert into t1 values(1)");
      updateCount = stmt.executeBatch();
			passed = false;
		} catch (SQLException sqle) {
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "22003");
      if (sqle instanceof BatchUpdateException) {
        updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
        if (updateCount.length != 1) {
          System.out.println("ERROR: Update is second statement in the batch, so there should have been only 1 update count");
          passed = false;
        }
        for (int i=0; i<updateCount.length; i++) {
          if (updateCount[i] != 1) {
            System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]);
            passed = false;
          }
        }
      }
		}

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 2) {
      System.out.println("There should been 2 row in the table, but found " + rs.getInt(1) + " rows");
      passed = false;
    }
    rs.close();

		try
		{
      //trying select as the last statement
      System.out.println("Negative Statement: statement testing overflow error as last stat in the batch");
      stmt.addBatch("insert into t1 values(1)");
      stmt.addBatch("insert into t1 values(1)");
      stmt.addBatch("update t1 set c1=2147483647 + 1");
      updateCount = stmt.executeBatch();
			passed = false;
		}
		catch (SQLException sqle) {
			/* Check to be sure the exception is the one we expect */
			passed = passed && checkException(sqle, "22003");
      if (sqle instanceof BatchUpdateException) {
        updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
        if (updateCount.length != 2) {
          System.out.println("ERROR: Update is last statement in the batch, so there should have been only 2 update count");
          passed = false;
        }
        for (int i=0; i<updateCount.length; i++) {
          if (updateCount[i] != 1) {
            System.out.println("ERROR: update count for stat " + i + "should have been 1 but it is " + updateCount[i]);
            passed = false;
          }
        }
      }
		}

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 4) {
      System.out.println("There should been 4 rows in the table, but found " + rs.getInt(1) + " rows");
      passed = false;
    }
    rs.close();

    stmt.executeUpdate("delete from t1");
    conn.commit();
    return(passed);
  }

  //try transaction error, in this particular case time out while getting the lock
  static boolean runTransactionErrorPreparedStmtBatch(Connection conn, Statement stmt,
    Connection conn2, Statement stmt2) throws SQLException {
    boolean passed = true;
    int updateCount[] = null;
    ResultSet rs;

		try
		{
      System.out.println("Negative Prepared Stat: testing transaction error, time out while getting the lock");

      stmt.execute("insert into t1 values(1)");
      stmt2.execute("insert into t1 values(2)");

      PreparedStatement pStmt1 = conn.prepareStatement("update t1 set c1=3 where c1=?");
      pStmt1.setInt(1, 2);
      pStmt1.addBatch();

      PreparedStatement pStmt2 = conn.prepareStatement("update t1 set c1=4 where c1=?");
      pStmt2.setInt(1, 1);
      pStmt2.addBatch();

      pStmt1.executeBatch();
      pStmt2.executeBatch();
		} catch (SQLException sqle) {
			/* Check to be sure the exception is time out while getting the lock related */
			passed = passed && checkException(sqle, "40XL1");
      if (sqle instanceof BatchUpdateException) {
        updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
        if (updateCount != null) {
          if (updateCount.length != 0) {
            System.out.println("ERROR: first statement in the batch caused time out while getting the lock, so there shouldn't have been any update count");
            passed = false;
          }
        }
      }
		}

    conn.rollback();
    conn2.rollback();
    stmt.executeUpdate("delete from t1");
    conn.commit();
    return passed;
  }

  //try transaction error, in this particular case time out while getting the lock
  static boolean runTransactionErrorBatch(Connection conn, Statement stmt,
    Connection conn2, Statement stmt2) throws SQLException {
    boolean passed = true;
    int updateCount[] = null;
    ResultSet rs;

		try
		{
      System.out.println("Negative Statement: statement testing time out while getting the lock in the batch");

      stmt.execute("insert into t1 values(1)");
      stmt2.execute("insert into t1 values(2)");

      stmt.addBatch("update t1 set c1=3 where c1=2");
      stmt2.addBatch("update t1 set c1=4 where c1=1");

      stmt.executeBatch();
      updateCount = stmt2.executeBatch();
		} catch (SQLException sqle) {
			/* Check to be sure the exception is time out while getting the lock related */
			passed = passed && checkException(sqle, "40XL1");
      if (sqle instanceof BatchUpdateException) {
        updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
        if (updateCount != null) {
          if (updateCount.length != 0) {
            System.out.println("ERROR: first statement in the batch caused time out while getting the lock, so there shouldn't have been any update count");
            passed = false;
          }
        }
      }
		}

    conn.rollback();
    conn2.rollback();
    stmt.clearBatch();
    stmt2.clearBatch();
    stmt.executeUpdate("delete from t1");
    conn.commit();
    return passed;
  }

  //statements which will return a resultset are not allowed in batch update
  //the following case should throw an exception for select.
  static boolean runPreparedStmtWithResultSetBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[] = null;
    ResultSet rs;

		try
		{
      //trying select as the first statement
      System.out.println("Negative Prepared Stat: testing select in the batch");
      PreparedStatement pStmt = conn.prepareStatement("select * from t1 where c1=?");
      pStmt.setInt(1, 1);
      pStmt.addBatch();
      updateCount = pStmt.executeBatch();
			passed = false;
		} catch (SQLException sqle) {
			/* Check to be sure the exception is the ResultSetReturnNotAllowed */
			passed = passed && checkException(sqle, "X0Y79");
        if (sqle instanceof BatchUpdateException) {
          updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
          if (updateCount != null) {
            if (updateCount.length != 0) {
              System.out.println("ERROR: Select is first statement in the batch, so there shouldn't have been any update count");
              passed = false;
            }
          }
      }
		}

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 0) {
      System.out.println("ERROR: There should been no rows in the table, but found " + rs.getInt(1) + " rows");
      passed = false;
    }
    rs.close();

    stmt.executeUpdate("delete from t1");
    conn.commit();
    return passed;
  }

  //try executing a batch with regular statement intermingled.
  static boolean runPreparedStmtNonBatchStuffInBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[] = null;
    ResultSet rs;

		try
		{
      //trying execute in the middle of batch
      System.out.println("Negative Prepared Stat: testing execute in the middle of batch");
      PreparedStatement pStmt = conn.prepareStatement("select * from t1 where c1=?");
      pStmt.setInt(1, 1);
      pStmt.addBatch();
      pStmt.execute();
      updateCount = pStmt.executeBatch();
			passed = false;
		} catch (SQLException sqle) {
			/* Check to be sure the exception is the MIDDLE_OF_BATCH */
			passed = passed && checkException(sqle, "XJ068");
			// do clearBatch so we can proceed
			if (checkException(sqle, "XJ068"))
        stmt.clearBatch();
		}

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 0) {
      System.out.println("ERROR: There should been no rows in the table, but found " + rs.getInt(1) + " rows");
      passed = false;
    }
    rs.close();

		try
		{
      //trying executeQuery in the middle of batch
      System.out.println("Negative Prepared Stat: testing executeQuery in the middle of batch");
      PreparedStatement pStmt = conn.prepareStatement("select * from t1 where c1=?");
      pStmt.setInt(1, 1);
      pStmt.addBatch();
      pStmt.executeQuery();
      updateCount = pStmt.executeBatch();
			passed = false;
		} catch (SQLException sqle) {
			/* Check to be sure the exception is the MIDDLE_OF_BATCH */
			passed = passed && checkException(sqle, "XJ068");
			// do clearBatch so we can proceed
			if (checkException(sqle, "XJ068"))
        stmt.clearBatch();
		}

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 0) {
      System.out.println("ERROR: There should been no rows in the table, but found " + rs.getInt(1) + " rows");
      passed = false;
    }
    rs.close();

		try
		{
      //trying executeUpdate in the middle of batch
      System.out.println("Negative Prepared Stat: testing executeUpdate in the middle of batch");
      PreparedStatement pStmt = conn.prepareStatement("select * from t1 where c1=?");
      pStmt.setInt(1, 1);
      pStmt.addBatch();
      pStmt.executeUpdate();
      updateCount = pStmt.executeBatch();
			passed = false;
		} catch (SQLException sqle) {
			/* Check to be sure the exception is the MIDDLE_OF_BATCH */
			passed = passed && checkException(sqle, "XJ068");
			// do clearBatch so we can proceed
			if (checkException(sqle, "XJ068"))
        stmt.clearBatch();
		}

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 0) {
      System.out.println("ERROR: There should been no rows in the table, but found " + rs.getInt(1) + " rows");
      passed = false;
    }
    rs.close();

    stmt.executeUpdate("delete from t1");
    conn.commit();
    return passed;
  }

  //statements which will return a resultset are not allowed in batch update
  //the following case should throw an exception for select. Below trying
  //various placements of select statement in the batch, ie as 1st stmt,
  //nth stat and last stmt
  static boolean runStatementWithResultSetBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[] = null;
    ResultSet rs;