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

3

5

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?

Deiform answered 16/1, 2015 at 0:39 Comment(2)
What would the desired SQL look like?Journeyman
@Pedrag: Sorry, I have no idea.Deiform
D
4

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?

Deiform answered 16/1, 2015 at 0:43 Comment(0)
D
3

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();
Deiform answered 16/1, 2015 at 0:46 Comment(0)
S
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();
Saturninasaturnine answered 19/1, 2015 at 8:23 Comment(5)
Yes, my example is redundant, but it's just an example. My problem is the Not unique table/alias: 'account1_' exception.Deiform
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_'.Deiform
With the IN directive I get java.lang.Class cannot be cast to java.lang.Integer at org.hibernate.type.descriptor.java.IntegerTypeDescriptor.unwrap.Deiform
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.Deiform
I can't actually create a new Interface. The subclasses I need are only known at runtime. Thank you for your help.Deiform

© 2022 - 2024 — McMap. All rights reserved.