001: package org.julp.misc.search;
002:
003: import java.util.*;
004: import org.julp.*;
005: import java.lang.reflect.Method;
006:
007: public abstract class SearchCriteriaBuilder {
008:
009: /**
010: * This object is not indended to be a *real* Query engine.
011: * It is indended to be used with GUI to allow end users uppend WHERE clause to SELECT statement.
012: * How it works?
013: * <ul>
014: * <li>User would add Criteria</li>
015: * <li>Criteria would have
016: * <ol>
017: * <li>List of fileds to search. User must select one of the fields</li>
018: * <li>List of operators for each field (fieldOperators). You can specify which operators can be used with selected field. For example you can remove "LIKE" operator for numeric field</li>
019: * <li>Some fields can have list of values to select from (fieldValues). Example: field "State" can have a drop-down control (combobox) with list of states</li>
020: * <li>Boolean condition AND/OR. User must select one of them if there will be another Criteria</li>
021: * </ol>
022: * </li>
023: * <li>Use <code>beforeBuildCriteria()</code> in descendants to modify/validate user input</li>
024: * <li>After user done <code>buildCriteria()</code> would generate WHERE clause, which would be added to pre-defined SELECT statement</li>
025: * <li>To make it user-friendly fields, operators, etc. should have "display values" and "real values"
026: * Example:
027: * <code>
028: * fields.add(new SomeObject("firstName", "First Name"));
029: * fields.add(new SomeObject("lastName", "Last Name"));
030: * fields.add(new SomeObject("state", "State"));<br>
031: * ...
032: * </code>
033: * </li>
034: * </ul>
035: */
036:
037: public SearchCriteriaBuilder() {
038: }
039:
040: protected List fields;
041: protected MetaData metaData;
042: protected List operatorsList;
043: protected Map fieldOperators;
044: protected Map fieldValues;
045: protected List searchCriteriaHolders = new ArrayList();
046: protected List arguments = new ArrayList();
047: protected String select = "";
048: protected String from = "";
049: protected String joins = "";
050: protected String where = "";
051: protected String groupBy = "";
052: protected String having = "";
053: protected String orderBy = "";
054: protected String query = "";
055: protected String dynamicWhere = "";
056: protected String executable;
057: protected Map likeHint = new HashMap(3);
058: protected static final String SPACE = " ";
059: protected Set adhocColumns = new HashSet();
060:
061: /* Override this method to add/modify arguments, etc */
062: public void beforeBuildCriteria() {
063: }
064:
065: public void reset() {
066: //if (fields != null) fields.clear();
067: //metaData = null;
068: //if (operatorsList != null) operatorsList.clear();
069: //if (fieldOperators != null) fieldOperators.clear();
070: //if (fieldValues != null) fieldValues.clear();
071: if (searchCriteriaHolders != null)
072: searchCriteriaHolders.clear();
073: if (arguments != null)
074: arguments.clear();
075: if (adhocColumns != null)
076: adhocColumns.clear();
077: //if (likeHint != null) likeHint.clear();
078: select = "";
079: from = "";
080: joins = "";
081: where = "";
082: groupBy = "";
083: having = "";
084: orderBy = "";
085: query = "";
086: dynamicWhere = "";
087: }
088:
089: public String buildCriteria() {
090: beforeBuildCriteria();
091: StringBuffer sb = new StringBuffer();
092: int size = searchCriteriaHolders.size();
093: Iterator it = searchCriteriaHolders.iterator();
094: int count = 0;
095: while (it.hasNext()) {
096: count++;
097: SearchCriteriaHolder holder = (SearchCriteriaHolder) it
098: .next();
099: String fieldName = holder.getFieldName();
100: String functions = holder.getFunctions();
101: if (fieldName == null || fieldName.trim().equals("")) {
102: throw new IllegalArgumentException(
103: "Search: missing field name");
104: }
105: String operator = holder.getOperator();
106: Object searchValue = holder.getSearchValue();
107: String booleanCondition = holder.getBooleanCondition();
108: String columnName;
109: try {
110: columnName = metaData.getFullColumnName(metaData
111: .getColumnIndexByFieldName(fieldName));
112: holder.setFieldName(columnName);
113: } catch (java.sql.SQLException sqle) {
114: throw new RuntimeException(sqle);
115: }
116: if (functions != null) {
117: sb.append(replace(functions, columnName));
118: } else {
119: sb.append(columnName);
120: }
121: if (searchValue == null
122: || searchValue.toString().trim().equals("")) {
123: if (operator.equals("=")) {
124: sb.append(SPACE).append("IS NULL").append(SPACE);
125: } else if (operator.equals("<>")
126: || operator.equals("!=")) {
127: sb.append(SPACE).append("IS NOT NULL")
128: .append(SPACE);
129: } else {
130: throw new IllegalArgumentException(
131: "Invalid operator for empty search value");
132: }
133: } else {
134: sb.append(SPACE).append(operator).append(SPACE);
135: sb.append("?");
136: this .arguments.add(searchValue);
137: }
138: if (count < size) {
139: sb.append(SPACE).append(booleanCondition).append("\n")
140: .append(SPACE);
141: } else {
142: if (booleanCondition
143: .equals(SearchCriteriaHolder.OR_NESTED_LOGIC)
144: || booleanCondition
145: .equals(SearchCriteriaHolder.AND_NESTED_LOGIC)
146: || booleanCondition
147: .equals(SearchCriteriaHolder.AND_NESTED_LOGIC_END)
148: || booleanCondition
149: .equals(SearchCriteriaHolder.OR_NESTED_LOGIC_END)
150: || booleanCondition
151: .equals(SearchCriteriaHolder.NESTED_LOGIC_END)) {
152: sb.append(SPACE).append(") ");
153: } else {
154: //sb.append(SPACE).append(booleanCondition);
155: }
156: }
157: }
158: if (searchCriteriaHolders.size() != 0) {
159: // int len = sb.length();
160: // int n = 1;
161: // char c = sb.charAt(len - n);
162: // while (Character.isWhitespace(c)){
163: // n++;
164: // c = sb.charAt(len - n);
165: // }
166: // if (sb.charAt(len - n) == '('){
167: // sb.delete(len - (n + 4), len - 1);
168: // }else{
169: // sb.delete(len - (n + 3), len - 1);
170: // }
171: } else {
172: throw new RuntimeException("No search criteria");
173: }
174: setDynamicWhere(sb.toString());
175: return dynamicWhere;
176: }
177:
178: public String setSort(List sort) {
179: return setSort(sort, true, true);
180: }
181:
182: public String setSort(List sort, boolean findMapping) {
183: return setSort(sort, findMapping, true);
184: }
185:
186: /** Cnange original "ORDER BY" clause.
187: @findMapping == true means convert field name into column name
188: @replace == true replace "OREDER BY" vs. append.
189: */
190: public String setSort(List sort, boolean findMapping,
191: boolean replace) {
192: if (sort.size() == 0) {
193: return orderBy;
194: }
195: StringBuffer sb = new StringBuffer(" ORDER BY ");
196: if (!replace) {
197: if (orderBy != null && !orderBy.trim().equals("")) {
198: sb.append(orderBy).append(", ");
199: }
200: }
201: Iterator iter = sort.iterator();
202: while (iter.hasNext()) {
203: String[] fieldNameAndSortDir = (String[]) iter.next();
204: String columnName;
205: if (findMapping) {
206: try {
207: columnName = metaData
208: .getFullColumnName(metaData
209: .getColumnIndexByFieldName(fieldNameAndSortDir[0]));
210: } catch (java.sql.SQLException sqle) {
211: throw new RuntimeException(sqle);
212: }
213: } else {
214: columnName = fieldNameAndSortDir[0];
215: }
216: sb.append(columnName);
217: if (fieldNameAndSortDir[1] != null) {
218: sb.append(SPACE).append(fieldNameAndSortDir[1]);
219: }
220: sb.append(", ");
221: }
222: int len = sb.length();
223: sb.delete(len - 2, len - 1);
224: orderBy = sb.toString();
225: return orderBy;
226: }
227:
228: public void addSearch() {
229: this .searchCriteriaHolders.add(new SearchCriteriaHolder());
230: }
231:
232: public void removeSearch(int idx) {
233: try {
234: searchCriteriaHolders.remove(idx);
235: } catch (IndexOutOfBoundsException e) {
236: throw new IllegalArgumentException(e.getMessage());
237: }
238: }
239:
240: public int size() {
241: return searchCriteriaHolders.size();
242: }
243:
244: /** Getter for property fields.
245: * @return Value of property fields.
246: *
247: */
248: public java.util.List getFields() {
249: return fields;
250: }
251:
252: /** Setter for property fields.
253: * @param fields New value of property fields.
254: *
255: */
256: public void setFields(java.util.List fields) {
257: this .fields = fields;
258: }
259:
260: /** Getter for property operatorsList.
261: * @return Value of property operatorsList.
262: *
263: */
264: public java.util.List getOperatorsList() {
265: return operatorsList;
266: }
267:
268: /** Setter for property operatorsList.
269: * @param operatorsList New value of property operatorsList.
270: *
271: */
272: public void setOperatorsList(java.util.List operatorsList) {
273: this .operatorsList = operatorsList;
274: }
275:
276: /** Getter for property fieldOperators.
277: * @return Value of property fieldOperators.
278: *
279: */
280: public java.util.Map getFieldOperators() {
281: return fieldOperators;
282: }
283:
284: /** Setter for property fieldOperators.
285: * @param fieldOperators New value of property fieldOperators.
286: *
287: */
288: public void setFieldOperators(java.util.Map fieldOperators) {
289: this .fieldOperators = fieldOperators;
290: }
291:
292: /** Getter for property fieldValues.
293: * @return Value of property fieldValues.
294: *
295: */
296: public java.util.Map getFieldValues() {
297: return fieldValues;
298: }
299:
300: /** Setter for property fieldValues.
301: * @param fieldValues New value of property fieldValues.
302: *
303: */
304: public void setFieldValues(java.util.Map fieldValues) {
305: this .fieldValues = fieldValues;
306: }
307:
308: /** Getter for property searchCriteriaHolders.
309: * @return Value of property searchCriteriaHolders.
310: *
311: */
312: public java.util.List getSearchCriteriaHolders() {
313: return searchCriteriaHolders;
314: }
315:
316: /** Setter for property searchCriteriaHolders.
317: * @param searchCriteriaHolders New value of property searchCriteriaHolders.
318: *
319: */
320: public void setSearchCriteriaHolders(
321: java.util.List searchCriteriaHolders) {
322: this .searchCriteriaHolders = searchCriteriaHolders;
323: }
324:
325: /** Getter for property arguments.
326: * @return Value of property arguments.
327: *
328: */
329: public java.util.List getArguments() {
330: return arguments;
331: }
332:
333: /** Setter for property arguments.
334: * @param arguments New value of property arguments.
335: *
336: */
337: public void setArguments(java.util.List arguments) {
338: this .arguments = arguments;
339: }
340:
341: /** Getter for property metaData.
342: * @return Value of property metaData.
343: *
344: */
345: public org.julp.MetaData getMetaData() {
346: return metaData;
347: }
348:
349: /** Setter for property metaData.
350: * @param metaData New value of property metaData.
351: *
352: */
353: public void setMetaData(org.julp.MetaData metaData) {
354: this .metaData = metaData;
355: }
356:
357: /**
358: * Getter for property select.
359: * @return Value of property select.
360: */
361: public java.lang.String getSelect() {
362: return select;
363: }
364:
365: /**
366: * Setter for property select.
367: * @param select New value of property select.
368: */
369: public void setSelect(java.lang.String select) {
370: if (select != null) {
371: this .select = select.trim();
372: }
373: }
374:
375: /**
376: * Getter for property from.
377: * @return Value of property from.
378: */
379: public java.lang.String getFrom() {
380: return from;
381: }
382:
383: /**
384: * Setter for property from.
385: * @param from New value of property from.
386: */
387: public void setFrom(java.lang.String from) {
388: if (from != null) {
389: this .from = from.trim();
390: }
391: }
392:
393: /**
394: * Getter for property joins.
395: * @return Value of property joins.
396: */
397: public java.lang.String getJoins() {
398: return joins;
399: }
400:
401: /**
402: * Setter for property joins.
403: * @param joins New value of property joins.
404: */
405: public void setJoins(java.lang.String joins) {
406: if (joins != null) {
407: this .joins = joins.trim();
408: }
409: }
410:
411: /**
412: * Getter for property where.
413: * @return Value of property where.
414: */
415: public java.lang.String getWhere() {
416: return where;
417: }
418:
419: /**
420: * Setter for property where.
421: * @param where New value of property where.
422: */
423: public void setWhere(java.lang.String where) {
424: if (where != null) {
425: this .where = where.trim();
426: }
427: }
428:
429: /**
430: * Getter for property groupBy.
431: * @return Value of property groupBy.
432: */
433: public java.lang.String getGroupBy() {
434: return groupBy;
435: }
436:
437: /**
438: * Setter for property groupBy.
439: * @param groupBy New value of property groupBy.
440: */
441: public void setGroupBy(java.lang.String groupBy) {
442: if (groupBy != null) {
443: this .groupBy = groupBy.trim();
444: }
445: }
446:
447: /**
448: * Getter for property having.
449: * @return Value of property having.
450: */
451: public java.lang.String getHaving() {
452: return having;
453: }
454:
455: /**
456: * Setter for property having.
457: * @param having New value of property having.
458: */
459: public void setHaving(java.lang.String having) {
460: if (having != null) {
461: this .having = having.trim();
462: }
463: }
464:
465: /**
466: * Getter for property orderBy.
467: * @return Value of property orderBy.
468: */
469: public java.lang.String getOrderBy() {
470: return orderBy;
471: }
472:
473: /**
474: * Setter for property orderBy.
475: * @param orderBy New value of property orderBy.
476: */
477: public void setOrderBy(java.lang.String orderBy) {
478: if (orderBy != null) {
479: this .orderBy = orderBy.trim();
480: }
481: }
482:
483: /**
484: * Getter for property query.
485: * @return Value of property query.
486: */
487: public java.lang.String getQuery() {
488: StringBuffer sb = new StringBuffer();
489: if (select.trim().toUpperCase().startsWith("SELECT")) {
490: sb.append(select);
491: } else {
492: sb.append("SELECT ").append(select);
493: }
494: if (!from.trim().equals("")) {
495: if (!from.toUpperCase().startsWith("FROM")) {
496: sb.append(" \nFROM ").append(from.trim());
497: } else {
498: sb.append(SPACE).append(from.trim());
499: }
500: }
501: if (!joins.trim().equals("")) {
502: sb.append(SPACE).append(joins.trim());
503: }
504: if (!where.equals("")) {
505: if (!where.trim().toUpperCase().startsWith("WHERE")) {
506: sb.append(" \nWHERE ").append(where.trim());
507: } else {
508: sb.append(SPACE).append(where.trim());
509: }
510: }
511:
512: if (!dynamicWhere.trim().equals("")) {
513: if (where != null && !where.trim().equals("")) {
514: sb.append(" AND\n (").append(dynamicWhere.trim());
515: } else {
516: sb.append(" \nWHERE (").append(dynamicWhere.trim());
517: }
518: sb.append(")");
519: }
520:
521: if (!groupBy.equals("")) {
522: if (!groupBy.trim().toUpperCase().startsWith("GROUP ")) {
523: sb.append(" \nGROUP BY ").append(groupBy.trim());
524: } else {
525: sb.append(SPACE).append(groupBy.trim());
526: }
527: }
528: if (!having.equals("")) {
529: if (!having.trim().toUpperCase().startsWith("HAVING")) {
530: sb.append(" \nHAVING ").append(having.trim());
531: } else {
532: sb.append(SPACE).append(having.trim());
533: }
534: }
535: if (!orderBy.equals("")) {
536: if (!orderBy.trim().toUpperCase().startsWith("ORDER ")) {
537: sb.append(" \nORDER BY ").append(orderBy.trim());
538: } else {
539: sb.append(SPACE).append(orderBy.trim());
540: }
541: }
542: query = sb.toString();
543: //reset();
544: return query;
545: }
546:
547: /**
548: * Setter for property query.
549: * @param query New value of property query.
550: */
551: public void setQuery(java.lang.String query) {
552: this .query = query;
553: }
554:
555: /**
556: * Getter for property likeHint.
557: * @return Value of property likeHint.
558: */
559: public java.util.Map getLikeHint() {
560: return likeHint;
561: }
562:
563: public int getLikeHint(String operatorName) {
564: return ((Integer) likeHint.get(operatorName)).intValue();
565: }
566:
567: /**
568: * Setter for property likeHint.
569: * @param likeHint New value of property likeHint.
570: */
571: public void setLikeHint(String operatorName, int likeHint) {
572: this .likeHint.put(operatorName, new Integer(likeHint));
573: }
574:
575: public void setLikeHint(java.util.Map likeHint) {
576: this .likeHint = likeHint;
577: }
578:
579: /**
580: * Getter for property dynamicWhere.
581: * @return Value of property dynamicWhere.
582: */
583: public java.lang.String getDynamicWhere() {
584: return dynamicWhere;
585: }
586:
587: /**
588: * Setter for property dynamicWhere.
589: * @param dynamicWhere New value of property dynamicWhere.
590: */
591: public void setDynamicWhere(java.lang.String dynamicWhere) {
592: if (where != null) {
593: this .dynamicWhere = dynamicWhere.trim();
594: }
595: }
596:
597: protected String replace(String functions, String columnName) {
598: int start = functions.indexOf("${");
599: int end = functions.indexOf("}", start);
600: String result = functions.substring(0, start);
601: result = result + columnName + functions.substring(end + 1);
602: return result;
603: }
604:
605: public String getExecutable() {
606: return executable;
607: }
608:
609: public void setExecutable(String executable) {
610: this .executable = executable;
611: }
612:
613: public String toString() {
614: Object[] EMPTY_READ_ARG = new Object[0];
615: StringBuffer sb = new StringBuffer();
616: Object value = null;
617: Method[] methods = getClass().getMethods();
618: for (int i = 0; i < methods.length; i++) {
619: String methodName = methods[i].getName();
620: if (methodName.equals("") || methodName.equals("getClass")) {
621: continue;
622: }
623: if ((methodName.startsWith("get") || methodName
624: .startsWith("is"))
625: && methods[i].getParameterTypes().length == 0) {
626: try {
627: value = methods[i].invoke(this , EMPTY_READ_ARG);
628: } catch (Throwable t) {
629: continue;
630: }
631: String fieldFirstChar = "";
632: if (methodName.startsWith("is")) {
633: fieldFirstChar = methodName.substring(2, 3)
634: .toLowerCase();
635: sb.append(fieldFirstChar);
636: sb.append(methodName.substring(3));
637: } else if (methodName.startsWith("get")) {
638: fieldFirstChar = methodName.substring(3, 4)
639: .toLowerCase();
640: sb.append(fieldFirstChar);
641: sb.append(methodName.substring(4));
642: }
643: sb.append("=");
644: sb.append((value == null) ? "" : value);
645: sb.append("&");
646: }
647: }
648: return sb.toString();
649: }
650:
651: }
|