为什么这个HQL搜索查询不起作用？ [英] Why is this HQL search query not working?
问题描述
我有以下模型;
- 我有用户，规则和团队
- a用户可以添加到0,1个或更多规则中
- 用户可以添加到0,1个或更多团队中
- 可添加规则仅适用于一个团队，但团队可以包含许多不同的规则
- 规则可包含0,1个或更多用户
以下是UserEntity类：
class UserEntity {
private String用户名;
私人列表< TeamEntity>团队;
私人清单< RuleEntity>规则;
@Column（name =username，nullable = false，unique = true）
public String getUsername（）{
return username;
}
@ManyToMany（mappedBy =users，fetch = FetchType.LAZY）
public List< RuleEntity> getRules（）{
返回规则;
@ManyToMany（mappedBy =users，fetch = FetchType.LAZY）
public List< TeamEntity> getTeams（）{
返回团队;
}
...
}
和RuleEntity类：
类RuleEntity {
私有字符串名称;
私人列表< UserEntity>用户;
私人TeamEntity ownerTeam;
@Column（name =name，nullable = false）
public String getRuleName（）{
return ruleName;
@ManyToOne
@JoinColumn（name = TeamEntity.TEAM_ID，nullable = false）
public TeamEntity getOwnerTeam（）{
return ownerTeam;
}
@ManyToMany（fetch = FetchType.LAZY）
@JoinTable（name =RULE_USER，joinColumns = @ JoinColumn
（name = RuleEntity.RULE_ID， （可选）referencedColumnName =ID，insertable = true，updatable = false，nullable = false），
inverseJoinColumns = @ JoinColumn
（name = UserEntity.USER_ID，referencedColumnName =ID，insertable = true，updatable = false，nullable = false），
uniqueConstraints = @UniqueConstraint（columnNames = {RuleEntity.RULE_ID，UserEntity.USER_ID}））
public List< UserEntity> getUsers（）{
返回用户;
}
...
}
和TeamEntity类：
class TeamEntity {
私有字符串名称
私人列表< UserEntity>用户;
私人清单< RuleEntity>规则;
@ManyToMany（fetch = FetchType.LAZY）
@JoinTable（name = TeamEntity.TEAM_USER_TABLE，joinColumns = @ JoinColumn（name = TeamEntity.TEAM_ID，referencedColumnName =ID，
insertable = true，updatable = false，nullable = false），
inverseJoinColumns = @ JoinColumn（name = TeamEntity.USER_ID，referencedColumnName =ID，
insertable = true，updatable = false，nullable = false ），
uniqueConstraints = @UniqueConstraint（columnNames = {TeamEntity.TEAM_ID，TeamEntity.USER_ID}））
public List< UserEntity> getUsers（）{
返回用户;
}
@OneToMany（mappedBy =ownerTeam，cascade = {CascadeType.ALL}，orphanRemoval = true）
public List< RuleEntity> getRules（）{
返回规则;
}
...
}
创建了以下模型：
/ **
*< pre>
*团队（team1）
* |
*规则（rule1）
* | |
*用户（john）
*< / pre>
* /
/ **
*< pre>
*团队（team4）
* |
*团队（team5）
* | |
*规则（rule4）规则（rule5）
* | |
*用户（paul）用户（john）
*< / pre>
* /
我试图执行搜索，用户可以使用用户名进行搜索，团队名称和规则名称，我......所以3必须匹配才能返回任何结果。我目前有以下功能来返回匹配3个搜索字词的用户：
从UserEntity中选择不同的用户作为用户inner join users.rules作为规则内部加入users.teams作为球队其中users.username像：john和rules.ruleName像：rule1和teams.name像：team5
因此，使用上面的HQL查询，我希望结果不返回任何用户实体（因为没有包含rule1的team5，rule1中有john），而是它正在返回'约翰'
有谁知道如何调整上面的查询，使其按照我所描述的方式工作（即它应该只返回一个结果，当所有3匹配）？
解决方案好的这些是我对此的看法：
你有3个表格：用户，规则和团队。
第一个表格包含paul和john。
第二张表包含rule1，rule4，rule 5。
这些表的连接返回john - rule1;约翰 - 规则5; （JOIN1）
第三张表包含team1，team4和team 5.
现在你加入这个表的用户。
你得到了john-team1; john-team 5.（JOIN2）
现在你问的是在JOIN1中是否有JONS中的john（是）和JOIN2中的team5（是） 。你去。
你需要做的是加入团队与规则。
从UserEntity中选择不同的用户作为用户
内部加入users.teams作为团队
内部加入user.rules作为rules1
内部连接teams.rules as rules2
where users.username like：john
and rules1.ruleName like：rule1
and rules2.ruleName like：rule1
和teams.name like ：team5。
但在用户和团队中都有规则是很奇怪的。我会重新考虑这一点。
I have the following model;
- I have users and rules and teams
- a user can be added to 0, 1 or more rules
- a user can be added to 0, 1 or more teams
- a rule can be added to one team only, but a team can contain many distinct rules
- a rule can contain 0, 1 or more users
Here is the UserEntity class:
class UserEntity { private String username; private List<TeamEntity> teams; private List<RuleEntity> rules; @Column(name = "username", nullable = false, unique = true) public String getUsername() { return username; } @ManyToMany(mappedBy="users" , fetch = FetchType.LAZY) public List<RuleEntity> getRules() { return rules; } @ManyToMany(mappedBy="users" , fetch = FetchType.LAZY) public List<TeamEntity> getTeams() { return teams; } ... }And the RuleEntity class:
class RuleEntity { private String name; private List<UserEntity> users; private TeamEntity ownerTeam; @Column(name = "name", nullable = false) public String getRuleName() { return ruleName; } @ManyToOne @JoinColumn(name=TeamEntity.TEAM_ID, nullable = false) public TeamEntity getOwnerTeam() { return ownerTeam; } @ManyToMany (fetch = FetchType.LAZY) @JoinTable(name= "RULE_USER" ,joinColumns=@JoinColumn (name=RuleEntity.RULE_ID, referencedColumnName="ID", insertable = true, updatable = false, nullable = false), inverseJoinColumns=@JoinColumn (name=UserEntity.USER_ID, referencedColumnName="ID", insertable = true, updatable = false, nullable = false), uniqueConstraints = @UniqueConstraint(columnNames = {RuleEntity.RULE_ID, UserEntity.USER_ID})) public List<UserEntity> getUsers() { return users; } ... }And the TeamEntity class:
class TeamEntity { private String name private List<UserEntity> users; private List<RuleEntity> rules; @ManyToMany (fetch = FetchType.LAZY) @JoinTable(name= TeamEntity.TEAM_USER_TABLE,joinColumns=@JoinColumn(name=TeamEntity.TEAM_ID, referencedColumnName="ID", insertable = true, updatable = false, nullable = false), inverseJoinColumns=@JoinColumn(name=TeamEntity.USER_ID, referencedColumnName="ID", insertable = true, updatable = false, nullable = false), uniqueConstraints = @UniqueConstraint(columnNames = {TeamEntity.TEAM_ID, TeamEntity.USER_ID})) public List<UserEntity> getUsers() { return users; } @OneToMany (mappedBy = "ownerTeam", cascade = {CascadeType.ALL}, orphanRemoval=true) public List<RuleEntity> getRules() { return rules; } ... }So, given I have the following model created:
/** * <pre> * Team ("team1") * | * Rule ("rule1") * | | * User ("john") * </pre> */ /** * <pre> * Team ("team4") * | * Team ("team5") * | | * Rule ("rule4") Rule ("rule5") * | | * User ("paul") User("john") * </pre> */I am trying to implement a search whereby a user can search using a username, a team name and a rule name, i..e so the 3 have to match in order to return any results. I currently have the following to return users matching the 3 search terms:
select distinct users from UserEntity as users inner join users.rules as rules inner join users.teams as teams where users.username like :john and rules.ruleName like :rule1 and teams.name like :team5So using the HQL query above, I would expect the result to return no user entities (because there is no team5 with a rule1 in it, where the rule1 has john in it), but instead it is returning 'John'
Does anyone know how to tweak the above query so that it works as I've described (i.e. it should only return a result when all 3 match)?
解决方案Ok these are my thoughts on this:
You have 3 tables: users, rules and teams. The first table contains paul and john. The second table contains rule1,rule4, rule 5.
The join of these tables return john - rule1; john - rule5; (JOIN1)
The third table contains team1, team4 and team 5. Now you join users with this table. You get john-team1; john-team 5.(JOIN2)
Now you are asking is there a john in USERS (yes) with rule1 in JOIN1(yes) and team5 in JOIN2(yes). There you go.
What you need to do is to join the teams with the rules.
select distinct users from UserEntity as users inner join users.teams as teams inner join user.rules as rules1 inner join teams.rules as rules2
where users.username like :john and rules1.ruleName like :rule1 and rules2.ruleName like :rule1 and teams.name like :team5.But it's pretty strange to have rules both in Users and Teams. I would reconsider this.
这篇关于为什么这个HQL搜索查询不起作用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
