Many to one relationship without a join table in spring

Question

I'm trying to build the relationship between two tables using spring-data jpa. I have read many SO articles like 1, 2 but they are pretty old and don't seem to apply to my specific use case. Hence this question:

There are 2 tables user_client_scopes and scopes listed below.

user_client_scopes:
user_id (long),
client_id (string)
last_updated (timestamp)
scope_id (Foreign key to scopes table),
primary key (user_id, client_id, scope_id)

scopes:
id (int, primary key)
name (string)

A <user_id, client_id> can have multiple scopes. Similarly, the same scope can be held by many <user_id, client_id>s. Hence the many-to-many relationship. The join table (as defined by spring-data-jpa) is kind of embedded within user_client_scope table.

Here is a half-written-code:

@Entity
@Table(name = "user_client_scopes")
@RequiredArgsConstructor
@IdClass(UserClientScopesPK.class)
public class UserClientScopes implements Serializable {


    @Id
    @Column(name = "user_id")
    private long userId;

    @Id
    @Column(name = "client_id")
    private String clientId;

    @Column(name = "last_updated")
    private Timestamp lastUpdated;

    @Id
    @Column(name = "scope_id")
    private int scopeId;

    @ManyToMany // <- how to complete this definition?
    private Set<Scope> scopes;

    getters and setters.

Here are 2 other classes (for the sake of completion).

@Data
@RequiredArgsConstructor
public class UserClientScopesPK implements Serializable {
    private long userId;
    private String clientId;
    private int scopeId;
}

@Entity
@Table(name = "scopes")
@RequiredArgsConstructor
public class Scope implements Serializable {

    @Id
    @GeneratedValue
    private long id;

    private String name;
}

How do I complete the user_client_scopes entity such that we can:

1. Find all scopes for a given <user_id, client_id>. i.e. execute the following SQL:

select user_id, client_id, scope 
from scopes
join user_client_scopes ucs on ucs.scope_id = scopes.id
where ucs.user_id = ? and ucs.client_id = ?

2. Save new scopes for a given <user_id, client_id>. i.e. execute the following SQL:

insert into user_client_scopes (user_id, client_id, scope_id, last_updated)
select ?, ?, id, now()
from scopes
where scopes.name = ?

UPDATE 1:

Changing title to Many to one instead of Many to many relationship.

Answer 1

That's not a many-to-many because the association scope is mapped by the column scope_id in user_client_scopes. This means that if I take a single row in the table user_client_scopes, it will be associated to only a single row in the table scopes. Therefore, this is a many-to-one.

If the three columns <user_id, client_id, scope_id> form the key for user_client_scopes, then the mapping for the table should look like:

Entity
@Table(name = "user_client_scopes")
@RequiredArgsConstructor
@IdClass(UserClientScopesPK.class)
public class UserClientScopes implements Serializable {

    @Id
    @Column(name = "user_id")
    private long userId;

    @Id
    @Column(name = "client_id")
    private String clientId;

    @Column(name = "last_updated")
    private Timestamp lastUpdated;

    @Id
    @ManyToOne
    @JoinedColumn(name = "scope_id")
    private Scope scope;


    getters and setters.
}

class UserClientScopesPK implements Serializable {

    private long userId;
    private String clientId;
    private Scope scope;

    // getters,setters, equals and hascode
}

With this mapping you can run the following HQL:

select ucs
from UserClientScopes ucs join ucs.scope
where ucs.userId = :userId and ucs.clientId = :clientId

It will return all UserClientScopes entities matching the selected pair <userId, clientId>. Each one with a different scope.

Or, if you only care about the scope:

select s
from UserClientScopes ucs join ucs.scope s
where ucs.userId = :userId and ucs.clientId = :clientId

With Spring Data JPA, it will look like this:

@Query("select s from UserClientScopes ucs join ucs.scope swhere ucs.userId = ?1 and ucs.clientId = ?2")
public List<Scope> findScopesByUserIdAndClientId(long userId, String clientId);

or

@Query("select s.name from UserClientScopes ucs join ucs.scope swhere ucs.userId = ?1 and ucs.clientId = ?2")
public List<String> findScopesNameByUserIdAndClientId(long userId, String clientId);

You can also run the insert query as native SQL (you can probably run something similar as HQL, but I don't remember the right syntax now. I will update the answer later).

One last thing, to keep track of the last updated time, you could use Spring Entity callback listener:

@Entity
...
@EntityListeners(AuditingEntityListener.class)
public class UserClientScopes implements Serializable {

    @LastModifiedDate
    @Column(name = "last_updated")
    private Date lastUpdated;
}