resultSet.next() returns false, even though table is populated

Question

I have a few functions that help with retrieving the objects from the database.

public User getUser(int beamID) throws NoSuchUserException {
    return userFromResultSet(getUserResultSet(beamID));
}


private ResultSet getUserResultSet(int beamID) {
    try(Connection conn = dataSource.getConnection()) {

        // queries.getUserByBeamID() returns "SELECT * FROM user WHERE beamID=?"
        PreparedStatement stmt = conn.prepareStatement(queries.getUserByBeamID());

        stmt.setInt(1, beamID);
        System.out.println(stmt.toString());
        return stmt.executeQuery();

    } catch (SQLException e) {
        e.printStackTrace();
        throw new IllegalStateException();
    }
}

private User userFromResultSet(ResultSet resultSet) {
    try {
        boolean next = resultSet.next();  // Debugger tells me this is false.
        if (!next)
            throw new NoSuchUserException();

        User user = new User(this,
             resultSet.getInt("beamID"),
             resultSet.getString("name"),
             resultSet.getInt("points"),
             resultSet.getInt("time")
        );

        if (resultSet.next())
            throw new IllegalStateException("Duplicate user entries exist - database integrity compromised!");

        return user;
    } catch (SQLException e) {
        e.printStackTrace();
        throw new IllegalStateException();
    }
}

The strange thing is, I know the data does exist for two reasons:

• My program tries to create the entry if it does not exist, but attempting that gives an error that the unique constraint isn't being followed.

• Running the query in my SQLite DB browser works just fine:

I highly doubt that this is an issue with uncommitted data, as this is a file-based database, and opening that file with a text editor shows instances of the usernames in the data.

Solution

Look closely at what you are doing here:

try (Connection conn = dataSource.getConnection()) {
    PreparedStatement stmt = conn.prepareStatement(queries.getUserByBeamID());

    stmt.setInt(1, beamID);
    System.out.println(stmt.toString());
    return stmt.executeQuery();
} catch (SQLException e) {
    e.printStackTrace();
    throw new IllegalStateException();
}

I believe it is the contract of try-with-resources to guarantee closing the resource specified in the try clause, after the expression finishes executing. I believe that the result set is also being closed at the end of the try block, hence calling next() returns false, because nothing is there.

The way I would have written your code is to populate the User POJO inside the try block, and return a User object instead of returning a result set:

private User getUserResultSet(int beamID) {
    User user = null;
    try (Connection conn = dataSource.getConnection()) {
        PreparedStatement stmt = conn.prepareStatement(queries.getUserByBeamID());

        stmt.setInt(1, beamID);

        ResultSet rs = stmt.executeQuery();
        rs.next();
        user = new User(this,
            rs.getInt("beamID"),
            rs.getString("name"),
            rs.getInt("points"),
            rs.getInt("time")
        );

    } catch (SQLException e) {
        e.printStackTrace();
        throw new IllegalStateException();
    }

    return user;
}

Now your separation of concerns is better than before. Should something go wrong with the connection, result set, etc., it is handled in the actual code which deals with those things. In the event of an exception or other error, a null user object would be returned, and you should update your code to handle this possibility.

Comments

[Erwin Bolwidt]

That's a good point, but: the javadoc of ResultSet.next() states that it throws an "SQLException if a database access error occurs or this method is called on a closed result set". So if that were the reason, the OP should have received a SQLException, not false. But it's possible the the driver is broken and doesn't implement the JDBC spec correctly.

[Tim Biegeleisen]

@ErwinBolwidt This crossed my mind as well, but from what I read the behavior is driver-specific. If you have a better explanation for the result set being empty on a valid query, then feel free to post an answer.

[Nat Karmios]

This, in fact, solved it; I nested the usage of the ResultSet inside the try-with-resources, and it worked just fine. Thanks for your help!

[Erwin Bolwidt]

@TimBiegeleisen I added a comment to the question already, since the OP's comment about the function that inserts a user "if it doesn't exist" is contradictory.

[Nat Karmios]

@ErwinBolwidt, the issue was that the ResultSet was closed early, making it behave as though all the data was processed, or in this case, that the query returned an empty set. Because of this, my program acted as though the user didn't exist, even though it did.