batchupdate.java

derby database source code.good for you.

    System.out.println("Positive Statement: batch, rollback, batch and commit combinations");
    stmt.addBatch("insert into t1 values(1)");
    stmt.addBatch("insert into t1 values(1)");
    updateCount = stmt.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();

    stmt.addBatch("insert into t1 values(1)");
    stmt.addBatch("insert into t1 values(1)");
    updateCount = stmt.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 Statement: batch and commit combinations");
    stmt.addBatch("insert into t1 values(1)");
    stmt.addBatch("insert into t1 values(1)");
    updateCount = stmt.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 Statement: batch, batch and rollback combinations");
    stmt.addBatch("insert into t1 values(1)");
    stmt.addBatch("insert into t1 values(1)");
    updateCount = stmt.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;
      }
    }

    stmt.addBatch("insert into t1 values(1)");
    stmt.addBatch("insert into t1 values(1)");
    updateCount = stmt.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 Statement: batch, batch and rollback combinations");
    stmt.addBatch("insert into t1 values(1)");
    stmt.addBatch("insert into t1 values(1)");
    updateCount = stmt.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;
      }
    }

    stmt.addBatch("insert into t1 values(1)");
    stmt.addBatch("insert into t1 values(1)");
    updateCount = stmt.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();

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

  //try prepared statement batch with autocommit true
  static boolean runAutoCommitTruePreparedStatBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[];
    ResultSet rs;

		conn.setAutoCommit(true);
   //prepared statement batch with autocommit true
    System.out.println("Positive Prepared Stat: testing batch with autocommit true");
    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    updateCount = pStmt.executeBatch();

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

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

    //turn it true again after the above negative test
		conn.setAutoCommit(false);

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

    //try batch with autocommit true
  static boolean runAutoCommitTrueBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[];
    ResultSet rs;

		conn.setAutoCommit(true);
     //try batch with autocommit true
    System.out.println("Positive Statement: statement testing batch with autocommit true");
    stmt.addBatch("insert into t1 values(1)");
    stmt.addBatch("insert into t1 values(1)");
    stmt.addBatch("delete from t1");
    updateCount = stmt.executeBatch();

    if (updateCount.length != 3) {
      System.out.println("ERROR: there were 3 statements in the batch");
      passed = false;
    }

    for (int i=0; i<(updateCount.length-1); 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;
      }
    }
    if (updateCount[2] != 2) {
      System.out.println("ERROR: update count for stat 2 should have been 2 but it is " + updateCount[2]);
      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();

    //turn it true again after the above negative test
		conn.setAutoCommit(false);

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

  //try callable statements with output parameters
  static boolean runCallableStatementWithOutputParamBatch(Connection conn) throws SQLException {
    boolean passed = true;
    int updateCount[] = null;
    ResultSet rs;

    System.out.println("Negative Callable Statement: callable statement with output parameters in the batch");
		Statement s = conn.createStatement();

		s.execute("CREATE PROCEDURE takesString(OUT P1 VARCHAR(40), IN P2 INT) " +
				"EXTERNAL NAME 'org.apache.derbyTesting.functionTests.tests.lang.outparams.takesString'" +
				" NO SQL LANGUAGE JAVA PARAMETER STYLE JAVA");

  	CallableStatement cs = conn.prepareCall("call takesString(?,?)");
		try
		{

		cs.registerOutParameter(1, Types.CHAR);
		cs.setInt(2, Types.INTEGER);
    cs.addBatch();
		System.out.println("FAIL - addBatch() allowed with registered out parameter");
		passed = false;
		}
		catch (SQLException sqle)
		{
			// Check to be sure the exception is callback related
			passed = passed && checkException(sqle, "XJ04C");
		}

    cs.close();
				s.execute("drop procedure takesString");
				s.close();
    conn.rollback();
    conn.commit();
    return passed;
  }

  //try callable statements
  static boolean runCallableStatementBatch(Connection conn) throws SQLException {
    boolean passed = true;
    int updateCount[] = null;
    ResultSet rs;

    System.out.println("Positive Callable Statement: statement testing callable statement batch");
  	CallableStatement cs = conn.prepareCall("insert into t1 values(?)");

	cs.setInt(1, 1);
    cs.addBatch();
	cs.setInt(1,2);
    cs.addBatch();
	try
	{
		passed = passed && executeBatchCallableStatement(cs);
	}
	catch (SQLException sqle)
	{
		/* Check to be sure the exception is callback related */
		passed = passed && checkException(sqle, "XJ04C");
        if (sqle instanceof BatchUpdateException) 
		{
			updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
			if (updateCount != null) 
			{
				if (updateCount.length != 0) 
				{
					System.out.println("ERROR: callable statement has output parameter, so there shouldn't have been any update count");
              		passed = false;
            	}
          	} 
      	}
	}

	cleanUpCallableStatement(conn, cs, "t1");

	/* Bug 2813 - verify setXXXX() works with
	 * Date, Time and Timestamp on CallableStatement.
	 */
	cs = conn.prepareCall("insert into datetab values(?)");

	cs.setDate(1, Date.valueOf("1990-05-05"));
    cs.addBatch();
	cs.setDate(1,Date.valueOf("1990-06-06"));
    cs.addBatch();
	try
	{
		passed = passed && executeBatchCallableStatement(cs);
	}
	catch (SQLException sqle)
	{
		/* Check to be sure the exception is callback related */
		passed = passed && checkException(sqle, "XJ04C");
        if (sqle instanceof BatchUpdateException) 
		{
			updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
			if (updateCount != null) 
			{
				if (updateCount.length != 0) 
				{
					System.out.println("ERROR: callable statement has output parameter, so there shouldn't have been any update count");
              		passed = false;
            	}
          	} 
      	}
	}

	cleanUpCallableStatement(conn, cs, "datetab");
 
	cs = conn.prepareCall("insert into timetab values(?)");

	cs.setTime(1, Time.valueOf("11:11:11"));
    cs.addBatch();
	cs.setTime(1, Time.valueOf("12:12:12"));
    cs.addBatch();
	try
	{
		passed = passed && executeBatchCallableStatement(cs);
	}
	catch (SQLException sqle)
	{
		/* Check to be sure the exception is callback related */
		passed = passed && checkException(sqle, "XJ04C");
        if (sqle instanceof BatchUpdateException) 
		{
			updateCount = ((BatchUpdateException)sqle).getUpdateCounts();
			if (updateCount != null) 
			{
				if (updateCount.length != 0) 
				{
					System.out.println("ERROR: callable statement has output parameter, so there shouldn't have been any update count");
              		passed = false;
            	}
          	} 
      	}
	}

	cleanUpCallableStatement(conn, cs, "timestamptab");
 
	cs = conn.prepareCall("insert into timestamptab values(?)");

	cs.setTimestamp(1, Timestamp.valueOf("1990-05-05 11:11:11.1"));
    cs.addBatch();
	cs.setTimestamp(1, Timestamp.valueOf("1992-07-07 12:12:12.2"));
    cs.addBatch();
	try
	{
		passed = passed && executeBatchCallableStatement(cs);