How to check if resultset has one row or more?

Question

How to check if resultset has one row or more with JDBC?

Solution 1

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

Solution 2

If you want to make sure that there is exactly one row, you can ensure that the first row is the last:

ResultSet rs = stmt.executeQuery("SELECT a FROM Table1 WHERE b=10");
if (rs.isBeforeFirst() && rs.next() && rs.isFirst() && rs.isLast()) {
    // Logic for where there's exactly 1 row
    Long valA = rs.getLong("a");    
    // ... 
} 
else {  
    // More that one row or 0 rows returned.    
    // .. 
}

Solution 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().

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

View more solutions

Comments

[Thirler]

This is indeed something that is missing in JDBC, reason for this is that not all database system support getting the size of the resultset in advance (because results aren't prefetched). Unfortunately this means you can not easily use those features in databases that do support it, such as MySQL.

[rogerdpack]

possible duplicate of Java ResultSet how to check if there are any results

[Marcus Adams]

If your driver doesn't support backwards iteration, first() usually works to start over.

[mindas]

This doesn't scale very well. What if the result set has millions of rows?

[BalusC]

@mindas: It would then already make no sense to SELECT * FROM table them. You need SELECT COUNT(*) FROM table then. JDBC is simply not the right tool for this particular purpose and that's exactly the reason a fictive ResultSet#size() method doesn't exist in JDBC.

[mindas]

Original question have never said it is doing SELECT * FROM table neither I have suggested that. Original question hasn't got an assumption that all data is actually necessary, too.

[BalusC]

@mindas: Either way, there's still no reason to do this using JDBC. Either use SQL to select the rowcount, or use collection methods to get the result size.

[bluish]

-1 ResultSetMetaData has nothing to do with row count.. rsmd.getColumnCount() gives you the number of columns of your resultset

[Vodo-Siosk Baas]

the isMoreThanOneRow give me the error, The requested operation is not supported on forward only result sets.

[Marcus Adams]

@Vodo-SioskBaas, you need to do that right after the executeQuery statement, so that the first() doesn't need to rewind or change your cursor so that it can rewind.

[amer]

This will not work since rs.next() will already skip to the next resultSet and it will give you the wrong data. I tested it on the join clause and it cuts out one element