Modelling a triple-join column in Hibernate an alternative way

Hi! I have a problem with mapping a triple join table as a map with Hibernate using annotations. I have 3 tables: project, group, role and triple join table project_group_role with columns project_id, group_id, role_id (the primary key is a pair of project_id and group_id). Database structure is given strictly from supervisor, so I'd rather not change it. I have to assign groups to projects with specific role so I could use maps:

In class Project:

   Map<Group, Role> groupRoles;

In class Group:

   Map<Project, Role> projectRoles;

I tried to do sth like this:

In class Project:

@ManyToMany(targetEntity = Role.class)
@MapKeyClass(Group.class)
@JoinTable(name = "project_group_role", joinColumns = @JoinColumn(name = "project_id"),
            inverseJoinColumns = @JoinColumn(name = "role_id"))
@MapKeyJoinColumn(name = "group_id")
Map<Group, Role> groupRoles;

And analogically in class Role, but it doesn't work.

Thank you for any help!

I'm attaching SQL code of database tables connected with problem.

     CREATE TABLE IF NOT EXISTS `project` (
     `project_id` int(11) unsigned NOT NULL auto_increment,
     `name` varchar(60) NOT NULL,
     `shortname` varchar(25) default NULL,
     `homepage` varchar(60) default NULL,
     `description` text,
     PRIMARY KEY  (`project_id`),
     UNIQUE KEY `name` (`name`),
     KEY `Projects_Name` (`name`),
     KEY `FKED904B19B6CF56DA` (`project_id`)
   ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;

   CREATE TABLE IF NOT EXISTS `group` (
     `group_id` int(11) unsigned NOT NULL auto_increment,
     `name` varchar(25) NOT NULL,
     `password` varchar(40) default NULL,
     `subscribable` tinyint(1) default NULL,
     PRIMARY KEY  (`group_id`),
     KEY `group_name` (`name`)
   ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

   CREATE TABLE IF NOT EXISTS `role` (
     `role_id` int(11) unsigned NOT NULL auto_increment,
     `name` varchar(25) NOT NULL,
     PRIMARY KEY  (`role_id`)
   ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

   CREATE TABLE IF NOT EXISTS `project_group_role` (
     `project_id` int(11) NOT NULL,
     `group_id` int(11) NOT NULL,
     `role_id` int(11) NOT NULL,
     PRIMARY KEY  (`project_id`,`group_id`),
     KEY `FK518ACB1C7FD34713` (`role_id`),
     KEY `FK518ACB1C9B33F950` (`group_id`),
     KEY `FK518ACB1CB8565590` (`project_id`),
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

When I tried to model it in the way i wrote in the beginning, my database automaticly transformed to sth like this (4 strenge columns) and adding new records started to throw Flush Session Exception.

   CREATE TABLE IF NOT EXISTS `project_group_role` (
     `project_id` int(11) NOT NULL,
     `group_id` int(11) NOT NULL,
     `role_id` int(11) NOT NULL,
     `projectRoles_role_id` bigint(20) default NULL,
     `mapkey_project_id` bigint(20) default NULL,
     `groupRoles_role_id` bigint(20) default NULL,
     `mapkey_group_id` bigint(20) default NULL,
     PRIMARY KEY  (`project_id`,`group_id`),
     KEY `FK518ACB1C7FD34713` (`role_id`),
     KEY `FK518ACB1C9B33F950` (`group_id`),
     KEY `FK518ACB1C7D52A149` (`projectRoles_role_id`),
     KEY `FK518ACB1C3E27BFCC` (`mapkey_project_id`),
     KEY `FK518ACB1C8B0E2BA3` (`groupRoles_role_id`),
     KEY `FK518ACB1C3D92C28C` (`mapkey_group_id`),
     KEY `FK518ACB1CB8565590` (`project_id`),
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Thank you in advance for helping me to model my database.

ansaurus

tags:

views:

answers:

Modelling a triple-join column in Hibernate an alternative way

related questions