CODEWITHSUNDEEP
javahibernatesubqueryhibernate-criteriadetachedcriteria
Marcelo Glasberg
Marcelo Glasberg

Reputation: 30919

Alias for properties that exist only in subclasses (Hibernate Criteria)

Consider the class Operation, and its 3 subclasses:

 class Operation {}
 class OpA extends Operation { }
 class OpB extends Operation { Account account; }
 class OpC extends Operation { Account account; }

Only OpB and OpC have a field called account.

I want to query for the account property:

session.createCriteria(Operation.class)
       .add(Restrictions.eq("account", account))
       .list();

This works. Hibernate ignores the fact that both Operation and OpA have no field called account, and returns the correct results for OpB and OpC.

However, now I also want to query for the account owner, and order by it. I then create the alias _account for account:

session.createCriteria(Operation.class)
       .add(Restrictions.eq("account", account))
       .createAlias("account", "_account")
       .add(Restrictions.eq("_account.owner", "John"))
       .addOrder(Order.asc("_account.owner"))
       .list();

This fails. There are 2 separate tables for the account (from OpB and OpC), so Hibernate complains:

Not unique table/alias: 'account1_'

My question: How can I query for both the account and the account owner, using only Criteria (no SQL, HQL) in the simplest possible way?

Upvotes: 5

Views: 4225

Answers (3)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154130

Try to add an AccountOperation abstract class as:

public abstract class AccountOperation extends Operation {
    public abstract Account getAccount();    
}

Now both OpB and OpC will extend AccountOperation.

Your query will become:

Criteria c = session.createCriteria(AccountOperation.class, "op")
    .createAlias("op.account", "ac")
    .add(Restrictions.eq("ac", account))
    .addOrder(Order.asc("ac.owner"))
    .list();

Upvotes: 1

Marcelo Glasberg
Marcelo Glasberg

Reputation: 30919

The two solutions below don't work. Both result in Not unique table/alias: 'account1_'.

I am posting them here to document what doesn't work, or maybe to give ideas to someone else, without complicating the question itself:

session.createCriteria(Operation.class)
       .createAlias("account", "_account")
       .add(
           Restrictions.and(
               Restrictions.or(
                   Property.forName("class").eq(OpB.class),
                   Property.forName("class").eq(OpC.class)),
               Restrictions.eq("account", account)
               Restrictions.eq("_account.owner", "John"))
               )
           )
       .list();

Without alias:

session.createCriteria(Operacao.class)
       .add(Restrictions.eq("account", account))
       .createCriteria("account")
       .add(Restrictions.eq("owner", "John"))
       .list();

Upvotes: 3

Marcelo Glasberg
Marcelo Glasberg

Reputation: 30919

This is my solution. It works only partially:

 Criterion subQ1 = Subqueries.propertyIn("id",
                       DetachedCriteria.forClass(OpB.class)
                            .add(Restrictions.eq("account", account))
                            .createAlias("account", "_account")
                            .add(Restrictions.eq("_account.owner", "John"))
                            .setProjection(Projections.groupProperty("id")));

 Criterion subQ2 = Subqueries.propertyIn("id",
                       DetachedCriteria.forClass(OpC.class)
                            .add(Restrictions.eq("account", account))
                            .createAlias("account", "_account")
                            .add(Restrictions.eq("_account.owner", "John"))
                            .setProjection(Projections.groupProperty("id")));

 session.createCriteria(Operacao.class)
        .add(Restrictions.disjunction()
            .add(subQ1)
            .add(subQ2))
        .list();

It works as long as I don't add order: .addOrder(Order.asc("_account.owner")).

The order cannot be added to the Subqueries, because it would have no effect. And it cannot be added to the Criteria, because it doesn't accept the alias.

Maybe there is a way to tweak this, or maybe this solution is too complicated and there is an easier one?

Upvotes: 4

Related Questions