Java Persistence Query Language

More good examples in Java Persistence Query Language

I. Use of query parameters

Input Parameters can be positional parameters or named parameters. Positional and named parameters may not be mixed in a single query.

For positional parameters, input parameters are numbered starting from 1. A positional parameter can be used more than once and also the order does not matter.

Some working examples,
    String ejbql = "SELECT i FROM Item i WHERE i.name = ?1 AND i.itemId = ?2";
    Query query = em.createQuery(ejbql);
    query.setParameter(1, "foo");
    query.setParameter(2, new Integer(1));

    String ejbql = "SELECT i FROM Item i WHERE i.name = ?2 and i.itemId = ?1";
    Query query = em.createQuery(ejbql);
    query.setParameter(2, "foo");
    query.setParameter(1, new Integer(1));

A named parameter is an identifier that is prefixed by the ":" symbol. Named parameters are case sensitive.

For Example,
    String ejbql = "SELECT i FROM Item i WHERE i.name = :name AND i.itemId = :id";
    Query query = em.createQuery(ejbql);
    query.setParameter("name", "foo");
    query.setParameter("id", new Integer(1));

When using setParameter() to binding input parameters, the binding values should have the same type as the input parameters. Also all input parameters must have a binding value.

II. GROUP BY and HAVING queries

The GROUP BY clause enables the aggregation of values according to a set of properties. The HAVING clause enables conditions to be specified that further restrict the query result.

The syntax of the GROUP BY and HAVING clauses is as follows:

groupby_clause ::= GROUP BY groupby_item {, groupby_item}*
groupby_item ::= single_valued_path_expression | identification_variable
having_clause ::= HAVING conditional_expression

The requirements for GROUP BY clause is that any item that appears in the SELECT clause (other than as an argument to an aggregate function) must also appear in the GROUP BY clause.

The requirement for HAVING clause is that the conditional_expression must specify over the grouping items or aggregate functions applying to grouping items.

The specification of the Java Persistence Query Language says that support for HAVING without GROUP BY is permitted but not required. So the query compiler allows a HAVING query without GROUP BY clause and generates a SQL statement. But some databases do not support this (e.g. oracle) and then the Java Persistence query results in a SQLException.

Here are some working examples:
a. Group the orders by their customer and for each group return the customer and the average totalPrice:
    SELECT o.customer, AVG(o.totalPrice)
    FROM Order o
    GROUP BY o.customer

b. Group the customers by their country. For each group return the country and the number of customers in that group, but only if there are more than 3:
    SELECT c.country, COUNT(c.country)
    FROM Customer c
    GROUP BY c.country HAVING COUNT(c.country) > 3

c. Group the employees with a salary over a certain limit per the name of their department, but consider only sales departments (to be more precise: where the department name starts with 'Sales'). For each group return the department name, the average and maximum salary.
    SELECT e.department.name, AVG(e.salary), MAX(e.salary)
    FROM Employee WHERE e.salary > :limit
    GROUP BY e.department.name HAVING e.department.name LIKE 'Sales%'

III. NULL handling

NULL value handling is a little tricking. Basically, according to the specification of the Java Persistence Query Language, 'IS NULL' and '= NULL' have different semantics.

In general, the following is a brief description of NULL value semantics:

•  Comparison or arithmetic operations with a NULL value always yield an unknown value.
• Two NULL values are not considered to be equal, the comparison yields an unknown value.
• Comparison or arithmetic operations with an unknown value always yield an unknown value.
• The IS NULL and IS NOT NULL operators convert a NULL state-field or single-valued association-field value into the respective TRUE or FALSE value.

So the query
    SELECT p FROM Product p WHERE p.name = NULL
is legal, but it would not return any products, even if there are products without name. The reason is that the expression p.name = NULL does not evaluate to true if the name is null. It evaluates to an unknown value.
The same holds true for a query using an input parameter
    SELECT p FROM Product p WHERE p.name = :name
when setting the actual value of the input parameter to null.

The correct syntax for checking whether or not the singled-valued path expression or input parameter is a NULL value is as:

{single_valued_path_expression | input_parameter} IS [NOT] NULL

For example,
    SELECT p FROM Product p WHERE p.name IS NULL
will return all the products which are without name.

IV. IN expression

The syntax for the use of the comparison operator [NOT] IN in a conditional is as follows:

in_expression ::= state_field_path_expression [NOT] IN (in_item {, in_item}* | subquery)
in_item ::= literal | input_parameter

When the IN expression is used, there are three things need to keep in mind:
First, the state_field_path_expression must have a string, numeric or enum value. Other types will be not supported for IN expression.
Second, the in_item or set of in_item must have the same type as state_field_path_expression.
Third, the results of the subquery must have the same type as the state_field_path_expression.

Here are some working examples:
    SELECT o FROM Order o
    WHERE o.customer.name IN
    (SELECT c.name FROM Customer c WHERE c.customerId = 3)

    SELECT o FROM Order o
    WHERE o.customer.customerId IN (1, 2)

    SELECT o FROM Order o
    WHERE o.customer.name IN ('foo', 'bar')

Here are some examples with wrong queries:
a. The type of in_item does not match the type of o.customer.name.
    SELECT o FROM Order o WHERE o.customer.name IN (1.0)

b. The results of subquery is not match the type of o.customer.name
    SELECT o from Order o where o.customer.name IN (select c from Customer c where c.customerId = 3)

Posted at 03:00PM Jun 09, 2006 by jielin in Sun  |  Comments[3]