Using hibernate criteria query for joining a not directly referenced entity

Question

I would like to run a criteria query on an entity using a join with another entity. But from Java/Hibernate point of view there is no reference to this second entity, there is only one from this second to the first entity.

To make it understandable, here is the cat'ifized situation:

===== Cat.java =====
@Id private long id; 

@Column( unique = true, nullable = false )
private String name;

===== Kitten.java =====
@Id  private long id; 

@Column( unique = true, nullable = false )
private String name;

@OneToOne(optional = false)
private Cat mother;         //only one child per mother ...

Now I want to query Cats with Restrictions and OrderBy for Kitten.

session.createCriteria(Cat.class)
   .createAlias(???? , "kitten")
   .add( Restrictions.like( "kitten.name", "name_of_a_kitten" )
   .addOrder( Order.asc( "kitten.name" ) ) 
   .list();

First I thought of using subqueries but I couldn't find a solution for ordering and subquerys aren't as readable as an alias in my eyes.

Cat can't be changed so there is no way to change probably to bi-directionallity.

How can I achive the query?

Btw. I'm using hibernate 4.2.

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

I think it's not possible to achieve your requirement by simple hibernate criteria query.

Criteria API can "select" only root entity and you require a Cat as a root entity. Unfortunately Cat entity has not reference to Kitten, so it's not possible to join it.

From my point of view, you have only two options:

1) HQL

if you are not pinned to criteria api, simply use hql.

final List<Cat> cats =  session.createQuery("select c from Kitten k join k.mother c where k.name=:name order by k.name")
    .setParameter("name", "name_of_a_kitten").list();

2) extract Cat from Kitten collection explicitly

Select Kitten to collection

final List<Kitten> kittens = session.createCriteria(Kitten.class)
                    .add(Restrictions.like("name", "name_of_a_kitten"))
                    .addOrder(Order.asc("name"))
                    .list();

and transform to cat collection, e.g. using lambda-j (OnToOne relation is not lazy,so Cat is loaded )

final List<Cat> cats =  extract(kittens, on(Kitten.class).getMother());

Subquery

As you've noted, using subquery is not useful because of impossibility for ordering by Kitten attributes

    final DetachedCriteria q = DetachedCriteria.forClass(Kitten.class)
            .createAlias("mother", "c")
            .add(Restrictions.like("name", "name_of_a_kitten"))
            .setProjection(Projections.property("c.id"));

    final List<Cat> cats =  session.createCriteria(Cat.class)
            .add(Property.forName("id").in(q)).list();  //not possible order by Kitten attributes

Using hibernate criteria query for joining a not directly referenced entity

1 Answers1