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