Re: addPrefetchedRelationship

[Danilo Tommasina]

Hi Jeff,
QueryByCriteria is used for generating queries that return filled instances of 
the class you defined when creating the QueryByCriteria.
ReportByCriteria is used for querying single columns just like you would do 
when directly using SQL.
When you define a ReportQueryByCriteria you have also to define a String [] 
containing columns you want to be returned.
The Iterator returned by calling 
PersistenceBroker.getReportQueryIteratorByQuery( Query q ) will then contain 
Object [] with the database entries matching your query.
The Object [] contains the column values in the same order as defined in the 
String [] that you passed to the ReportqueryByCriteria constructor.
bye
danilo
Jeff Krueger wrote:
Thanks for your quick reply.  I am just getting into OJB so I apologize if
my question is stupid.  One question, can someone explain the difference
between the ReportQueryByCriteria and QueryByCriteria.  What does the
"Report" object's provide?  I found out about OJB by reading the PRO Jakarta
Struts book.  I have found this book to be really helpful in talking about
overall design as much as anything.  One of the down falls that it mentions
is to have a Data Access tier that queries each table by itself, instead of
executing one query and joining the tables.  This seems really obvious, but
I can understand trying to translate one large result set into an object
relationship could be very difficult in code, but I guess that is what I was
hoping OJB provided.  Am I just not using it correctly, or is that not
possible.  Especially in my example the folderTypeVO reference.  That is
just a single row, so a 1 to 1 relationship.  I would think that the initial
query could bring back that row.
Thanks in advance for setting me straight:)
Jeff
-----Original Message-----
From: CLARAMONTE Jean-Baptiste [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 16, 2004 3:10 AM
To: 'Jeff Krueger '; '[EMAIL PROTECTED] '
Subject: RE: addPrefetchedRelationship

 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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]