Java – the jdbc driver throws a “resultset closed” exception on an empty resultset

I have a problem with the jdbc driver for SQLite

I am using the select statement to execute the query

If I get an empty resultset (row 0), I will see an "closed resultset" exception when calling getString (1)

Without a lot of previous JDBC experience, my theory (I can't confirm the resultset through JavaDocs) is like this

>GetString (1) is not applicable to empty (zero row) result set (by design or due to error) > the "open" flag of resultset is set to false in zero row (again, through design or error)

I see this bug report, but I don't know if it is relevant

My approach is:

Is the above theory correct? > Is it a bug? (if so, can anyone point out the document?) > Is it a SQLite specific JDBC or a common resultset in all JDBC drivers? > What is the right way to do such a thing?

For #4, my solution is to use the isfirst () call after executeQuery () to check if there are any rows in the result set Is this a best practice?

(I can also simply select a count insetad, because I really don't need a result set, just a zero non-zero flag, but I want to know the right thing if I care about the selected result)

thank you!

Solution

Is empty, but there is always a problem doing the following:

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

This is not a bug This is documented behavior Every decent JDBC tutorial mentions it You need to use next() to set the cursor of the resultset to access any data

If you are really interested, whether there is a unique row or not, just check the result of next () For example, in the fictional 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 really expect only zero or one line, just do something:

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,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;
}

It is then processed accordingly in the business / domain object, for example

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

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

If you really expect zero or more lines, you just need to do something:

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

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

    return users;
}

It is then processed accordingly in the business / domain object, for example

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

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