13.4. Hibernate Query Language
13.4.1. About Hibernate Query Language
13.4.2. HQL Statements
SELECT
, UPDATE
, DELETE
, and INSERT
statements. The HQL INSERT
statement has no equivalent in JPQL.
Important
UPDATE
or DELETE
statement is executed.
Statement | Description |
---|---|
SELECT |
The BNF for
SELECT statements in HQL is:
select_statement :: = [select_clause] from_clause [where_clause] [groupby_clause] [having_clause] [orderby_clause]
The simplest possible HQL
SELECT statement is of the form:
from com.acme.Cat |
UDPATE | The BNF for UPDATE statement in HQL is the same as it is in JPQL |
DELETE | The BNF for DELETE statements in HQL is the same as it is in JPQL |
13.4.3. About the INSERT Statement
INSERT
statements. There is no JPQL equivalent to this. The BNF for an HQL INSERT
statement is:
insert_statement ::= insert_clause select_statement insert_clause ::= INSERT INTO entity_name (attribute_list) attribute_list ::= state_field[, state_field ]*
attribute_list
is analogous to the column specification
in the SQL INSERT
statement. For entities involved in mapped inheritance, only attributes directly defined on the named entity can be used in the attribute_list
. Superclass properties are not allowed and subclass properties do not make sense. In other words, INSERT
statements are inherently non-polymorphic.
Warning
select_statement
can be any valid HQL select query, with the caveat that the return types must match the types expected by the insert. Currently, this is checked during query compilation rather than allowing the check to relegate to the database. This may cause problems between Hibernate Types which are equivalent as opposed to equal. For example, this might cause lead to issues with mismatches between an attribute mapped as a org.hibernate.type.DateType
and an attribute defined as a org.hibernate.type.TimestampType
, even though the database might not make a distinction or might be able to handle the conversion.
id
attribute, the insert statement gives you two options. You can either explicitly specify the id
property in the attribute_list
, in which case its value is taken from the corresponding select expression, or omit it from the attribute_list
in which case a generated value is used. This latter option is only available when using id
generators that operate "in the database"; attempting to use this option with any "in memory" type generators will cause an exception during parsing.
attribute_list
in which case its value is taken from the corresponding select expressions, or omit it from the attribute_list
in which case the seed value
defined by the corresponding org.hibernate.type.VersionType
is used.
Example 13.3. INSERT
Query Statements
String hqlInsert = "insert into DelinquentAccount (id, name) select c.id, c.name from Customer c where ..."; int createdEntities = s.createQuery( hqlInsert ).executeUpdate();
13.4.4. About the FROM Clause
FROM
clause is responsible defining the scope of object model types available to the rest of the query. It also is responsible for defining all the "identification variables" available to the rest of the query.
13.4.5. About the WITH Clause
WITH
clause to qualify the join conditions. This is specific to HQL; JPQL does not define this feature.
Example 13.4. With
Clause
select distinct c from Customer c left join c.orders o with o.value > 5000.00
with clause
are made part of the on clause
in the generated SQL as opposed to the other queries in this section where the HQL/JPQL conditions are made part of the where clause
in the generated SQL. The distinction in this specific example is probably not that significant. The with clause
is sometimes necessary in more complicated queries.
13.4.6. About Bulk Update, Insert and Delete
Warning
( UPDATE | DELETE ) FROM? EntityName (WHERE where_conditions)?
.
Note
FROM
keyword and the WHERE Clause
are optional.
Example 13.5. Bulk Update Statement
Session session = sessionFactory.openSession(); Transaction tx = session.beginTransaction(); String hqlUpdate = "update Company set name = :newName where name = :oldName"; int updatedEntities = s.createQuery( hqlUpdate ) .setString( "newName", newName ) .setString( "oldName", oldName ) .executeUpdate(); tx.commit(); session.close();
Example 13.6. Bulk Delete statement
Session session = sessionFactory.openSession(); Transaction tx = session.beginTransaction(); String hqlDelete = "delete Company where name = :oldName"; int deletedEntities = s.createQuery( hqlDelete ) .setString( "oldName", oldName ) .executeUpdate(); tx.commit(); session.close();
int
value returned by the Query.executeUpdate()
method indicates the number of entities within the database that were affected by the operation.
Company
table for companies that are named with oldName
, but also against joined tables. Thus, a Company table in a BiDirectional ManyToMany relationship with an Employee table, would lose rows from the corresponding join table Company_Employee
as a result of the successful execution of the previous example.
int deletedEntries
value above will contain a count of all the rows affected due to this operation, including the rows in the join tables.
INSERT INTO EntityName properties_list select_statement
.
Note
Example 13.7. Bulk Insert statement
Session session = sessionFactory.openSession(); Transaction tx = session.beginTransaction(); String hqlInsert = "insert into Account (id, name) select c.id, c.name from Customer c where ..."; int createdEntities = s.createQuery( hqlInsert ) .executeUpdate(); tx.commit(); session.close();
id
attribute via the SELECT statement, an identifier is generated for you, as long as the underlying database supports auto-generated keys. The return value of this bulk insert operation is the number of entries actually created in the database.
13.4.7. About Collection Member References
Example 13.8. Collection References
select c from Customer c join c.orders o join o.lineItems l join l.product p where o.status = 'pending' and p.status = 'backorder' // alternate syntax select c from Customer c, in(c.orders) o, in(o.lineItems) l join l.product p where o.status = 'pending' and p.status = 'backorder'
o
actually refers to the object model type Order
which is the type of the elements of the Customer#orders
association.
IN
syntax. Both forms are equivalent. Which form an application chooses to use is simply a matter of taste.
13.4.8. About Qualified Path Expressions
Expression | Description |
---|---|
VALUE |
Refers to the collection value. Same as not specifying a qualifier. Useful to explicitly show intent. Valid for any type of collection-valued reference.
|
INDEX |
According to HQL rules, this is valid for both Maps and Lists which specify a
javax.persistence.OrderColumn annotation to refer to the Map key or the List position (aka the OrderColumn value). JPQL however, reserves this for use in the List case and adds KEY for the MAP case. Applications interested in JPA provider portability should be aware of this distinction.
|
KEY |
Valid only for Maps. Refers to the map's key. If the key is itself an entity, can be further navigated.
|
ENTRY |
Only valid only for Maps. Refers to the Map's logical
java.util.Map.Entry tuple (the combination of its key and value). ENTRY is only valid as a terminal path and only valid in the select clause.
|
Example 13.9. Qualified Collection References
// Product.images is a Map<String,String> : key = a name, value = file path // select all the image file paths (the map value) for Product#123 select i from Product p join p.images i where p.id = 123 // same as above select value(i) from Product p join p.images i where p.id = 123 // select all the image names (the map key) for Product#123 select key(i) from Product p join p.images i where p.id = 123 // select all the image names and file paths (the 'Map.Entry') for Product#123 select entry(i) from Product p join p.images i where p.id = 123 // total the value of the initial line items for all orders for a customer select sum( li.amount ) from Customer c join c.orders o join o.lineItems li where c.id = 123 and index(li) = 1
13.4.9. About Scalar Functions
13.4.10. HQL Standardized Functions
Function | Description |
---|---|
BIT_LENGTH |
Returns the length of binary data.
|
CAST |
Performs a SQL cast. The cast target should name the Hibernate mapping type to use.
|
EXTRACT |
Performs a SQL extraction on datetime values. An extraction extracts parts of the datetime (the year, for example). See the abbreviated forms below.
|
SECOND |
Abbreviated extract form for extracting the second.
|
MINUTE |
Abbreviated extract form for extracting the minute.
|
HOUR |
Abbreviated extract form for extracting the hour.
|
DAY |
Abbreviated extract form for extracting the day.
|
MONTH |
Abbreviated extract form for extracting the month.
|
YEAR |
Abbreviated extract form for extracting the year.
|
STR |
Abbreviated form for casting a value as character data.
|
addSqlFunction
method of org.hibernate.cfg.Configuration
13.4.11. About the Concatenation Operation
CONCAT
) function. This is not defined by JPQL, so portable applications should avoid using it. The concatenation operator is taken from the SQL concatenation operator - ||
.
Example 13.10. Concatenation Operation Example
select 'Mr. ' || c.name.first || ' ' || c.name.last from Customer c where c.gender = Gender.MALE
13.4.12. About Dynamic Instantiation
Example 13.11. Dynamic Instantiation Example - Constructor
select new Family( mother, mate, offspr ) from DomesticCat as mother join mother.mate as mate left join mother.kittens as offspr
Example 13.12. Dynamic Instantiation Example - List
select new list(mother, offspr, mate.name) from DomesticCat as mother inner join mother.mate as mate left outer join mother.kittens as offspr
Example 13.13. Dynamic Instantiation Example - Map
select new map( mother as mother, offspr as offspr, mate as mate ) from DomesticCat as mother inner join mother.mate as mate left outer join mother.kittens as offspr select new map( max(c.bodyWeight) as max, min(c.bodyWeight) as min, count(*) as n ) from Cat cxt
13.4.13. About HQL Predicates
TRUE
or FALSE
, although boolean comparisons involving NULLs generally resolve to UNKNOWN
.
HQL Predicates
- Nullness Predicate
- Check a value for nullness. Can be applied to basic attribute references, entity references and parameters. HQL additionally allows it to be applied to component/embeddable types.
Example 13.14. Nullness Checking Examples
// select everyone with an associated address select p from Person p where p.address is not null // select everyone without an associated address select p from Person p where p.address is null
- Like Predicate
- Performs a like comparison on string values. The syntax is:
like_expression ::= string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]
The semantics follow that of the SQL like expression. Thepattern_value
is the pattern to attempt to match in thestring_expression
. Just like SQL,pattern_value
can use "_" and "%" as wildcards. The meanings are the same. "_" matches any single character. "%" matches any number of characters.The optionalescape_character
is used to specify an escape character used to escape the special meaning of "_" and "%" in thepattern_value
. This is useful when needing to search on patterns including either "_" or "%".Example 13.15. Like Predicate Examples
select p from Person p where p.name like '%Schmidt' select p from Person p where p.name not like 'Jingleheimmer%' // find any with name starting with "sp_" select sp from StoredProcedureMetadata sp where sp.name like 'sp|_%' escape '|'
- Between Predicate
- Analogous to the SQL
BETWEEN
expression. Perform a evaluation that a value is within the range of 2 other values. All the operands should have comparable types.Example 13.16. Between Predicate Examples
select p from Customer c join c.paymentHistory p where c.id = 123 and index(p) between 0 and 9 select c from Customer c where c.president.dateOfBirth between {d '1945-01-01'} and {d '1965-01-01'} select o from Order o where o.total between 500 and 5000 select p from Person p where p.name between 'A' and 'E'
13.4.14. About Relational Comparisons
Example 13.17. Relational Comparison Examples
// numeric comparison select c from Customer c where c.chiefExecutive.age < 30 // string comparison select c from Customer c where c.name = 'Acme' // datetime comparison select c from Customer c where c.inceptionDate < {d '2000-01-01'} // enum comparison select c from Customer c where c.chiefExecutive.gender = com.acme.Gender.MALE // boolean comparison select c from Customer c where c.sendEmail = true // entity type comparison select p from Payment p where type(p) = WireTransferPayment // entity value comparison select c from Customer c where c.chiefExecutive = c.chiefTechnologist
ALL
, ANY
, SOME
. SOME
and ANY
are synonymous.
ALL
qualifier resolves to true if the comparison is true for all of the values in the result of the subquery. It resolves to false if the subquery result is empty.
Example 13.18. ALL Subquery Comparison Qualifier Example
// select all players that scored at least 3 points // in every game. select p from Player p where 3 > all ( select spg.points from StatsPerGame spg where spg.player = p )
ANY
/SOME
qualifier resolves to true if the comparison is true for some of (at least one of) the values in the result of the subquery. It resolves to false if the subquery result is empty.
13.4.15. About the IN Predicate
IN
predicate performs a check that a particular value is in a list of values. Its syntax is:
in_expression ::= single_valued_expression [NOT] IN single_valued_list single_valued_list ::= constructor_expression | (subquery) | collection_valued_input_parameter constructor_expression ::= (expression[, expression]*)
single_valued_expression
and the individual values in the single_valued_list
must be consistent. JPQL limits the valid types here to string, numeric, date, time, timestamp, and enum types. In JPQL, single_valued_expression
can only refer to:
- "state fields", which is its term for simple attributes. Specifically this excludes association and component/embedded attributes.
- entity type expressions.
single_valued_expression
can refer to a far more broad set of expression types. Single-valued association are allowed. So are component/embedded attributes, although that feature depends on the level of support for tuple or "row value constructor syntax" in the underlying database. Additionally, HQL does not limit the value type in any way, though application developers should be aware that different types may incur limited support based on the underlying database vendor. This is largely the reason for the JPQL limitations.
constructor_expression
and collection_valued_input_parameter
, the list of values must not be empty; it must contain at least one value.
Example 13.19. In Predicate Examples
select p from Payment p where type(p) in (CreditCardPayment, WireTransferPayment) select c from Customer c where c.hqAddress.state in ('TX', 'OK', 'LA', 'NM') select c from Customer c where c.hqAddress.state in ? select c from Customer c where c.hqAddress.state in ( select dm.state from DeliveryMetadata dm where dm.salesTax is not null ) // Not JPQL compliant! select c from Customer c where c.name in ( ('John','Doe'), ('Jane','Doe') ) // Not JPQL compliant! select c from Customer c where c.chiefExecutive in ( select p from Person p where ... )
13.4.16. About HQL Ordering
ORDER BY
clause is used to specify the selected values to be used to order the result. The types of expressions considered valid as part of the order-by clause include:
- state fields
- component/embeddable attributes
- scalar expressions such as arithmetic operations, functions, etc.
- identification variable declared in the select clause for any of the previous expression types
ASC
(ascending) or DESC
(descending) to indicated the desired ordering direction.
Example 13.20. Order-by Examples
// legal because p.name is implicitly part of p select p from Person p order by p.name select c.id, sum( o.total ) as t from Order o inner join o.customer c group by c.id order by t