本文介绍了如何使用JPA Criteria Builder编写查询(包括子查询和存在查询)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
正在努力使用JPA编写以下查询。
Struggling to write the following query using JPA.
Oracle查询:
Select * from table1 s
where exists (Select 1 from table2 p
INNER JOIN table3 a ON a.table2_id = p.id
WHERE a.id = s.table3_id
AND p.name = 'Test');
此外,您想指出任何好的教程来用JPA编写复杂的查询。
Also, would you like to point any good tutorial to write complex queries in JPA.
推荐答案
我将使用 JpaRepository,JpaSpecificationExecutor,CriteriaQuery,CriteriaBuilder来回答简单汽车广告域(广告,品牌,模型)的示例:
- 品牌[一对多]模型
- 模型[一个-to-many]广告
实体:
@Entity
public class Brand {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;
@OneToMany(mappedBy = "brand", fetch = FetchType.EAGER)
private List<Model> models;
}
@Entity
public class Model {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private String name;
@ManyToOne
@JoinColumn(name = "brand_id")
private Brand brand;
}
@Entity
public class Advert {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@ManyToOne
@JoinColumn(name = "model_id")
private Model model;
private int year;
private int price;
}
存储库:
public interface AdvertRepository
extends JpaRepository<Advert, Long>, JpaSpecificationExecutor<Advert> {
}
规格:
public class AdvertSpecification implements Specification<Advert> {
private Long brandId;
public AdvertSpecification(Long brandId) {
this.brandId = brandId;
}
@Override
public Predicate toPredicate(Root<Advert> root,
CriteriaQuery<?> query,
CriteriaBuilder builder) {
Subquery<Model> subQuery = query.subquery(Model.class);
Root<Model> subRoot = subQuery.from(Model.class);
Predicate modelPredicate = builder.equal(root.get("model"), subRoot.get("id"));
Brand brand = new Brand();
brand.setId(brandId);
Predicate brandPredicate = builder.equal(subRoot.get("brand"), brand);
subQuery.select(subRoot).where(modelPredicate, brandPredicate);
return builder.exists(subQuery);
}
}
效果是此Hibernate SQL:
select advert0_.id as id1_0_,
advert0_.model_id as model_id5_0_,
advert0_.price as price3_0_,
advert0_.year as year4_0_
from advert advert0_
where exists (select model1_.id from model model1_
where advert0_.model_id=model1_.id
and model1_.brand_id=?)
这篇关于如何使用JPA Criteria Builder编写查询(包括子查询和存在查询)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!