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.