具有多对多关系的JPQL查询

Question

嘿，我有一个多对多关系在Knights和Quests两个表之间

我想写JPQL查询，我从id 1的骑士获得1的id任务，以后我会把它改为任务状态，但现在我想让它工作:

@Query("select  k.quests from Knight k join k.quests q  where k.id=1 and q.id=1")
Collection<Quest> findDoneStories();

它有点工作，因为它给我的任务从骑士id 1，但它给我所有的任务，而不是只有一个任务id 1。

@Entity
@Table(name = "knights")
public class Knight {
@Id
@Column(name = "id")
int id;

@Column
String name;

@Column(name = "status")
@Enumerated(EnumType.STRING)
private KnightEnum status;

@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(name = "knights_quests", joinColumns = @JoinColumn(name = "id_knights"),
inverseJoinColumns = @JoinColumn(name = "id_quest"))
List < Quest > stories;
}

@Entity
@Table(name = "quests")
public class Quest {
@Id
@Column(name = "id")
int id;

@Column
String name;

@Column(name = "description")
String description;

@Column(name = "status")
@Enumerated(EnumType.STRING)
QuestEnum status;

@Column(name = "story_points")
int storyPoints;

@ManyToMany(mappedBy = "stories", fetch = FetchType.EAGER)
List < Sprint > knights;
}

Answer 1

如果你只想得到一个Quest，你的查询和方法应该是这样的:

@Query("SELECT DISTINCT q FROM Knight k JOIN k.stories q WHERE k.id = 1 AND q.id = 1")
Quest findDoneStories();

Answer 2

因为它是多对多的关系，你必须添加一个表knights_quests与列:knights_id和quests_id，你可以存储1和1，例如

你的实体将像这样:

@ManyToMany() @JoinTable(name = "knights_quests", joinColumns = @JoinColumn(name = "knighs_id", referencedColumnName = "id"), inverseJoinColumns = @JoinColumn(name = "quests_id",referencedColumnName = "name") )

当你可以用简单的连接执行查询后，JPQL会在后台处理它