Overview | Package | Class | Use | Tree | Deprecated | Index | Help
PREV CLASS NEXT CLASS | FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD

## org.apache.torque.util
Class SummaryHelper

    java.lang.Object
      org.apache.torque.util.SummaryHelper

    public class SummaryHelper

extends Object

A utility to help produce aggregate summary information about a table. The default assumes that the underlying DB supports the SQL 99 Standard Aggregate functions, e.g. COUNT, SUM, AVG, MAX, & MIN. However, some non-standard functions (like MySQL's older LEAST instead of MIN can be handled programatically if needed (@see Aggregate class)

Here is a simple example to generate the results of a query like:

     SELECT EMPLOYEE, SUM(HOURS), MIN(HOURS), MAX(HOURS)
         FROM TIMESHEET WHERE TYPE = 1 GROUP BY EMPLOYEE ORDER BY EMPLOYEE ASC

Use the following code

        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.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 a simple way to do this. See the dumpResults* method code for a more complex example.

        String emp = results.get("EMPLOYEE").asString();
        int hours = results.get("Hours").asInt();

Notes:

If there are no group by columns specified, the aggregate is over the whole table. The from table is defined either via the Criteria.addAlias(...) method or by the first table prefix in an aggregate function.

This will also work with joined tables if the criteria is creates as to create valid SQL.

Version:
  $Id: SummaryHelper.java 1448414 2013-02-20 21:06:35Z tfischer $
Author:
  Greg Monroe

Constructor Summary
`SummaryHelper()`

Method Summary
`void` | `addAggregate(String alias, SQLFunction function)` Add in an Aggregate function to the summary information.
`void` | `addGroupBy(Column column)` Add a column that will be used to group the aggregate results by.
`Criteria` | `buildCriteria(Criteria c)` Deprecated. please use buildCriteria(org.apache.torque.criteria.Criteria) instead. This method will be removed in a future version of Torque.
`Criteria` | `buildCriteria(Criteria c)` Builds the criteria to use in summarizing the information.
`void` | `clear()` Resets the class internal variables to their initial states so the class can be re-used like a new class.
`void` | `dumpResults(Writer out, List<?> results, boolean includeHeader)` Convenience method to dump a summary results list to an output writer in a semi-CSV format.
`boolean` | `excludeExprColumns()` Should the results include unnamed columns, e.g.
`ListOrderedMapCI` | `getAggregates()` Get the order map list of aggregate functions to use in summarizing this table's informations.
`List<Column>` | `getGroupByColumns()`
`void` | `setExcludeExprColumns(boolean excludeExprColumns)` Define if unnamed output columns which get labeled as EXPR{index#}) should be included in the the output set.
`List<ListOrderedMapCI>` | `summarize(Criteria crit)` Deprecated. please use summarize(org.apache.torque.criteria.Criteria) instead. This method will be removed in a future version of Torque.
`List<ListOrderedMapCI>` | `summarize(Criteria crit)` Return a list of ListOrderedMapCI objects with the results of the summary query.
`List<ListOrderedMapCI>` | `summarize(Criteria crit, Connection conn)` Deprecated. please use summarize(org.apache.torque.criteria.Criteria, Connection) instead. This method will be removed in a future version of Torque.
`List<ListOrderedMapCI>` | `summarize(Criteria crit, Connection conn)` Return a list of OrderedMap objects with the results of the summary query.
`List<ListOrderedMapCI>` | `summarize(Criteria crit, List<Class<?>> resultTypes)` Deprecated. Please use summarize(org.apache.torque.criteria.Criteria, List>) instead. This method will be removed in a future version of Torque.
`List<ListOrderedMapCI>` | `summarize(Criteria crit, List<Class<?>> resultTypes)` Return a list of ListOrderedMapCI objects with the results of the summary query.
`List<ListOrderedMapCI>` | `summarize(Criteria crit, List<Class<?>> resultTypes, Connection conn)` Deprecated. please use summarize(org.apache.torque.criteria.Criteria, List>, Connection) instead. This method will be removed in a future version of Torque.
`List<ListOrderedMapCI>` | `summarize(Criteria crit, List<Class<?>> resultTypes, Connection conn)` Return a list of ListOrderedMapCI objects with the results of the summary query.

Methods inherited from class java.lang.Object
`clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

Constructor Detail

### SummaryHelper

    public SummaryHelper()

Method Detail

### summarize

    @Deprecated
    public List<ListOrderedMapCI> summarize(Criteria crit)
                                     throws TorqueException

  Deprecated. please use summarize(org.apache.torque.criteria.Criteria) instead. This method will be removed in a future version of Torque.

  Return a list of ListOrderedMapCI objects with the results of the summary query. The ListOrderedMapCI objects have a key of the column name or function alias and are in the order generated by the query. The class of the return values are decided by the database driver, which makes this method not database independent.

  Parameters:
    `crit` - The base criteria to build on.
  Returns:
    Results as a OrderMap> object.
  Throws:
    `TorqueException` - if a database error occurs.

### summarize

    public List<ListOrderedMapCI> summarize(Criteria crit)
                                     throws TorqueException

  Return a list of ListOrderedMapCI objects with the results of the summary query. The ListOrderedMapCI objects have a key of the column name or function alias and are in the order generated by the query. The class of the return values are decided by the database driver, which makes this method not database independent.

  Parameters:
    `crit` - The base criteria to build on.
  Returns:
    Results as a OrderMap> object.
  Throws:
    `TorqueException` - if a database error occurs.

### summarize

    @Deprecated
    public List<ListOrderedMapCI> summarize(Criteria crit,
                                                       List<Class<?>> resultTypes)
                                     throws TorqueException

  Deprecated. Please use summarize(org.apache.torque.criteria.Criteria, List>) instead. This method will be removed in a future version of Torque.

  Return a list of ListOrderedMapCI objects with the results of the summary query. The ListOrderedMapCI objects have a key of the column name or function alias and are in the order generated by the query.

  Parameters:
    `crit` - The base criteria to build on.
    `resultTypes` - the classes to which the return values of the query should be cast, or null to let the database driver decide. See org.apache.torque.om.mapper.ObjectListMapperï¿½for the supported classes.
  Returns:
    Results as a ListOrderMapCI> object.
  Throws:
    `TorqueException` - if a database error occurs.

### summarize

    public List<ListOrderedMapCI> summarize(Criteria crit,
                                            List<Class<?>> resultTypes)
                                     throws TorqueException

  Return a list of ListOrderedMapCI objects with the results of the summary query. The ListOrderedMapCI objects have a key of the column name or function alias and are in the order generated by the query.

  Parameters:
    `crit` - The base criteria to build on.
    `resultTypes` - the classes to which the return values of the query should be cast, or null to let the database driver decide. See org.apache.torque.om.mapper.ObjectListMapperï¿½for the supported classes.
  Returns:
    Results as a ListOrderMapCI> object.
  Throws:
    `TorqueException` - if a database error occurs.

### summarize

    @Deprecated
    public List<ListOrderedMapCI> summarize(Criteria crit,
                                                       Connection conn)
                                     throws TorqueException

  Deprecated. please use summarize(org.apache.torque.criteria.Criteria, Connection) instead. This method will be removed in a future version of Torque.

  Return a list of OrderedMap objects with the results of the summary query. The OrderedMap objects have a key of the column name or function alias and are in the order generated by the query. The class of the return values are decided by the database driver, which makes this method not database independent.

  Parameters:
    `crit` - The base criteria to build on.
    `conn` - The DB Connection to use.
  Returns:
    Results as a OrderMap> object.
  Throws:
    `TorqueException` - if a database error occurs.

### summarize

    public List<ListOrderedMapCI> summarize(Criteria crit,
                                            Connection conn)
                                     throws TorqueException

  Return a list of OrderedMap objects with the results of the summary query. The OrderedMap objects have a key of the column name or function alias and are in the order generated by the query. The class of the return values are decided by the database driver, which makes this method not database independent.

  Parameters:
    `crit` - The base criteria to build on.
    `conn` - The DB Connection to use.
  Returns:
    Results as a OrderMap> object.
  Throws:
    `TorqueException` - if a database error occurs.

### summarize

    @Deprecated
    public List<ListOrderedMapCI> summarize(Criteria crit,
                                                       List<Class<?>> resultTypes,
                                                       Connection conn)
                                     throws TorqueException

  Deprecated. please use summarize(org.apache.torque.criteria.Criteria, List>, Connection) instead. This method will be removed in a future version of Torque.

  Return a list of ListOrderedMapCI objects with the results of the summary query. The ListOrderedMapCI objects have a key of the column name or function alias and are in the order generated by the query.

  Parameters:
    `crit` - The base criteria to build on.
    `resultTypes` - the classes to which the return values of the query should be cast, or null to let the database driver decide. See org.apache.torque.om.mapper.ObjectListMapperï¿½for the supported classes.
    `conn` - The DB Connection to use.
  Returns:
    Results as a ListOrderedMapCI object.
  Throws:
    `TorqueException` - if a database error occurs.

### summarize

    public List<ListOrderedMapCI> summarize(Criteria crit,
                                            List<Class<?>> resultTypes,
                                            Connection conn)
                                     throws TorqueException

  Return a list of ListOrderedMapCI objects with the results of the summary query. The ListOrderedMapCI objects have a key of the column name or function alias and are in the order generated by the query.

  Parameters:
    `crit` - The base criteria to build on.
    `resultTypes` - the classes to which the return values of the query should be cast, or null to let the database driver decide. See org.apache.torque.om.mapper.ObjectListMapperï¿½for the supported classes.
    `conn` - The DB Connection to use.
  Returns:
    Results as a ListOrderedMapCI object.
  Throws:
    `TorqueException` - if a database error occurs.

### buildCriteria

    @Deprecated
    public Criteria buildCriteria(Criteria c)
                           throws TorqueException

  Deprecated. please use buildCriteria(org.apache.torque.criteria.Criteria) instead. This method will be removed in a future version of Torque.

  Builds the criteria to use in summarizing the information. Note that the criteria passed in will be modified.

  Parameters:
    `c` - The base criteria to build the summary criteria from.
  Returns:
    A criteria to use in summarizing the information.
  Throws:
    `TorqueException`

### buildCriteria

    public Criteria buildCriteria(Criteria c)
                           throws TorqueException

  Builds the criteria to use in summarizing the information. Note that the criteria passed in will be modified.

  Parameters:
    `c` - The base criteria to build the summary criteria from.
  Returns:
    A criteria to use in summarizing the information.
  Throws:
    `TorqueException`

### addGroupBy

    public void addGroupBy(Column column)

  Add a column that will be used to group the aggregate results by. This is a first added / first listed on SQL method. E.g.,

          add(TablePeer.COL1);
          add(TablePeer.COL2);

  Generates SQL like: SELECT .... GROUP BY Table.COL1, TABLE.COL2

  Parameters:
    `column` -

### addAggregate

    public void addAggregate(String alias,
                             SQLFunction function)

  Add in an Aggregate function to the summary information.

  Parameters:
    `alias` - A valid SQL99 column identifier ([_A-Z0-9] no spaces and no key words, e.g. function names.
    `function` - One of the inner classes from the Aggregate class.

### clear

    public void clear()

  Resets the class internal variables to their initial states so the class can be re-used like a new class.

### getGroupByColumns

    public List<Column> getGroupByColumns()

### getAggregates

    public ListOrderedMapCI getAggregates()

  Get the order map list of aggregate functions to use in summarizing this table's informations. The key is used as the result column alias.

  Returns:
    the avgColumns. Will always return a ListOrderedMap object.

### dumpResults

    public void dumpResults(Writer out,
                            List<?> results,
                            boolean includeHeader)
                     throws IOException

  Convenience method to dump a summary results list to an output writer in a semi-CSV format. E.g., there is no handling of embedded quotes/special characters.

  Parameters:
    `out` -
    `results` -
    `includeHeader` -
  Throws:
    `IOException`

### excludeExprColumns

    public boolean excludeExprColumns()

  Should the results include unnamed columns, e.g. EXPR{index#}.

  Returns:
    the excludeExprColumns

### setExcludeExprColumns

    public void setExcludeExprColumns(boolean excludeExprColumns)

  Define if unnamed output columns which get labeled as EXPR{index#}) should be included in the the output set.

  Note these are generally added by the criteria processing to handle special cases such as case insensitive ordering.

  Parameters:
    `excludeExprColumns` - if True, these columns won't be included.

Overview | Package | Class | Use | Tree | Deprecated | Index | Help
PREV CLASS NEXT CLASS | FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD

Copyright © 2000-2013 The Apache Software Foundation. All Rights Reserved.