主页 / 问题 / 1351806
Accepted
unkn0wn
Asked:2022-04-20 00:48:29 +0800 CST2022-04-20 00:48:29 +0800 CST

如何使用 Criteriq Query 编写查询？

  • 772

有表：

  1. gift_certificate（礼券）
  2. tag（标签）
  3. gift_certificate_to_tag_relation（对于礼券和标签之间的多对多关系，包含ID） 在此处输入图像描述

如何使用 Criteria Query 查询具有特定标签集的礼券。标签被传递给Set<String> tagNames.

Java 实体：

  1. GiftCertificate.class：
private Long id;
@Column(nullable = false, unique = true)
private String name;

@Column(nullable = false)
private String description;

@Column(nullable = false)
private BigDecimal price;

@Column(nullable = false)
private Integer duration;

@Column(nullable = false)
private LocalDateTime createDate;

@Column(nullable = false)
private LocalDateTime lastUpdateDate;
  1. Tag.class：
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@Column(nullable = false, unique = true)
private String name;
  1. GiftCertificateToTagRelation.class：
@Id
@ManyToOne
@JoinColumn(name = DatabaseColumnName.GIFT_CERTIFICATE_ID)
private GiftCertificate giftCertificate;

@Id
@ManyToOne
@JoinColumn(name = DatabaseColumnName.TAG_ID)
private Tag tag;

我正在尝试什么：

Set<String> tagNames = ...

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<GiftCertificate> criteriaQuery = builder.createQuery(GiftCertificate.class);
Root<GiftCertificate> root = criteriaQuery.from(GiftCertificate.class);
Root<GiftCertificateToTagRelation> relationRoot = criteriaQuery.from(GiftCertificateToTagRelation.class);
Join<GiftCertificateToTagRelation, Tag> tagJoin = relationRoot.join("tag");
Predicate condition = tagJoin.get("name").in(tagNames);
criteriaQuery.where(condition)
    .groupBy(root)
    .having(builder.count(root).in(tagNames.size()));

作为这个查询的结果Hibernate，它会生成一个 SQL 查询（在传递两个标签名称的情况下）：

select *
from gift_certificate gc
         cross join gift_certificate_to_tag_relation relation
         inner join tag tag3_ on relation.tag_id = tag3_.id
where tag3_.name in ('tag1', 'tag2')
group by gc.id
having count(gc.id) in (2)
order by gc.id;

但是这个 SQL 代码返回一个空结果。我发现如果你在 line 中cross join添加一个条件 on gc.id = relation.gift_certificate_id，结果就是想要的结果：

select *
from gift_certificate gc
         cross join gift_certificate_to_tag_relation relation on gc.id = relation.gift_certificate_id
         inner join tag tag3_ on relation.tag_id = tag3_.id
where tag3_.name in ('tag1', 'tag2')
group by gc.id
having count(gc.id) in (2)
order by gc.id;

我知道条件ON是在Join<?, ?>. 但是 c 如何使用 Criteria Query 来设置这个词ON（on gc.id = relation.gift_certificate_id）？

sql

1 个回答

  1. Best Answer

    由于转换的结果如下所示：

    select *
from gift_certificate gc
         cross join gift_certificate_to_tag_relation relation
         inner join tag tag3_ on relation.tag_id = tag3_.id
where tag3_.name in ('tag1', 'tag2')
group by gc.id
having count(gc.id) in (2)
order by gc.id;

    在这里，该行cross join gift_certificate_to_tag_relation relation缺少 condition ON，添加后该行将采用以下形式：

    cross join gift_certificate_to_tag_relation relation on gc.id = relation.gift_certificate_id

    为了解决这个问题，CriteriaQuery你需要在字符串中添加这个条件inner join tag tag3_ on relation.tag_id = tag3_.id，那么最终的 SQL 查询会是这样的：

    select *
from gift_certificate gc
         cross join gift_certificate_to_tag_relation relation 
         inner join tag tag3_ on relation.tag_id = tag3_.id AND gc.id = relation.gift_certificate_id
where tag3_.name in ('tag1', 'tag2')
group by gc.id
having count(gc.id) in (2)
order by gc.id;

    我们将其翻译成CriteriaQuery：

    Root<GiftCertificateToTagRelation> relation = query.from(GiftCertificateToTagRelation.class);
Join<GiftCertificateToTagRelation, Tag> rel = relation.join(ParameterName.TAG);
Predicate on = builder.equal(root, relation.get(ParameterName.GIFT_CERTIFICATE));
rel.on(on); // inner join tag t on rel.tag_id = t.id and (gc.id = rel.gift_certificate_id)
Predicate condition = rel.get(ParameterName.NAME).in(tagNames);
    • 0

相关问题