A Query Object is an interpreter [Gang of Four], that is, a structure of objects that can form itself into a SQL query. You can create this query by refer-ring to classes and fields rather than tables and columns. In this way those who write the queries can do so independently of the database schema and changes to the schema can be localized in a single place.
My purpose;
For my purposes, the Query Object is used as a means to unify a singular query device throughout the application stack. This entails allowing querying from the UI to the DAO. The importance is not to limit the creativity of the developer or impose redundant service / find methods. Of other importance is to insure the DTO nature of the object and not attach it to a specific framework or language.
In Java;
The Java implementation is based on the builder pattern and the original hibernate criteria query. It is based around a Find object and supported by Restriction objects. Finally, it is processed by a Converter.
Find.java
package com.wookets.query.dto;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.proxy.HibernateProxy;
import com.wookets.core.model.PersistentObject;
import com.wookets.query.constant.RestrictionSort;
import com.wookets.query.constant.RestrictionType;
public class Find {
private PersistentObject rootObject; // T stands for 'Type'
private List restrictions = new ArrayList();
public Find() {
this.add("active", RestrictionType.Eq, true); // add default restrictions
}
// from
public Find from(Class resourceClass) {
try {
this.rootObject = resourceClass.newInstance();
} catch(final Exception e) {
throw new RuntimeException(e.getMessage());
}
return this;
}
// generic restriction adding...
public Find add(String property, RestrictionType type, Object value) {
// don't duplicate adds, override value
for(final Restriction r : this.restrictions) {
if(r.getProperty() == null) {
if(type.equals(r.getType())) {
r.setValue(value);
return this;
}
} else if(r.getProperty().equals(property) && type.equals(r.getType())) {
r.setValue(value);
return this;
}
}
// standard add
this.restrictions.add(new Restriction(property, type, value));
return this;
}
// select
public Find select(String... properties) {
for(String property : properties) {
this.add(property, RestrictionType.Select, null);
}
return this;
}
// fetch (not only join, but load the data as well)
/*
* public Find fetch(String... properties) { for(String property : properties) { this.add(property,
* RestrictionType.Fetch, null); } return this; }
*/
// join
public Find join(String... properties) {
for(String property : properties) {
this.add(property, RestrictionType.Join, null);
}
return this;
}
// alias
public Find aliasSelect(String selectProperty, String alias) {
return add(selectProperty, RestrictionType.Select, alias);
}
public Find aliasJoin(String joinProperty, String alias) {
return add(joinProperty, RestrictionType.Join, alias);
}
// where
public Find eq(String property, Object value) {
return this.add(property, RestrictionType.Eq, value);
}
public Find ne(String property, Object value) {
return this.add(property, RestrictionType.Ne, value);
}
public Find le(String property, Object value) {
return this.add(property, RestrictionType.Le, value);
}
public Find ge(String property, Object value) {
return this.add(property, RestrictionType.Ge, value);
}
public Find lt(String property, Object value) {
return this.add(property, RestrictionType.Lt, value);
}
public Find gt(String property, Object value) {
return this.add(property, RestrictionType.Gt, value);
}
public Find between(String property, Object lowValue, Object highValue) {
this.add(property, RestrictionType.Gt, lowValue);
this.add(property, RestrictionType.Lt, highValue);
return this;
}
public Find in(String property, Object value) {
return this.add(property, RestrictionType.In, value);
}
public Find isNull(String property) {
return this.add(property, RestrictionType.IsNull, null);
}
public Find isNotNull(String property) {
return this.add(property, RestrictionType.IsNotNull, null);
}
public Find isEmpty(String property, String type) {
return this.add(property, RestrictionType.IsEmpty, type);
}
public Find like(String property, Object value) {
return this.like(property, value, RestrictionType.Match_Any);
}
public Find like(String property, Object value, RestrictionType matchType) {
return this.add(property, matchType, value);
}
// nestability
public Find or(Restriction restriction1, Restriction restriction2) {
final NestedRestriction r = new NestedRestriction(null, RestrictionType.Disjunction, null);
r.getNestedRestrictions().add(restriction1);
r.getNestedRestrictions().add(restriction2);
this.restrictions.add(r);
return this;
}
public Find and(Restriction restriction1, Restriction restriction2) {
final NestedRestriction r = new NestedRestriction(null, RestrictionType.Conjunction, null);
r.getNestedRestrictions().add(restriction1);
r.getNestedRestrictions().add(restriction2);
this.restrictions.add(r);
return this;
}
public Find disjunction(Restriction[] nestedRestrictions) {
final NestedRestriction r = new NestedRestriction(null, RestrictionType.Disjunction, null);
for(final Restriction rc : nestedRestrictions) {
r.getNestedRestrictions().add(rc);
}
this.restrictions.add(r);
return this;
}
public Find conjunction(Restriction[] nestedRestrictions) {
final NestedRestriction r = new NestedRestriction(null, RestrictionType.Conjunction, null);
for(final Restriction rc : nestedRestrictions) {
r.getNestedRestrictions().add(rc);
}
this.restrictions.add(r);
return this;
}
// order
public Find orderByAsc(String... properties) {
for(String property : properties) {
this.add(property, RestrictionType.OrderBy, RestrictionSort.Asc);
}
return this;
}
public Find orderByDesc(String... properties) {
for(String property : properties) {
this.add(property, RestrictionType.OrderBy, RestrictionSort.Desc);
}
return this;
}
// paging (limit)
public Find limit(Integer firstResult, Integer maxResults) {
this.add(null, RestrictionType.FirstResult, firstResult);
this.add(null, RestrictionType.MaxResults, maxResults);
return this;
}
// projection extras
public Find group(String property) {
return this.add(property, RestrictionType.Group, null);
}
// misc (this is needed for graniteds to inject values in to the object)
public List getRestrictions() {
return this.restrictions;
}
public void setRestrictions(List restrictions) {
this.restrictions = restrictions;
}
@SuppressWarnings("unchecked")
public Class getRootType() {
if(rootObject == null) {
throw new RuntimeException("Set a rootType using the 'from' method.");
}
return (Class) rootObject.getClass();
}
public PersistentObject getRootObject() {
return rootObject;
}
public void setRootObject(PersistentObject rootObject) {
this.rootObject = rootObject;
}
}
Restrictions.java
package com.wookets.query.dto;
import com.wookets.query.constant.RestrictionType;
public class Restriction {
private String property;
private RestrictionType type;
private Object value;
public Restriction() {
// needed for serialization and transfer of this object over a remote web service
}
public Restriction(String property, RestrictionType type, Object value) {
this.property = property;
this.type = type;
this.value = value;
}
public String getProperty() {
return this.property;
}
public void setProperty(String property) {
this.property = property;
}
public Object getValue() {
return this.value;
}
public void setValue(Object value) {
this.value = value;
}
public RestrictionType getType() {
return type;
}
public void setType(RestrictionType type) {
this.type = type;
}
}
Converter.java
package com.wookets.query.dao;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.wookets.core.model.PersistentObject;
import com.wookets.query.constant.RestrictionSort;
import com.wookets.query.constant.RestrictionType;
import com.wookets.query.dto.Find;
import com.wookets.query.dto.NestedRestriction;
import com.wookets.query.dto.Restriction;
/**
* This class will take a SearchableResource object and convert it in to hibernate criteria queries or something else...
*
* If null constraints object, it will be ignored, but the appOrg will still be added to constrain the query as a best
* practice.
*
* This is essentially v2 of the ConstraintConverter class, but more towards the Hibernate side of things.
*
* @author Wookets
*
*/
public class FindQueryConverter {
private static final Logger log = LoggerFactory.getLogger(FindQueryConverter.class);
/**
* This will use a Find object to run a query.
*
* @param find
* @param c
* @return
*/
public static Criteria convert(Find find, Criteria c) {
final boolean debug = log.isDebugEnabled();
if(debug) {
log.debug("Find for [" + find.getRootType() + "]");
}
// in case we have projections
final ProjectionList pl = Projections.projectionList();
// do we need to alias to a map?
boolean aliasToMap = false;
// loop thru restrictions
for(final Restriction r : find.getRestrictions()) {
if(debug) {
log.debug("Adding a restriction [" + r.getProperty() + "," + r.getType() + ","
+ r.getValue() + "]");
}
// do joins
if(RestrictionType.Join.equals(r.getType())) {
if(r.getValue() == null)
c.createAlias(r.getProperty(), r.getProperty(), Criteria.LEFT_JOIN);
else
c.createAlias(r.getProperty(), (String) r.getValue(), Criteria.LEFT_JOIN);
}
// order
else if(RestrictionType.OrderBy.equals(r.getType())) {
if(RestrictionSort.Asc.name().equals(r.getValue()))
c.addOrder(Order.asc(r.getProperty()));
else
c.addOrder(Order.desc(r.getProperty()));
}
// do select-projection
else if(RestrictionType.Select.equals(r.getType())) {
if(r.getValue() == null)
pl.add(Projections.property(r.getProperty()), r.getProperty());
else
pl.add(Projections.property(r.getProperty()), (String) r.getValue());
aliasToMap = true;
} else if(RestrictionType.Group.equals(r.getType())) {
if(r.getValue() == null)
pl.add(Projections.groupProperty(r.getProperty()), r.getProperty());
else
pl.add(Projections.groupProperty(r.getProperty()), (String) r.getValue());
} else if(RestrictionType.Average.equals(r.getType())) {
if(r.getValue() == null)
pl.add(Projections.avg(r.getProperty()), r.getProperty());
else
pl.add(Projections.avg(r.getProperty()), (String) r.getValue());
aliasToMap = true;
} else if(RestrictionType.Count.equals(r.getType())) {
if(r.getValue() == null)
pl.add(Projections.count(r.getProperty()), r.getProperty());
else
pl.add(Projections.count(r.getProperty()), (String) r.getValue());
aliasToMap = true;
} else if(RestrictionType.Max.equals(r.getType())) {
if(r.getValue() == null)
pl.add(Projections.max(r.getProperty()), r.getProperty());
else
pl.add(Projections.max(r.getProperty()), (String) r.getValue());
aliasToMap = true;
} else if(RestrictionType.Min.equals(r.getType())) {
if(r.getValue() == null)
pl.add(Projections.min(r.getProperty()), r.getProperty());
else
pl.add(Projections.min(r.getProperty()), (String) r.getValue());
aliasToMap = true;
} else if(RestrictionType.Sum.equals(r.getType())) {
if(r.getValue() == null)
pl.add(Projections.sum(r.getProperty()), r.getProperty());
else
pl.add(Projections.sum(r.getProperty()), (String) r.getValue());
aliasToMap = true;
}
else if(RestrictionType.FirstResult.equals(r.getType())) {
c.setFirstResult((Integer) r.getValue() < 0 ? 0 : (Integer) r.getValue());
} else if(RestrictionType.MaxResults.equals(r.getType())) {
c.setMaxResults((Integer) r.getValue());
}
// do where (restrictions)
else {
final Criterion ct = addWhereRestriction(r);
if(ct != null) {
c.add(ct);
}
}
}
// finally add the projection list to the criteria object
if(pl.getLength() > 0) {
c.setProjection(pl);
}
// do we need to alias to a map?
if(aliasToMap) {
c.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
}
return c;
}
@SuppressWarnings({ "rawtypes", "unchecked" })
private static Criterion addWhereRestriction(Restriction r) {
// do where (restrictions)
if(RestrictionType.Eq.equals(r.getType())) {
if(r.getValue() != null && !"-1".equals(r.getValue())) {
if((r.getProperty().equals("id") || r.getProperty().endsWith(".id") || r.getProperty()
.equals("resourceId")) && r.getValue() instanceof Integer)
return Restrictions.eq(r.getProperty(), ((Integer) r.getValue()).longValue());
else
return Restrictions.eq(r.getProperty(), r.getValue());
}
} else if(RestrictionType.Ne.equals(r.getType())) {
if(r.getValue() != null)
return Restrictions.ne(r.getProperty(), r.getValue());
} else if(RestrictionType.IsNull.equals(r.getType())) {
return Restrictions.isNull(r.getProperty());
} else if(RestrictionType.IsNotNull.equals(r.getType())) {
return Restrictions.isNotNull(r.getProperty());
} else if(RestrictionType.IsEmpty.equals(r.getType())) {
if("string".equalsIgnoreCase(r.getValue().toString())) {
return Restrictions.or(Restrictions.isNull(r.getProperty()),
Restrictions.eq(r.getProperty(), ""));
} else {
return Restrictions.isEmpty(r.getProperty());
}
} else if(RestrictionType.Le.equals(r.getType())) {
if(r.getValue() != null)
return Restrictions.le(r.getProperty(), r.getValue());
} else if(RestrictionType.Ge.equals(r.getType())) {
if(r.getValue() != null)
return Restrictions.ge(r.getProperty(), r.getValue());
} else if(RestrictionType.Lt.equals(r.getType())) {
if(r.getValue() != null)
return Restrictions.lt(r.getProperty(), r.getValue());
} else if(RestrictionType.Gt.equals(r.getType())) {
if(r.getValue() != null)
return Restrictions.gt(r.getProperty(), r.getValue());
} else if(RestrictionType.In.equals(r.getType())) {
if(r.getValue() == null)
return null;// if the value of the list is null
List valueList;
if(r.getValue() instanceof List) {
valueList = (List) r.getValue();
} else {
valueList = Arrays.asList((Object[]) r.getValue());
}
if(!valueList.isEmpty()) {
if(r.getProperty().endsWith(".id") || r.getProperty().equals("id")) {// special hack for flex graniteds, change
// ints to longs
final List nIds = new ArrayList();
for(final Number nId : (List) valueList) {
if(nId != null)
nIds.add(nId.longValue());
}
return Restrictions.in(r.getProperty(), nIds);
} else {
if(valueList.contains(null)) {
final List valueList2 = new ArrayList();
for(final Object obj : valueList) {
if(obj != null) {
valueList2.add(obj);
}
}
return Restrictions.or(Restrictions.isNull(r.getProperty()),
Restrictions.in(r.getProperty(), valueList2));
} else {
return Restrictions.in(r.getProperty(), valueList);
}
}
} else {
log.error("You are trying to constrain a query with an empty list of values, which will ALWAYS return nothing. Change your code to avoid server load.");
return Restrictions.isNull("id");// look, the user gave us an empty list of values, so return no results for
// this query.
}
}
else if(RestrictionType.Match_Wild.equals(r.getType())) {
final String valueString = (String) r.getValue();
if(checkString(valueString) != null) {
if(valueString.contains("*")) {
final String newValue = valueString.replaceAll("\\*", "%");
if(log.isDebugEnabled())
log.debug("New value: " + newValue);
return Restrictions.sqlRestriction(r.getProperty() + " like '" + newValue + "'");
} else {
if(valueString.length() <= 2) {
return Restrictions.like(r.getProperty(), valueString, MatchMode.START);
} else {
return Restrictions.like(r.getProperty(), valueString, MatchMode.ANYWHERE);
}
}
}
} else if(RestrictionType.Match_Any.equals(r.getType())) {
if(checkString((String) r.getValue()) != null)
return Restrictions.like(r.getProperty(), (String) r.getValue(), MatchMode.ANYWHERE);
} else if(RestrictionType.Match_Exact.equals(r.getType())) {
if(checkString((String) r.getValue()) != null)
return Restrictions.like(r.getProperty(), (String) r.getValue(), MatchMode.EXACT);
} else if(RestrictionType.Match_Start.equals(r.getType())) {
if(checkString((String) r.getValue()) != null)
return Restrictions.like(r.getProperty(), (String) r.getValue(), MatchMode.START);
} else if(RestrictionType.Match_End.equals(r.getType())) {
if(checkString((String) r.getValue()) != null)
return Restrictions.like(r.getProperty(), (String) r.getValue(), MatchMode.END);
} else if(RestrictionType.Conjunction.equals(r.getType())) {
final Conjunction cj = Restrictions.conjunction();
for(final Restriction rc : ((NestedRestriction) r).getNestedRestrictions()) {
cj.add(addWhereRestriction(rc));
}
return cj;
} else if(RestrictionType.Disjunction.equals(r.getType())) {
final Disjunction dj = Restrictions.disjunction();
for(final Restriction rc : ((NestedRestriction) r).getNestedRestrictions()) {
dj.add(addWhereRestriction(rc));
}
return dj;
} else {
throw new RuntimeException("Wait! I don't know what this restriction type is... ["
+ r.getType() + "]");
}
return null;
}
private static String checkString(String value) {
return (value != null && !"".equals(value)) ? value : null;
}
}
In Groovy;
TODO
In Actionscript;
References;
http://martinfowler.com/eaaCatalog/queryObject.html
http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html
http://grails.org/doc/latest/guide/5.%20Object%20Relational%20Mapping%20(GORM).html#5.4.2 Criteria
0 comments:
Post a Comment