[
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