Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Handling Denormalized Schema with Eclipselink 
Hi Rohit,
Your write scenario is going to be challenging to implement using your denormalized schema. The reason is that EclipseLink will use the Identity elements of you Entities to determine which rows to insert/update on write. If you have both an Employee and a Department Object that use different identity fields, it is impossible for EclipseLink to know about the components of the identity of that row that belong to the other Entity. (i.e. department does not know Employee's id, and employee does not know Department's id)
The only configuration I can think of that will allow a write with exactly the schema you have proposed is to map everything to one object for write. (i.e. a EmployeeDepartment object) If the schema is more flexible, there are other solutions, so it depends on your reason for denomalization.
It might also be possible to construct two dynamic projects, one for read and one for write.
Assuming an EmployeeDepartment object, it should be fairly easy to write a query that will get a subset of data, like the "dept" string. 

  In JPQL: "Select distinct e.dept from EmployeeDepartment e where e.empId = :id"
In EclipseLink query API, you would create a ReportQuery for EmployeeDepartment add e.dept as a query item, and add the appropriate selection criteria. 

-Tom


Rohit Banga wrote:

  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 <http://wiki.eclipse.org/EclipseLink/Development/JPA/Dynamic#EclipseLink_Dynamic_Persistence> (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 <http://www.eclipse.org/eclipselink/api/2.1/org/eclipse/persistence/queries/ReadAllQuery.html> (select query) on the table returns a DynamicEntity <http://www.eclipse.org/eclipselink/api/2.1/org/eclipse/persistence/dynamic/DynamicEntity.html> 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 <http://www.eclipse.org/eclipselink/api/2.1/org/eclipse/persistence/mappings/OneToManyMapping.html> from the first type to the second one. Then when I query <http://www.eclipse.org/eclipselink/api/2.1/org/eclipse/persistence/sessions/Session.html#executeQuery%28org.eclipse.persistence.queries.DatabaseQuery%29> 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


------------------------------------------------------------------------

_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

Back to the top