How to check if resultset has only one row or more?

Question

How to check if resultset has only one row or more?

Answer 1

My no-brainer suggestion: Fetch the first result row, and then try to fetch the next. If the attempt is successful, you have more than one row.

If there is more than one row and you want to process that data, you'll need to either cache the stuff from the first row, or use a scrollable result set so you can seek back to the top before going through the results.

You can also ask SQL directly for this information by doing a SELECT COUNT(*) on the rest of your query; the result will be 0, 1 or more depending on how many rows the rest of the query would return. That's pretty easy to implement but involves two queries to the DB, assuming you're going to want to read and process the actual query next.

Answer 2

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
boolean isMoreThanOneRow = rs.first() && rs.next();

You didn't ask this one, but you may need it:

boolean isEmpty = ! rs.first();

Normally, we don't need the row count because we use a WHILE loop to iterate through the result set instead of a FOR loop:

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1");
while (rs.next()) {
    // retrieve and print the values for the current row
    int i = rs.getInt("a");
    String s = rs.getString("b");
    float f = rs.getFloat("c");
    System.out.println("ROW = " + i + " " + s + " " + f);
}

However, in some cases, you might want to window the results, and you need the record count ahead of time to display to the user something like Row 1 to 10 of 100. You can do a separate query with SELECT COUNT(*) first, to get the record count, but note that the count is only approximate, since rows can be added or removed between the time it takes to execute the two queries.

Sample from ResultSet Overview

Answer 3

There are many options, and since you don't provide more context the only thing left is to guess. My answers are sorted by complexity and performance ascending order.

1. Just run select count(1) FROM ... and get the answer. You'd have to run another query that actually selects and returns the data.
2. Iterate with rs.next() and count until you're happy. Then if you still need the actual data re-run same query.
3. If your driver supports backwards iteration, go for rs.next() couple of times and then rewind back with rs.previous().

Answer 4

You don't need JDBC for this. The normal idiom is to collect all results in a collection and make use of the collection methods, such as List#size().

List<Item> items = itemDAO.list();

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

where the list() method look like something:

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

    try {
        connection = database.getConnection();
        statement = connection.createStatement();
        resultSet = statement.executeQuery(SQL_LIST);
        while (resultSet.next()) {
            Item item = new Item();
            item.setId(resultSet.getLong("id"));
            item.setName(resultSet.getString("name"));
            // ...
            items.add(item);
        }
    } 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 items;
}