Close

JPA Criteria API - Collection Operations size() and index()

[Updated: Mar 7, 2019, Created: Mar 7, 2019]

Size of a collection

In Criteria API, the size of a collection can be found by using following methods of CriteriaBuilder:

package javax.persistence.criteria;
 ........
public interface CriteriaBuilder {
    ........
    <C extends java.util.Collection<?>> Expression<Integer> size(Expression<C> collection);
    <C extends Collection<?>> Expression<Integer> size(C collection);
    ........	
}

Index of a collection

Following method of ListJoin create an expression that corresponds to the index of the object in the referenced association or collection.

package javax.persistence.criteria;
........	
public interface ListJoin<Z, E> extends PluralJoin<Z, List<E>, E> {
    .........
    Expression<Integer> index();
}

Example

Entity

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private double salary;
  private String dept;
  @ElementCollection
  @OrderColumn
  private List<String> phoneNumbers;
    .............
}

Using size() and index() methods

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

  public static void main(String[] args) {
      try {
          persistEmployees();
          findNumberOfPhoneNumbers();
          findEmployeeWithMoreThanOnePhoneNumber();
          findEmployeeWithMoreThanOnePhoneNumber2();
          findPhoneByIndex();

      } finally {
          entityManagerFactory.close();
      }
  }

  private static void findNumberOfPhoneNumbers() {
      System.out.println("-- Find number of phone numbers --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
      Root<Employee> employeeRoot = query.from(Employee.class);
      query.multiselect(employeeRoot.get(Employee_.id), employeeRoot.get(Employee_.name),
              criteriaBuilder.size(employeeRoot.get(Employee_.phoneNumbers.getName())))
           .groupBy(employeeRoot.get(Employee_.id), employeeRoot.get(Employee_.name));
      //The equivalent JPQL:
      //SELECT e.id, e.name, SIZE(e.phoneNumbers) from Employee e GROUP BY e.id, e.name

      List<Object[]> resultList = entityManager.createQuery(query).getResultList();
      resultList.forEach(o -> System.out.println(Arrays.toString(o)));
      entityManager.close();
  }

  private static void findEmployeeWithMoreThanOnePhoneNumber() {
      System.out.println("-- Find employees who have more than 1 phone number by using size() --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
      Root<Employee> employeeRoot = query.from(Employee.class);
      query.select(employeeRoot)
           .where(criteriaBuilder.greaterThan(criteriaBuilder.size(employeeRoot.get(Employee_.phoneNumbers)), 1));
      //The equivalent JPQL:
      //SELECT e from Employee e  WHERE SIZE(e.phoneNumbers) > 1

      entityManager.createQuery(query)
                   .getResultList()
                   .forEach(System.out::println);
  }

  private static void findEmployeeWithMoreThanOnePhoneNumber2() {
      System.out.println("-- Find employees who have more than 1 phone number by using index() --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
      Root<Employee> employeeRoot = query.from(Employee.class);
      ListJoin<Object, Object> phoneNumbersJoin = employeeRoot.joinList(Employee_.phoneNumbers.getName());
      query.select(employeeRoot)
           .distinct(true)
           .where(criteriaBuilder.greaterThan(phoneNumbersJoin.index(), 0));
      //The equivalent JPQL:
      //SELECT DISTINCT e from Employee e JOIN e.phoneNumbers p WHERE INDEX(p) > 0

      entityManager.createQuery(query)
                   .getResultList()
                   .forEach(System.out::println);
  }

  private static void findPhoneByIndex() {
      System.out.println("-- Find phones by index in phoneNumbers list --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
      Root<Employee> employeeRoot = query.from(Employee.class);
      ListJoin<Employee, String> phoneNumbersJoin = employeeRoot.joinList(Employee_.phoneNumbers.getName());

      query.multiselect(employeeRoot.get(Employee_.name), phoneNumbersJoin.index(), phoneNumbersJoin);
      //The equivalent JPQL:
      //Select e.name, INDEX(p), p from Employee e JOIN e.phoneNumbers p

      List<Object[]> resultList = entityManager.createQuery(query).getResultList();
      resultList.forEach(o -> System.out.println(Arrays.toString(o)));
      entityManager.close();
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", 2000, "IT", "111-111-111", "666-666-666", "777-777-777");
      Employee employee2 = Employee.create("Rose", 3500, "Admin", "222-222,222", "888-888-888");
      Employee employee3 = Employee.create("Denise", 2500, "Admin", "333-333-333");
      Employee employee4 = Employee.create("Mike", 4000, "IT", "444-444-444");
      Employee employee5 = Employee.create("Linda", 4500, "Sales", "555-555-555");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.persist(employee5);
      em.getTransaction().commit();

      System.out.println("-- Employees persisted --");
      Query query = em.createQuery(
              "SELECT e FROM Employee e");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }
}
-- Employees persisted --
Employee{id=1, name='Diana', salary=2000.0, dept='IT', phoneNumbers=[111-111-111, 666-666-666, 777-777-777]}
Employee{id=2, name='Rose', salary=3500.0, dept='Admin', phoneNumbers=[222-222,222, 888-888-888]}
Employee{id=3, name='Denise', salary=2500.0, dept='Admin', phoneNumbers=[333-333-333]}
Employee{id=4, name='Mike', salary=4000.0, dept='IT', phoneNumbers=[444-444-444]}
Employee{id=5, name='Linda', salary=4500.0, dept='Sales', phoneNumbers=[555-555-555]}
-- Find number of phone numbers --
[1, Diana, 3]
[2, Rose, 2]
[3, Denise, 1]
[4, Mike, 1]
[5, Linda, 1]
-- Find employees who have more than 1 phone number by using size() --
Employee{id=1, name='Diana', salary=2000.0, dept='IT', phoneNumbers={IndirectList: not instantiated}}
Employee{id=2, name='Rose', salary=3500.0, dept='Admin', phoneNumbers={IndirectList: not instantiated}}
Employee{id=3, name='Denise', salary=2500.0, dept='Admin', phoneNumbers={IndirectList: not instantiated}}
Employee{id=4, name='Mike', salary=4000.0, dept='IT', phoneNumbers={IndirectList: not instantiated}}
Employee{id=5, name='Linda', salary=4500.0, dept='Sales', phoneNumbers={IndirectList: not instantiated}}
-- Find employees who have more than 1 phone number by using index() --
Employee{id=1, name='Diana', salary=2000.0, dept='IT', phoneNumbers={IndirectList: not instantiated}}
Employee{id=2, name='Rose', salary=3500.0, dept='Admin', phoneNumbers={IndirectList: not instantiated}}
-- Find phones by index in phoneNumbers list --
[Rose, 0, 222-222,222]
[Rose, 1, 888-888-888]
[Linda, 0, 555-555-555]
[Mike, 0, 444-444-444]
[Denise, 0, 333-333-333]
[Diana, 0, 111-111-111]
[Diana, 1, 666-666-666]
[Diana, 2, 777-777-777]

Example Project

Dependencies and Technologies Used:

  • eclipselink 2.6.5: EclipseLink build based upon Git transaction b3d05bd.
    Related JPA version: org.eclipse.persistence:javax.persistence version 2.1.1
  • org.eclipse.persistence.jpa.modelgen.processor 2.6.5: EclipseLink build based upon Git transaction b3d05bd.
  • h2 1.4.198: H2 Database Engine.
  • JDK 1.8
  • Maven 3.5.4

The size() and index() methods in JPA Criteria API Select All Download
  • jpa-criteria-api-collection-methods
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also