mb124283

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.

The Java Persistence Query Language supports using relationships as defined in the object model in the query. The syntax of a relationship access expression is the same as in Java: a navigational expression using a dot as in o.customer. Please note, the query does not depend on the mapping of the relationship field to tables, columns and foreign keys in the underlying database schema. The type of the relationship field makes a difference, because a navigational expression is only allowed for single valued relationships.

Path Expressions:

A navigational expression might occur in any clause of a query. The first sample query navigates the Order<->Customer relationship in the WHERE clause selecting orders with a customer having the specified name. The other queries navigate the same relationship in the SELECT and GROUP BY clause:

     SELECT o FROM Order o WHERE o.customer.name = :name
     SELECT o.customer.name FROM Order o WHERE o.totalPrice > :limit
     SELECT AVG(o.totalPrice) FROM Order o GROUP BY o.customer

A path expression might navigate multiple single valued relationship fields. The following query navigates from an order to its customer to the related country to check a country state field:

     SELECT o FROM Order o WHERE o.customer.country.code = 'de'

JOIN Clause:

The FROM clause allows specifying an identification variable for a relationship field in a JOIN clause. The path expression in a JOIN clause always navigates a single relationship. Multiple navigations like o.customer.country are not allowed in a JOIN clause. Instead the FROM clause defines multiple JOIN clauses each of them navigating s single relationship. The following query is equivalent to the above query having a path with multiple navigations:

     SELECT o FROM Order o JOIN o.customer c JOIN c.country co 
     WHERE co.code = 'de'

Defining a JOIN variable might ease the query if the same relationship field is used multiple times. The following queries are equivalent:

     SELECT o FROM Order o 
     WHERE o.customer.name = :name AND o.customer.country = :country AND
           o.customer.city = :city

     SELECT o FROM Order o JOIN o.customer c 
     WHERE c.name = :name AND c.country = :country AND c.city = :city

A collection valued relationship field cannot be navigated in a path expression using a dot. Instead, the query declares an an identification variable to range over the elements of the collection. In the following queries the identification variableo denotes an element in the orders collection relationship of Customer, not the orders collection itself.

     SELECT c FROM Customer c JOIN c.orders o WHERE o.totalPrice > 1000
     SELECT c FROM Customer c, IN(c.orders) o WHERE o.totalPrice > 1000

Inner/Outer Joins:

A relationship navigation inside of a path expression uses inner join semantics. This means, if the relationship navigation evaluates to null this value is not used when determining the query result. In the following query an order without customer is not returned because the relationship navigation o.customer of the path expressiono.customer.name evaluates to null.

     SELECT o FROM Order o WHERE o.customer.name = :name

The JOIN clause supports inner and outer joins. An inner join has the form [INNER] JOIN path [AS] variable, an outer join uses LEFT JOIN or LEFT OUTER JOIN as keywords. Although the following query does not have a WHERE clause, it restricts the result to order instance having a customer.

     SELECT o FROM Order o JOIN o.customer c

The reason is the inner join specified as part of the FROM clause. It excludes any order without customer and returns the same result as

     SELECT o FROM Order o WHERE o.customer IS NOT NULL

Here is an example for an outer join query.

     SELECT c.name FROM Order o LEFT OUTER JOIN o.customer c

It returns the name of the related customer for all orders. The query result includes null for all orders without customer. So the outer join query might return more results (thenull values) as the corresponding query using an inner join in the FROM clause.

Fetch Join Clause:

The fetch join clause allows prefetching of relationships. The following query selects specific orders, but as a side effect of the query the related customers are fetched too:

     SELECT o FROM Order o JOIN FETCH o.customer 
     WHERE o.totalPrice > :limit

Collection valued relationships may also be prefetched:

     SELECT c FROM Customer c JOIN FETCH c.orders WHERE c.name = :name

Adding a fetch join clause does not change the query result. But it might improve the performance, because it allows the persistence provider to reduce the number of round trips to the database. A typical scenario is an application that executes a query, iterates the query result and then navigates a relationship field from the query result instances. This may result in multiple round trips to the database, because the relationships navigation executes its own SQL statement. A fetch join query might load the relationship fields along with the query result in a single SQL statement.

Other Expressions using Relationships:

The Java Persistence Query Language includes operators and functions that may be applied to relationship fields.

The IS NULL operator may be used to check whether a single valued relationship is null. This query returns orders not having a customer: SELECT o FROM Order o WHERE o.customer IS NULL

The IS EMPTY operator checks whether a collection has elements. This query returns customers without any order:SELECT c FROM Customer c WHERE c.orders IS EMPTY

The MEMBER OF operator checks whether a value is a member of a collection. This query returns the customer having the specified order in its orders collection: SELECT c FROM Customer c WHERE :order MEMBER OF c.orders

The SIZE function returns the number of elements in a collection. This query returns customers having at least 5 orders: SELECT c FROM Customer c WHERE SIZE(c.orders) >= 5