Thanks for the explanation. I don't disagree that the sub queries will be quick since they are going after PK's. I guess I was just going into learning this thinking if would do joining of tables for select. Just a matter of expectations. Thanks for everyone's help since there isn't many books on this.
Jeff -----Original Message----- From: Daniel Perry [mailto:[EMAIL PROTECTED] Sent: Thursday, December 16, 2004 8:09 AM To: OJB Users List Subject: RE: addPrefetchedRelationship Taken from: http://db.apache.org/ojb/docu/guides/query.html#Report+Queries Report Queries Report queries are used to retrieve row data, not 'real' business objects. A row is an array of Object. With these queries you can define what attributes of an object you want to have in the row. The attribute names may also contain path expressions like 'owner.address.street'. To define the attributes use ReportQuery #setAttributes(String[] attributes). The following ReportQuery retrieves the name of the ProductGroup, the name of the Article etc. for all Articles named like "C%": Criteria crit = new Criteria(); Collection results = new Vector(); crit.addLike("articleName", "C%"); ReportQueryByCriteria q = QueryFactory.newReportQuery(Article.class, crit); q.setAttributes(new String[] { "productGroup.groupName","articleId", "articleName", "price" }); Iterator iter = broker.getReportQueryIteratorByQuery(q); The ReportQuery returns an Iterator over a Collection of Object[4] ([String, Integer, String, Double]). Normal queries return object trees. Report queries return rows of the attributes/columns you select. So, report queries are great for reports, but not for general use. With regards to it generating more than one query - this isnt necessarily a bad thing. It will generate less data for a collection descriptor than a join would (resultset data would be repeated on the 1 end of 1:n). Also after the first query, the remaining queries are almost always querying on integer primary (or foreign) keys, so are very fast. Also, if you proxy collections (by just adding proxy="yes" to the descriptor) the collection wont be loaded until they are accessed. So if they are never accessed, this will significantly reduce database access. Daniel. > -----Original Message----- > From: Jeff Krueger [mailto:[EMAIL PROTECTED] > Sent: 16 December 2004 13:17 > To: 'OJB Users List' > Subject: RE: addPrefetchedRelationship > > > 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
