Multiple @ManyToMany sets from one join table

Question

Hi guys,

I'm mapping a proprietary database to Hibernate for use with Spring. In it, there are a couple of jointables that, for entity A and entity B have the following schema:

CREATE TABLE AjoinB (
  idA int not null,
  idB int not null,
  groupEnum enum ('groupC', 'groupD', 'groupE'),
  primary key(idA, idB, groupEnum)
);

As you can see, this indicates that there can be multiple A-B relationships that put them in different groups. I'd like to end up with, first line for entity A and second for entity B, the following sets

Set<B> BforGroupC, BforGroupD, BforGroupE;
Set<A> AforGroupC, AforGroupD, AforGroupE;

So far, I've only managed to put them in one set and disregard the groupEnum relationship attribute:

@ManyToMany(targetEntity=B.class, cascade={ CascadeType.PERSIST, CascadeType.MERGE } )
@JoinTable(name="AjoinB", joinColumns=@JoinColumn(name="idA"), inverseJoinColumns=@JoinColumn(name="idB") )
private Set<B> BforAllGroups;

and

@ManyToMany( mappedBy = "BforAllGroups", targetEntity = A.class )
private Set<A> AforAllGroups;

How can I make multiple sets where they belong either in groupC, groupD or groupE?

Cheers

Nik

Answer 1

To my knowledge, Hibernate cannot use such a "discriminator" column in the way that you want. Hibernate requires a join table for each of them.

Perhaps you might be able to define additional views on the table, showing each of the groupings?

Answer 2

I think the advise anytime you need to access a field in a link table is to make the link table an object and a hibernate entity in its own right. A would have a set of AtoB objects and AtoB would have a set of B objects. I have a simmilar situation where the link table has a user associated with the link.

select joinTable.b from A a 
left join a.AtoB joinTable
where joinTable.group = 'C'

It's not as elegant as having an implicit join done by hibernate, but it does give you the control you need.

Answer 3

If you're considering doing this, don't. Tables are cheap nowadays what's with the economy and all, so just create one per association; it'll be so much easier.

If you're bound by a legacy database and you can't change the structure of that table I would

1. Consider skaffman's solution first (+1, btw). Depending on your target database you may be able to write a trigger for your views that would insert adequate "discriminator" value.

2. If the above isn't possible in your DB, another solution is to use custom SQL for CRUD operations for your collections. Keep in mind that this will NOT work (e.g. your "discriminator value" won't get applied) for complex HQL queries involving your association as part of condition. You can also mix / match this with above - e.g. use views and use custom SQL for insert / delete.

3. If both of the above fail, go with "association as a separate entity" as suggested by framer8. That's going to be rather ugly (since we're assuming here you can't change your tables) due to composite keys and all extraneous code. It may, in fact, be impossible if any of your associations allows duplicates.