Using junction tables created in schema within Spring Boot JDBC

Question

I'm trying out pre-defining my database structure using SQL schema and then utilising it within my Kotlin code. I'm using Spring Boot and Spring Data JDBC.

Here's what I currently have so far:

My User class:

data class User(
    @Column(name = SqlQueries.Users.Entries.id) // "user_id"
    val id: String?,

    @Column(name = SqlQueries.Users.Entries.firstName) // "user_first_name"
    var firstName: String = "Joe",

    @Column(name = SqlQueries.Users.Entries.lastName) // "user_last_name"
    var lastName: String = "Bloggs",

    @Column(name = SqlQueries.Users.Entries.username) // "user_username"
    var username: String = "${firstName}.${lastName}",

    @Column(name = SqlQueries.Users.Entries.password) // "user_password"
    @JsonIgnore
    var password: String = "password1",

    @Column(name = SqlQueries.Users.Entries.isActive) // "user_is_active"
    val isActive: Boolean = true,
)

My UserRole class:

data class UserRole(
    @Column(name = SqlQueries.Lookups.UserRoles.Entries.id)
    val id: Int? = null,

    @Column(name = SqlQueries.Lookups.UserRoles.Entries.roleName)
    val name: String = "",
)

My Schema:

-- ===================================================================================
-- Lookup Tables
-- ===================================================================================
-- Creates our User Table if one does not exist within the database already.
CREATE TABLE IF NOT EXISTS table_users(
    user_id                     VARCHAR(60)         DEFAULT     RANDOM_UUID()   UNIQUE      PRIMARY KEY,
    user_first_name             VARCHAR             NOT NULL,
    user_last_name              VARCHAR             NOT NULL,
    user_username               VARCHAR             NOT NULL                    UNIQUE,
    user_password               VARCHAR             NOT NULL,
    user_is_active              VARCHAR             NOT NULL
);

-- ===================================================================================
-- Lookup Tables
-- ===================================================================================

-- Creates our Roles Lookup Table if one does not already exist within the database.
CREATE TABLE IF NOT EXISTS lookup_roles(
    role_id                     SMALLINT            AUTO_INCREMENT              UNIQUE      PRIMARY KEY,
    role_name                   VARCHAR             NOT NULL                    UNIQUE
);

-- ===================================================================================
-- Junction Tables
-- ===================================================================================

-- Creates our User/Role Junction table if one does not already exist within the database.
-- This will be the foundation of a many to many relationship between the two entities.
CREATE TABLE IF NOT EXISTS junction_user_role(
    user_id             VARCHAR(60),
    role_id             SMALLINT,
    CONSTRAINT          pk_user_role        PRIMARY KEY (user_id, role_id),
    CONSTRAINT          fk_user             FOREIGN KEY (user_id)       REFERENCES table_users (user_id),
    CONSTRAINT          fk_role             FOREIGN KEY (role_id)       REFERENCES lookup_roles (role_id)
);

As you can see, I've created a User, Role and User/Role table. This is designed to have a many to many relationship.

I'm in the dark with being able to "access" the relationship as a variable which I can use later on.

In my previous "prototype" which had a different design concept I used the following format:

User class:

...
    @ManyToMany(fetch = FetchType.EAGER)
    @Column(name = "user_roles")
    var userRoles: MutableSet<Role> = mutableSetOf(),
...

How would I define and implement this with the new way of doing it?

You tagged the question you claim to be using Spring Data JDBC, but you tagged [spring-data-jpa] and also used a JPA annotation. Which one is it? — Jens Schauder, Jan 19 '23 at 09:22
Which one is the JPA annotation? If you're referring to the last code snippet, that is my previous implementation which I don't want to be using — JamieRhys, Jan 19 '23 at 10:28
See https://spring.io/blog/2018/09/24/spring-data-jdbc-references-and-aggregates — Jens Schauder, Jan 19 '23 at 10:32

score 0 · Answer 1 · answered Jan 19 '23 at 10:57

I don't really speak Kotlin, but I try anyway. Please use ChatGPT or personal experience to fix Kotlin errors ;-)

The first step is to identify the aggregates an what belongs to which aggregate. I'd suggest that you have a UserRole and a User aggregate, with the later owning the relationship.

This implies that you need to add the relationship to the User, which is your aggregate root for the User aggregate. Since Role is a different aggregate you'd reference it by id, and you need a separate entity to hold that id

data class User ( 
...
    @MappedCollection(idColumn="user_id") 
    val roles: Set<RoleRef>
)

data class RoleRef(
    val roleId: AggregateReference<Role, Int>
)

If you want to navigate from UserRole to all the User entities having that role, you'd create a repository method in the UserRepository for this.

How this works is detailed in https://spring.io/blog/2018/09/24/spring-data-jdbc-references-and-aggregates

Also related: Spring Data JDBC many to many relationship management

Using junction tables created in schema within Spring Boot JDBC

1 Answers1