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:

1. Having id,name as the primary key
2. 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.