New versions: Dev (3.16) | Latest (3.15)

Nested records

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

The DSL.row() constructor isn't only useful for different types of row value expression predicates, but also to project nested record types, in most cases even Record1 to Record22 types, which maintain column level type safety.

All org.jooq.Row1 to org.jooq.Row22 types as well as the org.jooq.RowN type extend org.jooq.SelectField, meaning they can be placed in the SELECT clause or the RETURNING clause. The T type variable in SelectField<T> is bound to the appropriate Record1 to Record22 type, which allows for easily projecting nested records:

SELECT
  ID,
  ROW(
    FIRST_NAME, 
    LAST_NAME
  )
FROM AUTHOR
// Type inference via lambdas or var really shines here!
Result<Record2<Integer, Record2<String, String>>> result =
create.select(
         AUTHOR.ID,
         row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME))
      .from(AUTHOR)
      .fetch();

Combining nested records with arrays

If your RDBMS supports ARRAY types and ARRAY constructors, and if nested records are natively supported, chances are that you can combine the two features. For example, to find all books for an author, as a nested collection rather than a flat join:

SELECT
  ID,
  ROW(
    AUTHOR.FIRST_NAME, 
    AUTHOR.LAST_NAME
  ),
  ARRAY(
    SELECT BOOK.ID, BOOK.TITLE
    FROM BOOK
    WHERE BOOK.AUTHOR_ID = AUTHOR.ID
  )
FROM AUTHOR
// Type inference via lambdas or var really shines here!
Result<Record3<
  Integer, 
  Record2<String, String>,
  Record2<Integer, String>[]
>> result =
create.select(
         AUTHOR.ID,
         row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
         array(
           select(row(BOOK.ID, BOOK.TITLE))
           .from(BOOK)
           .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
         )
       )
      .from(AUTHOR)
      .fetch();

Attaching RecordMappers to nested records

Nested records help structure your result sets using structural typing, but they really shine when you attach a RecordMapper to them. A RecordMapper is a java.lang.FunctionalInterface that can convert a Record subtype to any user type E. By calling e.g. Row2.mapping(), you can attach an ad-hoc converter to the nested record type to turn the nested object into something much more meaningful:

// Especially useful using Java 16 record types!
record Name(String firstName, String lastName) {}
record Author(int id, Name name) {}

// The "scary" structural type has gone!
List<Author> authors =
create.select(
         AUTHOR.ID,
         row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).mapping(Name::new)
       )
      .from(AUTHOR)
      .fetch(Records.mapping(Author::new));

All of the above is type safe and uses no reflection! Try it out yourself - add or remove a column to the query or to the records, and observe the compilation errors that appear.

Now for the ARRAY example:

record Name(String firstName, String lastName) {}
record Book(int id, String title) {}
record Author(int id, Name name, Book[] books) {}

// Again, no structural typing here has gone!
List<Author> authors =
create.select(
         AUTHOR.ID,
         row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).mapping(Name::new),
         array(
           select(row(BOOK.ID, BOOK.TITLE).mapping(Book.class, Book::new)
           .from(BOOK)
           .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
         )
       )
      .from(AUTHOR)
      .fetch(Records.mapping(Author::new));

Again, everything is type safe. Unfortunately, reflection is needed in this case to construct a Book[] array. You must pass the Book.class reference to help jOOQ with that. If you prefer lists, no problem. You can wrap the array again using the same technique, using an explicit ad-hoc converter:

record Name(String firstName, String lastName) {}
record Book(int id, String title) {}
record Author(int id, Name name, List<Book> books) {} // Is now using a List<Book> instead of Book[]

// Again, no structural typing here has gone!
List<Author> authors =
create.select(
         AUTHOR.ID,
         row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).mapping(Name::new),
         array(
           select(row(BOOK.ID, BOOK.TITLE).mapping(Book.class, Book::new)
           .from(BOOK)
           .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
         ).convertFrom(Arrays::asList) // Additional converter here
       )
      .from(AUTHOR)
      .fetch(Records.mapping(Author::new));

Dialect support

This example using jOOQ:

select(row(BOOK.ID, BOOK.TITLE))

Translates to the following dialect specific expressions:

-- ACCESS
SELECT BOOK.ID AS row.ID,
BOOK.TITLE AS row.TITLE
FROM (
  SELECT count(*) dual
  FROM MSysResources
) AS dual

-- ASE, EXASOL, H2, IGNITE, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLSERVER, VERTICA
SELECT BOOK.ID row.ID,
BOOK.TITLE row.TITLE

-- AURORA_MYSQL, MEMSQL
SELECT BOOK.ID AS row.ID,
BOOK.TITLE AS row.TITLE
FROM DUAL

-- AURORA_POSTGRES, COCKROACHDB, POSTGRES, YUGABYTE
SELECT ROW (BOOK.ID, BOOK.TITLE)

-- BIGQUERY, MARIADB, MYSQL
SELECT BOOK.ID AS row.ID,
BOOK.TITLE AS row.TITLE

-- DB2
SELECT BOOK.ID row.ID,
BOOK.TITLE row.TITLE
FROM SYSIBM.DUAL

-- DERBY
SELECT BOOK.ID AS row.ID,
BOOK.TITLE AS row.TITLE
FROM SYSIBM.SYSDUMMY1

-- FIREBIRD
SELECT BOOK.ID row.ID,
BOOK.TITLE row.TITLE
FROM RDB$DATABASE

-- HANA, SYBASE
SELECT BOOK.ID row.ID,
BOOK.TITLE row.TITLE
FROM SYS.DUMMY

-- HSQLDB
SELECT BOOK.ID AS row.ID,
BOOK.TITLE AS row.TITLE
FROM (VALUES(1)) AS dual(dual)

-- INFORMIX
SELECT ROW (BOOK.ID, BOOK.TITLE)
FROM (
  SELECT 1 AS dual
  FROM systables
  WHERE (tabid = 1)
) AS dual

-- ORACLE
SELECT BOOK.ID row.ID,
BOOK.TITLE row.TITLE
FROM DUAL

-- SQLITE
SELECT BOOK.ID AS "row.ID",
BOOK.TITLE AS "row.TITLE"

-- TERADATA
SELECT BOOK.ID row.ID,
BOOK.TITLE row.TITLE
FROM (
  SELECT 1 AS "dual"
) AS "dual"

(These are currently generated with jOOQ 3.16, see #10141), or translate your own on our website

Feedback

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

The jOOQ Logo