jOOQ是否支持嵌套行的解析？(Does jOOQ support parsing of nested rows?)

jOOQ是否支持嵌套行的解析？(Does jOOQ support parsing of nested rows?)

我正在评估我们是否可以从我们的项目中的普通JDBC迁移到jOOQ。 它大部分看起来很有前途，但我现在想知道一个特定的流程：嵌套行。 让我解释。

假设你有以下两个表格：

class(id, name) student(id, name, class_id)

（我们假设一个学生只能属于一个班级的一部分。）

我们来为这些表创建一个响应类型 。 我将在下面的查询中使用它们。

create type type_student as(id integer, name text); create type type_class as(id integer, name text, students type_student[]);

现在让我们通过使用嵌套行获取所有类的学生：

select row(class.id, class.name, array ( select row(student.id, student.name)::type_student from student where student.class_id = class.id ))::type_class from class

一个有用的变体是仅使用数组中的嵌套行：

select class.id, class.name, array ( select row(student.id, student.name)::type_student from student where student.class_id = class.id ) as students from class

我想知道jOOQ是否有一个优雅的方法来解析包含嵌套行的结果？

I am evaluating if we can migrate from plain JDBC to jOOQ for our project. Most of it looks promising, but I am wondering currently about one specific flow: nested rows. Let me explain.

Say you have the following two tables:

class(id, name) student(id, name, class_id)

(We assume that a student can only be part of one class.)

Let's create a response type for these tables. I will be using these in the queries below.

create type type_student as(id integer, name text); create type type_class as(id integer, name text, students type_student[]);

Now let's fetch all classes with its student by using nested rows:

select row(class.id, class.name, array ( select row(student.id, student.name)::type_student from student where student.class_id = class.id ))::type_class from class

A useful variant is to use only nested rows in arrays:

select class.id, class.name, array ( select row(student.id, student.name)::type_student from student where student.class_id = class.id ) as students from class

I am wondering if jOOQ has an elegant approach to parse such results containing nested rows?

最满意答案

您对“parse”这个词的使用可能意味着几件事情，如果有人发现这个问题寻找“jOOQ”/“parse”/“row”，我会回答他们。

org.jooq.Parser是否支持行值表达式？

还没有（截至3.10和3.11）。 jOOQ附带了一个SQL解析器，它解析（几乎）任何可以使用jOOQ API表示的东西。 这有很多好处，包括：

能够为代码生成器反向工程DDL脚本 在方言之间翻译SQL（请参阅此处的在线版本： https ： //www.jooq.org/translate ）

不幸的是，它不能在投影中解析行值表达式，即在SELECT子句中。

jOOQ API是否支持（“解析”）行值表达式？

是的，您可以使用各种DSL.row()构造函数来使用它们，主要用于谓词，还可以通过使用DSL.rowField()将它们包装到Field来进行投影。 从第3.11条开始，这仍然有点实验性，因为PostgreSQL本身有很多边界案例，涉及什么是允许的，哪些不是。 但原则上，像你这样的查询应该是可能的

jOOQ是否支持解析PostgreSQL record的序列化版本

PostgreSQL支持这些匿名record类型，以及命名的“复合”类型。 和它的数组。 并嵌套数组和复合类型。 如果类型信息可用于jOOQ，即如果您使用的是代码生成器，则jOOQ可以对这些类型进行序列化和反序列化。 例如，如果您的查询存储为视图

create view test as
select row(class.id, class.name, array
       (
         select row(student.id, student.name)::type_student
         from   student
         where  student.class_id = class.id
       ))::type_class
from   class
 
 然后，代码生成器将生成适当的类型，其中包括： 
 
 
  TypeStudentRecord  
  TypeClassRecord  
 
 可以按预期序列化。 原则上，这也可能没有代码生成器，但您必须自己手动创建上述类型，为什么不使用代码生成器。 
Your usage of the word "parse" could mean several things, and I'll answer them all in case anyone finds this question looking for "jOOQ" / "parse" / "row".
 
Does the org.jooq.Parser support row value expressions?
 
Not yet (as of jOOQ 3.10 and 3.11). jOOQ ships with a SQL parser that parses (almost) anything that can be represented using the jOOQ API. This has various benefits, including:
 
 
 Being able to reverse engineer DDL scripts for the code generator 
 Translating SQL between dialects (see an online version here: https://www.jooq.org/translate) 
 
Unfortunately, it cannot parse row value expressions in the projection yet i.e. in the SELECT clause.
 
Does the jOOQ API support ("parse") row value expressions?
 
Yes, you can use them using the various DSL.row() constructors, mainly for predicates, but also for projections by wrapping them in a Field using DSL.rowField(). As of jOOQ 3.11, this is still a bit experimental as there are many edge cases in PostgreSQL itself, related to what is allowed and what isn't. But in principle, queries like yours should be possible
 
Does jOOQ support parsing the serialised version of a PostgreSQL record
 
PostgreSQL supports these anonymous record types, as well as named "composite" types. And arrays thereof. And nesting of arrays and composite types. jOOQ can serialise and deserialise these types if type information is available to jOOQ, i.e. if you're using the code generator. For instance, if your query is stored as a view
 
create view test as
select row(class.id, class.name, array
       (
         select row(student.id, student.name)::type_student
         from   student
         where  student.class_id = class.id
       ))::type_class
from   class
 
Then, the code generator will produce the appropriate types, including:
 
 
 TypeStudentRecord 
 TypeClassRecord 
 
Which can be serialised as expected. In principle, this would be possible also without the code generator, but you'd have to create the above types yourself, manually, so why not just use the code generator.