The Query API and JPQL
The Java Persistence API (JPA) utilizes a query language for communicating with underlying data stores. Although Java EE uses entities rather than SQL for database access, it provides a query language so that developers can obtain the required information via the entities. The Java Persistence Query Language (JPQL) does just that because it provides a facility for querying and working with Java EE entity objects. Although it is very similar to SQL, it is an object-relational query language, so there are some minor differences of which developers should be aware. Using JPQL along with Java EE entities allows developers to create versatile applications because JPQL is not database-specific and applications can be written once and deployed to run on top of a myriad of databases.
The release of Java EE 7 introduced with it the new JPA 2.1, and that means new features. For instance, some of the improvements to the JPQL include support for stored procedures and built-in functions, downcasting support, and outer join support with ON conditions. The recipes in this chapter will not attempt to cover all of the features that JPQL has to offer because there are many. However, the recipes contain enough information to introduce beginners to the world of JPQL and to get intermediate developers up-to-date with the latest that JPQL has to offer. To review the entire set of documentation for using JPQL, please see the online resources available at http://docs.oracle.com/javaee/7/tutorial/doc/bnbtg.html.
Note To run the sources for this chapter, please set up the provided NetBeans project entitled JavaEERecipes, or compile and deploy the sources in your own environment. You can also simply deploy the JavaEERecipes.war file that is distributed with the book to a GlassFish v4 application server. Once you’ve deployed it, please visit the following URL to run the example application for Chapter 10: http://localhost:8080/JavaEERecipes/faces/chapter10/home.xhtml.
Note The use of the CriteriaQuery is not very prevalent nowadays since JPQL makes the querying of data much easier. Therefore, this chapter will show you a couple of CriteriaQuery examples, but it will not go into detail on the topic. For further information regarding the use of the Criteria API, please refer to the online documentation.
Note The Netbeans IDE contains a JPQL editor that can come in handy when developing queries. For more information, please see Appendix A.
10-1. Querying All Instances of an Entity
Problem
You want to retrieve all the instances for a particular entity. That is, you want to query the underlying database table associated with the entity and retrieve all rows.
Solution #1
Call the EntityManager’s createQuery method, and use JPQL to formulate a query that will return all instances of a given entity. In the following example, a JPQL query is used to return all objects within the BookAuthor entity:
public List<BookAuthor> findAuthor(){
return em.createQuery("select object(o) from BookAuthor o ").getResultList();
}
When the findAuthor method is called, a List containing all of the BookAuthor entity instances in the entity (all records in the underlying database table) will be returned.
Solution #2
Create a CriteriaQuery object by generating a criteria builder from the EntityManager object and calling its createQuery method. Once a CriteriaQuery object has been created, generate a query by calling a series of the CriteriaBuilder methods against the entity that you want to query. Finally, call the EntityManager’s createQuery method, passing the query that you have previously built. Return the ResultList from the query to return all the rows from the table. In the following lines of code, you can see this technique performed:
javax.persistence.criteria.CriteriaQuery cq = getEntityManager().getCriteriaBuilder().createQuery();
Root<BookAuthor> bookAuthor = cq.from(BookAuthor);
cq.select(bookAuthor);
return getEntityManager().createQuery(cq).getResultList();
How It Works
An entity instance can be referred to as a record in the underlying data store. That is, there is an entity instance for each record within a given database table. That said, sometimes it is handy to retrieve all of the instances for a given entity. Some applications may require all objects in order to perform a particular task against each, or perhaps your application needs to simply display all of the instances of an entity for the user. Whatever the case, there are a couple of ways to retrieve all of the instances for a given entity. Each of the techniques should occur within an EJB.
In Solution #1, the JPQL can be used to query an entity for all instances. To create a dynamic query, call the EntityManager’s createQuery method, to which you can pass a string-based query that consists of JPQL syntax, or a javax.persistence.Query instance. The Query interface has a sizable number of methods that can be used to work with the query object. Table 10-1 describes what these methods do.
Table 10-1. javax.persistence.Query interface Methods
| Method | Description |
|---|---|
| executeUpdate | Executes an update or delete statement |
| getFirstResult | Specifies the position of the first result the query object was set to retrieve |
| getFlushMode | Gets the flush mode in effect for the query execution |
| getHints | Gets the properties and hints and associated values that are in effect for the query instance |
| getLockMode | Gets the current lock mode for the query |
| getMaxResults | Specifies the maximum number of results the query object was set to retrieve |
| getParameter | Gets the parameter object corresponding to the declared positional parameter |
| getParameters | Gets the parameter objects corresponding to the declared parameters of the query |
| getParameterValue(int) | Returns the value bound to the named or positional parameter |
| getResultList | Executes a SELECT query and then returns the query results as an untyped List |
| getSingleResult | Executes a SELECT query and then returns a single untyped result |
| isBound | Returns a Boolean indicating whether a value has been bound to the parameter |
In the example, a query string is passed to the method, and it reads as follows:
select object(o) from BookAuthor o
To break this down, the query is selecting all objects from the BookAuthor entity. Any letter could have been used in place of the o character within the query, but o is a bit of a standard since JPQL is referring to objects. All queries contain a SELECT clause, which is used to define the types of entity instances that you want to obtain. In the example, the entire instance is selected from the BookAuthor entity, as opposed to single fields that are contained within the instance. Since the JPA works with objects, queries should always return the entire object; if you want to use only a subset of fields from the object, then you can call upon those fields from the instance(s) returned from the query. The object keyword is optional and is purposeful mainly for readability. The same JPQL could be written as follows:
select o from BookAuthor o
The FROM clause can reference one or more identification variables that can refer to the name of an entity, an element of a single-valued relationship, an element of a collection relationship, or a member of a collection that is the multiple side of a one-to-many relationship. In the example, the BookAuthor variable refers to the entity itself.
Note For more information regarding the full query language syntax, please refer to the online documentation: http://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html.
The example in Solution #2 demonstrates the use of the CriteriaQuery, which is used to construct queries for entities by creating objects that define query criteria. To obtain a CriteriaQuery object, you can call the EntityManager’s getCriteriaBuilder method and, in turn, call the createQuery method of the CriteriaBuilder. The CriteriaQuery object allows you to specify a series of options that will be applied to a query so that an entity can be queried using native Java, without hard-coding any string queries. In the example, the CriteriaQuery instance is obtained by the chaining of subsequent method calls against the EntityManager and CriteriaBuilder instances. Once the CriteraQuery is obtained, it’s from method is called, passing the name of the entity that will be queried. A javax.persistence.criteria.Root object is returned from the call, which can then be passed to the CriteriaQuery instance select method to return a TypedQuery object to prepare the query for execution, which can then return the ResultList of entity instances. In the example, the final line of code chains method calls again, so you do not see the TypedQuery object referenced at all. However, if the chaining were to be removed, the code would look as follows:
cq.select(bookAuthor);
TypedQuery<BookAuthor> q = em.createQuery(cq);
return q.getResultList();
Both the JPQL and CriteriaQuery techniques can provide similar results. Neither technique is any better than the other, unless you prefer that the JPQL is easier to code or that CriteriaQuery is written in native Java.
10-2. Setting Parameters to Filter Query Results
Problem
You want to query an entity and retrieve only a subset of its instances that match specified criteria.
Solution
Write a JPQL dynamic query, and specify parameters that can be bound to the query using bind variables. Call the Query object’s setParameter method to assign a parameter value to each bind variable. In the following example, a query is written to search the Book entity for all Book instances that were written by a specified author. The BookAuthor object in this example is a named parameter that will be bound to the query using a bind variable.
public List<Book> findBooksByAuthor(BookAuthor authorId){
return em.createQuery("select o from Book o " +
"where :bookAuthor MEMBER OF o.authors")
.setParameter("bookAuthor", authorId)
.getResultList();
}
The matching Book instances for the given author will be returned.
How It Works
It is often desirable to return a refined list of results from a query, rather than returning the entire list of records within a database table. In standard SQL, the WHERE clause allows one or more expressions to be specified, which will ultimately refine the results of the query. Using JPQL, the WHERE clause works in the same manner, and the process of refining results of a query is almost identical to doing so with standard JDBC.
In the solution for this recipe, the JPQL technique is used to refine the results of a query against the Book entity such that only instances pertaining to books written by a specified author will be returned. The findBooksByAuthor method within the org.javaeerecipes.jpa.session.BookFacade class accepts a BookAuthor object as an argument, and the argument will then be specified to refine the results of the query. As you’ll see in the code, a single line of code (using the Effective Java builder pattern) within the findBooksByAuthor method performs the entire task. The EntityManager’s createQuery method is called, passing a string-based JPQL query that includes a bind variable named :bookAuthor. The JPQL string is as follows:
Select o from Book o where :bookAuthor MEMBER OF o.authors
After creating the query object, the Query interface’s setParameter method can be called, passing the name of the bind variable for which you want to substitute a value, along with the value you want to substitute it with. In this case, the String bookAuthor is passed along with the Author object you want to match against for obtaining Book instances. If more than one parameter needs to be specified, then more than one call to setParameter can be strung together so that each bind variable has a matching substitute. Finally, once all of the parameters have been set, the getResultList method can be called against the Query, returning the matching objects.
Note Two types of parameters can be used with JPQL: named and positional. The example in this recipe, along with many of the others in this book, use named parameters. Positional parameters are written a bit differently in that they are denoted within JPQL using a question mark (?) character, and a positional number is used instead of passing the variable name to the setParameter() method. The same query that is used in this recipe can be rewritten as follows to make use of positional parameters:
return em.createQuery("select o from Book o " +
"where ? MEMBER OF o.authors")
.setParameter(1, authorId)
.getResultList();
Both named and positional parameters achieve the same results. However, I recommend against using positional parameters because it makes code harder to manage, especially if there are more than a handful of parameters in use. It is also easier to mistype the setParameter() calls, and if the wrong positional number is passed with an incorrect parameter value, then issues can arise.
10-3. Returning a Single Object
Problem
You have specified JPQL for a given query that will return exactly one matching entity instance, and you want to store it within a local object so that tasks can be performed against it.
Solution
Create a dynamic query, specifying the JPQL that is necessary for obtaining the entity instance that matches the given criteria. The JPQL will include a bind variable that will bind the parameter to the query in order to obtain the desired instance. The method in the following code excerpt can be found in the org.javaeerecipes.jpa.session.BookFacade class within the sources:
public Book findByTitle(String title){
return (Book) em.createQuery("select object(o) from Book o " +
"where o.title = :title")
.setParameter("title", title.toUpperCase())
.getSingleResult();
}
To invoke the method and return results, the previous method, which resides within an EJB, can be invoked from within a JSF managed bean controller. The method that is defined within the controller can subsequently be referenced from within a JSF view to display the results.
How It Works
A single entity instance can be retrieved by specifying a query, along with the necessary parameters to refine the possible matches to a single object. The javax.persistence.Query interface’s getSingleResult method allows just one instance to be returned, given that there is only one instance that matches the given query specification. In the example to this recipe, you assume that each Book instance has a unique name to identify it. Therefore, you can be sure that when a name is bound to the query, it will return a single result.
Problems can arise if more than one instance matches the criteria. An attempt to call getSingleQuery using a query that returns more than one instance will result in a NonUniqueResultException being thrown. It is a good idea to catch this exception within your applications to avoid ugly error messages being displayed to the user if more than one matching instance exists. Another case to watch out for is when a query returns no result at all. If no result is returned, then a NoResultException will be thrown.
10-4. Creating Native Queries
Problem
The query you want to use against an entity contains some SQL functionality that pertains to the specific database vendor that your application is using, or you are more comfortable working with standard SQL than using JPQL. That said, you want to use standard SQL to query one of your entity objects.
Solution #1
Create a native query by calling the EntityManager object’s createNativeQuery method, and pass a SQL query as the first parameter and pass the entity class that you want to return the results of the query into as the second parameter. Once the query has been created, call one of the corresponding javax.persistence.Query methods (see Table 10-1) to return the results. The following example taken from the org.javaeerecipes.jpa.session.BookFacade EJB demonstrates the use of a native query on the Book entity:
public List<Book> obtainNativeList(){
Query query = em.createNativeQuery(
"select id, title, description " +
"FROM BOOK " +
" ORDER BY id", org.javaeerecipes.jpa.entity.Book.class);
return query.getResultList();
}
Solution #2
Specify a @NamedNativeQuery within the entity class for the entity class that you want to query. Provide a name, query, and mapping class for the @NamedNativeQuery via the annotation. Within the EJB method, call the EntityManager object’s createNativeQuery method, and provide the name that was specified as a named native query rather than a SQL string. The following code excerpt demonstrates the creation of a named native query for the org.javaeerecipes.jpa.entity.Book entity:
. . .
@Entity
@Table(name="BOOK")
@NamedNativeQuery(
name="allBooks",
query = "select id, title, description " +
"FROM BOOK " +
"ORDER BY id",
resultClass=Book.class)
. . .
Next, let’s take a look at how the named native query is invoked from within the EJB. The following excerpt of code is taken from the org.javaeerecipes.jpa.session.BookFacade bean, and it demonstrates the invocation of the allBooks named native query:
public List<Book> obtainNamedNativeList(){
Query query = em.createNamedQuery(
"allBooks", org.javaeerecipes.jpa.entity.Book.class);
return query.getResultList();
}
How It Works
Native queries provide a way to utilize native SQL code for retrieving data from an underlying data store within an EJB. Not only do they allow an inexperienced JPQL developer to write in native SQL, but they also allow native SQL syntax, such as Oracle-specific PL/SQL functions, or procedure calls to be made directly within an EJB. On the downside, however, native queries do not return results in an entity-oriented fashion, but rather as plain old objects. For this reason, the named native query provides the option to specify an entity class into which the results should be returned.
There are a handful of ways to work with native queries, and I’ve covered a couple of the most commonly used tactics in this recipe. A javax.persistence.Query is generated either by calling the EntityManager’s createNativeQuery method or by calling the EntityManager’s createNamedQuery method and passing a named native query. In Solution #1, a String-based SQL query is used to retrieve results into an entity class. For starters, the createNativeQuery method accepts a query in String format, or a named native query, for the first parameter. In Solution #1, a query is used to obtain all the records from the BOOK database table. The second argument to the createNativeQuery method is an optional mapping class into which the results of the query will be stored. Solution #1 specifies Book.class as the second parameter, which will map the columns of the database table to their corresponding fields within the Book entity. Once the Query instance is created, then its methods can be invoked in order to execute the query. In this case, the getResultSet method is invoked, which will return a List of the matching records and bind each of them to a Book entity class instance.
In Solution #2, a named native query is demonstrated. Named native queries allow the SQL string to be specified once within the corresponding entity class, and then they can be executed by simply passing the String-based name that has been assigned to the named native query. To utilize a named native query, add the @NamedNativeQuery annotation to the entity class that you want to query, and then specify values for the three parameters of the annotation: name, query, and resultClass. For the name parameter of the @NamedNativeQuery annotation, a String-based name that will be used to reference the query must be specified, the query parameter must be the native SQL string, and the resultClass must be the entity class that the query results will be stored into. The @NamedNativeQuery also includes the resultSetMapping parameter that can be used to specify a SqlResultSetMapping for those queries involving more than one table. To execute the named native query, use the same technique as demonstrated in Solution #1, but instead call the EntityManager object’s createNamedQuery method. Instead of specifying a SQL String, pass the name that was specified within the @NamedNativeQuery annotation.
Note If the named query involves more than one database table, then a SqlResultSetMapping must be defined. Please see Recipe 10-5 for more details.
In some cases using a native SQL query is the only solution for retrieving the data that your application requires. In all cases, it is recommended that JPQL be used, rather than native SQL, if possible. However, for those cases where native SQL is the only solution, then creating a native query using one of the techniques provided in this recipe is definitely the way to go. Which technique is better? Well, that depends on what you need to do. If you are trying to create a dynamic query, whereas the actual SQL String for the query may change dynamically, then the standard native query is the solution for you. However, if the SQL query that you are specifying will not change in a dynamic manner, then perhaps the named native query is the best choice for two reasons. First, the named native query allows SQL to be organized and stored within a single location, which is the entity class on which the SQL is querying. Second, named native queries can achieve better performance because they are cached after the first execution. Therefore, the next time the named native query is called, the SQL does not have to be recompiled. Such is not the case with a standard native query. Each time a standard native query is called, the SQL must be recompiled, which ultimately means that it will not be executed as fast.
10-5. Querying More Than One Entity
Problem
The JPQL or native SQL query being used references more than one entity or underlying database table, and therefore the results cannot be stored into a single entity object.
Solution #1
Use a SqlResultSetMapping, which allows the specification of more than one entity class for returning query results. The @SqlResultSetMapping annotation can be specified in order to map a result set to one or more entities, allowing the joining of database tables to become a nonissue. In the following example, the BOOK and BOOK_AUTHOR database tables are joined together using a native SQL query, and the results are returned using a SqlResultSetMapping. The following @SqlResultSetMapping can be found within the org.javaeerecipes.jpa.entity.BookAuthor entity class:
@SqlResultSetMapping(name="authorBooks",
entities= {
@EntityResult(entityClass=org.javaeerecipes.jpa.entity.Book.class, fields={
@FieldResult(name="id", column="BOOK_ID"),
@FieldResult(name="title", column="TITLE")
}),
@EntityResult(entityClass=org.javaeerecipes.jpa.entity.BookAuthor.class, fields={
@FieldResult(name="id", column="AUTHOR_ID"),
@FieldResult(name="first", column="FIRST"),
@FieldResult(name="last", column="LAST")
})
})
Next, let’s look at how the SqlResultSetMapping is used. The following method is taken from the org.javaeerecipes.jpa.session.BookAuthorFacade session bean:
public List findAuthorBooksMapping(){
Query qry = em.createNativeQuery(
"select b.id as BOOK_ID, b.title as TITLE, " +
"ba.id AS AUTHOR_ID, ba.first as FIRST, ba.last as LAST " +
"from book_author ba, book b, author_work aw " +
"where aw.author_id = ba.id " +
"and b.id = aw.book_id", "authorBooks");
return qry.getResultList();
}
The resulting List can then be referenced from within a JSF dataTable, or another client data iteration device, in order to display the results of the query.
Solution #2
Utilize a native query to return the necessary fields from more than one database table, and return the results to a HashMap, rather than to an entity class. In the following method taken from the org.javaeerecipes.jpa.session.BookAuthorFacade session bean, this technique is demonstrated:
public List<Map> findAuthorBooks(){
Query qry = em.createNativeQuery(
"select ba.id, ba.last, ba.first, ba.bio, b.id, b.title, b.image, b.description " +
"from book_author ba, book b, author_work aw " +
"where aw.author_id = ba.id " +
"and b.id = aw.book_id");
List<Object[]> results = qry.getResultList();
List data = new ArrayList<HashMap>();
if (!results.isEmpty()) {
for (Object[] result : results) {
HashMap resultMap = new HashMap();
resultMap.put("authorId", result[0]);
resultMap.put("authorLast", result[1]);
resultMap.put("authorFirst", result[2]);
resultMap.put("authorBio", result[3]);
resultMap.put("bookId", result[4]);
resultMap.put("bookTitle", result[5]);
resultMap.put("bookImage", result[6]);
resultMap.put("bookDescription", result[7]);
data.add(resultMap);
}
}
return data;
}
Using this solution, no SqlResultSetMapping is required, and the results are manually stored into a Map that can be referenced from a client, such as a JSF view.
How It Works
The SqlResultSetMapping can come in handy when you need to map your ResultSet to two or more entity classes. As demonstrated in the first solution to this recipe, configure the mapping by specifying a @SqlResultSetMapping annotation on the entity class of which you are querying. SqlResultSetMapping is useful when working with native queries and joining underlying database tables.
In the example, the @SqlResultSetMapping annotation is used to create a mapping between the Book and BookAuthor entity classes. The @SqlResultSetMapping annotation accepts a few different parameters, as described in Table 10-2.
Table 10-2. SqlResultSetMapping Parameters
| Parameter | Description |
|---|---|
| name | String-based name for the SqlResultSetMapping |
| entities | One or more @EntityResult annotations, denoting entity classes for the mapping |
| columns | One or more columns against which to map a resultSet, designated by @FieldResult or @ColumnResult annotations |
To use a SqlResultSetMapping, simply specify its name rather than an entity class when creating the native query. In the following excerpt taken from the solution, the query results are mapped to the authorBooks SqlResultSetMapping:
Query qry = em.createNativeQuery(
"select b.id as BOOK_ID, b.title as TITLE, " +
"ba.id AS AUTHOR_ID, ba.first as FIRST, ba.last as LAST " +
"from book_author ba, book b, author_work aw " +
"where aw.author_id = ba.id " +
"and b.id = aw.book_id", "authorBooks");
The List of results that is returned from this query can be utilized within a client, such as a JSF view, in the same manner as any List containing a single entity’s results. The SqlResultSetMapping allows fields of an entity class to be mapped to a given name so that the name can then be specified in order to obtain the value for the mapped field. For instance, the following JSF dataTable source is taken from the chapter10/recipe10_05a.xhtml view, and it displays the List of results from the query in the solution:
<h:dataTable id="table" value="#{authorController.authorBooks}"
var="authorBook">
<h:column>
<f:facet name="header">
<h:outputText value="Book ID"/>
</f:facet>
<h:outputText value="#{authorBook.id}"/>
</h:column>
<h:column>
<f:facet name="header">
<h:outputText value="Title"/>
</f:facet>
<h:outputText value="#{authorBook.title}"/>
</h:column>
<h:column>
<f:facet name="header">
<h:outputText value="Author"/>
</f:facet>
<h:outputText value="#{authorBook.first} #{authorBook.last}"/>
</h:column>
</h:dataTable>
As mentioned previously, entity fields can be mapped to a specified field returned from the database within the native SQL query. You can do so by specifying either the @FieldResult or @ColumnResult annotation for the columns parameter of a @SqlResultSetMapping annotation. For instance, in the example, you return only the TITLE and BOOK_ID columns from the BOOK database table, as well as the AUTHOR_ID, FIRST, and LAST columns from the BOOK_AUTHOR table. You include the SQL in the native query to join the tables and retrieve the values from these columns and return a SqlResultSetMapping that corresponds the following:
@SqlResultSetMapping(name="authorBooks",
entities= {
@EntityResult(entityClass=org.javaeerecipes.jpa.entity.Book.class, fields={
@FieldResult(name="id", column="BOOK_ID"),
@FieldResult(name="title", column="TITLE")
}),
@EntityResult(entityClass=org.javaeerecipes.jpa.entity.BookAuthor.class, fields={
@FieldResult(name="id", column="AUTHOR_ID"),
@FieldResult(name="first", column="FIRST"),
@FieldResult(name="last", column="LAST")
})
})
In Solution #2, no SqlResultSetMapping is used, and instead the results of the query are returned into a List of HashMap objects, rather than entity objects. The query returns a list of Object[], which can then be iterated over in order to make the data accessible to the client. As shown in the example, after the list of Object[] is obtained, a for loop can be used to iterate over each Object[], obtaining the data for each returned database record field and storing it into a HashMap. To access the field data, specify a positional index that corresponds to the position of the database field data that you want to obtain. The positional indices correlate to the ordering of the returned fields within the SQL query, beginning with an index of 0. Therefore, to obtain the data for the first field returned in the query, specify an index of 0 on the Object for each row. As the Object[] is traversed, each database record can be parsed, in turn, obtaining the data for each field in that row. The resulting data is then stored into the HashMap, and a String-based key that corresponds to the name of the returned field is specified so that the data can be made accessible to the client.
When accessing a HashMap of results from a client, such as a JSF view, the data can be accessed in the same fashion as if a standard entity list were being used. This is because each HashMap element contains a key field that corresponds to the name of the data field. The following excerpt, taken from chapter10/recipe10_05b.xhtml, demonstrates how to use the results of a native query that have been stored into a HashMap using this technique.
<h:dataTable id="table" value="#{authorController.authorBooks}"
var="authorBook">
<h:column>
<f:facet name="header">
<h:outputText value="Title"/>
</f:facet>
<h:outputText value="#{authorBook.bookTitle}"/>
</h:column>
<h:column>
<f:facet name="header">
<h:outputText value="Author"/>
</f:facet>
<h:outputText value="#{authorBook.authorFirst} #{authorBook.authorLast}"/>
</h:column>
</h:dataTable>
The SqlResultSetMapping makes it possible to use customized queries and joins into returning results via entity class objects. It is one more of the techniques that help complete the object-relational mapping (ORM) experience when using JPA.
10-6. Calling JPQL Aggregate Functions
Problem
You want to return the total number of records from a database table that match specified filtering criteria. For example, you want to return the total count of BookAuthor instances for a specified book.
Solution
Use the JPQL aggregate function COUNT to return the total number of objects that match the given query. The following method, which resides within the org.javaeerecipes.jpa.session.AuthorWorkFacade class, uses the COUNT aggregate function:
public Long findAuthorCount(Book book){
Query qry = em.createQuery("select COUNT(o.authorId) from AuthorWork o " +
"where o.bookId = :book")
.setParameter("book", book.id);
return (Long) qry.getSingleResult();
}
The function will return a Long result, which will be the count of matching AuthorWork results.
How It Works
Aggregate functions are those that can group values of multiple rows together on certain criteria to form a single value. Native SQL contains aggregate functions that can be useful for calculating the sum of all rows in a particular table, maximum values of a column, first values within a column, and so on. JPQL contains a number of aggregate functions that can be used within queries. In this recipe, the example demonstrates the use of the COUNT function, which returns the total number of rows in an underlying data store table. The value is calculated and returned as a Long data type, which can be cast from a call to the javax.persistence.Query object’s getSingleResult method. However, there are a number of other functions at your disposal. Table 10-3 lists those functions and their return type.
Table 10-3. JPQL Aggregate Functions
| Function | Description | Return Type |
|---|---|---|
| COUNT | Total number of records | Long |
| MAX | Record with largest numeric value | Same as field to which applied |
| MIN | Record with lowest numeric value | Same as field to which applied |
| AVG | Average of all numeric values in column | Double |
| SUM | Sum of all values in column | Long when applied to integral types |
| Double when applied to floating-point | ||
| BigInteger when applied to BigInteger | ||
| BigDecimal when applied to BigDecimal |
If a particular database record contains a NULL value for a column to which an aggregate function is being applied, then that NULL value is eliminated before the function is applied. The DISTINCT keyword can be used to specify that any duplicate values should be eliminated before the function is applied. The following line of code demonstrates the use of DISTINCT:
Query qry = em.createQuery("select DISTINCT(COUNT(o.title)) from Book o");
The important thing to remember when using aggregate functions is that they are applied to the same field within all objects that satisfy the query. This is analogous to the function being applied to all values returned for a single column’s results within a query.
10-7. Invoking Database Stored Procedures Natively
Problem
The application you are writing uses JPQL and relies on one or more database stored procedures to perform tasks on the data. You need to have the ability to call those stored procedures from within the business logic of your Java application code.
Solution
Create a native query, and write a SQL String that executes the database stored procedure. Suppose you have a database procedure named CREATE_USER and it accepts two arguments: username and password. You can invoke the CREATE_USER procedure by calling it via a native SQL query. The following method, named createUser, accepts a user name and password as arguments and passes them to the underlying database procedure and executes it:
public void createUser(String user, String pass){
Query qry = (Query) em.createNativeQuery("select CREATE_USER('" + user + "','" + pass + "')
from dual");
qry.getSingleResult();
}
How It Works
Historically, the only way to work with database-stored procedures from JPA was to utilize a native query. The solution to this recipe demonstrates this tactic because a native query is used to invoke a database-stored procedure. In the example, a method named createUser accepts two parameters, username and password, which are both passed to the database stored procedure named CREATE_USER via the native query. The EntityManager’s createNativeQuery method is called, and a SQL String that performs a SELECT on the stored procedure is passed to the method. In SQL, performing a SELECT on a stored procedure will cause the procedure to be executed. Notice that the DUAL table is being referenced in the SQL. The DUAL is a dummy table that can be used when you need to apply SELECT statements to different database constructs, such as a stored procedure.
Execution of native SQL is an acceptable solution for invoking stored procedures that have no return values or when you have only a limited number of SQL statements to maintain. However, in most enterprise situations that require an application with multiple stored procedure calls or calls that require a return value, the @NamedStoredProcedure solution in Recipe 9-10 can be advantageous.
10-8. Joining to Retrieve Instances Matching All Cases
Problem
You want to create joins between entities in order to return fields from more than one underlying database table.
Solution
Use JPQL to create a join between two entities that share a one-to-many and many-to-one relationship with each other. In this example, a one-to-many relationship is set up against the Book and Chapter entities such that one book can contain many chapters. The following excerpt from the org.javaeerecipes.jpa.entity.Book class demonstrates the one-to-many relationship declaration:
@OneToMany(mappedBy="book", cascade=CascadeType.ALL)
private List<Chapter> chapters = null;
The Chapter entity contains a many-to-one relationship with the Book entity, such that many chapters can be related to one book. The following excerpt from the org.javaeerecipes.jpa.entity.Chapter class demonstrates the many-to-one relationship:
@ManyToOne
@JoinColumn(name = "BOOK_ID")
private Book book;
Ultimately, the join query is contained within a method named findBookByChapterTitle, which resides in the org.javaeerecipes.jpa.session.Chapter session bean. The following code excerpt contains the lines of code that make up that method:
public List<Book> findBookByChapterTitle(String chapterTitle){
return em.createQuery("select b from Book b INNER JOIN b.chapters c " +
"where c.title = :title")
.setParameter("title", chapterTitle)
.getResultList();
}
Note To return several different properties within the SELECT clause, rather than an object, the result will be returned in an Object[]. To find out more about working with such a solution, please refer to Solution #2 of Recipe 10-5.
How It Works
The most common type of database table join operation is known as an inner join. When performing an inner join, all of the columns from each table will be available to be returned as if it were a single, combined table. To create a join between two entities, they must be related to each other via a one-to-many relationship. This means that one of the entities could contain an instance that possibly contains many references to the other entity, whereas the other entity could have many instances that would reference only one instance of the other entity. In the example for this recipe, the Book entity has a one-to-many relationship with the Chapter entity. This means that a single book may contain many chapters.
The example for this recipe demonstrates a join between the Book and Chapters entities. The method findBookByChapterTitle contains a JPQL query that will return any Book objects that contain a matching chapter title. To generate an inner join query, invoke the EntityManager object’s createQuery method, passing the String-based JPQL query that contains the join syntax. A JPQL string for performing an inner join should be written in the following format, where INNER is an optional (default) keyword:
SELECT a.col1, a.col2 from Entity1 a [INNER] JOIN a.collectionColumn b WHERE expressions
In the example, an entire Book instance will be returned for each Book entity that contains a Chapter instance, which has a title matching the parameter. Typically the join occurs over a foreign key, and in the case of the one-to-many relationship, it occurs on the field that is a collection of the related entity’s instances.
10-9. Joining to Retrieve All Rows Regardless of Match
Problem
You want to create joins between entities in order to produce results that will include all objects of the left entity listed and matching results or NULL values when there is no match from the right entity listed.
Solution
In this example, a one-to-many relationship is set up against the Book and Chapter entities such that one book can contain many chapters. The following excerpt from the org.javaeerecipes.jpa.entity.Book class demonstrates the one-to-many relationship declaration:
@OneToMany(mappedBy="book", cascade=CascadeType.ALL)
private List<Chapter> chapters = null;
The Chapter entity has a many-to-one relationship with the Book entity, such that many chapters can be related to one book. The following excerpt from the org.javaeerecipes.jpa.entity.Chapter class demonstrates the many-to-one relationship:
@ManyToOne
@JoinColumn(name = "BOOK_ID")
private Book book;
The code that contains the left outer join query resides within the findAllBooksByChapterNumber method, which is contained within the org.javaeerecipes.jpa.session.ChapterFacade class. The following excerpt taken from the class lists the method implementation:
public List<Book> findAllBooksByChapterNumber(BigDecimal chapterNumber){
return em.createQuery("select b from Book b LEFT OUTER JOIN b.chapters c " +
"where c.chapterNumber = :num")
.setParameter("num", chapterNumber)
.getResultList();
}
How It Works
An outer join, otherwise known as a LEFT OUTER JOIN or LEFT JOIN, is not as common of an occurrence as an inner join. To explain an outer join in database terminology, all rows of the table listed on the left side of the JOIN keyword are returned, and only those matching rows from the table listed to the right of the keyword will be returned. In other words, an outer join enables the retrieval of a set of database records where a matching value within the join may not be present. In JPA terminology, all instances of the entity class to the left of the JOIN keyword will be returned.
Outer joins on entities usually occur between two related entities in which there is a one-to-many relationship, or vice versa. To form an outer join JPQL query, use the following format, where the [OUTER] keyword is optional:
SELECT a.col1, a.col2 FROM Entity1 a LEFT [OUTER] JOIN a.collectionColumn b WHERE expression
In the example, all Book objects would be returned, but only those Chapter objects that match the specified criteria would be included in the ResultSet.
10-10. Applying JPQL Functional Expressions
Problem
You want to apply functions within your JPQL Strings to alter the results of the execution. For example, you are interested in altering Strings that will be used within the WHERE clause of your JPQL query.
Solution
Utilize any of the built-in JPQL functions to apply functional expressions to your JPQL. To alter Strings that are utilized within a JPQL query, develop the query containing String functions that will be applied within the WHERE clause of the query. In the following example, the UPPER function is utilized in order to change the case of the given text into all uppercase letters. In this case, a search page has been set up for users to enter an author’s last name and search the database for a match. The String that the user enters is converted to uppercase and used to query the database.
The following lines of code are taken from the search view, which resides within the JSF view that resides in the chapter10/recipe10_10.xhtml file.
<ui:composition template="layout/custom_template_search.xhtml">
<ui:define name="content">
<h:form>
<h2>Recipe 10-10: Using JPA String Functions</h2>
<br/>
<p>Enter an author's last name below to search the author database.</p>
<br/>
<h:outputLabel value="Last Name:"/>
<h:inputText id="last" value="#{authorController.authorLast}" size="75"/>
<br/>
<br/>
<h:commandButton value="Search" action="#{authorController.findAuthorByLast}"/>
</h:form>
</ui:define>
</ui:composition>
Next, the code for the managed bean controller method, findAuthorByLast, is listed next. This method resides within the org.javaeerecipes.jpa.jsf.AuthorController class. This code is responsible for populating the authorList and then directing navigation to the recipe10_10b.xhtml view.
public String findAuthorByLast(){
authorList = ejbFacade.findAuthorByLast(authorLast);
return "/chapter10/recipe10_10b.xhtml";
}
Lastly, the EJB method named findAuthorByLast(String) is contained within the org.javaeerecipes.jpa.session.BookAuthorFacade class. The method accepts the String value that the user entered into the web search form and uses it to query the database for a matching author.
public List<BookAuthor> findAuthorByLast(String authorLast){
return em.createQuery("select o from BookAuthor o " +
"where o.last = UPPER(:authorLast)")
.setParameter("authorLast", authorLast).getResultList();
}
The resulting page will display any author names that match the text that was entered by the user.
How It Works
The JPA query language contains a handful of functions that can be used to manipulate Strings, perform arithmetic, and make dates easier to work with. The functions can be specified within the WHERE or HAVING clause of JPQL query Strings. JPQL contains a number of String functions. Table 10-4 lists the different String functions that are available, along with a description of what they do.
Table 10-4. JPQL String Functions
| Function | Description |
|---|---|
| CONCAT(string1, string2) | Returns a concatenated String composed of the two arguments. |
| SUBSTRING(string, expr1, expr2) | Returns a substring of the specified String. The first position within the substring is denoted by expr1, and the length of the substring is denoted by expr2. |
| TRIM([[spec][char]FROM]str) | Trims a specified character (spec) from a string (str). |
| LOWER(string) | Returns the given String in all lowercase letters. |
| UPPER(string) | Returns the given String in all uppercase letters. |
There are also a number of functions within JPQL to help perform arithmetic within queries. Table 10-5 lists the different arithmetic functions that are available, along with a description of what they do.
Table 10-5. JPQL Arithmetic Functions
| Function | Description |
|---|---|
| ABS(expr) | Returns the absolute value. Takes a numeric argument and returns a number of the same type. |
| SQRT(expr) | Returns the square root value. Takes a numeric argument and returns a double. |
| MOD(expr1, expr2) | Returns the modulus value in integer format. |
| SIZE(collection) | Returns the total number of elements in the given collection in integer format. If the collection contains no elements, it evaluates to zero. |
Working with dates from any programming language can sometimes be a bit tough. The JPQL contains a handful of helpful datetime functions to make it a bit easier. Table 10-6 lists the different datetime functions that are available, along with a description of what they do.
Table 10-6. JPQL Datetime Functions
| Function | Description |
|---|---|
| CURRENT_DATE | Returns the current date |
| CURRENT_TIME | Returns the current time |
| CURRENT_TIMESTAMP | Returns the current timestamp |
10-11. Forcing Query Execution Rather Than Cache Use
Problem
The default EntityManager is using cached results from a database query, and you want to force a query to be executed each time a table is loaded, rather than allowing the results of the cache to be displayed.
Solution
After the javax.persistence.Query instance is created, set a hint, javax.persistence.cache.retrieveMode, to bypass the cache and force the query to be executed. In the following lines of code, the Book entity is queried, and the cache is bypassed by setting the hint:
public List<Book> findAllBooks(){
Query qry = em.createQuery("select o from Book o");
qry.setHint("javax.persistence.cache.retrieveMode", CacheRetrieveMode.BYPASS);
return qry.getResultList();
}
Upon execution, the query will be forced to execute, returning the most current results from the underlying database table.
How It Works
There are often occasions when an application requires the most current table data to be displayed or used for performing a given task. For instance, if you were to write a stock market application, it would not make sense to cache the current market results since stale data would not be very useful to investors. In such cases, it is imperative to force queries to be executed and bypass any caching. This is possible via the use of hints that can be registered with javax.persistence.Query instances.
By setting the javax.persistence.cache.retrieveMode hint to CacheRetrieveMode.BYPASS, the JPA is told to always force the execution of a query. When the query is executed, it will always return the most current results from the database.
10-12. Performing Bulk Updates and Deletes
Problem
You want to update or delete a group of entity objects.
Solution
Perform a bulk update or deletion using the Criteria API. The Criteria API allows the use of the Builder pattern for specifying entity operations. In the following example, a bulk update is performed on the Employee entity. The following example method resides in a session bean class for the org.javaeerecipes.jpa.entity.Employee entity. The session bean class name is org.javaeerecipes.jpa.session.EmployeeSession.java, and the following excerpt from that class shows how to perform a bulk update:
. . .
public String updateEmployeeStatusInactive() {
String returnMessage = null;
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaUpdate<Employee> q = builder.createCriteriaUpdate(Employee.class);
Root<Employee> e = q.from(Employee.class);
q.set(e.get("status"), "ACTIVE")
.where(builder.equal(e.get("status"), "INACTIVE"));
Query criteriaUpd = em.createQuery(q);
int result = criteriaUpd.executeUpdate();
if (result > 0){
returnMessage = result + " records updated";
} else {
returnMessage = "No records updated";
}
return returnMessage;
}
. . .
Similarly, the Criteria API can be used to perform a bulk deletion. The following method, also within the EmployeeSession bean, demonstrates how to do so:
. . .
public String deleteEmployeeOnStatus(String condition) {
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaDelete<Employee> q = builder.createCriteriaDelete(Employee.class);
Root<Employee> e = q.from(Employee.class);
q.where(builder.equal(e.get("status"), condition));
return null;
}
. . .
How It Works
The Criteria API has been enhanced to support bulk updates and deletions. The Criteria API allows developers to utilize Java language syntax in order to perform database queries and manipulations, rather than JPQL or SQL. A javax.persistence.criteria.CriteriaUpdate object can be used to perform bulk update operations, and a javax.persistence.critera.CriteriaDelete object can be used to perform bulk deletion operations. How do we obtain such objects? The Criteria API is dependent upon the javax.persistence.criteria.CriteriaBuilder interface, which is used to return objects that can be used to work with specified Entity classes. In the JPA 2.1 release, the CriteriaBuilder has been updated to include the methods createCriteriaUpdate and createCriteriaDelete, which will return the CriteriaUpdate or CriteriaDelete object, respectively.
To use the CriteriaBuilder, you first need to obtain a CriteriaBuilder from the EntityManager. You can then use the CriteriaBuilder to obtain the CriteriaUpdate or CriteriaDelete object of your choosing. In the following lines of code, a CriteriaUpdate object is obtained for use with an Employee entity:
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaUpdate<Employee> q = builder.createCriteriaUpdate(Employee.class);
Once obtained, the CriteriaUpdate can be used to build a query and set values, as desired, for making the necessary updates or deletions. In the following excerpt, the CriteriaUpdate object is used to update all Employee objects that have a status of INACTIVE, changing that status to ACTIVE:
Root<Employee> e = q.from(Employee.class);
q.set(e.get("status"), "ACTIVE")
.where(builder.equal(e.get("status"), "INACTIVE"));
Let’s break this down a bit to explain what exactly is going on. First, the query root is set by calling the q.from method and passing the entity class for which you want to obtain the root, where q is the CriteriaUpdate object. Next, the q.set method is invoked, passing the Path to the Employee status attribute, along with the ACTIVE String. The q.set method is performing the bulk update. To further refine the query, a WHERE clause is added by adding a chained call to the .where method and passing the Employee objects that have a status of INACTIVE. The entire criteria can be seen in the solution for this recipe.
Finally, to complete the transaction, you must create the Query object and then execute it using the following lines of code:
Query criteriaUpd = em.createQuery(q);
criteriaUpd.executeUpdate();
The bulk deletion is very similar, except instead of using the CriteriaBuilder to obtain a CriteriaUpdate object, use it to obtain a CriteriaDelete object instead. To obtain a CriteriaDelete object, call the CriteriaBuilder createCriteriaDelete method, as follows:
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaDelete<Employee> q = builder.createCriteriaDelete(Employee.class);
Once a CriteriaDelete object has been obtained, then the conditions for deletion need to be specified by filtering the results using a call (or chain of calls) to the .where method. When using the bulk delete, all objects that match the specified condition will be deleted. For example, the following lines of code demonstrate how to delete all Employee objects that have the status attribute equal to INACTIVE:
Root<Employee> e = q.from(Employee.class);
q.where(builder.equal(e.get("status"), "INACTIVE"));
Note Both the CriteriaUpdate and CriteriaDelete examples demonstrated can be made more type-safe by using the MetaModel API. For each entity class in a particular persistence unit, a metamodel class is created with a trailing underscore, along with the attributes that correspond to the persistent fields of the entity class. This metamodel can be used to manage entity classes and their persistent state and relationships. Therefore, instead of specifying an error-prone String in the Path to obtain a particular attribute, you could specify the metamodel attribute instead, as follows: e.get(Employee_.status).
The Criteria API can be very detailed, and it is also very powerful. To learn more about the Criteria API, please see the documentation online at http://docs.oracle.com/javaee/7/tutorial/doc/gjitv.html.
10-13. Retrieving Entity Subclasses
Problem
You want to obtain the data for an entity, along with all of the data from that entity’s subclasses.
Solution
Utilize the downcasting feature of the Java EE 7 JPA API. To do so, specify the TREAT keyword within the FROM and/or WHERE clause of a JPA query in order to filter the specified types and subtypes that you want to retrieve. In the following example, the query will return all BookStore entities that are from the IT book. The assumption is that the ItCategory entity is a subtype of the BookCategory entity. The method in the example, named getBookCategories, resides within the org.javaeerecipes.session.BookCategoryFacade session bean.
public List getBookCategories(){
TypedQuery<Object[]> qry = em.createQuery("select a.name, a.genre, a.description " +
"from BookStore s JOIN TREAT(s.categories as ItCategory) a", Object[].class);
List data = new ArrayList();
if (!qry.getResultList().isEmpty()) {
List<Object[]> tdata = qry.getResultList();
for (Object[] t : tdata) {
HashMap resultMap = new HashMap();
resultMap.put("name", t[0]);
resultMap.put("genre", t[1]);
resultMap.put("categoryDesc", t[2]);
data.add(resultMap);
}
}
return data;
}
When invoked, this query will return data from the ItCategory entity, which is a subclass of the BookCategory entity, as per the previous description. To better understand how to relate the entities, please refer the code within the two entities, located within the org.javaeerecipes.entity.BookCategory.java and org.javaeerecipes.entity.ItCategory.java files in the book sources.
How It Works
The act of downcasting is defined as the casting of a base type or class reference to one of its derived types or classes. The Java EE 7 platform introduces the concept of downcasting to JPA by providing the ability to obtain a reference to a subclass of a specified entity within a query. In other words, you can explicitly query one or more entities and retrieve the attributes from each of the entities as well as any attributes from entities that are subclasses of those that are explicitly declared. To provide this ability, the new TREAT keyword has been added to JPA.
The use of the TREAT operator is supported for downcasting within path expressions in the FROM and WHERE clauses. The first argument to the TREAT operator should be a subtype of the target type; otherwise, the path is considered to have no value, attributing nothing to the end result. The TREAT operator can filter on the specified types and subtypes, as well as perform a downcast.
The syntax for use of the TREAT operator is as follows:
SELECT b.attr1, b.attr2
FROM EntityA a JOIN TREAT(a.referenceToEntityB as EntityBSubType) b
In the previous JPQL, the TREAT operation contains an attribute from the specified entity (EntityA) that relates to a joined entity (EntityB). The TREAT operation tells the container to treat the referenced entity (EntityB) as the type of EntityBSubtype. Therefore, the downcast takes place and allows access to those subtype entities. The following lines of code demonstrate this technique in action:
SELECT a.name, a.genre, a.description
FROM BookStore s JOIN TREAT(s.categories AS ItCategory) a
As mentioned previously, the TREAT operator can also be used within the WHERE clause in order to filter a query based upon subtype attribute values. Downcasting support adds yet another feature to the scope of JPA, making it even more flexible for developers to use. This technique will make it easier to obtain values from related entities or subtypes, without the need to issue an extra query.
10-14. Joining with ON Conditions
Problem
You want to retrieve all the entities that match the specified criteria for joining two entities, along with each entity that does not match on the left side of an OUTER join.
Solution
Utilize the ON condition to specify a join of two or more entity classes based upon the specified filtering criteria. The following method includes the JPQL for retrieving all Jobs entities, along with a count of the number of Employee entities that belong to those Jobs. This method, named obtainActiveEmployeeCount, utilizes the ON condition to filter the join based upon the Employee status.
public List obtainActiveEmployeeCount() {
TypedQuery<Object[]> qry = em.createQuery("SELECT j.title, count(e) "
+ "FROM Jobs j LEFT JOIN j.employees e "
+ "ON e.status = 'ACTIVE' "
+ "WHERE j.salary >= 50000 "
+ "GROUP BY j.title", Object[].class);
List data = new ArrayList();
if (!qry.getResultList().isEmpty()) {
List<Object[]> tdata = qry.getResultList();
for (Object[] t : tdata) {
HashMap resultMap = new HashMap();
resultMap.put("title", t[0]);
resultMap.put("count", t[1]);
data.add(resultMap);
}
}
return data;
}
How It Works
When writing JPQL queries, it is sometimes beneficial to join two or more tables to acquire related information. Furthermore, it is usually helpful to filter information based upon certain specified criteria so that the number of records returned can be manageable. JPQL joins typically include INNER, OUTER, and FETCH joins. To review, an INNER join allows retrieval from two tables such that records being returned contain at least one match in both tables. For instance, you may want to query an Employee entity and join it to the Jobs entity to return only those employees who have a specific job title. An OUTER join allows retrieval from two tables such that all of the records from one of the entities (left entity) are returned, regardless of whether they match with a record in the other entity. Lastly, a FETCH join enables the fetching of an association as a side effect of the query execution. IN JPA 2.1, JPQL has been updated to include the ON condition, which allows you to perform an OUTER join and include a specified condition with the join. This capability has always been available with the WHERE clause of the JPQL query, but what about the cases when you want to return all matching records along with those that may not match, like with an OUTER join? The JPA 2.1 release provides this functionality in a concise manner with the addition of ON conditions. Simply put, an ON condition modifies a join query such that it will incorporate better control over the data that is returned in a concise manner.
To demonstrate this new syntax, let’s take a look at a SQL query, and then you will compare it to its JPQL counterpart. The following SQL will join the EMPLOYEE table with the JOBS table on the JOB_ID field. It will also limit the returned records to those that include a salary of greater than or equal to 50,000 with the specification in the WHERE clause.
SELECT J.TITLE, COUNT(E.ID)
FROM JOBS J LEFT JOIN EMPLOYEE E
ON J.JOB_ID = E.JOB_ID and E.STATUS 'ACTIVE'
WHERE J.SALARY >= 50000
GROUP BY J.TITLE;
This SQL will return all of the JOB records and include a count of each job that contains an Employee whose status is ACTIVE. The method in the solution of this recipe contains the JPQL equivalent for this SQL, using the ON condition to perform the join. In the end, the ON condition helps make JPQL outer joins more concise and easy to use. Although the same capability has been available in previous versions of JPQL, the ON clause helps make record filtering with joins much easier.