如何在联接JPA/Ebean上定义多个条件 [英] How do I define multiple conditions on a join JPA/Ebean

问题描述

好的，可以说我有订单和物品表.这是在我的Order类中描述商品的方式:

Ok, lets say I have order and items tables. This is how items are described in my Order class:

@ManyToMany(cascade=CascadeType.PERSIST)
@JoinTable(name="MY_ORDER_ITEM_BRIDGE",
joinColumns=@JoinColumn(name="bridge_order_id",referencedColumnName = "order_order_id"),
inverseJoinColumns = @JoinColumn(name="bridge_item_id", referencedColumnName="item_item_id"))
private List<Item> items;

这很好.但是，让我们想象一下桥接表中有一个名为MY_ORDER_ITEM_BRIDGE.expiration_date的日期.

This works fine. But lets image that there is a date in the bridge table called MY_ORDER_ITEM_BRIDGE.expiration_date.

我想将定义更改为仅包含expiration_date尚未出现的项目.

I want to change the definition to only include Items with expiration_date that has not occurred yet.

所以我希望生成的SQL看起来像这样:

So I want the generated SQL to look something like:

SELECT *
FROM order o
join my_order_item_bridge b
on b.bridge_order_id = o.order_order_id
join item i
on i.item_item_id = b.bridge_item_id
where b.expiration_date < sysdate;

我正在使用Ebean，Play Framework 2.1.3.感谢您的帮助.

I am using Ebean, Play Framework 2.1.3. Thanks for any help.

更新:或者，这也是任何一个联接的第二个条件:

UPDATE: Alternatively, it could also be a second condition on either of the joins:

SELECT *
FROM order o
join my_order_item_bridge b
on b.bridge_order_id = o.order_order_id
and b.expiration_date < sysdate
join item i
on i.item_item_id = b.bridge_item_id

((如果可能，我想在类定义中而不是在原始SQL中执行此操作)

(If possible I'd like to do this in the class definition not in raw SQL)

推荐答案

问题是由ebean生成的桥表只能有两列:两个外键.

The problem is that ebean-generated bridge table can have two and only two columns: two foreign keys.

如果要在该字段中拥有另一个字段，则应将整个集合建模为三个实体:Order.java，Item.java和OrderItem.java.

If you want to have another field there you should model the whole set as three entities: Order.java, Item.java and OrderItem.java.

要强制执行完整性，您可以在OrderItem类中使用约束:

To enforce integrity you can use constraints in OrderItem class:

@Table(uniqueConstraints=@UniqueConstraint(columnNames={"order_order_id", "item_item_id"}))

这篇关于如何在联接JPA/Ebean上定义多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！