Jooq entity mapping

Question

I have following schema:

Projects (ID, NAME) Projects_Users (PROJECT_ID, USERS_ID) Users (NAME, ID)

Entity are as follows

public class Projects {

    private String name;
    private long id;
    private List<User> users;

    public Projects() {
    }
}


public class User {

    private String name;
    private Long id;
}

so clearly one-to-many. Projects can have multiple users.

Now my goal is to write jooq query where I can fetch project objects already with corresponding users.

.select(PROJECT.NAME, PROJECT.ID, USERS)
.from(PROJECT)
.join(USERS_PROJECT).on(USERS_PROJECT.PROJECT_ID=PROJECT.ID)
.join(USERS).on(USERS.ID=USERS_PROJECT.USER_ID)
.fetchInto(Project.class);

but the query would resturn thousends results when expecting ~15

Answer 1

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