Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
Fetching
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Fetching is something that has been completely neglected by JDBC and also by various other database abstraction libraries. Fetching is much more than just looping or listing records or mapped objects. There are so many ways you may want to fetch data from a database, it should be considered a first-class feature of any database abstraction API. Just to name a few, here are some of jOOQ's fetching modes:
- Untyped vs. typed fetching: Sometimes you care about the returned type of your records, sometimes (with arbitrary projections) you don't.
- Fetching arrays, maps, or lists: Instead of letting you transform your result sets into any more suitable data type, a library should do that work for you.
- Fetching through handler callbacks: This is an entirely different fetching paradigm. With Java 8's lambda expressions, this will become even more powerful.
- Fetching through mapper callbacks: This is an entirely different fetching paradigm. With Java 8's lambda expressions, this will become even more powerful.
- Fetching custom POJOs: This is what made Hibernate and JPA so strong. Automatic mapping of tables to custom POJOs.
- Lazy vs. eager fetching: It should be easy to distinguish these two fetch modes.
- Fetching many results: Some databases allow for returning many result sets from a single query. JDBC can handle this but it's very verbose. A list of results should be returned instead.
- Fetching data asynchronously: Some queries take too long to execute to wait for their results. You should be able to spawn query execution in a separate process.
Convenience and how ResultQuery, Result, and Record share API
The term "fetch" is always reused in jOOQ when you can fetch data from the database. An org.jooq.ResultQuery provides many overloaded means of fetching data:
Various modes of fetching
These modes of fetching are also documented in subsequent sections of the manual
// The "standard" fetch Result<R> fetch(); // The "standard" fetch when you know your query returns at most one record. This may return null. R fetchOne(); // The "standard" fetch when you know your query returns at most one record. Optional<R> fetchOptional(); // The "standard" fetch when you only want to fetch the first record R fetchAny(); // Create a "lazy" Cursor, that keeps an open underlying JDBC ResultSet Cursor<R> fetchLazy(); Cursor<R> fetchLazy(int fetchSize); Stream<R> stream(); // Fetch several results at once List<Result<Record>> fetchMany(); // Fetch records into a custom callback <H extends RecordHandler<R>> H fetchInto(H handler); // Map records using a custom callback <E> List<E> fetch(RecordMapper<? super R, E> mapper); // Execute a ResultQuery with jOOQ, but return a JDBC ResultSet, not a jOOQ object ResultSet fetchResultSet();
Fetch convenience
These means of fetching are also available from org.jooq.Result and org.jooq.Record APIs
// These methods are convenience for fetching only a single field,
// possibly converting results to another type
<T> List<T> fetch(Field<T> field);
<T> List<T> fetch(Field<?> field, Class<? extends T> type);
<T, U> List<U> fetch(Field<T> field, Converter<? super T, U> converter);
List<?> fetch(int fieldIndex);
<T> List<T> fetch(int fieldIndex, Class<? extends T> type);
<U> List<U> fetch(int fieldIndex, Converter<?, U> converter);
List<?> fetch(String fieldName);
<T> List<T> fetch(String fieldName, Class<? extends T> type);
<U> List<U> fetch(String fieldName, Converter<?, U> converter);
// These methods are convenience for fetching only a single field, possibly converting results to another type
// Instead of returning lists, these return arrays
<T> T[] fetchArray(Field<T> field);
<T> T[] fetchArray(Field<?> field, Class<? extends T> type);
<T, U> U[] fetchArray(Field<T> field, Converter<? super T, U> converter);
Object[] fetchArray(int fieldIndex);
<T> T[] fetchArray(int fieldIndex, Class<? extends T> type);
<U> U[] fetchArray(int fieldIndex, Converter<?, U> converter);
Object[] fetchArray(String fieldName);
<T> T[] fetchArray(String fieldName, Class<? extends T> type);
<U> U[] fetchArray(String fieldName, Converter<?, U> converter);
// These methods are convenience for fetching only a single field from a single record,
// possibly converting results to another type
<T> T fetchOne(Field<T> field);
<T> T fetchOne(Field<?> field, Class<? extends T> type);
<T, U> U fetchOne(Field<T> field, Converter<? super T, U> converter);
Object fetchOne(int fieldIndex);
<T> T fetchOne(int fieldIndex, Class<? extends T> type);
<U> U fetchOne(int fieldIndex, Converter<?, U> converter);
Object fetchOne(String fieldName);
<T> T fetchOne(String fieldName, Class<? extends T> type);
<U> U fetchOne(String fieldName, Converter<?, U> converter);
Fetch transformations
These means of fetching are also available from org.jooq.Result and org.jooq.Record APIs
// Transform your Records into arrays, Results into matrices
Object[][] fetchArrays();
Object[] fetchOneArray();
// Reduce your Result object into maps
<K> Map<K, R> fetchMap(Field<K> key);
<K, V> Map<K, V> fetchMap(Field<K> key, Field<V> value);
<K, E> Map<K, E> fetchMap(Field<K> key, Class<E> value);
Map<Record, R> fetchMap(Field<?>[] key);
<E> Map<Record, E> fetchMap(Field<?>[] key, Class<E> value);
// Transform your Result object into maps
List<Map<String, Object>> fetchMaps();
Map<String, Object> fetchOneMap();
// Transform your Result object into groups
<K> Map<K, Result<R>> fetchGroups(Field<K> key);
<K, V> Map<K, List<V>> fetchGroups(Field<K> key, Field<V> value);
<K, E> Map<K, List<E>> fetchGroups(Field<K> key, Class<E> value);
Map<Record, Result<R>> fetchGroups(Field<?>[] key);
<E> Map<Record, List<E>> fetchGroups(Field<?>[] key, Class<E> value);
// Transform your Records into custom POJOs
<E> List<E> fetchInto(Class<? extends E> type);
// Transform your records into another table type
<Z extends Record> Result<Z> fetchInto(Table<Z> table);
Note, that apart from the fetchLazy() methods, all fetch() methods will immediately close underlying JDBC result sets.
Table of contents
- 5.3.1.
- Record vs. TableRecord
- 5.3.2.
- Record1 to Record22
- 5.3.3.
- Arrays, Maps and Lists
- 5.3.4.
- RecordHandler
- 5.3.5.
- RecordMapper
- 5.3.6.
- POJOs
- 5.3.7.
- POJOs with RecordMappers
- 5.3.8.
- Lazy fetching
- 5.3.9.
- Lazy fetching with Streams
- 5.3.10.
- Many fetching
- 5.3.11.
- Later fetching
- 5.3.12.
- ResultSet fetching
- 5.3.13.
- Auto data type conversion
- 5.3.14.
- Custom data type conversion
| previous : next |
Feedback
Do you have any feedback about this page? We'd love to hear it!