RE: addPrefetchedRelationship

[Jeff Krueger <je...@the-kruegers.com>]

Hi,

 

            I am trying to create a fairly complicated query.  The
current query works, but to me is inefficient.  There is a main table
with two one to one relationship and one one to many to it.  Currently
this is done in four queries.  I have the auto-retrieve set to true.  I
have tried using addPrefetchedRelationship and that doesn't seem to
change anything.  Below is the entry in my repository.xml, the code to
execute the query and the debug showing the generated sql.  Any help on
how to turn this into one query would be very helpful.

 

QueryByCriteria query = QueryFactory.newQuery(FolderVO.class, crit);

 

query.addPrefetchedRelationship("folderTypeVO");

            

broker = ServiceLocator.getInstance().findBroker();

 

result = broker.getCollectionByQuery(query);

 

 

<class-descriptor class="net.crownmedia.folder.vo.FolderVO"
table="FOLDER">

               <field-descriptor name="folderId" column="FOLDER_ID"
jdbc-type="BIGINT"  primarykey="true"/>

               <field-descriptor name="folderName" column="FOLDER_NAME"
jdbc-type="VARCHAR"/>

               <field-descriptor name="folderTypeId"
column="FOLDER_TYPE_ID" jdbc-type="BIGINT"/>

       <reference-descriptor name="folderTypeVO"
class-ref="net.crownmedia.folder.vo.FolderTypeVO" auto-retrieve="true">

         <foreignkey field-ref="folderTypeId"/>

       </reference-descriptor>           

       <reference-descriptor name="folderSecurityVO"
class-ref="net.crownmedia.folder.vo.FolderSecurityVO"
auto-retrieve="true"  auto-delete="true">

         <foreignkey field-ref="folderId"/>

       </reference-descriptor>           

       <collection-descriptor name="folderCommentVOs"
element-class-ref="net.crownmedia.folder.vo.FolderCommentVO"
auto-retrieve="true" auto-delete="true">

         <inverse-foreignkey field-ref="folderId"/>

       </collection-descriptor>          

               <field-descriptor name="parentFolderId"
column="PARENT_FOLDER_ID" jdbc-type="BIGINT"/>

               <field-descriptor name="dateCreated"
column="DATE_CREATED"  jdbc-type="DATE"

 
conversion="org.apache.ojb.broker.accesslayer.conversions.Calendar2DateF
ieldConversion"/>

               <field-descriptor name="createdByUserProfileId"
column="CREATED_BY_USER_ID" jdbc-type="BIGINT"/>

       <field-descriptor name="dateUpdated"  column="DATE_UPDATED"
jdbc-type="DATE"

 
conversion="org.apache.ojb.broker.accesslayer.conversions.Calendar2DateF
ieldConversion"/>

               <field-descriptor name="updatedByUserProfileId"
column="UPDATED_BY_USER_ID" jdbc-type="BIGINT"/>

</class-descriptor>

 

[org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
Result of getTableAlias(): FOLDER_SECURITY A1

[org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
SQL:SELECT
A0.DATE_UPDATED,A0.FOLDER_NAME,A0.CREATED_BY_USER_ID,A0.UPDATED_BY_USER_
ID,A0.PARENT_FOLDER_ID,A0.DATE_CREATED,A0.FOLDER_TYPE_ID,A0.FOLDER_ID
FROM FOLDER A0 INNER JOIN FOLDER_SECURITY A1 ON
A0.FOLDER_ID=A1.FOLDER_ID WHERE ( USER_PROFILE_ID = ?) AND
(A0.FOLDER_ID = ?)

 

[org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
SQL:SELECT
A0.DATE_UPDATED,A0.STRUTS_ACTION,A0.DELETE_SECURITY_KEY_ID,A0.CREATE_SEC
URITY_KEY_ID,A0.CREATED_BY_USER_ID,A0.UPDATED_BY_USER_ID,A0.FOLDER_TYPE_
NAME,A0.DATE_CREATED,A0.UPDATE_SECURITY_KEY_ID,A0.FOLDER_TYPE_ID FROM
FOLDER_TYPE A0 WHERE A0.FOLDER_TYPE_ID = ?

 

[org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
SQL:SELECT
A0.DATE_UPDATED,A0.COMMENT,A0.CREATED_BY_USER_ID,A0.UPDATED_BY_USER_ID,A
0.USER_PROFILE_ID,A0.SEQ_NO,A0.DATE_CREATED,A0.FOLDER_ID FROM
FOLDER_COMMENT A0 WHERE A0.FOLDER_ID = ?

 

[org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl] DEBUG:
SQL:SELECT
A0.DATE_UPDATED,A0.ALLOW_INSERT,A0.APPROVAL,A0.CREATED_BY_USER_ID,A0.ALL
OW_DELETE,A0.UPDATED_BY_USER_ID,A0.ALLOW_UPDATE,A0.USER_PROFILE_ID,A0.DA
TE_CREATED,A0.FOLDER_ID FROM FOLDER_SECURITY A0 WHERE A0.FOLDER_ID = ?

 

Thanks 

 

Jeff