Java Persistence Query Language (JPQL), the criteria API, and native SQL.

Introducing JPQL

JPQL is an extension of EJB QL, the query language of EJB 2.

The use of JPQL will make the migration of EJB 2 entity beans to EJB 3 persistence easier.

How is JPQL different from SQL?

JPQL operates on classes and objects (entities) in the Java space. SQL operates on tables, columns, and rows in the database space. Although JPQL and SQL look sim- ilar, they operate in two very different worlds.

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

Statement types

Statement types supported by JPQL

Statement type	Description
Select	Retrieves entities or entity-related data
Update	Updates one or more entities
Delete	Deletes one or more entities

Defining and using `SELECT`

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

Defining `UPDATE` and `DELETE`

Using `UPDATE`

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

Using `DELETE`

DELETE entityName indentifierVariable
WHERE where_clause

`FROM` clause

FROM Category c

Indentifying the query domain: Naming and Entity

Identifier variables

FROM entityName [AS] identificationVariable

JPQL keywords reserved by the specification. You’re not allowed to give any of your variables these names.

Types	Reserved words
Statements and clauses	SELECT, UPDATE, DELETE, FROM, WHERE, GROUP, HAVING, ORDER, BY, ASC, DESC
Joins	JOIN, OUTER, INNER, LEFT, FETCH
Conditions and operators	DISTINCT, OBJECT, NULL, TRUE, FALSE, NOT, AND, OR, BETWEEN, LIKE, IN, AS, UNKNOWN, EMPTY, MEMBER, OF, IS, NEW, EXISTS, ALL, ANY, SOME
Functions	AVG, MAX, MIN, SUM, COUNT, MOD, UPPER, LOWER, TRIM,POSITION, CHARACTER_LENGTH, CHAR_LENGTH, BIT_LENGTH, CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP

What's a path expression

SELECT distinct c
FROM Category c
WHERE c.items isn't EMPTY

c.user.firstName

c.user.contactDetails.email

you can’t navigate through the collection-value path expressions to access a persistence or association field as in the following example:

c.items.itemName or c.items.seller

Filtering with `WHERE`

Passing parameters: positional and named

JPQL supports two types of parameters: posi- tional and named.

Conditional expressions and operators

Operator types supported by JPQL

Operator type	Operator
Navigational	.
Unary sign	+,-
Arithmetic	*, /, +, -
Relational	=, >, >=, <, <=, <>, [NOT] BETWEEN, [NOT] LIKE, [NOT] IN, IS [NOT] NULL, IS [NOT] EMPTY, [NOT] MEMBER [OF]
Logical	NOT, AND, OR

Dealing with null valus and empty collections.

Truth table of Boolean value compared with null

Expression 1 value	Boolean operator	Expression 2 value	Result
TRUE	AND	null	UNKNOWN
FALSE	AND	null	FALSE
NULL	AND	null	UNKNOWN
TRUE	OR	null	TRUE
NULL	OR	null	UNKNOWN
FALSE	OR	null	UNKNOWN
	NOT	null	UNKNOWN

Checking for the existence of an entity in a collection

You can use the MEMBER OF operator to test whether an identifier variable, a single- value path expression, or an input parameter exists in a collection-value path expres- sion.

entity_expression [NOT] MEMBER [OF] collection_value_path_expression

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

WHERE :item MEMBER OF c.items

Working with JPQL functions

JPQL provides several built-in functions for performing different kinds of operations.

String functions
Arithmetic functions
Datetime functions

String functions

JPQL string functions

String functions	Description
`CONCAT(string1, string2)`	Returns the value of concatenating two strings or literals together.
`SUBSTRING(string, position, length)`	Returns the substring starting at position that’s length long.
`TRIM([LEADING	TRAILING	BOTH] [trim_character] FROM] string_to_trimmed)`	Trims the specified character to a new length. The trimming can be LEADING, TRAILING, or from BOTH ends. If no trim_character is specified, then a blank space is assumed.
`LOWER(string)`	Returns the string after converting to lowercase.
`UPPER(string)`	Returns the string after converting to uppercase.
`LENGTH(string)`	Returns the length of a string.
`LOCATE(searchString, stringToBeSearched[initialPosition])`	Returns the position of a given string within another string. The search starts at position 1 if initialPosition isn’t specified.

Arithmetic functions

JPQL arithmetic functions

Arithmetic functions	Description
`ABS(simple_arithmetic_expression)`	Returns the absolute value of simple_arithmetic_expression
`SQRT(simple_arithmetic_expression)`	Returns the square root value of simple_ arithmetic_expression as a double
`MOD(num, div)`	Returns the result of executing the modulus operation for num, div
`SIZE(collection_value_path_expression)`	Returns the number of items in a collection

Temporal functions

JPQL temporal functions

Temporal functions	Description
`CURRENT_DATE`	Returns current date
`CURRENT_TIME`	Returns current time
`CURRENT_TIMESTAMP`	Returns current timestamp

`SELECT` clause

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

Using a constructor expression in a `Select` clause

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

Using aggregates

Aggregate functions supported by JPQL

Aggregate functions	Description	Return type
`AVG`	Returns the average value of all values of the field it’s applied to	Double
`COUNT`	Returns the number of results returned by the query	Long
`MAX`	Returns the maximum value of the field it’s applied to	Depends on the type of the persistence field
`MIN`	Returns the minimum value of the field it’s applied to	Depends on the type of the persistence field
`SUM`	Returns the sum of all values on the field it’s applied to	May return either Long or Double

Ordering results

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

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

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

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

Joining entities

[INNER] JOIN join_association_path_expression [AS]
  identification_variable
  
SELECT u
FROM User u INNER JOIN u.Category c
WHERE u.userId LIKE ?1


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

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

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

Bulk updates and deletes

Criteria queries

Starting with Java EE 6, the criteria queries were introduced to provide a type-safe mechanism for creating queries.

A type-safe API means that you’ll build queries using real Java objects to represent the SQL statement.
Using the type-safe API, it can be built entirely in code with no hardcoded strings. The queries are guaranteed to be syntactically correct, although they still may contain logical errors.

Meta-model API

The steps for using a meta-model are as follows:

1. Annotate your POJOs using the JPA annotations.

2. Compile the code with the meta-model processor as part of the compile process.

3. Code criteria queries using the generated classes.

4. Repeat as the model changes

At the heart of the meta-model APIs is the Metamodel interface. You acquire a reference to the Metamodel implementation by calling getMetamodel() on the EntityManager .

Generated code

`CriteriaBuilder`

The CriteriaBuilder is at the heart of the JPA criteria API. It’s responsible for creating criteria queries, component selections, expressions, predicates, and orderings.

Create methods on `CriteriaBuilder`

Create method	Description
`createQuery()`	Creates a new criteria object
`createQuery(java.lang.Class<T> resultClass)`	Creates a new criteria object with a specific return type
`createTupleQuery()`	Creates a new criteria object that will return a tuple as its result
`createCriteriaDelete( Class<T> targetEntity)`	Creates a new criteria object for performing bulk delete operations
`createCriteriaUpdate( Class<T> targetEntity)`	Creates a new criteria object that will perform a bulk update operation

`CriteriaBuilder` methods grouped by type

Type	Methods
Expression	abs, all, any, avg, coalesce, concat, construct, count, countDistinct, currentDate, currentTime, currentTimestamp, diff, function, greatest, keys, least, length, literal, locate, lower, max, min, mod, neg, nullif, nullLiteral, parameter, prod, quot, selectCase, size, some, sqrt, substring, sum, sumAsDouble, sumAsLong, toBigDecimal, toBigInteger, toDouble, toFloat, toInteger, toLong, toString, trim, upper, values
Selection	array, construct, tuple
Ordering	asc, desc
Predicate	and, between, conjunction, disjunction, equal, exists, ge, greaterThan, greaterThanOrEqualTo, gt, in, isEmpty, isFalse, isMember, isNotEmpty, isNotMember, isNotNull, isNull, isTrue, le, lessThan, lessThanOrEqualTo, like, lt, not, notEqual, notLike, or

`CriteriaQuery`

Core `CriteriaQuery` methods

Method	Parameter type	Description
`groupBy`	Expression	Constructs the SQL groupBy construct
`having`	Expression	Constructs the SQL HAVING construct
`multiselect`	Selection	Creates a query with multiple selections
`orderBy`	Order	Constructs the SQL orderBy statement
`select`	Selection	Creates a single selection
`where`	Predicate	Creates the SQL WHERE clause

Query root

The code in this listing will produce the following SQL query:

SELECT ITEM_NAME FROM ITEMS

Expressions

The entire listing results in the following query going to the database:

SELECT ITEM_ID, BID_END_DATE, BID_START_DATE, CREATEDDATE, DESCRIPTION, INITIAL_PRICE, ITEM_NAME, PICTURE, STARRATING, SELLER_ID FROM ITEMS WHERE (CREATEDDATE BETWEEN ? AND ?)

Native queries

JPQL returns an entity, or set, of scalar values, but a SQL query returns database records. Therefore, a SQL query may return more than entities, because you may join multiple tables in your SQL.

Using dynamic queries with native SQL

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

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

JPA allows a @SqlResultSetMapping to be used with the createNativeQuery method instead of passing an entity class. A @SqlResultSetMapping may be mapped to one or more entities. For example, if you want to create a SqlResultSetMapping for the User entity and use it in your native query, then you can use the @SqlResultSetMapping annotation as follows:

@SqlResultSetMapping(name = "UserResults",
  entities = @EntityResult(
    entityClass =  actionbazaar.persistence.User.class))
    
Query q = em.createNativeQuery("SELECT user_id, first_name, last_name "
          + " FROM users WHERE user_id IN (SELECT seller_id FROM "
          + "items GROUP BY seller_id HAVING COUNT(*) > 1)",
            "UserResults");
return q.getResultList();

Using a named native SQL query

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

First step is to define the named native query in the User entity:

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

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

@NamedNativeQuery(
  name = "findUserWithMoreItems",
  query = "SELECT user_id , first_name , last_name,
                birth_date
          FROM users
          WHERE user_id IN
            (SELECT seller_id
             FROM items
             GROUP BY seller_id
             HAVING COUNT(*) > ?)",
  resultSetMapping = "UserResults")

you’ll execute the named native query findUserWithMoreItems (which you defined earlier in a session bean method):

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

Using stored procedures

A stored procedure is basically a function that takes a set of parameters and can optionally return data. The data can be returned from the function or passed back via the parameters to the function. Parameters can be IN, OUT , or INOUT—IN being a parameter passed in, OUT being a parameter used to return a value, and INOUT being a parameter that’s consumed and also returns a value.

Faster performance, data logging, avoiding network traffic on complex queries, encapsulating business logic, handling permissions, and more.

With JPA’s stored procedure support, you work with a javax.persistence.StoredProcedureQuery object. This class extends the javax.persistence.Query interface that you’ve seen throughout this chapter. To create a StoredProcedureQuery , you use one of the three methods on the EntityManager:

StoredProcedureQuery createStoredProcedureQuery(String procedureName);
StoredProcedureQuery createStoredProcedureQuery(
    String procedureName, Class... resultClasses);
StoredProcedureQuery createStoredProcedureQuery(
    String procedureName, String... resultSetMappings);

Stored procedure for counting the bids for a particular user ID (pgSQL)

Invoking a stored procedure using JPA

EJB 3 In Action - Using EJB with JPA and CDI - JPQL