The jOOQ User Manual SQL execution Fetching Lazy fetching

previous : next

Lazy fetching

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Unlike JDBC's java.sql.ResultSet, jOOQ's org.jooq.Result does not represent an open database cursor with various fetch modes and scroll modes, that needs to be closed after usage. jOOQ's results are simple in-memory Java java.util.List objects, containing all of the result values. If your result sets are large, or if you have a lot of network latency, you may wish to fetch records one-by-one, or in small chunks. jOOQ supports a org.jooq.Cursor type for that purpose. In order to obtain such a reference, use the ResultQuery.fetchLazy() method. An example is given here:

// Obtain a Cursor reference:
try (Cursor<BookRecord> cursor = create.selectFrom(BOOK).fetchLazy()) {

    // Cursor has similar methods as Iterator<R>
    while (cursor.hasNext()) {
        BookRecord book = cursor.fetchOne();

        Util.doThingsWithBook(book);
    }
}

As a org.jooq.Cursor holds an internal reference to an open java.sql.ResultSet, it may need to be closed at the end of iteration. If a cursor is completely scrolled through, it will conveniently close the underlying ResultSet. However, you should not rely on that.

Fetch sizes

While using a Cursor prevents jOOQ from eager fetching all data into memory, your underlying JDBC driver may still do that. To configure a fetch size in your JDBC driver, use ResultQuery.fetchSize(int), which specifies the JDBC Statement.setFetchSize(int) when executing the query. Please refer to your JDBC driver manual to learn about fetch sizes and their possible defaults and limitations.

Cursors ship with all the other fetch features

Like org.jooq.ResultQuery or org.jooq.Result, org.jooq.Cursor gives access to all of the other fetch features that we've seen so far, i.e.

• Strongly or weakly typed records: Cursors are also typed with the <R> type, allowing to fetch custom, generated org.jooq.TableRecord or plain org.jooq.Record types.
• RecordHandler callbacks: You can use your own org.jooq.RecordHandler callbacks to receive lazily fetched records.
• RecordMapper callbacks: You can use your own org.jooq.RecordMapper callbacks to map lazily fetched records.
• POJOs: You can fetch data into your own custom POJO types.

The jOOQ User Manual SQL execution Fetching Lazy fetching

previous : next