[eclipselink-users] How to generate a single ReadAllQuery to read data f

[Rohit Banga <rohit.banga@xxxxxxxxxx>]

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