Jooq single query with one to one relationship

Question

I have a table experiment and table tags. There can be many tags for one experiment. Scheme:

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

Is it possible to create a query with jooq that returns experiments and the corresponding tag list. something like a result where Record is a Record experiment and a tag list or map.

I also have a query that returns only one result, is there anything convenient there?

EDIT: java8, newest jooq.

Answer 1

There are many ways to materialize a nested collection with SQL and / or with jOOQ. I just look through some of them:

Using Unions

If you do not deeply nest these collections, denormalizing (smoothing) your results with JOIN can do the trick for you without adding too much overhead because the data is duplicated. Essentially, you will 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 tags as values.

Creating Two Queries

If you want to materialize a complex graph of objects, using joins may no longer be optimal. Instead, you probably want to collect data in your client from two different 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 combine the two results in your client memory, for example

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

Answer 2

Now you can use SimpleFlatMapper to match the result with Tuple2>. All you have to do is.

1 - create a mapping, specify a key column, suppose that it is the identifier JdbcMapper mapper = JdbcMapperFactory .newInstance () .addKeys (EXPERIMENT.ID.getName ()) .newMapper (new TypeReference → () {});

2 - use mapper in the ResultSet of your request

 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.