SQLite 3 - JDBC driver throws "ResultsSet Closed" exception on empty resultset

Question

I 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 checking 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 practices 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

while (rs.next()) {
 // process the row
}

Answer 2

From the JavaDocs for ResultSet:

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

You'll need to position the ResultSet on a row, e.g. by calling calling next(), before attempting to read any data. If the call to next() returns false then the result set is empty.

Answer 3

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 {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    boolean exist = false;

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement("SELECT id FROM user WHERE username = ? AND password = MD5(?)");
        statement.setString(1, username);
        statement.setString(2, password);
        resultSet = statement.executeQuery();
        exist = resultSet.next();
    } finally {
        close(resultSet, statement, connection);
    }

    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 {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    User user = null;

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement("SELECT id, username, email, age FROM user WHERE username = ? AND password = MD5(?)");
        statement.setString(1, username);
        statement.setString(2, password);
        resultSet = statement.executeQuery();
        if (resultSet.next()) {
            user = new User(
                resultSet.getLong("id"),
                resultSet.getString("username"),
                resultSet.getString("email"),
                resultSet.getInteger("age"));
        }
    } finally {
        close(resultSet, statement, connection);
    }

    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 {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<User> users = new ArrayList<User>();

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement("SELECT id, username, email, age FROM user");
        resultSet = statement.executeQuery();
        while (resultSet.next()) {
            users.add(new User(
                resultSet.getLong("id"),
                resultSet.getString("username"),
                resultSet.getString("email"),
                resultSet.getInteger("age")));
        }
    } finally {
        close(resultSet, statement, connection);
    }

    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?
}