db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From b..@apache.org
Subject cvs commit: db-ojb/forrest/src/documentation/content/xdocs/docu query.xml
Date Sat, 05 Jun 2004 13:55:52 GMT
brj         2004/06/05 06:55:52

  Modified:    forrest/src/documentation/content/xdocs/docu query.xml
  Log:
  added section about limitations of ReportQueries
  
  Revision  Changes    Path
  1.6       +104 -13   db-ojb/forrest/src/documentation/content/xdocs/docu/query.xml
  
  Index: query.xml
  ===================================================================
  RCS file: /home/cvs/db-ojb/forrest/src/documentation/content/xdocs/docu/query.xml,v
  retrieving revision 1.5
  retrieving revision 1.6
  diff -u -r1.5 -r1.6
  --- query.xml	2 Jun 2004 18:53:01 -0000	1.5
  +++ query.xml	5 Jun 2004 13:55:52 -0000	1.6
  @@ -631,7 +631,7 @@
   
               <anchor id="report-queries"/>
               <section>
  -                <title>report queries</title>
  +                <title>Report Queries</title>
                   <p>
                   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.
  @@ -640,25 +640,116 @@
                   <strong>#setAttributes(String[] attributes)</strong>.
                   </p>
                   <p>
  -                The following ReportQuery summarizes the number of articles in stock and
their price for each ProductGroup:
  +                The following ReportQuery retrieves the name of the ProductGroup, the name
of the Article etc. for all
  +                Articles named like "C%":
                       </p>
                   <source><![CDATA[
   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"
});
   
  -ReportQueryByCriteria q = QueryFactory.newReportQuery(
  -                            ProductGroup.class, crit);
  -// define the 'columns' of the report
  -q.setAttributes(new String[] { "groupName",
  -                            "sum(allArticlesInGroup.stock)",
  -                            "sum(allArticlesInGroup.price)" });
  -q.addGroupBy("groupName");
  -
  -Iterator iter = broker.getReportQueryIteratorByQuery(q);]]></source>
  +Iterator iter = broker.getReportQueryIteratorByQuery(q);
  +]]></source>
                   <p>
                   The ReportQuery returns an Iterator over a Collection of
  -                Object[3] ([String, Integer, Double]).
  +                Object[4] ([String, Integer, String, Double]).
                   </p>
  +                
  +                <section>
  +	                <title>Limitations of Report Queries</title>
  +					<p>
  +    	            ReportQueries should not be used with columns referencing classes with
extents. Assume we want to select all ProductGroups
  +    	            and summarize the amount and prize of items in stock per group. The class
Article referenced by <strong>allArticlesInGroup</strong>						 has the extents
Books and CDs.
  +    	            </p>
  +                <source><![CDATA[
  +Criteria crit = new Criteria();
  +Collection results = new Vector();
  +ReportQueryByCriteria q = QueryFactory.newReportQuery(ProductGroup.class, crit);
  +q.setAttributes(new String[] { "groupName", "sum(allArticlesInGroup.stock)", "sum(allArticlesInGroup.price)"
});
  +q.addGroupBy("groupName");
  +
  +Iterator iter = broker.getReportQueryIteratorByQuery(q);
  +]]></source>
  +					<p>
  +					The ReportQuery looks quite reasonable, but it will produce an SQL not suitable for
the task:
  +					</p>
  +                <source><![CDATA[
  +SELECT A0.KategorieName,sum(A1.Lagerbestand),sum(A1.Einzelpreis)
  +FROM Kategorien A0
  +LEFT OUTER JOIN artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
  +LEFT OUTER JOIN books A1E2 ON A0.Kategorie_Nr=A1E2.Kategorie_Nr
  +LEFT OUTER JOIN cds A1E1 ON A0.Kategorie_Nr=A1E1.Kategorie_Nr
  +GROUP BY A0.KategorieName
  +]]></source>
  +					<p>
  +					This SQL will select the columns "Lagerbestand" and "Einzelpreis" from one extent
only, and for ProductGroups
  +					having Articles, Books and CDs the result is wrong!
  +					</p>
  +					<p>
  +					As a workaround the query can be "reversed". Instead of selection the ProductGroup
we go for the Articles:
  +					</p>
  +                <source><![CDATA[
  +Criteria crit = new Criteria();
  +Collection results = new Vector();
  +ReportQueryByCriteria q = QueryFactory.newReportQuery(Article.class, crit);
  +q.setAttributes(new String[] { "productGroup.groupName", "sum(stock)", "sum(price)" });
  +q.addGroupBy("productGroup.groupName");
  +]]></source>
  +					<p>
  +					This ReportQuery executes the following three selects (one for each concrete extent)
and produces better results.
  +					</p>
  +                <source><![CDATA[
  +SELECT  A1.KategorieName,sum(A0.Lagerbestand),sum(A0.Einzelpreis) 
  +FROM artikel A0 
  +INNER JOIN Kategorien A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
  +GROUP BY A1.KategorieName
  +
  +SELECT  A1.KategorieName,sum(A0.Lagerbestand),sum(A0.Einzelpreis) 
  +FROM cds A0 
  +INNER JOIN Kategorien A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
  +GROUP BY A1.KategorieName
  + 
  +SELECT  A1.KategorieName,sum(A0.Lagerbestand),sum(A0.Einzelpreis) 
  +FROM books A0 
  +INNER JOIN Kategorien A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
  +GROUP BY A1.KategorieName
  +]]></source>
  +					<p>
  +					Of course there's also a drawback here: the same ProductGroup may be selected several
times, so to get the correct sum,
  +					the results of the ProductGroup has to be added. In our sample the ProductGroup "Books"
will be listed three times.
  +					</p>
  +					<p>
  +					After listing so many drawbacks and problems, here's the SQL the produces the desired
result. 
  +					This is a manually created SQL, not generated by OJB. Unfortunately it's not fully
supported by some DBMS because of 
  +					"union" and sub-selects.
  +					</p>
  +                <source><![CDATA[
  +select KategorieName, sum(lagerbestand), sum(einzelpreis)
  +from
  +(
  +	SELECT  A1.KategorieName,A0.Lagerbestand,A0.Einzelpreis
  +	FROM artikel A0
  +	INNER JOIN Kategorien A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
  +
  +	union
  +
  +	SELECT  A1.KategorieName,A0.Lagerbestand,A0.Einzelpreis
  +	FROM books A0
  +	INNER JOIN Kategorien A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
  +
  +	union
  +
  +	SELECT  A1.KategorieName,A0.Lagerbestand,A0.Einzelpreis
  +	FROM cds A0
  +	INNER JOIN Kategorien A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
  +)
  +group by kategorieName
  +]]></source>
  +
  +                </section>
  +                
               </section>
           </section>
   
  
  
  

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message