Testing two result.next()

Question

I would need help to know why one of my prepared statement are not executed. here is the code :

The prepared statement :

updaterBalance = connection.prepareStatement("UPDATE accounts SET balance = ? WHERE account_id = ?")

The table :

statmnt.executeUpdate("CREATE TABLE accounts ( account_id INTEGER, balance DOUBLE PRECISION)");

statmnt.executeUpdate("ALTER TABLE accounts ADD CONSTRAINT balance_must_be_positive CHECK (balance >= 0)");

The failing method :

 public boolean transfer(int from, int to, double amount) throws DataStoreException {

  try {
    lookForAccount.setInt(1, to);
    ResultSet result = lookForAccount.executeQuery();

            if(result.next()) {

                result.close();

                lookForAccount.setInt(1, from);
                ResultSet result2 = lookForAccount.executeQuery();


                if(result2.next()) {
                    updaterBalance.setDouble(1, result2.getDouble("balance") - amount);
                    updaterBalance.setInt(2, from);
                    updaterBalance.executeUpdate();
                    result2.close();

                    updaterBalance.setDouble(1, result2.getDouble("balance") + amount);
                    updaterBalance.setInt(2, to);
                    updaterBalance.executeUpdate();

                    return true;
                }
                else {
                   return false;
                }
            }
            else {
                return false;
            }

          } catch (Exception error) {
        error.printStackTrace();
                return false;
    }

}

The call :

 boolean a = manager.transfer(1, 2, 10);

The prepared statement works perfectly fine is a addMoney method that add money on a single specified account_id. At the moment of the call, there is 1000 in balance for the first account_id and 0 for the second. I never went in the if block. I simply can't fullfil the conditions.

Any idea ?

Answer 1

The documentation for Statement says:

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

This basically means that because you used the same lookForAccount statement for both result and result2, result is being closed, so result.next() will not return true.