Spring Data JPA无单独关联表的多对一关系映射配置问题

一、实体类配置调整 #

单条user_client_scopes记录对应唯一一条scopes记录，多条记录可以关联同一个Scope，属于多对一关系，原有@ManyToMany和集合类型属性配置错误，调整后的UserClientScopes代码如下：

@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配置
    @ManyToOne
    @JoinColumn(name = "scope_id", insertable = false, updatable = false)
    private Scope scope;

    // 原有getter、setter保留，新增scope的getter、setter
}

注：insertable = false, updatable = false是为了避免和主键字段scopeId的映射冲突，写入时直接给scopeId赋值即可，不会重复操作scope_id字段。

二、需求实现代码 #

1. 查询指定<user_id, client_id>对应的所有scope #

定义Spring Data JPA Repository方法即可，直接匹配你需要的SQL逻辑：

public interface ScopeRepository extends JpaRepository<Scope, Long> {
    @Query("SELECT s FROM Scope s JOIN UserClientScopes ucs ON s.id = ucs.scopeId WHERE ucs.userId = :userId AND ucs.clientId = :clientId")
    List<Scope> findScopesByUserAndClient(long userId, String clientId);
}

2. 给指定<user_id, client_id>新增scope #

使用原生SQL实现插入逻辑，加@Modifying注解标识写操作：

public interface UserClientScopesRepository extends JpaRepository<UserClientScopes, UserClientScopesPK> {
    @Modifying
    @Transactional
    @Query(value = "INSERT INTO user_client_scopes (user_id, client_id, scope_id, last_updated) " +
            "SELECT :userId, :clientId, id, NOW() FROM scopes WHERE name = :scopeName", nativeQuery = true)
    int addScopeForUserClient(long userId, String clientId, String scopeName);
}

注：如果业务层已经加了@Transactional注解，Repository上的注解可以去掉。

内容的提问来源于stack exchange，提问作者Bhushan