可用版本：开发版 (3.21) | 最新版 (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15

嵌套记录

适用于 ✅ 开源版 ✅ 专业版 ✅ 企业版

DSL.row() 构造函数不仅适用于不同类型的行值表达式谓词，而且还适用于投影嵌套记录类型，在大多数情况下甚至是 Record1 到 Record22 类型，这些类型保持列级别的类型安全。

所有 org.jooq.Row1 到 org.jooq.Row22 类型以及 org.jooq.RowN 类型都扩展了 org.jooq.SelectField，这意味着它们可以放置在 SELECT 子句或 RETURNING 子句中。 SelectField<T> 中的 T 类型变量绑定到相应的 Record1 到 Record22 类型，这使得可以轻松地投影嵌套记录。

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

将嵌套记录与数组组合

如果您的 RDBMS 支持 ARRAY 类型和 ARRAY 构造函数，并且本地支持嵌套记录，那么您很可能可以将这两个特性结合起来。例如，要查找作者的所有书籍，作为嵌套集合而不是平面 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();

嵌套记录有助于使用结构类型组织结果集，但是当您将 RecordMapper 附加到它们时，它们才会真正发挥作用。 RecordMapper 是一个 java.lang.FunctionalInterface，可以将 Record 子类型转换为任何用户类型 E。通过调用例如 Row2.mapping()，您可以将 ad-hoc 转换器附加到嵌套记录类型，将嵌套对象转换为更有意义的内容。

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

以上所有操作都是类型安全的，并且不使用反射！亲自尝试一下 - 向查询或记录添加或删除列，并观察出现的编译错误。

现在是 ARRAY 示例

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

同样，一切都是类型安全的。不幸的是，在这种情况下需要反射来构造 Book[] 数组。您必须传递 Book.class 引用以帮助 jOOQ。如果您喜欢列表，没问题。您可以使用相同的技术再次包装数组，使用显式 ad-hoc 转换器

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

方言支持

此示例使用 jOOQ

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

翻译成以下特定方言的表达式

访问

SELECT BOOK.ID [nested__ID],
BOOK.TITLE [nested__TITLE]
FROM (
  SELECT count(*) dual
  FROM MSysResources
) AS dual

ASE, SQLDataWarehouse, SQLServer

SELECT BOOK.ID [nested__ID],
BOOK.TITLE [nested__TITLE]

Aurora MySQL, MemSQL

SELECT BOOK.ID `nested__ID`,
BOOK.TITLE `nested__TITLE`
FROM DUAL

Aurora Postgres, CockroachDB, DuckDB, H2, Postgres, YugabyteDB

SELECT ROW (BOOK.ID, BOOK.TITLE) nested

BigQuery, MariaDB, MySQL, SQLite

SELECT BOOK.ID `nested__ID`,
BOOK.TITLE `nested__TITLE`

ClickHouse

SELECT TUPLE (BOOK.ID, BOOK.TITLE) nested

Databricks

SELECT (
  coalesce(BOOK.ID),
  coalesce(BOOK.TITLE)
) nested

DB2

SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"
FROM SYSIBM.DUAL

Derby

SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"
FROM SYSIBM.SYSDUMMY1

Exasol, Oracle, Redshift, Snowflake, Trino, Vertica

SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"

Firebird

SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"
FROM RDB$DATABASE

Hana

SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"
FROM SYS.DUMMY

HSQLDB

SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"
FROM (VALUES (1)) AS dual (dual)

Informix

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

Sybase

SELECT BOOK.ID [nested__ID],
BOOK.TITLE [nested__TITLE]
FROM SYS.DUMMY

Teradata

SELECT BOOK.ID "nested__ID",
BOOK.TITLE "nested__TITLE"
FROM (
  SELECT 1 AS "dual"
) AS "dual"

使用 jOOQ 3.21 生成。早期 jOOQ 版本的支持可能有所不同。在我们的网站上翻译您自己的 SQL

引用此页

反馈

您对此页面有任何反馈吗？我们很乐意倾听！

请告知我们您的一些情况，并订阅我们的新闻通讯 (示例)

嵌套记录

将嵌套记录与数组组合

将 RecordMapper 附加到嵌套记录

方言支持

访问

ASE, SQLDataWarehouse, SQLServer

Aurora MySQL, MemSQL

Aurora Postgres, CockroachDB, DuckDB, H2, Postgres, YugabyteDB

BigQuery, MariaDB, MySQL, SQLite

ClickHouse

Databricks

DB2

Derby

Exasol, Oracle, Redshift, Snowflake, Trino, Vertica

Firebird

Hana

HSQLDB

Informix

Sybase

Teradata

引用此页

反馈

社区

支持

法律

文档