具有一对多关系的jooq单查询 [英] jooq single query with one to many relationship

问题描述

我有一个表格实验和一个表格标签.一个实验可能有很多标签. 架构:

I have a table experiment and a table tags. There may be many tags for one experiment. schema:

--------                  --------
|Table1|  1           n   |Table2|
|      | <--------------> |      |
|      |                  |      |
--------                  --------
(experiment)              (tags)

是否可以使用jooq创建查询以返回实验和相应的标签列表?

Is it possible to create a query with jooq which returns the experiments and the corresponding List of tags?

类似Result<Record>的地方，其中Record是一个ExperimentRecord和一个标签列表，或者是map<experimentRecord，List<TagRecord>.

something like Result<Record> where Record is a experimentRecord and a list of Tags, or a map<experimentRecord, List<TagRecord>.

我还有一个查询，该查询仅返回一个结果，那里是否有方便的东西?

I also have a query which returns only one result, is there something convenient out there?

java8，最新的页面.

java8, newest jooq.

推荐答案

有很多方法可以使用SQL和/或jOOQ实现嵌套集合.我只是在经历其中一些:

There are many ways to materialise a nested collection with SQL, and / or with jOOQ. I'm just going through some of them:

如果您不深入嵌套这些集合，请使用JOIN对结果进行归一化(拼合)可以为您解决问题，而不会因复制数据而增加过多开销.本质上，您将编写:

If you don't deeply nest those collections, denormalising (flattening) your results with a JOIN might do the trick for you, without adding too much overhead as data is being duplicated. Essentially, you'll write:

Map<ExperimentRecord, Result<Record>> map =
DSL.using(configuration)
   .select()
   .from(EXPERIMENT)
   .join(TAGS)
   .on(...)
   .fetchGroups(EXPERIMENT);

上面的地图包含实验记录作为键，以及包含所有标签作为值的嵌套集合.

The above map contains experiment records as keys, and nested collections containing all the tags as values.

如果要具体化一个复杂的对象图，使用联接可能不再是最佳选择.相反，您可能想从两个不同的查询中收集客户端中的数据:

If you want to materialise a complex object graph, using joins might no longer be optimal. Instead, you probably want to collect the data in your client from two distinct queries:

Result<ExperimentRecord> experiments = 
DSL.using(configuration)
   .selectFrom(EXPERIMENT)
   .fetch();

还有

Result<TagsRecord> tags =
DSL.using(configuration)
   .selectFrom(TAGS)
   .where(... restrict to the previous experiments ...)
   .fetch();

现在，将这两个结果合并到客户的内存中，例如

And now, merge the two results in your client's memory, e.g.

experiments.stream()
           .map(e -> new ExperimentWithTags(
                e, 
                tags.stream()
                    .filter(t -> e.getId().equals(t.getExperimentId()))
                    .collect(Collectors.toList())
           ));

这篇关于具有一对多关系的jooq单查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！