Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
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 Results results = create.fetchMany("sp_help 'author'");
The returned org.jooq.Results
type extends the List<Result<Record>>
type for backwards-compatibility reasons, but it also allows to access individual update counts that may have been returned by the database in between result sets.
Feedback
Do you have any feedback about this page? We'd love to hear it!