HQL

HQL
Prev	Chapter 11. Querying data	Next

The chapter HQL of the Hibernate reference is really good. This is why I keep this chapter fairly short. Further query examples can be found in the source code of the mapping examples. The queries can be found in the example project DeveloperGuide in the class test.de.laliluna.example.TestQuery

Select objects, scalars, etc

With HQL you can select

one single class object (one JavaClub )
a list of class objects (all JavaClubs)
a list of an array of class objects( a list of an array, the array contains JavaClub and , multiple objects, object.children, data touple, create new objects (report item)

Simple select

   List results = session.createQuery("from JavaClub3 ").list();
      for (Iterator iter = results.iterator(); iter.hasNext();) {
         JavaClub3 club3 = (JavaClub3) iter.next();
         log.debug(club3);
      }

You do not need to write

createQuery("select c from JavaClub3 c").list()

This is only needed when your want to specify a specific object from joined object or a specific property. See below for further examples.

Select with a unique result

If you expect a unique result, you can call uniqueResult instead of list to get one object only.

JavaClub3 aClub = (JavaClub3) session.createQuery("from JavaClub3 where id=5")
   .uniqueResult();
log.debug("one single club: "+aClub);

Select with join returning multiple objects

If you use a join, you will receive multiple objects. The next example will show how to select only one of the objects.

results = session.createQuery("from JavaClub3 c left join c.members").list();
for (Iterator iter = results.iterator(); iter.hasNext();) {
   Object object[] = (Object[]) iter.next();
   log.debug("Club:  "+object[0]+ " Member: "+object[1]);
}

Select with join returning one object

If you use a join, you will receive multiple objects. If you name one object with select c you will get only one of them. This query will return multiple lines of the same Javaclub3 if multiple members exist.

results = session.createQuery(
   "select c from JavaClub3 c left join fetch c.members where "+
   "c.members.name='Peter'")
   .list();
   for (Iterator iter = results.iterator(); iter.hasNext();) {
      JavaClub3 javaClub3 = (JavaClub3) iter.next();
      log.debug("Club:  "+javaClub3);
   }

Select with join returning distinct results

The former example returned multiple entries of Javaclub3 if multiple members exist. You can get distinct results if you create a HashSet but you will loose your sort order. This approach was recommended in former times.

Set setResults = new HashSet(session.createQuery(
      "select c from JavaClub3 c left join c.members  order by c.name desc")
   .list());
for (Iterator iter = setResults.iterator(); iter.hasNext();)
{
   JavaClub3 club3 = (JavaClub3) iter.next();
   log.debug("Club:  " + club3 );
}

The new class DistinctRootEntityResultTransformer provides a better approach. It will keep your sort order.

results = session.createQuery(
      "select c from JavaClub3 c left join c.members  order by c.name desc")
   .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();)
{
   JavaClub3 club3 = (JavaClub3) iter.next();
   log.debug("Club:  " + club3 );
}

Selecting a single column (scalar values)

Simple types of Integer, String are called scalar values. You can select them explicitly.

results = session.createQuery("select c.id from JavaClub3 c").list();
for (Iterator iter = results.iterator(); iter.hasNext();) {
   Integer clubId = (Integer) iter.next();
   log.debug("Club Id:  "+clubId);
}

Selecting multiple columns (scalar values)

When you select multiple scalar you will get an array of objects.

results = session.createQuery("select c.id, c.name from JavaClub3 c").list();
for (Iterator iter = results.iterator(); iter.hasNext();) {
   Object object[] = (Object[]) iter.next();
   log.debug("Club Id:  "+object[0]+ " name: "+object[1]);
}

Selecting objects and scalar values

You can mix scalar and objects of course. You only have to keep in mind that you get an array holding different kind of classes.

results = session.createQuery(
   "select c, m.name from JavaClub3 c left join c.members m").list();
for (Iterator iter = results.iterator(); iter.hasNext();) {
   Object object[] = (Object[]) iter.next();
   JavaClub3 club3 = (JavaClub3) object[0];
   String name = (String) object[1];
   log.debug("Club:  "+club3+ " Member name: "+name);
}

Selecting selective properties of a class

If you have a class with a lot of properties but need only a few of them you can create an object with selective properties. This is only useful when reading data. You can not persist such an object.

results = session.createQuery(
      "select new JavaClub3(c.name) from JavaClub3 c").list();
for (Iterator iter = results.iterator(); iter.hasNext();)
{
   JavaClub3 element = (JavaClub3) iter.next();
   log.debug(element.getName());
}

Do not forget that you need a corresponding constructor in your class.

Simple where condition

If your where condition will limit the result to one object you can use the method uniqueResult() instead of list().

aClub = (JavaClub3) session.createQuery("from JavaClub3 c where c.id=5")
   .uniqueResult();
log.debug("one single club: "+aClub);

You are not obliged to use alias names but it is recommended to use them.

JavaClub3 aClub = (JavaClub3) session.createQuery("from JavaClub3 where id=5")
   .uniqueResult();
log.debug("one single club: "+aClub);

Walking through relations

Once your application becomes more complex, you will have to walk through deeper relations. The following picture shows a room having multiple cupboards with one lock each with one key each.

images:images/c_queries_walking_relations.jpg[]

You can easily walk through one to one relations:

results = session.createQuery("from Cupboard c where c.lock.key.name = ?")
   .setString(0, "old key")
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();)
{
   Cupboard   cupboard=  (Cupboard) iter.next();
   log.debug(cupboard);
}

If you want to walk through 1:n relations you cannot use something like:

session.createQuery("from Room r where r.cupboards.lock.key.name = ?")
   .setString(0, "old key")
   .list();

The cupboards attribute of room is a java.util.List and has no attribute lock. Instead you must use alias names for each many attribute.

/* walking through relations with 1:n */
results = session.createQuery(
   "select r from Room r left join r.cupboards c where c.lock.key.name = ?")
   .setString(0, "old key")
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();)
{
   Room   room=  (Room) iter.next();
   log.debug(room);
}

Where condition in a related object

We want all clubs where there is a member named Peter, so the where condition is in a related object. We do not need a join, Hibernate will join implicitly.

List results = session.createQuery(
    "select c from JavaClub3 c left join c.members m where m.name='Peter' ")
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();) {
   JavaClub3 club3 = (JavaClub3) iter.next();
   log.debug("Club with member named Peter: "+club3);
}

Where condition with parameters

You can pass any kind of parameters using the session.set…. methods. There are special methods for all kinds of Java types. Mapped classes can be set with setEntity

Query query = session
   .createQuery("select c from JavaClub3 c left join c.members m " +
                  "where c.id > ? and m.name like ?");
results = query.setInteger(0,5).setString(1, "Peter%").list();
for (Iterator iter = results.iterator(); iter.hasNext();) {
   JavaClub3 javaClub3 = (JavaClub3) iter.next();
   log.debug("Some clubs: "+javaClub3);
}

Conditions on collections

If you want to select all authors having written more than 2 books, you can use the size condition on a collection.

List<Author> authors = session.createQuery(
   "from Author a where size(a.books) > 2").list();
for (Author author : authors) {
    log.info(author);
}

To select entities with empty collections, you may use empty.

List<Author> authors = session.createQuery(
   "from Author a where a.books is empty").list();
for (Author author : authors) {
    log.info(author);
}

The collections can be searched as well for a specific entity. The example selects all books belonging to the crime story category.

BookCategory crimeStory = (BookCategory) session.createQuery(
   "from BookCategory b where b.name = ?")
   .setString(0, "Crime story")
   .uniqueResult();
List<Book> books = session.createQuery(
    "from Book b where ? in elements(b.bookCategories)")
    .setParameter(0, crimeStory).list();
for (Book book : books) {
    log.info(book);
}

An alternative solution is using member of.

List<Book> books = session.createQuery(
   "from Book b where :x member of b.bookCategories")
   .setParameter("x", crimeStory)
   .list();

The functions empty, elements or size are a comfortable short cut to avoid subqueries.

Where condition with mapped class

We use a mapped class as parameter in this query.

results = session.createQuery("from JavaClubMember3 m where m.club = ?")
   .setEntity(0,club3)
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();) {
   JavaClubMember3 member = (JavaClubMember3) iter.next();
   log.debug("Member in the club: "+club3+" is: "+member);
}

Where condition with mapped class on the multiple side

When the attribute you want to compare is a collection like JavaClub3.members then you must use in elements(…).

results = session.createQuery("from JavaClub3 c where ? in elements (c.members)" )
   .setEntity(0,member3)
   .list();
for (Iterator iter = results.iterator(); iter.hasNext();) {
 JavaClub3 javaClub3 = (JavaClub3) iter.next();
   log.debug("Member: "+member3+" is in the club: "+javaClub3);
}

All, In, Some, Exists, Any elements queries

These element can be used to compare a field to values of a subselect. The following samples will explain the usage.

This query selects all pools not being in Frankfurt which size is at least bigger than one pool in Frankfurt.

pools = session.createQuery(
   "from SwimmingPool p where p.city <> 'Frankfurt' and p.size > "+
   "any (select p2.size from SwimmingPool p2 where p2.city like 'Frankfurt')")
   .list();
for (Iterator iter = pools.iterator(); iter.hasNext();)
{
   SwimmingPool pool = (SwimmingPool) iter.next();
   log.debug(pool);
}

Some is a synonym for Any.

This query selects all pools not being in Frankfurt which size is bigger than all pools in Frankfurt.

pools = session.createQuery(
   "from SwimmingPool p where p.city <> 'Frankfurt' and p.size "+
   "> all (select p2.size from SwimmingPool p2 where p2.city like 'Frankfurt')")
    .list();
for (Iterator iter = pools.iterator(); iter.hasNext();)
{
   SwimmingPool pool = (SwimmingPool) iter.next();
   log.debug(pool);
}

This query selects all pools being the same size as a pool in Frankfurt.

pools = session.createQuery(
   "select p from SwimmingPool p where p.city <> 'Frankfurt' and p.size in "+
   "(select p2.size from SwimmingPool p2 where p2.city like 'Frankfurt')")
    .list();
for (Iterator iter = pools.iterator(); iter.hasNext();)
{
   SwimmingPool pool = (SwimmingPool) iter.next();
   log.debug(pool);
}

Elements can be used to refer to single elements of the n side of a relation. The next query selects all pool owners having a swimming pool.

pools = session.createQuery(
      "select o from PoolOwner o where exists elements(o.swimmingPools)")
      .list();

Finally, we select all PoolOwner having a swimming pool greater than 20.

pools = session.createQuery(
    "select o from PoolOwner o where elements(o.swimmingPools) in "+
   "(select p from SwimmingPool p where p.size > 20)")
    .list();

NamedQuery

A named query is defined once and reused multiple times. It has a name to be identified. Named queries are useful, if you need to reuse a complex query at various places. In addition they provide a slight performance advantage.

Sample code in mapping-examples-annotation package de.laliluna.other.namedquery.

The example below shows a named SQL query using a result set mapping.

@NamedQueries({
   @NamedQuery(name = "bookQuery", query =
   "from ComputerBook b where b.id > :minId and b.name = :name",
    hints = {@QueryHint(name = "org.hibernate.readOnly", value = "false")})
})
@Entity
public class ComputerBook {...

Using a named query is pretty simple.

List<ComputerBook> books  = session.getNamedQuery("bookQuery").list();
for (ComputerBook report : books) {
    System.out.println(report);
}

org.hibernate.annotation.NamedQuery and javax.persistence.NamedQuery

Both Hibernate and Java Persistence includes a @NamedQuery annotation. If you use the Hibernate API (= Session) you might consider to use the Hibernate version. It is easier to configure, as it provides variables for configurations like fetchSize, timeOut or the caching behaviour cacheMode of the query. JPA requires to use string constants for query hints.

Hibernate named query.

@org.hibernate.annotations.NamedQueries{
   @org.hibernate.annotations.NamedQuery(
   name = "foo", query =
   "from ComputerBook b where b.id > :minId and b.name = :name",
   flushMode = FlushModeType.AUTO,
    cacheable = true, cacheRegion = "", fetchSize = 20, timeout = 5000,
    comment = "A comment", cacheMode = CacheModeType.NORMAL,
    readOnly = true)})

Java Persistence named query.

@javax.persistence.NamedQueries({@javax.persistence.NamedQuery(
   name = "bookQuery", query =
   "from ComputerBook b where b.id > :minId and b.name = :name",
    hints = {
        @QueryHint(name = "org.hibernate.readOnly", value = "false"),
        @QueryHint(name = "org.hibernate.timeout", value = "5000")})
    })

Opinion on Named Queries

I personally do not like them, as I prefer to see the HQL or SQL code right in the code where it occurs without looking around for the definition of the named query.