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)

Is it possible to create a query with jooq wich returns the experiments and the corresponding List of tags. something like Result where Record is a experimentRecord and a list of Tags, or a map.

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

EDIT: java8, newest jooq.

回答1:

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

Using joins

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.

Creating two queries

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

And

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

回答2:

You can now use SimpleFlatMapper to map your result to a Tuple2>. All you need to do is.

1 - create a mapper, specify the key column, assumed it would be id JdbcMapper mapper = JdbcMapperFactory .newInstance() .addKeys(EXPERIMENT.ID.getName()) .newMapper(new TypeReference>>() {});

2 - use the mapper on the ResultSet of your query

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

See here for more details