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

Question

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?

What would the desired SQL look like? – Predrag Maric Jan 21 '15 at 17:09 — Predrag Maric, Jan 21 '15 at 17:09
@Pedrag: Sorry, I have no idea. – Marcelo Glasberg Jan 21 '15 at 17:21 — Marcelo Glasberg, Jan 21 '15 at 17:21

score 3 · Answer 1 · answered Jan 16 '15 at 00:43

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?

score 3 · Answer 2 · answered Jan 16 '15 at 00:46

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();

Vlad Mihalcea · Answer 3 · 2015-01-22T21:06:56.683

1

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();

edited Jan 22 '15 at 21:06

answered Jan 19 '15 at 08:23

Vlad Mihalcea

142,745
71
566
911

Yes, my example is redundant, but it's just an example. My problem is the `Not unique table/alias: 'account1_'` exception. – Marcelo Glasberg Jan 21 '15 at 17:24
Sorry Vlad, I was out of office and couldn't test it before. Well, your example gives me this exception: `Not unique table/alias: 'ac1_'`. – Marcelo Glasberg Jan 22 '15 at 15:55
With the IN directive I get `java.lang.Class cannot be cast to java.lang.Integer` at `org.hibernate.type.descriptor.java.IntegerTypeDescriptor.unwrap`. – Marcelo Glasberg Jan 22 '15 at 19:37
Vlad, classes are being given numbers, for some reason, so something like `Restrictions.in("op.class", new Integer[]{1,2})` finds them (the corresponding numbers change each time). But even when it selects only classes that contain `account`, the same `Not unique table/alias: 'ac1_'` exception occurs. – Marcelo Glasberg Jan 22 '15 at 19:55
I can't actually create a new Interface. The subclasses I need are only known at runtime. Thank you for your help. – Marcelo Glasberg Jan 22 '15 at 23:20

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

3 Answers3