Re: performing count

From: Lachlan Deck (lachlan.dec..mail.com)
Date: Fri Jun 01 2007 - 23:03:50 EDT

  • Next message: marco turchi: "Re: UTF8 problem"

    On 02/06/2007, at 11:29 AM, Michael Gentry wrote:

    > Also, the query would most likely
    > need to be SQLTemplate-compatible, not an Expression or Query
    > (although maybe this can be done -- I'll have to explore). Keep in
    > mind this is going outside of the ORM arena to a degree. A row count
    > isn't being mapped to Java classes with relationships/attributes/etc.
    > You can't persist it back to the DB. :-)

    How about something like this? (Completely untested but looks right)

    ObjectContext oc; // assume exists

    int count = new StatsQuery.Count(SomeClass.class[,
    anExpression]).intResult(oc);
    int max = new StatsQuery.Max(SomeClass.class, "age"[,
    anExpression]).intResult(oc);
    int min = new StatsQuery.Min(SomeClass.class, "age"[,
    anExpression]).intResult(oc);
    int sum = new StatsQuery.Sum(SomeClass.class, "age"[,
    anExpression]).intResult(oc);

    with regards, 

    --

    Lachlan Deck

    import java.util.Map;
import org.apache.cayenne.CayenneRuntimeException;
import org.apache.cayenne.ObjectContext;
import org.apache.cayenne.exp.Expression;
import org.apache.cayenne.map.DbEntity;
import org.apache.cayenne.map.EntityResolver;
import org.apache.cayenne.map.ObjAttribute;
import org.apache.cayenne.map.ObjEntity;
import org.apache.cayenne.query.IndirectQuery;
import org.apache.cayenne.query.Query;
import org.apache.cayenne.query.SQLTemplate;

    /**
   *..uthor ldeck
   */
public abstract class StatsQuery extends IndirectQuery {

    	protected Class objClass;
	protected Expression expression;
	protected String statsType;
	protected String attributeName;

    	protected StatsQuery(Class objClass, Expression e, String statsType) {
		this(objClass, e, statsType, "*");
	}
	protected StatsQuery(Class objClass, Expression e, String statsType,  
String attributeName) {
		this.objClass = objClass;
		this.expression = e;
		this.statsType = statsType;
		this.attributeName = attributeName;
	}
	protected Query createReplacementQuery(EntityResolver resolver) {
		ObjEntity objEntity = resolver.lookupObjEntity(this.objClass);
		if (objEntity == null)
			throw new CayenneRuntimeException("No ObjEntity is mapped for java  
class: " + this.objClass.getName());

    		DbEntity dbEntity = objEntity.getDbEntity();
		if (dbEntity == null)
			throw new CayenneRuntimeException("No DbEntity is mapped for java  
class: " + this.objClass.getName());

    		String dbAttributeName = ((ObjAttribute) objEntity.getAttribute 
(this.attributeName)).getDbAttribute().getName();
		StringBuffer sql = new StringBuffer().append("SELECT #result('" +  
this.statsType + "(" + dbAttributeName + ")' 'int' 'C') FROM " +
				dbEntity.getName());

    		// TODO: (lachlan) determine if expression needs translating to  
dbAttribute equivalent keys
		if (this.expression != null)
			sql.append(" WHERE " + objEntity.translateToDbPath 
(this.expression).toString());

    		SQLTemplate replacement = new SQLTemplate(dbEntity, sql.toString());
		replacement.setFetchingDataRows(true);
		return replacement;
	}
	public int intResult(ObjectContext oc) {
		return queryResult(oc).intValue();
	}
	public Number queryResult(ObjectContext oc) {
		Map row = (Map) oc.performQuery(this).get(0);
		return (Number) row.get("C");
	}

    	//-------------------------------------------------------
	// Statistical helpers
	//-------------------------------------------------------

    	public static class Count extends StatsQuery {
		public Count(Class objClass) {
			this(objClass, null);
		}
		public Count(Class objClass, Expression exp) {
			super(objClass, exp, "count");
		}
	}
	public static class Max extends StatsQuery {
		public Max(Class objClass, String attributeName) {
			this(objClass, null, attributeName);
		}
		public Max(Class objClass, String attributeName, Expression exp) {
			super(objClass, exp, "max", attributeName);
		}
	}
	public static class Min extends StatsQuery {
		public Min(Class objClass, String attributeName) {
			this(objClass, null, attributeName);
		}
		public Min(Class objClass, String attributeName, Expression exp) {
			super(objClass, exp, "min", attributeName);
		}
	}
	public static class Sum extends StatsQuery {
		public Sum(Class objClass, String attributeName) {
			this(objClass, null, attributeName);
		}
		public Sum(Class objClass, String attributeName, Expression exp) {
			super(objClass, exp, "sum", attributeName);
		}
	}
}

    This archive was generated by hypermail 2.0.0 : Fri Jun 01 2007 - 23:04:29 EDT