JDBC driver throws "ResultSet Closed" exception on empty ResultSet

Question

I have a problem in JDBC driver for SQLite.

I am executing a query with SELECT statement.

If I get an empty ResultSet (0 rows) then I see a "Closed ResultSet" exception thrown when calling getString(1).

Without much prior JDBC experience, my theory (which I could not confirm via JavaDocs for ResultSet) is that

• getString(1) does NOT work on an empty (zero-row) resultset (by design or due to a bug)
• ResultSet's "open" flag is set to false on zero rows (again, by design or a bug)

I saw this bug report but am not sure if it's related.

My qeustions are:

1. Is the theory above correct?
2. Is it a bug? Feature? (and if so, can someone point to documentation please?)
3. Is it specific to SQLIte's JDBC or to generic ResultSet in all JDBC drivers?
4. What is the correct way of doing stuff like that??

For #4, my solution was to use isFirst() call right after executeQuery() to check whether any rows are there in result set. Is this the best practice approach?

(I could also have simply selected a count insetad since I didn't really need a result set, merely zero-nonzero flag, but I want to know the correct thingh to do if I did care about select's results)

Thanks!

Answer 1

Empty or not, but doing the following is always faulty:

resultSet = statement.executeQuery(sql);
string = resultSet.getString(1); // Epic fail. The cursor isn't set yet.

This is not a bug. This is documented behaviour. Every decent JDBC tutorial mentions it. You need to set the ResultSet's cursor using next() before being able to access any data.

If you're actually interested whether the supposedly unique row exist or not, then just check the outcome of next(). For example in a fictive UserDAO class:

public boolean exist(String username, String password) throws SQLException {
    boolean exist = false;

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id FROM user WHERE username = ? AND password = MD5(?)");
    ) {
        statement.setString(1, username);
        statement.setString(2, password);

        try (ResultSet resultSet = statement.executeQuery()) {
            exist = resultSet.next();
        }
    }

    return exist;
}

If you actually expect only zero or one row, then just do something like:

public User find(String username, String password) throws SQLException {
    User user = null;

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id, username, email, birthdate FROM user WHERE username = ? AND password = MD5(?)");
    ) {
        statement.setString(1, username);
        statement.setString(2, password);

        try (resultSet = statement.executeQuery()) {
            if (resultSet.next()) {
                user = new User(
                    resultSet.getLong("id"),
                    resultSet.getString("username"),
                    resultSet.getString("email"),
                    resultSet.getDate("birthdate")); 
            }
        }
    }

    return user;
}

and then just handle it accordingly in the business/domain object, e.g.

User user = userDAO.find(username, password);

if (user != null) {
    // Login?
}
else {
    // Show error?
}

If you actually expect only zero or many rows, then just do something like:

public List<User> list() throws SQLException {
    List<User> users = new ArrayList<User>();

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id, username, email, birthdate FROM user");
        ResultSet resultSet = statement.executeQuery();
    ) {
        while (resultSet.next()) {
            users.add(new User(
                resultSet.getLong("id"),
                resultSet.getString("username"),
                resultSet.getString("email"),
                resultSet.getDate("birthdate")));
        }
    }

    return users;
}

and then just handle it accordingly in the business/domain object, e.g.

List<User> users = userDAO.list();

if (!users.isEmpty()) {
    int count = users.size();
    // ...
}
else {
    // Help, no users?
}