具有一对多关系的Jooq单个查询

Question

我有一个桌子实验和一个桌子标签。一个实验可能有许多标签。架构：

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

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

类似Result之类的东西

我也有一个只返回一个结果的查询，有什么方便的吗？

编辑：java8，最新的jooq。




Answer 1

您现在可以使用SimpleFlatMapper将结果映射到Tuple2

1-创建映射器，指定键列，假设它是id

JdbcMapper mapper = 
    JdbcMapperFactory
     .newInstance()
     .addKeys(EXPERIMENT.ID.getName())
     .newMapper(new TypeReference<Tuple2<ExperimentRecord, List<TagRecord>>>() {});

2-在查询的结果集上使用映射器

try (ResultSet rs = DSL.using(configuration)
   .select()
   .from(EXPERIMENT)
   .join(TAGS)
   .on(...)
   .fetchResultSet()) {
    Stream<Tuple2<ExperimentRecord, List<TagRecord>>> stream = mapper.stream(rs);
    ....
}

请参阅此处了解更多详细信息

Answer 2

匿名用户

有许多方法可以使用SQL和/或jOOQ实现嵌套集合。我只是讲了其中的一些：

如果您没有深度嵌套这些集合，那么使用连接对结果进行非规范化（展平）可能会帮到您，而不会在复制数据时增加太多开销。基本上，你会写：

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

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

如果您想将复杂的对象图具体化，那么使用连接可能不再是最佳选择。相反，您可能希望从两个不同的查询中收集客户端中的数据：

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

和

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

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

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

这个问题不需要它，但是其他人可能会在寻找与jOOQ嵌套多关系的方法时找到这个问题。我在这里提供了一个答案。从jOOQ 3.14开始，您可以使用RDBMS的SQL /XML或SQL /JSON功能，然后使用Jackson、Gson或JAXB嵌套集合，如下所示：

List<Experiment> experiments =
ctx.select(
      EXPERIMENT.asterisk(),
      field(
        select(jsonArrayAgg(jsonObject(TAGS.fields())))
        .from(TAGS)
        .where(TAGS.EXPERIMENT_ID.eq(EXPERIMENT.ID))
      ).as("tags")
    )
   .from(EXPERIMENT)
   .fetchInto(Experiment.class);

其中，实验是这样一个自定义Java类：

class Experiment {
  long id;
  String name;
  List<Tag> tags;
}

class Tag {
  long id;
  String name;
}

甚至比上述更好的是，您可以使用SQL/XML或SQL/JSON隐藏在jOOQ 3.15新的MULTISET运算符支持后面。假设上述Java类是Java 16记录（或任何其他不可变类），您甚至可以将嵌套集合类型安全地映射到DTO中：

List<Experiment> experiments =
ctx.select(
      EXPERIMENT.ID,
      EXPERIMENT.NAME,
      multiset(
        select(TAGS.ID, TAGS.NAME)
        .from(TAGS)
        .where(TAGS.EXPERIMENT_ID.eq(EXPERIMENT.ID))
      ).as("tags").convertFrom(r -> r.map(Records.mapping(Tag::new)))
    )
   .from(EXPERIMENT)
   .fetch(Records.mapping(Experiment::new));

其中，实验是这样一个自定义Java类：

record Experiment(long id, String name, List<Tag> tags) {}
record Tag(long id, String name) {}

有关更多信息，请参阅此博客文章。