batchupdate.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.batchUpdate

   Copyright 1999, 2005 The Apache Software Foundation or its licensors, as applicable.

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

 */

package org.apache.derbyTesting.functionTests.tests.jdbcapi;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;

import org.apache.derby.tools.ij;
import org.apache.derby.tools.JDBCDisplayUtil;

public class batchUpdate { 

	public static void main(String[] args) {
		boolean		passed = true;
		Connection	conn = null;
    Connection  conn2 = null;
		try {
			System.out.println("Test batchUpdate starting");

			// use the ij utility to read the property file and
			// make the initial connection.
			ij.getPropertyArg(args);
			conn = ij.startJBMS();

			conn2 = ij.startJBMS();
            passed = runTests( conn, conn2);
		} catch (SQLException se) {
			passed = false;
			dumpSQLExceptions(se);
		} catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception caught in main():\n");
			System.out.println(e.getMessage());
			e.printStackTrace();
			passed = false;
		}

		if (passed)
			System.out.println("PASS");

		System.out.println("Test batchUpdate finished");
  }

    // the runTests method is also used by the wascache/wsc_batchUpdate.java test.
    public static boolean runTests( Connection conn, Connection conn2)
    {
        boolean passed = true;
		Statement	stmt = null;
		Statement	stmt2 = null;

        try
        {
			conn.setAutoCommit(false);
            stmt = conn.createStatement();
			conn2.setAutoCommit(false);
            stmt2 = conn2.createStatement();

			/* Create the table and do any other set-up */
			passed = passed && setUpTest(conn, stmt);

			// Positive tests for statement batch update
			passed = passed && statementBatchUpdatePositive(conn, stmt);

			// Negative tests for statement batch update
			passed = passed && statementBatchUpdateNegative(conn, stmt, conn2, stmt2);

			// Positive tests for callable statement batch update
			passed = passed && callableStatementBatchUpdate(conn, stmt);

			// Positive tests for prepared statement batch update
			passed = passed && preparedStatementBatchUpdatePositive(conn, stmt);

			// Negative tests for prepared statement batch update
			passed = passed && preparedStatementBatchUpdateNegative(conn, stmt, conn2, stmt2);
        }            
        catch (SQLException se)
        {
			passed = false;
			dumpSQLExceptions(se);
		}
        catch (Throwable e)
        {
			System.out.println("FAIL -- unexpected exception caught in main():\n");
			System.out.println(e.getMessage());
			e.printStackTrace();
			passed = false;
		}
        finally
        {
			/* Test is finished - clean up after ourselves */
			passed = passed && cleanUp(conn, stmt);
		}
        return passed;
    } // end of runTests
    
	static boolean callableStatementBatchUpdate( Connection conn, Statement stmt)
		throws SQLException
	{
		boolean 	passed = true;

    //try callable statements
    passed = passed && runCallableStatementBatch(conn);

    //try callable statement with output parameters
    passed = passed && runCallableStatementWithOutputParamBatch(conn);

    return passed;
  }


	/**
 	 * Positive tests for statement batch update.
	 *
	 * @param conn	The connection to use.
	 *
	 * @return	Whether or not we were successful.
	 *
	 * @exception SQLException	Thrown if some unexpected error happens
	 */
	static boolean statementBatchUpdatePositive( Connection conn, Statement stmt)
		throws SQLException
	{
		boolean 	passed = true;

    //try executing a batch which nothing in it.
    passed = passed && runEmptyStatementBatch(conn, stmt);

    //try executing a batch which one statement in it.
    passed = passed && runSingleStatementBatch(conn, stmt);

    //try executing a batch with 3 different statements in it.
    passed = passed && runMultipleStatementsBatch(conn, stmt);

    //try executing a batch with 1000 statements in it.
    passed = passed && run1000StatementsBatch(conn, stmt);

    //try batch with autocommit true
    passed = passed && runAutoCommitTrueBatch(conn, stmt);

    //try clear batch
    passed = passed && runCombinationsOfClearBatch(conn, stmt);

	// confirm associated parameters run ok with batches
    passed = passed && checkAssociatedParams(conn, stmt);
	
    conn.commit();

    return passed;
  }

	/**
 	 * Negative tests for statement batch update.
	 *
	 * @param conn	The connection to use.
	 *
	 * @return	Whether or not we were successful.
	 *
	 * @exception SQLException	Thrown if some unexpected error happens
	 */
	static boolean statementBatchUpdateNegative( Connection conn, Statement stmt,
    Connection conn2, Statement stmt2) throws SQLException 
	{
		boolean 	passed = true;

    //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
    passed = passed && runStatementWithResultSetBatch(conn, stmt);

    //try executing a batch with regular statement intermingled.
    passed = passed && runStatementNonBatchStuffInBatch(conn, stmt);

    //Below trying various placements of overflow update statement in the batch, ie
    //as 1st stmt, nth stat and last stmt
    passed = passed && runStatementWithErrorsBatch(conn, stmt);

    //try transaction error, in this particular case time out while getting the lock
    passed = passed && runTransactionErrorBatch(conn, stmt, conn2, stmt2);

    return passed;
  }

	/**
 	 * Positive tests for prepared statement batch update.
	 *
	 * @param conn	The connection to use.
	 *
	 * @return	Whether or not we were successful.
	 *
	 * @exception SQLException	Thrown if some unexpected error happens
	 */
	static boolean preparedStatementBatchUpdatePositive(Connection conn, Statement stmt)
		throws SQLException 
	{
		boolean 	passed = true;

    //try executing a batch which nothing in it.
    passed = passed && runEmptyValueSetPreparedBatch(conn, stmt);

    //try executing a batch with no parameters.
    passed = passed && runNoParametersPreparedBatch(conn, stmt);

    //try executing a batch which one parameter set in it.
    passed = passed && runSingleValueSetPreparedBatch(conn, stmt);

    //try executing a batch with 3 parameter sets in it.
    passed = passed && runMultipleValueSetPreparedBatch(conn, stmt);

    //try executing a batch with 2 parameter sets in it and they are set to null.
    passed = passed && runMultipleValueSetNullPreparedBatch(conn, stmt);

    //try executing a batch with 1000 statements in it.
    passed = passed && run1000ValueSetPreparedBatch(conn, stmt);

    //try executing batches with various rollback and commit combinations.
    passed = passed && runPreparedStatRollbackAndCommitCombinations(conn, stmt);

    //try prepared statement batch with autocommit true
    passed = passed && runAutoCommitTruePreparedStatBatch(conn, stmt);

    //try clear batch
    passed = passed && runCombinationsOfClearPreparedStatBatch(conn, stmt);

    return passed;
  }

	/**
 	 * Negative tests for prepared statement batch update.
	 *
	 * @param conn	The connection to use.
	 *
	 * @return	Whether or not we were successful.
	 *
	 * @exception SQLException	Thrown if some unexpected error happens
	 */
	static boolean preparedStatementBatchUpdateNegative(Connection conn, Statement stmt,
    Connection conn2, Statement stmt2) throws SQLException 
	{
		boolean 	passed = true;

    //statements which will return a resultset are not allowed in batch update
    //the following case should throw an exception for select.
    passed = passed && runPreparedStmtWithResultSetBatch(conn, stmt);

    //try executing a batch with regular statement intermingled.
    passed = passed && runPreparedStmtNonBatchStuffInBatch(conn, stmt);

    //Below trying various placements of overflow update statement in the batch
    passed = passed && runPreparedStmtWithErrorsBatch(conn, stmt);

    //try transaction error, in this particular case time out while getting the lock
    passed = passed && runTransactionErrorPreparedStmtBatch(conn, stmt, conn2, stmt2);

    return passed;
  }


	static public void dumpSQLExceptions (SQLException se) {
		System.out.println("FAIL -- unexpected exception");
		while (se != null) {
			System.out.print("SQLSTATE("+se.getSQLState()+"):");
			se.printStackTrace();
			se = se.getNextException();
		}
	}

	/**
	 * Check to make sure that the given SQLException is an exception
	 * with the expected sqlstate.
	 *
	 * @param e		The SQLException to check
	 * @param SQLState	The sqlstate to look for
	 *
	 * @return	true means the exception is the expected one
	 */

	private static boolean checkException(SQLException e,
											String SQLState)
	{
		String				state;
		String				nextState;
		SQLException		next;
		boolean				passed = true;

		state = e.getSQLState();


		if (! SQLState.equals(state)) {
				System.out.println("FAIL -- unexpected exception " + e +
					"sqlstate: " + state + SQLState);
				passed = false;
			}

		return passed;
	}

	/**
	 * Clean up after ourselves when testing is done.
	 *
	 * @param conn	The Connection
	 * @param s		A Statement on the Connection
	 *
	 * @return	true if it succeeds, false if it doesn't
	 *
	 * @exception SQLException	Thrown if some unexpected error happens
	 */

	static boolean cleanUp(Connection conn, Statement s) {
    boolean passed = true;
		try {
			/* Drop the table we created */
			if (s != null)
			{
				s.execute("drop table t1");
				s.execute("drop table datetab");
				s.execute("drop table timetab");
				s.execute("drop table timestamptab");
				s.execute("drop table usertypetab");
				s.execute("drop procedure Integ");
			}

			/* Close the connection */
			if (conn != null) {
				conn.rollback();
				conn.close();
			}
		} catch (Throwable e) {
			System.out.println("FAIL -- unexpected exception caught in cleanup()");
			JDBCDisplayUtil.ShowException(System.out, e);
			passed = false;
		}

		return passed;
	}

  //Below trying placements of overflow update statement in the batch
  static boolean runPreparedStmtWithErrorsBatch(Connection conn, Statement stmt) throws SQLException {
    boolean passed = true;
    int updateCount[] = null;
    ResultSet rs;
    PreparedStatement pStmt = null;

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

		try
		{
      System.out.println("Negative Prepared Stat: testing overflow as first set of values");
      pStmt = conn.prepareStatement("update t1 set c1=(? + 1)");
      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 != 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
		{
      System.out.println("Negative Prepared Stat: testing overflow as nth set of values");
      pStmt = conn.prepareStatement("update t1 set c1=(? + 1)");
      pStmt.setInt(1, 1);
      pStmt.addBatch();
      pStmt.setInt(1, java.lang.Integer.MAX_VALUE);
      pStmt.addBatch();
      pStmt.setInt(1, 1);
      pStmt.addBatch();
      updateCount = pStmt.executeBatch();
			passed = false;