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.
Feedback
Do you have any feedback about this page? We'd love to hear it!