JPA CriteriaBuilder - Use of Multiselect with select count(*)

Question

Is it possible to use the query result as column in my final query resultset with Multiselect?

For example:

Query 1:

select EMPLOYEE_NAME name, (Query 2) TOTAL_WORKING_DAYS FROM EMPLOYEE;

Query 2:

select COUNT(*) from WORKING_DAYS;

I was trying this:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();

Root<Employee> employeeRoot = criteriaQuery.from(Employee.class);
Path<String> employeeName = employeeRoot .get(Employee_.employeeName);

criteriaQuery.multiselect(employeeName , ??<This is the place where I am confused> );

List<Tuple> results = em.createQuery(criteriaQuery).getResultList();

Any help is highly appreciated?

you can test criteriaBuilder.count(criteriaBuilder.literal(1)) or criteriaBuilder.count(criteriaBuilder.sum(1)) — fatemeakbari, Aug 29 '22 at 18:42

score 1 · Answer 1 · answered Aug 19 '13 at 11:23

Just came to know that SubQueries is not possible inside a Select statement.

As per the JPA 2.0 Specification:

Subqueries may be used in the WHERE or HAVING clauses.

So I need to modify my query like below:

SELECT 
      EMPLOYEE_NAME name,
      COUNT(*) TOTAL_WORKING_DAYS
FROM EMPLOYEE INNER JOIN WORKING_DAYS ON
      EMPLOYEE.WORKING_DAY = WORKING_DAYS.ID;

Now this is really easy to implement with CriteriaBuilder query as Tuple (Multiselect). If you guys have any better idea, please share here. Thanks and happy coding.

JPA CriteriaBuilder - Use of Multiselect with select count(*)

1 Answers1