Spring Data JDBC: Join on non-owned relationship

Question

I have two aggregate root entities in my database, first an Organization with Volunteer and Moderators.

Tables:

create table organizations
(
    id   uuid primary key default uuid_generate_v4(),
    name varchar(255) not null,
    slug varchar(64)  not null unique
);

create table organization_moderators
(
    id              uuid primary key default uuid_generate_v4(),
    user_id         uuid not null,
    organization_id uuid not null,
    is_owner        boolean          default false,
    unique (user_id, organization_id),
    foreign key (user_id) references users (id) on delete cascade,
    foreign key (organization_id) references organizations (id) on delete cascade
);

create table organization_volunteers
(
    id              uuid primary key default uuid_generate_v4(),
    user_id         uuid not null,
    organization_id uuid not null,
    unique (user_id, organization_id),
    foreign key (user_id) references users (id) on delete cascade,
    foreign key (organization_id) references organizations (id) on delete cascade
);

Entities:

@Value
@Table("organizations")
public class Organization {
    @Id
    @JsonIgnore
    UUID id;

    @Column("name")
    String name;

    @Column("slug")
    String slug;

    @MappedCollection(idColumn = "organization_id")
    Set<Moderator> moderators;

    @MappedCollection(idColumn = "organization_id")
    Set<Volunteer> volunteers;
}

@Value
@Table("organization_moderators")
public class Moderator {
    @Id
    @JsonIgnore
    UUID id;

    @Column("user_id")
    UUID userId;

    @Column("is_owner")
    boolean isOwner;
}

@Value
@Table("organization_volunteers")
public class Volunteer {
    @Id
    @JsonIgnore
    UUID id;

    @JsonIgnore
    @Column("user_id")
    UUID userId;
}

And second, the user aggregate root.

Table:

create table users
(
    id            uuid primary key default uuid_generate_v4(),
    username      varchar(32)  not null unique,
    email_address varchar(255) not null unique,
    password      varchar(128) not null
);

Entity:

@Value
@Table("users")
public class User {
    @Id
    UUID id;

    @Column("username")
    String username;

    @Column("email_address")
    String emailAddress;

    @Column("password")
    String password;
}

For a query I want to do, I would like to grab an organization and its volunteers, and with the volunteers I would like to include the username from the users table. How would I do this with Spring Data JDBC? I understand User cannot be part of the aggregate root of Organization, but this is still data that I would like to query in one go.

score 0 · Accepted Answer · answered Feb 21 '21 at 19:34

The first question for me is: Why do you want to do this? It's not that the request is completely unreasonable, but it might be that it is better solved by adding some caching to the user repository.

But lets assume we agreed that your requested way is the way to go.

I see two alternatives to do this:

create an alternative Organization Aggregate, which contains a modified variant of the Volunteer class which references the User class directly. The important part is to never try to save this kind of Organization. Therefore I recommend to to not inherit the repository from CrudRepository but only from Repository and only include the reading methods you actually need and no writing methods.
You can always write a cumstom Query and a ResultSetExtractor to load data in exactly the way you want.

Remember Spring Data JDBC tries to make some stuff easy while still allowing you to do whatever you want with JDBC.

Thank you, option #2 is the way I ended up going and I think is the most correct way to approach the problem. I hadn't considered caching, but this data isn't very 'hot' so that's not the way I want to go. — Jesse Brands, Feb 22 '21 at 14:24

Spring Data JDBC: Join on non-owned relationship

1 Answers1