Jooq select pojos from many to many relationship

user_12823863 0
Viewed 60

I just started learn jooq, i got these 3 tables:

             _______________
            |   USER_ROLES  |
            | userid        |
            | roleid        |
            |_______________|
         ____/___        __\____
        |  USERS |      | ROLES |
        |___id___|      |___id__|

I need to select all roles pojos for user with id

public List<Roles> getRolesForUser(Long userId) {
   List<Roles> roles = dsl.select .... where(User.id.eq(id)).fetch()....
   return roles;
}

i cant google it properly so i am sorry and i am asking help.

jooq java
1 Answers

I assume that you generated to code for your database then the query would look like this:

public List<Roles> getRolesForUser(Long userId) {
   List<Roles> roles = dsl
        .select(ROLES.fields())
        .from(ROLES)
        .join(USERS_ROLES).on(USER_ROLES.ROLEID.eq(ROLES.id)
        .where(USER_ROLES.USERID.eq(userId)
        .fetchInto(Roles.class);
   return roles;
}
user_10451420
Related