JPQL: What kind of objects contains a result list when querying multiple columns?

Question

Hello!

I'm trying to do something which is easy as pie in PHP & Co: SELECT COUNT(x) as numItems, AVG(y) as average, ... FROM Z

In PHP I would get a simple array like [{ numItems: 0, average: 0 }] which I could use like this:

echo "Number of Items: " . $result[0]['numItems'];

Usually in JPQL you only query single objects or single columns and get Lists types, for example List<SomeEntity> or List<Long>. But what do you get, when querying multiple columns?

Answer 1

You get an Object[] (or a List<Object[]>). From the section 4.8.1 Result Type of the SELECT Clause of the JPA 1.0 specification:

The result type of the SELECT clause is defined by the the result types of the select_expressions contained in it. When multiple select_expressions are used in the SELECT clause, the result of the query is of type Object[], and the elements in this result correspond in order to the order of their specification in the SELECT clause and in type to the result types of each of the select_expressions.

If you want strong typing, you can use a constructor expression in the SELECT clause. From the section 4.8.2 Constructor Expressions in the SELECT Clause:

A constructor may be used in the SELECT list to return one or more Java instances. The specified class is not required to be an entity or to be mapped to the database. The constructor name must be fully qualified.

If an entity class name is specified in the SELECT NEW clause, the resulting entity instances are in the new state.

SELECT NEW com.acme.example.CustomerDetails(c.id, c.status, o.count)
FROM Customer c JOIN c.orders o
WHERE o.count > 100