Using a SELECT clause
Although you saw some examples of the select clause at the beginning of this topic, we avoided a detailed discussion of the select clause until now. From the previous examples it is evident that the select clause denotes the result of the query. Here is the JPQL syntax of SELECT clause:
You can also use one or more path expressions in the select clause:
A select clause may have more than one identifier variable, one or more single-value path expressions, or aggregate functions separated by commas. Earlier we used an identifier in the select clause as follows:
The expressions used in the select clause have to be single value. In other words, you cannot have a collection-value path expression in the clause. The path expressions can be an association field, as in the previous example, where c.createdBy is an association field of the Category entity.
The previous query may return duplicate entities. If you want the result not to contain duplicate data, use the distinct keyword in this way:
The following select statement is invalid:
because c.items is a collection-type association field, and collection-value path expressions are not allowed in a select clause. We’ll talk about using aggregate functions in the select clause in the next section.
Using a constructor expression in a SELECT clause
You can use a constructor in a select clause to return one or more Java instances. This is particularly useful when you want to create instances in a query that are initialized with data retrieved from a subquery:
The specified class does not have to be mapped to the database, nor is it required to be an entity.
Polymorphic queries
You may be surprised to find out that JPA supports polymorphism, and that JPQL queries are polymorphic. This means a JPQL query to retrieve a parent entity in an entity hierarchy is not just limited to the entity, but retrieves all subclasses as well. For example, in ActionBazaar any query to retrieve User entities will retrieve its subclasses, such as Seller, Bidder, and Admin.
Suppose we have a query like this:
The query will retrieve all instances of Seller, Bidder, Admin, and so forth that match this query condition. How do you handle a polymorphic query in your client code? Consider the following:
This code snippet uses the instanceof keyword to test user. Some Java gurus recommend you avoid using instanceof, but we use it here as a last resort. You have to ensure that your operations are just as polymorphic as your queries! In our example, you can easily convert the operations to be polymorphic by adding a getRate method in all entities. The getRate method will return the commissionRate for the Seller entity, whereas it will return the discount-Rate for the Bidder entity. The resulting code should look like this:
Using aggregations
Aggregations are useful when writing report queries that deal with a collection of entities. In this section we’ll discuss support of aggregate functions in JPQL.
Aggregate functions
JPQL provides these aggregate functions: avg, count, max, min, and sum. Each function’s name suggests its purpose. The aggregate functions are commonly used in creating report queries. You can only use a persistence field with the AVG, MAX, MIN, and SUM functions, but you can use any type of path expression or identifier with the count function.
Table 10.13 shows all the aggregate functions supported by JPQL.
Table 10.13 JPQL aggregate functions
|
Description |
Return Type |
|
|
AVG |
Returns the average value of all values of the field it is applied to |
Double |
|
COUNT |
Returns the number of results returned by the query |
Long |
|
MAX |
Returns the maximum value of the field it is applied to |
Depends on the type of the persistence field |
|
MIN |
Returns the minimum value of the field it is applied to |
Depends on the type of the persistence field |
|
SUM |
Returns the sum of all values on the field it is applied to |
May return either Long or Double |
If we want to find the MAX value for the i.itemPrice field among all Items, use the following query:
You’ve just seen some simple examples of aggregate functions. In the next section you’ll learn how to aggregate results based on a path expression.
Grouping with GROUP BY and HAVING
If you want to find out how many Category entities exist in the system, use COUNT like this:
In an enterprise business application, you may need to group data by some persistence field. Assuming that there is a one-many relationship between User and Category, this query will generate a report that lists the number of Category entities created by each c.user:
In addition, you can have a WHERE clause in a query along with a GROUP BY clause such as
As you can see, we have grouped by an associated entity. You can group by a single-value path expression that is either a persistence or an association field. Only aggregate functions are allowed when you perform aggregation using GROUP BY. You can also filter the results of an aggregated query with a having clause. Suppose you want to retrieve only the Users who have created more than five Category entities. Simply modify the previous query as follows:
A WHERE clause in a query containing both the GROUP BY and HAVING clauses results in multistage processing. First, the where clause is applied to filter the results. Then, the results are aggregated based on the group by clause. Finally, the having clause is applied to filter the aggregated result.
Ordering the query result
You can control the order of the values and objects retrieved by a query by using the order by clause:
Here is an example JPQL query with an ORDER BY clause. In this case we want to retrieve all Category entities and sort them alphabetically by c.categoryName.
By specifying asc, we’ve indicated that we want the result set to be ordered in ascending order by c.categoryName. Specifying ASC is optional; if you leave it off, then the persistence provider will assume you want ascending order by default.
If you want results sorted in descending order, then specify desc for the path expression. You can use compound ordering to further customize the sorting of the query results by using
Keep in mind that if you use single-value path expressions instead of an identifier variable, the select clause must contain the path expression that is used in the ORDER BY clause. The previous example used c.categoryName and c.createDate in the ORDER BY clause. Therefore, c.categoryName and c.createDate must also be used in the select clause unless you use the identifier variable in the select statement. This next JPQL snippet is invalid because the ORDER BY clause contains c.createDate but the SELECT clause does not:
In a JPQL query that contains both ORDER BY and WHERE clauses, the result is filtered based on the where clause first, and then the filtered result is ordered using the ORDER BY clause.
Using subqueries
A subquery is a query inside a query. You use a subquery in either a WHERE or HAVING clause to filter the result set. Unlike SQL subqueries, EJB 3 subqueries are not supported in the FROM clause. If you have a subquery in a JPQL query, the subquery will be evaluated first, and then the main query is retrieved based on the result of the subquery.
Here is the syntax for the subquery:
From the syntax of the language, it is clear that you can use in, exists, all, any, or some with a subquery. Let’s look at some examples of subqueries in more detail.
Using IN with a subquery
We’ve already discussed using the in operator where a single-value path expression is evaluated against a list of values. You can use a subquery to produce a list of results:
In this query, first the subquery (in parentheses) is executed to retrieve a list of users, and then the i.item path expression is evaluated against the list.
EXISTS
EXISTS (or NOT exists) tests whether the subquery contains any result set. It returns true if the subquery contains at least one result and false otherwise. Here is an example illustrating the exists clause:
If you look carefully at the result of this subquery, you’ll notice that it is the same as the query example we used in the previous section with the IN operator. An EXISTS clause is generally preferred over IN, particularly when the underlying tables contain a large number of records. This is because databases typically perform better when using EXISTS. Again, this is due to the work of the query processor translating JPQL queries into SQL by the persistence provider.
ANY, ALL, and SOME
Using the ANY, ALL, and SOME operators is similar to using the IN operator. You can use these operators with any numeric comparison operators, such as =, >, >=,<, <= and <>.
If we include the ALL predicate, the subquery returns true if all the results retrieved by the subquery meet the condition; otherwise, the expression returns false. In our example the subquery returns false if any item in the subquery has a createDate later than the createDate for the category in the main query.
As the name suggests, if we use ANY or SOME, the expression returns true if any of the retrieved results meet the query condition. We can use ANY in a query as follows:
SOME is just an alias (or a synonym) for ANY, and can be used anywhere ANY can be used.
Joining entities
If you’ve used relational databases and SQL, you must have some experience with the join operator. You can use join to create a Cartesian product between two entities. Normally you provide a where clause to specify the join condition between entities instead of just creating a Cartesian product.
You have to specify the entities in the from clause to create a join between two or more entities. The two entities are joined based either on their relationships or any arbitrary persistence fields. When two entities are joined, you may decide to retrieve results that match the join conditions. For example, suppose we join Category and Item using the relationships between them and retrieve only entities that match the join condition. Such joins are known as inner joins. Conversely, suppose we need to retrieve results that satisfy the join conditions but also include entities from one side of the domain that don’t have matching entities on the other side. For example, we may want to retrieve all instances of Category even if there is no matching instance of Item. This type of join is called an outer join. Note that an outer join can be left, right, or both.
Here is an example of a subquery demonstrating the ALL operator:
Let’s first look at some examples of different types of inner joins. Then we’ll see examples of joins based on arbitrary persistence fields and relationships, and finally we’ll look at outer joins and fetch joins.
Theta-joins
Theta-joins are not very common, and are based on arbitrary persistence or association fields in the entities being joined, rather than the relationship defined between them. For example, in the ActionBazaar system we have a persistence field named rating that stores the rating for a Category. The values for rating include deluxe, gold, standard, and premium. We also have a persistence field named star that we use to store a star rating for an Item; the values for star also include deluxe, gold, standard, and premium. Assume that both persistence fields store some common values in these fields, such as gold, and we want to join these two entities based on the rating and star fields of Category and Item, respectively. To accomplish this, we use this query:
Although this type of join is less common in applications, it cannot be ruled out. Relationship joins
A more common situation in applications is the need to join two or more entities based on their relationships. Here is the syntax for INNER JOIN:
In ActionBazaar, Category and User entities have a many-to-one association. To retrieve all users that match a specific criterion we could try this query:
Remember that when you use the JOIN operator by itself, an inner join is always performed. Now let’s move to the other end of the spectrum: outer joins.
Outer joins
Outer joins allow you to retrieve additional entities that do not match the join conditions when associations between entities are optional. Outer joins are particularly useful in reporting. Let’s assume that there is an optional relationship between User and Category and we want to generate a report that prints all the Category names for the user. If the user doesn’t have any Category, then we want to print NULL. If we specify the User on the left side of the JOIN, we can use either the left join or left outer join keyword phrases with a JPQL query as follows:
This will also retrieve User entities that do not have a matching Category, as well as Users that do. It’s worth noting that, if an outer join is not used, the query would only retrieve the users with the matching Category, but would fail to retrieve users that do not have a matching Category.
Are there any other types of joins supported by JPQL? We’re glad you asked! The final type is called the fetch join.
Fetch joins
In a typical business application, you may want to query for a particular entity but also retrieve its associated entities at the same time. For example, when we retrieve a Bid in the ActionBazaar system, we want to eagerly load and initialize the associated instance of Bidder. We can use a fetch join in JPQL to retrieve an associated entity as a side effect of the retrieval of an entity:
A fetch join is generally useful when you have lazy loading enabled for your relationship but you want to eagerly load the related entities in a specific query. You can use fetch join with both inner and outer joins.
Did you have any idea there was so much to JPQL? If you didn’t know any better you might think it was a whole other language… Oh wait, it is! And it’s just waiting for you to give it a test drive. We hope you were able to get your bearings so that you can get started with JPQL and put it to work in your applications.
We’re in the home stretch of this topic, with only a couple of topics left. We still need to discuss native SQL queries, but first we’ll talk about bulk updates and deletes.
Bulk updates and deletes
ActionBazaar categorizes its users by Gold, Platinum, and similar terms based on the number of successful trades in a year. At the end of the year, an application module is executed that appropriately set the user status. You could run a query to retrieve the collection of User entities and then iterate through the collection and update the status. An easier way is to use a bulk update statement to update the collection of entities matching the condition, as in this example:
You’ve seen some examples of delete and update statements in JPQL in previous sections, but we avoided any in-depth discussion until now. Let’s assume that ActionBazaar administrators need functionality to remove instances of entities such as Users based on certain conditions. We start with the following code:
In this code, the use of update and delete statements is quite similar to using any other JPQL statements, except for two significant differences. First, we use the executeUpdate method of the Query interface to perform bulk updates and deletes instead of getResultList or getSingleResult. Second, we must invoke executeUpdate within an active transaction.
Because bulk updates and deletes involve many pitfalls, we recommend that you isolate any bulk operations to a discrete transaction, because they are directly translated into database operations and may cause inconsistencies between managed entities and the database. Vendors are only required to execute the update or delete operations, and not required to modify any changes to the managed entities according the specification. In other words, the persistence provider won’t remove any associated entities when an entity is removed as a result of a bulk operation.
At this point, we’ve covered a lot of ground: queries, annotations, and JPQL. There’s only one topic left to discuss in this arena: using regular SQL queries in EJB 3.
