Handling Denormalized Schema with Eclipselink
- by iamrohitbanga
Hello All
I have a denormalized table containing employee information. The fields are employee id, name and department name. The primary key is a composite one consisting of all three fields. An employee can belong to multiple departments. I want to read/write the objects in the table using the Eclipselink Dynamic Persistence API (which is infact a wrapper on top of JPA descriptors etc.).
Example Data:
1 e1 dep1
2 e1 dep2
3 e2 dep1
4 e2 dep3
5 e3 dep1
5 e3 dep2
5 e3 dep3
A normal ReadAllQuery (select query) on the table returns a DynamicEntity corresponding to each row in the table.
However I want to club all entities based on the emp id and return all the departments he belongs to as a list. I can merge the entities after retrieving them but if I can use some Eclipselink feature out of the box then it would be better.
One way to do the read is the following:
I create two dynamic types corresponding to employee:
Having id,name as the primary key
Having id, department as the primary key,
I create a OneToManyMapping from the first type to the second one. Then when I query the first type it does return the departments to which employee belongs as a list of DynamicEntity of the second type. This satisfies the read scenario.
Is there a better way of doing this?
Is this inherently supported by Eclipselink or JPA?
I cannot get the same dynamic type configuration working for the write scenario. This is because when I write the changes using the writeObject method of UnitOfWork, it generates insert queries which enter the following entries in the table
id name department
102 emp_102
102 st
102 dep_102
102 dep_102
102 dep_102
instead of:
id name department
102 emp_102 st
102 emp_102 dep_102
102 emp_102 dep_102
102 emp_102 dep_102
Is there any way I can get write to work with this schema using eclipselink? I want to avoid doing the heavy lifting of merging the rows for such a denormalized schema or generating each row before doing a write. Is there no clean way of doing this using Eclipselink or JPA?
Thanks in Advance.
