jOOQ – 具有多对多关系的嵌套对象

huangapple go评论90阅读模式
英文:

jOOQ - nested object with many to many relationship

问题

我一直在尝试找出如何将jOOQ查询中的嵌套对象转换的方法。我看到了RecordMapper或Java 8流被提到作为可能的解决方案,但我有困难弄清楚如何使用它们。

我的对象看起来类似于这样,其中Student-Teacher是一个N:N关系,而Student-Book是一个1:N关系。

Student {
  List<Teacher> teachers
  List<Book> books;
}

现在我想使用jooq查询表格并将其转换为包含其他内容的Student对象。是否有一种直接的方法来做到这一点?是否有示例显示如何实现这一点?
对于类似这样的架构,是否可以使用intoGroups?如果我使用intoGroups,是否会得到我将数据库连接的所有不同类型的记录的列表?

我也可以做两个查询,这应该不是问题。
谢谢。

英文:

I've been trying to figure out how to convert nested objects from a jOOQ query.
I've seen the RecordMapper or java 8 streams mentioned as possible solutions but have trouble figuring out how I could use those.

My objects look similar to this, with Student-Teacher being a N:N relationship and Student-Book a 1:N.

Student {
  List&lt;Teacher&gt; teachers
  List&lt;Book&gt; books;
}

Now I want to query the tables using jooq and convert that to a Student object that contains the others. Is there a straight forward way to do this? Are there examples that show how to achieve this?
Is intoGroups usable with a schema similar to this? If I do intoGroups do I get a list of all the different types of records that I joined the database with?

I could also do 2 queries, that shouldn't really be a problem.
Thanks.

答案1

得分: 2

Before jOOQ 3.14

在 jOOQ 3.14 之前,历史上,jOOQ 没有为多对多关系提供任何映射解决方案。使用来自 SQL 连接的平面结果集来实现这一点并不容易,其中不相关实体之间的笛卡尔积(在您的情况下是TeacherBook之间)并不少见。

虽然可以使用两个或更多的查询来解决这个问题,但人们通常希望避免大量手写的映射代码。

After jOOQ 3.14

jOOQ 3.14 和新的 SQL/XML 和 SQL/JSON 支持 开始,这将相对容易实现。本质上,您将使用您的 RDBMS 的本机 XML 或 JSON 支持在 SQL 中直接嵌套集合。

您可以编写这样的查询(假设您使用代码生成器):

List<Student> students =
ctx.select(jsonObject(
     jsonEntry("name", STUDENT.NAME),
     jsonEntry("id", STUDENT.ID),
     jsonEntry("teachers", field(
       select(jsonArrayAgg(jsonObject(TEACHER.NAME, TEACHER.ID)))
       .from(TEACHER)
       .join(STUDENT_TEACHER).on(TEACHER.ID.eq(STUDENT_TEACHER.TEACHER_ID))
       .where(STUDENT_TEACHER.STUDENT_ID.eq(STUDENT.ID))
     )),
     jsonEntry("books", field(
       select(jsonArrayAgg(jsonObject(BOOK.NAME, BOOK.ID)))
       .from(BOOK)
       .join(STUDENT_BOOK).on(BOOK.ID.eq(STUDENT_BOOK.BOOK_ID))
       .where(STUDENT_BOOK.STUDENT_ID.eq(STUDENT.ID))
     ))
   ))
   .from(STUDENT)
   .fetchInto(Student.class);

请注意,JSON_ARRAYAGG() 会将空集合聚合成 NULL,而不是空的 []如果这是个问题,可以使用 COALESCE()

After jOOQ 3.15

jOOQ 终于支持 MULTISET,请参见#3884这篇博文。这允许简化上面的 JSON 方法:

使用反射映射

List<Student> students =
ctx.select(
     STUDENT.NAME,
     STUDENT.ID,
     multiset(
       select(TEACHER.NAME, TEACHER.ID)
       .from(TEACHER)
       .join(STUDENT_TEACHER)
         .on(TEACHER.ID.eq(STUDENT_TEACHER.TEACHER_ID))
       .where(STUDENT_TEACHER.STUDENT_ID.eq(STUDENT.ID))
     ).as("teachers").convertFrom(r -> r.map(Teacher.class)),
     multiset(
       select(BOOK.NAME, BOOK.ID)
       .from(BOOK)
       .join(STUDENT_BOOK).on(BOOK.ID.eq(STUDENT_BOOK.BOOK_ID))
       .where(STUDENT_BOOK.STUDENT_ID.eq(STUDENT.ID))
     ).as("books").convertFrom(r -> r.map(Book.class))
   ))
   .from(STUDENT)
   .fetchInto(Student.class);

使用类型安全的、无反射的映射

如果您拥有不可变的 DTO(例如 Java 16 记录),那么比使用反射更好的方式是将 jOOQ 记录直接映射到构造函数引用中,以一种类型安全、编译时检查和无反射的方式:

List<Student> students =
ctx.select(
     STUDENT.NAME,
     STUDENT.ID,
     multiset(
       select(TEACHER.NAME, TEACHER.ID)
       .from(TEACHER)
       .join(STUDENT_TEACHER)
         .on(TEACHER.ID.eq(STUDENT_TEACHER.TEACHER_ID))
       .where(STUDENT_TEACHER.STUDENT_ID.eq(STUDENT.ID))
     ).as("teachers").convertFrom(r -> r.map(Records.mapping(Teacher::new))),
     multiset(
       select(BOOK.NAME, BOOK.ID)
       .from(BOOK)
       .join(STUDENT_BOOK).on(BOOK.ID.eq(STUDENT_BOOK.BOOK_ID))
       .where(STUDENT_BOOK.STUDENT_ID.eq(STUDENT.ID))
     ).as("books").convertFrom(r -> r.map(Records.mapping(Book::new)))
   ))
   .from(STUDENT)
   .fetch(Records.mapping(Student::new));
英文:

Before jOOQ 3.14

Historically, jOOQ did not offer any mapping solutions for many-to-many relationships. It is not easy to achieve using flat result sets originating from SQL joins, where cartesian products between unrelated entities (in your case: between Teacher and Book) are not uncommon.

Solutions using 2 or more queries are possible, but there's a lot of hand written mapping code that one would like to avoid.

After jOOQ 3.14

Starting from jOOQ 3.14 and the new SQL/XML and SQL/JSON support, this will be possible relatively easily. In essence, you will be using your RDBMS's native XML or JSON support to nest collections directly in SQL.

You can write a query like this (assuming you use the code generator):

List&lt;Student&gt; students =
ctx.select(jsonObject(
     jsonEntry(&quot;name&quot;, STUDENT.NAME),
     jsonEntry(&quot;id&quot;, STUDENT.ID),
     jsonEntry(&quot;teachers&quot;, field(
       select(jsonArrayAgg(jsonObject(TEACHER.NAME, TEACHER.ID)))
       .from(TEACHER)
       .join(STUDENT_TEACHER).on(TEACHER.ID.eq(STUDENT_TEACHER.TEACHER_ID))
       .where(STUDENT_TEACHER.STUDENT_ID.eq(STUDENT.ID))
     )),
     jsonEntry(&quot;books&quot;, field(
       select(jsonArrayAgg(jsonObject(BOOK.NAME, BOOK.ID)))
       .from(BOOK)
       .join(STUDENT_BOOK).on(BOOK.ID.eq(STUDENT_BOOK.BOOK_ID))
       .where(STUDENT_BOOK.STUDENT_ID.eq(STUDENT.ID))
     ))
   ))
   .from(STUDENT)
   .fetchInto(Student.class);

Note that JSON_ARRAYAGG() aggregates empty sets into NULL, not into an empty []. If that's a problem, use COALESCE()

After jOOQ 3.15

jOOQ finally has MULTISET support, see #3884 or this blog post. This allows for simplifying the above JSON approach:

Using reflection mapping

List&lt;Student&gt; students =
ctx.select(
     STUDENT.NAME,
     STUDENT.ID,
     multiset(
       select(TEACHER.NAME, TEACHER.ID)
       .from(TEACHER)
       .join(STUDENT_TEACHER)
         .on(TEACHER.ID.eq(STUDENT_TEACHER.TEACHER_ID))
       .where(STUDENT_TEACHER.STUDENT_ID.eq(STUDENT.ID))
     ).as(&quot;teachers&quot;).convertFrom(r -&gt; r.map(Teacher.class)),
     multiset(
       select(BOOK.NAME, BOOK.ID)
       .from(BOOK)
       .join(STUDENT_BOOK).on(BOOK.ID.eq(STUDENT_BOOK.BOOK_ID))
       .where(STUDENT_BOOK.STUDENT_ID.eq(STUDENT.ID))
     ).as(&quot;books&quot;).convertFrom(r -&gt; r.map(Book.class))
   ))
   .from(STUDENT)
   .fetchInto(Student.class);

Using type-safe, reflection free mapping

Much better than using reflection would be if you had immutable DTOs (e.g. Java 16 records), in case of which you could map jOOQ records directly into constructor references, in a type-safe, compile-time checked, reflection free way:

List&lt;Student&gt; students =
ctx.select(
     STUDENT.NAME,
     STUDENT.ID,
     multiset(
       select(TEACHER.NAME, TEACHER.ID)
       .from(TEACHER)
       .join(STUDENT_TEACHER)
         .on(TEACHER.ID.eq(STUDENT_TEACHER.TEACHER_ID))
       .where(STUDENT_TEACHER.STUDENT_ID.eq(STUDENT.ID))
     ).as(&quot;teachers&quot;).convertFrom(r -&gt; r.map(Records.mapping(Teacher::new))),
     multiset(
       select(BOOK.NAME, BOOK.ID)
       .from(BOOK)
       .join(STUDENT_BOOK).on(BOOK.ID.eq(STUDENT_BOOK.BOOK_ID))
       .where(STUDENT_BOOK.STUDENT_ID.eq(STUDENT.ID))
     ).as(&quot;books&quot;).convertFrom(r -&gt; r.map(Records.mapping(Book::new)))
   ))
   .from(STUDENT)
   .fetch(Records.mapping(Student::new));

huangapple
  • 本文由 发表于 2020年8月2日 00:44:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/63207665.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定