I'm just about done with a summary query "helper"
class and a SQL FunctionFactory methodology that I
think will fill a long time "hole" in Torque's
base functionality.
But before I just check it in, I thought I'd better
see if folks would be against adding this "new"
capability to 3.3.
Best practices says NO... but please note that this is
99% new runtime classes that don't change any of the
current tested class methods. The only change to
existing classes are a new method being added to the
DB interface and a default implementation being added
to the DBAbstract class.
IMHO, since it doesn't change or break any tested
existing feature, the benefits of getting this
into a formal release is worth it.
Here's a description of this new capability.
The biggest "user" change is the addition of a
SummaryHelper class that makes it very easy to create
queries that include "Aggregate" functions. E.g.
queries like:
Select Employee Sum(hours),avg(hours),min(hours),max(hours)
From time_sheet
Where Type = 1
Group By Employee
Order By Employee ASC
Here's how you would do that with the SummaryHelper class:
SummaryHelper sHelp = new SummaryHelper();
Criteria c = new Criteria();
c.add(TimeSheetPeer.TYPE, 1);
c.addAscendingOrderBy(TimeSheetPeer.EMPLOYEE);
sHelper.addGroupBy(TimeSheetPeer.EMPLOYEE);
sHelper.addAggregate(FunctionFactory.Sum(TimeSheetPeer.HOURS),"Hours");
sHelper.addAggregate(FunctionFactory.Avg(TimeSheetPeer.HOURS),"Avg_Hrs")
;
sHelper.addAggregate(FunctionFactory.Min(TimeSheetPeer.HOURS),"Min_Hrs")
;
sHelper.addAggregate(FunctionFactory.Max(TimeSheetPeer.HOURS),"Max_Hrs")
;
List results = sHelper.summarize( c );
The results list will be an OrderedMap with a key of either
the group by column name or the name specified for the aggregate
function (e.g. EMPLOYEE or Hours). The value will be a Village
Value Class. Below is the basic way to do this.
String emp = results.get("EMPLOYEE").asString();
int hours = results.get("Hours").asInt();
In order to support this in a DB server specific manner,
I've created a new org.apache.torque.util.functions
package. This has a generic SQLFunction interface, a
FunctionFactory class, and other supporting classes.
The FunctionFactory class is responsible for creating
DB Adaptor specific SQLFunction implementations. This is
Done by calling a new DB interface method with the
signature of:
public Class getFunctionClass( FunctionEnum type )
Note that FunctionEnum is like SQLEnum, just a list
of supported function type. This is used as a key
to look up the SQLFunction implementation class that
supports the (potentially DB specific) function.
Currently, I have SQL99 standard implementation for
All the "Aggregate" functions, e.g. AVG, COUNT, MAX,
MIN, and SUM. The DBAbstract class will be modified
to use these by default. These are pretty standard
across all the DB server types Torque supports so
this should be a good starting point.
However, if there is need for DB specific functions,
it is easy for the specific DB adaptor to replace
the SQL 99 standard with its own function.
Also, the SQLFunction interface is designed with an
eye towards expanding FunctionFactory's support into
other SQL standard function categories (like Date
and String).
Questions? Comments?
If people think it's needed, I'll make the JavaDocs and
code available via the web.
Greg
Greg Monroe <[EMAIL PROTECTED]> (919)680-5050
C&IS Solutions Team Lead
Duke Corporate Education, Inc.
330 Blackwell St.
Durham, NC 27701
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
