How do I describe a bridge table to Ebean?

Question

Lets say I have these tables:

ORDER: id
ITEM: id
ORDER_ITEM: order_id, item_id

The table: ORDER_ITEM is a bridge table between ORDER and ITEM.

How do I describe this threesome to Ebean?

I would prefer not to use raw SQL so that I can still create and update these entities.

UPDATE Sat Nov 9 02:32:40 UTC 2013

Ok, lets make this problem harder and more representative of my actual situation. The column names dont fit the convention:

ORDER: order_number
ITEM: item_number
ORDER_ITEM: my_order, my_item

Unsound

Answer 1

You don't have to create special bridge table yourself unless you want to have other fields there except foreign keys.

In Order class you should define field:

@ManyToMany
public List<Item> items;

In Item class:

@ManyToMany(mappedBy = "items")
public List<Order> orders;

And Ebean will generate bridge table for you.

And moreover:

You can have asymmetric relationship types between two classes:

@ManyToMany
@JoinTable(name="Order_Item")
public List<Item> items;

and

@ManyToOne
public Order order;

in case when order is an optional field in Item and you don't want to have a lot of empty fields in Item table.

UPDATE:

This will work for you. All the table and column names are now explicitly named by annotations:

@Entity
@Table(name="ITEM")
public class Item extends Model {
    @Id
    @Column(name="item_number")
    public Integer id;

    @ManyToMany(mappedBy = "items")
    public List<Order> orders;
}

and

@Entity @Table(name="ORDER") public class Order extends Model {
    @Id
    @Column(name="order_number")
    public Integer id;

    @ManyToMany
    @JoinTable(name="ORDER_ITEM",
            joinColumns=@JoinColumn(name="my_order",referencedColumnName = "order_number"),
            inverseJoinColumns = @JoinColumn(name="my_item", referencedColumnName="item_number"))
    public List<Item> items; }

The code is here: https://github.com/cosmolev/BridgeTable/tree/master/app/models

Mahout

I'm trying to describe a bridge table to Ebean. The bridge table already exists. This is an existing database that I am trying to build a play app on top of. – Unsound

That is the way to describe bridge table in Ebean. Don't forget to disable evolutions, otherwise Ebean tries to generate everything from scratch instead of working with you existing database. – Mahout

I would actually make some tests on empty DB to see what schema does Ebean produce. And once schema is the same as you existing one - switch to original DB. – Mahout

Ok, I think my problem was I was thinking I would only need two classes: Order and Item and there would be JPA annotations that would describe the bridge table. But now I'm thinking it isnt possible and I need to write a class for the bridge table class? – Unsound

Look at your existing bridge table. It's possible to write with only two classes unless you have other fields except two foreign keys in the bridge table. How many fields are there now? – Mahout

The bridge table contains only two columns- the keys to the other tables. [ORDER]-one-to-many->[ORDER_ITEM]-one-to-one->[ITEM] – Unsound

Ok. This is exactly what is described in my answer. From two classes Ebena will generate 3 tables. – Mahout

I updated the question. My column names dont fit the convention. I'm trying to use @@JoinTable and @@JoinColumn but its complaining "Could not find the matching foreign key for [order_number] in table[ITEM]?" Sorry I cant move this to chat I dont have enough rep. – Unsound

cosmolev I'm sorry to bug you this way but if you have a moment will you see: #20485575 – Unsound

It would be good to point out that (mappedBy = "items") only goes on one side of the mapping. I don't think it matters which side. – Danner