/*=======================================================================================================
	Problem:
	When multiple SQL statements are issued to MySQL database from a Java class, multiple updatecounts
	and resultsets may be produced and are returned to the statement object from which they were executed.
	
	The order in which these 'results' are returned and the information contained in the updatecounts is
	NOT the same if these multiple SQL statements are issued via a stored procedure compared with using
	prepared statements alone.
	
	Test Class - UpdateCountA
	Test behaviour of MySQL, JDBC & Connector J in handling stored procedures that return multiple results.
	Prepared Statements are used to drop and create a different stored procedure for each test case.
	execute() method of CallableStatement used to execute the stored procedure.
=======================================================================================================*/
/*
	This is the Callable Statement Version
*/
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;

public class UpdateCountA {
	
// Build Database URL String
	final private static String host = "localhost:8889";
	final private static String database = "test";
	final private static String user = "root";
	final private static String password = "pwd";
	final private static String dbURL = "jdbc:mysql://" + host + "/" + database + "?user=" + user +
										"&password=" + password + "&allowMultiQueries=true";

public static void main(String[] args) {

	UpdateCountA uct = new UpdateCountA();

try {
// SQL Statements
	String strCreateTable = "DROP TABLE IF EXISTS test_updateCount;CREATE TABLE test_updateCount"+
							"(id integer not null auto_increment primary key, foo varchar(255))";
	String strPopulateTable = "INSERT INTO test_updateCount(foo) VALUES ('a'),('b'),('c')";
	String strResetTable = "TRUNCATE TABLE test_updateCount;"+strPopulateTable;	
	String strStoredProcStart = "DROP PROCEDURE IF EXISTS multiRS;"+
	 							"CREATE PROCEDURE multiRS() BEGIN ";
	String strSelectAsc = "SELECT * from test_updateCount ORDER BY id ASC;";
	String strSelectDesc = "SELECT * from test_updateCount ORDER BY id DESC;";
	String strUpdate = "UPDATE test_updateCount SET foo = 'Bar' WHERE id > 0;";
	String strDelete = "DELETE from test_updateCount WHERE id = 2;";
	String strInsert = "INSERT INTO test_updateCount(foo) VALUES ('d');";
	String strStoredProcEnd = "END";

// Get Database Connection Object, enable MultiQueries in PreparedStatements
	Class.forName("com.mysql.jdbc.Driver");
	Connection connection = DriverManager.getConnection(dbURL);

// Create the Table
	PreparedStatement pstmt = connection.prepareStatement(strCreateTable);
	pstmt.executeUpdate();

// Insert 3 new rows
	pstmt = connection.prepareStatement(strPopulateTable);
	pstmt.executeUpdate();	
	
//================
//	Test Cases
//================
	
	String strTestCase = "";

	strTestCase = "Test Case 1: SELECT";
	pstmt = connection.prepareStatement(strStoredProcStart+strSelectAsc+strStoredProcEnd);
	pstmt.executeUpdate();
	uct.execStoredProc(connection,strTestCase);

	strTestCase = "Test Case 2: SELECT - SELECT";
	pstmt = connection.prepareStatement(strStoredProcStart+strSelectAsc+strSelectDesc+strStoredProcEnd);
	pstmt.executeUpdate();
	uct.execStoredProc(connection,strTestCase);
	
	strTestCase = "Test Case 3: SELECT - UPDATE";
	pstmt = connection.prepareStatement(strStoredProcStart+strSelectAsc+strUpdate+strStoredProcEnd);
	pstmt.executeUpdate();
	uct.execStoredProc(connection,strTestCase);
	
	// Put table back to init state for next test case
	pstmt = connection.prepareStatement(strResetTable);
	pstmt.executeUpdate();
	
	strTestCase = "Test Case 4: UPDATE - SELECT";
	pstmt = connection.prepareStatement(strStoredProcStart+strUpdate+strSelectAsc+strStoredProcEnd);
	pstmt.executeUpdate();
	uct.execStoredProc(connection,strTestCase);

	// Put table back to init state for next test case
	pstmt = connection.prepareStatement(strResetTable);
	pstmt.executeUpdate();

	strTestCase = "Test Case 5: SELECT - UPDATE - SELECT";
	pstmt = connection.prepareStatement(strStoredProcStart+strSelectAsc+strUpdate+strSelectAsc+strStoredProcEnd);
	pstmt.executeUpdate();
	uct.execStoredProc(connection,strTestCase);

	// Put table back to init state for next test case
	pstmt = connection.prepareStatement(strResetTable);
	pstmt.executeUpdate();
			
	strTestCase = "Test Case 6: UPDATE - SELECT - DELETE";
	pstmt = connection.prepareStatement(strStoredProcStart+strUpdate+strSelectAsc+strDelete+strStoredProcEnd);
	pstmt.executeUpdate();
	uct.execStoredProc(connection,strTestCase);

	// Put table back to init state for next test case
	pstmt = connection.prepareStatement(strResetTable);
	pstmt.executeUpdate();
		
	strTestCase = "Test Case 7: SELECT - UPDATE - SELECT - DELETE - SELECT";
	pstmt = connection.prepareStatement(strStoredProcStart+strSelectAsc+strUpdate+strSelectAsc+strDelete+strSelectAsc+strStoredProcEnd);
	pstmt.executeUpdate();
	uct.execStoredProc(connection,strTestCase);	
/**/

// Close off everything
	pstmt.close();
	connection.close();
}
catch(Exception e) {
	e.printStackTrace();
	}
}

private void execStoredProc(Connection connection, String strTestCase){	

try{
// Create CallableStatement
	CallableStatement cstmt = connection.prepareCall("CALL multiRS()");

// Execute the Stored Procedure.
// Use execute() as 'results' may be a mix of updatecounts and resultsets
    boolean getResultSetNow = cstmt.execute();
    int updateCount = -1;
	System.out.println(strTestCase);
	
// Loop through the 'results' to display the Resultsets and/or UpdateCounts	
	while (true) {
	if (getResultSetNow) {
		System.out.println("CallableStatement is: " +getResultSetNow);
	    ResultSet rs = cstmt.getResultSet();
	      while (rs.next()) {
	        	// fully process result set before calling getMoreResults() again
			        System.out.println( rs.getInt(1) + " " + rs.getString(2) );
		      }
	      	rs.close();
	    } else {
			updateCount = cstmt.getUpdateCount();
			if (updateCount  != -1) { // it's a valid update count
				System.out.println("CallableStatement is: " +getResultSetNow);
				System.out.println("Update Count is: " +updateCount);
			}
		}
		if ((!getResultSetNow) && (updateCount == -1)) break;
			// done with loop, finished all the returns
				getResultSetNow = cstmt.getMoreResults();
	}
	cstmt.close();
	System.out.println("Test Complete");
}
catch(Exception e) {
	e.printStackTrace();
}

}
}