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.

Answer 1

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.