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 with Streams
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
jOOQ 3.7+ supports Java 8, and with Java 8, it supports java.util.stream.Stream. This opens up a range of possibilities of combining the declarative aspects of SQL with the functional aspects of the new Stream API. Much like the Cursors from the previous section, such a Stream keeps an internal reference to a JDBC java.sql.ResultSet, which means that the Stream has to be treated like a resource. Here's an example of using such a stream:
// Obtain a Stream reference:
try (Stream<BookRecord> stream = create.selectFrom(BOOK).stream()) {
stream.forEach(Util::doThingsWithBook);
}
A more sophisticated example would be using streams to transform the results and add business logic to it. For instance, to generate a DDL script with CREATE TABLE statements from the INFORMATION_SCHEMA of an H2 database:
create.select(
COLUMNS.TABLE_NAME,
COLUMNS.COLUMN_NAME,
COLUMNS.TYPE_NAME)
.from(COLUMNS)
.orderBy(
COLUMNS.TABLE_CATALOG,
COLUMNS.TABLE_SCHEMA,
COLUMNS.TABLE_NAME,
COLUMNS.ORDINAL_POSITION)
.fetch() // Eagerly load the whole ResultSet into memory first
.stream()
.collect(groupingBy(
r -> r.getValue(COLUMNS.TABLE_NAME),
LinkedHashMap::new,
mapping(
r -> new SimpleEntry(
r.getValue(COLUMNS.COLUMN_NAME),
r.getValue(COLUMNS.TYPE_NAME)
),
toList()
)))
.forEach(
(table, columns) -> {
// Just emit a CREATE TABLE statement
System.out.println("CREATE TABLE " + table + " (");
// Map each "Column" type into a String containing the column specification,
// and join them using comma and newline. Done!
System.out.println(
columns.stream()
.map(col -> " " + col.getKey() +
" " + col.getValue())
.collect(Collectors.joining(",\n"))
);
System.out.println(");");
});
The above combination of SQL and functional programming will produce the following output:
CREATE TABLE CATALOGS( CATALOG_NAME VARCHAR ); CREATE TABLE COLLATIONS( NAME VARCHAR, KEY VARCHAR ); CREATE TABLE COLUMNS( TABLE_CATALOG VARCHAR, TABLE_SCHEMA VARCHAR, TABLE_NAME VARCHAR, COLUMN_NAME VARCHAR, ORDINAL_POSITION INTEGER, COLUMN_DEFAULT VARCHAR, IS_NULLABLE VARCHAR, DATA_TYPE INTEGER, CHARACTER_MAXIMUM_LENGTH INTEGER, CHARACTER_OCTET_LENGTH INTEGER, NUMERIC_PRECISION INTEGER, NUMERIC_PRECISION_RADIX INTEGER, NUMERIC_SCALE INTEGER, CHARACTER_SET_NAME VARCHAR, COLLATION_NAME VARCHAR, TYPE_NAME VARCHAR, NULLABLE INTEGER, IS_COMPUTED BOOLEAN, SELECTIVITY INTEGER, CHECK_CONSTRAINT VARCHAR, SEQUENCE_NAME VARCHAR, REMARKS VARCHAR, SOURCE_DATA_TYPE SMALLINT );
Feedback
Do you have any feedback about this page? We'd love to hear it!