Hi Robert,
Indeed - jOOQ currently doesn't offer a solution for fetching nested
collections. The main reason is that compared to JPA, jOOQ would have to be
able to fetch any type of nested collection, not just previously declared
ones in hard-wired entities. You can observe that JPA also doesn't offer
this service when fetching projections, even if it would be possible in
principle. In fact, SQL has a feature called the MULTISET operator, which
I'm hoping to support in jOOQ at some point:
https://github.com/jOOQ/jOOQ/issues/3884
This would be the optimal way to do it: Let the database nest your
collections, e.g.
SELECT u.*, MULTISET (
SELECT d.*, MULTISET (
SELECT e.*
FROM employee e
WHERE e.department_id = d.department_id
) employees
FROM department d
WHERE d.university_id = u.university_id
) departments
FROM university u
It is not trivial to implement as data transfer would have to be emulated
using XML or JSON in most databases, given that MULTISET is poorly
supported (only CUBRID, Informix, Oracle).
Until that's supported by jOOQ, the there are these options:
1. Use the jOOQ API for limited nesting capabilities through
Result.intoGroups()
2. Use the stream API in a similar way as you described
3. Use 3 separate queries, as the duplicate data will cost you dearly when
result sets grow, both on the server and on the client
4. Use simple flat mapper, a third party library with jOOQ integration:
https://simpleflatmapper.org/0106-getting-started-jooq.html
I hope this helps,
Lukas
On Tue, Dec 4, 2018 at 12:49 AM Robert Bain <[email protected]>
wrote:
> Hey Lukas!
>
> I'm following up on my stackOverflow comment on
> https://stackoverflow.com/questions/53122656/mapping-jooq-join-result-into-multiple-pojos#comment94047335_53122656
> .
>
> When stepping away from JPA and using something like jooq, I miss the way
> that JPA provides you with a object graph.
>
> Let's say we have the tables `university`, `department` and `employee` and
> we want to join them, then get a Java representation something like:
>
> public class University {
> private final Long id;
> private final List<Department> departments;
> // some other fields
> }
>
>
> pubic class Department {
> private final Long id;
> private final Long universityId;
> private final List<Employee> employees;
> // some other fields
> }
>
>
> public class Employee {
> private final Long id;
> private final Long departmentId;
> // some other fields
> }
>
>
> From jooq, we get the denormalised list of pojos that represent a row.
> When putting it into the desired structure, the best I've come up with is
> overriding the `equals` method to compare `id` and using Java 8 streams.
>
> E.G. (assume a builder for each class)
>
> List<University> universities = rows
> .stream()
> .map(p -> University
> .builder()
> .id(p.getUniverisityId())
> // some other fields
> .build())
> .distinct()
> .collect(Collectors.toList());
>
>
> List<Department> departments = rows
> .stream()
> .map(p -> Department
> .builder()
> .id(p.getDepartmentId())
> // some other fields
> .build())
> .distinct()
> .collect(Collectors.toList());
>
>
> Map<Long, List<Department>> universityDepartment = departments
> .stream()
> .collect(Collectors.
> groupingBy(Department::getUniversityId));
>
>
> universities = //loop through universities, set the departments based on
> universityDepartment
> map (I'm using lombok so would stream and map again using `toBuilder()`)
>
>
> I feel like I'm missing a trick. Is there a better way?
>
> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
