Douglas McCarroll recently sent me an email asking if I'd had any luck getting Hibernate, Hibernate Tools for Eclipse and FDS to work with many to many relationships. I quickly threw together an example that I had built for a client a couple weeks ago, but in that particular case it wasn't a true many-to-many... I had a student, course and registration table but my registration table had it's own unique ID so really, it was just a combination of 2 one-to-many relationships. For most situations this would probably work however, as he pointed out, some clients might already have a db in place and would be unwilling to make a change just to accommodate the poor starving flex programmer.
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:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Dec 5, 2006 1:55:21 PM by Hibernate Tools 3.2.0.beta8 -->
<hibernate-mapping>
<class name="samples.crm.Employee" table="employee" catalog="crm">
<comment></comment>
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="native" />
</id>
<property name="firstName" type="string">
<column name="First_Name" length="45" not-null="true">
<comment></comment>
</column>
</property>
<property name="lastName" type="string">
<column name="Last_Name" length="45" not-null="true">
<comment></comment>
</column>
</property>
<property name="email" type="string">
<column name="Email" length="45" not-null="true">
<comment></comment>
</column>
</property>
<property name="phone" type="string">
<column name="Phone" length="45" not-null="true">
<comment></comment>
</column>
</property>
<property name="title" type="string">
<column name="Title" length="45" not-null="true">
<comment></comment>
</column>
</property>
<set name="events" inverse="true" table="eventemployee">
<key>
<column name="employeeID" not-null="true">
<comment></comment>
</column>
</key>
<many-to-many entity-name="samples.crm.Event">
<column name="eventID" not-null="true">
<comment></comment>
</column>
</many-to-many>
</set>
</class>
</hibernate-mapping>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Dec 5, 2006 1:55:21 PM by Hibernate Tools 3.2.0.beta8 -->
<hibernate-mapping>
<class name="samples.crm.Event" table="event" catalog="crm">
<comment></comment>
<id name="id" type="java.lang.Integer">
<column name="id" />
<generator class="native" />
</id>
<property name="name" type="string">
<column name="name" length="200" not-null="true">
<comment></comment>
</column>
</property>
<property name="eventDate" type="timestamp">
<column name="eventDate" length="19" not-null="true">
<comment></comment>
</column>
</property>
<property name="eventTime" type="string">
<column name="eventTime" length="45" not-null="true">
<comment></comment>
</column>
</property>
<set name="employees" inverse="true" table="eventemployee">
<key>
<column name="eventID" not-null="true">
<comment></comment>
</column>
</key>
<many-to-many entity-name="samples.crm.Employee">
<column name="employeeID" not-null="true">
<comment></comment>
</column>
</many-to-many>
</set>
</class>
</hibernate-mapping>
I also configured the Tools to create my domain code. What I found encouraging is that it only created the Employee.java and the Event.java classes, no EventEmployee.java class. Things were looking up! Ok, now it was time to configure the corresponding destinations in my data-management-config.xml file. I took a bit of an educated guess here, since the fds documentation surrounding the metadata tags is pretty weak... lucky for me I got it first try - I guess I spend way too much time here :)
<destination id="crm.employee.hibernate">
<adapter ref="java-dao" />
<properties>
<use-transactions>true</use-transactions>
<source>flex.data.assemblers.HibernateAssembler</source>
<scope>application</scope>
<metadata>
<identity property="id"/>
<many-to-many property="events"
destination="crm.event.hibernate" lazy="false" />
</metadata>
<network>
<session-timeout>20</session-timeout>
<paging enabled="false" pageSize="10" />
<throttle-inbound policy="ERROR" max-frequency="500"/>
<throttle-outbound policy="REPLACE" max-frequency="500"/>
</network>
<server>
<hibernate-entity>samples.crm.Employee</hibernate-entity>
<fill-configuration>
<use-query-cache>false</use-query-cache>
<allow-hql-queries>true</allow-hql-queries>
</fill-configuration>
</server>
</properties>
</destination>
<destination id="crm.event.hibernate">
<adapter ref="java-dao" />
<properties>
<use-transactions>true</use-transactions>
<source>flex.data.assemblers.HibernateAssembler</source>
<scope>application</scope>
<metadata>
<identity property="id"/>
<many-to-many property="employees"
destination="crm.employee.hibernate" lazy="false" />
</metadata>
<network>
<session-timeout>20</session-timeout>
<paging enabled="false" pageSize="10" />
<throttle-inbound policy="ERROR" max-frequency="500"/>
<throttle-outbound policy="REPLACE" max-frequency="500"/>
</network>
<server>
<hibernate-entity>samples.crm.Event</hibernate-entity>
<fill-configuration>
<use-query-cache>false</use-query-cache>
<allow-hql-queries>true</allow-hql-queries>
</fill-configuration>
</server>
</properties>
</destination>
Finally in my flex application I built my correponding .as classes:
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!
