Review of Java Persistence Query Language for the Component Developer Exam II

[Java Exam]

In the second part of this article we will look more in depth at the Java Persistence API Query Language Functions as well as native Structured Query Language (SQL) and the Criteria API in order to prepare you for the Component Developer Exam. If you want to review the first article, Tutorial: Review of Java Persistence Query Language for the Component Developer Exam

In the previous article, we introduced the Java Persistence Query Language and it’s Processor Engine. We noted that the Processor Engine translates from JPQL, which operates on classes and entities (objects) in Java to SQL which operates on tables, columns and rows. JPQL supports the three main statements that form also part of SQL (i.e. SELECT, UPDATE and DELETE statements) as well as named parameters, JOIN operations, subqueries, GROUP BY and HAVING operations and bulk updates and deletes. It now provides a comprehensive set of operations to facilitate all persistence operations on entities. Next we will cover the wide range of functions available in JPQL as well as the Criteria API.

Using JPQL Functions

JPQL provides a number of built-in functions for performing string or arithmetic operations. You can use these functions in either a WHERE or HAVING clause of a JPQL statement. The JPQL functions

String Functions

You can use string functions in the SELECT clause of a JPQL query; table 10.10 lists all string functions supported by JPQL. These functions are only meant to be used to filter the results of the query. You have to use the functions available in the Java language if you want to perform any string manipulations on your data. The primary reason is that in-memory string manipulation in your application will be much faster than doing the manipulation in the database.

Table: JPQL String Functions

Below are a number of common expressions that it is important to know. To compare the results of two string expressions concatenated with a string literal, you can use the following expression using the WHERE clause in conjunction with the CONCAT function:

WHERE CONCAT(t.todoId, t.description) = '12045Contact Administrator for the form'

If the concatenation does not result in 12045Contact Administrator for the form then the condition will return false.

Another example is the use of the SUBSTRING function to determine if the first part of t.description is Contact Administrator:

WHERE SUBSTRING(t.description, 1, 21) = 'Contact Administrator'

You can use the name of each string function to determine what operation it will perform. The name of each string function is a good indicator of the functional operation it can perform.

Arithmetic Functions

Math functions are primarily used to manipulate data for reports in either WHERE or HAVING clauses in JPQL. It is rarely used to perform CRUD operations. JPQL supports a minimum set of functions although some vendors provide additional functions to enhance the reporting capabilities of the vendor’s database. Below is the list of minimum set of JPQL arithmetic functions being supported:

Table: JPQL Arithmetic Functions

An example of using the arithmetic function SIZE is provided below:

WHERE SIZE(l.todos) = 32

Temporal Functions

JPQL provides a set of temporal functions that retrieves the current date, time, or timestamp from the database. Because of this, they may vary slightly from your JVM if they are not running on the same server. This can be easily resolved by running a time service on all servers in your environment. Below if the list of temporal functions available in JPQL:

Table: JPQL DATE/TIME Functions

Using Aggregate Functions

Aggregations are useful when writing report queries that deal with a collection of entities. In JPQL, the aggregate functions that are provided are: AVG, COUNT, MAX, MIN, and SUM. These functions are commonly used in creating report queries. With the functions, SUM, AVG, MAX and MIN, you need to use a persistence field in conjunction with the function. The function COUNT can be used with any type of path expression as well as any identifier. Below are the list of aggregate functions that are supported in JPQL:

Table: JPQL Aggregate Functions

Here are a couple of examples of using the aggregate functions. Below we have used have used the MAX function in conjunction with the t.priority field in order to select only the Todo entities with the highest priority:

SELECT MAX(t.priority) FROM Todo t

In this example, if you need to count the number of Todo entities presently available in the system, you could do this with the COUNT function:

SELECT COUNT(t) FROM Todo t

Grouping with GROUP BY and HAVING

Another means of aggregating values or data is by using the GROUP BY or HAVING operators. In the Tudo List application, we have a one-many relationship between TodoList and Todo, if we were generating a report that provides the number of TodoList for all the Todos, we would use the following query:

SELECT t.todoList, COUNT(t.todoId) FROM Todo t GROUP BY t.todoList

In this example, the grouping is by an associated entity. For a single value path expression you can group either by persistence or association field. When you use the GROUP BY for aggregation, you are only allowed to use the aggregate functions. You can also use the HAVING clause to filter the results of an aggregated query. If for example you want to retrieve only the TodoList who have more than five Todos. You can query in the following manner:

SELECT t.todoList, COUNT(t.todoId) FROM Todo t GROUP BY t.todoList HAVING COUNT(t.todoId) > 5

You can also use a WHERE clause in a query along with a GROUP BY clause. Including a
WHERE clause in a query containing both the GROUP BY and HAVING clauses will lead to multistage processing. The first stage is the WHERE clause is applied to filter the results. In the next stage the results are aggregated based on the GROUP BY clause. In the final stage, the HAVING clause is applied to filter the aggregated result.

Ordering the Query Result

A key functionality in queries is the ability to control the order of the values and objects retrieved by a query. We do this by use of the ORDER BY clause. The syntax of the ORDER BY clause is shown below:

ORDER BY path_expression1 [ASC | DESC], ... path_expressionN [ASC | DESC]

In order to understand more concretely we will provide an example of an JPQL query with an ORDER BY clause. WE can retrieve all of the TodoList entities and then order them by the name of the TodoList:

SELECT l FROM TodoList l ORDER BY l.name DESC

By specifying DES, we’ve specified that the result set should be ordered in descending order by l.name. We have done this because the default of the persistence provider is to order the column in ascending order. It is also possible to use compound ordering to provide more fine grain control over the sorting of the query results. An example is shown below:

SELECT l.name, l.lastUpdate FROM TodoList l ORDER BY l.name ASC, l.lastUpdate DESC

If you choose to use a single-value path expressions rather than an identifier variable, the SELECT clause must have the path expression that is used in the ORDER BY clause. That is the reason that the l.name and l.lastUpdate are found in both the SELECT clause as well as the ORDER BY clause. The only means to avoid this is by using an identifier variable in the SELECT statement. The pathway in which a JPQL query containing an ORDER BY and WHERE clause, will first filter the result by the WHERE clause and then filter the result by the ORDER BY clause.

Using Subqueries

A subquery is just as it says. It is a query within a query that is used as part of a WHERE or HAVING clause in order to filter the result set. You may not know that subqueries are not supported in FROM clauses in JPQL. This is different to SQL where subqueries from FROM clauses are supported. In JPQL, a subquery within a query will first be evaluated before the main query is retrieved based on the evaluation and result of the subquery. You can use EXISTS, ALL, ANY, IN or SOME in the subquery. The general syntax for a subquery is the following:

[NOT] IN / [NOT] EXISTS / ALL / ANY / SOME (Subquery)

Using IN with a Subquery

You can use the IN operator when evaluating an expression against a list of values. As many relationship between entity beans are collection-based, it is important to be able to access and select beans due to these relationships. The IN operator is one of the means provided by JPQL to represent individual elements in a collection-based relationship field. An example of a subquery using the IN clause is shown where the first subquery is executed to retrieve a todoList, and the the l.todoList path expression is evaluated against the list. This is shown below:

SELECT l FROM TodoList l WHERE l.listId IN (SELECT t.todoList FROM Todo t WHERE t.name LIKE :name)

Using EXISTS with a Subquery

An EXISTS (or NOT EXISTS) operator tests whether there is any result returned in the subquery. If it returns true then the subquery has at least one result. If it returns false otherwise . In an example of the EXISTS clause, we have reused the subquery example used with the IN operator. Best practice is to use the EXISTS operator rather than the IN operator especially in cases where you are dealing with a large number of records as databases have better performance using EXISTS. Part of this is due to how the query processor translates the JPQL query into a SQL query.

SELECT l FROM TodoList l WHERE EXISTS (SELECT t.todoList FROM Todo t WHERE t.name LIKE :name)

Using ANY, ALL, or SOME Operators

The ANY, ALL, and SOME operators can be used in a manner similar to using the IN operator. You use them in conjunction with numeric comparison operators such as =, >, >=, <, <= and <>. Similar to the IN operator, the ANY or SOME operators in an expression will return true if any of the results returned fulfill the query condition. For the ALL operator, all of the results returned must meet the condition. Examples for both ANY and ALL are provided below. Please note that as SOME is an alias of ANY, they can be used interchangeably.

Query with an ANY operator:

SELECT l FROM TodoList l WHERE l.creationDate >= ANY
(SELECT t.creationDate FROM Todo t WHERE t.assignedUser = l.user)

Query with an ALL operator:

SELECT l FROM TodoList l WHERE l.creationDate >= ALL
(SELECT t.creationDate FROM Todo t WHERE t.assignedUser = l.user)

Joining Entities

JOIN operations are very important in JPQL as well as SQL. JOIN operations allow you o navigate across two or more entities. Similarly to the IN operator, it is used primarily to access and select entities from collection-based relationships. But here we have a situation where we are unable to construct a composite path expression from a collection association field. But a JOIN operator allow you to navigate across collection based relationships. The phrase which is used to explain what a JOIN operation does is what is called a Cartesian product between the two entities. The Cartesian product is defined as a construction to build a new set out of a number of given sets. Each member of the Cartesian product (in our case entity instances in JPQL) corresponds to the selection of one element each in every one of those sets. Mathematically this is expressed in the following manner:

X x Y = { (x,y) | x ∈ X and y ∈ Y}

We use JOIN operators to create a Cartesian product between two entities. A WHERE clause is used to specify the JOIN condition between entities.
In JPQL , in order to create a JOIN between two or more entities, we use the FROM clause in conjunction with the entities. You can use either any arbitrary persistence fields or the existing relationships of the two entities for creating the join. Once the two entities are joined, you can now retrieve results that match with the JOIN conditions. So in the case of the TudoList application, if we wanted to create a join between TodoList and Todo using the one-to-many or many-to-many relationship (if the Todos are being shared) and then retrieve the entities that match the conditions of the join, we can use what is known as an inner join. If we wanted to include in the results retrieved entities from one side of the domain that don’t match the JOIN criteria from the other entity, we can do what is known as an outer join. An outer join can be left, right, or both. Next we will look at all the types of joins that are possible.

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.

Inner Joins

An inner join is the most frequently used type of join for joining two or more entities. You specify it explicitly by the use of a Cartesian product in the FROM clause in conjunction with WHERE clause where the join condition is specified. This type of relationship is used when there is no relationship specified between the entities in our model. Below is the syntax of an inner join:

[INNER] JOIN join_association_path_expression [AS] identification_variable

An example of an inner join is shown below. Here look at the many to one relation between Todo and TodoList which has a many-to-one relationship. If we want to retrieve all the TodoList that match a specific criterion we can do the following:

SELECT l FROM TodoList l INNER JOIN l.todo t WHERE l.listId LIKE ?1

Note that when the JOIN operator is used by itself, the default is to do an inner join.

Outer Joins

As we had mentioned before an outer join permits use to retrieve not only the entities that match the join conditions but also entities that don’t match the join conditions. This sort of join is quite useful when you are generating a report. There are different types of outer joins that can be performed. You can do either a left outer join or a right outer join. This is dependent upon whether the relationship of the entities in the join.

A left outer join will result for entity A and B as always containing all records of the "left" entity (A), even if the join-condition does not find any matching record for the "right" entity (B). The implication being that if the JOIN clause matches 0 (zero) records in B, the join will still return a row in the result. So a left outer join returns all the values from the left entity, plus matched values from the right entity. It will be NULL when there are no matching values for the left outer join.

A right outer join is similar to a left outer join, except for the treatment of the entities being reversed. Every row from the "right" entity (B) will appear in the result at least once. If no matching row from the "left" entity (A) exists, there will be the equivalent of a blank record in the result columns for entity A for all of those records that have no match in entity B. So to be clear a right outer join returns all the values from the right entity and matched values from the left entity. It will be NULL when there are no matching values for the right outer join.

An example of an outer join based upon our previous example for the inner join is shown below. In this case, we assume that there is only an optional relationship between Todo and TodoList. Now we want to generate a report showing all the TodoList names for the Todo. If a Todo is not associated with a TodoList then print NULL. The left outer join would look like the following:

SELECT l FROM TodoList l LEFT OUTER JOIN l.todo t WHERE l.listId LIKE ?1

Theta-Joins

A theta-join is also known as an equijoin, is a type of comparator-based join that uses only equality comparisons in the join operation. Note that using other comparison operators (i.e. <, >, etc) means that it is not a theta-join. They are based on arbitrary persistence or association fields in the entities being joined. So if we were to modify the Todo entity, to have a persistence field named priority that stores the priority for a Todo. The values for priority go from 1 to 5. Let’s assume that in the TodoList there is also a persistence field named importance for determining the importance of the Todos in the list. Assume that we want to join the two entities TodoList and Todo on the following fields, importance and priority respectively. To do this we would do the following:

SELECT l FROM TodoList l, Todo t WHERE l.importance = t.priority

Note that this type of join based on equality comparison tend to be less common in applications although you cannot rule it out.

Fetch Joins

A Fetch Join can be used to preload the relationship of an entity returned even if the relationships FetchType property is set to LAZY. This would allow you to query for a particular entity but also retrieve its associated entities at the same time. For example, when we retrieve a TodoList in the TudoList application, we need to eagerly load and initialize the associated instances of Todo. In this case, the relationship between Todo and TodoList is one to many as shown below:

/** * All {@link Todo}s for this {@link TodoList} */
@OneToMany (fetch=FetchType.LAZY) 
private Collection<Todo> todos;

Now if you want to print out information on the Todos the normal way of doing this would be to do a basic query of every TodoList and then iterate the getTodos() method from within the loop for every TodoList. It would look something like the code below:

Query query = manager.createQuery("SELECT e FROM TodoList l"); 
List results = query.getResultList( ); 
Iterator it = results.iterator( ); 
while (it.hasNext( )) {
   TodoList l = (TodoList)it.next( ); 
   System.out.print(l.getName( )); 
   for (Todo t : l.getTodos( )) { 
      System.out.print(t.getDescription( )); 
   }
}
System.out.println("");

This code will have problems because the relationship of the Todo is annotated so that the Todo is to be lazy loaded into the TodoList. In this case, the Todo collection will not be initialized when the initial query is done. So when the getTodos is invoked, the persistence engine will need to do an additional query in order to bring the associated Todos for the TodoList.

Rather than dealing with the problems in the above listing, you can use a fetch join to retrieve the associated Todos as a side effect of the retrieval of the TodoList. In the example below we use a LEFT JOIN FTECH:

SELECT l FROM TodoList l LEFT JOIN FETCH l.todos

Using the LEFT JOIN FETCH clause will allow you to additionally load the todos. The main benefit of using this is that the performance will be significantly enhanced because rather than having N + 1 queries for the todos, there will be only one query made to the database.

Using the DISTINCT Keyword

The DISTINCT keyword is used to prevent the query returning duplicate entities. If you don’t understand what this would mean, consider a query that does the following:

SELECT role FROM User u INNER JOIN u.roles role

This query will search for all the roles for the users but at the same time it will return duplications. Since a role can be used by many users so there will be duplicate references for the roles with all the user. By using the DISTINCT keyword the role would be represented only once in the result. The query would look like the following:

SELECT DISTINCT role FROM User u INNER JOIN u.roles role

Using Bulk Updates and Deletes

Similar to using a relational database, you can perform bulk UPDATE and DELETE operations. As this is an extremely powerful operation to use in JPQL. For example, let’s use the example we used for the theta-join where the TodoList has a field called importance that has values from 1 to 5 and the Todo entity has a field priority that has values from 1 to 5. Let’s assume that at periodic times, an application module is executed that archives completed Todos and TodoLists. You could run a query to retrieve the collection of TodoList entities and then iterate through the collection of Todos and archive each TodoList and Todos individually or an easier means is to use a bulk UPDATE statement to update the collection of entities matching the relevant condition. An example of this is shown below:

UPDATE Todo t WHERE t.completed =?1

This is a simple example of how to use the bulk update statement. Another example is if you wanted to remove instances of entities such as Users based on certain conditions. An example of this would be the following:

@PersistenceContext em; 
... 
// start transaction 
Query query = em.createQuery("DELETE USER u WHERE u.enabled = :enabled ");
query.setParameter("enabled", 'FALSE’);
int results = query.executeUpdate();
//end transaction

You can note that the use of UPDATE and DELETE statements is similar to other JPQL statements except that we use the executeUpdate method to perform bulk updates and deletes and we must invoke executeUpdate within an active transaction. You will note that we don’t have to use getSingleResult() or getResultList() method.

Since bulk updates and deletes are very powerful methods, you should isolate any bulk operations within a discrete transaction. This is because they are directly translated into database operations by the query processor. Because of this, it can cause inconsistencies between managed entities and the database. The JPA specification does not require persistence providers to modify any changes to managed entities but they only need to do the update or delete operations. 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. So it’s an open question as to how any associated entities will be removed when an entity is removed as a result of a bulk operation.

Native SQL Queries

Native SQL allows you to take advantage of the specific vendor’s database native capabilities. It grew out of the limitations of EJBQL where because of the limited functionality, vendors added their own specific extensions for native SQL in EJB2. The entity manager service facilitates the creation of native SQL queries as well as the mapping the queries to the objects. Native SQL allows you to manually optimize queries by the use of indexes and hints. The results of native SQL query can be one of more entity types, scalar values or a combination of entities and scalar values. Despite the facilitation of JPA for using of native SQL queries, using SQL proficiently requires experience and knowledgeable of databases. It is not something to be taken lightly. This is especially so since each database vendor provides proprietary extensions that require experience to use. Also the use of native SQL from a specific database vendor makes your applications less portable. If at all possible stick with JPQL unless you need features in native SQL that you cannot find in JPQL such as recursive joins.

So for example if you want to retrieve all Todos with of a particular priority by using recursive joins in an Oracle database the form of a START WITH ... CONNECT BY ... clause as follows:

SELECT TODO_ID, CREATION_DATE FROM TODO START WITH todo_id = ? CONNECT BY PRIOR todo_id = todo_id

Note that the native SQL query shown above cannot be expressed in JPQL since it is an Oracle database native SQL query. In contrast to the JPQL, the JPA provider executes the SQL statements but doesn’t track whether the SQL statement updated data related to any entities. When using native queries, avoid using SQL INSERT, UPDATE, and DELETE statements. So unlike with managed entities there is no synchronization between the data in the database and the data in the entity since your persistence provider will have no knowledge of such changes in the database and can lead to inconsistent/stale data especially if the persistence provider is using caching.

There are two types of native queries that can be used. There are dynamic queries and named queries similar to the types that exist with JPQL. As with the JPQL, the EntityManager interface is used for both named and dynamic queries.

Using Dynamic Queries with Native SQL

The dynamic queries are just as with JPQL, created on the fly within your code. You use the EntityManager interface to use the createNativeQuery method. An example of a dynamic native SQL query is shown below:

[CODE-JAVA; Example of Dynamic Native Query]Query q = em.createNativeQuery("SELECT todo_id, creation_date, description "
+ " FROM todo WHERE todo_id IN (SELECT user_id FROM "
+ "user GROUP BY user_id HAVING COUNT(*) > 1)",
com.acme.todo.domain.Todo.class);
return q.getResultList();[/CODE]

The listing above, shows the createNativeQuery method taking two arguments: the SQL query and the entity class being returned. If your query returns more than one entity, then this will create problems for you. This is the reason why you use the @SqlResultSetMapping annotation with the createNativeQuery method. If you had passed an entity class as an argument that would limit what could be mapped in the result set. Instead with the @SqlResultSetMapping annotation, you can map the query results to one or more entities. Consider if you wanted to create the @SqlResultSetMapping for the User entity in combination with a native query, then we can use the @SqlResultSetMapping annotation as follows:

@SqlResultSetMapping(name = "UserResults", entities = 
@EntityResult(entityClass = com.acme.todo.domain.Todo.class))

The query mapping can then be specified as follows:

Query q = em.createNativeQuery(SELECT todo_id, creation_date, description "
                 + " FROM todo WHERE todo_id IN (SELECT user_id FROM " 
                 + "user GROUP BY user_id HAVING COUNT(*) > 1)",
                    "UserResults"); 

return q.getResultList();

This shows you how to make an SQL query with the @SqlResultSetMapping that will automatically determine the entities being returned based on the annotation. It will instantiate the appropriate entities as well as initialize those entities with values based on the O/R mapping metadata.

Using a Named Native SQL Query

Named Native SQL query is similar to JPQL named queries. As with named JPQL query you must first create the named native query. The @NamedNativeQuery annotation has the following properties:

public @interface NamedNativeQuery { 
   String name() - the name of the native query; 
   String query() - the query to be executed; 
   QueryHint[] hints() default {} - the persistence provider specified hints; 
   Class resultClass() default void.class - the class of the result set that is being mapped; 
   String resultSetMapping() default "" - name of SQLResultSetMapping; 
}

You have the option of using a result set mapping or entity class with the @NamedNativeQuery annotation. So if we use an example from an earlier article, “Tutorial: Review of Java Persistence Query Language for the Component Developer Exam” where we discussed named JPQL queries. We will transform the named JPQL query to a named native query:

@NamedNativeQuery( name = "findUserWithMoreTodos", 
query = "SELECT user_id , first_name , last_name, creation_date FROM user
WHERE user_id IN ( SELECT assigned_user
FROM todo GROUP BY assigned_user	HAVING COUNT(*) > ?)", 
hints = {@QueryHint(name = "toplink.cache-usage",
value="DoNotCheckCache")}, 
resultClass = com.acme.todo.domain.Todo.class)

Next, if our query returns more than one entity class, we must define SqlResultSetMapping in the entity class using resultSetMapping as follows:

@NamedNativeQuery( name = "findUserWithMoreTodos", query = "SELECT user_id , first_name , last_name,
creation_date FROM users
WHERE user_id IN (SELECT assigned__user
FROM Todo GROUP BY assigned__user HAVING COUNT(*) > ?)",
resultSetMapping = "UserResults")

As named native SQL queries has the same properties that exists for JPQL named queries, you can provide vendor-specific hints in your NamedNativeQuery using the queryHint element. Finally remember that whether you want to use a JPQL named query or named native SQL query, the execution of the query is exactly the same.

Other Features of JPQL

Although JPQL has provided many of the features for exploiting relational databases as well as access other features that you don’t have access to through JPQL that are accessible directly through SQL , there are still some features that are not supported in JPQL. One of the features that are not supported by JPA is database-stored procedures. Instead you will need to use proprietary features of the database vendor in order to get access to stored procedures. Note that doing this will mean that you will need to depend on a proprietary feature of your persistence provider to use stored procedures. However, you can still use simple stored functions without "out" parameters in your native SQL queries.

OK. That’s it for Java Persistence Query Language. You should be ready now for this part of the Component Developer Exam. In the next article we will look at how Transactions are managed within the EJB3 Specification.