| Re: [eclipselink-users] How to generate a single ReadAllQuery to read data from multiple tables for the given configuration? |
|
Filed bug 339529. Thanks Rohit On 3/9/2011 11:42 PM, Rohit Banga wrote: Hi Chris This looks interesting. Can you help me resolve the following issues with the code sample: DynamicTypeBuilder.addMapping() is a protected method and hence I cannot use it. I assume you meant: rootBuilder.getType().getDescriptor().addMapping(mapping); But with this I get the following exception: Exception Description: The instance variable [root.child2] is not defined in the domain class [jptest.root], or it is not accessible. Internal Exception: java.lang.NoSuchFieldException: root.child2 Mapping: org.eclipse.persistence.mappings.OneToOneMapping[root.child2] Descriptor: RelationalDescriptor(jptest.root --> [DatabaseTable(root)]) Runtime Exceptions: --------------------------------------------------------- java.lang.NullPointerException at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.initializeDescriptors(DatabaseSessionImpl.java:548) at org.eclipse.persistence.sessions.Project.addDescriptors(Project.java:295) at org.eclipse.persistence.internal.sessions.DatabaseSessionImpl.addDescriptors(DatabaseSessionImpl.java:227) at org.eclipse.persistence.dynamic.DynamicHelper.addTypes(DynamicHelper.java:222) at org.eclipse.persistence.jpa.dynamic.JPADynamicHelper.addTypes(JPADynamicHelper.java:51) Thanks Rohit <br>----- Original Message -----<br>From: christopher.delahunt@xxxxxxxxxx<br>To: eclipselink-users@xxxxxxxxxxx<br>Cc: jamesssss@xxxxxxxxx<br>Sent: Wednesday, March 9, 2011 10:52:58 PM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi<br>Subject: Re: [eclipselink-users] How to generate a single ReadAllQuery to read data from multiple tables for the given configuration?<br><br> <html> <head> <title></title> </head> <div> Hello Rohit,<br> <br> Problem here is that you are querying on Child1 not root, so child2.root doesn't exist on the expressionbuilder. You need to add a root->child2 relationship, and then perform a join<br> on child1->root->child2. Of course, you can query on root and then join root->child1, and root->child2 as well. <br> <br> The problem is you are trying to use DynamicTypeBuilder.addOneToOneMapping() which is just a helper method to build the generic 1:1 mapping. You will need to build the OneToOneMapping yourself and have it use target foreign keys, and then use<br> DynamicTypeBuilder.addMapping(mapping) instead. <br> <br> Something like:<br> OneToOneMapping mapping = new OneToOneMapping();<br> mapping.setAttributeName("root.child2");<br> mapping.setReferenceClass(childBuilder2.getType().getJavaClass());<br> <br> String targetField = rootBuilder.getType().getDescriptor().getPrimaryKeyFields().get(0).getName();<br> mapping.addTargetForeignKeyFieldName(targetField , "fkey_2");<br> <br> rootBuilder.addMapping(mapping);<br> <br> This allows adding the revers 1:1 to Root using the foreign key in the Child2 table, and allow you to query on root and join using this relationship.<br> <br> Best Regards,<br> Chris<br> <br> On 09/03/2011 11:54 AM, Rohit Banga wrote: <blockquote cite=""> <pre>Hi James Thanks for your response. I have attached a sample program for the usecase. Please see line 112 of the program: query.addJoinedAttribute(query.getExpressionBuilder().get("child2.root")); I get the following exception which is expected if I understand the Eclipselink Join Attribute API correctly. Can you please suggest the changes I should make in my program? Exception Description: The join _expression_ Query Key child2.root Base jpatest.child1 is not valid, or for a mapping type that does not support joining. at org.eclipse.persistence.internal.queries.JoinedAttributeManager.prepareJoinExpression(JoinedAttributeManager.java:851) at org.eclipse.persistence.internal.queries.JoinedAttributeManager.prepareJoinExpressions(JoinedAttributeManager.java:778) at org.eclipse.persistence.queries.ObjectLevelReadQuery.prePrepare(ObjectLevelReadQuery.java:2063) at org.eclipse.persistence.queries.ObjectLevelReadQuery.checkPrePrepare(ObjectLevelReadQuery.java:842) at org.eclipse.persistence.queries.ObjectLevelReadQuery.checkPrepare(ObjectLevelReadQuery.java:824) at org.eclipse.persistence.queries.DatabaseQuery.prepareCall(DatabaseQuery.java:1694) at org.eclipse.persistence.exceptions.QueryException.mappingForExpressionDoesNotSupportJoining(QueryException.java:674) Thanks Rohit ----- Original Message ----- From: <a class="moz-txt-link-abbreviated" href="" class="moz-txt-link-rfc2396E" href="mailto:jamesssss@xxxxxxxxx">"mailto:jamesssss@xxxxxxxxx" target="_blank">jamesssss@xxxxxxxxx</a> To: <a class="moz-txt-link-abbreviated" href="" class="moz-txt-link-rfc2396E" href="mailto:eclipselink-users@xxxxxxxxxxx">"mailto:eclipselink-users@xxxxxxxxxxx" target="_blank">eclipselink-users@xxxxxxxxxxx</a> Sent: Wednesday, March 9, 2011 8:17:28 PM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi Subject: Re: [eclipselink-users] How to generate a single ReadAllQuery to read data from multiple tables for the given configuration? </pre> <blockquote> <blockquote> <pre>query.addJoinedAttribute(query.getExpressionBuilder().get("child2.root")); </pre> </blockquote> </blockquote> <pre><!---->// this cannot not work right! I don't understand what you mean. This should work fine, if it can be null, you will want to use an outer join. In general a OneToOne can have the foreign key on the source or target table. What error are you getting? Rohit Banga-2 wrote: </pre> <blockquote> <pre>If I configure a one-to-one mapping from child1 to root and from root to child2 then I can add two joined attribute expressions to construct a single query. However, for the given tables this configuration is not possible. If I write root.addOneToOneMapping("root.child2", child2, fkey_fields); The fkey_fields should be the fields on the root side of the mapping. But the foreign key in my case is on the child1, child2 tables. Which means I can only configure the following two mappings: child1.addOneToOneMapping("child1.root", root, "fkey_id"); child2.addOneToOneMapping("child2.root", root, "fkey_id"); Now I construct a query as follows: ObjectLevelReadQuery query = helper.newReadAllQuery(child1Class, new ExpresssionBuilder()); query.addJoinedAttribute(query.getExpressionBuilder().get("child1.root")); query.addJoinedAttribute(query.getExpressionBuilder().get("child2.root")); // this cannot not work right! So the only options I see are: 1. Perform two queries. 2. Treat the One-To-One Mapping as a One-to-Many mapping. Because when configuring a One-to-Many mapping, the foreign key is expected to be on the latter side of the mapping. Am I missing something here? Thanks Rohit ----- Original Message ----- From: <a class="moz-txt-link-abbreviated" href="" class="moz-txt-link-rfc2396E" href="mailto:jamesssss@xxxxxxxxx">"mailto:jamesssss@xxxxxxxxx" target="_blank">jamesssss@xxxxxxxxx</a> To: <a class="moz-txt-link-abbreviated" href="" class="moz-txt-link-rfc2396E" href="mailto:eclipselink-users@xxxxxxxxxxx">"mailto:eclipselink-users@xxxxxxxxxxx" target="_blank">eclipselink-users@xxxxxxxxxxx</a> Sent: Wednesday, March 2, 2011 9:38:01 PM GMT +05:30 Chennai, Kolkata, Mumbai, New Delhi Subject: Re: [eclipselink-users] How to generate a single ReadAllQuery to read data from multiple tables for the given configuration? You should be able to use a join fetch or a batch fetch to read both OneToOnes in a single query. If the OneToOne can be null, then you would need to use an outer join. query.addJoinedAttribute(eb.getAllowingNull("child1.root")); Rohit Banga-2 wrote: </pre> <blockquote> <pre>Hi All Consider the following three tables: describe root_table_1; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | description | varchar(30) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) describe child1_table_1; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | child1_id | int(11) | NO | PRI | 0 | | | description | varchar(30) | YES | | NULL | | | fkey_id | int(11) | YES | MUL | NULL | |-> foreign key references root_table_1 (id) +-------------+-------------+------+-----+---------+-------+ 3 rows in set (0.06 sec) describe child2_table_1; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | child2_id | int(11) | NO | PRI | 0 | | | description | varchar(30) | YES | | NULL | | | fkey_id | int(11) | YES | MUL | NULL | | -> foreign key references root_table_1 (id) +-------------+-------------+------+-----+---------+-------+ 3 rows in set (0.03 sec) Basically each of the two child tables has a foreign key field fkey_id referencing the primary key field of the root table. Let us say that there is a one-to-one relationship between the entities of root & child1 and also between root & child2. To represent the tables and their relationships I create the types using the following code: Class<?> rootClass = dcl.createDynamicClass("jpatest.rootClass"); DynamicTypeBuilder rootBuilder = new JPADynamicTypeBuilder(rootClass, null, "root_table_1"); rootBuilder.setPrimaryKeyFields("id"); rootBuilder.addDirectMapping("id", int.class, "id"); rootBuilder.addDirectMapping("description", String.class, "description"); Class<?> childClass1 = dcl.createDynamicClass("jpatest.childClass1"); DynamicTypeBuilder childBuilder1 = new JPADynamicTypeBuilder(childClass1, null, "child1_table_1"); childBuilder1.setPrimaryKeyFields("child1_id"); childBuilder1.addDirectMapping("child1_id", int.class, "child1_id"); childBuilder1.addDirectMapping("description", String.class, "description"); Class<?> childClass2 = dcl.createDynamicClass("jpatest.childClass2"); DynamicTypeBuilder childBuilder2 = new JPADynamicTypeBuilder(childClass2, null, "child2_table_1"); childBuilder2.setPrimaryKeyFields("child2_id"); childBuilder2.addDirectMapping("child2_id", int.class, "child2_id"); childBuilder2.addDirectMapping("description", String.class, "description"); childBuilder1.addOneToOneMapping("child1.root", rootBuilder.getType(), "fkey_id"); childBuilder2.addOneToOneMapping("child2.root", rootBuilder.getType(), "fkey_id"); helper.addTypes(false, false, rootBuilder.getType(), childBuilder1.getType(), childBuilder2.getType()); Now is it possible to construct a single ReadAllQuery that reads the objects from all three tables? If yes will it require modification in the way the mappings are configured. With the above mapping configuration, the only way we may read all the tables is by two queries. Traversing ObjectLevelReadQuery readQuery1 = helper.newReadAllQuery(childBuilder1.getType().getDescriptor().getAlias()); List<DynamicEntity> entities1 = (List<DynamicEntity>) session.executeQuery(readQuery1); List<DynamicEntity> entities1 = (List<DynamicEntity>) session.executeQuery(readQuery1); EntityContainer.printEntities(helper, entities1); The above code generates the following SQL query: *SELECT t1.child1_id, t1.description, t1.fkey_id, t0.id, t0.description FROM root_table_1 t0, child1_table_1 t1 WHERE (t0.id = t1.fkey_id)* // similarly a query for table child2_table_2 Since the One-To-One mapping expects the foreign key to be on the first side of the mapping, I think it is not possible to configure the "one-to-one" mappings in a way that we are able to generate a single query like: *SELECT t1.child1_id, t1.description, t1.fkey_id, t0.id, t0.description, t2.child2_id, t2.description, t2.fkey_id FROM root_table_1 t0, child1_table_1 t1, child2_table_1 t2 WHERE (t0.id = t1.fkey_id) and (t0.id = t2.fkey_id); */Can anyone confirm if this is the case? One workaround I have found is to configure one-to-many mappings from the root class to the child1 and child2 classes and logically treat them as one-to-one mappings. Is there any other way to direct Eclipselink to generate a single query? / Thanks in Advance! --
Thanks and Regards Rohit Banga Member Technical Staff Oracle Server Technologies |