Left-join read-ahead
Since 3.2.4.
Left-join read-ahead is an enhanced on-find read-ahead from previous versions. It allows to preload in one SQL query not only fields from the base instance but also related instances which can be reached from the base instance by CMR navigation. There are no limitation for the depth of CMR navigations. There are also no limitations for cardinality of CMR fields used in navigation and relationship type mapping, i.e. both foreign key and relation-table mapping styles are supported.
Let's look at some examples. Entity and relationship declarations can be found below.
DfindByPrimaryKey
Suppose we have an entity D. Usually, the following SQL is generated for the findByPrimaryKey:
SELECT t0_D.id, t0_D.name FROM D t0_D WHERE t0_D.id=?
But while executing findByPrimaryKey we also want to preload two collection-valued CMR fields bs and cs. Here is how it would look like in jbosscmp-jdbc.xml
<query> <query-method> <method-name>findByPrimaryKey</method-name> <method-params> <method-param>java.lang.Long</method-param> </method-params> </query-method> <jboss-ql><![CDATA[SELECT OBJECT(o) FROM D AS o WHERE o.id = ?1\]\]\></jboss-ql> <read-ahead> <strategy>on-find</strategy> <page-size>4</page-size> <eager-load-group>basic</eager-load-group> <left-join cmr-field="bs" eager-load-group="basic"></left-join> <left-join cmr-field="cs" eager-load-group="basic"></left-join> </read-ahead> </query>
The generated SQL will be
SELECT t0_D.id, t0_D.name, t1_D_bs.id, t1_D_bs.name, t2_D_cs.id, t2_D_cs.name FROM D t0_D LEFT OUTER JOIN B t1_D_bs ON t0_D.id=t1_D_bs.D_FK LEFT OUTER JOIN C t2_D_cs ON t0_D.id=t2_D_cs.D_FK WHERE t0_D.id=?
Which means for the D with the specific id we preload all its related B's and C's and can access those instance loading them from the read-ahead cache, not from the database.
DfindAll
The same way we could optimize findAll method which selects all the D's.
SELECT DISTINCT t0_o.id, t0_o.name FROM D t0_o ORDER BY t0_o.id DESC
When we add the following metadata
<query> <query-method> <method-name>findAll</method-name> </query-method> <jboss-ql><![CDATA[SELECT DISTINCT OBJECT(o) FROM D AS o ORDER BY o.id DESC\]\]\></jboss-ql> <read-ahead> <strategy>on-find</strategy> <page-size>4</page-size> <eager-load-group>basic</eager-load-group> <left-join cmr-field="bs" eager-load-group="basic"></left-join> <left-join cmr-field="cs" eager-load-group="basic"></left-join> </read-ahead> </query>
the SQL will be
SELECT DISTINCT t0_o.id, t0_o.name, t1_o_bs.id, t1_o_bs.name, t2_o_cs.id, t2_o_cs.name FROM D t0_o LEFT OUTER JOIN B t1_o_bs ON t0_o.id=t1_o_bs.D_FK LEFT OUTER JOIN C t2_o_cs ON t0_o.id=t2_o_cs.D_FK ORDER BY t0_o.id DESC
We are loading all the D's with all their related B's and C's.
AfindAll
Now let's look at a more complex configuration. Here we want to preload instance A, its parent (self-relation) reached from A with CMR field parent, B reached from A with CMR field b and related to it C reached from B with CMR field c, again B reached from A but this time with CMR field b2 and related to it C reached from B with CMR field c.
Here is a usual findAll SQL
SELECT t0_o.id, t0_o.name FROM A t0_o ORDER BY t0_o.id DESC FOR UPDATE
Let's add the following metadata that describes our preloading plan.
<query> <query-method> <method-name>findAll</method-name> </query-method> <jboss-ql><![CDATA[SELECT OBJECT(o) FROM A AS o ORDER BY o.id DESC\]\]\></jboss-ql> <read-ahead> <strategy>on-find</strategy> <page-size>4</page-size> <eager-load-group>basic</eager-load-group> <left-join cmr-field="parent" eager-load-group="basic"></left-join> <left-join cmr-field="b" eager-load-group="basic"> <left-join cmr-field="c" eager-load-group="basic"></left-join> </left-join> <left-join cmr-field="b2" eager-load-group="basic"> <left-join cmr-field="c" eager-load-group="basic"></left-join> </left-join> </read-ahead> </query>
Now we get
SELECT t0_o.id, t0_o.name, t1_o_parent.id, t1_o_parent.name, t2_o_b.id, t2_o_b.name, t3_o_b_c.id, t3_o_b_c.name, t4_o_b2.id, t4_o_b2.name, t5_o_b2_c.id, t5_o_b2_c.name FROM A t0_o LEFT OUTER JOIN A t1_o_parent ON t0_o.PARENT=t1_o_parent.id LEFT OUTER JOIN B t2_o_b ON t0_o.B_FK=t2_o_b.id LEFT OUTER JOIN C t3_o_b_c ON t2_o_b.C_FK=t3_o_b_c.id LEFT OUTER JOIN B t4_o_b2 ON t0_o.B2_FK=t4_o_b2.id LEFT OUTER JOIN C t5_o_b2_c ON t4_o_b2.C_FK=t5_o_b2_c.id ORDER BY t0_o.id DESC FOR UPDATE
You can navigate CMRs from any found instance of A w/o loading related instances from the database.
AfindMeParentGrandParent
Here is some more example of self-relation. Suppose, we want to write a method that would preload an instance, its parent, grand-parent and its grand-grand-parent in one query. We would do it like
<query> <query-method> <method-name>findMeParentGrandParent</method-name> <method-params> <method-param>java.lang.Long</method-param> </method-params> </query-method> <jboss-ql><![CDATA[SELECT OBJECT(o) FROM A AS o WHERE o.id = ?1\]\]\></jboss-ql> <read-ahead> <strategy>on-find</strategy> <page-size>4</page-size> <eager-load-group>*</eager-load-group> <left-join cmr-field="parent" eager-load-group="basic"> <left-join cmr-field="parent" eager-load-group="basic"> <left-join cmr-field="parent" eager-load-group="basic"></left-join> </left-join> </left-join> </read-ahead> </query>
The generated SQL would be
SELECT t0_o.id, t0_o.name, t0_o.secondName, t0_o.B_FK, t0_o.B2_FK, t0_o.PARENT, t1_o_parent.id, t1_o_parent.name, t2_o_parent_parent.id, t2_o_parent_parent.name, t3_o_parent_parent_parent.id, t3_o_parent_parent_parent.name FROM A t0_o LEFT OUTER JOIN A t1_o_parent ON t0_o.PARENT=t1_o_parent.id LEFT OUTER JOIN A t2_o_parent_parent ON t1_o_parent.PARENT=t2_o_parent_parent.id LEFT OUTER JOIN A t3_o_parent_parent_parent ON t2_o_parent_parent.PARENT=t3_o_parent_parent_parent.id WHERE (t0_o.id = ?) FOR UPDATE
Note, if we remove left-join metadata we will have only
SELECT t0_o.id, t0_o.name, t0_o.secondName, t0_o.B2_FK, t0_o.PARENT FOR UPDATE
ejb-jar.xml declations for entities and relationships used in the examples above
<entity > <ejb-name>D</ejb-name> <local-home>org.jboss.test.cmp2.advanced.ejb.DLocalHome</local-home> <local>org.jboss.test.cmp2.advanced.ejb.DLocal</local> <ejb-class>org.jboss.test.cmp2.advanced.ejb.DCMP</ejb-class> <persistence-type>Container</persistence-type> <prim-key-class>java.lang.Long</prim-key-class> <reentrant>false</reentrant> <cmp-version>2.x</cmp-version> <abstract-schema-name>D</abstract-schema-name> <cmp-field> <field-name>id</field-name> </cmp-field> <cmp-field > <field-name>name</field-name> </cmp-field> <cmp-field > <field-name>secondName</field-name> </cmp-field> <primkey-field>id</primkey-field> <query> <query-method> <method-name>findAll</method-name> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(o) FROM D AS o\]\]\></ejb-ql> </query> <query> <query-method> <method-name>findByPrimaryKey</method-name> <method-params> <method-param>java.lang.Long</method-param> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(o) FROM D AS o WHERE o.id = ?1\]\]\></ejb-ql> </query> </entity> <entity> <ejb-name>B</ejb-name> <local-home>org.jboss.test.cmp2.advanced.ejb.BLocalHome</local-home> <local>org.jboss.test.cmp2.advanced.ejb.BLocal</local> <ejb-class>org.jboss.test.cmp2.advanced.ejb.BCMP</ejb-class> <persistence-type>Container</persistence-type> <prim-key-class>java.lang.Long</prim-key-class> <reentrant>false</reentrant> <cmp-version>2.x</cmp-version> <abstract-schema-name>B</abstract-schema-name> <cmp-field> <field-name>id</field-name> </cmp-field> <cmp-field> <field-name>name</field-name> </cmp-field> <cmp-field > <field-name>secondName</field-name> </cmp-field> <primkey-field>id</primkey-field> <query> <query-method> <method-name>findAll</method-name> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(o) FROM B AS o\]\]\></ejb-ql> </query> <query> <query-method> <method-name>findByPrimaryKey</method-name> <method-params> <method-param>java.lang.Long</method-param> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(o) FROM B AS o WHERE o.id = ?1\]\]\></ejb-ql> </query> </entity> <entity> <ejb-name>C</ejb-name> <local-home>org.jboss.test.cmp2.advanced.ejb.CLocalHome</local-home> <local>org.jboss.test.cmp2.advanced.ejb.CLocal</local> <ejb-class>org.jboss.test.cmp2.advanced.ejb.CCMP</ejb-class> <persistence-type>Container</persistence-type> <prim-key-class>java.lang.Long</prim-key-class> <reentrant>false</reentrant> <cmp-version>2.x</cmp-version> <abstract-schema-name>C</abstract-schema-name> <cmp-field > <field-name>id</field-name> </cmp-field> <cmp-field > <field-name>name</field-name> </cmp-field> <cmp-field > <field-name>secondName</field-name> </cmp-field> <primkey-field>id</primkey-field> <query> <query-method> <method-name>findAll</method-name> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(o) FROM C AS o\]\]\></ejb-ql> </query> <query> <query-method> <method-name>findByPrimaryKey</method-name> <method-params> <method-param>java.lang.Long</method-param> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(o) FROM C AS o WHERE o.id = ?1\]\]\></ejb-ql> </query> </entity> <entity > <ejb-name>A</ejb-name> <local-home>org.jboss.test.cmp2.advanced.ejb.ALocalHome</local-home> <local>org.jboss.test.cmp2.advanced.ejb.ALocal</local> <ejb-class>org.jboss.test.cmp2.advanced.ejb.ACMP</ejb-class> <persistence-type>Container</persistence-type> <prim-key-class>java.lang.Long</prim-key-class> <reentrant>false</reentrant> <cmp-version>2.x</cmp-version> <abstract-schema-name>A</abstract-schema-name> <cmp-field > <field-name>id</field-name> </cmp-field> <cmp-field > <field-name>name</field-name> </cmp-field> <cmp-field > <field-name>secondName</field-name> </cmp-field> <primkey-field>id</primkey-field> <query> <query-method> <method-name>findAll</method-name> <method-params> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(o) FROM A AS o\]\]\></ejb-ql> </query> <query> <query-method> <method-name>findByPrimaryKey</method-name> <method-params> <method-param>java.lang.Long</method-param> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(o) FROM A AS o WHERE o.id = ?1\]\]\></ejb-ql> </query> <query> <query-method> <method-name>findMeParentGrandParent</method-name> <method-params> <method-param>java.lang.Long</method-param> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(o) FROM A AS o WHERE o.id = ?1\]\]\></ejb-ql> </query> <query> <query-method> <method-name>findMeLeftJoinCs</method-name> <method-params> <method-param>java.lang.Long</method-param> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(o) FROM A AS o WHERE o.id = ?1\]\]\></ejb-ql> </query> <query> <query-method> <method-name>findAllLeftJoinCs</method-name> <method-params> </method-params> </query-method> <ejb-ql><![CDATA[SELECT OBJECT(o) FROM A AS o\]\]\></ejb-ql> </query> </entity> <relationships > <ejb-relation > <ejb-relation-name>D-Bs</ejb-relation-name> <ejb-relationship-role > <ejb-relationship-role-name>D-has-Bs</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source > <ejb-name>D</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>bs</cmr-field-name> <cmr-field-type>java.util.Collection</cmr-field-type> </cmr-field> </ejb-relationship-role> <ejb-relationship-role > <ejb-relationship-role-name>B-belongsto-D</ejb-relationship-role-name> <multiplicity>Many</multiplicity> <relationship-role-source > <ejb-name>B</ejb-name> </relationship-role-source> </ejb-relationship-role> </ejb-relation> <ejb-relation > <ejb-relation-name>B-C</ejb-relation-name> <ejb-relationship-role > <ejb-relationship-role-name>B-has-C</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source > <ejb-name>B</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>c</cmr-field-name> </cmr-field> </ejb-relationship-role> <ejb-relationship-role > <ejb-relationship-role-name>C-has-B</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source > <ejb-name>C</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>b</cmr-field-name> </cmr-field> </ejb-relationship-role> </ejb-relation> <ejb-relation > <ejb-relation-name>A-C</ejb-relation-name> <ejb-relationship-role > <ejb-relationship-role-name>A-has-Cs</ejb-relationship-role-name> <multiplicity>Many</multiplicity> <relationship-role-source > <ejb-name>A</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>cs</cmr-field-name> <cmr-field-type>java.util.Collection</cmr-field-type> </cmr-field> </ejb-relationship-role> <ejb-relationship-role > <ejb-relationship-role-name>Cs-belongto-A</ejb-relationship-role-name> <multiplicity>Many</multiplicity> <relationship-role-source > <ejb-name>C</ejb-name> </relationship-role-source> </ejb-relationship-role> </ejb-relation> <ejb-relation > <ejb-relation-name>A-A</ejb-relation-name> <ejb-relationship-role > <ejb-relationship-role-name>A-has-A</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source > <ejb-name>A</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>parent</cmr-field-name> </cmr-field> </ejb-relationship-role> <ejb-relationship-role > <ejb-relationship-role-name>A-belongsto-A</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source > <ejb-name>A</ejb-name> </relationship-role-source> </ejb-relationship-role> </ejb-relation> <ejb-relation > <ejb-relation-name>D-Cs</ejb-relation-name> <ejb-relationship-role > <ejb-relationship-role-name>D-has-Cs</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source > <ejb-name>D</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>cs</cmr-field-name> <cmr-field-type>java.util.Collection</cmr-field-type> </cmr-field> </ejb-relationship-role> <ejb-relationship-role > <ejb-relationship-role-name>C-belongsto-D</ejb-relationship-role-name> <multiplicity>Many</multiplicity> <relationship-role-source > <ejb-name>C</ejb-name> </relationship-role-source> </ejb-relationship-role> </ejb-relation> <ejb-relation > <ejb-relation-name>A-B2</ejb-relation-name> <ejb-relationship-role > <ejb-relationship-role-name>A-has-B</ejb-relationship-role-name> <multiplicity>Many</multiplicity> <relationship-role-source > <ejb-name>A</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>b2</cmr-field-name> </cmr-field> </ejb-relationship-role> <ejb-relationship-role > <ejb-relationship-role-name>B-belongsto-A</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source > <ejb-name>B</ejb-name> </relationship-role-source> </ejb-relationship-role> </ejb-relation> <ejb-relation > <ejb-relation-name>A-B</ejb-relation-name> <ejb-relationship-role > <ejb-relationship-role-name>A-has-B</ejb-relationship-role-name> <multiplicity>Many</multiplicity> <relationship-role-source > <ejb-name>A</ejb-name> </relationship-role-source> <cmr-field > <cmr-field-name>b</cmr-field-name> </cmr-field> </ejb-relationship-role> <ejb-relationship-role > <ejb-relationship-role-name>B-belongsto-A</ejb-relationship-role-name> <multiplicity>One</multiplicity> <relationship-role-source > <ejb-name>B</ejb-name> </relationship-role-source> </ejb-relationship-role> </ejb-relation> </relationships>
Comments