Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Development/2.1/AdvancedJPA Queries/DownCast"
(→Expression Support for Downcast) |
(→Enhancement: Query Down Cast) |
||
Line 22: | Line 22: | ||
e.g. "select p from Employee e join e.projects p where type(p) = LargeProject" can be used to retrieve all the LargeProjects (Subclass of Project) from Employee. | e.g. "select p from Employee e join e.projects p where type(p) = LargeProject" can be used to retrieve all the LargeProjects (Subclass of Project) from Employee. | ||
+ | |||
+ | === JPQL Extensions to use Downcast === | ||
+ | |||
+ | This part of the feature will be considered only after the Expression support is complete. | ||
+ | |||
+ | '''Note: Any JPQL below is used only to illustrate what queries are being run - the actual JPQL design will be completed later.''' | ||
+ | |||
+ | There are two ways this functionality could be written into JPQL. | ||
+ | |||
+ | * In the FROM clause: | ||
+ | ** e.g. select x from X x join x.y y downcast as Z z where z.attribute = value | ||
+ | * In the WHERE clause: | ||
+ | ** e.g. select x from X x join x.y y where downcast(y, Z).attribute = value | ||
+ | |||
+ | Other suggestions are welcome | ||
=== Expression Support for Downcast === | === Expression Support for Downcast === | ||
Line 34: | Line 49: | ||
''In this query Employee has a xToMany mapping to Project. LargeProject is a subclass of Project and the "budget" attribute is contained on LargeProject.'' | ''In this query Employee has a xToMany mapping to Project. LargeProject is a subclass of Project and the "budget" attribute is contained on LargeProject.'' | ||
− | * An exception will be thrown | + | * An exception will be thrown at query execution time if the class that is downcast to is not a subclass of the class of the query key being downcast. |
* Downcasts are only allowed on ObjectExpressions (QueryKeyExpression and ExpressionBuilder). The parent expression of a downcast must be an ObjectExpression | * Downcasts are only allowed on ObjectExpressions (QueryKeyExpression and ExpressionBuilder). The parent expression of a downcast must be an ObjectExpression | ||
* Downcasts use outer joins to join any additional tables required by the query | * Downcasts use outer joins to join any additional tables required by the query | ||
Line 42: | Line 57: | ||
** Should be written as: ''select f from Foo f join f.bars b downcast as BarSubclass s where type(s) = BarSubclass And s.subclassAttribute = "value"'' by users that wish to enforce the type. | ** Should be written as: ''select f from Foo f join f.bars b downcast as BarSubclass s where type(s) = BarSubclass And s.subclassAttribute = "value"'' by users that wish to enforce the type. | ||
** Generally, the joins will be adequate to enforce the type, but in cases where multiple subclasses use exactly the same table set, the TYPE expression may be required. | ** Generally, the joins will be adequate to enforce the type, but in cases where multiple subclasses use exactly the same table set, the TYPE expression may be required. | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
=== Open Issues === | === Open Issues === | ||
− | * Our | + | * Our JPA models currently do not run on Informix and since the testing for this feature is run through our JPA models InformixPlatform will not be exhaustively tested. Some work will be done to allow outer joins associated with this feature to work on Informix, but they will bevery lightly tested. |
== Design == | == Design == | ||
− | |||
=== API === | === API === | ||
Line 71: | Line 71: | ||
* downcast(Class downcastClass) | * downcast(Class downcastClass) | ||
** The parent expression for this must be an ObjectExpression (ExpressionBuilder or QueryKeyExpression) and an exception will be thrown if this is not the case when the downcast method is called. | ** The parent expression for this must be an ObjectExpression (ExpressionBuilder or QueryKeyExpression) and an exception will be thrown if this is not the case when the downcast method is called. | ||
− | ** An exception will be thrown at query | + | ** An exception will be thrown at query execution time if either downcastClass is not part of a mapped inheritance relationship or if the inheritance uses Table Per Class Inheritance |
** If downcastClass is not either equal to the class described by the parent or a subclass of the class described by the parent, an exception will be thrown when the query is executed | ** If downcastClass is not either equal to the class described by the parent or a subclass of the class described by the parent, an exception will be thrown when the query is executed | ||
* isDowncastExpression() | * isDowncastExpression() | ||
Line 105: | Line 105: | ||
=== DowncastExpression === | === DowncastExpression === | ||
+ | |||
* A new class, DowncastExpression will be added as a subclass of ObjectExpression | * A new class, DowncastExpression will be added as a subclass of ObjectExpression | ||
** The descriptor for DowncastExpression will be the descriptor of the class it downcasts too | ** The descriptor for DowncastExpression will be the descriptor of the class it downcasts too |
Revision as of 10:57, 5 April 2010
Enhancement: Query Down Cast
This page captures the requirements, design, and existing functionality for bug 259266 to enable EclipseLink JPA/ORM developers to define queries on inheritance hierarchies with down casting to specific classes. RequirementsMust Have
Nice to Have
High Level DesignLimiting results by typeExtensions to the expression framework to limit the results to those of a specific subclass have already been implemented as part of the JPA 2.0 effort. Expression.type(Class) is available in the expression framework and equivalent functionality is available in JPQL. e.g. "select p from Employee e join e.projects p where type(p) = LargeProject" can be used to retrieve all the LargeProjects (Subclass of Project) from Employee. JPQL Extensions to use DowncastThis part of the feature will be considered only after the Expression support is complete. Note: Any JPQL below is used only to illustrate what queries are being run - the actual JPQL design will be completed later. There are two ways this functionality could be written into JPQL.
Other suggestions are welcome Expression Support for DowncastWe will implement Expression.downcast(Class). The following is an example of how one could use it: ReadAllQuery raq = new ReadAllQuery(Employee.class); Expression criteria = raq.getExpressionBuilder().anyOf("projects").downcast(LargeProject.class).get("budget").greaterThan(100); raq.setSelectionCriteria(criteria); List resultList = query.getResultList(); In this query Employee has a xToMany mapping to Project. LargeProject is a subclass of Project and the "budget" attribute is contained on LargeProject.
Open Issues
DesignAPIWe will add the following public API: Expression
InheritancePolicy
Imagine the following hierarchy Project (Table: PROJ, Key: PROJ_ID) LargeProject subclass of Project (Table: L_PROJ, FK: PROJ_ID1) SuperProject subclass of LargeProject (Table S_PROJ, FK: PROJ_ID2) Downcast join expression will be an expression representing: L_PROJ.PROJ_ID1 = PROJ.PROJ_ID && S_PROJ.PROJ_ID2 = PROJ.PROJ_ID
L_PROJ -> expression for: L_PROJ.PROJ_ID1 = PROJ.PROJ_ID S_PROJ -> expression for: S_PROJ.PROJ_ID2 = PROJ.PROJ_ID downcastTableRelationships will contain L_PROJ -> PROJ S_PROJ -> PROJ DowncastExpression
Example SQLThe following query: Select e from Employee e join e.projects project Will currently produce the following sql: SELECT <select list> FROM CMP3_EMPLOYEE t1 LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID), CMP3_EMP_PROJ t4, CMP3_PROJECT t3, CMP3_SALARY t2 WHERE ((t2.EMP_ID = t1.EMP_ID) AND ((t4.EMPLOYEES_EMP_ID = t1.EMP_ID) AND (t3.PROJ_ID = t4.projects_PROJ_ID)))
Expression criteria = project.downcast(LargeProject.class).get("budget").greaterThan(100); raq.setSelectionCriteria(criteria); The following SQL will be produced: SELECT <select list> FROM CMP3_PROJECT t3 LEFT OUTER JOIN CMP3_LPROJECT t4 ON (t4.PROJ_ID = t3.PROJ_ID),CMP3_EMPLOYEE t1 LEFT OUTER JOIN CMP3_DEPT t0 ON (t0.ID = t1.DEPT_ID), CMP3_EMP_PROJ t5, CMP3_SALARY t2 WHERE (((t4.BUDGET > ?) AND (t2.EMP_ID = t1.EMP_ID)) AND ((t5.EMPLOYEES_EMP_ID = t1.EMP_ID) AND (t3.PROJ_ID = t5.projects_PROJ_ID))) bind => [100.0] The changes as listed above in bold. |
Work-Arounds
Since this feature will take significant development effort the following suggest work-around options are provided to assist users who require this functionality. If having this is a show-stopper for your project please vote for and add your feedback to bug 259266.
Single Class Results
While this may seem obvious it often important to point out all potential solutions. If the query you are executing will only be returning a single type from the inheritance hierarchy then it is important that that be the target type of the query. This will allow you to access all mapped attributes in this class and its mapped parent classes.
Accessing un-mapped attributes using QueryKey
When your inheritance hierarchy leverages a common table for multiple mapped classes in the hierarchy it is possible to query for attributes that are not visible in the class you are querying for through the use of query keys.
Example
In this example we'll map a simple inheritance hierarchy of class A having two subclasses B and C. Each class will have its own value and they will all be mapped to a single table.
@Entity @Table(name="DOWNCAST_SIMPLE_A") @Inheritance(strategy=InheritanceType.SINGLE_TABLE) @DiscriminatorColumn(name="INH_TYPE",discriminatorType=DiscriminatorType.CHAR) public abstract class A { @Id private int id; private String aValue; // accessor methods } @Entity @DiscriminatorValue("B") public class B extends A{ private String bValue; // accessor methods } @Entity @DiscriminatorValue("C") public class C extends A { private String cValue; // accessor methods }
Based on this mapped entity model the generated schema looks like:
CREATE TABLE DOWNCAST_SIMPLE_A ( ID NUMBER(10) NOT NULL, INH_TYPE VARCHAR2(31) NULL, AVALUE VARCHAR2(255) NULL, BVALUE VARCHAR2(255) NULL, CVALUE VARCHAR2(255) NULL, PRIMARY KEY (ID))
Now to build a heterogenous query for A using bValue and cValue I need to define query keys on A to make this fields visible.
// Configure the use of a customizer on the entity class @Customizer(ACustomizer.class) public abstract class A { // The customizer adds the direct query keys public class ACustomizer implements DescriptorCustomizer { public void customize(ClassDescriptor descriptor) throws Exception { descriptor.addDirectQueryKey("bValue", "BVALUE"); descriptor.addDirectMapping("cValue", "CVALUE"); } }
Now you can write your query:
ReadAllQuery raq = new ReadAllQuery(A.class); ExpressionBuilder eb = raq.getExpressionBuilder(); raq.setSelectionCriteria(eb.get("aValue").like("A%").and(eb.get("bValue").like("bValue")).and(eb.get("cValue").like("CVALUE"))); // Wrap in JPA Query Query query = JpaHelper.createQuery(raq, em); // Execute Query List<A> results = query.getResultList();
The resulting SQL appears as:
SELECT ID, INH_TYPE, AVALUE, CVALUE, BVALUE FROM DOWNCAST_SIMPLE_A WHERE (((AVALUE LIKE ?) AND (BVALUE LIKE ?)) AND (CVALUE LIKE ?))
Querying JOINED Hierarchies using Joining
It is possible to query joined hierarchies as well:
Query query = em.createQuery("Select a from JoinedA a, JoinedB b, JoinedC c WHERE (b.bValue LIKE 'B%' and b = a) OR (c.cValue LIKE 'C%' and c = a)"); // Execute Query List<JoinedA> results = query.getResultList();
The result SQL is:
[EL Fine]: Connection(27978063)--SELECT DISTINCT t0.INH_TYPE FROM DOWNCAST_JOINED_C t4, DOWNCAST_JOINED_A t3, DOWNCAST_JOINED_A t2, DOWNCAST_JOINED_B t1, DOWNCAST_JOINED_A t0 WHERE ((((t1.BVALUE LIKE ?) AND (t2.ID = t0.ID)) OR ((t4.CVALUE LIKE ?) AND (t2.ID = t3.ID))) AND (((t1.ID = t0.ID) AND (t0.INH_TYPE = ?)) AND ((t4.ID = t3.ID) AND (t3.INH_TYPE = ?)))) bind => [B%, C%, B, C]
The challenge here is that the joining limits the results incorrectly for the OR condition and fails in some test cases.
Querying JOINED Hierarchies using IN
Another solution is to use an IN operator on each subclass you are interested in against a single part PK:
Select a from JoinedA a WHERE a.id IN (SELECT b.id FROM JoinedB b WHERE b.bValue LIKE 'B%') OR a.id IN (SELECT c.id FROM JoinedC c WHERE c.cValue LIKE 'C%')
the result SQL for this scenario is:
[EL Fine]: Connection(14707008)--SELECT DISTINCT t0.INH_TYPE FROM DOWNCAST_JOINED_A t0 WHERE (t0.ID IN (SELECT t1.ID FROM DOWNCAST_JOINED_B t2, DOWNCAST_JOINED_A t1 WHERE ((t2.BVALUE LIKE ?) AND ((t2.ID = t1.ID) AND (t1.INH_TYPE = ?)))) OR t0.ID IN (SELECT t3.ID FROM DOWNCAST_JOINED_C t4, DOWNCAST_JOINED_A t3 WHERE ((t4.CVALUE LIKE ?) AND ((t4.ID = t3.ID) AND (t3.INH_TYPE = ?))))) bind => [B%, B, C%, C] [EL Fine]: Connection(14707008)--SELECT t0.ID, t0.INH_TYPE, t0.AVALUE, t1.ID, t1.BVALUE FROM DOWNCAST_JOINED_A t0, DOWNCAST_JOINED_B t1 WHERE ((t0.ID IN (SELECT t2.ID FROM DOWNCAST_JOINED_B t3, DOWNCAST_JOINED_A t2 WHERE ((t3.BVALUE LIKE ?) AND ((t3.ID = t2.ID) AND (t2.INH_TYPE = ?)))) OR t0.ID IN (SELECT t4.ID FROM DOWNCAST_JOINED_C t5, DOWNCAST_JOINED_A t4 WHERE ((t5.CVALUE LIKE ?) AND ((t5.ID = t4.ID) AND (t4.INH_TYPE = ?))))) AND ((t1.ID = t0.ID) AND (t0.INH_TYPE = ?))) bind => [B%, B, C%, C, B] [EL Fine]: Connection(14707008)--SELECT t0.ID, t0.INH_TYPE, t0.AVALUE, t1.ID, t1.CVALUE FROM DOWNCAST_JOINED_A t0, DOWNCAST_JOINED_C t1 WHERE ((t0.ID IN (SELECT t2.ID FROM DOWNCAST_JOINED_B t3, DOWNCAST_JOINED_A t2 WHERE ((t3.BVALUE LIKE ?) AND ((t3.ID = t2.ID) AND (t2.INH_TYPE = ?)))) OR t0.ID IN (SELECT t4.ID FROM DOWNCAST_JOINED_C t5, DOWNCAST_JOINED_A t4 WHERE ((t5.CVALUE LIKE ?) AND ((t5.ID = t4.ID) AND (t4.INH_TYPE = ?))))) AND ((t1.ID = t0.ID) AND (t0.INH_TYPE = ?))) bind => [B%, B, C%, C, C]
Solution Design
TBD