jooq single query with one to many relationship

Tags: , , ,

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 which returns the experiments and the corresponding List of tags?

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?

EDIT: java8, newest jooq.


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 =

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 = 


Result<TagsRecord> tags =
   .where(... restrict to the previous experiments ...)

And now, merge the two results in your client’s memory, e.g.
           .map(e -> new ExperimentWithTags(
                    .filter(t -> e.getId().equals(t.getExperimentId()))

Nesting collections using SQL/XML or SQL/JSON

This question didn’t require it, but others may find this question in search for a way of nesting to-many relationships with jOOQ. I’ve provided an answer here. Starting with jOOQ 3.14, you can use your RDBMS’s SQL/XML or SQL/JSON capabilities, and then use Jackson, Gson, or JAXB to nest collections like this:

List<Experiment> experiments =

Where Experiment is a custom Java class like this:

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

class Tag {
  long id;
  String name;

Source: stackoverflow