/*======================================================================================================= 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 - UpdateCountB Test behaviour of MySQL, JDBC & Connector J in handling stored procedures that return multiple results. With allowMultiQueries enabled, Prepared Statements are used to issue multiple SQL statements to MYSQL database in a series of test cases. =======================================================================================================*/ /* This is the Prepared Statement Version */ import java.sql.ResultSet; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class UpdateCountB { // Class scope variables 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) { UpdateCountB uct = new UpdateCountB(); 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 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');"; // 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(strSelectAsc); uct.execPSQuery(connection,pstmt,strTestCase); strTestCase = "Test Case 2: SELECT - SELECT"; pstmt = connection.prepareStatement(strSelectAsc+strSelectDesc); uct.execPSQuery(connection,pstmt,strTestCase); strTestCase = "Test Case 3: SELECT - UPDATE"; pstmt = connection.prepareStatement(strSelectAsc+strUpdate); uct.execPSQuery(connection,pstmt,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(strUpdate+strSelectAsc); uct.execPSQuery(connection,pstmt,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(strSelectAsc+strUpdate+strSelectAsc); uct.execPSQuery(connection,pstmt,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(strUpdate+strSelectAsc+strDelete); uct.execPSQuery(connection,pstmt,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(strSelectAsc+strUpdate+strSelectAsc+strDelete+strSelectAsc); uct.execPSQuery(connection,pstmt,strTestCase); /**/ // Close off everything pstmt.close(); connection.close(); } catch(Exception e) { e.printStackTrace(); } } // end main() private void execPSQuery(Connection connection, PreparedStatement pstmt, String strTestCase){ try{ // Use execute() as 'results' may be a mix of updatecounts and resultsets boolean getResultSetNow = pstmt.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("PreparedStatement is: " +getResultSetNow); ResultSet rs = pstmt.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 = pstmt.getUpdateCount(); if (updateCount != -1) { // it's a valid update count System.out.println("PreparedStatement is: " +getResultSetNow); System.out.println("Update Count is: " +updateCount); } } if ((!getResultSetNow) && (updateCount == -1)) break; // done with loop, finished all the returns getResultSetNow = pstmt.getMoreResults(); } // Show Test Complete System.out.println("Test Complete"); } // end try catch(Exception e) { e.printStackTrace(); } } // end execPSQuery() }