package org.apache.torque.util;
/*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*/
import java.io.IOException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
import org.apache.commons.collections.OrderedMapIterator;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.torque.Column;
import org.apache.torque.ColumnImpl;
import org.apache.torque.TorqueException;
import org.apache.torque.criteria.SqlEnum;
import org.apache.torque.om.mapper.ObjectListMapper;
import org.apache.torque.om.mapper.RecordMapper;
import org.apache.torque.sql.SqlBuilder;
import org.apache.torque.util.functions.SQLFunction;
/**
* <p>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)</p>
*
* <P>Here is a simple example to generate the results of a query like:</P>
*
* <pre>
* SELECT EMPLOYEE, SUM(HOURS), MIN(HOURS), MAX(HOURS)
* FROM TIMESHEET WHERE TYPE = 1 GROUP BY EMPLOYEE ORDER BY EMPLOYEE ASC
* </pre>
* <p>Use the following code</p>
* <pre>
* 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 );
* </pre>
* <p>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.
* </p>
* <pre>
* String emp = results.get("EMPLOYEE").asString();
* int hours = results.get("Hours").asInt();
* </pre>
* <p>
* Notes:</p>
* <p>
* 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.</p>
* <p>
* This will also work with joined tables if the criteria is creates as
* to create valid SQL.</p>
*
* @author <a href="mailto:greg.monroe@dukece.com">Greg Monroe</a>
* @version $Id: SummaryHelper.java 1448414 2013-02-20 21:06:35Z tfischer $
*/
public class SummaryHelper
{
/** The class log. */
private static Log logger = LogFactory.getLog(SummaryHelper.class);
/** A list of the group by columns. */
private List<Column> groupByColumns;
/** A ListOrderMapCI<String, Aggregate.Function> with the aggregate functions
* to use in generating results. */
private ListOrderedMapCI aggregates;
/** Flag for excluding unnamed columns. */
private boolean excludeExprColumns = false;
/**
* 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.
*
* @param crit The base criteria to build on.
*
* @return Results as a OrderMap<String, List<Object>> object.
*
* @throws TorqueException if a database error occurs.
*
* @deprecated please use
* summarize(org.apache.torque.criteria.Criteria)
* instead.
* This method will be removed in a future version of Torque.
*/
@Deprecated
public List<ListOrderedMapCI> summarize(Criteria crit)
throws TorqueException
{
return summarize(crit, (List<Class<?>>) null);
}
/**
* 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.
*
* @param crit The base criteria to build on.
*
* @return Results as a OrderMap<String, List<Object>> object.
*
* @throws TorqueException if a database error occurs.
*/
public List<ListOrderedMapCI> summarize(
org.apache.torque.criteria.Criteria crit)
throws TorqueException
{
return summarize(crit, (List<Class<?>>) null);
}
/**
* 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.
*
* @param crit The base criteria to build on.
* @param 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.
*
* @return Results as a ListOrderMapCI<String, List<Object>> object.
*
* @throws TorqueException if a database error occurs.
*
* @deprecated Please use
* summarize(org.apache.torque.criteria.Criteria, List<Class<?>>)
* instead.
* This method will be removed in a future version of Torque.
*/
@Deprecated
public List<ListOrderedMapCI> summarize(
Criteria crit,
List<Class<?>> resultTypes)
throws TorqueException
{
Connection connection = null;
try
{
connection = Transaction.begin(crit.getDbName());
List<ListOrderedMapCI> result = summarize(crit, resultTypes, connection);
Transaction.commit(connection);
connection = null;
return result;
}
finally
{
if (connection != null)
{
Transaction.safeRollback(connection);
}
}
}
/**
* 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.
*
* @param crit The base criteria to build on.
* @param 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.
*
* @return Results as a ListOrderMapCI<String, List<Object>> object.
*
* @throws TorqueException if a database error occurs.
*/
public List<ListOrderedMapCI> summarize(
org.apache.torque.criteria.Criteria crit,
List<Class<?>> resultTypes)
throws TorqueException
{
Connection connection = null;
try
{
connection = Transaction.begin(crit.getDbName());
List<ListOrderedMapCI> result = summarize(crit, resultTypes, connection);
Transaction.commit(connection);
connection = null;
return result;
}
finally
{
if (connection != null)
{
Transaction.safeRollback(connection);
}
}
}
/**
* 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.
*
* @param crit The base criteria to build on.
* @param conn The DB Connection to use.
*
* @return Results as a OrderMap<String, List<Object>> object.
*
* @throws TorqueException if a database error occurs.
*
* @deprecated please use
* summarize(org.apache.torque.criteria.Criteria, Connection)
* instead.
* This method will be removed in a future version of Torque.
*/
@Deprecated
public List<ListOrderedMapCI> summarize(Criteria crit, Connection conn)
throws TorqueException
{
return summarize(crit, null, conn);
}
/**
* 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.
*
* @param crit The base criteria to build on.
* @param conn The DB Connection to use.
*
* @return Results as a OrderMap<String, List<Object>> object.
*
* @throws TorqueException if a database error occurs.
*/
public List<ListOrderedMapCI> summarize(
org.apache.torque.criteria.Criteria crit,
Connection conn)
throws TorqueException
{
return summarize(crit, null, conn);
}
/**
* 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.
*
* @param crit The base criteria to build on.
* @param 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.
* @param conn The DB Connection to use.
*
* @return Results as a ListOrderedMapCI<String,Values> object.
*
* @throws TorqueException if a database error occurs.
*
* @deprecated please use
* summarize(org.apache.torque.criteria.Criteria, List<Class<?>>, Connection)
* instead.
* This method will be removed in a future version of Torque.
*/
@Deprecated
public List<ListOrderedMapCI> summarize(
Criteria crit,
List<Class<?>> resultTypes,
Connection conn)
throws TorqueException
{
Criteria c = buildCriteria(crit);
// TODO use BasePeerImpl.doSelect instead of parsing the result manually
String query = SqlBuilder.buildQuery(c).toString();
RecordMapper<List<Object>> mapper = new ObjectListMapper(resultTypes);
Statement statement = null;
ResultSet resultSet = null;
List<List<Object>> rows = new ArrayList<List<Object>>();
try
{
statement = conn.createStatement();
long startTime = System.currentTimeMillis();
logger.debug("Executing query " + query);
resultSet = statement.executeQuery(query.toString());
long queryEndTime = System.currentTimeMillis();
logger.trace("query took " + (queryEndTime - startTime)
+ " milliseconds");
while (resultSet.next())
{
List<Object> rowResult = mapper.processRow(resultSet, 0, null);
rows.add(rowResult);
}
long mappingEndTime = System.currentTimeMillis();
logger.trace("mapping took " + (mappingEndTime - queryEndTime)
+ " milliseconds");
}
catch (SQLException e)
{
throw new TorqueException(e);
}
finally
{
if (resultSet != null)
{
try
{
resultSet.close();
}
catch (SQLException e)
{
logger.warn("error closing resultSet", e);
}
}
if (statement != null)
{
try
{
statement.close();
}
catch (SQLException e)
{
logger.warn("error closing statement", e);
}
}
}
List<ListOrderedMapCI> resultsList = new Vector<ListOrderedMapCI>(rows.size());
List<String> columnNames = new ArrayList<String>();
for (Column column : c.getSelectColumns())
{
columnNames.add(column.getColumnName());
}
columnNames.addAll(c.getAsColumns().keySet());
for (List<Object> row : rows)
{
ListOrderedMapCI recordMap = new ListOrderedMapCI();
for (int i = 0; i < row.size(); i++)
{
Object value = row.get(i);
String cName = columnNames.get(i);
if (cName == null || cName.equals(""))
{
if (excludeExprColumns())
{
continue;
}
cName = "Expr" + i;
}
recordMap.put(cName, value);
}
resultsList.add(recordMap);
}
return resultsList;
}
/**
* 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.
*
* @param crit The base criteria to build on.
* @param 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.
* @param conn The DB Connection to use.
*
* @return Results as a ListOrderedMapCI<String,Values> object.
*
* @throws TorqueException if a database error occurs.
*/
public List<ListOrderedMapCI> summarize(
org.apache.torque.criteria.Criteria crit,
List<Class<?>> resultTypes,
Connection conn)
throws TorqueException
{
org.apache.torque.criteria.Criteria c = buildCriteria(crit);
// TODO use BasePeerImpl.doSelect instead of parsing the result manually
String query = SqlBuilder.buildQuery(c).toString();
RecordMapper<List<Object>> mapper = new ObjectListMapper(resultTypes);
Statement statement = null;
ResultSet resultSet = null;
List<List<Object>> rows = new ArrayList<List<Object>>();
try
{
statement = conn.createStatement();
long startTime = System.currentTimeMillis();
logger.debug("Executing query " + query);
resultSet = statement.executeQuery(query.toString());
long queryEndTime = System.currentTimeMillis();
logger.trace("query took " + (queryEndTime - startTime)
+ " milliseconds");
while (resultSet.next())
{
List<Object> rowResult = mapper.processRow(resultSet, 0, null);
rows.add(rowResult);
}
long mappingEndTime = System.currentTimeMillis();
logger.trace("mapping took " + (mappingEndTime - queryEndTime)
+ " milliseconds");
}
catch (SQLException e)
{
throw new TorqueException(e);
}
finally
{
if (resultSet != null)
{
try
{
resultSet.close();
}
catch (SQLException e)
{
logger.warn("error closing resultSet", e);
}
}
if (statement != null)
{
try
{
statement.close();
}
catch (SQLException e)
{
logger.warn("error closing statement", e);
}
}
}
List<ListOrderedMapCI> resultsList = new Vector<ListOrderedMapCI>(rows.size());
List<String> columnNames = new ArrayList<String>();
for (Column column : c.getSelectColumns())
{
columnNames.add(column.getColumnName());
}
columnNames.addAll(c.getAsColumns().keySet());
for (List<Object> row : rows)
{
ListOrderedMapCI recordMap = new ListOrderedMapCI();
for (int i = 0; i < row.size(); i++)
{
Object value = row.get(i);
String cName = columnNames.get(i);
if (cName == null || cName.equals(""))
{
if (excludeExprColumns())
{
continue;
}
cName = "Expr" + i;
}
recordMap.put(cName, value);
}
resultsList.add(recordMap);
}
return resultsList;
}
/**
* Builds the criteria to use in summarizing the information. Note that
* the criteria passed in will be modified.
*
* @param c The base criteria to build the summary criteria from.
* @return A criteria to use in summarizing the information.
* @throws TorqueException
*
* @deprecated please use
* buildCriteria(org.apache.torque.criteria.Criteria)
* instead.
* This method will be removed in a future version of Torque.
*/
@Deprecated
public Criteria buildCriteria(Criteria c) throws TorqueException
{
c.getSelectColumns().clear();
c.getGroupByColumns().clear();
UniqueList<String> criteriaSelectModifiers;
criteriaSelectModifiers = c.getSelectModifiers();
if (criteriaSelectModifiers != null
&& criteriaSelectModifiers.size() > 0
&& criteriaSelectModifiers.contains(SqlEnum.DISTINCT.toString()))
{
criteriaSelectModifiers.remove(SqlEnum.DISTINCT.toString());
}
c.setIgnoreCase(false);
List<Column> cols = getGroupByColumns();
boolean haveFromTable = !cols.isEmpty(); // Group By cols define src table.
for (Column col : cols)
{
c.addGroupByColumn(col);
c.addSelectColumn(col);
}
if (haveFromTable)
{
logger.debug("From table defined by Group By Cols");
}
// Check if the from table is set via a where clause.
if (!haveFromTable && !c.isEmpty())
{
haveFromTable = true;
logger.debug("From table defined by a where clause");
}
ListOrderedMapCI cMap = getAggregates();
OrderedMapIterator iMap = cMap.orderedMapIterator();
while (iMap.hasNext())
{
String key = (String) iMap.next();
SQLFunction f = (SQLFunction) iMap.getValue();
Column col = f.getColumn();
c.addAsColumn(key, new ColumnImpl(
null,
col.getTableName(),
col.getColumnName(),
f.getSqlExpression()));
if (!haveFromTable) // Last chance. Get it from the func.
{
{
// Kludgy Where table.col = table.col clause to force
// from table identification.
c.add(col,
(col.getColumnName()
+ "=" + col.getColumnName()),
SqlEnum.CUSTOM);
haveFromTable = true;
String table = col.getTableName();
logger.debug("From table, '" + table
+ "', defined from aggregate column");
}
}
}
if (!haveFromTable)
{
throw new TorqueException(
"No FROM table defined by the GroupBy set, "
+ "criteria.setAlias, or specified function column!");
}
return c;
}
/**
* Builds the criteria to use in summarizing the information. Note that
* the criteria passed in will be modified.
*
* @param c The base criteria to build the summary criteria from.
* @return A criteria to use in summarizing the information.
* @throws TorqueException
*/
public org.apache.torque.criteria.Criteria buildCriteria(
org.apache.torque.criteria.Criteria c) throws TorqueException
{
c.getSelectColumns().clear();
c.getGroupByColumns().clear();
UniqueList<String> criteriaSelectModifiers;
criteriaSelectModifiers = c.getSelectModifiers();
if (criteriaSelectModifiers != null
&& criteriaSelectModifiers.size() > 0
&& criteriaSelectModifiers.contains(SqlEnum.DISTINCT.toString()))
{
criteriaSelectModifiers.remove(SqlEnum.DISTINCT.toString());
}
c.setIgnoreCase(false);
List<Column> cols = getGroupByColumns();
boolean haveFromTable = !cols.isEmpty(); // Group By cols define src table.
for (Column col : cols)
{
c.addGroupByColumn(col);
c.addSelectColumn(col);
}
if (haveFromTable)
{
logger.debug("From table defined by Group By Cols");
}
// Check if the from table is set via a where clause.
if (!haveFromTable && c.getTopLevelCriterion() != null)
{
haveFromTable = true;
logger.debug("From table defined by a where clause");
}
ListOrderedMapCI cMap = getAggregates();
OrderedMapIterator iMap = cMap.orderedMapIterator();
while (iMap.hasNext())
{
String key = (String) iMap.next();
SQLFunction f = (SQLFunction) iMap.getValue();
Column col = f.getColumn();
c.addAsColumn(key, new ColumnImpl(
null,
col.getTableName(),
col.getColumnName(),
f.getSqlExpression()));
if (!haveFromTable) // Last chance. Get it from the func.
{
{
// Kludgy Where table.col = table.col clause to force
// from table identification.
c.and(col,
(col.getColumnName()
+ "=" + col.getColumnName()),
SqlEnum.CUSTOM);
haveFromTable = true;
String table = col.getTableName();
logger.debug("From table, '" + table
+ "', defined from aggregate column");
}
}
}
if (!haveFromTable)
{
throw new TorqueException(
"No FROM table defined by the GroupBy set, "
+ "criteria.setAlias, or specified function column!");
}
return c;
}
/**
* <p>
* 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.,
* </p>
* <pre>
* add(TablePeer.COL1);
* add(TablePeer.COL2);
* </pre>
*
* <p>Generates SQL like: SELECT .... GROUP BY Table.COL1, TABLE.COL2</p>
*
* @param column
*/
public void addGroupBy(Column column)
{
getGroupByColumns().add(column);
}
/**
* Add in an Aggregate function to the summary information.
*
* @param alias A valid SQL99 column identifier ([_A-Z0-9] no spaces and
* no key words, e.g. function names.
* @param function One of the inner classes from the Aggregate class.
*/
public void addAggregate(String alias, SQLFunction function)
{
getAggregates().put(alias, function);
}
/**
* Resets the class internal variables to their initial states so
* the class can be re-used like a new class.
*/
public void clear()
{
getGroupByColumns().clear();
getAggregates().clear();
setExcludeExprColumns(false);
}
public List<Column> getGroupByColumns()
{
if (groupByColumns == null)
{
groupByColumns = new Vector<Column>();
}
return groupByColumns;
}
/**
* 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.
*
* @return the avgColumns. Will always return a ListOrderedMap object.
*/
public ListOrderedMapCI getAggregates()
{
if (aggregates == null)
{
aggregates = new ListOrderedMapCI();
}
return aggregates;
}
/**
* 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.
*
* @param out
* @param results
* @param includeHeader
* @throws IOException
*/
public void dumpResults(Writer out, List<?> results, boolean includeHeader)
throws IOException
{
Iterator<?> i = results.iterator();
boolean first = includeHeader;
while (i.hasNext())
{
ListOrderedMapCI rec = (ListOrderedMapCI) i.next();
OrderedMapIterator rI = rec.orderedMapIterator();
StringBuilder heading = new StringBuilder();
StringBuilder recString = new StringBuilder();
while (rI.hasNext())
{
String colId = (String) rI.next();
if (first)
{
heading.append("\"").append(colId).append("\"");
if (rI.hasNext())
{
heading.append(", ");
}
}
Object v = rI.getValue();
recString.append(v.toString());
if (rI.hasNext())
{
recString.append(", ");
}
}
if (first)
{
first = false;
out.write(heading.toString());
out.write("\n");
}
out.write(recString.toString());
out.write("\n");
}
}
/**
* Should the results include unnamed columns, e.g. EXPR{index#}.
*
* @return the excludeExprColumns
*/
public boolean excludeExprColumns()
{
return excludeExprColumns;
}
/**
* <p>Define if unnamed output columns which get labeled as EXPR{index#})
* should be included in the the output set.</p>
* <p>
* Note these are generally added by the criteria
* processing to handle special cases such as case insensitive ordering.
* </p>
*
* @param excludeExprColumns if True, these columns won't be included.
*/
public void setExcludeExprColumns(boolean excludeExprColumns)
{
this.excludeExprColumns = excludeExprColumns;
}
}