How to find out if a Java ResultSet obtained is em

问题:

Class.forName("org.sqlite.JDBC");
Connection conn =
    DriverManager.getConnection("jdbc:sqlite:userdata.db");
Statement stat = conn.createStatement();

ResultSet rs = stat.executeQuery("SELECT * from table WHERE is_query_processed = 0;");

int rowcount = rs.getRow(); 
System.out.println("Row count = "+rowcount); // output 1

rs.first(); // This statement generates an exception

Why is it so?

回答1:

The pattern I normally use is as follows:

boolean empty = true;
while( rs.next() ) {
    // ResultSet processing here
    empty = false;
}

if( empty ) {
    // Empty result set
}

回答2:

Here's a simple method to do it:

public static boolean isResultSetEmpty(ResultSet resultSet) {
    return !resultSet.first();
}

Caveats

This moves the cursor to the beginning. But if you just want to test whether it's empty, you probably haven't done anything with it yet anyways.

Alternatively

Use the first() method immediately, before doing any processing. ResultSet rs = stat.executeQuery("SELECT * from table WHERE is_query_processed = 0;");

if(rs.first()) {
    // there's stuff to do
} else {
    // rs was empty
}

References

ResultSet (Java Platform SE 6)

回答3:

You can do this too:

rs.last();
int numberOfRows = rs.getRow();
if(numberOfRows) {
    rs.beforeFirst();
    while(rs.next()) {
        ...
    }
}

回答4:

 while (results.next())

is used to iterate over a result set.so results.next() will return false if its empty.

回答5:

Why is execution not entering the while loop?

If your ResultSet is empty the rs.next() method returns false and the body of the while loop isn't entered regardless to the rownumber (not count) rs.getRow() returns. Colins example works.

回答6:

Shifting the cursor forth and back to determine the amount of rows is not the normal JDBC practice. The normal JDBC practice is to map the ResultSet to a List of value objects each representing a table row entity and then just use the List methods to determine if there are any rows.

For example:

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

if (users.isEmpty()) {
    // It is empty!
if (users.size() == 1) {
    // It has only one row!
} else {
    // It has more than one row!
}

where the list() method look like as follows:

public List<User> list() throws SQLException {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    List<User> users = new ArrayList<User>();

    try {
        connection = database.getConnection();
        statement = connection.createStatement();
        resultSet = statement.executeQuery(SQL_LIST);
        while (resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getLong("id"));
            user.setName(resultSet.getString("name"));
            // ...
            users.add(user);
        }
    } finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return users;
}

Also see this answer for other JDBC examples.

回答7:

CLOSE_CURSORS_AT_COMMIT

public static final int CLOSE_CURSORS_AT_COMMIT

The constant indicating that ResultSet objects should be closed when the method Connection.commit is called. 

回答8:

Try with this:

ResultSet MyResult = null;
MyResult = Conexion.createStatement().executeQuery("Your Query  Here!!!");
MyResult.last();
int NumResut = MyResult.getRow();MyResult.beforeFirst();
//Follow with your other operations....

This manner you'll be able work normally.

回答9:

May be you can convert your resultset object into String object and check whether is it empty or not.

`if(resultset.toString().isEmpty()){
     // containg null result
 }
 else{
     //This conains the result you want
 }`

回答10:

This checks if it's empty or not while not skipping the first record

if (rs.first()) {
    do {
        // ResultSet is not empty, Iterate over it
    } while (rs.next());
} else {
    // ResultSet is empty
}