| Re: [eclipselink-users] How to generate a single ReadAllQuery to read data from multiple tables for the given configuration? |
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: jamesssss@xxxxxxxxx
To: eclipselink-users@xxxxxxxxxxx
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?
>> query.addJoinedAttribute(query.getExpressionBuilder().get("child2.root"));
// 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:
>
> 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: jamesssss@xxxxxxxxx
> To: eclipselink-users@xxxxxxxxxxx
> 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:
>>
>> 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
>>
>>
>
>
-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland
http://www.eclipse.org/eclipselink/
EclipseLink , http://www.oracle.com/technology/products/ias/toplink/
TopLink
Wiki: http://wiki.eclipse.org/EclipseLink EclipseLink ,
http://wiki.oracle.com/page/TopLink TopLink
Forums: http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink ,
http://www.nabble.com/EclipseLink-f26430.html EclipseLink
Book: http://en.wikibooks.org/wiki/Java_Persistence Java Persistence
Blog: http://java-persistence-performance.blogspot.com/ Java Persistence
Performance
--
View this message in context: http://old.nabble.com/How-to-generate-a-single-ReadAllQuery-to-read-data-from-multiple-tables-for-the-given-configuration--tp31040748p31107006.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
Attachment:
TestSingleQuery.java
Description: Binary data