In SQL, ALL and ANY expressions are used in where clause. Such an expression returns true if all or any of a subquery results meet the where condition.

ANY is equivalent to SOME expression.

This tutorial will show how to use ALL or ANY expressions in JPA Criteria API.

all(), any(), some() methods of CriteriaBuilder

package javax.persistence.criteria;
 ....
public interface CriteriaBuilder {
 ....
    <Y> Expression<Y> all(Subquery<Y> subquery);
	
    <Y> Expression<Y> some(Subquery<Y> subquery);

    <Y> Expression<Y> any(Subquery<Y> subquery);
 .....   
}

Quick Example

Following example finds OrderItem where orderItem.quantity > ALL of the corresponding ProductInventory.quantity

 ....
   CriteriaQuery<OrderItem> orderItemQuery = criteriaBuilder.createQuery(OrderItem.class);
   Root<OrderItem> orderItem = orderItemQuery.from(OrderItem.class);

   Subquery<Integer> productInventorySubquery = orderItemQuery.subquery(Integer.class);
   Root<ProductInventory> productInventory = productInventorySubquery.from(ProductInventory.class);
   productInventorySubquery.select(productInventory.get(ProductInventory_.QUANTITY))
                           .where(criteriaBuilder.equal(productInventory.get(ProductInventory_.productName),
                                   orderItem.get(OrderItem_.productName)));

   orderItemQuery.select(orderItem).where(criteriaBuilder.greaterThan(
           orderItem.get(OrderItem_.QUANTITY), criteriaBuilder.all(productInventorySubquery)));

   TypedQuery<OrderItem> typedQuery = entityManager.createQuery(orderItemQuery);
   List<OrderItem> list = typedQuery.getResultList();
 ....

Equivalent JPQL:

SELECT o FROM OrderItem o WHERE o.quantity >  ALL (SELECT p.quantity FROM ProductInventory p 
                                                             WHERE p.productName = o.productName)

Complete Example

Entities

@Entity
public class OrderItem {
  @Id
  @GeneratedValue
  private long id;
  private String productName;
  private int quantity;
    .............
}

@Entity
public class ProductInventory {
  @Id
  @GeneratedValue
  private long id;
  private String productName;
  private int quantity;
  private String inventoryLocation;
    .............
}

Using all() and any() methods

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEntities();
          executeAllQuery();
          executeAnyQuery();
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void executeAllQuery() {
      System.out.println("-- finding OrderItem which have qty > ALL inventories qty --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<OrderItem> orderItemQuery = criteriaBuilder.createQuery(OrderItem.class);
      Root<OrderItem> orderItem = orderItemQuery.from(OrderItem.class);

      Subquery<Integer> productInventorySubquery = orderItemQuery.subquery(Integer.class);
      Root<ProductInventory> productInventory = productInventorySubquery.from(ProductInventory.class);
      productInventorySubquery.select(productInventory.get(ProductInventory_.QUANTITY))
                              .where(criteriaBuilder.equal(productInventory.get(ProductInventory_.productName),
                                      orderItem.get(OrderItem_.productName)));

      orderItemQuery.select(orderItem).where(criteriaBuilder.greaterThan(
              orderItem.get(OrderItem_.QUANTITY), criteriaBuilder.all(productInventorySubquery)));

      TypedQuery<OrderItem> typedQuery = entityManager.createQuery(orderItemQuery);
      List<OrderItem> list = typedQuery.getResultList();
      list.forEach(System.out::println);
      entityManager.close();
  }

  private static void executeAnyQuery() {
      System.out.println("-- finding OrderItem which have qty > ANY inventories qty --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<OrderItem> orderItemQuery = criteriaBuilder.createQuery(OrderItem.class);
      Root<OrderItem> orderItem = orderItemQuery.from(OrderItem.class);

      Subquery<Integer> productInventorySubquery = orderItemQuery.subquery(Integer.class);
      Root<ProductInventory> productInventory = productInventorySubquery.from(ProductInventory.class);
      productInventorySubquery.select(productInventory.get(ProductInventory_.QUANTITY))
                              .where(criteriaBuilder.equal(productInventory.get(ProductInventory_.productName),
                                      orderItem.get(OrderItem_.productName)));

      orderItemQuery.select(orderItem).where(criteriaBuilder.greaterThan(
              orderItem.get(OrderItem_.QUANTITY), criteriaBuilder.any(productInventorySubquery)));

      TypedQuery<OrderItem> typedQuery = entityManager.createQuery(orderItemQuery);
      List<OrderItem> list = typedQuery.getResultList();
      list.forEach(System.out::println);
      entityManager.close();
  }

  public static void persistEntities() {
      System.out.println("-- persisting entities --");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      getProductInventoryList().forEach(em::persist);
      getOrderItemList().forEach(em::persist);
      em.getTransaction().commit();

      System.out.println("-- entities persisted --");
      em.createQuery("SELECT p FROM ProductInventory p")
        .getResultList()
        .forEach(System.out::println);

      em.createQuery("SELECT o FROM OrderItem o")
        .getResultList()
        .forEach(System.out::println);
      em.close();

  }

  private static List<ProductInventory> getProductInventoryList() {
      List<ProductInventory> list = new ArrayList<>();
      list.add(ProductInventory.of("Monitor", 10, "Vritville"));
      list.add(ProductInventory.of("Monitor", 50, "Lonpore"));
      list.add(ProductInventory.of("Desktop Computer", 15, "Vritville"));
      list.add(ProductInventory.of("Desktop Computer", 20, "Lonpore"));
      list.add(ProductInventory.of("Laptop", 25, "Vritville"));
      list.add(ProductInventory.of("Laptop", 30, "Lonpore"));
      list.add(ProductInventory.of("SSD", 45, "Vritville"));
      list.add(ProductInventory.of("SSD", 30, "Lonpore"));
      return list;
  }

  private static List<OrderItem> getOrderItemList() {
      List<OrderItem> list = new ArrayList<>();
      list.add(OrderItem.of("Monitor", 60));
      list.add(OrderItem.of("Desktop Computer", 9));
      list.add(OrderItem.of("Laptop", 25));
      list.add(OrderItem.of("SSD", 40));
      return list;
  }
}

-- persisting entities --
-- entities persisted --
ProductInventory{id=1, productName='Monitor', quantity=10, inventoryLocation='Vritville'}
ProductInventory{id=2, productName='Monitor', quantity=50, inventoryLocation='Lonpore'}
ProductInventory{id=3, productName='Desktop Computer', quantity=15, inventoryLocation='Vritville'}
ProductInventory{id=4, productName='Desktop Computer', quantity=20, inventoryLocation='Lonpore'}
ProductInventory{id=5, productName='Laptop', quantity=25, inventoryLocation='Vritville'}
ProductInventory{id=6, productName='Laptop', quantity=30, inventoryLocation='Lonpore'}
ProductInventory{id=7, productName='SSD', quantity=45, inventoryLocation='Vritville'}
ProductInventory{id=8, productName='SSD', quantity=30, inventoryLocation='Lonpore'}
OrderItem{id=9, productName='Monitor', quantity=60}
OrderItem{id=10, productName='Desktop Computer', quantity=9}
OrderItem{id=11, productName='Laptop', quantity=25}
OrderItem{id=12, productName='SSD', quantity=40}
-- finding OrderItem which have qty > ALL inventories qty --
OrderItem{id=9, productName='Monitor', quantity=60}
-- finding OrderItem which have qty > ANY inventories qty --
OrderItem{id=9, productName='Monitor', quantity=60}
OrderItem{id=12, productName='SSD', quantity=40}

Example Project

Dependencies and Technologies Used:

• hibernate-core 5.4.1.Final: Hibernate's core ORM functionality.
  Implements javax.persistence:javax.persistence-api version 2.2
• hibernate-jpamodelgen 5.4.1.Final: Annotation Processor to generate JPA 2 static metamodel classes.
• h2 1.4.197: H2 Database Engine.
• JDK 1.8
• Maven 3.5.4