HQL 'with' clause in JPQL

Question

From Hibernate 3.6 documentation:

You may supply extra join conditions using the HQL with keyword.

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

This with clause allows to add a restriction on the JOIN condition (ON clause). Is there any such thing in JPQL?

When I run the following JPQL:

select c from ContainerDef c left join fetch c.displayState ds where c.id = 1 and ds.user.id = 2

The following SQL is generated:

select
        ...
    from
        CONTAINER_DEF containerd0_ 
    left outer join
        USER_CONTAINERDEF displaysta1_ 
            on containerd0_.CONTAINERDEF_ID=displaysta1_.CONTAINERDEF_ID 
    where
        containerd0_.CONTAINERDEF_ID=? 
        and displaysta1_.AUTHUSER_ID=?

What should really get generated is:

select
        ...
    from
        CONTAINER_DEF containerd0_ 
    left outer join
        USER_CONTAINERDEF displaysta1_ 
            on containerd0_.CONTAINERDEF_ID=displaysta1_.CONTAINERDEF_ID 
            and displaysta1_.AUTHUSER_ID=?
    where
        containerd0_.CONTAINERDEF_ID=?

I am sure I'm missing the right JPQL clause for HQL's with.

From Hibernate documentation: HQL also defines a WITH clause to qualify the join conditions. Again, this is specific to HQL; JPQL does not define this feature. docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/… — RinaldoDev

carbontax carbontax · Accepted Answer · 2012-10-15T20:37:31

But you can do it with Criteria API

    Criteria crit = session.createCriteria(Cat.class);
    crit.createAlias("kittens", "kitten", 
        Criteria.LEFT_JOIN, Restrictions.gt("weight", 10.0);

    List<Cat> catsWithFatKittens = crit.list();

This is the barely documented signature of Criteria.createAlias() with a Restriction object as the fourth parameter. It works so well that it is worth learning Criteria API to get the functionality.

HQL 'with' clause in JPQL

3 Answers