2 posts

The Java Persistence API (JPA) defines a query language that allows to define queries over entities and their persistent state. In this weblog I would like to point out what determines the type of the query result, such as the method to execute the query and the structure of the query SELECT clause.

Query API execution methods

An instance of the interface javax.persistence.Query represents a query at runtime. The interface provides three methods to execute the query:

    List getResultList()
    Object getSingleResult()
    int executeUpdate()

The first two methods are used to execute a SELECT query. Method getResultList returns the query result as a list, so the following query returns a list of Employee instances:

    Query q = em.createQuery("SELECT e FROM Employee e WHERE e.name = :name");
    q.setParameter("name", "Michael");
    List<Employee> result = q.getResultList();

You can use method getSingleResult in case you know the query returns exactly one instance, e.g. if you retrieve an instance by its primary key field and you know the instance exists or if you execute an aggregate query. Here are two sample queries: the first query returns a single Employee instance, the aggregate query returns a Long.

    Query q1 = em.createQuery("SELECT e FROM Employee e WHERE e.id = :id");
    q1.setParameter("id", "4711");
    Employee e = (Employee)q1.getSingleResult();

    Query q2 = em.createQuery(
        "SELECT COUNT(e) FROM Employee e WHERE e.salary > :limit");
    q2.setParameter("limit", new Double(10000.0));
    Long count = (Long)q2.getSingleResult();

Method getSingleResult throws a NoResultException if the query does not have a result and it throws a NonUniqueResultException if it would return more than one result.

The third method executeUpdate is used to execute a bulk UPDATE or bulk DELETE query. It returns the number of entities modified by the query:

    Query q1 = em.createQuery(
        "UPDATE Employee e SET e.salary = e.salary + 100 WHERE e.name = :name");
    q1.setParameter("name", "Michael");
    int updated = q1.executeUpdate();

    Query q2 = em.createQuery("DELETE FROM Employee e WHERE e.name = 'Michael');
    int deleted = q2.executeUpdate();

Shape of a SELECT query result

For a SELECT query the shape of the single result or the list result elements depends on the following:

  • The number of SELECT clause expressions
  • The type(s) of the SELECT clause expression(s)
  • Constructor expressions
  • Aggregate functions
  • GROUP BY clause

Single SELECT clause expression

In case of a single expression in the SELECT clause the expression type determines the type of the query result. If the query is executed using getSingleResult the instance returned by getSingleResult is of the expression type. Method getResultList returns a list of instance of this type. Primitive type values are wrapped into a instances of their corresponding Java wrapper classes.

The following sample queries return Employee instances, Department instances and Double instances if the state field salary is of type double or Double:

    SELECT e FROM Employee e
    SELECT e.department FROM Employee e
    SELECT e.salary FROM Employee e

Multiple SELECT clause expressions

A query having multiple SELECT clause expression wraps the values of these expression into an Object[]. The array has as many elements as there are expressions in the SELECT clause and the order of the array elements corresponds to the expression order in the query definition.

Here are two sample queries having multiple SELECT clause expressions:

    Query q1 = em.createQuery("SELECT e.name, e.salary FROM Employee e");
    List<Object[]> result1 = q1.getResultList();
    for (Object[] resultElement : result1) {
        String name = (String)resultElement[0];
        Double salary = (Double)resultElement[1];

    Query q2 = em.createQuery("SELECT e.name, e.salary, e.department FROM Employee e");
    List<Object[]> result2 = q2.getResultList();
    for (Object[] resultElement : result2) {
        String name = (String)resultElement[0];
        Double salary = (Double)resultElement[1];
        Department dept = (Department)resultElement[2];

    Query q3 = em.createQuery("SELECT COUNT(e), SUM(e.salary) FROM Employee e");
    Object[] result3 = (Object[])q3.getSingleResult();
    Long count = (Long)result3[0];
    Double sum = (Double)result3[1];

The result element of the first sample query is an array with two elements: a String representing the employee's name and a Double which is the employee's salary. The second sample query adds a the employee's department as third element to the Object[]. The third query returns a Long and Double value wrapped in a single Object[].

Constructor expressions

The constructor expression allows to wrap values of multiple select clause expressions into a user specified class different from Object[]. Suppose there is a class called EmployeeDetail with properties id, name and salary and a constructor taking these properties as arguments. The following query selects the employee state field values and wraps them into a EmployeeDetail instance:

    Query q = em.createQuery(
        "SELECT NEW mypackage.EmployeeDetail(e.id, e.name, e.salary) FROM Employee e");
    List<EmployeeDetail> result = q.getResultList();

Please note, a constructor expression is a regular SELECT clause expression, so it might be just one expression in a SELECT clause having multiple expressions. The following query returns an array with an EmployeeDetail as first element and the employee's department as the second element:

    Query q = em.createQuery(
        "SELECT NEW mypackage.EmployeeDetail(e.id, e.name, e.salary), e.department " + 
        "FROM Employee e");
    List<Object[]> result = em.getResultList();
    for (Object[] resultElement : result) {
        EmployeeDetail detail = (EmployeeDetail)resultElement[0];
        Department dept = (Department)resultElement[1];

Aggregate functions

The result of an aggregate expression depends on the aggregate function and the type of the expression the aggregate function is applied on:

  • COUNT always returns Long, e.g.
    SELECT COUNT(o) FROM Order o WHERE o.customer = :cust
  • MAX and MIN return the type of the expression they are applied on, e.g the following query returns a Float if the field totalPrice is of type float:
    SELECT MAX(o.totalPrice) FROM Order o WHERE o.customer = :cust
  • AVG always returns Double, e.g
    SELECT AVG(o.totalPrice) FROM Order o WHERE o.customer = :cust
  • SUM returns Long for an integral type, Double for a floating point types, BigInteger for BigInteger and BigDecimal for BigDecimal, e.g. the following query returns a Double, because totalPrice has a floating point type:
    SELECT SUM(o.totalPrice) FROM Order o WHERE o.customer = :cust


GROUP BY clause

Aggregate functions are most useful when used with a grouping query. Grouping does not change the typing of the query result, but it changes the number of query result elements. The SELECT clause is evaluated for each group. The following query groups the employees by their department and returns the employee's department, the number of employees per group and their average salary.

    Query q = em.createQuery("SELECT e.department, AVG(e.salary), COUNT(e) " +
                             "FROM Employee e GROUP BY e.department");
    List<Object[]> result = q.getResultList();

The query result list has as many elements as there are groups. Each element is an Object[] consisting of three items: a department instance, a Long and a Double.