Forms of join syntax

• The where-clause is also optional.

As an example, to execute an HQLUPDATE, use theQuery.executeUpdate()method (the method is named for

those familiar with JDBC'sPreparedStatement.executeUpdate()): Session session = sessionFactory.openSession();

Transaction tx = session.beginTransaction();

String hqlUpdate = "update Customer c set c.name = :newName where c.name = :oldName"; // or String hqlUpdate = "update Customer set name = :newName where name = :oldName"; int updatedEntities = s.createQuery( hqlUpdate )

.setString( "newName", newName ) .setString( "oldName", oldName ) .executeUpdate();

tx.commit(); session.close();

HQLUPDATE_{statements, by default do not effect the Section 5.1.7, “version (optional)” or the Section 5.1.8,}

“timestamp (optional)” property values for the affected entities; this is in keeping with the EJB3 specification. However, you can force Hibernate to properly reset theversionortimestampproperty values through the use

of aversioned update. This is achieved by adding theVERSIONEDkeyword after theUPDATEkeyword. Session session = sessionFactory.openSession();

Transaction tx = session.beginTransaction();

String hqlVersionedUpdate = "update versioned Customer set name = :newName where name = :oldName"; int updatedEntities = s.createQuery( hqlUpdate )

.setString( "newName", newName ) .setString( "oldName", oldName ) .executeUpdate();

tx.commit(); session.close();

Note that custom version types (org.hibernate.usertype.UserVersionType) are not allowed in conjunction

with aupdate versionedstatement.

To execute an HQLDELETE, use the sameQuery.executeUpdate()method: Session session = sessionFactory.openSession();

Transaction tx = session.beginTransaction();

String hqlDelete = "delete Customer c where c.name = :oldName"; // or String hqlDelete = "delete Customer where name = :oldName"; int deletedEntities = s.createQuery( hqlDelete )

.setString( "oldName", oldName ) .executeUpdate();

tx.commit(); session.close();

Theintvalue returned by theQuery.executeUpdate()method indicate the number of entities effected by the

operation. Consider this may or may not correlate to the number of rows effected in the database. An HQL bulk operation might result in multiple actual SQL statements being executed, for joined-subclass, for example. The returned number indicates the number of actual entities affected by the statement. Going back to the example of joined-subclass, a delete against one of the subclasses may actually result in deletes against not just the table to which that subclass is mapped, but also the "root" table and potentially joined-subclass tables further down the inheritence hierarchy.

The pseudo-syntax forINSERTstatements is:INSERT INTO EntityName properties_list select_statement.

Some points to note:

• Only the INSERT INTO ... SELECT ... form is supported; not the INSERT INTO ... VALUES ... form. The properties_list is analogous to thecolumn speficiationin the SQLINSERTstatement. For entities in-

volved in mapped inheritence, only properties directly defined on that given class-level can be used in the properties_list. Superclass properties are not allowed; and subclass properties do not make sense. In other words,INSERTstatements are inherently non-polymorphic.

• 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. Note however that this might cause problems between Hibernate Types

which are equivalent as opposed to equal. This might cause issues with mismatches between a property

defined as a org.hibernate.type.DateType and a property 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 property, the insert statement gives you two options. You can either explicitly specify the id prop- erty in the properties_list (in which case its value is taken from the corresponding select expression) or omit it from the properties_list (in which case a generated value is used). This later 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. Note that for the purposes of this discussion, in-database generators are considered to beorg.hibernate.id.SequenceGenerator(and its subclasses) and any imple-

mentors of org.hibernate.id.PostInsertIdentifierGenerator. The most notable exception here is org.hibernate.id.TableHiLoGenerator, which cannot be used because it does not expose a selectable

way to get its values.

• For properties mapped as eitherversionortimestamp, the insert statement gives you two options. You can

either specify the property in the properties_list (in which case its value is taken from the corresponding se- lect expressions) or omit it from the properties_list (in which case the seed value defined by the org.hibernate.type.VersionTypeis used).

An example HQLINSERTstatement execution:

Session session = sessionFactory.openSession(); Transaction tx = session.beginTransaction();

String hqlInsert = "insert into DelinquentAccount (id, name) select c.id, c.name from Customer c where ..."; int createdEntities = s.createQuery( hqlInsert )

.executeUpdate(); tx.commit();

Hibernate is equipped with an extremely powerful query language that (quite intentionally) looks very much like SQL. But don't be fooled by the syntax; HQL is fully object-oriented, understanding notions like inherit- ence, polymorphism and association.

14.1. Case Sensitivity

Queries are case-insensitive, except for names of Java classes and properties. SoSeLeCTis the same assELEct

is the same as SELECT but org.hibernate.eg.FOO is not org.hibernate.eg.Foo and foo.barSet is not foo.BARSET.

This manual uses lowercase HQL keywords. Some users find queries with uppercase keywords more readable, but we find this convention ugly when embedded in Java code.

14.2. The from clause

The simplest possible Hibernate query is of the form:

from eg.Cat

which simply returns all instances of the classeg.Cat. We don't usually need to qualify the class name, since auto-importis the default. So we almost always just write:

from Cat

Most of the time, you will need to assign an alias, since you will want to refer to theCatin other parts of the

query.

from Cat as cat

This query assigns the aliascattoCatinstances, so we could use that alias later in the query. Theaskeyword

is optional; we could also write:

from Cat cat

Multiple classes may appear, resulting in a cartesian product or "cross" join.

from Formula, Parameter

from Formula as form, Parameter as param

It is considered good practice to name query aliases using an initial lowercase, consistent with Java naming standards for local variables (eg.domesticCat).

14.3. Associations and joins

from Cat as cat

inner join cat.mate as mate

left outer join cat.kittens as kitten

from Cat as cat left join cat.mate.kittens as kittens

from Formula form full join form.parameter param

The supported join types are borrowed from ANSI SQL • inner join

• left outer join

• right outer join

• full join(not usually useful)

Theinner join,left outer joinandright outer joinconstructs may be abbreviated. from Cat as cat

join cat.mate as mate

left join cat.kittens as kitten

You may supply extra join conditions using the HQLwithkeyword. from Cat as cat

left join cat.kittens as kitten with kitten.bodyWeight > 10.0

In addition, a "fetch" join allows associations or collections of values to be initialized along with their parent objects, using a single select. This is particularly useful in the case of a collection. It effectively overrides the outer join and lazy declarations of the mapping file for associations and collections. See Section 19.1, “Fetching strategies” for more information.

from Cat as cat

inner join fetch cat.mate left join fetch cat.kittens

A fetch join does not usually need to assign an alias, because the associated objects should not be used in the

whereclause (or any other clause). Also, the associated objects are not returned directly in the query results. In-

stead, they may be accessed via the parent object. The only reason we might need an alias is if we are recurs- ively join fetching a further collection:

from Cat as cat

inner join fetch cat.mate

left join fetch cat.kittens child left join fetch child.kittens

Note that thefetch construct may not be used in queries called using iterate() (though scroll() can be

used). Nor shouldfetchbe used together withsetMaxResults()orsetFirstResult()as these operations are

based on the result rows, which usually contain duplicates for eager collection fetching, hence, the number of rows is not what you'd expect. Nor mayfetchbe used together with an ad hocwithcondition. It is possible to

create a cartesian product by join fetching more than one collection in a query, so take care in this case. Join fetching multiple collection roles also sometimes gives unexpected results for bag mappings, so be careful about how you formulate your queries in this case. Finally, note thatfull join fetchandright join fetch

are not meaningful.

fetch the lazy properties immediately (in the first query) usingfetch all properties. from Document fetch all properties order by name

from Document doc fetch all properties where lower(doc.name) like '%cats%'

14.4. Forms of join syntax

HQL supports two forms of association joining:implicitandexplicit.

The queries shown in the previous section all use theexplicitform where the join keyword is explicitly used

in the from clause. This is the recommended form.

The implicit form does not use the join keyword. Instead, the associations are "dereferenced" using dot-

notation.implicitjoins can appear in any of the HQL clauses.implicitjoin result in inner joins in the result-

ing SQL statement.

from Cat as cat where cat.mate.name like '%s%'