New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11 | 3.10 | 3.9 | 3.8 | Old versions: 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | 2.6
Many fetching
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Many databases support returning several result sets, or cursors, from single queries. An example for this is Sybase ASE's sp_help command:
> sp_help 'author' +--------+-----+-----------+-------------+-------------------+ |Name |Owner|Object_type|Object_status|Create_date | +--------+-----+-----------+-------------+-------------------+ | author|dbo |user table | -- none -- |Sep 22 2011 11:20PM| +--------+-----+-----------+-------------+-------------------+ +-------------+-------+------+----+-----+-----+ |Column_name |Type |Length|Prec|Scale|... | +-------------+-------+------+----+-----+-----+ |id |int | 4|NULL| NULL| 0| |first_name |varchar| 50|NULL| NULL| 1| |last_name |varchar| 50|NULL| NULL| 0| |date_of_birth|date | 4|NULL| NULL| 1| |year_of_birth|int | 4|NULL| NULL| 1| +-------------+-------+------+----+-----+-----+
The correct (and verbose) way to do this with JDBC is as follows:
ResultSet rs = statement.executeQuery();
// Repeat until there are no more result sets
for (;;) {
// Empty the current result set
while (rs.next()) {
// [ .. do something with it .. ]
}
// Get the next result set, if available
if (statement.getMoreResults()) {
rs = statement.getResultSet();
}
else {
break;
}
}
// Be sure that all result sets are closed
statement.getMoreResults(Statement.CLOSE_ALL_RESULTS);
statement.close();
As previously discussed in the chapter about differences between jOOQ and JDBC, jOOQ does not rely on an internal state of any JDBC object, which is "externalised" by Javadoc. Instead, it has a straight-forward API allowing you to do the above in a one-liner:
// Get some information about the author table, its columns, keys, indexes, etc
List<Result<Record>> results = create.fetchMany("sp_help 'author'");
Using generics, the resulting structure is immediately clear.
