|
|
| Line 1: |
Line 1: |
| − | = Enhancement: Query Down Cast =
| + | #REDIRECT [[EclipseLink/Development/2.1/AdvancedJPA_Queries/DownCast]] |
| − | <table><tr>
| + | |
| − | <td width="75%" valign="top">
| + | |
| − | | + | |
| − | 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.
| + | |
| − | | + | |
| − | == Requirements ==
| + | |
| − | | + | |
| − | '''Must Have'''
| + | |
| − | * Support extensions to the expression framework to cast to a specific subclass or limit the results to those of a specific subclass.
| + | |
| − | * Support specifying queries that use attributes only available in a subclass when querying on or through a relationship to a parent class in the persistent hierarchy.
| + | |
| − | | + | |
| − | '''Nice to Have'''
| + | |
| − | * JPQL Extensions to support 'casting' when querying through relationships
| + | |
| − | | + | |
| − | </td>
| + | |
| − | <td style="background-color: #cff8d9; border-width: 1px; border-style: solid; border-color: #999999; padding: 10px;">
| + | |
| − | __TOC__
| + | |
| − | </td>
| + | |
| − | </tr>
| + | |
| − | </table>
| + | |
| − | | + | |
| − | == 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.
| + | |
| − | | + | |
| − | <source lang="java">
| + | |
| − | @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
| + | |
| − | }
| + | |
| − | | + | |
| − | </source>
| + | |
| − | | + | |
| − | Based on this mapped entity model the generated schema looks like:
| + | |
| − | | + | |
| − | <pre>
| + | |
| − | 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))
| + | |
| − | </pre>
| + | |
| − | | + | |
| − | 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.
| + | |
| − | | + | |
| − | <source lang="java">
| + | |
| − | // 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");
| + | |
| − | }
| + | |
| − | | + | |
| − | }
| + | |
| − | </source>
| + | |
| − | | + | |
| − | Now you can write your query:
| + | |
| − | | + | |
| − | <source lang="java">
| + | |
| − | 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();
| + | |
| − | </source>
| + | |
| − | | + | |
| − | The resulting SQL appears as:
| + | |
| − | | + | |
| − | <pre>
| + | |
| − | SELECT ID, INH_TYPE, AVALUE, CVALUE, BVALUE FROM DOWNCAST_SIMPLE_A WHERE (((AVALUE LIKE ?) AND (BVALUE LIKE ?)) AND (CVALUE LIKE ?))
| + | |
| − | </pre>
| + | |
| − | | + | |
| − | === Querying JOINED Hierarchies using Joining ===
| + | |
| − | | + | |
| − | It is possible to query joined hierarchies as well:
| + | |
| − | | + | |
| − | <source lang="java">
| + | |
| − | 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();
| + | |
| − | </source>
| + | |
| − | | + | |
| − | The result SQL is:
| + | |
| − | | + | |
| − | <pre>
| + | |
| − | [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]
| + | |
| − | </pre>
| + | |
| − | | + | |
| − | 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:
| + | |
| − | | + | |
| − | <source lang="java">
| + | |
| − | 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%')
| + | |
| − | </source>
| + | |
| − | | + | |
| − | the result SQL for this scenario is:
| + | |
| − | | + | |
| − | <pre>
| + | |
| − | [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]
| + | |
| − | </pre>
| + | |
| − | | + | |
| − | == Solution Design ==
| + | |
| − | | + | |
| − | TBD
| + | |
