How to check if resultset has one row or more?

Question

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

Answer 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

Answer 2

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