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

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo