[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
[eclipselink-users] Handling Denormalized Schema with Eclipselink

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.

Rohit Banga
Member Technical Staff
Oracle Server Technologies