Many-to-Many using FDS & Hibernate
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` (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.
`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;
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"?>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 :)
<!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>
<destination id="crm.employee.hibernate">Finally in my flex application I built my correponding .as classes:
<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>
package samples.crmThen 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!
{
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;
}
}
8 comments:
Hello Vic!
I am very interesting in FDS+Hibernate developing, but at this moment I have lots of troubles with lazy initializtion exception in hibernate. I have my java relations set to lazy in hibernate mapping, since usually I dont need to access all of the object's dependencies. But while trying to get data with fill method I got lazy init. exception for lazy associations.
Even if I do separate destinations in fds config with lazy set to false in metdata, I still got in FDS trying to serialize full object with all associations.
Did you see such situation before? If yes, plz give me some recipe :)
.. am tried to implement opensessioninviewfilter (hibernate support pattern to get session open during full request), but no luck :(
Found only one way - add additional VO classes in Java to hold only neccessary info and work with them in Flex, but this is not very good..
Thanks in any advance,
Igor Sazhnev
Igor,
I'll admit I've never really worried about configuring hibernate to handle lazy loading. I've played around with the settings in my data destinations quite a bit, and the lazy setting there seems to work quite well. Here are some examples of what I mean. In this first screenshot you see the results returned when both destinations are configured with lazy="false", like in the article above. The employee has a full list of events, and each event has a full list of employees. From what I can tell on the server console, FDS only loads each employee once, but creates references when needed. In the second screenshot I have changed the setting in the events destination to lazy. As you can see, the employee still loads all events, however the events themselves no longer contain a collection of employees. We can take this even further still and in this screenshot you can see what happens when I set both destinations to lazy. NOw the employee no longer contains a collection of the events.
Behind the scenes, looking at the console output again, it shows that even though only the employees are loaded, any record that is related to the employee has its referenceID retrieved and passed to the client by FDS. As you can see in this screenshot the client does receive this array of related record references in the result event's message header. How you get Flex to actually go get those records on demand is something that I will blog eventually, when I figure it out.
Just a quick note to add here. Though it seems quirky, Flex does seem to retrieve child collections automatically as they are needed. So if you load employees with both destinations set to lazy, and you click on an employee row in the datagrid, if there's a reference anywhere to selectedItem.events, Flex will go and get the related records for you, no extra coding required.
Thanks for reply.
Actually, think my troubles in my backend - i didnt used FDS hibernateassembler, but my POJO backed with hibernate. Looks like HibernateAssmbler.fetchObjectProperties do the trick and handles associations . Also maybe scope=application did their job. I'll try to go deeper into this to solve my problems.
Igor
I was able to display everything, but when I attempt to add an event to an employee I get LazyInitializationException.
I have set lazy="false" for both desinations in the data-management-config.xml and default-lazy="false" in the both hibernate mapping files.
No luck, still can't add an event to an employee.
Please, help
Thanks,
Yevgeniy
I am able to display both employees and events using the technique described in the article.
But I do get LazyInitializationException when I try to assign an event to the selected employee. The event already exists, but it is not related to the selected employee.
I have tried several things including setting default-lazy="false" in both hibernate mapping files and lazy="false" for both destinations in the data-management-config.xml, but I still can't get it to work.
Please, help.
Thank you very much
Galper
BlazeDS + Spring/Hibernate works perfectly for me. It's all about using Gilead which is much better than LCDS (pricey.. need to re-write hibernate mappings), dpHibernate (impossible to get it to work), GraniteDS (Seam only).
All you have to do in Gilead is basically extend LightEntity on each javabean
I usually don’t post in Blogs but your blog forced me to, amazing work.. beautiful …
Post a Comment