Re: [eclipselink-users] How to generate a single ReadAllQuery to read da

[James Sutherland <jamesssss@xxxxxxxxx>]

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.