Chapter 4. Hibernate Query Language 4.1. About Hibernate Query Language Introduction to Jakarta Persistence query language The Jakarta Persistence query language is a platform-independent object-oriented query language defined as part of the Jakarta Persistence specification . Jakarta Persistence query language is used to make queries against entities stored in a relational database. It is heavily inspired by SQL, and its queries resemble SQL queries in syntax, but operate against Jakarta Persistence entity objects rather than directly with database tables. Introduction to HQL The Hibernate Query Language (HQL) is a powerful query language, similar in appearance to SQL. Compared with SQL, however, HQL is fully object-oriented and understands notions like inheritance, polymorphism and association. HQL is a superset of Jakarta Persistence query language. An HQL query is not always a valid Jakarta Persistence query language query, but a Jakarta Persistence query language query is always a valid HQL query. Both HQL and Jakarta Persistence query language are non-type-safe ways to perform query operations. Criteria queries offer a type-safe approach to querying. 4.2. About HQL Statements Both HQL and Jakarta Persistence query language allow SELECT , UPDATE , and DELETE statements. HQL additionally allows INSERT statements, in a form similar to a SQL INSERT-SELECT . The following table shows the syntax in Backus-Naur Form (BNF) notation for the various HQL statements. Table 4.1. HQL Statements Statement Description SELECT The BNF for SELECT statements in HQL is: UPDATE The BNF for UPDATE statement in HQL is the same as it is in Jakarta Persistence query language. DELETE The BNF for DELETE statements in HQL is the same as it is in Jakarta Persistence query language. INSERT The BNF for INSERT statement in HQL is: There is no Jakarta Persistence query language equivalent to this. Warning Hibernate allows the use of Data Manipulation Language (DML) to bulk insert, update and delete data directly in the mapped database through the Hibernate Query Language (HQL). Using DML may violate the object/relational mapping and may affect object state. Object state stays in memory and by using DML, the state of an in-memory object is not affected, depending on the operation that is performed on the underlying database. In-memory data must be used with care if DML is used. About the UPDATE and DELETE Statements The pseudo-syntax for UPDATE and DELETE statements is: ( UPDATE | DELETE ) FROM? EntityName (WHERE where_conditions)? . Note The FROM keyword and the WHERE Clause are optional. The FROM clause is responsible for 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. The WHERE clause allows you to refine the list of instances returned. The result of execution of a UPDATE or DELETE statement is the number of rows that are actually affected (updated or deleted). Example: 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: 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(); The int value returned by the Query.executeUpdate() method indicates the number of entities within the database that were affected by the operation. Internally, the database might use multiple SQL statements to execute the operation in response to a DML Update or Delete request. This might be because of relationships that exist between tables and the join tables that need to be updated or deleted. For example, issuing a delete statement, as in the example above, may actually result in deletes being executed against not just the Company table for companies that are named with oldName , but also against joined tables. Therefore a Company table in a bidirectional, many-to-many relationship with an Employee table would also lose rows from the corresponding join table, Company_Employee , as a result of the successful execution of the example. The deletedEntries value contains a count of all the rows affected due to this operation, including the rows in the join tables. Important Care should be taken 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 transaction in a new persistence context or before fetching or accessing entities whose state might be affected by such operations. About the INSERT Statement HQL adds the ability to define INSERT statements. There is no Jakarta Persistence query language equivalent to this. The Backus-Naur Form (BNF) for an HQL INSERT statement is: The 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 The 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 can cause problems with Hibernate Types that are equivalent as opposed to equal . For example, this might cause mismatch issues between an attribute mapped as an 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. For the 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. For optimistic locking attributes, the insert statement again gives you two options. You can either specify the attribute in the 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: 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(); Example: 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(); If you do not supply the value for the id attribute using 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. 4.3. About HQL Ordering The results of the query can also be ordered. The 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 expression types HQL does not mandate that all values referenced in the order-by clause must be named in the select clause, but it is required by Jakarta Persistence query language. Applications desiring database portability should be aware that not all databases support referencing values in the order-by clause that are not referenced in the select clause. Individual expressions in the order-by can be qualified with either ASC (ascending) or DESC (descending) to indicate the desired ordering direction. Example: Order By 4.4. About Collection Member References References to collection-valued associations actually refer to the values of that collection. Example: Collection References In the example, the identification variable o actually refers to the object model type Order which is the type of the elements of the Customer#orders association. The example also shows the alternate syntax for specifying collection association joins using the IN syntax. Both forms are equivalent. Which form an application chooses to use is simply a matter of taste. 4.5. About Qualified Path Expressions It was previously stated that collection-valued associations actually refer to the values of that collection. Based on the type of collection, there are also available a set of explicit qualification expressions. Table 4.2. 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). Jakarta Persistence query language however, reserves this for use in the List case and adds KEY for the MAP case. Applications interested in Jakarta Persistence 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: Qualified Collection References 4.6. About HQL Functions HQL defines some standard functions that are available regardless of the underlying database in use. HQL can also understand additional functions defined by the dialect and the application. 4.6.1. About HQL Standardized Functions The following functions are available in HQL regardless of the underlying database in use. Table 4.3. HQL Standardized Functions Function Description BIT_LENGTH Returns the length of binary data. CAST Performs an SQL cast. The cast target should name the Hibernate mapping type to use. EXTRACT Performs an SQL extraction on datetime values. An extraction returns a part of the date/time value, for example, the year. 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. 4.6.2. About HQL Non-Standardized Functions Hibernate dialects can register additional functions known to be available for that particular database product. They would only be available when using that database or dialect. Applications that aim for database portability should avoid using functions in this category. Application developers can also supply their own set of functions. This would usually represent either custom SQL functions or aliases for snippets of SQL. Such function declarations are made by using the addSqlFunction method of org.hibernate.cfg.Configuration . 4.6.3. About the Concatenation Operation HQL defines a concatenation operator in addition to supporting the concatenation ( CONCAT ) function. This is not defined by Jakarta Persistence query language, so portable applications should avoid using it. The concatenation operator is taken from the SQL concatenation operator ( || ). Example: Concatenation Operation Example 4.7. About Dynamic Instantiation There is a particular expression type that is only valid in the select clause. Hibernate calls this "dynamic instantiation". Jakarta Persistence query language supports some of this feature and calls it a "constructor expression". Example: Dynamic Instantiation Example - Constructor So rather than dealing with the Object[] here we are wrapping the values in a type-safe Java object that will be returned as the results of the query. The class reference must be fully qualified and it must have a matching constructor. The class here does not need to be mapped. If it does represent an entity, the resulting instances are returned in the NEW state (not managed!). This is the part Jakarta Persistence query language supports as well. HQL supports additional "dynamic instantiation" features. First, the query can specify to return a List rather than an Object[] for scalar results: Example: Dynamic Instantiation Example - List The results from this query will be a List<List> as opposed to a List<Object[]>. HQL also supports wrapping the scalar results in a Map. Example: Dynamic Instantiation Example - Map The results from this query will be a List<Map<String,Object>> as opposed to a List<Object[]>. The keys of the map are defined by the aliases given to the select expressions. 4.8. About HQL Predicates Predicates form the basis of the where clause, the having clause and searched case expressions. They are expressions which resolve to a truth value, generally TRUE or FALSE , although boolean comparisons involving NULL values generally resolve to UNKNOWN . HQL Predicates Null Predicate Check a value for null. Can be applied to basic attribute references, entity references and parameters. HQL additionally allows it to be applied to component/embeddable types. Example: NULL Check Like Predicate Performs a like comparison on string values. The syntax is: The semantics follow that of the SQL like expression. The pattern_value is the pattern to attempt to match in the string_expression . Just like SQL, pattern_value can use _ (underscore) and % (percent) as wildcards. The meanings are the same. The _ matches any single character. The % matches any number of characters. The optional escape_character is used to specify an escape character used to escape the special meaning of _ and % in the pattern_value . This is useful when needing to search on patterns including either _ or % . Example: LIKE Predicate Between Predicate Analogous to the SQL BETWEEN expression. Perform an evaluation that a value is within the range of 2 other values. All the operands should have comparable types. Example: BETWEEN Predicate IN Predicate The IN predicate performs a check that a particular value is in a list of values. Its syntax is: The types of the single_valued_expression and the individual values in the single_valued_list must be consistent. Jakarta Persistence query language limits the valid types here to string, numeric, date, time, timestamp, and enum types. In Jakarta Persistence query language , 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. In HQL, 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 Jakarta Persistence query language limitations. The list of values can come from a number of different sources. In the constructor_expression and collection_valued_input_parameter , the list of values must not be empty; it must contain at least one value. Example: IN Predicate 4.9. About Relational Comparisons Comparisons involve one of the comparison operators - =, >, >=, <, ⇐, <>. HQL also defines != as a comparison operator synonymous with <>. The operands should be of the same type. Example: Relational Comparison Examples Comparisons can also involve subquery qualifiers - ALL , ANY , SOME . SOME and ANY are synonymous. The 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: ALL Subquery Comparison Qualifier Example The ANY / SOME qualifier resolves to true if the comparison is true for at least one of the values in the result of the subquery. It resolves to false if the subquery result is empty. 4.10. Bytecode enhancement Bytecode enhancement is used to manipulate the bytecode (.class) representation of a class for certain purposes, such as adding persistence-related capabilities. For a JBoss EAP instance, you can perform bytecode enhancement at runtime. Important Build-time bytecode enhancement is not supported and tested in JBoss EAP. Runtime enhancement of the domain model is only supported in a managed JPA environment because it uses the JPA-defined Service Provider Interface (SPI) for performing class transformations. The runtime enhancement is disabled, by default. To enable runtime enhancement, set the value of one or more of the following properties in the persistent unit based on your requirements: enableLazyInitialization : Enable this property when enhancement for lazy attribute loading is required. enableDirtyTracking : Enable this property when enhancement for self-dirty tracking is required. enableAssociationManagement : Enable this property when enhancement for bi-directional association management is required. Example: Setting properties to enable runtime enhancement <?xml version="1.0" encoding="UTF-8"?> <persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0"> <persistence-unit name="Example"> ... <properties> <property name="hibernate.enhancer.enableLazyInitialization" value="true" /> <property name="hibernate.enhancer.enableDirtyTracking" value="true" /> <property name="hibernate.enhancer.enableAssociationManagement" value="true" /> ... </properties> </persistence-unit> </persistence> Note Only annotated classes are supported for runtime enhancements. This means only the classes declared in the persistent unit are enhanced. 4.10.1. Lazy Attribute Loading Lazy attribute loading is a bytecode enhancement which allows you to tell Hibernate that only certain parts of an entity should be loaded upon fetching from the database, and when the other remaining parts should be loaded as well. This is different from proxy-based idea of lazy loading which is entity-centric where the entity's state is loaded at once as needed. With bytecode enhancement, individual attributes or groups of attributes are loaded as needed. Lazy attributes can be designated to be loaded together and this is called a lazy group . By default, all singular attributes are part of a single group. When one lazy singular attribute is accessed, all lazy singular attributes are loaded. Contrary to lazy singular group, lazy plural attributes are each a discrete lazy group. This behavior is explicitly controllable through the @org.hibernate.annotations.LazyGroup annotation. @Entity public class Customer { @Id private Integer id; private String name; @Basic( fetch = FetchType.LAZY ) private UUID accountsPayableXrefId; @Lob @Basic( fetch = FetchType.LAZY ) @LazyGroup( "lobs" ) private Blob image; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public UUID getAccountsPayableXrefId() { return accountsPayableXrefId; } public void setAccountsPayableXrefId(UUID accountsPayableXrefId) { this.accountsPayableXrefId = accountsPayableXrefId; } public Blob getImage() { return image; } public void setImage(Blob image) { this.image = image; } } In the example above, there are two lazy attributes: accountsPayableXrefId and image . Each of these attributes is part of a different fetch group. The accountsPayableXrefId attribute is a part of the default fetch group, which means that accessing accountsPayableXrefId will not force the loading of the image attribute, and vice versa. 4.10.2. In-line dirty tracking Hibernate supports difference-based tracking calculation for determining the entities that are changed in a persistence context. This means Hibernate checks the current state of an entity against the entity's last known state to keep track of any changes. The in-line dirty tracking capability is useful to track data types that can change their internal state, without performing state-difference calculations. Hibernate manipulates the bytecode of your classes to add the dirty tracking capability directly to the entity, enabling the entity to keep track of the attributes that are changed. 4.10.3. Bi-directional association management Hibernate helps to develop your applications close to the conventions of the Java language. The following example shows an incorrect Java usage: Example: Incorrect Java usage Order order = new Order(); LineItem lineItem = new LineItem(); order.getLineItems().add( lineItem ); // This blows up (NPE) in normal Java usage lineItem.getOrder.getname(); The following example shows a correct Java usage: Example: Correct Java usage Order order = new Order(); LineItem lineItem = new LineItem(); order.getLineItems().add( lineItem ); lineItem.setOrder( order ); // Now this is OK... lineItem.getOrder.getname(); The bi-directional association management capability allows the other side of a bi-directional association works when one side is manipulated.

Commands:
select_statement :: = [select_clause] from_clause [where_clause] [groupby_clause] [having_clause] [orderby_clause]
update_statement ::= update_clause [where_clause] update_clause ::= UPDATE entity_name [[AS] identification_variable] SET update_item {, update_item}* update_item ::= [identification_variable.]{state_field | single_valued_object_field} = new_value new_value ::= scalar_expression | simple_entity_expression | NULL
delete_statement ::= delete_clause [where_clause] delete_clause ::= DELETE FROM entity_name [[AS] identification_variable]
insert_statement ::= insert_clause select_statement insert_clause ::= INSERT INTO entity_name (attribute_list) attribute_list ::= state_field[, state_field ]*
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();
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();
insert_statement ::= insert_clause select_statement insert_clause ::= INSERT INTO entity_name (attribute_list) attribute_list ::= state_field[, state_field ]*
String hqlInsert = "insert into DelinquentAccount (id, name) select c.id, c.name from Customer c where ..."; int createdEntities = s.createQuery(hqlInsert).executeUpdate();
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();
// 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
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'
// 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
select 'Mr. ' || c.name.first || ' ' || c.name.last from Customer c where c.gender = Gender.MALE
select new Family( mother, mate, offspr ) from DomesticCat as mother join mother.mate as mate left join mother.kittens as offspr
select new list(mother, offspr, mate.name) from DomesticCat as mother inner join mother.mate as mate left outer join mother.kittens as offspr
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
// 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_expression ::= string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]
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 '|'
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'
in_expression ::= single_valued_expression [NOT] IN single_valued_list single_valued_list ::= constructor_expression | (subquery) | collection_valued_input_parameter constructor_expression ::= (expression[, expression]*)
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 Jakarta Persistence query language compliant! select c from Customer c where c.name in ( ('John','Doe'), ('Jane','Doe') ) // Not Jakarta Persistence query language compliant! select c from Customer c where c.chiefExecutive in ( select p from Person p where )
// 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
// 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 )
<?xml version="1.0" encoding="UTF-8"?> <persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0"> <persistence-unit name="Example"> <properties> <property name="hibernate.enhancer.enableLazyInitialization" value="true" /> <property name="hibernate.enhancer.enableDirtyTracking" value="true" /> <property name="hibernate.enhancer.enableAssociationManagement" value="true" /> </properties> </persistence-unit> </persistence>
@Entity public class Customer { @Id private Integer id; private String name; @Basic( fetch = FetchType.LAZY ) private UUID accountsPayableXrefId; @Lob @Basic( fetch = FetchType.LAZY ) @LazyGroup( "lobs" ) private Blob image; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public UUID getAccountsPayableXrefId() { return accountsPayableXrefId; } public void setAccountsPayableXrefId(UUID accountsPayableXrefId) { this.accountsPayableXrefId = accountsPayableXrefId; } public Blob getImage() { return image; } public void setImage(Blob image) { this.image = image; } }
Order order = new Order(); LineItem lineItem = new LineItem(); order.getLineItems().add( lineItem ); // This blows up (NPE) in normal Java usage lineItem.getOrder.getname();
Order order = new Order(); LineItem lineItem = new LineItem(); order.getLineItems().add( lineItem ); lineItem.setOrder( order ); // Now this is OK lineItem.getOrder.getname();