How to check if resultset has one row or more?
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.
- Just run
select count(1) FROM ...
and get the answer. You'd have to run another query that actually selects and returns the data. - Iterate with
rs.next()
and count until you're happy. Then if you still need the actual data re-run same query. - If your driver supports backwards iteration, go for
rs.next()
couple of times and then rewind back withrs.previous()
.
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;
}
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