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