Close

JPA Criteria API - Multiple Query selection and use of Tuple

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

In JPA Criteria API, Tuple provides another way to select multiple values. Tuple contains ordered query results which can be access via index, type, alias and TupleElement.

A query involving Tuple can be created by using following method of CriteriaBuilder:

CriteriaQuery<Tuple> createTupleQuery();

For example

    CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
    Root<Employee> employee = query.from(Employee.class);
    query.select(criteriaBuilder.tuple(employee.get(Employee_.name), employee.get(Employee_.salary)));
    List<Tuple> resultList = em.createQuery(query).getResultList();

Or alternatively we can use CriteriaQuery#multiselect() method:

   CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
   Root<Employee> employee = query.from(Employee.class);
   query.multiselect(employee.get(Employee_.name), employee.get(Employee_.salary));
   List<Tuple> resultList = em.createQuery(query).getResultList();

According to CriteriaQuery#multiselect() document:

If the type of the criteria query is CriteriaQuery<Tuple> (i.e., a criteria query object created by either the createTupleQuery method or by passing a Tuple class argument to the createQuery method), a Tuple object corresponding to the arguments of the multiselect method, in the specified order, will be instantiated and returned for each row that results from the query execution.

Also creating Tuple Query via:

CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();

is same as:

CriteriaQuery<Tuple> query = criteriaBuilder.createQuery(Tuple.class);

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();
          findEmployeeInfo();
          findEmployeeInfo2();
          findEmployeeInfo3();
      } 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);
      Root<Employee> employee = query.from(Employee.class);
      query.select(employee);
      TypedQuery<Employee> typedQuery = em.createQuery(query);
      List<Employee> resultList = typedQuery.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findEmployeeInfo() {
      System.out.println("-- Employee name and salaries using Tuple --");
      EntityManager em = entityManagerFactory.createEntityManager();
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<Tuple> query = cb.createTupleQuery();
      Root<Employee> employee = query.from(Employee.class);
      query.select(cb.tuple(employee.get(Employee_.name),
              employee.get(Employee_.salary)));
      TypedQuery<Tuple> typedQuery = em.createQuery(query);
      List<Tuple> resultList = typedQuery.getResultList();
      resultList.forEach(tuple -> {
          System.out.printf("Name: %s, Salary: %s%n",
                  tuple.get(0, String.class), tuple.get(1, Double.class));
      });
      em.close();
  }

  private static void findEmployeeInfo2() {
      System.out.println("-- Employee name and salaries using multiselect --");
      EntityManager em = entityManagerFactory.createEntityManager();
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<Tuple> query = cb.createTupleQuery();
      Root<Employee> employee = query.from(Employee.class);
      query.multiselect(employee.get(Employee_.name), employee.get(Employee_.salary));
      TypedQuery<Tuple> typedQuery = em.createQuery(query);
      List<Tuple> resultList = typedQuery.getResultList();
      resultList.forEach(tuple -> {
          System.out.printf("Name: %s, Salary: %s%n",
                  tuple.get(0, String.class), tuple.get(1, Double.class));
      });
      em.close();
  }

  private static void findEmployeeInfo3() {
      System.out.println("-- Employee name and salaries using createQuery(Tuple.class) --");
      EntityManager em = entityManagerFactory.createEntityManager();
      CriteriaBuilder cb = em.getCriteriaBuilder();
      CriteriaQuery<Tuple> query = cb.createQuery(Tuple.class);
      Root<Employee> employee = query.from(Employee.class);
      query.select(cb.tuple(employee.get(Employee_.name),
              employee.get(Employee_.salary)));
      TypedQuery<Tuple> typedQuery = em.createQuery(query);
      List<Tuple> resultList = typedQuery.getResultList();
      resultList.forEach(tuple -> {
          System.out.printf("Name: %s, Salary: %s%n",
                  tuple.get(0, String.class), tuple.get(1, Double.class));
      });
      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 name and salaries using Tuple --
Name: Diana, Salary: 2000.0
Name: Rose, Salary: 4500.0
Name: Denise, Salary: 2000.0
Name: Mike, Salary: 4000.0
Name: Linda, Salary: 4500.0
-- Employee name and salaries using multiselect --
Name: Diana, Salary: 2000.0
Name: Rose, Salary: 4500.0
Name: Denise, Salary: 2000.0
Name: Mike, Salary: 4000.0
Name: Linda, Salary: 4500.0
-- Employee name and salaries using createQuery(Tuple.class) --
Name: Diana, Salary: 2000.0
Name: Rose, Salary: 4500.0
Name: Denise, Salary: 2000.0
Name: Mike, Salary: 4000.0
Name: Linda, Salary: 4500.0

Also check out how to use Tuple in JPQL.

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

Query selection and use of Tuple Example Select All Download
  • jpa-criteria-api-tuple
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also