Querying is a fundamental feature of all relational databases. It allows you to pull complex reports, calculations, and information about intricately related objects from persistence storage. Queries in Java Persistence are done using the JPA QL query language, native Structured Query Language (SQL), and the new Criteria API.
JPA QL is a declarative query language similar to the SQL used in relational databases, but it is tailored to work with Java objects rather than a relational schema. To execute queries, you reference the properties and relationships of your entity beans rather than the underlying tables and columns these objects are mapped to. When a JPA QL query is executed, the entity manager uses the information you provided through the mapping metadata, discussed in the previous two topics, and automatically translates it to an appropriate native SQL query. This generated native SQL is then executed through a JDBC driver directly on your database. Since JPA QL is a query language that represents Java objects, it is portable across vendor database implementations because the entity manager handles the conversion to raw SQL for you.
The JPA QL language is easy for developers to learn, yet precise enough to be interpreted into native database code. This rich and flexible query language empowers developers while executing in fast native code at runtime. Plus, because JPA QL is object-oriented, queries are usually much more compact and readable than their SQL equivalent. EJB QL existed in the EJB 2.1 specification, and it is really the only feature that survived in the new release. Since Persistence is completely spun off in its own specification, it’s now known as JPA QL. Although it was well-formed, EJB QL in EJB 2.1 was incomplete, forcing developers to escape to JDBC or write really inefficient code. JPA QL has been greatly improved and expanded to be more parallel to SQL and should now meet most of your needs. Things such as projection, GROUP BY, and HAVING have been added, as well as bulk updates and deletes.
Like all query languages, JPA QL is modeled as a string, and therefore cannot be checked for structural correctness by the Java compiler. New to Java Persistence 2.0 is the Criteria API, a fluid interface for building queries using an object model.
Sometimes, though, JPA QL and the Criteria API are not enough. Because these are portable query language extensions, they cannot always take advantage of specific proprietary features of your database vendor. JPA QL does not allow you to execute stored procedures, for instance. The EJB 3.x Expert Group foresaw the need for this and has provided an API to map native SQL calls to your entity beans.
JPA QL and native SQL queries are executed through the javax.persistence.Query interface. The Query interface is analogous to the java.sql.PreparedStatement interface. This Query API gives you methods for paging your result set, as well as passing Java parameters to your query. Queries can be predeclared through annotations or XML or created dynamically at runtime through EntityManager APIs.
Similarly, the Criteria API has the javax.persistence.criteria.CriteriaQuery interface, through which we can construct queries via the object model and submit these via the EntityManager.
Query API
A query in Java Persistence is a full-blown Java interface that you obtain at runtime from the entity manager:
The previous query looks for a single, unique Employee entity named Dave. The query is executed when the getSingleResult() method is called. This method expects that the call will return only one result. If no result is returned, the method throws a javax.persistence.EntityNotFoundException runtime exception. If more than one result is found, a javax.persistence.NonUniqueResultException runtime exception is thrown. Since both of these exceptions are RuntimeExceptions, the example code is not required to have a full try/catch block.
Queries are created using these EntityManager methods:
Let’s first look at using EntityManager.createQuery() to create a query dynamically at runtime:
There is a good chance that the NonUniqueResultException would be thrown by this example. Believe it or not, there are a lot of Daves in the world. You can change the query to use the getResultList() method to obtain a collection of results:
The getResultList() method does not throw an exception if there are no Daves; the returned list would just be empty.
Parameters
Much like a java.sql.PreparedStatement in JDBC, JPA QL allows you to specify parameters in query declarations so that you can reuse and execute the query multiple times on different sets of parameters. Two syntaxes are provided: named parameters and positional parameters. Let’s modify our earlier Employee query to take both last name and first name as named parameters:
The : character followed by the parameter name is used in JPA QL statements to identify a named parameter. The setParameter() method in this example takes the name of the parameter first, and then the actual value. EJB QL also supports positional parameters. Let’s modify the previous example to see this mode in action:
Instead of a string named parameter, setParameter() also takes a numeric parameter position. The ? character is used instead of the : character used with named parameters. Numeric parameters are indexed (start at) 1, not 0.
Using named parameters over positional parameters is recommended, as the JPA QL code becomes self-documenting. This is especially useful when working with predeclared queries.
A Date or Calendar object can represent a real date, a time of day, or a numeric timestamp. Because these object types can represent different things at the same time, you need to tell your Query object how it should use these parameters. The javax.persistence.TemporalType passed in as a parameter to the setParameter() method tells the Query interface what database type to use when converting the java.util.Date or java.util.Calendar parameter to a native SQL type.
Paging Results
Date Parameters
If you need to pass java.util.Date or java.util.Calendar parameters into a query, you need to use special setParameter methods:
Sometimes an executed query returns too many results. For instance, maybe we’re displaying a list of customers on a web page. The web page can display only so many customers, and maybe there are thousands or even millions of customers in the database. The Query API has two built-in functions to solve this type of scenario—setMax Results() and setFirstResult():
The getEmployees() method executes a query that obtains all employees from the database. We limit the number of employees it returns by using the setMaxResults() method, passing in the max method parameter. The method is also designed so that you can define an arbitrary set of results that you want returned by the execution of the query. The setFirstResult() method tells the query what position in the executed query’s result set you want returned. So, if you had a max result of 3 and a first result of 5, employees 5, 6, and 7 would be returned. We set this value in the getEmployees() method with the index parameter. Let’s take this method and write a code fragment that lists all customers in the database:
In this example, we loop through all employees in the database and output their names to the system output stream. If we had thousands or even millions of employees in the database, we could quickly run out of memory, as each execution of the getEmployees() method would return customers that were still managed by the entity manager. So, after we are finished outputting a block of customers, we call EntityManager.clear() to detach these customers and let them be garbage-collected by the Java VM. Use this pattern when you need to deal with a lot of entity objects within the same transaction.
Hints
Some Java Persistence vendors will provide additional add-on features that you can take advantage of when executing a query. For instance, the JBoss EJB 3.x implementation allows you to define a timeout for the query. These types of add-on features can be specified as hints using the setHint() method on the query. Here’s an example of defining a JBoss query timeout using hints:
The setHint() method takes a string name and an arbitrary object parameter.
FlushMode
We talked about flushing and flush modes. Sometimes you would like a different flush mode to be enforced for the duration of a query. For instance, maybe a query wants to make sure that the entity manager does not flush before the query is executed (since the default value implies that the entity manager can). The Query interface provides a setFlushMode() method for this particular purpose:
In this example, we’re telling the persistence provider that we do not want the query to do any automatic flushing before this particular query is executed. Using this commit mode can be dangerous if some correlated dirty entities are in the persistence context. You might return wrong entities from your query. Therefore, it is recommended that you use the FlushModeType.AUTO.
JPA QL
Now that you have a basic understanding of how to work with Query objects, you can learn what features are available to you for creating your own JPA QL queries. JPA QL is expressed in terms of the abstract persistence schema of an entity: its abstract schema name, basic properties, and relationship properties. JPA QL uses the abstract schema names to identify beans, the basic properties to specify values, and the relationship properties to navigate across relationships.
Abstract Schema Names
The abstract schema name can be defined by metadata, or it can default to a specific value. It defaults to the unqualified name of the entity bean class if the name() attribute is not specified when declaring the @Entity annotation.
In the following example, the @Entity.name() attribute is not specified on the Employee bean class, so Employee is used to reference the entity within JPA QL calls:
In the following example, since the @Entity.name() attribute is defined, you would reference Employee entities in JPA QL as Emp:
Simple Queries
The simplest JPA QL statement has no WHERE clause and only one abstract schema type. For example, you could define a query method to select all Employee beans:
The AS operator is optional, but it is used in this topic to help make the JPA QL statements clearer. The following two statements are equivalent:
The FROM clause determines which entity bean types will be included in the SELECT statement (i.e., it provides the scope of the select). In this case, the FROM clause declares the type to be Employee, which is the abstract schema name of the Employee entity. The AS e part of the clause assigns e as the identifier of the Employee entity. This is similar to SQL, which allows an identifier to be associated with a table. Identifiers can be any length and follow the same rules that are applied to field names in the Java programming language. However, identifiers cannot be the same as existing abstract schema name values. In addition, identification variable names are not case-sensitive, so an identifier of employee would be in conflict with an abstract schema name of Employee. For example, the following statement is illegal because Employee is the abstract schema name of the Employee EJB:
The SELECT clause determines the type of any values that are returned. In this case, the statement returns the Employee entity bean, as indicated by the e identifier.
The OBJECT() operator is optional and is a relic requirement of the EJB 2.1 specification. It is there for backward compatibility.
Identifiers cannot be EJB QL reserved words. In Java Persistence, the following words are reserved:
It’s a good practice to avoid all SQL reserved words, because you never know which ones will be used by future versions of EJB QL. You can find more information in the topic of SQL in a Nutshell.
Selecting Entity and Relationship Properties
JPA QL allows SELECT clauses to return any number of basic or relationship properties. For example, we can define a simple SELECT statement to return the name of all of employees in the registry:
The SELECT clause uses a simple path to select the Employee entity’s name property as the return type. The persistence property names are identified by the access type of your entity bean class, regardless of whether you’ve applied your mapping annotations on a get or set method or on the member fields of the class.
If you use get or set methods to specify your persistent properties, then the property name is extracted from the method name. The get part of the method name is removed, and the first character of the remaining string is lowercase.
When a query returns more than one item, you must use the Query.getResultList() method. The return value of the earlier query is dependent upon the type of the field being accessed. In our case, we obtain a List of Strings because the name property is a String. If the SELECT clause queries more than one column or entity, the results are aggregated in an object array (Object[]) in the java.util.List returned by getResult List(). The following code shows how to access the returned results from a multi-property select:
Paths can be as long as required. It’s common to use paths that navigate over one or more relationship fields to end at either a basic or a relationship property. For example, the following JPA QL statement selects the name of the manager of the employees:
You can’t navigate to one of the ZipCode class’s instance fields:
This JPA QL would now be legal:
Using these relationships, we can specify more complex paths. Paths cannot navigate beyond persistent properties. For example, imagine that Address uses a ZipCode class as its zip property and this property is stored as a byte stream in the database:
Of course, you could make the ZipCode class @Embeddable. If you did this, then you could obtain properties of the ZipCode class:
It’s illegal to navigate across a collection-based relationship field. The following JPA QL statement is illegal, even though the path ends in a single-type relationship field:
If you think about it, this limitation makes sense. You can’t use a navigation operator (.) in Java to access elements of a java.util.Collection object. For example, if get Phones() returns a java.util.Collection type, this statement is illegal:
Referencing the elements of a collection-based relationship field is possible, but it requires the use of an IN or JOIN operator and an identification assignment in the FROM clause.
Constructor Expressions
One of the most powerful features of JPA QL is the ability to specify a constructor within the SELECT clause that can allocate plain Java objects (nonentities) and pass columns you select into that constructor. For example, let’s say we want to aggregate IDs and names from our Employee entity into a plain Java object called Name:
We can actually have our query return a list of Name classes instead of a plain list of strings. We do this by calling the constructor of Name directly within our query:
The Query object will automatically allocate an instance of Name for each row returned, passing in the id and name columns as arguments to the Name’s constructor. This feature is incredibly useful for generating typed reports and can save you a lot of typing.
The IN Operator and INNER JOIN
Many relationships between entity beans are collection-based, and being able to access and select beans from these relationships is important. We’ve seen that it is illegal to select elements directly from a collection-based relationship. To overcome this limitation, JPA QL introduces the IN operator, which allows an identifier to represent individual elements in a collection-based relationship field.
The following query uses the IN operator to select the elements from a collection-based relationship. It returns all the Phones for an Employee:
The IN operator assigns the individual elements in the phones property to the p identifier. Once we have an identifier to represent the individual elements of the collection, we can reference them directly and even select them in the JPA QL statement. We can also use the element identifier in path expressions. For example, the following statement selects all phone numbers (a property of Phone) for an Employee:
The identifiers assigned in the FROM clause are evaluated from left to right. Once you declare an identifier, you can use it in subsequent declarations in the FROM clause. The e identifier, which was declared first, was subsequently used in the IN operator to define the p identifier.
This query can also be expressed as an INNER JOIN:
If there were three employees in our system, and john did not provide any phone numbers, the return values might look like this:
The previous query can also be expressed as a LEFT OUTER JOIN. This is just syntax sugar to parallel SQL-92:
The INNER JOIN syntax parallels the SQL language much better and is more intuitive for developers coming from the relational world.
LEFT JOIN
The LEFT JOIN syntax enables retrieval of a set of entities where matching values in the join statement may not exist. For values that do not exist, a null value is placed in the result set.
For example, let’s say we want to generate a report with an employee’s name and all the employee’s phone numbers. Some employees may not have specified a phone number, but we still want to list their names. We would use a LEFT JOIN to acquire all of this information, including employees with no phone numbers:
Fetch Joins
The JOIN FETCH syntax allows you to preload a returned entity’s relationships, even if the relationship property has a FetchType of LAZY. For example, let’s say we have defined our employee’s one-to-many relationship to Phone as follows:
If we want to print out all employee information, including their phone numbers, usually we would just query for all employees and then traverse the getPhones() method inside a for loop:
O There are performance problems with the preceding code. Because the Phone relationship is annotated as being lazily loaded in the Employee bean class, the Phone collection will not be initialized when we do the initial query.
© When getPhones() is executed, the persistence engine has to do an additional query to get the Phone entities associated with the Employee. This is called the N + 1 problem, as we have to do N extra queries beyond our initial query. When tuning database applications, it is always important to reduce the number of round-trips made to the database as much as possible. This is where the JOIN FETCH syntax comes into play. Let’s modify our query to preload the Phone association:
Using LEFT JOIN FETCH will additionally preload the Phone association. This can have a dramatic effect on performance because instead of N + 1 queries, only one query is made to the database.
Using DISTINCT
The DISTINCT keyword ensures that the query does not return duplicates. For example, the following query finds all employees on a team. This query will return duplicates:
If an Employee belongs to more than one Team, there will be duplicate references to that employee in the result. Using the DISTINCT keyword ensures that each Employee is represented only once in the result:
The WHERE Clause and Literals
You can use literal values to narrow the scope of the elements selected. This is accomplished through the WHERE clause, which behaves in much the same way as the WHERE clause in SQL.
For example, you can define a JPA QL statement that selects all the Employee entities that have a specific name. The literal in this case is a String literal. Literal strings are enclosed by single quotes. Literal values that include a single quote, such as the restaurant name Wendy’s, use two single quotes to escape the quote: Wendy”s. The following statement returns employees who have the name “Natalie Glass”:
Path expressions in the WHERE clause may be used in the same way as in the SELECT clause. When making comparisons with a literal, the path expression must evaluate to a basic property; you can’t compare a relationship field with a literal.
In addition to literal strings, literals can be exact numeric values (long types) and approximate numeric values (double types). Exact numeric literal values are expressed using the Java integer literal syntax (321, -8932, +22). Approximate numeric literal values are expressed using Java floating-point literal syntax in scientific (5E3, -8.932E5) or decimal (5.234, 38282.2) notation. Boolean literal values use TRUE and FALSE.
The WHERE Clause and Operator Precedence
The WHERE clause is composed of conditional expressions that reduce the scope of the query and limit the number of items selected. Several conditional and logical operators can be used in expressions; they are listed here in order of precedence:
The WHERE Clause and Arithmetic Operators
The arithmetic operators allow a query to perform arithmetic in the process of doing a comparison. Arithmetic operators can be used only in the WHERE clause, not in the SELECT clause.
The following JPA QL statement returns references to all the Employees who have an ID less than 100:
The rules applied to arithmetic operations are the same as those used in the Java programming language, where numbers are widened, or promoted, in the process of performing a calculation. For example, multiplying a double and an int value requires that the int first be promoted to a double value. (The result will always be that of the widest type used in the calculation, so multiplying an int and a double results in a double value.)
String, boolean, and entity object types cannot be used in arithmetic operations. For example, using the addition operator with two String values is considered an illegal operation. There is a special function for concatenating String values, covered in “Functional expressions in the WHERE clause” on page 225.
The WHERE Clause and Logical Operators
Logical operators such as AND, OR, and NOT operate the same way in JPA QL as their corresponding logical operators in SQL.
Logical operators evaluate only Boolean expressions, so each operand (i.e., each side of the expression) must evaluate to true, false, or NULL. Logical operators have the lowest precedence so that all the expressions can be evaluated before they are applied.
The AND and OR operators don’t behave like their Java language counterparts, && and ||. JPA QL does not specify whether the righthand operands are evaluated conditionally. For example, the && operator in Java evaluates its righthand operand only if the lefthand operand is true. Similarly, the || logical operator evaluates the righthand operand only if the lefthand operand is false. We can’t make the same assumption for the AND and OR operators in JPA QL. Whether these operators evaluate righthand operands depends on the native query language into which the statements are translated. It’s best to assume that both operands are evaluated on all logical operators.
NOT simply reverses the Boolean result of its operand; expressions that evaluate to the Boolean value of true become false, and vice versa
The WHERE Clause and Comparison Symbols
Comparison operators, which use the symbols =, >, >=, <, <=, and <> should be familiar to you. Only the = and <> (equals and not equals) operators may be used on boolean and entity object identifiers. The greater-than and less-than symbols (>, >=, <, <=) can be used on numeric values as well as strings. However, the semantics of these operations are not defined by the Java Persistence specification. Is character case (upper or lower) important? Does leading and trailing whitespace matter? Issues like these affect the ordering of string values. In order for JPA QL to maintain its status as an abstraction of native query languages, it cannot dictate String ordering, because native query languages may have very different ordering rules. In fact, even different relational database vendors vary on the question of String ordering, which makes it all but impossible to standardize ordering, even for SQL “compliant” databases.
Of course, this is all academic if you plan on using the same database well into the future. In such a case, the best thing to do is to examine the documentation for the database you are using to find out how it orders strings in comparisons. This tells you exactly how your JPA QL comparisons will work.
The WHERE Clause and Equality Semantics
Although it is legal to compare an exact numeric value (short, int, long) to an approximate numeric value (double, float), all other equality comparisons must compare the same types. You cannot, for example, compare a String value of 123 to the Integer literal 123. However, you can compare two String types for equality.
You can compare numeric values for which the rules of numeric promotion apply. For example, a short may be compared to an int, an int to a long, etc. Java Persistence also states that primitives may be compared to primitive wrapper types—the rules of numeric promotion apply.
In older versions of the spec, String type comparisons had to match exactly, character for character. EJB 2.1 dropped this requirement, making the evaluation of equality between String types more ambiguous. This continued in Java Persistence. Again, this ambiguity arises from the differences between kinds of databases (relational versus object-oriented versus file), as well as differences between vendors of relational databases. Consult your vendor’s documentation to determine exactly how String equality comparisons are evaluated.
You can also compare entity objects for equality, but these too must be of the same type. To be more specific, they must both be entity object references to beans from the same deployment. Once it’s determined that the bean is the correct type, the actual comparison is performed on the beans’ primary keys. If they have the same primary key, they are considered equal.
You may use java.util.Date objects in equality comparisons. See “Date Parameters” on page 209.
The WHERE Clause and BETWEEN
The BETWEEN clause is an inclusive operator specifying a range of values. In this example, we use it to select all Employees with an ID between 100 and 200: SELECT e FROM Employee AS e WHERE e.id BETWEEN 100 AND 200
The BETWEEN clause may be used only on numeric primitives (byte, short, int, long, double, float) and their corresponding java.lang.Number types (Byte, Short, Integer, etc.). It cannot be used on String, boolean, or entity object references.
Using the NOT logical operator in conjunction with BETWEEN excludes the range specified. For example, the following JPA QL statement selects all the Employees that have an ID less than 100 or greater than 200:
The net effect of this query is the same as if it had been executed with comparison symbols:
The WHERE Clause and IN
The IN conditional operator used in the WHERE clause is not the same as the IN operator used in the FROM clause (that’s why the JOIN keyword in the FROM clause should be preferred over the IN keyword for collection navigation). In the WHERE clause, IN tests for membership in a list of literal values. For example, the following JPA QL statement uses the IN operator to select all the customers who reside in a specific set of states:
Applying the NOT operator to this expression reverses the selection, excluding all customers who reside in the list of states:
The IN operator can also be used with input parameters. For example, the following query selects all the parameters:
If the field tested is null, the value of the expression is “unknown,” which means it cannot be predicted.
The IN operator can be used with operands that evaluate to either string or numeric values. For example, the following JPA QL fragment uses the IN operator to select values 1, 3, 5, and 7:
In this case, the input parameters (?1, ?2, and ?3) are combined with parameters sent to the query during runtime execution.
The WHERE Clause and IS NULL
The IS NULL comparison operator allows you to test whether a path expression is null. For example, the following EJB QL statement selects all the Employees who do not have a manager (those who are self-managing):
Using the NOT logical operator, we can reverse the results of this query, selecting all Employees who report to a manager:
Path expressions are composed using “inner join” semantics. If an entity has a null relationship field, any query that uses that field as part of a path expression eliminates that entity from consideration. For example, if the Employee entity representing “John Smith” has a null value for its address relationship field, then the “John Smith” Employee entity won’t be included in the result set for the following query:
This seems obvious at first, but stating it explicitly helps eliminate much of the ambiguity associated with null relationship fields.
The NULL comparison operator can also be used to test input parameters. In this case, NULL is usually combined with the NOT operator to ensure that an input parameter is not a null value. For example, this query can be used to test for null input parameters. The JPA QL statement first checks that the city and state input parameters are not null and then uses them in comparison operations:
In this case, if either of the input parameters is a null value, the query returns an empty List, avoiding the possibility of UNKNOWN results from null input parameters. Your Java code should do these null checks (input assertions) up front to avoid an unnecessary database round-trip.
If the results of a query include a null relationship or a basic field, the results must include null values. For example, the following query selects the addresses of customers whose name is “John Smith”:
If the Employee entity representing “John Smith” has a null value for its address relationship field, the previous query returns a List that includes a null value—the null represents the address relationship field of “John Smith”—in addition to a bunch of Address entity references. You can eliminate null values by including the NOT NULL operator in the query, as shown here:
The WHERE Clause and IS EMPTY
The IS EMPTY operator allows the query to test whether a collection-based relationship is empty. If a collection-based relationship field has no elements, it returns an empty Collection or Set.
Testing whether a collection-based relationship is empty has the same purpose as testing whether a single relationship field or basic field is null: it can be used to limit the scope of the query and items selected. For example, the following query selects all the Employees who have no Phones:
The NOT operator reverses the result of IS EMPTY. The following query selects all the Employees who have at least one Phone:
Applying the NOT operator to MEMBER OF has the reverse effect, selecting all the customers on which the specified customer does not have a team:
The WHERE Clause and MEMBER OF
The MEMBER OF operator is a powerful tool for determining whether an entity is a member of a specific collection-based relationship. The following query determines whether a particular Employee entity (specified by the input parameter) is a member of any of the Team/Employee relationships:
Checking whether an entity is a member of an empty collection always returns false.
The WHERE Clause and LIKE
The LIKE comparison operator allows the query to select String type fields that match a specified pattern. For example, the following JPA QL statement selects all the customers with hyphenated names, like and “Berners-Lee”: SELECT OBJECT( e ) FROM Employee AS e WHERE e.name LIKE ‘%-%’
You can use two special characters when establishing a comparison pattern: % (percent) stands for any sequence of characters, and _ (underscore) stands for any single character. You can use these characters at any location within a string pattern. If a % or _ actually occurs in the string, you can escape it with the \ (backslash) character. The NOT logical operator reverses the evaluation so that matching patterns are excluded. The following examples show how the LIKE clause evaluates String type fields:
The LIKE operator can also be used with input parameters:
Functional Expressions
JPA QL has numerous functions that you can use to process strings and numeric values
Functional expressions in the WHERE clause
JPA QL has seven functional expressions that allow for simple String manipulation and three functional expressions for basic numeric operations. The String functions are:
The start and length parameters indicate positions in a String as integer values. You can use these expressions in the WHERE clause to refine the scope of the items selected. Here’s how the LOCATE and LENGTH functions might be used:
This statement selects all the employees with somewhere in their last names but specifies that the names must be longer than six characters. Therefore, “Ares” would evaluate to true, but would return false because it has only six characters.
The arithmetic functions in JPA QL may be applied to primitive as well as corresponding primitive wrapper types:
Functions returning dates and times
JPA QL has three functions that can return you the current date, time, and timestamp: CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP. Here’s an example of searching for employees who joined the company on the current date:
Aggregate functions in the SELECT clause
Aggregate functions are used with queries that return a collection of values. They are fairly simple to understand and can be handy, especially the COUNT() function.
COUNT (identifier or path expression). This function returns the number of items in the query’s final result set. The return type is a java.lang.Long, depending on whether it is the return type of the query method. For example, the following query provides a count of all the customers who live in Wisconsin:
The COUNT() function can be used with an identifier, in which case it always counts entities, or with path expressions, in which case it counts fields. For example, the following statement provides a count of all the zip codes that start with the numbers 554:
In some cases, queries that count a path expression have a corresponding query that can be used to count an identifier. For example, the result of the following query, which counts Customers instead of the zip field, is equivalent to the previous query:
MAX( path expression), MIN( path expression). These functions can be used to find the largest or smallest value from a collection of any type of field. They cannot be used with identifiers or paths that terminate in a relationship field. The result type will be the type of field that is being evaluated. For example, the following query returns the highest price paid for an order:
The MAX() and MIN() functions can be applied to any valid value, including primitive types, strings, and even serializable objects. The result of applying the MAX() and MIN() functions to serializable objects is not specified, because there is no standard way to determine which serializable object is greater than or less than another one.
The result of applying the MAX() and MIN() functions to a String field depends on the underlying data store. This has to do with the inherent problems associated with String type comparisons.
AVG( numeric ), SUM( numeric). The AVG() and SUM() functions can be applied only to path expressions that terminate in a numeric primitive field (byte, long, float, etc.) or in one of their corresponding numeric wrappers (Byte, Long, Float, etc.). The result of a query that uses the SUM() function has the same type as the numeric type it’s evaluating. The result type of the AVG() function is a java.lang.Double, depending on whether it is used in the return type of the SELECT method.
For example, the following query uses the SUM() function to get the total amount for all orders:
DISTINCT, nulls, and empty arguments. The DISTINCT operator can be used with any of the aggregate functions to eliminate duplicate values. The following query uses the DISTINCT operator to count the number of different zip codes that match the pattern specified:
The DISTINCT operator first eliminates duplicate zip codes. If 100 customers live in the same area with the same zip code, their zip code is counted only once. After the duplicates have been eliminated, the COUNT() function counts the number of items left.
Any field with a null value is automatically eliminated from the result set operated on by the aggregate functions. The COUNT() function also ignores values with null values. The aggregate functions AVG(), SUM(), MAX(), and MIN() return null when evaluating an empty collection.
The COUNT() function returns 0 (zero) when the argument it evaluates is an empty collection. If the following query is evaluated on an order with no line items, the result is 0 (zero) because the argument is an empty collection:
The ORDER BY Clause
The ORDER BY clause allows you to specify the order of the entities in the collection returned by a query. The semantics of the ORDER BY clause are basically the same as in SQL. For example, we can construct a simple query that uses the ORDER BY clause to return an alphabetical list of all employees:
This will return a Collection of Employee entities in alphabetical order by name.
You can use the ORDER BY clause with or without the WHERE clause. For example, we can refine the previous query by listing only those U.S. employees who reside in Boston:
The default order of an item listed in the ORDER BY clause is always ascending, which means that the lesser values are listed first and the greater values last. You can explicitly specify the order as ascending or descending by using the keywords ASC and DESC. The default is ASC. Null elements will be placed either on top or at the bottom of the query result, depending on the underlying database. Here’s a statement that lists customers in reverse (descending) order:
You can specify multiple order-by items. For example, you can sort customers by lastName in ascending order and firstName in descending order:
If you have five Customer entities with the lastName equal to Brooks, this query sorts the results as follows:
Although the fields used in the ORDER BY clause must be basic fields, the value selected can be an entity identifier, a relationship field, or a basic field. For example, the following query returns an ordered list of all zip codes:
The following query returns all the Address entities for customers named Smith, ordered by their zip code:
You must be careful which basic fields you use in the ORDER BY clause. If the query selects a collection of entities, then the ORDER BY clause can be used with only basic fields of the entity type that is selected. The following query is illegal, because the basic field used in the ORDER BY clause is not a field of the entity type selected:
Because the city field is not a direct field of the Employee entity, you cannot use it in the ORDER BY clause.
A similar restriction applies to results. The field used in the ORDER BY clause must also be in the SELECT clause. The following query is illegal because the field identified in the SELECT clause is not the same as the one used in the ORDER BY clause:
As an example of DELETE, say that we want to remove all orders placed by john:
In the previous query, we wanted a list of all the cities ordered by their state. Unfortunately, this is illegal. You can’t order by the state field if you are not selecting the state field.
Bulk UPDATE and DELETE
Java Persistence has the ability to perform bulk UPDATE and DELETE operations. This can save you a lot of typing. For example, let’s say we want to give a $10 credit across the board to any customer. We can do the following bulk UPDATE:
Be very careful how you use bulk UPDATE and DELETE. It is possible, depending on the vendor implementation, to create inconsistencies between the database and entities that are already being managed by the current persistence context. Vendor implementations are required only to execute the update or delete directly on the database. They do not have to modify the state of any currently managed entity. For this reason, it is recommended that you do these operations within their own transaction or at the beginning of a transaction (before any entities are accessed that might be affected by these bulk operations). Alternatively, executing EntityManager.flush() and EntityManager.clear() before executing a bulk operation will keep you safe.
Native Queries
JPA QL is a very rich syntax and should meet most of your querying needs. Sometimes, though, you want to take advantage of certain proprietary capabilities that are available only on a specific vendor’s database.
The entity manager service provides a way to create native SQL queries and map them to your objects. Native queries can return entities, column values, or a combination of the two. The EntityManager interface has three methods for creating native queries: one for returning scalar values, one for returning one entity type, and one for defining a complex result set that can map to a mix of multiple entities and scalar values.
You can always get the underlying JDBC connection through a javax.sql.DataSource injected by the @Resource and execute any SQL statement you need. Be aware that your changes will not be reflected in the current persistence context.
Scalar Native Queries
Query createNativeQuery(String sql)
This creates a native query that returns scalar results. It takes one parameter: your native SQL. It executes as is and returns the result set in the same form as JPA QL returns scalar values.
Simple Entity Native Queries
Query createNativeQuery(String sql, Class entityClass)
A simple entity native query takes an SQL statement and implicitly maps it to one entity based on the mapping metadata you declared for that entity. It expects that the columns returned in the result set of the native query will match perfectly with the entity’s O/R mapping. The entity that the native SQL query maps to is determined by the entity Class parameter:
All the properties of the entities must be pulled.
Complex Native Queries
This entity manager method allows you to have complex mappings for your native SQL. You can return multiple entities and scalar column values at the same time. The map pingName parameter references a declared @javax.persistence.SqlResultSetMapping. This annotation is used to define how the native SQL results hook back into your O/R model. If your returned column names don’t match the parallel annotated property mapping, you can provide a field-to-column mapping for them using
Let’s create an example to show how this would work. Native queries with multiple entities
First, let’s create a native query that returns multiple entity types—a Customer and its CreditCard:
Because the result set returns multiple entity types, we must define an @SqlResultSet Mapping. This annotation can be placed on an entity class or method. The entities() attribute is set to an array of @EntityResult annotations. Each @EntityResult annotation specifies the entities that will be returned by the native SQL query.
The @javax.persistence.FieldResult annotation is used to explicitly map columns in the query with properties of an entity. The name() attribute of @FieldResult identifies the entity bean’s property, and the column() attribute identifies the result set column returned by the native query.
In this example, we do not need to specify any @FieldResults for Customer, as the native query pulls in each column for this entity. However, since we are querying only the ID and number columns of the CreditCard entity, an @FieldResult annotation should be specified. In the @EntityResult annotation for CreditCard, the fields() attribute defines what CreditCard properties each queried column maps to. Because the Customer and CreditCard primary-key columns have the same name, the SQL query needs to distinguish that they are different. The cc.id As CC_ID SQL fragment performs this identification.
Named Queries
Java Persistence provides a mechanism so that you can predefine JPA QL or native SQL queries and reference them by name when creating a query. You would want to pre-declare queries for the same reason you create String constant variables in Java: to reuse them in multiple situations. If you predefine your queries in one place, you have an easy way to fine-tune or modify them as time goes on. The @javax.persistence.Named Query annotation is used for predefining JPA QL queries:
You use the @javax.persistence.NamedQueries annotation when you are declaring more than one query on a class or package. The @javax.persistence.QueryHint annotation declares vendor-specific hints. These hints work in the same way as the Query.setHint () method described earlier in this topic. Here’s an example:
This example declares a JPA QL query on the ProductOrder entity bean class. You can then reference these declarations in the EntityManager.createNamedQuery() method:
Named Native Queries
The @javax.persistence.NamedNativeQuery annotation is used for predefining native SQL queries:
The resultClass() attribute is for when you have a native query that returns only one entity type (see “Native Queries” on page 230). The resultSetMapping() attribute must resolve to a predeclared @SqlResultSetMapping. Both attributes are optional, but you must declare at least one of them. Here is an example of predeclaring an @NamedNative Query:
You can then reference this declaration in the EntityManager.createNamedQuery() method:
