TopCoder TopCoder - 5 months ago 12
Java Question

How to check if resultset has one row or more?

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

Answer
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