CODEWITHSUNDEEP
javasqljooq
phoenix
phoenix

Reputation: 717

jOOQ : fetchMany and fetchAny

I was going through jooq documentation to try and understand how fetchMany and fetchAny work. But there aren't many examples and usecases available. Could someone show the proper use of these commands how are they different from each other and also from fetch().

Upvotes: 1

Views: 1811

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220842

The general idea of the various ResultQuery.fetch() methods is outlined in the manual:

And in particular:

As far as your specific question is concerned, I think the relevant Javadocs might help:

fetchAny()

This executes the query and returns at most one resulting record.

Example:

TableRecord randomRecord =
DSL.using(configuration)
   .select()
   .from(TABLE)
   .fetchAny();

So, this will fetch whatever record the database returns first. A similar query would be the following one, where you explicitly limit the number of records to 1 in the database:

TableRecord randomRecord =
DSL.using(configuration)
   .select()
   .from(TABLE)
   .limit(1)
   .fetchOne();

fetchMany()

A variety of databases support returning more than one result set from stored procedures. A Sybase ASE example:

> 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|
+-------------+-------+------+----+-----+-----+

When using JDBC directly, this is rather tedious as you have to write a lot of code to fetch one result after the other:

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();

With jOOQ and fetchMany(), this is dead simple:

List<Result<Record>> results = create.fetchMany("sp_help 'author'");

Upvotes: 2

Related Questions