Chapter 10. Using the query API and JPQL to retrieve entities – EJB 3 in Action

Chapter 10. Using the query API and JPQL to retrieve entities

This chapter covers

  • Creating and executing queries
  • The Java Persistence Query Language
  • Using SQL queries

Chapters 8 and 9 discussed how the EJB 3 Java Persistence API (JPA) O/R mapping mechanism shields developers from having to write SQL to directly manipulate data. Chapter 8 explained how to map entities to relational tables, and chapter 9 revealed how to manipulate those entities using the EntityManager API. The next couple of topics we’ll tackle in our quest for JPA mastery are the query API and the Java Persistence Query Language (JPQL). We’ll continue to use ActionBazaar to explore these new concepts, incorporating JPQL into our auction system as well as looking at how the various one-to-many and many-to-one mappings work with JPA.

With JPA, you can use the following methods to retrieve entities and related data:

  • EntityManager.find with the entity’s primary key
  • Queries written in JPQL
  • SQL queries native to the underlying database

We discussed the first option, retrieving entities using a primary key, in chapter 9. In this chapter we’ll focus on retrieving entities using the latter two methods: JPQL and SQL queries. First we’ll look at the query API and show you how to execute queries; then we’ll explore the basics of JPQL. The chapter concludes by exploring the use of SQL queries with EJB 3.

10.1. Introducing the query API

Queries are as important as storing data. Therefore, a flexible query language is an important aspect of ORM. One of the primary problems in EJB 2 was that its query capabilities were limited. EJB 3 greatly improves the query capabilities of entities. In this section we introduce the query API and two types of queries: dynamic and named.

10.1.1. The big picture

In the previous chapter, we used the EntityManager.find method to retrieve entities. This method only retrieves entities by their ID or primary key. However, there are many instances when you need more powerful queries. The query API allows developers to write custom queries to retrieve either one or a collection of entities.

The EntityManager interface has several methods designed for creating queries to retrieve entities. The EntityManager methods are used together with the javax.persistence.Query interface methods, which perform the actual query definition, parameter binding, execution, and pagination.

The JPA query API allows you to use either JPQL or SQL to create the queries. We’ll cover JPQL in detail in section 10.3. SQL is the standard for querying relational data; we’ll discuss how to use SQL to create queries with JPA in section 10.4. If you are not familiar with SQL, refer to appendix B. Most of the time when you use the Query interface, you will likely choose to work with JPQL rather than SQL, since a SQL query returns database records and a JPA query returns entities.

The EntityManager interface methods for creating query instances, the Query interface methods that define and execute the query, and JPQL are referred to as the query API. This is shown in figure 10.1.

Figure 10.1. The JPA query API includes EntityManager methods to create queries, the methods in the Query interface for executing the query, and the Java Persistence Query Language.

As we dive deeper into the query API, you will start to realize that building queries to retrieve entities is often the most challenging task of building any enterprise applications, and almost every aspect of an application requires some data retrieval. The query API supports building and using queries in several ways, as you’ll learn in the next few sections. Depending on what you are trying to accomplish, you may devise some interesting ways to utilize queries.

The steps you follow to create a JPA query are similar to those of a traditional JBDC query. Table 10.1 compares using a JDBC query to the basic flow of a JPA query. As you can see, the primary differences lie in the way you obtain the database connection and the way in which you manipulate the query itself. In JDBC it is normal to employ a database connection directly. Conversely, JPA hides the database connection behind the EntityManager.

Table 10.1. Comparing a JDBC basic query to a JPA query. The JDBC SQL query returns the data in the database table whereas the JPQL query returns JPA entities.

Basic Steps for JDBC Query Using SQL

Basic Steps for a JPA Query Using JPQL

1. Obtain a database connection.

1. Obtain an instance of an entity manager.

2. Create a query statement.

2. Create a query instance.

3. Execute the statement.

3. Execute the query.

4. Retrieve the results (database records).

4. Retrieve the results (entities).

By now, you must be eager to see what a query looks like. Let’s do that next.

10.1.2. Anatomy of a query

The query API supports two types of queries: named and dynamic. Named and dynamic queries have different purposes. Named queries are intended to be stored and reused. For instance, suppose your application requires you to list the most popular item in a specific category. Since this requirement occurs in several places in your application, you know this query will be executed by multiple modules. You’d probably want to create a named query so you wouldn’t have to build the query every time you wanted to query the most popular item in that category. On the other hand, say you have a requirement to build a query screen for items and you want to dynamically create the query statement based on user inputs or some conditions in your application logic. In this case, you’d have to use a dynamic query. Dynamic (or ad hoc) queries are different from named queries in that they are created on the fly.

In either case, the format of the queries is similar. For a first taste of how a query works, let’s look at an example, starting with a dynamic query. Suppose we want to retrieve all the categories in the system. The following code will perform the desired task:

In this example, we first grab an instance of an EntityManager provided by dependency injection . Then we create an instance of a Query object for querying entities using the EntityManager.createQuery method , passing it the query string. (In section 10.2 we discuss the Query interface in more detail.) Once we have a query instance, the final step is to return the results list using its getResultList method.

Note that in this example we used JPQL to query the entities. You can use either JPQL or native SQL in both named and dynamic queries.

A named query will be almost the same: the only difference is that it uses createNamedQuery rather than createQuery, and you must pass it a query object that has already been created rather than a query string. In the next section, you’ll learn how to create that object for JPQL queries. We’ll discuss creating the named query object for SQL queries in section 10.4.

10.1.3. Defining named queries

You must create a named (or static) query before you can use it. It is defined either in the entity using annotations, or in the XML file defining O/R mapping metadata. You’ll learn about O/R mapping and how to define a named query with XML in chapter 11. A named query is accessed by its name when you create an instance of it. Any query that is used in multiple components of your applications is a candidate for a named query.

Named queries have three primary benefits:

  • They improve reusability of queries.
  • They improve maintainability of code; queries are not scattered among the business logic.
  • They can enhance performance because they are prepared once and can be efficiently reused.

Although a named query can also be stored in the ORM XML file, in this chapter we focus on using it with metadata annotations. Let’s look at an example. Say you want to create a named query on the Category entity to retrieve all categories by passing a category name. To achieve this, use the @javax.persistence.NamedQuery annotation:

@Entity
@NamedQuery(
name = "findAllCategories",
query = "SELECT c FROM Category c WHERE c.categoryName
LIKE :categoryName ")

public class Category implements Serializable {
..
}

For a complex application, you’ll probably have multiple named queries. In that case, you can use the @javax.persistence.NamedQueries annotation to specify multiple named queries like this:

@Entity
@NamedQueries({
@NamedQuery(
name = "findCategoryByName",
query = "SELECT c FROM Category c WHERE c.categoryName
LIKE :categoryName order by c.categoryId"
),
@NamedQuery(
name = "findCategoryByUser",
query = "SELECT c FROM Category c JOIN c.user u
WHERE u.userId = ?1"
)})
@Table(name = "CATEGORIES")

public class Category implements Serializable {
}

Note

Keep in mind that a named query is scoped with a persistence unit and therefore must have a unique name. We recommend that you devise a naming convention for your applications that will avoid duplicate names for named queries.


So now you know the difference between named and dynamic queries. Next we’ll show you how to execute your queries.

10.2. Executing the queries

If you’ve used Hibernate or TopLink, you’ll note many similarities between the query APIs in those frameworks and the EJB 3 Java Persistence API. As you saw in section 10.1.2, there are three steps to running a query in EJB 3:

  • Create an instance of the EntityManager.
  • Create an instance of the query.
  • Execute the query.

In chapter 9, you saw how to create an instance of EntityManager. (To recap, you can either inject an instance of a container-managed EntityManager or create an application-managed EntityManager from an EntityManagerFactory.) In this section we focus on the last two steps. First we’ll look at creating a query instance and then explore the methods in the Query interface designed for executing queries.

10.2.1. Creating a query instance

As our first example showed, before you can execute a named or dynamic query using JPQL, you must create the query instance to retrieve persistent data. The EntityManager interface provides methods for creating a query instance, as well as methods for creating native SQL queries. Table 10.2 lists the methods.

Table 10.2. The EntityManager interface provides several methods to create queries using either JPQL or native SQL statements.

Method

Purpose

public Query createQuery(String qlString);

Creates a dynamic query using a JPQL statement.

public Query createNamedQuery(String name);

Creates a query instance based on a named query. This method can be used for both JPQL and native SQL queries.

public Query createNativeQuery(String sqlString);

Creates a dynamic query using a native SQL statement with UPDATE or DELETE.

public Query createNativeQuery(String sqlString,Class result-class);

Creates a dynamic query using a native SQL statement that retrieves a single entity type.

public Query createNativeQuery(String sqlString,String result-setMapping);

Creates a dynamic query using a native SQL statement that retrieves a result set with multiple entity types.

In table 10.2, some of the methods for creating query instances use JPQL and others use with native SQL queries. Section 10.3 explores JPQL, and section 10.4 shows you how to work with native SQL queries. In this section, the sample queries are JPQL based. We suggest you use native SQL only as a last resort.


Note

You do not need an active transaction to create or execute a query; if one does not exist, the retrieved entities become detached instances.


Creating a named query instance

As we discussed earlier, named queries are globally scoped. You can create a named query instance from any component that has access to the persistence unit to which the entity belongs. You must have an open EntityManager instance to create a named query instance. To use a named query stored on the entity, you invoke the EntityManager.createNamedQuery method, passing the name of the desired named query as the parameter. In the previous example when we created the stored query, we stored the findAllCategories named query in the Category entity. Creating a named query from that stored query is as easy as this:

Query query = em.createNamedQuery("findAllCategories");

The EntityManager instance em takes care of all the details of fetching our named query findAllCategories and returning a reference, which we assign to the query object.

Creating a dynamic query instance

A dynamic query can be created wherever the EntityManager is available. This includes using it in session beans, MDBs, web applications, or even outside the container (as long as you can you can access the EntityManager). EJB 2 did not support dynamic queries, and many developers found that to be a significant limitation.

We can use the EntityManager.createQuery method to create a dynamic query. The only requirement is to pass a valid JPQL statement. It makes no difference whether the EntityManager is a container- or application-managed entity manager. The following shows how to create a dynamic query:

Query query = em.createQuery("SELECT i FROM Item i");

You can see that the JPQL syntax resembles SQL, but JPA recommends that you use JPQL. The differences in notation between SQL and JPQL will be more evident later in section 10.3, when we explore JPQL by itself.

Let’s recap where we are now. We’ve created an instance of the EntityManager, and we’ve created an instance of the query. The next step is the actual execution of the query. The Query interface provides the methods we need.

10.2.2. Working with the Query interface

The Query interface defines several methods for executing a query. It provides methods to set parameters for a Query instance, specify pagination properties for the result, control the flush mode, and so forth. The Query interface does not differentiate between JPQL and native SQL, and the same interface can be used for both types of queries. Table 10.3 lists all methods of the Query interface.

Table 10.3. The javax.persistence.Query interface enables developers to set parameters for a query, set pagination properties, control the flush mode, and retrieve results for the query.

Method Signature

Purpose

public List getResultList()

Retrieves a result set for a query

public Object getSingleResult()

Retrieves a single result or object

public int executeUpdate()

Executes a JPQL UPDATE or DELETE statement

public Query setMaxResults(int maxResult)

Sets the maximum number of objects to be retrieved

public Query setFirstResult(int startPosition)

Sets the initial position for the first result being retrieved by the query

public Query setHint(String hintName, Object value)

Sets a vendor-specific hint for the query

public Query setParameter(String name, Object value)

Sets the value for a named parameter

public Query setParameter(String name, Date value, TemporalType temporalType)

Sets the value for a named parameter when the parameter is of the Date type

public Query setParameter(String name, Calendar value, TemporalType temporalType)

Sets the value for a named parameter when the parameter is of the Calendar type

public Query setParameter(int position, Object value)

Sets the value for a positional parameter

public Query setParameter(int position, Calendar value, TemporalType temporalType)

Set the value for a positional parameter when the parameter is of the Calendar type

public Query setFlushMode(FlushModeType flushMode)

Sets the flush mode

You can use these methods on the query instance for setting parameters for the query or for executing the query or iterating through the results after you retrieve them. Here is a quick example of some commonly used Query methods in action:

In this example, we create a query instance from a named query that was defined earlier. Here we want to retrieve a List of Category entities by name and hence we set the parameter . We limit the maximum number of items returned to 10, and we position the first entity to be returned at the third item. Finally we retrieve the result .

In this section we examine all aspects of executing a query: setting parameters, retrieving either a single entity or a list of entities, and setting vendor-specific query hints to improve query performance.

Setting parameters for a query

The number of entities retrieved in a query can be limited by specifying a WHERE clause. If we want to retrieve all instances of the Item entity with a specific price, the JPQL would look like this:

SELECT i FROM Item i WHERE i.initialPrice = ?1

In this statement, we’ve used a parameter (?1) for the WHERE clause. There are two ways we can specify this parameter: by number or by name. When we have an integer in the parameter, we call it a positional (or numbered) parameter. Positional parameters are common in query languages. EJBQL 2 also supported positional parameters.

Before we execute a query, we have to set the parameter for the query:

query.setParameter(1, 100.00);

In some cases you’ll want to specify multiple parameters for a query. Say you want to retrieve all items with an initialPrice that falls within a particular range:

SELECT i FROM Item i WHERE i.initialPrice > ?1 AND i.initialPrice < ?2

The following code should do the trick:

query.setParameter(1, 100.00);
query.setParameter(2, 200.00);

Here we set the first parameter in position 1 to 100.00. This is the lower limit of the range in our query. The upper limit is loaded by setting the parameter in position 2 to 200.00.


Note

Always double-check the positions of parameters when using numbered parameters. If you have trouble with your queries using positional parameters, probably one or more of the positions are incorrect, which nearly always forces a data type mismatch. For this reason, we recommend that you use named parameters instead of positional parameters when possible.


When you specify a specific name for a parameter, it’s called a named parameter. Named parameters improve the readability of code tremendously and make troubleshooting your queries much easier. The previous query can be written using named parameters as follows:

SELECT i FROM Item i WHERE i.initialPrice = :price

As you can see, the only difference between the positional parameter and the named parameter is the notation of the parameter itself. A positional parameter starts with a ? followed by the parameter’s position. A named parameter starts with : and is followed by the name of the parameter. To populate a named parameter, you have to pass the name of the parameter when calling the setParameter method on the query like this:

query.setParameter("price", 100.00);

This code sets the named price parameter to a value of 100.00. It also makes the developer’s intent a little clearer to those reading the code.

Retrieving a single entity

You can retrieve a single entity instance by using the Query.getSingleResult method. Be sure that the query retrieves only one entity when using this method. For example, if we are absolutely sure that no two categories in the ActionBazaar application have exactly the same name and we are retrieving an instance of the Category by its name, we can retrieve it by using

query.setParameter(1, "Recycle from Mr. Dumpster");
Category cat = (Category)query.getSingleResult();

Note

If your query retrieves multiple instances of Category entities with the same name, it will throw NonUniqueResultException. The persistence provider will throw NoResultException when your query does not retrieve any result.


These exceptions will not roll back the active transactions. You must handle these exceptions as follows:

try {
...
query.setParameter(1, "Recycle from Mr. Dumpster");
Category cat = (Category)query.getSingleResult();
...
}catch (NonUniqueResultException ex) {
handleException(ex);
}
catch (NoResultException ex) {
handleException(ex);
}

Retrieving an entity using getSingleResult does not require an active transaction. However, if no transactions are available, the retrieved entity will be detached after retrieval. It’s worth mentioning that the persistence provider will throw IllegalStateException if Query contains an UPDATE or DELETE statement.

Retrieving a collection of entities

Most queries normally retrieve more than one instance of an entity in what is commonly called a result set or result list. You can use the getResultList method of the Query interface to retrieve the results of a query. For example, to retrieve all instances of Item with an initial price between 100 and 200 using named parameters, use the following:

query.setParameter("lowPrice", lowPriceValue)
query.setParameter("highPrice", highPriceValue)
List items = query.getResultList();

Note

If getResultList does not retrieve any results for a query, it returns an empty list. No exceptions are thrown.


Here you see that lowPrice is the name parameter for the lower range parameter, and the value is provided by lowPriceValue. The upper range parameter is named highPrice in the query, and its value is specified by highPriceValue. The query.getResultsList method returns the list of items that fall within this range.

As with retrieving a single entity, retrieving a collection does not require an active transaction, and if one isn’t available, the retrieved entities will be detached after retrieval.

Paginating through a result list

A query may retrieve hundreds, or even millions of entities, and processing a retrieved list of entities is not necessarily straightforward. Here’s a common way to iterate through a list of items:

Iterator l = items.iterator();
while (l.hasNext()) {
Item item = (Item)l.next();
System.out.println("Id:" + item.getItemId() +
" Initial Price:"+item.getInitialPrice());
}

This code does not provide any logic to paginate through a specific set of results. But suppose you need to create a report in which every page is limited to a display of 50 entities, and when the user clicks the Next button, the succeeding 50 entities are displayed. If you used the previous method, you’d have two issues. First, the code would be more complicated than necessary to handle the 50-item page requirement. Second, you’d retrieve all entities from the database at one time, which might consume a lot of memory depending on how many items were returned.

The good news is that JPA provides the ability to paginate through the result set. You can use the following code to specify the pagination property for a query:

query.setMaxResults(50);
query.setFirstResult(0);
List items = query.getResultList();

The setMaxResults method lets you specify the maximum number of entities being retrieved in the result list, and setFirstResult lets you set the position of the first result in the ResultList. The previous code returns the first 50 entities retrieved by the query. If you want to retrieve the next 50 entities, use the following:

query.setMaxResults(50);
query.setFirstResult(50);
List items = query.getResultList();

The only difference between the previous two code snippets is the starting offset of the result list.

Instead of hard-coding the offset and page size, you can create a method that takes these settings as a parameter:

public List getPagedItems(int pageIndex, int pageSize) {
...
query.setMaxResults(pageSize) ;
query.setFirstResult(pageIndex) ;
return query.getResultList();
}

You can use this method from your application code to paginate through the entities.

This is starting to look familiar, isn’t it? Now let’s turn our attention to another topic: controlling the flush mode of the query.

Controlling the query flush mode

You may remember from our discussion in chapter 9 that the flush mode determines how the EntityManager performs the database writes. The results of a query can vary depending on the flush mode setting. Chapter 9 discussed setting FlushMode for the persistence context. FlushMode can be changed for a specific query by using the Query.setFlushMode method. The default flush mode is AUTO, as shown in table 10.4. In AUTO mode, when queries are executed within a transaction the persistence provider is responsible for making sure that all entities are updated in the persistence context. This is true whether FlushModeType.AUTO is set on the Query object or the flush mode is AUTO for the persistence context.

Table 10.4. Defined flush modes for persistence providers and how the flush mode affects the results of a query

Flush Mode

Description

AUTO (default)

The persistence provider is responsible for updates to entities in the persistence context.

COMMIT

Updates made to entities in the persistence context are undefined.

If the Query is set to FlushModeType.COMMIT, the effect of updates made to entities in the persistence context is not defined by the specification, and the actual behavior is implementation specific. This means your mileage may vary depending on which persistence provider you are using.

The default behavior of the flush mode is acceptable for most cases, and you should not change it unless you absolutely need to tweak this setting.

10.2.3. Specifying query hints

Persistence providers typically include a vendor-specific extension that can be used while executing a query. Such extensions are usually performance optimizations and are passed as query hints. A query hint is a tip that is used by the persistence provider while executing queries or retrieving entities. For example, a hint can be a directive to the persistence provider whether to use a cache while executing a query. You can provide a hint for a query by using the Query.setHint method. Unfortunately, the hints are implementation specific. If you want to set the timeout for a query to 10 seconds when using Oracle TopLink, you do so with

query.setHint("toplink.jdbc.timeout", new Integer(10000));

You can do the same with Hibernate by using

query.setHint("org.hibernate.timeout", new Integer(10));

Notice the difference in how the timeout of 10 seconds is specified for each provider? Be sure to verify the format for the provider you are using by checking the documentation provided by the vendor. Look for hints that may be supported by the container. Table 10.5 lists some commonly used hints for two of the leading JPA providers.

Table 10.5. You can use the setHint method of the Query interface to specify query hints to the persistence provider. Common query hints are supported by two popular ORM frameworks.

TopLink

Hibernate

Purpose

toplink.jdbc.fetch-size

org.hibernate.fetchSize

Specifies the number of rows fetched by the JDBC driver

toplink.cache-usage

org.hibernate.cacheMode

Specifies how to use the cache

toplink.refresh

CacheMode.REFRESH

Specifies whether the cache should be refreshed from the database

toplink.jdbc.timeout

org.hibernate.timeout

Specifies the query timeout

If you are using named queries, then you can optionally specify the vendor-specific hints using the hints element of @NamedQuery. This element is specified as a name-value pair using the @QueryHint annotation:

@NamedQuery(
name = "findUserWithNoItems",
query = "SELECT DISTINCT u FROM User u WHERE u.items is EMPTY",
hints = { @QueryHint(name = "org.hibernate.timeout", value = "10") }
)

Whew! How do you feel now that you know all the basics of the JPA query API? Well, there’s more where that came from! We’ll bet you’re ready for an adventurous trek into the winding trails of the Java Persistence Query Language. Lace up your high-tops—those trails are just ahead!

10.3. Introducing JPQL

The meat of this chapter covers the ins and outs of the Java Persistence Query Language. We’ll start with a definition of the language, provide numerous examples illustrating almost every aspect, and include some little-known tips along the way. Can you handle a little heavy lifting? Proceed with caution if you think you can; and make sure you have your work gloves on...

Hibernate provides HSQL, while JDO-compliant providers such as BEA’s Kodo support JDO QL to query entities. There was not much debate among the EJB 3 Expert Group on which to use as the standard query language for JPA, and it was agreed to use JPQL. JPQL is an extension of EJB QL, the query language of EJB 2. It didn’t make sense to invent yet another language for such a well-known domain, so the group voted unanimously to make EJBQL the query language of choice and to address all its previous limitations. It’s good news that yet another query language was not forced upon the developer community. The use of JPQL will make the migration of EJB 2 entity beans to EJB 3 persistence easier.


How is JPQL Different from SQL?

JPQL operates on classes and objects (entities) in the Java space. SQL operates on tables, columns, and rows in the database space. While JPQL and SQL look similar to us humans, they operate in two very different worlds.


The JPQL Query Parser or Processor Engine of a persistence provider, as shown in figure 10.2, translates the JPQL query into native SQL for the database being used by the persistence provider.

Figure 10.2. Each JPQL query is translated to a SQL query by the JPQL query processor and executed by the database. The query processor is supplied by the JPA provider, most likely the application server vendor.

JPQL looks so much like SQL that it’s easy to forget you are looking at JPQL when you’re reviewing source code. Just remember that although JPQL may look like SQL, you’ll need to be aware of the differences discussed in this chapter to effectively use and troubleshoot JPQL in your programs.

All this talk about JPQL queries has piqued your interest, hasn’t it? What do you say we continue this line of thinking by going over the types of statements JPQL supports? Then we’ll discuss different elements of a JPQL statement, such as FROM and SELECT clauses, conditional statements, subqueries, and various types of functions. Finally, we’ll take a look at updates and delete statements.

10.3.1. Defining statement types

JPQL supports three types of statements, as shown in table 10.6. You can use JPQL to perform selects, updates, and deletes in your queries.

Table 10.6. Statement types supported by the Java Persistence Query Language

Statement Type

Description

SELECT

Retrieves entities or entity-related data

UPDATE

Updates one or more entities

DELETE

Deletes one or more entities

Let’s first focus on retrieving entities using a SELECT statement with JPQL.

Defining and using SELECT

Suppose we get jump-started with a simple JPQL query:

SELECT c
FROM Category c
WHERE c.categoryName LIKE :categoryName
ORDER BY c.categoryId

This JPQL query has (or can have) the following:

  • A SELECT clause that specifies the object type or entity or values being retrieved
  • A FROM clause that specifies an entity declaration that is used by other clauses
  • An optional WHERE clause to filter the results returned by the query
  • An optional ORDER BY clause to order the results retrieved by the query
  • An optional GROUP BY clause to perform aggregation
  • An optional HAVING clause to perform filtering in conjunction with aggregation
Defining UPDATE and DELETE

In chapter 9, we discussed updating and removing entities using the EntityManager API. But these were limited to only one entity instance. What about when you want to remove more than one entity in a single call? Like SQL, JPQL also provides UPDATE and DELETE statements to perform updates and deletions of entities, and we can continue to specify a condition using a WHERE clause. These statements are quite similar to their SQL relatives. They are referred to as bulk updates or deletes because you’ll primarily use these to update or delete a set of entities matching a specific condition. In this section we’ll limit our discussion to the JPQL syntax for update and delete, and we’ll discuss the implications of using bulk updates and deletes in section 10.3.10.

Using UPDATE

Only one entity type can be specified with an UPDATE statement, and we should provide a WHERE clause to limit the number of entities affected by the statement. Here is the syntax for the UPDATE statement:

UPDATE entityName indentifierVariable
SET single_value_path_expression1 = value1, ...
single_value_path_expressionN = valueN
WHERE where_clause

You can use any persistence field and single value association field in the SET clause of the UPDATE statement. Assume that we want to provide Gold status and a commissionRate of 10 percent to all Sellers whose lastName starts with Packrat. Start with the following JPQL statement:

UPDATE Seller s
SET s.status = 'G', s.commissionRate = 10
WHERE s.lastName like 'PackRat%'

It is clear from this statement that the WHERE clause of an UPDATE behaves exactly the same as the one we used in the SELECT statement. We will return to a detailed discussion on the WHERE clause later in this chapter.

Using DELETE

Like UPDATE, DELETE in JPQL resembles its SQL cousin. You can specify only one entity type with a DELETE statement, and again you should specify a WHERE clause to limit the number of entities affected by the statement. Here is the syntax for the DELETE statement:

DELETE entityName indentifierVariable
WHERE where_clause

For example, if we want to remove all instances of Seller with Silver status we’d use this:

DELETE Seller s
WHERE s.status = 'Silver'

10.3.2. Using the FROM clause

The FROM clause of JPQL is by far the most important clause. It defines the domain for the query—that is, the names for the entities that will be used in the query. In the previous example we specified the FROM clause as follows:

FROM Category c

Category is the domain that we want to query, and here we have specified c as an identifier of type Category.

Identifying the query domain: naming an entity

You specify the entity name defined for the entity using the @Entity annotation as the domain type. As you learned in chapter 7, you could define the name for an entity using the name element of the @Entity annotation. If you don’t specify the name element, it defaults to the name of the entity class. The name of entity must be unique within a persistence unit. In other words, you cannot have two entities with the same name or the persistence provider will generate a deployment error. This makes sense because the persistence provider would not be able to identify which entity domain to use if duplicate names for entities are allowed.


EJB 2’s EJBQL compared to EJB 3’s JPQL

If you have used EJBQL with EJB 2, you’ll see a significant difference with the new version. Some of the major enhancements in EJB 3 with respect to JPQL are as follows:

  • Use of named parameters
  • Simplification of syntax
  • Support for JOIN operations
  • Support for subqueries
  • Bulk updates and deletes
  • Support for GROUP BY and HAVING

The good news is that these JPQL enhancements may also be available to EJB 2.1 entity beans, because most application servers probably share the same JPQL parser for both EJB 2.1 entity beans and EJB 3 entities.

One of the greatest shortcomings of EJB 2 CMP entity beans is the inability to use dynamic queries and native SQL. Both of these limitations have been addressed by JPQL.


In the previous example, we are assuming the Category entity class that we discussed in earlier chapters does not define a name. If we assume that the Category class defines an entity name using the name element as follows:

@Entity(name = "CategoryEntity")
public class Category

then we must change the FROM clause of the query as follows:

FROM CategoryEntity c

This change is required in order for JPQL to map the correct entity type as defined by the annotation.

Identifier variables

In our JPQL example, we defined an identifier variable named c, and we used that variable in other clauses, such as SELECT and WHERE. A simple identifier variable is defined using the following general syntax:

FROM entityName [AS] identificationVariable

The square brackets ([]) indicate that the AS operator is optional. The identifier variable (which is not case sensitive) must be a valid Java identifier, and it must not be a JPQL reserved identifier. Table 10.7 lists all of the JPQL reserved identifiers for your convenience. Keep in mind that the identifier cannot be another entity name packaged in the same persistence unit.

Table 10.7. JPQL keywords reserved by the specification. You are not allowed to give any of your variables these names.

Types

Reserved Words

Statements and clauses

SELECT, UPDATE, DELETE, FROM, WHERE, GROUP,HAVING, ORDER, BY, ASC, DESC

Joins

JOIN, OUTER, INNER, LEFT, FETCH

Conditions and operators

DISTINCT, OBJECT, NULL, TRUE, FALSE, NOT, AND, OR, BETWEEN, LIKE, IN, AS, UNKNOWN, EMPTY, MEMBER, OF, IS, NEW, EXISTS, ALL, ANY, SOME

Functions

AVG, MAX, MIN, SUM, COUNT, MOD, UPPER, LOWER, TRIM,POSITION, CHARACTER_LENGTH, CHAR_LENGTH, BIT_LENGTH, CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP

Thus, we cannot define the FROM clause like this:

FROM Category User

or like this:

FROM Category Max

because we already have an entity named User in the ActionBazaar application, and MAX is a reserved identifier.

You can define multiple identifiers in the FROM clause, and you’ll see how to use them when we discuss joining multiple entities by association or field name later in this chapter.

What is a path expression?

In our JPQL example we used expressions such as c.categoryName and c.categoryId. Such expressions are known as path expressions. A path expression is an identifier variable followed by the navigation operator (.), and a persistence or association field. We normally use a path expression to narrow the domain for a query by using it in a WHERE clause, or order the retrieved result by using an ORDER BY clause.

An association field can contain either a single-value object or a collection. The association fields that represent one-to-many and many-to-many associations are collections of types, and such a path expression is a collection-value path expression. For example, if we have a many-to-many relationship between Category and Item, we can utilize a query to find all Category entities that have associated Items as follows:

SELECT distinct c
FROM Category c
WHERE c.items is NOT EMPTY

Here c.items is a collection type. Such expressions are known as collection-value expressions. If the association is either many-to-one or one-to-one, then the association fields are of a specific object type, and those types are known as single-value path expressions.

You can navigate further to other persistence fields or association fields using a single-value path expression. For example, say we have a many-to-one relationship between Category and User; we can navigate to a persistence field such as firstName using association field user as follows:

c.user.firstName

We may also want to navigate to the association field contactDetails to use its e-mail address:

c.user.contactDetails.email

While using path expressions, keep in mind that you cannot navigate through the collection-value path expressions to access a persistence or association field as in the following example:

c.items.itemName or c.items.seller

This is due to the fact you cannot access an element of a Collection, and items is in fact is a collection of items. Using c.items.itemName in JPQL is similar to using category.getItems().getItemName(), and this is not allowed.

Next we’ll see how you can use path expressions in a WHERE clause.

Filtering with WHERE

The WHERE clause allows you to filter the results of a query. Only entities that match the query condition specified will be retrieved. Say we want to retrieve all instances of the Category entity; we can use a JPQL statement without a WHERE clause:

SELECT c
FROM Category c

Using this code will probably result in thousands of Category instances. But say we actually want to retrieve instances of a Category by a specific condition. To retrieve the Category instances that have a categoryId greater than 500, we’d have to rewrite the query like this:

SELECT c
FROM Category c
WHERE c.categoryId > 500

Almost all types of Java literals such as boolean, float, enum, String, int, and so forth are supported in the WHERE clause. You cannot use numeric types such as octal and hexadecimals, nor can you use array types such as byte[] or char[] in the WHERE clause. Remember that JPQL statements are translated into SQL; SQL is actually imposing the restriction that BLOB and CLOB types cannot be used in a WHERE clause.

Passing parameters: positional and named

Recall from our earlier discussion that JPQL supports two types of parameters: positional and named. Later in this chapter we’ll show you how to set values for both named and positional parameters.

The value of the parameter is not limited to numeric or String types; the value depends on the type of path expression used in the WHERE clause. The parameter can take more complex types, such as another entity type; however, you are limited to using conditional expressions that involve a single-value path expression.

10.3.3. Conditional expressions and operators

A condition in the WHERE clause that filters results from a query is known as a conditional expression. You can build a conditional expression using path expressions and operators supported by the language. JPQL can evaluate a path expression with numeric, string, or boolean values using relational operators. Here’s an example of a conditional expression:

c.categoryName = 'Dumped Cars'

Table 10.8 lists the types of operators supported by JPQL, in order of precedence.

Table 10.8. Operators supported by JPQL

Operator Type

Operator

Navigational

.

Unary sign

+, -

Arithmetic

*, /+, -

Relational

=, >, >=, <, <=, <>, [NOT] BETWEEN, [NOT] LIKE, [NOT] IN, IS [NOT] NULL, IS [NOT] EMPTY, [NOT] MEMBER [OF]

Logical

NOT

AND

OR

A complex conditional expression may include other expressions that are combined for evaluation using logical operators such as AND or OR. For instance, we can retrieve a Category that meets either of these conditional expressions:

WHERE c.categoryName = 'Dumped Cars'
OR c.categoryName = 'Furniture from Garbage'

We can use all types of relational operators with numeric types of path expressions. String and Boolean operands can use the relational operators: equality (=) and nonequality (<>).

Using a range with BETWEEN

You can use the BETWEEN operator in an arithmetic expression to compare a variable with a range of values. You can also use the BETWEEN operator in arithmetic, string, or DATETIME expressions to compare a path expression to a lower and upper limit using the following syntax:

path_expression [NOT] BETWEEN lowerRange and upperRange

Suppose you want to filter the results so that categoryId falls within a specified range. You can use a WHERE clause and named parameters for the range this way:

WHERE c.categoryId BETWEEN :lowRange AND :highRange

Note

The lower and upper range used in a BETWEEN operator must be the same data type.


Using the IN operator

The IN operator allows you to create a conditional expression based on whether a path expression exists in a list of values. Here is the syntax for the IN operator:

path_expression [NOT] IN (List_of_values)

The list of values can be a static list of comma-separated values, or a dynamic list retrieved by a subquery. Suppose you want to retrieve the results for userId that exist in a static list of userIds. This WHERE clause will do the trick:

WHERE u.userId IN ('viper', 'drdba', 'dumpster')

If you want to retrieve the information from users that do not exist in the same static list, then you can use this WHERE clause:

WHERE u.userId NOT IN ('viper', 'drdba', 'dumpster')

A subquery is a query within a query. A subquery may return a single or multiple values. You’ll learn more about subqueries in section 10.3.8. Let’s review an example of a subquery with an IN operator:

WHERE c.user IN (SELECT u
FROM User u
WHERE u.userType = 'A')

In this expression you are trying to evaluate the User field with a list of users retrieved by the subquery. When a query contains a subquery, the subquery is executed first, and then the parent query is evaluated against the result retrieved by the subquery.

Using the LIKE operator

The LIKE operator allows you to determine whether a single-value path expression matches a string pattern. The syntax for the LIKE operator is

string_value_path_expression [NOT] LIKE pattern_value_

Here pattern_value is a string literal or an input parameter. The pattern_value may contain an underscore (_) or a percent sign (%). The underscore stands for a single character. Consider the following clause:

WHERE c.itemName LIKE '_ike'

This expression will return true when c.itemName has values such as mike, bike, and so forth. You should be able to extend this technique to embed a space into any search string, effectively making the space a wildcard. If you search for a single space, it will only match a single character.

The percent sign (%) represents any numbers of characters. Whenever you want to search for all Category entities with a name that starts with Recycle, use this WHERE clause:

WHERE c.categoryName LIKE 'Recycle%'

The expression will return true when c.categoryName has values such as Recycle from Garbage, Recycle from Mr. Dumpster, and RecycleMania – the Hulkster strikes again!.

Suppose you want to retrieve a result set in which a string expression does not match a literal. You can use the NOT operator in conjunction with the LIKE operator as in the following example:

WHERE c.categoryName NOT LIKE '%Recycle%'

The expression will return false when c.categoryName has any values that includes Recycle as any part of the return value, because in this example you used % before and after the filter string. Some examples that match this situation are Dr. T will Recycle your face and Recycle from the Garbage, it's the American Way.

In most applications you probably want to supply a parameter for flexibility rather than use a string literal. You can use positional parameters as shown here to accomplish this:

WHERE c.categoryName NOT LIKE ?1

Here, the result set will contain all c.categoryNames that are not like values bound to the positional parameter ?1.

Dealing with null values and empty collections

So far we have been able to avoid discussing null and how an expression deals with null values. Alas, now it is time to deal with this little mystery. You have to remember that null is different from an empty string, and JPQL treats them differently. However, not all databases treat an empty string and null differently. We already know that JPQL is translated into SQL by the persistence provider. If the database returns true when an empty string is compared with null, you cannot rely on consistent results from your queries across two different databases. We recommend that you test this situation with your database.

When a conditional expression encounters a null value, the expression evaluates to null or unknown. A complex WHERE clause that combines more than one conditional expression with a boolean operator such as AND may produce a result that is unknown. Table 10.9 lists the results of a conditional expression when it is compared with a null value.

Table 10.9. Results of boolean operations involving null

Expression 1 Value

Boolean Operator

Expression 2 Value

Result

TRUE

AND

null

UNKNOWN

FALSE

AND

null

FALSE

Null

AND

null

UNKNOWN

TRUE

OR

null

TRUE

Null

OR

null

UNKNOWN

FALSE

OR

null

UNKNOWN

 

NOT

null

UNKNOWN

You can use the IS NULL or IS NOT NULL operator to check whether a single-value path expression contains null or not null values. If a single-value path expression contains null, then IS NULL will return true and IS NOT NULL will return false. If you want to compare whether the single-value path expression is not null, use the following WHERE clause:

WHERE c.parentCategory IS NOT NULL

You cannot use the IS NULL expression to compare a path expression that is of type collection; in other words, IS NULL will not detect whether a collection type path expression is an empty collection. JPQL provides the IS [NOT] EMPTY comparison operator to check whether a collection type path expression is empty. The following WHERE clause would work when you want to retrieve all Category entities that do not have any Items:

WHERE c.items IS EMPTY

As we explained earlier, JPQL statements are translated to SQL statements by the persistence provider. There is no equivalent of the IS EMPTY clause in SQL. So, you must be wondering what SQL statement is generated when IS EMPTY is used. The IS EMPTY clause is used with a collection-valued path expression that is typically an association field, and therefore the generated SQL statement will be determining whether the JOIN for the association retrieves any record in a subquery. To clarify, let’s examine this JPQL query:

SELECT c
FROM Category c
WHERE c.items IS EMPTY

If you recall our discussions from chapters 7 and 8, a many-to-many relationship exists between Category and Item entities, with CATEGORIES_ITEMS as the intersection table. This means the persistence provider will generate the following SQL statement:

SELECT
c.CATEGORY_ID, c.CATEGORY_NAME, c.CREATE_DATE,
c.CREATED_BY, c.PARENT_ID
FROM CATEGORIES c
WHERE (
(SELECT COUNT(*)
FROM CATEGORIES_ITEMS ci, ITEMS i
WHERE (
(ci.CATEGORY_ID = c.CATEGORY_ID) AND
(i.ITEM_ID = ci.ITEM_ID))) = 0)

From this generated SQL, you can see that the persistence provider uses a subquery to retrieve the number of associated items for a category by using the COUNT group function, and then compares the result with 0. This means that if no items are found, the collection must be empty, and the IS EMPTY clause is true.

Have you ever had an occasion to detect the presence of a single value in a collection? Sure you have! In JPQL you can use the MEMBER OF operator for just that purpose. Let’s take a look at how it works.

Checking for the existence of an entity in a collection

You can use the MEMBER OF operator to test whether an identifier variable, a single-value path expression, or an input parameter exists in a collection-value path expression. Here is the syntax for the MEMBER OF operator:

entity_expression [NOT] MEMBER [OF] collection_value_path_expression

The OF and NOT keywords are optional and can be omitted. Here is an example of using an input parameter with MEMBER OF:

WHERE :item MEMBER OF c.items

This condition will return true if the entity instance passed (as:item) in the query exists in the collection of c.items for a particular Category c.

10.3.4. Working with JPQL functions

JPQL provides several built-in functions for performing string or arithmetic operations. These functions can be used either in the WHERE or HAVING clause of a JPQL statement. You’ll learn more about the HAVING clause when we cover aggregate functions later in this chapter.

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 10.10. JPQL String functions

String Functions

Description

CONCAT(string1, string2)

Returns the value of concatenating two strings or literals together.

SUBSTRING(string, position, length)

Returns the substring starting at position that is length long.

TRIM([LEADING | TRAILING | BOTH]
[trim_character] FROM]
string_to_trimmed)

Trims the specified character to a new length. The trimming can either be LEADING, TRAILING, or from BOTH ends. If no trim_character is specified, then a blank space is assumed.

LOWER(string)

Returns the string after converting to lowercase.

UPPER(string)

Returns the string after converting to uppercase.

LENGTH(string)

Returns the length of a string.

LOCATE(searchString, stringToBeSearched[initialPosition])

Returns the position of a given string within another string. The search starts at position 1 if initialPosition is not specified.

Let’s look at a couple of common string function examples. Suppose we want to compare the result of concatenating of two string expressions with a string literal. The following WHERE clause will perform the task well:

WHERE CONCAT(u.firstName, u.lastName) = 'ViperAdmin'

If the concatenation of u.firstName and u.lastName does not result in ViperAdmin then the condition will return false.

You can use the SUBSTRING function to determine if the first three letters of u.lastName start with VIP:

WHERE SUBSTRING(u.lastName, 1, 3) = 'VIP'

The name of each string function is a good indicator of the functional operation it can perform. The direct analog of string functions is arithmetic functions. We’ll look at what JPQL supports in this area next.

Arithmetic functions

Although math is rarely used to perform CRUD operations, it is useful when trying to manipulate data for reports. JPQL only supports a bare minimum set of functions in this regard, and some vendors may choose to add functions to enhance their reporting capabilities. As with all vendor-specific features, be aware that using them will make your code less portable should you decide to change vendors in the future. You can use arithmetic functions in either the WHERE or HAVING clause of JPQL. Table 10.11 lists all arithmetic functions supported by JPQL.

Table 10.11. JPQL arithmetic functions

Arithmetic Functions

Description

ABS(simple_arithmetic_expression)

Returns the absolute value of simple_arithmetic_expression

SQRT(simple_arithmetic_expression)

Returns the square root value of simple_arithmetic_expression as a double

MOD(num, div)

Returns the result of executing the modulus operation for num, div

SIZE(collection_value_path_expression)

Returns the number of items in a collection

Most of the arithmetic functions are self-explanatory, such as this example of SIZE:

WHERE SIZE(c.items) = 5

This expression will return true when the SIZE of c.items is 5, and false otherwise.

Temporal functions

Most languages provide functions that retrieve the current date, time, or timestamp. JPQL offers the temporal functions shown in table 10.12. These functions translate into database-specific SQL functions, and the requested current date, time, or timestamp is retrieved from the database.

Table 10.12. JPQL temporal functions

Temporal Functions

Description

CURRENT_DATE

Returns current date

CURRENT_TIME

Returns current time

CURRENT_TIMESTAMP

Returns current timestamp

Note that because JPQL time values are retrieved from the database, they may vary slightly from the time retrieved from your JVM, if they aren’t both running on the same server. This is only an issue if you have a time-sensitive application. You can resolve this issue by running a time service on all servers that are part of your environment.

Next we’ll look at the SELECT clause of JPQL.

10.3.5. Using a SELECT clause

Although you saw some examples of the SELECT clause at the beginning of this chapter, 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:

SELECT [DISTINCT] expression1, expression2, .... expressionN

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:

SELECT c
FROM Category AS c

You can also use one or more path expressions in the SELECT clause:

SELECT c.categoryName, c.createdBy
FROM Category c

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:

SELECT DISTINCT c.categoryName, c.createdBy
FROM Category c

The following SELECT statement is invalid:

SELECT c.categoryName, c.items
FROM Category

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:

SELECT NEW actionbazaar.persistence.ItemReport (c.categoryID, c.createdBy)
FROM Category
WHERE categoryId.createdBy = :userName

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:

SELECT u
FROM User u
WHERE u.firstName LIKE :firstName

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:

   query = em.createNamedQuery("findUserByName");
query.setParameter("firstName", firstName);
List<User> users = query.getResultList();

Iterator i = users.iterator();
while (i.hasNext()) {
User user = (User) i.next();
System.out.print("User:"+emp.getUserId());
if (user instanceof Seller) {
Seller seller = (Seller) user;
System.out.println("Seller:" +
seller.getCommissionRate());
}
else if (user instanceof Bidder) {
Bidder bidder = (Bidder) bidder;
System.out.println("Bidder:" +
bidder.getDiscountRate());
}
}

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 discountRate for the Bidder entity. The resulting code should look like this:

Iterator i = users.iterator();
while (i.hasNext()) {
User user = (User)i.next();
System.out.print("User:" + emp.getUserId());
System.out.println(user.getRate());
}

10.3.6. 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

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:

SELECT MAX(i.itemPrice)
FROM Item i

If you want to find out how many Category entities exist in the system, use COUNT like this:

SELECT COUNT(c)
FROM Category c

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

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:

SELECT c.user, COUNT(c.categoryId)
FROM Category c
GROUP BY c.user

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:

SELECT c.user, COUNT(c.categoryId)
FROM Category c
GROUP BY c.user
HAVING COUNT(c.categoryId) > 5

In addition, you can have a WHERE clause in a query along with a GROUP BY clause such as

SELECT c.user, COUNT(c.categoryId)
FROM Category c
WHERE c.createDate is BETWEEN :date1 and :date2
GROUP BY c.user
HAVING COUNT(c.categoryId) > 5

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.

10.3.7. Ordering the query result

You can control the order of the values and objects retrieved by a query by using the ORDER BY clause:

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

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.

SELECT c
FROM Category c
ORDER BY c.categoryName ASC

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

SELECT c.categoryName, c.createDate
FROM Category c
ORDER BY c.categoryName ASC, c.createDate DESC

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:

SELECT c.categoryName, c.createDate
FROM Category c
ORDER BY c.categoryName ASC, c.createDate DESC

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.

10.3.8. 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:

[NOT] IN / [NOT] EXISTS / ALL / ANY / SOME (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:

SELECT i
FROM Item i
WHERE i.user IN (SELECT c.user
FROM Category c
WHERE c.categoryName LIKE :name)

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:

SELECT i
FROM Item i
WHERE EXISTS (SELECT c
FROM Category c
WHERE c.user = i.user)

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 <>.

Here is an example of a subquery demonstrating the ALL operator:

SELECT c
FROM Category c
WHERE c.createDate >= ALL
(SELECT i.createDate
FROM Item i
WHERE i.user = c.user)

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:

SELECT c
FROM Category c
WHERE c.createDate >= ANY
(SELECT i.createDate
FROM Item i
WHERE i.seller = c.user)

SOME is just an alias (or a synonym) for ANY, and can be used anywhere ANY can be used.

10.3.9. 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.

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:

SELECT i
FROM Item i, Category c
WHERE i.star = c.rating

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:

[INNER] JOIN join_association_path_expression [AS]
identification_variable

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:

SELECT u
FROM User u INNER JOIN u.Category c
WHERE u.userId LIKE ?1

The INNER clause is optional.

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:

SELECT u
FROM User u LEFT OUTER JOIN u.Category c
WHERE u.userId like ?1

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:

SELECT b
FROM Bid b FETCH JOIN b.bidder
WHERE b.bidDate >= :bidDate

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 chapter, 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.

10.3.10. 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:

UPDATE User u
SET u.status = 'G'
WHERE u.numTrades >=?1

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:

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

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.

10.4. Native SQL queries

Just what is native SQL? It’s the SQL understood by the specific database server—Oracle, MySQL, Derby, etc.—that you are using. This section provides what you need to start using native SQL with EJB 3 right now.


Note

In EJB 2 CMP entity beans, almost every vendor supported their own way of using SQL to perform queries. The many limitations in EJBQL were the primary driver for the vendor-specific extension for native SQL in EJB 2. Although JPA standardizes use of native SQL queries you should think twice about using native SQL in your applications, unless you are very proficient in SQL and you are trying to take advantage of the proprietary features of your database. Also keep in mind that the use of native SQL will make your applications less portable, if you decide to change your underlying database.


Suppose you want to generate a hierarchical list of categories, each showing its subcategories; it’s impossible to do that in JPQL because JPQL does not support recursive joins, similar to databases like Oracle. This means you have to take advantage of native SQL.

Let’s assume you’re using an Oracle database and you want to retrieve all subcategories of a particular Category by using recursive joins in the form of a START WITH ... CONNECT BY ... clause as follows:

SELECT CATEGORY_ID, CATEGORY_NAME
FROM CATEGORY
START WITH parent_id = ?
CONNECT BY PRIOR category_id = category_id

Ideally, you should limit your use of native SQL to queries that you cannot express using JPQL (as in our Oracle database–specific SQL query). However, for demonstration purposes, in our example in the next section, we’ve used a simple SQL statement that can be used with most relational databases.


Note

A JPA provider just executes SQL statements as JDBC statements and does not track whether the SQL statement updated data related to any entities. You should avoid using SQL INSERT, UPDATE, and DELETE statements in a native query because your persistence provider will have no knowledge of such changes in the database and it may lead to inconsistent/stale data if your JPA provider uses caching.


As in JPQL, you can use both dynamic queries and named queries with SQL. You have to remember the subtle differences between JPQL and SQL. JPQL returns an entity, or set, of scalar values, but a SQL query returns database records. Therefore, a SQL query may return more than entities, because you may join multiple tables in your SQL. Let’s see how to use native SQL with both dynamic and native queries.

10.4.1. Using dynamic queries with native SQL

You can use the createNativeQuery method of the EntityManager interface to create a dynamic query using SQL as follows:

Query q = em.createNativeQuery("SELECT user_id, first_name, last_name "
+ " FROM users WHERE user_id IN (SELECT seller_id FROM "
+ "items GROUP BY seller_id HAVING COUNT(*) > 1)",
actionbazaar.persistence.User.class);

return q.getResultList();

In this statement, the createNativeQuery method takes two parameters: the SQL query and the entity class being returned. This will become an issue if the query returns more than one entity class—which is why JPA allows a @SqlResultSetMapping to be used with the createNativeQuery method instead of passing an entity class. A @SqlResultSetMapping may be mapped to one or more entities.

For example, if we want to create a SqlResultSetMapping for the User entity and use in our native query, then we can use the @SqlResultSetMapping annotation as follows:

@SqlResultSetMapping(name = "UserResults",
entities = @EntityResult(
entityClass = actionbazaar.persistence.User.class))

Then we can specify the mapping in the Query as follows:

Query q = em.createNativeQuery("SELECT user_id, first_name, last_name "
+ " FROM users WHERE user_id IN (SELECT seller_id FROM "
+ "items GROUP BY seller_id HAVING COUNT(*) > 1)",
"UserResults");

return q.getResultList();

This is useful when the SQL query returns more than one entity. The persistence provider will automatically determine the entities being returned based on the SqlResultSetMapping, instantiate the appropriate entities, and initialize those entities with values based on the O/R mapping metadata.

Once you create a query, it makes no difference whether you retrieve the results from a native SQL or a JPQL query.

10.4.2. Using a named native SQL query

Using a named native query is quite similar to using a named JPQL query. To use a named native query, you must first create it. You can use the @NamedNativeQuery annotation to define a named query:

public @interface NamedNativeQuery {
String name();
String query();
QueryHint[] hints() default {};
Class resultClass() default void.class;
String resultSetMapping() default ""; // name of SQLResultSetMapping
}

You can either use an entity class or a result set mapping with the @NamedNative-Query annotation. Suppose we want to convert the query that we used earlier to a named native query. The first step is to define the named native query in the User entity:

@NamedNativeQuery(
name = "findUserWithMoreItems",
query = "SELECT user_id , first_name , last_name,
birth_date
FROM users
WHERE user_id IN
( SELECT seller_id
FROM items
GROUP BY seller_id HAVING COUNT(*) > ?)",
hints = {@QueryHint(name = "toplink.cache-usage",
value="DoNotCheckCache")},
resultClass = actionbazaar.persistence.User.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 = "findUserWithMoreItems",
query = "SELECT user_id , first_name , last_name,
birth_date
FROM users
WHERE user_id IN
(SELECT seller_id
FROM items
GROUP BY seller_id
HAVING COUNT(*) > ?)",
resultSetMapping = "UserResults")

You can provide a vendor-specific hint using the queryHint element of the NamedNativeQuery. It is similar to the hints element for NamedQuery discussed in section 10.2.4.


Note

There is no difference in executing a named native SQL query and a JPQL named query—except that a named parameter in native SQL query is not required by the JPA spec.


To illustrate how similar the execution of JPQL and native SQL queries is, let’s execute the named native query findUserWithMoreItems (which we defined earlier in a session bean method):

  return em.createNamedQuery("findUserWithMoreItems")
.setParameter(1, 5)
.getResultList();

This statement first creates a query instance for the named native query findUserWithMoreItems. Next, the required positional parameter is set. Finally, we return the result set.


JPA and database stored procedures

If you’ re a big fan of SQL, you may be willing to exploit the power of databasestored procedures. Unfortunately, JPA doesn’t support stored procedures, and you have to depend on a proprietary feature of your persistence provider. However, you can use simple stored functions (without out parameters) with a native SQL query.


Well, it appears you made it through the jungle of native SQL queries relatively unscathed. We hope you can see that while it is possible to drop down into the bowels of SQL from JPA, we don’t recommend it. Embedding SQL into Java strings can be very time-consuming when it comes to debugging. And you lose all the benefits that JPQL affords you. However, if you really do need to go native, JPA will make it possible.

10.5. Summary

Queries are an important piece of the persistence puzzle, and in this chapter we explored the various query capabilities provided by the EJB 3 Java Persistence API. JPA uses the following three methods to query entities:

  • EntityManager.find with the entity’s primary key
  • Queries written in JPQL
  • SQL queries native to the underlying database

You can either create ad hoc queries dynamically or use named queries that are stored with their associated entities. The EntityManager interface provides methods to create JPQL- or SQL-based queries, and the Query interface provides methods to execute a query. You can set parameters, pagination properties, and flush mode, and retrieve query results using methods in the Query interface. JPQL is the safest bet for writing queries, as it addresses the major limitations of EJBQL and is the only way to build applications that are portable across databases. Avoid using native SQL unless you have to use a vendor-specific feature.

This chapter concludes part 3 of this book. Part 4 assembles everything we’ve covered thus far, and allows you to apply these new skills in interesting, practical ways. We’ll also delve into packaging concerns and explore some EJB design patterns.