|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 AllI 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