Restriction including a Sub

I'm trying to do a rather complex Criteria query in a project.

This particular example consists of 2 Database Tables: ORDER and PAYMENT. The ORDER Table has a field (ie an embedded Hibernate Entity called "Money" consisting of a property "amount" and an enum value for the currency) containing the total price of the order.

The PAYMENT table also has an amount column that contains the amount the customer has paid. Each order contains the order total price and a payment. An order doesn't have to have a payment, so the payment property can be null. The hibernate mapping itself works.

In my DAO I have a Criteria for the Order object object already containing some Restrictions. What I need to do now is to query for all Orders with a total order price between 2 values. This would look something like:

criteria.add(Restrictions.and(Restrictions.ge("orderTotalPrice.amount", amountFrom),
                    Restrictions.le("orderTotalPrice.amount", amountTo)));

Additionally, I need to get all Orders containing a payment with a payment amount between the same two values. If the paymentAmount would be a property of the Order entity, We could to something like this:

criteria.add(Restrictions.or(
                    Restrictions.and(Restrictions.ge("orderTotalPrice.amount", amountTo),
                        Restrictions.le("orderTotalPrice.amount", amountFrom)),
                    Restrictions.and(Restrictions.ge("paymentAmount.amount", amountFrom),
                        Restrictions.le("paymentAmount.amount", amountTo))));

My problem is, that the payment amount only exists inside a payment object inside the order entity. So to get the payment amount of an order, I would need something like "payment.paymentAmount.amount". This doesn't work of course, so usually I'd create a sub criteria like this:

 criteria.createCriteria("payment").add(
                    Restrictions.and(Restrictions.ge("paymentAmount.amount", amountFrom),
                        Restrictions.le("paymentAmount.amount", amountTo)));

Adding the above example and the first example to the criteria means that the amounts have to be the same for the order price and the payment amount. What I would need is an "OR" between those two Restrictions.

So my question is: Is it supported by the Criteria API to add a Criterion to a criteria object to represent an OR between another Criterion and a sub criteria?

I hope It is clear what my problem is. If anyone needs more information, don't hesitate to add a comment!

Thanks in advance!

PS: These are the (simplified) hibernate entity classes:

@Entity
public class Order {

    @Embedded
    private Money orderTotalPrice;

    @OneToOne(optional = true)
    private Payment payment;

    // Getters & Setters
}


@Entity
public class Payment {

    @Embedded
    private Money paymentAmount;

    // Getters & Setters
}


@Embeddable
public class Money {
    @Column
    private BigDecimal amount;

    // Getters & Setters
}

---

I'm not sure I have fully understood what you needed, but it's often easier to use aliases than to use sub-criteria:

Criteria c = session.createCriteria(Order.class, "order");
// join with payment:
c.createAlias("order.payment", "p");
// now you can refer to properties of order.payment using the "p" alias
c.add(Restrictions.or(
    Restrictions.and(Restrictions.ge("p.paymentAmount.amount", amountFrom),
                     Restrictions.le("p.paymentAmount.amount", amountTo)),
    Restrictions.and(Restrictions.ge("order.orderTotalPrice.amount", amountTo),
                     Restrictions.le("order.orderTotalPrice.amount", amountFrom))));

This is more natural to me, because it's almost a direct translation of the following HQL:

select order from Order order
join order.payment p
where (p.paymentAmount.amount >= :amountFrom and p.paymentAmount.amount <= :amountTo)
or (order.orderTotalPrice.amount >= amountFrom and order.orderTotalPrice.amount <= amountTo)

See http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#querycriteria-associations

链接地址: http://www.djcxy.com/p/36984.html

上一篇: 使用Hibernate标准匹配列表中的* ALL *项

下一篇: 限制包括一个小组