hibernate - HQL 判断某个值是否在连接表中

Question

我有 2 个实体表(场景和标签)和一个表示多对多关系的连接表。使用 Scenario 作为基表，我创建了一个 sql 语句，它允许我查看某个标签是否是与单个场景关联的标签集的一部分，并输出具有该标签的所有场景:

select count(*)
from scenarios
where scenarios.id not in (
    select tag_to_scenarios.scenarioid
    from tag_to_scenarios
    join tags ON tags.id = tag_to_scenarios.tagid
    where (
        exists( 
            select concat(group_concat(tags.tagname), ',') as tag_list
            from scenarios as sub_scenarios
            left outer join tag_to_scenarios ON tag_to_scenarios.scenarioid = sub_scenarios.id
            join tags ON tags.id = tag_to_scenarios.tagid
            where scenarios.id = sub_scenarios.id
            group by scenarios.id
            having (position('@regression,' in tag_list) = 0))
        )
    )
 );

以下是正在使用的模型的相关部分:

@Entity
@Table(name="tags")
public class Tag {

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private int id;

public void setId(int id) {
    this.id = id;
}

public int getId() {
    return id;
}

@NotNull
@Column(name = "tagname")
private String tagName;

public void setTagName(String tagName) {
    this.tagName = tagName;
}

public String getTagName() {
    return tagName;
}

}

@Entity

@Table(名称=“场景”)公开课场景{

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private int id;

public int getId() {
    return this.id;
}

public void setId(int id) {
    this.id = id;
}
...
@ManyToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@JoinTable(name = "tag_to_scenarios",
    joinColumns = {@JoinColumn(name = "scenarioid", nullable = false, updatable = false)},
    inverseJoinColumns = {@JoinColumn(name = "tagid", nullable = false, updatable = false)},
    uniqueConstraints = @UniqueConstraint(name = "scenarioid", columnNames = {"tagid"})
)
private Set<Tag> tags = new HashSet<Tag>(0);

public Set<Tag>  getTags() {
    return this.tags;
}

public void setTags(Set<Tag> tags) {
    this.tags = tags;
}
}

我需要将此 SQL 语句转换为 HQL，因为我的模型中没有联接表。

Answer 1

以下 HQL 可以帮助您实现此目的:

  SELECT s from Scenario s
    JOIN s.tags t 
   WHERE t.name = :tag 
GROUP BY s 
  HAVING count(t) = 1

制作人员来自以下网站:

http://www.sergiy.ca/how-to-write-many-to-many-search-queries-in-mysql-and-hibernate/