How to define several conditions for a JPA / Ebean connection

Question

Well, let's say I have a table of orders and items. This is how the elements in my Order class are described:

@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; 

It works great. But let the image, which is the date in the bridge table, be called MY_ORDER_ITEM_BRIDGE.expiration_date.

I want to change the definition to include only elements with expiration_date that have not yet occurred.

So I want the generated SQL to look something like this:

 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; 

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

UPDATE : alternatively, this may also be the second condition for any of the connections:

 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 

(If possible, I would like to do this in a class definition not in raw SQL)

Answer 1

The problem is that the bridge table created by ebean can have two and only two columns: two foreign keys.

If you want to have a different field, you must model the entire set as three objects: Order.java, Item.java and OrderItem.java.

To ensure integrity, you can use the constraints in the OrderItem class:

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

Answer 2

You can use SQL directly in ebean. See http://www.avaje.org/static/javadoc/pub/com/avaje/ebean/RawSql.html