Writing Strongly Typed NOT IN Subqueries with JPA CriteriaBuilder
This one took a while to figure out and it required a few beers.
Let’s say you have an entity called Product and an entity called ProductOwner and there is a 1 to n relationship from ProductOwner to Product but you can only access Product from ProductOwner.
This may not be the way the database is designed – that would be terrible, but it may be the way your model has been designed. Probably an oversight but in any case, now you’re stuck with it.
What do you do if you want find out how many ProductOwners don’t actually own any products? It can happen, but because of the design of the model, it’s not obvious how to fetch those records.
In SQL, it’s easy…
select * from ProductOwner po where po.id not in (select p.id from product)
Using JPA CriteriaBuilder you would do the following.
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<ProductOwner> query = cb.createQuery(ProductOwner.class); Root<ProductOwner> poRoot = query.from(ProductOwner.class); query.select(poRoot); Subquery<Product> subquery = query.subquery(Product.class); Root<Product> subRoot = subquery.from(Product.class); subquery.select(subRoot); Predicate p = cb.equal(subRoot.get(Product_.productOwner),poRoot); subquery.where(p); query.where(cb.not(cb.exists(subquery))); TypedQuery<ProductOwner> typedQuery = entityManager.createQuery(query); List<ProductOwner> result = typedQuery.getResultList();
相关推荐
Learn to use the Java Persistence API (JPA) and other related APIs as found in the Java EE 8 platform from the perspective of one of the specification creators. A one-of-a-kind resource, this in-depth...
Web Service -Modeling Your First JPA Entity in CE 7.1
hibernate常见异常,hibernatejpa注解使用开发的解决方案
JPA教程JPA教程JPA教程JPA教程JPA教程
jpa jpa规范 jpa源码 jpa jpa规范 jpa源码
jpajpajpajpajpajpajpajpajpajpajpajpajpa
包括TOPLink JPA,Hibernate JPA,Open Jpa,jpa批注
jpa in action pdf 中文 不是真实这个系列的,是我自己写的。虽然很多地方语句不通顺,不过也很用心,花了很长时间写的,看看还是可以的。(主要我语文太差了,打小语文就不好)请勿转载 写的很一般,等做完手里的...
JPA视频_PPT及源码 · 1. JPA视频_概述 · 2. JPA视频_HelloWorld · 3. JPA视频_基本注解 · 4. JPA视频_Transient注解 · 5. JPA视频_Temporal注解 · 6. JPA视频_Table主键生成策略 · 7. JPA视频_...
jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联映射jpa 关联...
Spring Data JPA API。 Spring Data JPA 开发文档。 官网 Spring Data JPA API。
JPA
Spring Data JPA 简化 JPA 开发
JPA学习参考文档 JPA学习参考文档 JPA学习参考文档
这是JPA标签配置手册,从ORACLE官方得到的资料,里面对JPA所有的标签都进行详细的说明。 Index of Annotations • A o @AssociationOverride o @AssociationOverrides o @AttributeOverride o @AttributeOverrides ...
JPA JAPJPA JAPJPA JAPJPA JAPJPA JAPJPA JAPJPA JAPJPA JAPJPA JAPJPA JAP
jpa 全面学习资料 入门 实战 示例 jpa开发手册 JPA_全面讲解 JPA使用入门_基础 JPA学习笔记 JPA注解 JPA简介
spring注解完整版+spring data jpa官方文档中文翻译+JPA2.0官方文档 文档内容齐全 值得参考学习
Spring Data JPA中文文档1.4.3