How to check ResultSet for emptiness inside Spring JDBC ResultSetExtractor?

Question

It seems that standard check of ResultSet emptiness will not work in Spring's JDBC ResultSetExtractor. I use it in following way:

    return jdbcTemplate.query(sql, ps -> {
        ps.setLong(1, fooParam1);
        ps.setLong(2, fooParam2);
    }, rs -> {
        return rs.getLong(1);
    });

So what is the correct way check ResultSet emptiness in such pattern?

Answer 1

There seems to be some confusion about the roles of the interfaces, I saw some tutorials doing it wrong. The interfaces RowMapper and RowCallbackHandler are responsible for providing a value, resp. producing a side effect, per row.

Therefore, they are not responsible for invoking next() as the framework will take care of advancing to the next row and not invoking the callback, once next() returned false which may happen even when advancing to the first row, in which case the ResultSet is empty. In the latter case, query(… RowMapper<T>) will return an empty List without invoking the RowMapper and query(… RowCallbackHandler) will simply do nothing.

In contrast, ResultSetExtractor is responsible for processing the entire ResultSet, which includes invoking next() appropriately. So your question’s premise (“… each call of extractData gives me new row.”) is wrong. The standard behavior of all JDBC operations is to return a ResultSet being before the first row so that the first next() invocation will either move the current row pointer to the first row or return false if the ResultSet is empty. So if you expect at most one row, your query should rather look like

return jdbcTemplate.query(sql, ps -> {
        ps.setLong(1, fooParam1);
        ps.setLong(2, fooParam2);
   }, rs -> rs.next()? rs.getLong(1): null);

EDIT: This solution tested and works correctly on standard MySQL and Postgresql instances /Andremoniy/.

EDIT-2: This solution absolutely correct and works correctly anywhere. But it should be noticed that there are two different methods in JdbcTemplate class:

1. public <T> T query(String sql, PreparedStatementSetter pss, ResultSetExtractor<T> rse) - it is used in this example and it returns 0 for rs.getRows() always, indeed. Provided solution is correct for this case.

2. public void query(String sql, PreparedStatementSetter pss, RowCallbackHandler rch) which has another type of 3rd parameter, and similar code:

   jdbcTemplate.query(sql, ps -> { ps.setLong(1, fooParam1); ps.setLong(2, fooParam2); }, rs -> { System.out.println(rs.getRow()); });

will work differently: rs.getRow() will return number of processing row, and if no rows exist, processRow method will be never invoked (this second method caused my confusing). /Andremoniy/