batchupdate.java

derby database source code.good for you.

		try
		{
      //trying select as the first statement
      System.out.println("Negative Statement: statement testing select as first stat in the batch");
      stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
      stmt.addBatch("insert into t1 values(1)");
      updateCount = stmt.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();

		try
		{
      //trying select as the nth statement
      System.out.println("Negative Statement: statement testing select as nth stat in the batch");
      stmt.addBatch("insert into t1 values(1)");
      stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
      stmt.addBatch("insert into t1 values(1)");
      updateCount = stmt.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.length != 1) {
          System.out.println("ERROR: Select 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 Statement: statement testing select as last stat in the batch");
      stmt.addBatch("insert into t1 values(1)");
      stmt.addBatch("insert into t1 values(1)");
      stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
      updateCount = stmt.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.length != 2) {
          System.out.println("ERROR: Select 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) != 3) {
      System.out.println("There should been 3 row in the table, but found " + rs.getInt(1) + " rows");
      passed = false;
    }
    rs.close();

    conn.rollback();

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 0) {
      System.out.println("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 runStatementNonBatchStuffInBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[] = null;
    ResultSet rs;

		try
		{
      //trying execute after addBatch
      System.out.println("Negative Statement: statement testing execute in the middle of batch");
      stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
      stmt.execute("insert into t1 values(1)");
      stmt.addBatch("insert into t1 values(1)");
      updateCount = stmt.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 after addBatch
      System.out.println("Negative Statement: statement testing executeQuery in the middle of batch");
      stmt.addBatch("insert into t1 values(1)");
      stmt.executeQuery("SELECT * FROM SYS.SYSTABLES");
      updateCount = stmt.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("There should been no rows in the table, but found " + rs.getInt(1) + " rows");
      passed = false;
    }
    rs.close();

		try
		{
      //trying executeUpdate after addBatch
      System.out.println("Negative Statement: statement testing executeUpdate in the middle of batch");
      stmt.addBatch("insert into t1 values(1)");
      stmt.executeUpdate("insert into t1 values(1)");
      stmt.addBatch("insert into t1 values(1)");
      stmt.addBatch("SELECT * FROM SYS.SYSCOLUMNS");
      updateCount = stmt.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("There should been no rows in the table, but found " + rs.getInt(1) + " rows");
      passed = false;
    }
    rs.close();

    conn.rollback();

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 0) {
      System.out.println("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 batches with various rollback and commit combinations.
  static boolean runPreparedStatRollbackAndCommitCombinations(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[];
    ResultSet rs;

    System.out.println("Positive Prepared Stat: batch, rollback, batch and commit combinations");
    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    updateCount = pStmt.executeBatch();

    if (updateCount.length != 2) {
      System.out.println("ERROR: there were 2 statements in the batch");
      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;
      }
    }

    conn.rollback();

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 0) {
      System.out.println("ERROR: There should have been 0 rows");
      passed = false;
    }
    rs.close();

    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    updateCount = pStmt.executeBatch();

    if (updateCount.length != 2) {
      System.out.println("ERROR: there were 2 statements in the batch");
      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;
      }
    }

    conn.commit();

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 2) {
      System.out.println("ERROR: There should have been 2 rows");
      passed = false;
    }
    rs.close();

    //try batch and commit
    System.out.println("Positive Prepared Stat: batch and commit combinations");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    updateCount = pStmt.executeBatch();

    if (updateCount.length != 2) {
      System.out.println("ERROR: there were 2 statements in the batch");
      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;
      }
    }

    conn.commit();

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 4) {
      System.out.println("ERROR: There should have been 4 rows");
      passed = false;
    }
    rs.close();

    //try batch, batch and rollback
    System.out.println("Positive Prepared Stat: batch, batch and rollback combinations");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    updateCount = pStmt.executeBatch();

    if (updateCount.length != 2) {
      System.out.println("ERROR: there were 2 statements in the batch");
      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;
      }
    }

    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    updateCount = pStmt.executeBatch();

    if (updateCount.length != 2) {
      System.out.println("ERROR: there were 2 statements in the batch");
      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;
      }
    }

    conn.rollback();

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 4) {
      System.out.println("ERROR: There should have been 4 rows");
      passed = false;
    }
    rs.close();

    //try batch, batch and commit
    System.out.println("Positive Prepared Stat: batch, batch and commit combinations");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    updateCount = pStmt.executeBatch();

    if (updateCount.length != 2) {
      System.out.println("ERROR: there were 2 statements in the batch");
      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;
      }
    }

    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    updateCount = pStmt.executeBatch();

    if (updateCount.length != 2) {
      System.out.println("ERROR: there were 2 statements in the batch");
      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;
      }
    }

    conn.commit();

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 8) {
      System.out.println("ERROR: There should have been 8 rows");
      passed = false;
    }
    rs.close();

    pStmt.close();

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

  //try executing batches with various rollback and commit combinations.
  static boolean runRollbackAndCommitCombinations(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[];
    ResultSet rs;