batchupdate.java

derby database source code.good for you.

	}
	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");

	// Try with a user type
	cs = conn.prepareCall("insert into usertypetab values(?)");

	cs.setObject(1, Date.valueOf("1990-05-05"));
    cs.addBatch();
	cs.setObject(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, "usertypetab");
 
	return passed;
	}

	private static boolean executeBatchCallableStatement(CallableStatement cs)
		throws SQLException
	{
		boolean passed = true;
		int updateCount[];

		updateCount = cs.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 should have been 1 but it's " + updateCount[i]);
				passed = false;
			}
		}

		return passed;
	}

	private static void cleanUpCallableStatement(Connection conn, CallableStatement cs, String tableName)
		throws SQLException
	{
		cs.close();
		conn.rollback();
		cs = conn.prepareCall("delete from " + tableName);
		cs.executeUpdate();
		cs.close();
		conn.commit();
	}

  //try combinations of clear batch.
  static boolean runCombinationsOfClearPreparedStatBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[];
    ResultSet rs;

    System.out.println("Positive Prepared Stat: add 3 statements, clear batch and execute batch");
    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 2);
    pStmt.addBatch();
    pStmt.setInt(1, 3);
    pStmt.addBatch();
    pStmt.clearBatch();
    updateCount = pStmt.executeBatch();

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

    System.out.println("Positive Prepared Stat: add 3 statements, clear batch, add 3 and execute batch");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 2);
    pStmt.addBatch();
    pStmt.setInt(1, 3);
    pStmt.addBatch();
    pStmt.clearBatch();
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 2);
    pStmt.addBatch();
    pStmt.setInt(1, 3);
    pStmt.addBatch();
    updateCount = pStmt.executeBatch();

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

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

  //try combinations of clear batch.
  static boolean runCombinationsOfClearBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[];
    ResultSet rs;

    System.out.println("Positive Statement: add 3 statements, clear batch and execute batch");
    stmt.addBatch("insert into t1 values(2)");
    stmt.addBatch("insert into t1 values(2)");
    stmt.addBatch("insert into t1 values(2)");
    stmt.clearBatch();
    updateCount = stmt.executeBatch();

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

    System.out.println("Positive Statement: add 3 statements, clear batch, add 3 and execute batch");
    stmt.addBatch("insert into t1 values(2)");
    stmt.addBatch("insert into t1 values(2)");
    stmt.addBatch("insert into t1 values(2)");
    stmt.clearBatch();
    stmt.addBatch("insert into t1 values(2)");
    stmt.addBatch("insert into t1 values(2)");
    stmt.addBatch("insert into t1 values(2)");
    updateCount = stmt.executeBatch();

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

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

  //try executing a batch with 1000 statements in it.
  static boolean run1000ValueSetPreparedBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[];
    ResultSet rs;

    System.out.println("Positive Prepared Stat: 1000 parameter set batch");
    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)");
    for (int i=0; i<1000; i++){
      pStmt.setInt(1, 1);
      pStmt.addBatch();
    }
    updateCount = pStmt.executeBatch();
    
    if (updateCount.length != 1000) {
      System.out.println("ERROR: there were 1000 parameter sets in the batch");
      passed = false;
    }

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

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

  //try executing a batch with 1000 statements in it.
  static boolean run1000StatementsBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[];
    ResultSet rs;

    System.out.println("Positive Statement: 1000 statements batch");
    for (int i=0; i<1000; i++){
      stmt.addBatch("insert into t1 values(1)");
    }
    updateCount = stmt.executeBatch();
    
    if (updateCount.length != 1000) {
      System.out.println("ERROR: there were 1000 statements in the batch");
      passed = false;
    }

    rs = stmt.executeQuery("select count(*) from t1");
    rs.next();
    if(rs.getInt(1) != 1000) {
      System.out.println("There should been 1000 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 3 different parameter sets in it.
  static boolean runMultipleValueSetPreparedBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[];
    ResultSet rs;

    //try prepared statement batch with just one set of values
    System.out.println("Positive Prepared Stat: set 3 set of parameter values and run the batch");
    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    pStmt.setInt(1, 2);
    pStmt.addBatch();
    pStmt.setInt(1, 3);
    pStmt.addBatch();
    updateCount = pStmt.executeBatch();
    if (updateCount.length != 3) {
      System.out.println("ERROR: there were 3 parameter sets 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.close();

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

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

  //try executing a batch with 3 different statements in it.
  static boolean runMultipleStatementsBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[];
    ResultSet rs;

    System.out.println("Positive Statement: testing 2 inserts and 1 update batch");
    stmt.addBatch("insert into t1 values(2)");
    stmt.addBatch("update t1 set c1=4");
    stmt.addBatch("insert into t1 values(3)");

    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; 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 where c1=2");
    rs.next();
    if(rs.getInt(1) != 0) {
      System.out.println("ERROR: There should have been 0 rows with c1 = 2");
      passed = false;
    }
    rs.close();

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

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

    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();

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

  //try prepared statement batch with just one set of values.
  static boolean runSingleValueSetPreparedBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[];
    ResultSet rs;

    //try prepared statement batch with just one set of values
    System.out.println("Positive Prepared Stat: set one set of parameter values and run the batch");
    PreparedStatement pStmt = conn.prepareStatement("insert into t1 values(?)");
    pStmt.setInt(1, 1);
    pStmt.addBatch();
    updateCount = pStmt.executeBatch();
    if (updateCount.length != 1) {
      System.out.println("ERROR: there was 1 parameter set 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.close();
    rs = stmt.executeQuery("select count(*) from t1 where c1=1");
    rs.next();
    if(rs.getInt(1) != 1) {
      System.out.println("ERROR: There should have been one rows with c1 = 1");
      passed = false;
    }
    rs.close();

    rs =