Jooq entity mapping

java, jooq, sql

Solution

You're doing two things:

Run a jOOQ query:

.select(PROJECT.NAME, PROJECT.ID, USERS)
.from(PROJECT)
.join(USERS_PROJECT).on(USERS_PROJECT.PROJECT_ID.eq(PROJECT.ID))
.join(USERS).on(USERS.ID.eq(USERS_PROJECT.USER_ID))
.fetch();

This is straightforward. jOOQ transforms the above expression to a SQL string, sends it to JDBC, receives the result set and provides you with a jOOQ `Result`. Obviously, the result is denormalised (because you wrote joins), and thus you have many rows (about as many as there are rows in the `USERS_PROJECT` table).

Map the `Result` to your POJO

Now, this is what's confusing you. You called `fetchInto(Project.class)`, which is just syntax sugar for calling `fetch()`.`into(Class)`. These are two separate steps, and in particular, the `into(Class)` step has no knowledge of your query nor of your intent. Thus, it doesn't "know" that you're expecting ~15 unique projects, and a nested collection of users.

But there are ways to map things more explicitly. E.g. by using `intoGroups(Class, Class)`. This may not return nested types as you designed them, but something like a

Map<Person, List<User>> map = query.fetch().intoGroups(Person.class, User.class);

You can take it from there, manually. Or, you can write a `RecordMapper` and use that:

List<Person> list = query.fetch().map(record -> new Person(...));

Or, you could use any of the recommended third party mappers, e.g.:

- http://modelmapper.org

- http://simpleflatmapper.org

Problem

Original source