Spring data r2dbc for many to many mapping

Question

I am using Spring data r2dbc and it doesn't support many to many mapping.

Tables :-

user
user_role
user_role_mapping

As usual, one user can have multiple mapping.

Sql query,

select * from user u, user_role_mapping urm, user_role ur where u.user_id = :userId and u.org_id = :orgId and u.user_id = urm.user_id and ur.role_id = urm.role_id

Here, I have a model object with all the properties from all 3 classes. Here, since user has multiple roles, I am getting multiple records and then constructing single record with user details and List of roles.

Model calss,

class User {
  private long userId;
  private String userName;
  private List<String> roles;
}

Is there any out of the box support from Spring r2dbc which returns one user object with user details and List<String> roles.

spring r2dbc doesn't support relations yet: https://stackoverflow.com/questions/61726512/best-practice-of-handling-relations-between-tables-in-spring-data-r2dbc — nulldroid, Feb 20 '21 at 20:32

score 2 · Answer 1 · edited Feb 21 '21 at 05:13

Spring data r2dbc does not support automatic joins such as a full ORM does.

There is this extension that supports it, may be that can answer your need for an out of the box support. It integrates with spring-data-r2dbc, and add some ORM functionalities such as relationships.

Your model will be something like that:

@Table
class User {
    @Id @GeneratedValue
    private Long userId;
    @Column
    private String userName;
    @JoinTable
    private Set<UserRole> roles;
}

@Table
class UserRole {
    @Id @GeneratedValue
    private Long roleId;
    @Column
    private String roleName;
    @JoinTable
    private Set<User> users;
}

And your repository to load a user with linked roles:

interface UserRepository extends LcR2dbcRepository<User, Long> {
    default Mono<User> findUserByIdWithRoles(long userId) {
        return SelectQuery.from(User.class, "user")
            .join("user", "roles", "role")
            .where(Criteria.property("user", "userId", userId))
            .execute(getLcClient())
            .single();
    }
}

Spring data r2dbc for many to many mapping

1 Answers1