mysql - 连接表查询: Table is not mapped

Question

我有 4 个实体和一个连接表，我想进行如下查询:

合作者:

@Entity
public class Collaborator implements Serializable{

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@ManyToOne(cascade = {CascadeType.MERGE,
                      CascadeType.DETACH,
                      CascadeType.REFRESH }, 
           fetch = FetchType.EAGER)
@JoinColumn(name = "UserID", nullable = true)
private User user;

@ManyToOne(cascade = {CascadeType.MERGE,
                      CascadeType.DETACH,
                      CascadeType.REFRESH }, 
            fetch = FetchType.EAGER)
@JoinColumn(name = "TaskID", nullable = true)
private Task task;

...}

用户:

@Entity
public class User implements Serializable {


@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@ManyToMany(cascade = {CascadeType.MERGE,
            CascadeType.DETACH,
            CascadeType.REFRESH },
fetch = FetchType.EAGER)
@JoinTable( 
    name = "users_roles", 
    joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"), 
    inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id")) 
private Set<Role> roles = new HashSet<>();

OneToMany(mappedBy="user", fetch = FetchType.EAGER)
private Set<Collaborator> collaborators = new HashSet<>(); 
...}

角色:

@Entity
public class Role implements Serializable{

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;

@Column(name = "name", nullable = false)
private String name;

@JsonIgnore
@ManyToMany(mappedBy = "roles",fetch = FetchType.EAGER)
private Set<User> users; 
... }

任务:

@Entity
public class Task implements Serializable{

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@OneToMany(mappedBy = "task",  fetch = FetchType.EAGER)
private Set<Collaborator> collaborators = new HashSet<>();
...}

我的问题:

我想按角色获取任务吗？我尝试了这个查询:

@Query("select t from Task as t, Collaborator as c, Role as r, User as u, users_roles as ur "
        + "where c.user = u.id "
        + "and u.id = ur.user_id "
        + "and r.id = ur.role_id "
        + "and r.name = :role "
        + "and c.task = t.id "
        + "and t.done = :done ")
List<Task> getTasksByRoleAndState(@Param("role")String role, @Param("done") boolean done);

但我收到一个错误:

users_roles is not mapped

如有任何建议，欢迎提出，谢谢

Answer 1

JPQL 是围绕关系构建的，因此只需沿着关系导航即可包含您想要施加约束的任何实体。所以，类似

SELECT t FROM Task t JOIN t.collaborators c JOIN c.user u JOIN u.roles r
WHERE r.name = :role AND t.done = :done

因此，您不会引用任何“连接表”，这只是一个持久性构造，与查询无关。