Close

JPA Criteria API - Query Selection Other Than Entities

[Updated: Dec 11, 2018, Created: Aug 4, 2018]

This tutorial will show how to select columns other than entities.

Entity selection

In previous examples we saw how to select entities by using CriteriaQuery#select() i.e.

     CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);//query object
     Root<Employee> employee = query.from(Employee.class);//from part
     query.select(employee);//selection part
     List<Employee> employees = entityManager.createQuery(query).getResultList();

Single field selection

First, let's have a look at select() method signature in CriteriaQuery class:

CriteriaQuery<T> select(Selection<? extends T> selection);

The parameter Selection defines an item that is to be returned in a query result. In above entity selection example we selected Root<Employee> (query.select(employee)). Root class also implements Selection interface (Root > From > Path > Expression> Selection). To select a single field other entities we can use Root.get() method which returns a Path instance (also a sub-interface of Selection).
For example:

    CriteriaQuery<String> query = criteriaBuilder.createQuery(String.class);//query object
    Root<Employee> employee = query.from(Employee.class);//from part
    query.select(employee.get("name"));//single field selection
    List<String> list = entityManager.createQuery(query).getResultList()

Or using metamodel object:

     query.select(employee.get(Employee_.name));//single field selection   

Multiple fields selection

To select multiple fields, we need to pass CompoundSelection (sub-interface of Selection) to CriteriaQuery#select() method.

One way to create CompoundSelection is to use array() method of CriteriaBuilder:

CompoundSelection<Object[]> array(Selection<?>... selections);

For example:

    CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);//query object  
    Root<Employee> employee = query.from(Employee.class);
    query.select(criteriaBuilder.array(employee.get(Employee_.id), employee.get(Employee_.name)));
    List<Object[]> list = entityManger.createQuery(query).getResultList();

Alternatively we can use CriteriaQuery#multiselect() instead of CriteriaQuery#select().

 CriteriaQuery<T> multiselect(Selection<?>... selections);

For example:

    CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);//query object  
    Root<Employee> employee = query.from(Employee.class);
    query.multiselect(employee.get(Employee_.name), employee.get(Employee_.salary));
    List<Object[]> list = entityManager.createQuery(query).getResultList();

According to CriteriaQuery#multiselect() document:

If the type of the criteria query is CriteriaQuery<Object> or if the criteria query was created without specifying a type, and only a single argument is passed to the multiselect method, an instance of type Object will be returned for each row.
If the type of the criteria query is CriteriaQuery<Object> or if the criteria query was created without specifying a type, and more than one argument is passed to the multiselect method, an instance of type Object[] will be instantiated and returned for each row. The elements of the array will correspond to the arguments to the multiselect method, in the specified order.

Example

Entity

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String name;
  private double salary;
  private String dept;
    .............
}

Main class

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

  public static void main(String[] args) {
      try {
          persistEmployees();
          findAllEmployeeEntities();
          findEmployeeNames();
          findEmployeeNamesAndIds();
          findEmployeeNamesAndSalaries();
      } finally {
          entityManagerFactory.close();
      }
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", 2000, "IT");
      Employee employee2 = Employee.create("Rose", 4500, "Admin");
      Employee employee3 = Employee.create("Denise", 2000, "Admin");
      Employee employee4 = Employee.create("Mike", 4000, "IT");
      Employee employee5 = Employee.create("Linda", 4500, "Sales");
      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();
      em.close();
  }

  private static void findAllEmployeeEntities() {
      System.out.println("-- finding Employee entities - entity selection --");
      EntityManager em = entityManagerFactory.createEntityManager();
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<Employee> query = cb.createQuery(Employee.class);//query object
      Root<Employee> employee = query.from(Employee.class);//from part
      query.select(employee);//entity selection
      TypedQuery<Employee> typedQuery = em.createQuery(query);
      List<Employee> resultList = typedQuery.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findEmployeeNames() {
      System.out.println("-- Employee names - single field selection --");
      EntityManager em = entityManagerFactory.createEntityManager();
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<String> query = cb.createQuery(String.class);//query object
      Root<Employee> employee = query.from(Employee.class);//from part
      query.select(employee.get(Employee_.name));//query selection ('select x, y, ... from' part)
      TypedQuery<String> typedQuery = em.createQuery(query);
      List<String> resultList = typedQuery.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findEmployeeNamesAndIds() {
      System.out.println("-- Employee names and ids - multi selection using select() + array() --");
      EntityManager em = entityManagerFactory.createEntityManager();
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);//query object
      Root<Employee> employee = query.from(Employee.class);//from part
      query.select(cb.array(employee.get(Employee_.id), employee.get(Employee_.name)));//multiple selection
      TypedQuery<Object[]> typedQuery = em.createQuery(query);
      List<Object[]> resultList = typedQuery.getResultList();
                resultList.forEach(arr -> System.out.println(Arrays.toString(arr)));
      em.close();
  }

  private static void findEmployeeNamesAndSalaries() {
      System.out.println("-- Employee names and salaries - multi selection using multiselect() --");
      EntityManager em = entityManagerFactory.createEntityManager();
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);//query object
      Root<Employee> employee = query.from(Employee.class);//from part
      query.multiselect(employee.get(Employee_.name), employee.get(Employee_.salary));//multiple selection
      TypedQuery<Object[]> typedQuery = em.createQuery(query);
      typedQuery.getResultList()
                .forEach(arr -> System.out.println(Arrays.toString(arr)));
      em.close();
  }
}
-- finding Employee entities - entity selection --
Employee{id=1, name='Diana', salary=2000.0, dept='IT'}
Employee{id=2, name='Rose', salary=4500.0, dept='Admin'}
Employee{id=3, name='Denise', salary=2000.0, dept='Admin'}
Employee{id=4, name='Mike', salary=4000.0, dept='IT'}
Employee{id=5, name='Linda', salary=4500.0, dept='Sales'}
-- Employee names - single field selection --
Diana
Rose
Denise
Mike
Linda
-- Employee names and ids - multi selection using select() + array() --
[1, Diana]
[2, Rose]
[3, Denise]
[4, Mike]
[5, Linda]
-- Employee names and salaries - multi selection using multiselect() --
[Diana, 2000.0]
[Rose, 4500.0]
[Denise, 2000.0]
[Mike, 4000.0]
[Linda, 4500.0]

Example Project

Dependencies and Technologies Used:

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

Selection Other Than Entities Select All Download
  • jpa-criteria-api-select-list
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also