2.10.1.1. Query Basics
SELECT x FROM Magazine x

The preceding is a simple JPQL query for all Magazine entities.

public Query createQuery(String jpql);

The EntityManager.createQuery method creates a Query instance from a given JPQL string.

public List getResultList();

Invoking Query.getResultList executes the query and returns a List containing the matching objects. The following example executes our Magazine query above:

EntityManager em = ...
Query q = em.createQuery("SELECT x FROM Magazine x");
List<Magazine> results = (List<Magazine>) q.getResultList();

A JPQL query has an internal namespace declared in the from clause of the query. Arbitrary identifiers are assigned to entities so that they can be referenced elsewhere in the query. In the query example above, the identifier x is assigned to the entity Magazine.

Note

The as keyword can optionally be used when declaring identifiers in the from clause. SELECT x FROM Magazine x and SELECT x FROM Magazine AS x are synonymous.

Following the select clause of the query is the object or objects that the query returns. In the case of the query above, the query’s result list will contain instances of the Magazine class.

Note

When selecting entities, you can optionally use the keyword object. The clauses select x and SELECT OBJECT(x) are synonymous.

The optional where clause places criteria on matching results. For example:

SELECT x FROM Magazine x WHERE x.title = 'JDJ'

Keywords in JPQL expressions are case-insensitive, but entity, identifier, and member names are not. For example, the expression above could also be expressed as:

select x from Magazine x where x.title = 'JDJ'

But it could not be expressed as:

SELECT x FROM Magazine x WHERE x.TITLE = 'JDJ'

As with the select clause, alias names in the where clause are resolved to the entity declared in the from clause. The query above could be described in English as "for all Magazine instances x, return a list of every x such that x's title field is equal to 'JDJ'".

JPQL uses SQL-like syntax for query criteria. The and and or logical operators chain multiple criteria together:

SELECT x FROM Magazine x WHERE x.title = 'JDJ' OR x.title = 'JavaPro'

The = operator tests for equality. <> tests for inequality. JPQL also supports the following arithmetic operators for numeric comparisons: >, >=, <, ⇐. For example:

SELECT x FROM Magazine x WHERE x.price > 3.00 AND x.price <= 5.00

This query returns all magazines whose price is greater than 3.00 and less than or equal to 5.00.

SELECT x FROM Magazine x WHERE x.price <> 3.00

This query returns all Magazines whose price is not equal to 3.00.

You can group expressions together using parentheses in order to specify how they are evaluated. This is similar to how parentheses are used in Java. For example:

SELECT x FROM Magazine x WHERE (x.price > 3.00 AND x.price <= 5.00) OR x.price < 7.00

This expression would match magazines whose price is less than 7.00. Alternately:

SELECT x FROM Magazine x WHERE x.price > 3.00 AND (x.price <= 5.00 OR x.price < 7.00)

This expression would match magazines whose price is 4.00, 5.00 or 6.00, but not 1.00, 2.00 or 3.00.

JPQL also includes the following conditionals:

  • [NOT] BETWEEN: Shorthand for expressing that a value falls between two other values. The following two statements are synonymous:

    SELECT x FROM Magazine x WHERE x.price >= 3.00 AND x.price <= 5.00
    SELECT x FROM Magazine x WHERE x.price BETWEEN 3.00 AND 5.00
  • [NOT] LIKE: Performs a string comparison with wildcard support. The special character '_' in the parameter means to match any single character, and the special character '%' means to match any sequence of characters. The following statement matches title fields "JDJ" and "JavaPro", but not "IT Insider":

    SELECT x FROM Magazine x WHERE x.title LIKE 'J%'

    The following statement matches the title field "JDJ" but not "JavaPro":

    SELECT x FROM Magazine x WHERE x.title LIKE 'J__'
  • [NOT] IN: Specifies that the member must be equal to one element of the provided list. The following two statements are synonymous:

    SELECT x FROM Magazine x WHERE x.title IN ('JDJ', 'JavaPro', 'IT Insider')
    SELECT x FROM Magazine x WHERE x.title = 'JDJ' OR x.title = 'JavaPro' OR x.title = 'IT Insider'
  • IS [NOT] EMPTY: Specifies that the collection field holds no elements. For example:

    SELECT x FROM Magazine x WHERE x.articles is empty

    This statement will return all magazines whose articles member contains no elements.

  • IS [NOT] NULL: Specifies that the field is equal to null. For example:

    SELECT x FROM Magazine x WHERE x.publisher is null

    This statement will return all Magazine instances whose "publisher" field is set to null.

  • NOT: Negates the contained expression. For example, the following two statements are synonymous:

    SELECT x FROM Magazine x WHERE NOT(x.price = 10.0)
    SELECT x FROM Magazine x WHERE x.price <> 10.0