Version 3

    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>