Oracle Application Server TopLink is an advanced
object-to-relational persistence. It helps to build high performance
applications that store persistent data in a relational database. OracleAS
TopLink query framework API provide report querying functionalites which
uses reporting functionalities and features provided by the database to
retrieve aggregate data.
Prerequisites
Before we get started, the following assumptions
are made in this document:
Minimum knowledge about TopLink.
Must have HR schema installed on the database to
run the sample
HowTo fetch aggregate data using
ReportQuery in OracleAS TopLink
Report Query enables you to retrieve aggregate
data from a set of objects and their related objects. Report Query allows
you to use expression for grouping data, specifying selection criteria
and ordering criteria. It allows to use database aggregation function
like MIN, MAX, SUM, AVG, and COUNT. Report Query returns results as collection
of ReportQueryResult objects, which is similar to DatabaseRow
or Map. The following code snippet shows how to fetch aggregate
data using ReportQuery
ExpressionBuilder emp = new ExpressionBuilder();
// Construct Report Query ReportQuery query = new ReportQuery(emp); query.setReferenceClass(Employees.class);
// Add departmentId to the querys result query.addAttribute("departmentId", emp.get("departmentId"));
// Add Count, MIN, MAX, AVG, SUM Aggregate functions to query result query.addCount(); query.addMinimum("min-salary",emp.get("salary")); query.addMaximum("max-salary", emp.get("salary")); query.addAverage("average-salary", emp.get("salary").); query.addSum("sum-salary",emp.get("salary"));
// Construct Date object for specifying criteria Calendar fromDate = new GregorianCalendar(1900,0,1); Date from = new Date(fromDate.getTime().getTime()); // Set Selection Criteria query.setSelectionCriteria(emp.get("hireDate").roundDate("year").greaterThan(from));
// Set Order by clause query.addOrdering(emp.get("departmentId"));
// Set Group by clause query.addGrouping(emp.get("departmentId"));
// Execute the report query Vector reports = (Vector) session.executeQuery(query);
Bookmark
