|
Now back in the summer I did attempt to get a true many-to-many relationship to work using Hibernate Tools. What I didn't realize at the time is that the tools didn't yet support this feature. Then i saw this link and decided to give it another go.
I will be using mysql for this example. It is based loosely off the crm db that ships with FDS. Here's the scripts for the 3 tables in play:
CREATE TABLE `crm`.`employee` ( `id` int(10) unsigned NOT NULL auto_increment, `First_Name` varchar(45) NOT NULL default '', `Last_Name` varchar(45) NOT NULL default '', `Email` varchar(45) NOT NULL default '', `Phone` varchar(45) NOT NULL default '', `Title` varchar(45) NOT NULL default '', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `crm`.`event` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(200) NOT NULL default '', `eventDate` datetime NOT NULL default '0000-00-00 00:00:00', `eventTime` varchar(45) NOT NULL default '', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `crm`.`eventemployee` ( `eventID` int(10) unsigned NOT NULL default '0', `employeeID` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`eventID`,`employeeID`), KEY `FK_eventemployee_1` (`employeeID`), CONSTRAINT `FK_eventemployee_1` FOREIGN KEY (`employeeID`) REFERENCES `employee` (`id`), CONSTRAINT `FK_eventemployee_2` FOREIGN KEY (`eventID`) REFERENCES `event` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;As you can see I have 3 tables, an Employee table, an Event table and an EventEmployee table. The EventEmployee table only has 2 fields, each a foreign key to the corresponding table.
The next step was to ensure I had the right version of the Hibernate Tools installed in Eclipse (3.2.0.beta7 or greater). I'm not going to spend any time on explaining how to configure the Hibernate Tools.. needless to say I got the Tool configured to reverse-engineer my db and it created these .hbm.xml files:
package samples.crm{ import mx.collections.ArrayCollection; [Managed] [RemoteClass(alias="samples.crm.Event")] public class Event{ public function Event() {} public var id:int; public var name:String; public var eventDate:Date; public var eventTime:String; public var employees:ArrayCollection; }}package samples.crm{ import mx.collections.ArrayCollection; [Managed] [RemoteClass(alias="samples.crm.Employee")] public class Employee { public function Employee() {} public var id:int; public var firstName:String; public var lastName:String; public var title:String; public var phone:String; public var email:String; public var events:ArrayCollection; }}Then I just created a quick application with 2 datagrids: one id="dg" with a dataProvider="{employees}" and the other id="childDg" with a dataProvider="{dg.selectedItem.events}" - by setting lazy to false on my many-to-many relationship in the destination, it's guaranteed that the child records will load automatically. You could also turn this around... create a datagrid that points to {events} and have the other point to {dg.selectedItem.employees}. By the time you get to this point you can do pretty much anything anyway. The real trick was getting here!