Is it possible to map trinary associations with annotations?

geirhe · **Posted:** Tue Apr 17, 2007 7:01 am

I have a mapping table between two tables "customers" and "accounts" that look like this:

Code:

CREATE TABLE `customers_roles` (
  `role` varchar(10) NOT NULL,
  `customers_id` int(10) NOT NULL,
  `accounts_id` int(10) NOT NULL,
  PRIMARY KEY (`role`,`customers_id`,`accounts_id`)
);

My first attempt at handling this was something like this:

Code:

public class Account {

   private Map<String, Set<Customer>> permissions;

}

The key in the map would map to the role column, accounts_id and customers_id would be a regular one-to-many-association.

I can't find any resources that describe how trinary associations are meant to be handled. I would be happy to be told to go RTFM or RTFE, but so far, I haven't been able to find anything covering this.

emmanuel · **Posted:** Tue Apr 17, 2007 10:26 am

ternary assoc are possible @MapKeyManyToMany
But a collection of collection is not possible

geirhe · **Posted:** Thu Apr 19, 2007 3:55 am

emmanuel wrote:

ternary assoc are possible @MapKeyManyToMany

I still can't find how that is meant to work documented anywhere, and there don't seem to be tests for this kind of use in the annotations project?

Not to worry, though, I have got it working now. Thanks for the help.

I used a mapping like this:

Code:

@Entity
public class Account {
   (...)
    @CollectionOfElements( fetch = FetchType.EAGER )
    @JoinTable(name="customers_roles")
    private Set<CustomerRole> customerPermission;
    (...)
}

Code:

@Embeddable
public class CustomerRole implements Comparable<CustomerRole> {
   (...)
   @Basic
   private String role;
   @ManyToOne( optional = false )
   @JoinColumn( name="customers_id" )
   private Customer customer;
   (...)
}

I found a halfway surprising behaviour in one of my tests. I had an entry in account, one in customer, and a link between them.

I had the test call the lookup functionality to read this information, then remove the @Id-mapped values in Account and Customer, then use the save functionality for Account to create a new account.

This gave me the expected three new entries, but for some reason, the "old" entry in customers_roles disappeared. If I rebuilt the customerPermission set with a "fresh" TreeSet so I got rid of the hibernate-extended set type, the old row was not hosed.

This is a test that probably won't happen in real life, but have I done something wrong in the mapping?

Schema, for your reference:

Code:

CREATE TABLE `accounts` (
  `id` int(10) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
);
CREATE TABLE `customers` (
  `id` int(10) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
);
CREATE TABLE `customers_roles` (
  `role` varchar(10) NOT NULL,
  `customers_id` int(10) NOT NULL,
  `accounts_id` int(10) NOT NULL,
  PRIMARY KEY (`role`,`customers_id`,`accounts_id`)
):

Quote:

But a collection of collection is not possible

Fair enough. It is really not a good match for a map anyway.

emmanuel · **Posted:** Fri Apr 20, 2007 10:47 am

geirhe wrote:

emmanuel wrote:

ternary assoc are possible @MapKeyManyToMany

I still can't find how that is meant to work documented anywhere, and there don't seem to be tests for this kind of use in the annotations project?

tests are in
org.hibernate.test.annotations.indexcoll

Doc is here
http://www.hibernate.org/hib_docs/annotations/reference/en/html_single/#entity-hibspec-collection-extratype
enhancement patch very welcome :-)

As of your surprising problem, I don't think I understood it, but you have to remember that the collection of elements lifecycle is completely bound to the owner entity, it can not live without it.[/url]