[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] How to generate a single ReadAllQuery to read data from multiple tables for the given configuration?
|
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--tp31040748p31050727.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
