Hibernate - how to model triple join table as map?

Hi! I have a problem with mapping 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 (primary key is a pair of project_id and group_id). Database structure is given strictly from supervisor, so it would be great if i don't have to 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 that database.

ansaurus

tags:

views:

answers:

Hibernate - how to model triple join table as map?

related questions