The Java Persistence Query Language (JPQL) is a platform-independent object-oriented query language defined by
JPA. JPQL is similar to SQL, but operates on objects, attributes and relationships instead of tables and columns.
JPQL is used to define searches against persistent entities independent of the mechanism used to store those entities.
As such, JPQL is “portable”, and not constrained to any particular data store (although in some cases it does get constrained).
JPQL is an extension of the Enterprise JavaBeans query language, EJB QL, adding operations such as bulk deletes and
updates, join operations, aggregates, projections, and subqueries. Furthermore, JPQL queries can be declared statically
in metadata, or can be dynamically built in code
JPQL is not portable when it comes to GROUP BY clause. Let’s say we have query that reads everyone who has lived
in Washington. It is common in the U.S., however, that cities share common name. For example, there are more than 80
locations whose names are “Washington”. When a person move from Washington, Maine to Washington, Massachusetts, the
Address table will have 2 rows with the same combinations of ssn and city columns.
1
SELECT person
2
FROM Person person LEFT JOINAddressaddressON person.city = address.city
The Java Persistence query language is a typed language, and every expression has a type. The type of an expression
is derived from the structure of the expression, the abstract schema types of the identification variable
declarations, the types to which the persistent fields and relationships evaluate, and the types of literals. The
abstract schema type of an entity is derived from the entity class and the metadata information provided by Java
language annotations or in the XML descriptor.
Informally, the abstract schema type of an entity can be characterized as follows:
For every persistent field or get accessor method (for a persistent property) of the entity class, there is a
field (“state-field”) whose abstract schema type corresponds to that of the field or the result type of the
accessor method.
For every persistent relationship field or get accessor method (for a persistent relationship property) of the
entity class, there is a field (“association-field”) whose type is the abstract schema type of the related entity
(or, if the relationship is a one-to-many or many-to-many, a collection of such). Abstract schema types are
specific to the query language data model. The persistence provider is not required to implement or otherwise
materialize an abstract schema type. The domain of a query consists of the abstract schema types of all
entities that are defined in the same persistence unit. The domain of a query may be restricted by the
navigability of the relationships of the entity on which it is based. The association-fields of an entity’s
abstract schema type determine navigability. Using the association-fields and their values, a query can select
related entities and use their abstract schema types in the query.
Entities are designated in query strings by their entity names. The entity name is defined by the name element of
the Entity annotation (or the entity-name XML descriptor element), and defaults to the unqualified name of the
entity class. Entity names are scoped within the persistence unit and must be unique within the persistence unit.
This example assumes that the application developer provides several entity classes representing magazines,
publishers, authors, and articles. The abstract schema types for these entities are Magazine, Publisher,
Author, and Article.
The entity Publisher has a one-to-many relationships with Magazine. There is also a one-to-many relationship
between Magazine and Article. The entity Article is related to Author in a one-to-one relationship.
Queries to select magazines can be defined by navigating over the association-fields and state-fields defined by
Magazine and Author. A query to find all magazines that have unpublished articles is as follows:
1
SELECT DISTINCT mag
2
FROM Magazine AS mag
3
JOIN mag.articles AS art
4
WHERE art.published = FALSE
The next query navigates over the association-field authors of the abstract schema type Magazine to find
articles, and uses the state-field published of Article to select those magazines that have at least one
article that is
published. Although predefined reserved identifiers, such as DISTINCT, FROM, AS, JOIN, WHERE, and FALSE,
appear in upper case in this example, predefined reserved identifiers are case insensitive. The SELECT clause of this
example designates the return type of this query to be of type Magazine. Because the same persistence unit defines the
abstract persistence schemas of the related entities, the developer can also specify a query over articles that
utilizes the abstract schema type for products, and hence the state-fields and association-fields of both the abstract
schema types Magazine and Author. For example, if the abstract schema type Author has a state-field named
firstName, a query over articles can be specified using this state-field. Such a query might be to find all
magazines that have articles authored by someone with the first name “John”.
1
SELECT DISTINCT mag
2
FROM Magazine mag
3
JOIN mag.articles art JOIN art.author auth WHERE auth.firstName ='John'
Because Magazine is related to Author by means of the relationships between Magazine and Article and between
Article and Author, navigation using the association-fields authors and product is used to express the query.
This query is specified by using the abstract schema name Magazine, which designates the abstract schema type
over which the query ranges. The basis for the navigation is provided by the association-fields authors and product
of the abstract schema types Magazine and Article respectively.
The FROM clause of a query defines the domain of the query by declaring identification variables. An
identification variable is an identifier declared in the FROM clause of a query. The domain of the query may be
constrained by path expressions. Identification variables designate instances of a particular entity abstract
schema type. The FROM clause can contain multiple identification variable declarations separated by a comma (,):
from_clause ::= FROM identification_variable_declaration {, {identification_variable_declaration | collection_member_declaration}}*
An identifier is a character sequence of unlimited length. The character sequence must begin with a Java
identifier start character, and all other characters must be Java identifier part characters. An identifier start
character is any character for which the method Character.isJavaIdentifierStart returns true. This includes the
underscore (_) character and the dollar-sign ($) character. An identifier-part character is any character for
which the method Character.isJavaIdentifierPart returns true . The question-mark (?) character is reserved for
use by the Java Persistence query language. The following are reserved identifiers:
SELECT
FROM
WHERE
UPDATE
DELETE
JOIN
OUTER
INNER
LEFT
GROUP
BY
HAVING
FETCH
DISTINCT
OBJECT
NULL
TRUE
FALSE
NOT
AND
OR
BETWEEN
LIKE
IN
AS
UNKNOWN
EMPTY
MEMBER
OF
IS
AVG
MAX
MIN
SUM
COUNT
ORDER
BY
ASC
DESC
MOD
UPPER
LOWER
TRIM
POSITION
CHARACTER_LENGTH
CHAR_LENGTH
BIT_LENGTH
CURRENT_TIME
CURRENT_DATE
CURRENT_TIMESTAMP
NEW
EXISTS
ALL
ANY
SOME
Reserved identifiers are case insensitive. Reserved identifiers must not be used as identification variables. It is
recommended that other SQL reserved words not be used as identification variables in queries, as they may be used as
reserved identifiers in future releases of the specification.
An identification variable is a valid identifier declared in the FROM clause of a query. All identification
variables must be declared in the FROM clause. Identification variables cannot be declared in other clauses. An
identification variable must not be a reserved identifier or have the same name as any entity in the same
persistence unit: Identification variables are case insensitive. An identification variable evaluates to a value of
the type of the expression used in declaring the variable. For example, consider the previous query:
1
SELECT DISTINCT mag
2
FROM Magazine mag JOIN mag.articles art JOIN art.author auth
3
WHERE auth.firstName ='John'
In the FROM clause declaration mag.articles art, the identification variable art evaluates to any Article
value directly reachable from Magazine. The association-field articles is a collection of instances of the
abstract schema type Article and the identification variable art refers to an element of this collection. The
type of auth is the abstract schema type of Author. An identification variable ranges over the abstract schema
type of an entity. An identification variable designates an instance of an entity abstract schema type or an
element of a collection of entity abstract schema type instances. Identification variables are existentially
quantified in a query. An identification variable always designates a reference to a single value. It is declared
in one of three ways:
in a range variable declaration,
in a join clause, or
in a collection member declaration.
The identification variable declarations are evaluated from left to right in the FROM clause, and an
identification variable declaration can use the result of a preceding identification variable declaration of the
query string.
Range variable declarations allow the developer to designate a “root” for objects which may not be reachable by
navigation. In order to select values by comparing more than one instance of an entity abstract schema type, more
than one identification variable ranging over the abstract schema type is needed in the FROM clause.
The following query returns magazines whose prices are greater than the price of magazines published by “Adventure”
publishers. This example illustrates the use of two different identification variables in the FROM clause, both
of the abstract schema type Magazine. The SELECT clause of this query determines that it is the magazines with
prices greater than those of “Adventure” publisher’s that are returned.
1
SELECT DISTINCT mag1
2
FROM Magazine mag1, Magazine mag2
3
WHERE mag1.price > mag2.price AND mag2.publisher.name='Adventure'
An identification variable followed by the navigation operator (.) and a state-field or association-field is a
path expression. The type of the path expression is the type computed as the result of navigation; that is, the
type of the state-field or association-field to which the expression navigates. Depending on navigability, a path
expression that leads to an association-field may be further composed. Path expressions can be composed from other
path expressions if the original path expression evaluates to a single-valued type (not a collection) corresponding
to an association-field. Path-expression navigability is composed using “inner join” semantics. That is, if the
value of a non-terminal association-field in the path expression is null, the path is considered to have no
value, and does not participate in the determination of the result. The syntax for single-valued path expressions
and collection-valued path expressions is as follows:
A single_valued_association_field is designated by the name of an association-field in a one-to-one or many-to-one
relationship. The type of a single_valued_association_field and thus a single_valued_association_path_expression is
the abstract schema type of the related entity. A collection_valued_association_field is designated by the name of
an association-field in a one-to-many or a many-to-many relationship. The type of a
collection_valued_association_field is a collection of values of the abstract schema type of the related entity.
An embedded_class_state_field is designated by the name of an entity-state field that corresponds to an embedded
class. Navigation to a related entity results in a value of the related entity’s abstract schema type.
The evaluation of a path expression terminating in a state-field results in the abstract schema type corresponding
to the Java type designated by the state-field. It is syntactically illegal to compose a path expression from a path
expression that evaluates to a collection. For example, if mag designates Magazine, the path expression
mag.articles.author is illegal since navigation to authors results in a collection. This case should produce an
error when the query string is verified. To handle such a navigation, an identification variable must be declared
in the FROM clause to range over the elements of the articles collection. Another path expression must be used to
navigate over each such element in the WHERE clause of the query, as in the following query, which returns all
authors that have any articles in any magazines:
For example, the query below joins over the relationship between publishers and magazines. This type of join
typically equates to a join over a foreign key relationship in the database.
1
SELECT pub
2
FROM Publisher pub JOIN pub.magazines mag
3
WHERE pub.revenue >1000000
The keyword INNER may optionally be used:
1
SELECT pub
2
FROM Publisher pub INNER JOIN pub.magazines mag
3
WHERE pub.revenue >1000000
This is equivalent to the following query using the earlier IN construct. It selects those publishers with
revenue of over 1 million for which at least one magazine exists:
LEFT JOIN and LEFT OUTER JOIN are synonymous. They enable the
retrieval of a set of entities where matching values in the join condition may be absent. The syntax for a left
outer join is
1
LEFT [OUTER] JOIN join_association_path_expression [AS] identification_variable
For example:
1
SELECT pub
2
FROM Publisher pub LEFT JOIN pub.magazines mag
3
WHERE pub.revenue >1000000
The keyword OUTER may optionally be used:
1
SELECT pub
2
FROM Publisher pub LEFT OUTER JOIN pub.magazines mags
3
WHERE pub.revenue >1000000
An important use case for LEFT JOIN is in enabling the prefetching of related data items as a side effect of a
query. This is accomplished by specifying the LEFT JOIN as a FETCH JOIN.
A FETCH JOIN enables the fetching of an association as a side effect of the execution of a query. A FETCH JOIN
is specified over an entity and its related entities. The syntax for a fetch join is
1
fetch_join ::= [ LEFT [OUTER] | INNER ] JOINFETCH join_association_path_expression
The association referenced by the right side of the FETCH JOIN clause must be an association that belongs to an
entity that is returned as a result of the query. It is not permitted to specify an identification variable for the
entities referenced by the right side of the FETCH JOIN clause, and hence references to the implicitly fetched
entities cannot appear elsewhere in the query. The following query returns a set of magazines. As a side effect,
the associated articles for those magazines are also retrieved, even though they are not part of the explicit query
result. The persistent fields or properties of the articles that are eagerly fetched are fully initialized. The
initialization of the relationship properties of the articles that are retrieved is determined by the metadata for
the Article entity class.
1
SELECT mag
2
FROM Magazine mag LEFT JOINFETCH mag.articles
3
WHERE mag.id =1
A fetch join has the same join semantics as the corresponding inner or outer join, except that the related objects
specified on the right-hand side of the join operation are not returned in the query result or otherwise referenced
in the query. Hence, for example, if magazine id 1 has five articles, the above query returns five references to the
magazine 1 entity.
An identification variable declared by a collection_member_declaration ranges over values of a collection
obtained by navigation using a path expression. Such a path expression represents a navigation involving the
association-fields of an entity abstract schema type. Because a path expression can be based on another path
expression, the navigation can use the association-fields of related entities. An identification variable of a
collection member declaration is declared using a special operator, the reserved identifier IN. The argument to
the IN operator is a collection-valued path expression. The path expression evaluates to a collection type
specified as a result of navigation to a collection-valued association-field of an entity abstract schema type. The
syntax for declaring a collection member identification variable is as follows:
may equivalently be expressed as follows, using the IN operator:
1
SELECT DISTINCT mag
2
FROM Magazine mag, IN(mag.articles) art
3
WHERE art.author.lastName ='Grisham'
In this example, articles is the name of an association-field whose value is a collection of instances of the
abstract schema type Article. The identification variable art designates a member of this collection, a single
Article abstract schema type instance. In this example, mag is an identification variable of the abstract schema
type Magazine.
Java Persistence queries are automatically polymorphic. The FROM clause of a query designates not only instances
of the specific entity classes to which the query explicitly refers but of subclasses as well. The instances
returned by a query include instances of the subclasses that satisfy the query criteria.
The WHERE clause of a query consists of a conditional expression used to select objects or values that satisfy the
expression. The WHERE clause restricts the result of a select statement or the scope of an update or delete
operation. A WHERE clause is defined as follows:
1
where_clause ::=WHERE conditional_expression
The GROUP BY construct enables the aggregation of values according to the properties of an entity class. The
HAVING construct enables conditions to be specified that further restrict the query result as restrictions upon
the groups. The syntax of the HAVING clause is as follows:
The following sections describe the language constructs that can be used in a conditional expression of the WHERE
clause or HAVING clause. State-fields that are mapped in serialized form or as lobs may not be portably used in
conditional expressions
NOTE
The implementation is not expected to perform such query operations involving such fields in memory rather than in
the database.
A string literal is enclosed in single quotes — for example: ‘literal’. A string literal that includes a single
quote is represented by two single quotes — for example: ‘literal”s’. String literals in queries, like Java
String literals, use unicode character encoding. The use of Java escape notation is not supported in query string
literals. Exact numeric literals support the use of Java integer literal syntax as well as SQL exact numeric
literal syntax. Approximate literals support the use Java floating point literal syntax as well as SQL approximate
numeric literal syntax. Enum literals support the use of Java enum literal syntax. The enum class name must be
specified. Appropriate suffixes may be used to indicate the specific type of a numeric literal in accordance with
the Java Language Specification. The boolean literals are TRUE and FALSE. Although predefined reserved literals
appear in upper case, they are case insensitive.
All identification variables used in the WHERE or HAVING clause of a SELECT or DELETE statement must be
declared in the FROM clause, as described in JPQL Identification Variables. The
identification variables used in the WHERE clause of an UPDATE statement must be declared in the UPDATE
clause. Identification variables are existentially quantified in the WHERE and HAVING clause. This means that an
identification variable represents a member of a collection or an instance of an entity’s abstract schema type. An
identification variable never designates a collection in its entirety.
It is illegal to use a collection_valued_path_expression within a WHERE or HAVING clause as part of a
conditional expression except in an empty_collection_comparison_expression, in a collection_member_expression,
or as an argument to the SIZE operator.
Either positional or named parameters may be used. Positional and named parameters may not be mixed in a single
query. Input parameters can only be used in the WHERE clause or HAVING clause of a query.
The following rules apply to positional parameters.
Input parameters are designated by the question mark (?) prefix followed by an integer. For example: ?1.
Input parameters are numbered starting from 1. Note that the same parameter can be used more than once in the
query string and that the ordering of the use of parameters within the query string need not conform to the order
of the positional parameters.
A named parameter is an identifier that is prefixed by the ”:” symbol. It follows the rules for identifiers defined
in JPQL FROM Identifiers. Named parameters are case sensitive.
Conditional expressions are composed of other conditional expressions, comparison operations, logical operations,
path expressions that evaluate to boolean values, boolean literals, and boolean input parameters. Arithmetic
expressions can be used in comparison expressions. Arithmetic expressions are composed of other arithmetic
expressions, arithmetic operations, path expressions that evaluate to numeric values, numeric literals, and numeric
input parameters. Arithmetic operations use numeric promotion. Standard bracketing () for ordering expression
evaluation is supported. Conditional expressions are defined as follows:
conditional_expression ::= conditional_term | conditional_expression OR conditional_term
conditional_term ::= conditional_factor | conditional_term AND conditional_factor
conditional_factor ::= [ NOT ] conditional_primary
The syntax for the use of the comparison operator [NOT] BETWEEN in a conditional expression is as follows:
arithmetic_expression [NOT] BETWEEN arithmetic_expression AND arithmetic_expression | string_expression [NOT] BETWEEN string_expression AND string_expression | datetime_expression [NOT] BETWEEN datetime_expression AND datetime_expression
The BETWEEN expression x BETWEEN y AND z is semantically equivalent to y <= x AND x <= z,
The literal and/or input_parameter values must be like the same abstract schema type of the
state_field_path_expression in type. The results of the subquery must be like the same abstract schema type of the
state_field_path_expression in type. For example,, o.country IN ('UK', 'US', 'France')
The syntax for the use of the comparison operator [NOT] LIKE in a conditional expression is as follows:
1
string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]
The string_expression must have a string value. The pattern_value is a string literal or a string-valued
input parameter in which an underscore (_) stands for any single character, a percent (%) character stands for
any sequence of characters (including the empty sequence), and all other characters stand for themselves. The
optional escape_character is a single-character string literal or a character-valued input parameter (i.e., char
or Character) and is used to escape the special meaning of the underscore and percent characters in pattern_value. Examples are:
asentence.word LIKE 'l_se' - is true for ‘lose’ and false for ‘loose’
aword.underscored LIKE '\_%' ESCAPE '\' - is true for ‘_foo’ and false for ‘bar’
The syntax for the use of the comparison operator IS EMPTY in an empty_collection_comparison_expression is as follows:
collection_valued_path_expression IS [NOT] EMPTY
This expression tests whether or not the collection designated by the collection-valued path expression is empty
(i.e, has no elements).
For example, the following query will return all magazines that don’t have any articles at all:
1
SELECT mag
2
FROM Magazine mag
3
WHERE mag.articles ISEMPTY
If the value of the collection-valued path expression in an empty collection comparison expression is unknown, the
value of the empty comparison expression is unknown.
This expression tests whether the designated value is a member of the collection specified by the collection-valued
path expression. If the collection valued path expression designates an empty collection, the value of the MEMBER OF expression is FALSE and the value of the NOT MEMBER OF expression is TRUE. Otherwise, if the value of the
collection-valued path expression or single-valued association-field path expression in the collection member
expression is NULL or unknown, the value of the collection member expression is unknown.
An EXISTS expression is a predicate that is true only if the result of the subquery consists of one or more
values and that is false otherwise. The syntax of an exists expression is
exists_expression ::= [NOT] EXISTS (subquery)
The use of the reserved word OF is optional in this expression.
An ALL conditional expression is a predicate that is true if the comparison operation is true for all values in the
result of the subquery or the result of the subquery is empty. An ALL conditional expression is false if the
result of the comparison is false for at least one row, and is unknown if neither true nor false. An ANY
conditional expression is a predicate that is true if the comparison operation is true for some value in the result
of the subquery. An ANY conditional expression is false if the result of the subquery is empty or if the
comparison operation is false for every value in the result of the subquery, and is unknown if neither true nor
false. The keyword SOME is synonymous with ANY. The comparison operators used with ALL or ANY conditional
expressions are =, <, <=, >, >=, <>. The result of the subquery must be like that of the other argument to
the comparison operator in type.
1
all_or_any_expression ::= { ALL | ANY | SOME} (subquery)
The following example select the authors who make the highest salary for their magazine:
1
SELECT auth
2
FROM Author auth
3
WHERE auth.salary >= ALL(SELECT a.salary FROM Author a WHERE a.magazine = auth.magazine)
Subqueries are restricted to the WHERE and HAVING clauses in this release. Support for subqueries in the FROM
clause will be considered in a later release of the specification.
The JPQL includes the following built-in functions, which may be used in the WHERE or HAVING clause of a query.
If the value of any argument to a functional expression is null or unknown, the value of the functional expression
is unknown.
functions_returning_numerics ::= LENGTH(string_primar y) | LOCATE(string_primar y, string_primar y[, simple_arithmetic_expression])
The CONCAT function returns a string that is a concatenation of its arguments. The second and third arguments of
the SUBSTRING function denote the starting position and length of the substring to be returned. These arguments
are integers. The first position of a string is denoted by 1. The SUBSTRING function returns a string. The TRIM
function trims the specified character from a string. If the character to be trimmed is not specified, it is
assumed to be space (or blank). The optional trim_character is a single-character string literal or a
character-valued input parameter (i.e., char or Character). If a trim specification is not provided, BOTH is
assumed. The TRIM function returns the trimmed string. The LOWER and UPPER functions convert a string to
lower and upper case, respectively. They return a string. The LOCATE function returns the position of a given
string within a string, starting the search at a specified position. It returns the first position at which the
string was found as an integer. The first argument is the string to be located; the second argument is the string
to be searched; the optional third argument is an integer that represents the string position at which the search
is started (by default, the beginning of the string to be searched). The first position in a string is denoted by
“1.” If the string is not found, 0 is returned. The LENGTH function returns the length of the string in characters
“2.” as an integer.
The ABS function takes a numeric argument and returns a number (integer, float, or double) of the same type as the
argument to the function. The SQRT function takes a numeric argument and returns a double.
The MOD function takes two integer arguments and returns an integer. The SIZE function returns an integer
value, the number of elements of the collection. If the collection is empty, the SIZE function evaluates to zero.
Numeric arguments to these functions may correspond to the numeric Java object types as well as the primitive
numeric types.
The GROUP BY construct enables the aggregation of values according to a set of properties. The HAVING construct
enables conditions to be specified that further restrict the query result. Such conditions are restrictions upon
the groups. The syntax of the GROUP BY and HAVING clauses is as follows:
groupby_clause ::= GROUP BY groupby_item {, groupby_item}*
If a query contains both a WHERE clause and a GROUP BY clause, the effect is that of first applying the where
clause, and then forming the groups and filtering them according to the HAVING clause. The HAVING clause causes
those groups to be retained that satisfy the condition of the HAVING clause. The requirements for the SELECT
clause when GROUP BY is used follow those of SQL: namely, any item that appears in the SELECT clause (other
than as an argument to an aggregate function) must also appear in the GROUP BY clause. In forming the groups,
null values are treated as the same for grouping purposes. Grouping by an entity is permitted. In this case, the
entity must contain no serialized state fields or lob-valued state fields. The HAVING clause must specify search
conditions over the grouping items or aggregate functions that apply to grouping items.
The SELECT clause denotes the query result. More than one value may be returned from the SELECT clause of a
query. The SELECT clause may contain one or more of the following elements:
a single range variable or identification variable that ranges over an entity abstract schema type,
aggregate_expression ::= { AVG | MAX | MIN | SUM } ([DISTINCT] state_field_path_expression) | COUNT ([DISTINCT] identification_variable | state_field_path_expression | single_valued_association_path_expression)
For example:
1
SELECT pub.id, pub.revenue
2
FROM Publisher pub JOIN pub.magazines mag
3
WHERE mag.price >5.00
WARNING
⚠️ Note that the SELECT clause must be specified to return only single-valued expressions. The query below is
therefore not valid
1
SELECT mag.authors
2
FROM Magazine AS mag
The DISTINCT keyword is used to specify that duplicate values must be eliminated from the query result. If
DISTINCT is not specified, duplicate values are not eliminated. Standalone identification variables in the
SELECT clause may optionally be qualified by the OBJECT operator. The SELECT clause must not use the OBJECT
operator to qualify path expressions.
The type of the query result specified by the SELECT clause of a query is an entity abstract schema type, a
state-field type, the result of an aggregate function, the result of a construction operation, or some sequence of
these. The result type of the SELECT clause is defined by the the result types of the select_expressions
contained in it. When multiple select_expressions are used in the SELECT clause, the result of the query is of
type Object[], and the elements in this result correspond in order to the order of their specification in the
SELECT clause and in type to the result types of each of the select_expressions. The type of the result of a
select_expression is as follows:
A single_valued_path_expression that is a state_field_path_expression results in an object of the same type
as the corresponding state field of the entity. If the state field of the entity is a primitive type, the
corresponding object type is returned.
single_valued_path_expression that is a single_valued_association_path_expression results in an entity object
of the type of the relationship field or the subtype of the relationship field of the entity object as determined
by the object/relational mapping.
The result type of an identification_variable is the type of the entity to which that identification variable
corresponds or a subtype as determined by the object/relational mapping.
The result type of a constructor_expression is the type of the class for which the constructor is defined. The
types of the arguments to the constructor are defined by the above rules.
In the SELECT Clause a constructor may be used in the SELECT list to return one or more Java instances. The
specified class is not required to be an entity or to be mapped to the database. The constructor name must be fully
qualified.
If an entity class name is specified in the SELECT NEW clause, the resulting entity instances are in the new
state.
1
SELECT NEW com.company.PublisherInfo(pub.id, pub.revenue, mag.price)
If the result of a query corresponds to an association-field or state-field whose value is null, that null value is
returned in the result of the query method. The IS NOT NULL construct can be used to eliminate such null values
from the result set of the query.
NOTE
State-field types defined in terms of Java numeric primitive types cannot produce NULL values in the query result.
A query that returns such a state-field type as a result type must not return a null value.
In the SELECT clause the result of a query may be the result of an aggregate function applied to a path
expression. The following aggregate functions can be used in the SELECT clause of a query:
AVG
COUNT
MAX
MIN
SUM
For all aggregate functions except COUNT, the path expression that is the argument to the aggregate function must
terminate in a state-field. The path expression argument to COUNT may terminate in either a state-field or an
association-field, or the argument to COUNT may be an identification variable. Arguments to the functions SUM
and AVG must be numeric. Arguments to the functions MAX and MIN must correspond to orderable state-field
types (i.e., numeric types, string types, character types, or date types). The Java type that is contained in the
result of a query using an aggregate function is as follows:
COUNT returns Long.
MAX & MIN return the type of the state-field to which they are applied.
AVG returns Double.
SUM returns Long when applied to state-fields of integral types (other than BigInteger); Double when
applied to state-fields of floating point types; BigInteger when applied to state-fields of type BigInteger;
and BigDecimal when applied to state-fields of type BigDecimal.
If SUM, AVG, MAX, or MIN is used, and there are no values to which the aggregate function can be applied,
the result of the aggregate function is NULL. If COUNT is used, and there are no values to which COUNT can be
applied, the result of the aggregate function is 0.
The argument to an aggregate function may be preceded by the keyword DISTINCT to specify that duplicate values
are to be eliminated before the aggregate function is applied. Null values are eliminated before the aggregate
function is applied, regardless of whether the keyword DISTINCT is specified.
It is legal to specify DISTINCT with MAX or MIN, but it does not affect the result.
When the ORDER BY clause is used in a query, each element of the SELECT clause of the query must be one of the
following:
an identification variable x, optionally denoted as OBJECT(x),
a single_valued_association_path_expression, or a state_field_path_expression. For example:
SELECT pub FROM Publisher pub JOIN pub.magazines mag ORDER BY o.revenue, o.name
If more than one orderby-item is specified, the left-to-right sequence of the orderby-item elements determines the
precedence, whereby the leftmost orderby-item has highest precedence. The keyword ASC specifies that ascending
ordering be used; the keyword DESC specifies that descending ordering be used. Ascending ordering is the default.
SQL rules for the ordering of null values apply: that is, all null values must appear before all non-null values in
the ordering or all null values must appear after all non-null values in the ordering, but it is not specified
which. The ordering of the query result is preserved in the result of the query method if the ORDER BY clause is
used.
Operations Bulk update and delete operations apply to entities of a single entity class (together with its
subclasses, if any). Only one entity abstract schema type may be specified in the FROM or UPDATE clause. The
syntax of these operations is as follows:
update_statement ::= update_clause [where_clause]
update_clause ::= UPDATE abstract_schema_name [[AS] identification_variable] SET update_item {, update_item}*
delete_clause ::= DELETE FROM abstract_schema_name [[AS] identification_variable]
The syntax of the WHERE clause is described in JPQL WHERE Clause. A delete operation only
applies to entities of the specified class and its subclasses. It does not cascade to related entities. The
new_value specified for an update operation must be compatible in type with the state-field to which it is
assigned. Bulk update maps directly to a database update operation, bypassing optimistic locking checks. Portable
applications must manually update the value of the version column, if desired, and/or manually validate the value
of the version column. The persistence context is not synchronized with the result of the bulk update or delete.
Caution should be used when executing bulk update or delete operations because they may result in inconsistencies
between the database and the entities in the active persistence context. In general, bulk update and delete
operations should only be performed within a separate transaction or at the beginning of a transaction (before
entities have been accessed whose state might be affected by such operations).
When the target of a reference does not exist in the database, its value is regarded as NULL. SQL 92 NULL
semantics defines the evaluation of conditional expressions containing NULL values. The following is a brief
description of these semantics:
Comparison or arithmetic operations with a NULL value always yield an unknown value.
Two NULL values are not considered to be equal, the comparison yields an unknown value.
Comparison or arithmetic operations with an unknown value always yield an unknown value.
The IS NULL and IS NOT NULL operators convert a NULL state-field or single-valued association-field value into the respective TRUE or FALSE value.
TIP
The JPQL defines the empty string, "", as a string with 0 length, which is not equal to a NULL value. However,
NULL values and empty strings may not always be distinguished when queries are mapped to some databases.
Application developers should therefore not rely on the semantics of query comparisons involving the empty string
and NULL value.
Only the values of like types are permitted to be compared. A type is like another type if they correspond to the
same Java language type, or if one is a primitive Java language type and the other is the wrappered Java class type
equivalent (e.g., int and Integer are like types in this sense). There is one exception to this rule: it is
valid to compare numeric values for which the rules of numeric promotion apply. Conditional expressions attempting
to compare non-like type values are disallowed except for this numeric case. Note that the arithmetic operators and
comparison operators are permitted to be applied to state-fields and input parameters of the wrappered Java class
equivalents to the primitive numeric Java types. Two entities of the same abstract schema type are equal if and
only if they have the same primary key value. Only equality/inequality comparisons over enums are required to be
supported.
between_expression ::= arithmetic_expression [NOT] BETWEEN arithmetic_expression AND arithmetic_expression | string_expression [NOT] BETWEEN string_expression AND string_expression | datetime_expression [NOT] BETWEEN datetime_expression AND datetime_expression
You may notice that command line result of mysql is not displaying results properly. Some columns of table are in the
first line and some are in the second line. Output is also broken into two rows. How do we adjust these settings so that
it properly display results?
We can use the \G command (instead of the ;) at the end of our SQL queries:
1
SELECT*FROM USER \G
It will display your table in row form instead of column form.
The safest way would be to create a new config file
and pass it to mysql using either the --defaults-file= or --defaults-extra-file= command line option. The
difference between the two is that the latter is read in addition to the default config files whereas with the former
only the one file passed as the argument is used. The additional configuration file should contain something similar to:
Suppose we have x_table with columns (instance, user, item) where instance ID is unique. I want to insert a new row
only if the user already does not have a given item.
For example trying to insert instance=919191 user=123 item=456
1
Insert into x_table (instance, user, item) values (919191, 123, 456)
2
ONLY IF there are norowswhere user=123and item=456
If your DBMS does not impose limitations on which table you select from when you execute an insert, try:
1
INSERT INTO x_table(instance, user, item)
2
SELECT919191, 123, 456
3
FROM dual
4
WHERENOTEXISTS (SELECT*FROM x_table
5
WHERE user =123
6
AND item =456)
In this, dual is a table with one row only (found originally in Oracle, now in mysql too). The logic is that the
SELECT statement generates a single row of data with the required values, but only when the values are not already
found.
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or
two numeric arguments, which must both be non-negative integer constants, with these exceptions:
Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.
Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the
maximum number of rows to return. The offset of the initial row is 0 (not 1):
1
SELECT*FROM tbl LIMIT5,10; # Retrieve rows6-15
With one argument, the value specifies the number of rows to return from the beginning of the result set:
Returns a value if a condition is TRUE, or another value if a condition is FALSE. For example, Return “YES” if the
condition is TRUE, or “NO” if the condition is FALSE:
1
SELECTIF(500<1000, "YES", "NO");
This function is useful if we would like to replicate a table and update some column values on the flight:
In the example above, all rows with column value “Blue” will have “Dark Blue” as the new value for that column; all
other rows will be changed to “Dark Color”.
We could have richer modifications, other than 2-branch modification, using
CASE
Copying Tables or Databases from One MySQL Server to Another#
We could use mysqldump to export the data from one MySQL instance and mysql command line utility to load it into
another.
Suppose we have a person table in “prod” database hosted in “192.168.1.100” and we would like to migrate this table
data into a dev instance (“192.168.1.101”) for testing purposes.
Represents an inheritance hierarchy of classes as a single table that has columns for all the fields of the various
classes.
For a full description see Patterns of Enterprise Application Architecture - Martin Fowler (The Addison-Wesley
Signature Series) page 278
Relational databases don’t support inheritance, so when mapping from objects to databases we have to consider how to
represent our nice inheritance structures in relational tables. When mapping to a relational database, we try to
minimize the joins that can quickly mount up when processing an inheritance structure in multiple tables. Single Table
Inheritance maps all fields of all classes of an inheritance structure into a single table.
Represents an inheritance hierarchy of classes with one table for each class.
For a full description see Patterns of Enterprise Application Architecture - Martin Fowler (The Addison-Wesley
Signature Series) page 285
A very visible aspect of the object-relational mismatch is the fact that relational databases don’t support inheritance.
You want database structures that map clearly to the objects and allow links anywhere in the inheritance structure.
Class Table Inheritance supports this by using one database table per class in the inheritance structure.
Represents an inheritance hierarchy of classes with one table per concrete class in the hierarchy.
For a full description see Patterns of Enterprise Application Architecture - Martin Fowler (The Addison-Wesley
Signature Series) page 293
As any object purist will tell you, relational databases don’t support inheritance - a fact that complicates
object-relational mapping. Thinking of tables from an object instance point of view, a sensible route is to take each
object in memory and map it to a single database row. This implies Concrete Table Inheritance, where there’s a table for
each concrete class in the inheritance hierarchy.
How Applications Become Vulnerable to SQL Injection#
Despite being one of the best-known vulnerabilities, SQL Injection continues to rank on the top spot of the infamous
OWASP Top 10’s list - now part of the more general Injection class.
Injection attacks work because, for many applications, the only way to execute a given computation is to dynamically
generate code that is in turn run by another system or component. If in the process of generating this code we use
untrusted data without proper sanitization, we leave an open door for hackers to exploit
This statement may sound a bit abstract, so let’s take look at how this happens in practice with an example:
The problem with this code is obvious: we’ve put the customerId‘s value into the query with no validation at all.
Nothing bad will happen if we’re sure that this value will only come from trusted sources, but can we?
Let’s imagine that this function is used in a REST API implementation for an account resource. Exploiting this code is
trivial: all we have to do is to send a value that, when concatenated with the fixed part of the query, change its
intended behavior:
This is a common misconception. JPA and other ORMs relieves us from creating hand-coded SQL statements, but they won’t
prevent us from writing vulnerable code.
Let’s see how the JPA version of the previous example looks:
The same issue we’ve pointed before is also present here: we’re using unvalidated input to create a JPA query, so we’re
exposed to the same kind of exploit here.
Now that we know what a SQL injection is, let’s see how we can protect our code from this kind of attack. Here we’re
focusing on a couple of very effective techniques available in Java and other JVM languages, but similar concepts are
available to other environments, such as PHP, .Net, Ruby and so forth.
For those looking for a complete list of available techniques, including database-specific ones, the
OWASP Project maintains a
SQL Injection Prevention Cheat Sheet,
which is a good place to learn more about the subject.
This technique consists of using prepared statements with the question mark placeholder (“?”) in our queries
whenever we need to insert a user-supplied value. This is very effective and, unless there’s a bug in the JDBC driver’s
implementation, immune to exploits.
Let’s rewrite our example function to use this technique:
+"customer_id, acc_number, branch_id, balance from Accounts"
4
+"where customer_id = ?";
5
6
Connection c = dataSource.getConnection();
7
PreparedStatement p = c.prepareStatement(sql);
8
p.setString(1, customerId);
9
ResultSet rs = p.executeQuery(sql));
10
// omitted - process rows and return an account list
11
}
Here we’ve used the prepareStatement() method available in the Connection instance to get a
PreparedStatement. This interface extends the regular Statement interface with several methods that allow us to
safely insert user-supplied values in a query before executing it. For JPA, we have a similar feature:
1
String jql ="FROM Account WHERE customerId = :customerId";
// Execute query and return mapped results (omitted)
As a bonus, this approach usually results in a better performing query, since most databases can cache the query plan
associated with a prepared statement.
Please note that this approach only works for placeholders used as values. For instance, we can’t use placeholders to
dynamically change the name of a table:
1
// This WILL NOT WORK !!!
2
PreparedStatement p = c.prepareStatement("select count(*) from ?");
3
p.setString(1, tableName);
Here, JPA won’t help either:
1
// This WILL NOT WORK EITHER !!!
2
entityManager
3
.createQuery("SELECT COUNT(*) FROM :tableName", Long.class)
4
.setParameter("tableName", tableName);
5
.getSingleResult();
In both cases, we’ll get a runtime error.
The main reason behind this is the very nature of a prepared statement: database servers use them to cache the query
plan required to pull the result set, which usually is the same for any possible value. This is not true for table names
and other constructs available in the SQL language such as columns used in an order by clause.
Let’s say we want to login to MySQL shell as root, whose password is lost. We could do
Stop and start mysql with --skip-grant-tables option
Terminal window
1
mysql.serverstop
2
mysql.serverstart--skip-grant-tables
then connect to our mysqld without username/password
Terminal window
1
mysql
Drop the user (e.g. root). After deleting the user, flush the mysql privileges. then create the user. Assuming we want
to create the user root @ localhost, these would be the commands:
If mysql still complains about access denied after mysql.server start --skip-grant-tables, uninstall mysql and
mysql-client with
Terminal window
1
brewuninstallmysqlmysql-client
Then do not install mysql through homebrew, but from
MySQL official page instead.
CAUTION
It is very important to remember that the installation will show us a temporary root password. Please DO make sure to
copy this as we will be needing it to reset our root password later
We will, however, still install mysql-client using homebrew
Terminal window
1
brewinstallmysql-client
Then we can do
Terminal window
1
mysql-hlocalhost-uroot-p
MySQL won’t allow us to do anything further unless we reset password using the temporary one given during the
installation step:
1
SET PASSWORD = PASSWORD('your_new_password');
Exit mysql shell and re-enter it. We shall start using MySQL now.
For data entry into a NOT NULL column that has no explicit DEFAULT clause, if an INSERT or REPLACE statement
includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according
to the SQL mode in effect at the time:
If strict SQL mode is enabled, an error occurs for transactional tables and the statement is rolled back. For
nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row
statement, the preceding rows are inserted.
If strict mode is not enabled, MySQL sets the column to the implicit default value for the column data type.
The cause of the duplicate rows is that the left table has rows that have more than 1 associated rows in the right
table.
The solution is to GROUP BY the results.
For example, take a look at the following query:
1
tbl_contents
2
3
content_id content_title content_text
4
10002 New case Study New case Study
5
10003 New case Study New case Study
6
10004 New case Study New case Study
7
10005 New case Study New case Study
8
10006 New case Study New case Study
9
10007 New case Study New case Study
10
10008 New case Study New case Study
11
10009 New case Study New case Study
12
10010 SEO News Title SEO News Text
13
10011 SEO News Title SEO News Text
14
10012 Publish Contents SEO News Text
1
tbl_media
2
3
media_id media_title content_id
4
1000 New case Study 10012
5
1001 SEO News Title 10010
6
1002 SEO News Title 10011
7
1003 Publish Contents 10012
1
SELECT
2
C.content_id, C.content_title, M.media_id
3
FROM
4
tbl_contents C LEFT JOIN tbl_media M ON M.content_id = C.content_id
5
ORDER BY C.Content_DatePublished ASC
1
10002 New case Study 2014-03-31 13:39:29.280 NULL
2
10003 New case Study 2014-03-31 14:23:06.727 NULL
3
10004 New case Study 2014-03-31 14:25:53.143 NULL
4
10005 New case Study 2014-03-31 14:26:06.993 NULL
5
10006 New case Study 2014-03-31 14:30:18.153 NULL
6
10007 New case Study 2014-03-31 14:30:42.513 NULL
7
10008 New case Study 2014-03-31 14:31:56.830 NULL
8
10009 New case Study 2014-03-31 14:35:18.040 NULL
9
10010 SEO News Title 2014-03-31 15:22:15.983 1001
10
10011 SEO News Title 2014-03-31 15:22:30.333 1002
11
10012 Publish 2014-03-31 15:25:11.753 1000
12
10012 Publish 2014-03-31 15:25:11.753 1003
We see that 10012 are coming up twice…, because some rows in tbl_contents has more than 1 associated rows in
tbl_media. To eliminate the duplicate:
1
SELECT
2
C.content_id,
3
C.content_title,
4
C.Content_DatePublished,
5
M.media_id
6
FROM
7
tbl_contents C LEFT JOIN tbl_media M ON M.content_id = C.content_id
8
GROUP BY
9
C.content_id
10
ORDER BY
11
C.Content_DatePublished ASC
Loading Large File into Database Causes “MySQL Server has gone away”#
According to documentation and personal experience, the most
common reason is that we send a query to the server that is incorrect or too large. If MySQL Server receives a
packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the
connection. If we need big queries (for example, if we are working with big BLOB columns), we can increase the query
limit by setting the server’s max_allowed_packet variable, which has a default value of 64MB. We may also need to
increase the maximum packet size on the client end.
Both the client and the server have their own max_allowed_packet variable, so if we want to handle big packets, we
must increase this variable both in the client and in the server.
If we are using the mysql client program, its default max_allowed_packet variable is 16MB. To set a larger value,
start mysql like this:
Terminal window
1
mysql--max_allowed_packet=32M
That sets the packet size to 32MB.
The server’s default max_allowed_packet value is 64MB. We can increase this if the server needs to handle big queries
(for example, if we are working with big BLOB columns). For example, to set the variable to 128MB, start the server like
this:
Terminal window
1
mysqld--max_allowed_packet=128M
We can also use an option file to set max_allowed_packet. For example, to set the size for the server to 128MB, add
the following lines in an option file:
Terminal window
1
[mysqld]
2
max_allowed_packet=128M
It is safe to increase the value of this variable because the extra memory is allocated only when needed. For example,
MySQL Server allocates more memory only when we issue a long query or when MySQL Server must return a large result row.
The small default value of the variable is a precaution to catch incorrect packets between the client and server and
also to ensure that we do not run out of memory by using large packets accidentally.
We can also get strange problems with large packets if we are using large BLOB values but have not given MySQL Sever
access to enough memory to handle the query. If we suspect this is the case, try adding ulimit -d 256000 to the
beginning of the mysqld_safe script and restarting MySQL Server.
a single SQL statement sent to the MySQL server, or
a single row that is sent to the client, or
a binary log event sent from a master replication server to a slave.
When a MySQL client or the MySQL server receives a packet bigger than max_allowed_packet bytes, it issues an
ER_NET_PACKET_TOO_LARGE error and closes the connection.
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation ’=’#
Resolving this issues requires understanding of MySQL collations.
Go to the database related to the issue and issue the following command
Terminal window
1
mysql> showvariablesWHEREvariable_namelike"col%";
2
+----------------------+-------------------+
3
|Variable_name|Value|
4
+----------------------+-------------------+
5
|collation_connection|latin1_swedish_ci|
6
|collation_database|utf8_general_ci|
7
|collation_server|latin1_general_ci|
8
+----------------------+-------------------+
9
3rowsinset (0.02 sec)
We see the database collation is for UTF8.
When we see the error “Illegal mix of collations…”, it is complaining about a table whose collation is inconsistent
with what’s configured for “collation_database” shown above.
Go to the table that throws the “Illegal mix of collations…” error and issue the following command:
We see the table collation has “latin1_swedish_ci”, which is not UTF8. We will alter this table so that is aligns with
database level config like this:
Oracle database has been widely used in commercial institutions in China, such as banks. This post offers a practical
reference guide on Oracle database operations
Whenever a client program (such as sqlplus) connects to the database, it tells the database what characterset it is
using. Some environments may have a very restricted characterset and use something like US7ASCII so they don’t get
anything that can upset them.
As you can see in the following example, what is output by a query is dependent on the NLS_LANG setting of a client.
If you have worked with MySQL, you may be familiar with the SHOW TABLES command that lists all tables in a database:
1
SHOW TABLES;
Unfortunately, Oracle does not directly support the SHOW TABLES command. However, you can list all tables in a
database by querying from various data dictionary views.
To show tables owned by the current user, you query from the user_tables view.
1
SELECT table_name
2
FROM user_tables
3
ORDER BY table_name;
Note that this view does not show the OWNER column. Also, the user_tables table does not contain the other tables
that are accessible by the current user.
To show all tables in the entire Oracle Database, you query from the dba_tables view as follows:
1
SELECT table_name
2
FROM dba_tables;
You will get the following error message if you don’t have access to the dba_tables view:
Terminal window
1
ORA-00942:tableorviewdoesnotexist
In this case, you should request your database administrator to grant your account either privileges on the dba_tables
view, or SELECT ANY DICTIONARY privilege, or SELECT_CATALOG_ROLE privilege.
This is assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but
need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the
SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data
dictionary table). Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers
of Oracle tables that you probably don’t care about.
Alternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to
through the ALL_TABLES view:
1
SELECTOWNER, TABLE_NAME
2
FROM ALL_TABLES;
Although, that may be a subset of the tables available in the database (ALL_TABLES shows you the information for all
the tables that your user has been granted access to).
If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES:
1
SELECT TABLE_NAME
2
FROM USER_TABLES;
Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column - the owner,
by definition, is you.
Oracle also has a number of legacy data dictionary views — TAB, DICT, TABS, and CAT for example — that could
be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to
Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For
example, the TAB and CAT views both show information about tables that are in the user’s recycle bin while the
[DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a
TABLE_TYPE of “TABLE” which is unlikely to be what you really want. DICT combines tables and synonyms and doesn’t
tell you who owns the object.
When you have a script that has syntax errors, the script won’t exit with error while executing the line that has the
syntax error. We would like to, instead, halt the execution at that point for the script tuning purposes. To do that
WHENEVER SQLERROR could be used.
The commands in the following script cause SQL*Plus to exit and return the SQL error code if the SQL UPDATE command
fails:
Given that the operation of moving from a varchar column to a CLOB is disallowed, the most straightforward way would be
to create a new column and move the data from the old column to the new column:
1
ALTERTABLE atable ADD (tmpdetails CLOB);
2
UPDATE atable SET tmpdetails=details;
3
COMMIT;
4
ALTERTABLE atable DROP COLUMN details;
5
ALTERTABLE atable RENAME COLUMN tmpdetails TO details;
Querydsl was born out of the need to maintain HQL queries in a typesafe way. Incremental construction of HQL queries
requires String concatenation and results in hard to read code. Unsafe references to domain types and properties via
plain Strings were another issue with String based HQL construction.
With a changing domain model type-safety brings huge benefits in software development. Domain changes are directly
reflected in queries and autocomplete in query construction makes query construction faster and safer.
HQL for Hibernate was the first target language for Querydsl, but nowadays it supports JPA, JDO, JDBC, Lucene, Hibernate
Search, MongoDB, Collections and RDFBean as backends.
Type safety is the core principle of Querydsl. Queries are constructed based on generated query types that reflect the
properties of our domain types. Also function/method invocations are constructed in a fully type-safe manner.
Consistency is another important principle. The query paths and operations are the same in all implementations and also
the Query interfaces have a common base interface. This means the domain types are defined only once and they will be
ready to be used against SQL, Lucene, MongoDB, etc.
Querydsl defines a general statically typed syntax for querying on top of persisted domain model data.
JDO and JPA are the primary integration technologies for Querydsl.
This guide describes how to use Querydsl in combination with JPA.
Querydsl for JPA is an alternative to both JPQL and Criteria queries. It combines the dynamic nature of Criteria
queries with the expressiveness of JPQL and all that in a fully typesafe manner.
The JPAAnnotationProcessor finds domain types annotated with the javax.persistence.Entity annotation and generates
query types for them.
WARNING
If we use Hibernate annotations in our domain types we should use the APT processor of
com.querydsl.apt.hibernate.HibernateAnnotationProcessor instead.
To generate source codes the query types, run mvn clean install and we will get our Query types generated into
target/generated-sources/java.
Now we are able to construct JPA query instances and instances of the query domain model. For example, let’s assume that
our project has the following domain type:
1
@Entity
2
publicclassCustomer{
3
4
privateString firstName;
5
privateString lastName;
6
7
publicStringgetFirstName(){
8
return firstName;
9
}
10
11
publicStringgetLastName(){
12
return lastName;
13
}
14
15
publicvoidsetFirstName(Stringfn){
16
firstName = fn;
17
}
18
19
publicvoidsetLastName(Stringln){
20
lastName = ln;
21
}
22
}
Querydsl will generate a query type with the simple name QCustomer into the same package as Customer. QCustomer can
be used as a statically typed variable in Querydsl queries as a representative for the Customer type.
QCustomer has a default instance variable which can be accessed as a static field:
1
QCustomer customer = QCustomer.customer;
Alternatively we can define our own Customer variables like this:
Suppose we have a SQL query that retrieves all unique names of a “Person” table. This query works perfectly in MySQL:
1
SELECT id, name
2
FROM Person
3
GROUP BYname
Oracle, databases, requires all projected columns to be in GROUP BY clause, i.e. the same query must be modified to
the following:
1
SELECT id, name
2
FROM Person
3
GROUP BYname, id
Note that we’ve appended id column in the GROUP BY clause.
Oracle, in this case, brings up an important issue for Querydsl. How do we comply with DRY principle while maintaining
2 copies of columns, one in projection and one in group-by clause, in the code level?
The answer is deduplicating by reducing the 2 copies into 1 single-source of truth and use that to re-generate
projections and GROUP BY clause.
For example, we could define projected columns first before specifying projections and group-bys:
1
/**
2
* Returns an object including all of the projections for a query.
3
* <p>
4
* The object is a mapping from projected column to its column type.
Note that in order to generate projections and group bys later, all we need is the name(display/alias) of the column
and the field type of the Database bean, as shown above.
To generate projections, we could use dynamic path
to dynamic bind columns to our query:
1
/**
2
* Generate the same group-by columns as the projected columns determined by {@link #getQueryDslProjections()}
3
* <p>
4
* For example, if the projection is
5
* <pre>
6
* {@code
7
* SELECT person.id, person.name FROM ...
8
* }
9
* </pre>
10
* Then this method returns
11
* <pre>
12
* GROUP BY person.id, person.name
13
* </pre>
14
* The purpose of this method is to make Oracle-compatible GROUP-BY, which must includes all projected columns
15
*
16
* @paramprojections a mapping from path suffix and the field type of the suffix path. For example:
