Changing Mocked ResultSet value each time a SQL statement is run

Question

I'm using Mockrunner to create a mock result set for a select statement. I have a loop that executes the select statement (which returns a single value). I want to have the result set return a different value each time, but I have been unable to find anything about how to specify the result set return value based on the times the statement has been called. Here's a pseudocode snippet of the code:

In the test Code:

String selectSQL = "someselectStmt";
StatementResultSetHandler stmtHandler = conn.GetStatementResultSetHandler();
MockResultSet result = stmtHandler.createResultSet();
result.addRow(new Integer[]{new Integer(1)});
stmtHandler.prepareResultSet(selectSQL, result);

In the Actual Target Class:

Integer[] Results = getResults(selectSQL);

while(Results.length != 0){
    //do some stuff that change what gets returned in the select stmt
    Results = getResults(selectSQL)
}

So essentially I'd like to return something like 1 on the first time through, 2 on the 2nd and nothing on the 3rd. I haven't found anything so far that I'd be able to leverage that could achieve this. The mocked select statement will always return whatever the last result set was to be associated with it (for instance if I created two MockResultSets and associated both with the same select stmt). Is this idea possible?

Answer 1

Looping Control Flow Working Within Java and SQL

If you're coding this one in Java, a way to make your code execution calls return different, sequenced results can be accomplished throughh a looping control flow statement such as a do-while-loop. This Wikipedia reference has a good discussion using the contrast of the do-while-loop between implementations in Java and also in different programming lanugages.

Some Additional Influences through Observation:

A clue from your work with the Mockrunner tool:

The mocked select statement will always return whatever the last result set was to be associated with it (for instance if I created two MockResultSets and associated both with the same select stmt)

This is the case because the SELECT statement must actually change as well or else repeating the query will also repeat the result output. A clue is that your SQL exists as a literal string value throughout the execution of the code. Strings can be altered through code and simple string manipulations.

String selectSQL = "someselectStmt";
StatementResultSetHandler stmtHandler = conn.GetStatementResultSetHandler();
MockResultSet result = stmtHandler.createResultSet();
result.addRow(new Integer[]{new Integer(1)});
stmtHandler.prepareResultSet(selectSQL, result);

in addition to the selectSQL variable, also add a line for a numeric variable to keep track of how many times the SQL statement is executed:

Int queryLoopCount = 0;

In the following target class:

Integer[] Results = getResults(selectSQL);

while(Results.length != 0){
//do some stuff that change what gets returned in the select stmt
Results = getResults(selectSQL)
}

Try rewriting this WHILE loop control following this example. In your pseudocode, you will keep pulling the same data from the call to getResults(selectSQL); because the query remains the same through every pass made through the code.

Setting up the Test Schema and Example SQL Statement

Here is a little workup using a single MySQL table that contains "testdata" output to be fed into some result set. The ID column could be a way of uniquely identifying each different record or "test case"

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE testCaseData 
    (
     id int primary key,
     testdata_col1 int,
     testdata_col2 varchar(20),
     details varchar(30)
    );

INSERT INTO testCaseData
(id, testdata_col1, testdata_col2, details)
VALUES
(1, 2021, 'alkaline gab', 'First Test'),
(2, 322, 'rebuked girdle', '2nd Test'),
(3, 123, 'municipal shunning', '3rd Test'),
(4, 4040, 'regal limerick', 'Skip Test'),
(5, 5550, 'admonished hundredth', '5th Test'),
(6, 98, 'docile pushover', '6th Test'),
(7, 21, 'mousiest festivity', 'Last Test');

commit;

Query 1 A Look at All the Test Data:

SELECT id, testdata_col1, testdata_col2, details
  FROM testCaseData

Results:

| ID | TESTDATA_COL1 |        TESTDATA_COL2 |    DETAILS |
|----|---------------|----------------------|------------|
|  1 |          2021 |         alkaline gab | First Test |
|  2 |           322 |       rebuked girdle |   2nd Test |
|  3 |           123 |   municipal shunning |   3rd Test |
|  4 |          4040 |       regal limerick |  Skip Test |
|  5 |          5550 | admonished hundredth |   5th Test |
|  6 |            98 |      docile pushover |   6th Test |
|  7 |            21 |   mousiest festivity |  Last Test |

Query 2 Querying Only the First Record in the Table:

SELECT id, testdata_col1, testdata_col2, details
  FROM testCaseData
 WHERE id = 1

Results:

| ID | TESTDATA_COL1 | TESTDATA_COL2 |    DETAILS |
|----|---------------|---------------|------------|
|  1 |          2021 |  alkaline gab | First Test |

Query 3 Querying a Specific Test Record Within the Table:

SELECT id, testdata_col1, testdata_col2, details
  FROM testCaseData
 WHERE id = 2

Results:

| ID | TESTDATA_COL1 |  TESTDATA_COL2 |  DETAILS |
|----|---------------|----------------|----------|
|  2 |           322 | rebuked girdle | 2nd Test |

Query 4 Returning and Limiting the Output Set Size:

SELECT id, testdata_col1, testdata_col2, details
  FROM testCaseData
 WHERE id < 5

Results:

| ID | TESTDATA_COL1 |      TESTDATA_COL2 |    DETAILS |
|----|---------------|--------------------|------------|
|  1 |          2021 |       alkaline gab | First Test |
|  2 |           322 |     rebuked girdle |   2nd Test |
|  3 |           123 | municipal shunning |   3rd Test |
|  4 |          4040 |     regal limerick |  Skip Test |

Writing a Parameterized SQL Statement

I do not know if this difference in syntax yields the exact same results as your pseudocode, but I am recommending it from references of code structures that I know already work.

set condition value before loop
do{
    // do some work
    // update condition value
}while(condition);

The WHILE condition is instead at the end of the statement and should be based on a change to a value made within the looping block. We will now introduce the second variable, an int which tracks the number of times that the loop is iterated over:

String selectSQL = "someselectStmt";
String[] Results; = getResults(selectSQL);

// set condition value before loop 
queryLoopCount = 0

do{
    // do some work
    Results = getResults(selectSQL);

    // update condition value
    queryLoopCount = queryLoopcount + 1;

}while(queryLoopCount < 6);

Where selectSQL comes from:

SELECT id, testdata_col1, testdata_col2, details
  FROM testCaseData
 WHERE id = 2;

And adapts with a built in parameter to:

selectSQL = 'SELECT id, testdata_col1, testdata_col2, details
  FROM testCaseData
 WHERE id = ' + queryLoopCount;

Mixing the string and integer values may not be a problem as in this reference on concatenated(+) values suggests: Anything concatenated to a string is converted to string (eg, "weight = " + kilograms).

Ideas for Specialized Case Requirements

• You could introduce your own numbering sequence to get the records of each case to cycle through the reference table. There are a lot of possibilities by introducing an ORDER BY statement and altering the key ORDER BY value.

• The "Skip" case. Within the Do-While loop, add a IF-THEN statement to conditionally skip a specific record.

  set condition value before loop
  do{
  
      if ( queryLoopCount <> 4 ) {
      // do some work}
  
      // update condition value
      queryLoopCount = queryLoopCount + 1;
  
  }while(condition);
  

  Using an if-then loop, this code sample will process all test records but will skip over the record of ID = 4 and continue through until the while loop condition is met.