Coverage Report

 /*
  * Copyright 2007-2008 The Kuali Foundation
  *
  * Licensed under the Educational Community 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.opensource.org/licenses/ecl2.php
  *
  * 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.
  */
 package org.kuali.rice.core.framework.persistence.jdbc.sql;
 
 import org.kuali.rice.core.api.CoreConstants;
 import org.kuali.rice.core.api.datetime.DateTimeService;
 import org.kuali.rice.core.api.resourceloader.GlobalResourceLoader;
 import org.kuali.rice.core.api.util.RiceConstants;
 import org.kuali.rice.core.api.util.type.TypeUtils;
 import org.kuali.rice.core.framework.persistence.jpa.criteria.QueryByCriteria.QueryByCriteriaType;
 import org.kuali.rice.core.framework.persistence.platform.DatabasePlatform;
 import org.kuali.rice.core.web.format.BooleanFormatter;
 
 import javax.persistence.Query;
 import java.math.BigDecimal;
 import java.sql.Timestamp;
 import java.text.ParseException;
 import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.HashMap;
 import java.util.Iterator;
 import java.util.LinkedHashMap;
 import java.util.List;
 import java.util.Map;
 
 
 
 /**
  * A criteria builder for JDBC Query strings.
  *
  * TODO: Rewrite this class with a better criteria building algorithm.
  *
  * @author Kuali Rice Team (rice.collab@kuali.org)
  */
 @SuppressWarnings("unchecked")
 public class Criteria {
         private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(Criteria.class);
 
         private Integer searchLimit;
 
         private String entityName;
 
         private String alias;
 
         private boolean distinct = false;
         
         private transient DateTimeService dateTimeService;
 
         protected List tokens = new ArrayList();
 
         private List orderByTokens = new ArrayList();
 
         protected Map<String, Object> params = new LinkedHashMap<String, Object>();
 
         DatabasePlatform dbPlatform = null;
 
         public Criteria(String entityName) {
                 this(entityName, "a");
         }
 
         public DatabasePlatform getDbPlatform() {
             if (dbPlatform == null) {
                     dbPlatform = (DatabasePlatform) GlobalResourceLoader.getService(RiceConstants.DB_PLATFORM);
             }
             return dbPlatform;
     }
 
         public void setDbPlatform(DatabasePlatform dbPlatform){
                 this.dbPlatform = dbPlatform;
         }
 
         public Criteria(String entityName, String alias) {
                 this.entityName = entityName;
                 this.alias = alias;
         }
 
         public void between(String attribute, Object value1, Object value2, Class propertyType) {
 
                 String fixedValue1 = this.fixValue(value1, propertyType);
                 String fixedValue2= this.fixValue(value2, propertyType);
                 if (attribute.contains("__JPA_ALIAS__")) {
                         tokens.add(" (" + fix(attribute) + " BETWEEN " + fixedValue1 + " AND " + fixedValue2 + ") ");
                 } else {
                         tokens.add(" (" + alias + "." + attribute + " BETWEEN " + fixedValue1 + " AND " + fixedValue2 + ") ");
                 }
 
         }
 
         private String fixValue(Object value, Class propertyType){
                 
                 if (value == null) {
                         return "";
                 }
 
                 if(SqlBuilder.isJoinClass(propertyType)){
                         return value.toString();
                 }
 
                 if(TypeUtils.isIntegralClass(propertyType) || TypeUtils.isDecimalClass(propertyType)){
                         new BigDecimal(value.toString()); // This should throw an exception if the number is invalid.
                         return value.toString();
                 }
                 if(TypeUtils.isTemporalClass(propertyType)){
                         try {
                                 if (value instanceof String) {
                                         value = getDateTimeService().convertToSqlTimestamp(value.toString());
                                 }
                                 return getFixedTemporalValue(value);
                         } catch (ParseException pe) {
                                 LOG.warn("Could not parse "+value.toString()+" as date");
                                 throw new RuntimeException("Could not parse "+value.toString()+" as date", pe);
                         }
                 }
                 if (TypeUtils.isStringClass(propertyType)) {
                         return " '" + getDbPlatform().escapeString(value.toString().trim()) + "' ";
                 }
                 if (TypeUtils.isBooleanClass(propertyType)) {
                         if (value instanceof String) {
                                 value = new BooleanFormatter().convertFromPresentationFormat(value.toString());
                         }
                         boolean bVal = ((Boolean)value).booleanValue();
                         if(bVal){return "1";}
                         else { return "0";}
                 }
 
                 return value.toString();
         }
         
         /**
          * Prepares a temporally classed value for inclusion in criteria
          * @param value the Timestamp value to convert
          * @return the fixed SQL version of that value
          */
         private String getFixedTemporalValue(Object value) {
                 Timestamp ts = (Timestamp)value;
                 java.sql.Date dt = new java.sql.Date(ts.getTime());
                 SimpleDateFormat sdfDate = new SimpleDateFormat("yyyy-MM-dd");
                 SimpleDateFormat sdfTime = new SimpleDateFormat("HH:mm:ss");
 
                 String sql = getDbPlatform().getDateSQL(sdfDate.format(dt),sdfTime.format(dt)) ;
                 return sql;
         }
 
 
         /**
          * This method ...
          *
          * @param string
          * @return
          */
 /*
         private String fixAttr(String attr) {
                 return "?";
                 //return fixAttr(attr,0);
         }
         private String fixAttr(String attr, int cnt) {
                 String sRet = attr.replace(".", "_");
 
                 if(params.containsKey(sRet)){
                         sRet = fixAttr(attr, ++cnt);
                 }
                 return sRet;
         }
 */
         public void eq(String attribute, Object value, Class propertyType) {
 
                 tokens.add(alias + "." + attribute + " = " + fixValue(value, propertyType) + " ");
 
         }
 
         public void gt(String attribute, Object value, Class propertyType) {
                 if (attribute.contains("__JPA_ALIAS__")) {
                         tokens.add(fix(attribute) + " > " + fixValue(value, propertyType) + " ");
                 } else {
                         tokens.add(alias + "." + attribute + " > " + fixValue(value, propertyType) + " ");
                 }
 
         }
 
         public void gte(String attribute, Object value, Class propertyType) {
                 if (attribute.contains("__JPA_ALIAS__")) {
                         tokens.add(fix(attribute) + " >= " + fixValue(value, propertyType) + " ");
                 } else {
                         tokens.add(alias + "." + attribute + " >= " + fixValue(value, propertyType) + " ");
                 }
         }
 
         public void like(String attribute, Object value, Class propertyType, boolean allowWildcards) {
                 String fixedValue = fixValue(value, propertyType);
 
                 if(allowWildcards){
                         fixedValue = fixWildcards(stripFunctions(fixedValue));
                 }
 
                 if (attribute.contains("__JPA_ALIAS__")) {
                         tokens.add(fix(attribute) + " LIKE " +  fixedValue + " ");
                 } else {
                         tokens.add(alias + "." + attribute + " LIKE " + fixedValue + " ");
                 }
         }
 
         public void notLike(String attribute, Object value, Class propertyType, boolean allowWildcards) {
                 String fixedValue = fixValue(value, propertyType);
 
                 if(allowWildcards){
                         fixedValue = fixWildcards(stripFunctions(fixedValue));
                 }
 
                 if (attribute.contains("__JPA_ALIAS__")) {
                         tokens.add(fix(attribute) + " NOT LIKE " + fixedValue + " ");
                 } else {
                         tokens.add(alias + "." + attribute + " NOT LIKE " + fixedValue + " ");
                 }
                 //tokens.add(alias + "." + attribute + " NOT LIKE " + stripFunctions(fixedValue).replaceAll("\\*", "%") + " ");
         }
 
         private static String fixWildcards(String sIn){
                 String sRet = sIn.replaceAll("\\*", "%");
                 return sRet.replaceAll("\\?", "_");
         }
 
         public void lt(String attribute, Object value, Class propertyType) {
                 if (attribute.contains("__JPA_ALIAS__")) {
                         tokens.add(fix(attribute) + " < " + fixValue(value, propertyType) + " ");
                 } else {
                         tokens.add(alias + "." + attribute + " < " + fixValue(value, propertyType) + " ");
                 }
         }
 
         public void lte(String attribute, Object value, Class propertyType) {
                 if (attribute.contains("__JPA_ALIAS__")) {
                         tokens.add(fix(attribute) + " <= " + fixValue(value, propertyType) + " ");
                 } else {
                         tokens.add(alias + "." + attribute + " <= " + fixValue(value, propertyType) + " ");
                 }
         }
 
         public void ne(String attribute, Object value, Class propertyType) {
                 tokens.add(alias + "." + attribute + " != " + fixValue(value, propertyType) + " ");
         }
 
         public void isNull(String attribute) {
                 tokens.add(alias + "." + attribute + " IS NULL ");
         }
 
         public void rawJpql(String jpql) {
                 tokens.add(" " + jpql + " ");
         }
 
         public void in(String attribute, List values, Class propertyType) {
                 String in = "";
                 for (Object object : values) {
                         in += fixValue(object, propertyType) + ",";
                 }
                 if (!"".equals(in)) {
                         in = in.substring(0, in.length()-1);
                 }
                 tokens.add(alias + "." + attribute + " IN (" + in + ") ");
         }
 
         public void notIn(String attribute, List values, Class propertyType) {
                 String in = "";
                 for (Object object : values) {
                         in += fixValue(object, propertyType) + ",";
                 }
                 if (!"".equals(in)) {
                         in = in.substring(in.length()-1);
                 }
                 tokens.add(alias + "." + attribute + " NOT IN (" + in + ") ");
         }
 
         public void orderBy(String attribute, boolean sortAscending) {
                 String sort = (sortAscending ? "ASC" : "DESC");
                 orderByTokens.add(alias + "." + attribute + " " + sort + " ");
         }
 
         public void and(Criteria and) {
                 tokens.add(new AndCriteria(and));
         }
 
         public void or(Criteria or) {
                 tokens.add(new OrCriteria(or));
         }
 
         public void exists(Criteria exists) {
         tokens.add(new ExistsCriteria(exists));
     }
         
         public String toQuery(QueryByCriteriaType type) {
                 String queryType = type.toString();
                 if (type.equals(QueryByCriteriaType.SELECT)) {
                         if(distinct){
                                 queryType += " " + "DISTINCT";
                         }
 
                         queryType += " " + alias;
                 }
                 String queryString = queryType + " FROM " + entityName + " AS " + alias;
                 if (!tokens.isEmpty()) {
                         queryString += " WHERE " + buildWhere();
                 }
                 if (!orderByTokens.isEmpty()) {
                         queryString += " ORDER BY ";
                         int count = 0;
                         for (Iterator iterator = orderByTokens.iterator(); iterator.hasNext();) {
                                 Object token = (Object) iterator.next();
                                 if (count == 0) {
                                         count++;
                                 } else {
                                         queryString += ", ";
                                 }
                                 queryString += (String) token;
                         }
                 }
                 return fix(queryString);
         }
 
         public String toCountQuery() {
                 String queryString = "SELECT COUNT(*) FROM " + entityName + " AS " + alias;
                 if (!tokens.isEmpty()) {
                         queryString += " WHERE " + buildWhere();
                 }
                 return fix(queryString);
         }
 
         private String fix(String queryString) {
                 queryString = queryString.replaceAll("__JPA_ALIAS__", alias);
                 return queryString;
         }
 
         public String buildWhere() {
                 return fix(buildWhere(null));
         }
 
         private String buildWhere(Criteria parentCriteria) {
                 String queryString = "";
                 int i = 0;
                 for (Iterator iterator = tokens.iterator(); iterator.hasNext();) {
                         Object token = (Object) iterator.next();
                         if (token instanceof Criteria) {
                                 String logic = "";
                                 if (i>0 && token instanceof AndCriteria) {
                                         logic = " AND ";
                                 } else if (i>0 && token instanceof OrCriteria) {
                                         logic = " OR ";
                                 } else if (i>0 && token instanceof ExistsCriteria) {
                     logic = " EXISTS ";
                 }
                         queryString += logic + " (" + ((Criteria) token).buildWhere(((Criteria) token)) + ") ";
                         } else {
                                 if(i>0){
                                         queryString += " AND " + (String) token;
                                 }else{
                                         queryString += (String) token;
                                 }
                         }
                         i++;
                 }
                 return queryString;
         }
 
         // Keep this package access so the QueryByCriteria can call it from this package.
         void prepareParameters(Query query) {
                 prepareParameters(query, tokens, params);
         }
 
         public List<Object> getParameteres() {
                 return getParameteres(tokens, params);
         }
 
         public List<Object> getParameteres(List tokens, Map<String, Object> params) {
 
                 List<Object> mRet = new ArrayList<Object>();
 
                 for (Map.Entry<String, Object> param : params.entrySet()) {
                         Object value = param.getValue();
                         if (value instanceof BigDecimal) {
                                 value = new Long(((BigDecimal)value).longValue());
                         }
                         if (value instanceof String) {
                                 value = ((String)value).replaceAll("\\*", "%");
                         }
                         mRet.add(value);
                 }
                 for (Iterator iterator = tokens.iterator(); iterator.hasNext();) {
                         Object token = (Object) iterator.next();
                         if (token instanceof Criteria) {
                                 mRet.addAll(getParameteres(((Criteria)token).tokens, ((Criteria)token).params));
                         }
                 }
                 return mRet;
         }
 
         void prepareParameters(Query query, List tokens, Map<String, Object> params) {
                 for (Map.Entry<String, Object> param : params.entrySet()) {
                         Object value = param.getValue();
                         if (value instanceof BigDecimal) {
                                 value = new Long(((BigDecimal)value).longValue());
                         }
                         if (value instanceof String) {
                                 value = ((String)value).replaceAll("\\*", "%");
                         }
                         query.setParameter(param.getKey(), value);
                 }
                 for (Iterator iterator = tokens.iterator(); iterator.hasNext();) {
                         Object token = (Object) iterator.next();
                         if (token instanceof Criteria) {
                                 prepareParameters(query, ((Criteria)token).tokens, ((Criteria)token).params);
                         }
                 }
         }
 
         private class AndCriteria extends Criteria {
                 public AndCriteria(Criteria and) {
                         super(and.entityName, and.alias);
                         this.tokens = new ArrayList(and.tokens);
                         this.params = new HashMap(and.params);
                 }
         }
 
         private class OrCriteria extends Criteria {
                 public OrCriteria(Criteria or) {
                         super(or.entityName, or.alias);
                         this.tokens = new ArrayList(or.tokens);
                         this.params = new HashMap(or.params);
                 }
         }
         
         private class ExistsCriteria extends Criteria {
         public ExistsCriteria(Criteria exists) {
             super(exists.entityName, exists.alias);
             this.tokens = new ArrayList(exists.tokens);
             this.params = new HashMap(exists.params);
         }       
     }
 
         public Integer getSearchLimit() {
                 return this.searchLimit;
         }
 
         public void setSearchLimit(Integer searchLimit) {
                 this.searchLimit = searchLimit;
         }
 
 
         public void notNull(String attribute) {
                 tokens.add(alias + "." + attribute + " IS NOT NULL ");
         }
 
         public void distinct(boolean distinct){
                 this.distinct = distinct;
         }
 
         /**
          * This method ...
          *
          * @param string
          * @param timestamp
          * @param timestamp2
          */
         public void notBetween(String attribute, Object value1,
                         Object value2, Class propertyType) {
                 String fixedValue1 = fixValue(value1, propertyType);
                 String fixedValue2 = fixValue(value1, propertyType);
                 if (attribute.contains("__JPA_ALIAS__")) {
                         tokens.add(" (" + fix(attribute) + " NOT BETWEEN " + fixedValue1 + " AND " + fixedValue2 + ") ");
                 } else {
                         tokens.add(" (" + alias + "." + attribute + " NOT BETWEEN " + fixedValue1 + " AND " + fixedValue2 + ") ");
                 }
 
         }
 
         /**
          * This method ...
          *
          * @param string
          * @param responsibilitySubQuery
          */
         public void in(String match, Criteria subQuery, String attribute, Class propertyType) {
                 if("a".equals(subQuery.alias)){
                         subQuery.alias="b";
                 }
                 String whereClause = "";
                 if(subQuery.tokens.isEmpty()){
                         whereClause = "WHERE ";
                 }else{
                         whereClause = "AND ";
                 }
                 whereClause += subQuery.alias+"."+attribute + " = " + alias+"."+match;
 
                 tokens.add("EXISTS (" + subQuery.toQuery(QueryByCriteriaType.SELECT) + whereClause + " ) ");
 
         }
 
         private String stripFunctions(String attribute) {
             int index = attribute.lastIndexOf('(');
             if(index != -1) {
                 return attribute.substring(index+1, attribute.indexOf(')'));
             }
 
             return attribute;
         }
         public String getAlias(){
                 return this.alias;
         }
 
         public String establishDateString(String fromDate, String toDate, String columnDbName, String whereStatementClause) {
             DatabasePlatform platform = getDbPlatform();
             StringBuffer dateSqlString = new StringBuffer(whereStatementClause).append(" " + platform.escapeString(columnDbName) + " ");
         if (fromDate != null && SQLUtils.getSqlFormattedDate(fromDate) != null && toDate != null && SQLUtils.getSqlFormattedDate(toDate) != null) {
             return dateSqlString.append(" >= " + platform.getDateSQL(platform.escapeString(SQLUtils.getSqlFormattedDate(fromDate.trim())), null) + " and " + platform.escapeString(columnDbName) + " <= " + platform.getDateSQL(platform.escapeString(SQLUtils.getSqlFormattedDate(toDate.trim())), "23:59:59")).toString();
         } else {
             if (fromDate != null && SQLUtils.getSqlFormattedDate(fromDate) != null) {
                 return dateSqlString.append(" >= " + platform.getDateSQL(platform.escapeString(SQLUtils.getSqlFormattedDate(fromDate.trim())), null)).toString();
             } else if (toDate != null && SQLUtils.getSqlFormattedDate(toDate) != null) {
                 return dateSqlString.append(" <= " + platform.getDateSQL(platform.escapeString(SQLUtils.getSqlFormattedDate(toDate.trim())), "23:59:59")).toString();
             } else {
                 return "";
             }
         }
     }
         
         private DateTimeService getDateTimeService() {
             if (this.dateTimeService == null) {
                     this.dateTimeService = GlobalResourceLoader.getService(CoreConstants.Services.DATETIME_SERVICE);
             }
             return this.dateTimeService;
     }
 }

1		/*
2		* Copyright 2007-2008 The Kuali Foundation
3		*
4		* Licensed under the Educational Community License, Version 2.0 (the "License");
5		* you may not use this file except in compliance with the License.
6		* You may obtain a copy of the License at
7		*
8		* http://www.opensource.org/licenses/ecl2.php
9		*
10		* Unless required by applicable law or agreed to in writing, software
11		* distributed under the License is distributed on an "AS IS" BASIS,
12		* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13		* See the License for the specific language governing permissions and
14		* limitations under the License.
15		*/
16		package org.kuali.rice.core.framework.persistence.jdbc.sql;
17
18		import org.kuali.rice.core.api.CoreConstants;
19		import org.kuali.rice.core.api.datetime.DateTimeService;
20		import org.kuali.rice.core.api.resourceloader.GlobalResourceLoader;
21		import org.kuali.rice.core.api.util.RiceConstants;
22		import org.kuali.rice.core.api.util.type.TypeUtils;
23		import org.kuali.rice.core.framework.persistence.jpa.criteria.QueryByCriteria.QueryByCriteriaType;
24		import org.kuali.rice.core.framework.persistence.platform.DatabasePlatform;
25		import org.kuali.rice.core.web.format.BooleanFormatter;
26
27		import javax.persistence.Query;
28		import java.math.BigDecimal;
29		import java.sql.Timestamp;
30		import java.text.ParseException;
31		import java.text.SimpleDateFormat;
32		import java.util.ArrayList;
33		import java.util.HashMap;
34		import java.util.Iterator;
35		import java.util.LinkedHashMap;
36		import java.util.List;
37		import java.util.Map;
38
39
40
41		/**
42		* A criteria builder for JDBC Query strings.
43		*
44		* TODO: Rewrite this class with a better criteria building algorithm.
45		*
46		* @author Kuali Rice Team (rice.collab@kuali.org)
47		*/
48		@SuppressWarnings("unchecked")
49	0	public class Criteria {
50	0	private static org.apache.log4j.Logger LOG = org.apache.log4j.Logger.getLogger(Criteria.class);
51
52		private Integer searchLimit;
53
54		private String entityName;
55
56		private String alias;
57
58	0	private boolean distinct = false;
59
60		private transient DateTimeService dateTimeService;
61
62	0	protected List tokens = new ArrayList();
63
64	0	private List orderByTokens = new ArrayList();
65
66	0	protected Map<String, Object> params = new LinkedHashMap<String, Object>();
67
68	0	DatabasePlatform dbPlatform = null;
69
70		public Criteria(String entityName) {
71	0	this(entityName, "a");
72	0	}
73
74		public DatabasePlatform getDbPlatform() {
75	0	if (dbPlatform == null) {
76	0	dbPlatform = (DatabasePlatform) GlobalResourceLoader.getService(RiceConstants.DB_PLATFORM);
77		}
78	0	return dbPlatform;
79		}
80
81		public void setDbPlatform(DatabasePlatform dbPlatform){
82	0	this.dbPlatform = dbPlatform;
83	0	}
84
85	0	public Criteria(String entityName, String alias) {
86	0	this.entityName = entityName;
87	0	this.alias = alias;
88	0	}
89
90		public void between(String attribute, Object value1, Object value2, Class propertyType) {
91
92	0	String fixedValue1 = this.fixValue(value1, propertyType);
93	0	String fixedValue2= this.fixValue(value2, propertyType);
94	0	if (attribute.contains("__JPA_ALIAS__")) {
95	0	tokens.add(" (" + fix(attribute) + " BETWEEN " + fixedValue1 + " AND " + fixedValue2 + ") ");
96		} else {
97	0	tokens.add(" (" + alias + "." + attribute + " BETWEEN " + fixedValue1 + " AND " + fixedValue2 + ") ");
98		}
99
100	0	}
101
102		private String fixValue(Object value, Class propertyType){
103
104	0	if (value == null) {
105	0	return "";
106		}
107
108	0	if(SqlBuilder.isJoinClass(propertyType)){
109	0	return value.toString();
110		}
111
112	0	if(TypeUtils.isIntegralClass(propertyType) \|\| TypeUtils.isDecimalClass(propertyType)){
113	0	new BigDecimal(value.toString()); // This should throw an exception if the number is invalid.
114	0	return value.toString();
115		}
116	0	if(TypeUtils.isTemporalClass(propertyType)){
117		try {
118	0	if (value instanceof String) {
119	0	value = getDateTimeService().convertToSqlTimestamp(value.toString());
120		}
121	0	return getFixedTemporalValue(value);
122	0	} catch (ParseException pe) {
123	0	LOG.warn("Could not parse "+value.toString()+" as date");
124	0	throw new RuntimeException("Could not parse "+value.toString()+" as date", pe);
125		}
126		}
127	0	if (TypeUtils.isStringClass(propertyType)) {
128	0	return " '" + getDbPlatform().escapeString(value.toString().trim()) + "' ";
129		}
130	0	if (TypeUtils.isBooleanClass(propertyType)) {
131	0	if (value instanceof String) {
132	0	value = new BooleanFormatter().convertFromPresentationFormat(value.toString());
133		}
134	0	boolean bVal = ((Boolean)value).booleanValue();
135	0	if(bVal){return "1";}
136	0	else { return "0";}
137		}
138
139	0	return value.toString();
140		}
141
142		/**
143		* Prepares a temporally classed value for inclusion in criteria
144		* @param value the Timestamp value to convert
145		* @return the fixed SQL version of that value
146		*/
147		private String getFixedTemporalValue(Object value) {
148	0	Timestamp ts = (Timestamp)value;
149	0	java.sql.Date dt = new java.sql.Date(ts.getTime());
150	0	SimpleDateFormat sdfDate = new SimpleDateFormat("yyyy-MM-dd");
151	0	SimpleDateFormat sdfTime = new SimpleDateFormat("HH:mm:ss");
152
153	0	String sql = getDbPlatform().getDateSQL(sdfDate.format(dt),sdfTime.format(dt)) ;
154	0	return sql;
155		}
156
157
158		/**
159		* This method ...
160		*
161		* @param string
162		* @return
163		*/
164		/*
165		private String fixAttr(String attr) {
166		return "?";
167		//return fixAttr(attr,0);
168		}
169		private String fixAttr(String attr, int cnt) {
170		String sRet = attr.replace(".", "_");
171
172		if(params.containsKey(sRet)){
173		sRet = fixAttr(attr, ++cnt);
174		}
175		return sRet;
176		}
177		*/
178		public void eq(String attribute, Object value, Class propertyType) {
179
180	0	tokens.add(alias + "." + attribute + " = " + fixValue(value, propertyType) + " ");
181
182	0	}
183
184		public void gt(String attribute, Object value, Class propertyType) {
185	0	if (attribute.contains("__JPA_ALIAS__")) {
186	0	tokens.add(fix(attribute) + " > " + fixValue(value, propertyType) + " ");
187		} else {
188	0	tokens.add(alias + "." + attribute + " > " + fixValue(value, propertyType) + " ");
189		}
190
191	0	}
192
193		public void gte(String attribute, Object value, Class propertyType) {
194	0	if (attribute.contains("__JPA_ALIAS__")) {
195	0	tokens.add(fix(attribute) + " >= " + fixValue(value, propertyType) + " ");
196		} else {
197	0	tokens.add(alias + "." + attribute + " >= " + fixValue(value, propertyType) + " ");
198		}
199	0	}
200
201		public void like(String attribute, Object value, Class propertyType, boolean allowWildcards) {
202	0	String fixedValue = fixValue(value, propertyType);
203
204	0	if(allowWildcards){
205	0	fixedValue = fixWildcards(stripFunctions(fixedValue));
206		}
207
208	0	if (attribute.contains("__JPA_ALIAS__")) {
209	0	tokens.add(fix(attribute) + " LIKE " + fixedValue + " ");
210		} else {
211	0	tokens.add(alias + "." + attribute + " LIKE " + fixedValue + " ");
212		}
213	0	}
214
215		public void notLike(String attribute, Object value, Class propertyType, boolean allowWildcards) {
216	0	String fixedValue = fixValue(value, propertyType);
217
218	0	if(allowWildcards){
219	0	fixedValue = fixWildcards(stripFunctions(fixedValue));
220		}
221
222	0	if (attribute.contains("__JPA_ALIAS__")) {
223	0	tokens.add(fix(attribute) + " NOT LIKE " + fixedValue + " ");
224		} else {
225	0	tokens.add(alias + "." + attribute + " NOT LIKE " + fixedValue + " ");
226		}
227		//tokens.add(alias + "." + attribute + " NOT LIKE " + stripFunctions(fixedValue).replaceAll("\\*", "%") + " ");
228	0	}
229
230		private static String fixWildcards(String sIn){
231	0	String sRet = sIn.replaceAll("\\*", "%");
232	0	return sRet.replaceAll("\\?", "_");
233		}
234
235		public void lt(String attribute, Object value, Class propertyType) {
236	0	if (attribute.contains("__JPA_ALIAS__")) {
237	0	tokens.add(fix(attribute) + " < " + fixValue(value, propertyType) + " ");
238		} else {
239	0	tokens.add(alias + "." + attribute + " < " + fixValue(value, propertyType) + " ");
240		}
241	0	}
242
243		public void lte(String attribute, Object value, Class propertyType) {
244	0	if (attribute.contains("__JPA_ALIAS__")) {
245	0	tokens.add(fix(attribute) + " <= " + fixValue(value, propertyType) + " ");
246		} else {
247	0	tokens.add(alias + "." + attribute + " <= " + fixValue(value, propertyType) + " ");
248		}
249	0	}
250
251		public void ne(String attribute, Object value, Class propertyType) {
252	0	tokens.add(alias + "." + attribute + " != " + fixValue(value, propertyType) + " ");
253	0	}
254
255		public void isNull(String attribute) {
256	0	tokens.add(alias + "." + attribute + " IS NULL ");
257	0	}
258
259		public void rawJpql(String jpql) {
260	0	tokens.add(" " + jpql + " ");
261	0	}
262
263		public void in(String attribute, List values, Class propertyType) {
264	0	String in = "";
265	0	for (Object object : values) {
266	0	in += fixValue(object, propertyType) + ",";
267		}
268	0	if (!"".equals(in)) {
269	0	in = in.substring(0, in.length()-1);
270		}
271	0	tokens.add(alias + "." + attribute + " IN (" + in + ") ");
272	0	}
273
274		public void notIn(String attribute, List values, Class propertyType) {
275	0	String in = "";
276	0	for (Object object : values) {
277	0	in += fixValue(object, propertyType) + ",";
278		}
279	0	if (!"".equals(in)) {
280	0	in = in.substring(in.length()-1);
281		}
282	0	tokens.add(alias + "." + attribute + " NOT IN (" + in + ") ");
283	0	}
284
285		public void orderBy(String attribute, boolean sortAscending) {
286	0	String sort = (sortAscending ? "ASC" : "DESC");
287	0	orderByTokens.add(alias + "." + attribute + " " + sort + " ");
288	0	}
289
290		public void and(Criteria and) {
291	0	tokens.add(new AndCriteria(and));
292	0	}
293
294		public void or(Criteria or) {
295	0	tokens.add(new OrCriteria(or));
296	0	}
297
298		public void exists(Criteria exists) {
299	0	tokens.add(new ExistsCriteria(exists));
300	0	}
301
302		public String toQuery(QueryByCriteriaType type) {
303	0	String queryType = type.toString();
304	0	if (type.equals(QueryByCriteriaType.SELECT)) {
305	0	if(distinct){
306	0	queryType += " " + "DISTINCT";
307		}
308
309	0	queryType += " " + alias;
310		}
311	0	String queryString = queryType + " FROM " + entityName + " AS " + alias;
312	0	if (!tokens.isEmpty()) {
313	0	queryString += " WHERE " + buildWhere();
314		}
315	0	if (!orderByTokens.isEmpty()) {
316	0	queryString += " ORDER BY ";
317	0	int count = 0;
318	0	for (Iterator iterator = orderByTokens.iterator(); iterator.hasNext();) {
319	0	Object token = (Object) iterator.next();
320	0	if (count == 0) {
321	0	count++;
322		} else {
323	0	queryString += ", ";
324		}
325	0	queryString += (String) token;
326	0	}
327		}
328	0	return fix(queryString);
329		}
330
331		public String toCountQuery() {
332	0	String queryString = "SELECT COUNT(*) FROM " + entityName + " AS " + alias;
333	0	if (!tokens.isEmpty()) {
334	0	queryString += " WHERE " + buildWhere();
335		}
336	0	return fix(queryString);
337		}
338
339		private String fix(String queryString) {
340	0	queryString = queryString.replaceAll("__JPA_ALIAS__", alias);
341	0	return queryString;
342		}
343
344		public String buildWhere() {
345	0	return fix(buildWhere(null));
346		}
347
348		private String buildWhere(Criteria parentCriteria) {
349	0	String queryString = "";
350	0	int i = 0;
351	0	for (Iterator iterator = tokens.iterator(); iterator.hasNext();) {
352	0	Object token = (Object) iterator.next();
353	0	if (token instanceof Criteria) {
354	0	String logic = "";
355	0	if (i>0 && token instanceof AndCriteria) {
356	0	logic = " AND ";
357	0	} else if (i>0 && token instanceof OrCriteria) {
358	0	logic = " OR ";
359	0	} else if (i>0 && token instanceof ExistsCriteria) {
360	0	logic = " EXISTS ";
361		}
362	0	queryString += logic + " (" + ((Criteria) token).buildWhere(((Criteria) token)) + ") ";
363	0	} else {
364	0	if(i>0){
365	0	queryString += " AND " + (String) token;
366		}else{
367	0	queryString += (String) token;
368		}
369		}
370	0	i++;
371	0	}
372	0	return queryString;
373		}
374
375		// Keep this package access so the QueryByCriteria can call it from this package.
376		void prepareParameters(Query query) {
377	0	prepareParameters(query, tokens, params);
378	0	}
379
380		public List<Object> getParameteres() {
381	0	return getParameteres(tokens, params);
382		}
383
384		public List<Object> getParameteres(List tokens, Map<String, Object> params) {
385
386	0	List<Object> mRet = new ArrayList<Object>();
387
388	0	for (Map.Entry<String, Object> param : params.entrySet()) {
389	0	Object value = param.getValue();
390	0	if (value instanceof BigDecimal) {
391	0	value = new Long(((BigDecimal)value).longValue());
392		}
393	0	if (value instanceof String) {
394	0	value = ((String)value).replaceAll("\\*", "%");
395		}
396	0	mRet.add(value);
397	0	}
398	0	for (Iterator iterator = tokens.iterator(); iterator.hasNext();) {
399	0	Object token = (Object) iterator.next();
400	0	if (token instanceof Criteria) {
401	0	mRet.addAll(getParameteres(((Criteria)token).tokens, ((Criteria)token).params));
402		}
403	0	}
404	0	return mRet;
405		}
406
407		void prepareParameters(Query query, List tokens, Map<String, Object> params) {
408	0	for (Map.Entry<String, Object> param : params.entrySet()) {
409	0	Object value = param.getValue();
410	0	if (value instanceof BigDecimal) {
411	0	value = new Long(((BigDecimal)value).longValue());
412		}
413	0	if (value instanceof String) {
414	0	value = ((String)value).replaceAll("\\*", "%");
415		}
416	0	query.setParameter(param.getKey(), value);
417	0	}
418	0	for (Iterator iterator = tokens.iterator(); iterator.hasNext();) {
419	0	Object token = (Object) iterator.next();
420	0	if (token instanceof Criteria) {
421	0	prepareParameters(query, ((Criteria)token).tokens, ((Criteria)token).params);
422		}
423	0	}
424	0	}
425
426		private class AndCriteria extends Criteria {
427	0	public AndCriteria(Criteria and) {
428	0	super(and.entityName, and.alias);
429	0	this.tokens = new ArrayList(and.tokens);
430	0	this.params = new HashMap(and.params);
431	0	}
432		}
433
434		private class OrCriteria extends Criteria {
435	0	public OrCriteria(Criteria or) {
436	0	super(or.entityName, or.alias);
437	0	this.tokens = new ArrayList(or.tokens);
438	0	this.params = new HashMap(or.params);
439	0	}
440		}
441
442		private class ExistsCriteria extends Criteria {
443	0	public ExistsCriteria(Criteria exists) {
444	0	super(exists.entityName, exists.alias);
445	0	this.tokens = new ArrayList(exists.tokens);
446	0	this.params = new HashMap(exists.params);
447	0	}
448		}
449
450		public Integer getSearchLimit() {
451	0	return this.searchLimit;
452		}
453
454		public void setSearchLimit(Integer searchLimit) {
455	0	this.searchLimit = searchLimit;
456	0	}
457
458
459		public void notNull(String attribute) {
460	0	tokens.add(alias + "." + attribute + " IS NOT NULL ");
461	0	}
462
463		public void distinct(boolean distinct){
464	0	this.distinct = distinct;
465	0	}
466
467		/**
468		* This method ...
469		*
470		* @param string
471		* @param timestamp
472		* @param timestamp2
473		*/
474		public void notBetween(String attribute, Object value1,
475		Object value2, Class propertyType) {
476	0	String fixedValue1 = fixValue(value1, propertyType);
477	0	String fixedValue2 = fixValue(value1, propertyType);
478	0	if (attribute.contains("__JPA_ALIAS__")) {
479	0	tokens.add(" (" + fix(attribute) + " NOT BETWEEN " + fixedValue1 + " AND " + fixedValue2 + ") ");
480		} else {
481	0	tokens.add(" (" + alias + "." + attribute + " NOT BETWEEN " + fixedValue1 + " AND " + fixedValue2 + ") ");
482		}
483
484	0	}
485
486		/**
487		* This method ...
488		*
489		* @param string
490		* @param responsibilitySubQuery
491		*/
492		public void in(String match, Criteria subQuery, String attribute, Class propertyType) {
493	0	if("a".equals(subQuery.alias)){
494	0	subQuery.alias="b";
495		}
496	0	String whereClause = "";
497	0	if(subQuery.tokens.isEmpty()){
498	0	whereClause = "WHERE ";
499		}else{
500	0	whereClause = "AND ";
501		}
502	0	whereClause += subQuery.alias+"."+attribute + " = " + alias+"."+match;
503
504	0	tokens.add("EXISTS (" + subQuery.toQuery(QueryByCriteriaType.SELECT) + whereClause + " ) ");
505
506	0	}
507
508		private String stripFunctions(String attribute) {
509	0	int index = attribute.lastIndexOf('(');
510	0	if(index != -1) {
511	0	return attribute.substring(index+1, attribute.indexOf(')'));
512		}
513
514	0	return attribute;
515		}
516		public String getAlias(){
517	0	return this.alias;
518		}
519
520		public String establishDateString(String fromDate, String toDate, String columnDbName, String whereStatementClause) {
521	0	DatabasePlatform platform = getDbPlatform();
522	0	StringBuffer dateSqlString = new StringBuffer(whereStatementClause).append(" " + platform.escapeString(columnDbName) + " ");
523	0	if (fromDate != null && SQLUtils.getSqlFormattedDate(fromDate) != null && toDate != null && SQLUtils.getSqlFormattedDate(toDate) != null) {
524	0	return dateSqlString.append(" >= " + platform.getDateSQL(platform.escapeString(SQLUtils.getSqlFormattedDate(fromDate.trim())), null) + " and " + platform.escapeString(columnDbName) + " <= " + platform.getDateSQL(platform.escapeString(SQLUtils.getSqlFormattedDate(toDate.trim())), "23:59:59")).toString();
525		} else {
526	0	if (fromDate != null && SQLUtils.getSqlFormattedDate(fromDate) != null) {
527	0	return dateSqlString.append(" >= " + platform.getDateSQL(platform.escapeString(SQLUtils.getSqlFormattedDate(fromDate.trim())), null)).toString();
528	0	} else if (toDate != null && SQLUtils.getSqlFormattedDate(toDate) != null) {
529	0	return dateSqlString.append(" <= " + platform.getDateSQL(platform.escapeString(SQLUtils.getSqlFormattedDate(toDate.trim())), "23:59:59")).toString();
530		} else {
531	0	return "";
532		}
533		}
534		}
535
536		private DateTimeService getDateTimeService() {
537	0	if (this.dateTimeService == null) {
538	0	this.dateTimeService = GlobalResourceLoader.getService(CoreConstants.Services.DATETIME_SERVICE);
539		}
540	0	return this.dateTimeService;
541		}
542		}