Hi Jeff,
I don't think you could do better than 4 queries unless you use a
ReportQueryByCriteria.
As I understand it, addPrefetchedRelationship can optimze the number of
queries you'll send to your database only when you have a 0,1 relationship :
for example : A---(0,1)->B
=> if you make a queries which retrieve a collection of A and you have added
a addPrefetchedRelationship for B then you will retrieve your B with only
one query which will look like : SELECT B.* FROM B WHERE B.id IN(1, 2, ...)
Regards
-----Message d'origine-----
De: Jeff Krueger
A: [EMAIL PROTECTED]
Date: 16/12/2004 02:27
Objet: RE: addPrefetchedRelationship
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
*** We scanned this email for malicious content ***
*** IMPORTANT: Do not open attachments from unrecognized senders ***
*** MailSystem ASTON ***
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
