JPA not in subqueries CriteriaBuilder

最新推荐文章于 2024-08-18 09:17:52 发布

原创最新推荐文章于 2024-08-18 09:17:52 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#jpa

JPA 专栏收录该内容

1 篇文章

订阅专栏

本文探讨了如何利用JPACriteriaBuilder解决强类型化实体中的子查询问题，具体场景为通过ProductOwner实体查询未拥有任何产品的记录。通过构建JPACriteriaBuilder查询，实现SQL中的子查询功能，从而高效地获取所需数据。

Writing Strongly Typed NOT IN Subqueries with JPA CriteriaBuilder

Written by Sean Ryan on May 9, 2012. Posted in Misc Ramblings

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();