View Javadoc

1   /* ===================================================================
2    * Copyright 2002-06 SRI International.
3    * Released under the MOZILLA PUBLIC LICENSE Version 1.1
4    * which can be obtained at http://www.mozilla.org/MPL/MPL-1.1.html
5    * This software is distributed on an "AS IS"
6    * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied.
7    * See the License for the specific language governing rights and
8    * limitations under the License.
9    * =================================================================== */
10  
11  package com.sri.common.util;
12  
13  import com.jcorporate.expresso.core.controller.ControllerException;
14  import com.jcorporate.expresso.core.db.DBException;
15  import com.jcorporate.expresso.core.misc.ConfigManager;
16  import com.jcorporate.expresso.core.misc.ConfigurationException;
17  import com.jcorporate.expresso.core.misc.StringUtil;
18  
19  /***
20   * <p>Title: QueryUtils </p>
21   * <p>Description: Utilities for formatting keywords and where clauses for queries </p>
22   * <p>Note:  DBObj.buildWhereClause() basically works for building where clauses,
23   * so why do I write similar methods here? There are some issues with buildWhereClause
24   * and package protection for it (and various) methods, so I had to do my own methods
25   * here. Hopefully a later version of Expresso will make more methods public
26   * instead of protected. Here are some of the issues with using
27   * DBObject.buildWhereClause() in the version of Expresso we are currently using:</p>
28   * <ol>
29   * <li>) it only allows AND as a conjunction; no option to do OR,
30   * which I need for keyword matches
31   * <li> it automatically prepends WHERE to the front (WHERE is added by setWhereClause)
32   * so we'd have to manually strip it
33   * <li> it's package protected instead of public so all DBObjects you want
34   * to call buildWhereClause on will have to implement a new public method that
35   * ust calls buildWhereClause
36   * </ol>
37   * <p> So, I wrote my own below. In some of the methods below I would have liked to
38   * pass the DBObject and just look for what fields were set (like buildWhereClause does)
39   * but I can't even access getAllFieldsIterator() here (it is package protected
40   * also) so I can't loop through the fields in the object to see which ones are set.
41   * Aaag! So I just construct and pass arrays with the relevant fields instead.<p>
42   *
43   * @author Patti Schank and Zaz Harris
44   */
45  
46  public class QueryUtils {
47      // characters that could mess up database queries
48      public static final String[] STRIP_CHARS = {"\n", "\r"};
49  
50      // like wildcards that should be escaped
51      public static final String[] LIKE_WILDCARDS = {"%", "_"};
52  
53      // special chars for db
54      public static final String ESCAPE_CHAR = "//";
55      public static final String ENCLOSE_CHAR = "'";
56  
57  //    private static Category sLog = null;
58  
59      // "unknown" value constant
60      public static String UNKNOWN_VALUE = "(unknown)";
61  
62      // descending sort
63      public static String DESC_SORT = " DESC";
64  
65      /***
66       * Create logger as necessary.
67       *
68       * @return Logger
69       */
70  //    private static Category getLogger() {
71  //        if (sLog == null) {
72  //            setupLog();
73  //        }
74  //        return sLog;
75  //    }
76  
77      /***
78       * Set up log.
79       */
80  //    private static synchronized void setupLog() {
81  //        if (sLog == null) {
82  //
83  //            sLog = Category.getInstance(QueryUtils.class.getName());
84  //        }
85  //    }
86  
87      /***
88       * Prepares a value to be in a like clause by escaping the enclosing
89       * character and any wildcards and stripping any characters db can't handle.
90       * Returns null if value is null.
91       *
92       * @param value The value to be modified
93       * @return The modified value ready to be used in a like clause
94       */
95      public static String prepLikeValue(String value) {
96          if (value == null) return null;
97          value = stripChars(value);
98          value = escapeChar(value, ENCLOSE_CHAR);
99          for (int i = 0; i < LIKE_WILDCARDS.length; i++) {
100             value = escapeChar(value, LIKE_WILDCARDS[i]);
101         }
102         return value;
103     }
104 
105     /***
106      * Prepares a value to be in a equals clause by escaping the enclosing
107      * character and stripping any characters db can't handle.
108      * Returns null if value is null.
109      *
110      * @param value The value to be modified
111      * @return The modified value ready to be used in an equals clause
112      */
113     public static String prepEqualsValue(String value) {
114         if (value == null) return null;
115         value = stripChars(value);
116         value = escapeChar(value, ENCLOSE_CHAR);
117         return value;
118     }
119 
120     /***
121      * Removes undesirable characters from a string.  Returns null if value is
122      * null.
123      *
124      * @param value The string value passed in
125      * @return The stripped string
126      */
127     public static String stripChars(String value) {
128         if (value == null) return null;
129         for (int i = 0; i < STRIP_CHARS.length; i++) {
130             int stripIndex = value.indexOf(STRIP_CHARS[i]);
131             while (stripIndex != -1) {
132                 if (stripIndex == value.length() - 1) {
133                     value = value.substring(value.length() - 2);
134                     break;
135                 }
136 
137                 value = value.substring(0, stripIndex) + value.substring(stripIndex + 1);
138                 stripIndex = value.indexOf(STRIP_CHARS[i]);
139             }
140         }
141         return value;
142     }
143 
144     /***
145      * Precedes each instance of a character passed in with the escape character
146      * in the string passed in.
147      *
148      * @param value     The string to modify
149      * @param charToEsc The character precede with the escape character
150      * @return The modified string
151      */
152     public static String escapeChar(String value, String charToEsc) {
153         if (value == null) return null;
154         return StringUtil.replace(value, charToEsc, ESCAPE_CHAR + charToEsc);
155     }
156 
157 
158     /***
159      * Generate a subclause that determines if the values exist for the given fields
160      * of an object. Uses AND and is case sensitive. Could use DBObj.buildWhereClause
161      * but that only works within the expresso package and doesn't allow option of OR
162      * conjunction! Can't use DBObj methods because they are protected (duh, why?)
163      * Use this if you are constructing a custom clause clause for other
164      * purposes and need to do an exact match on fields also.
165      * Returns a clause of the form
166      * <p/>
167      * DbObj.FIELDNAME1 = '<value1>'
168      * AND    DbObj.FIELDNAME2 = '<value2>'
169      * AND ... (repeat for field and value 2...n)
170      *
171      * @param fields the array of fields
172      * @param values the array of values
173      * @return a String with the clause
174      */
175     public static String fieldEqualsValueClause(String[] fields,
176                                                 String[] values) {
177         return fieldEqualsValueClause(fields, values, false, true, false);
178     }
179 
180     /***
181      * Generate a subclause that determines if the values exist for the given fields
182      * of an object. Uses AND by default, unless the parameters specify to use OR.
183      * Use this if you are constructing a custom clause clause for other
184      * purposes and need to do an exact match on fields also.
185      * Returns a clause of the form
186      * <p/>
187      * DbObj.FIELDNAME1 = '<value1>'
188      * <OR/AND>   DbObj.FIELDNAME2 = '<value2>'
189      * <OR/AND> .... (repeat for field and value 2...n)
190      *
191      * @param fields        the array of fields
192      * @param values        the array of values
193      * @param useOr         true if the clause should OR results, otherwise it will AND
194      * @param caseSensitive true if the field match should be case sensitive
195      * @param not           If true, matches field that aren't equal
196      * @return a String with the clause
197      */
198     public static String fieldEqualsValueClause(String[] fields,
199                                                 String[] values,
200                                                 boolean useOr,
201                                                 boolean caseSensitive,
202                                                 boolean not) {
203         // if we don't have the same number of fields as values, abort
204         if (fields == null || values == null || fields.length != values.length) {
205             return null;
206         }
207 
208         // use AND by default unless told to use OR
209         String conjunction = "AND";
210         if (useOr) {
211             conjunction = "OR";
212         }
213 
214         StringBuffer clause = new StringBuffer();
215         clause.append("(");
216 
217         // loop through field array and construct a clause that looks
218         // for a match with the cooresponding value
219         for (int i = 0; i < fields.length; i++) {
220             if (i > 0) {
221                 // more than one field to match; add a conjunction
222                 clause.append(" ");
223                 clause.append(conjunction);
224                 clause.append(" ");
225             }
226             clause.append(fieldEqualsValueClause(fields[i], values[i], caseSensitive, not));
227         }
228         clause.append(")");
229         return clause.toString();
230     }
231 
232     /***
233      * Constructs a SQL where clause checking if a field equals any of the
234      * values in the array passed in.  For example:
235      * <p/>
236      * ((Field1 == 'Value1') OR (Field1 == 'Value2') OR (Field1 == 'Value3'))
237      * <p/>
238      * where field = "Field1" and value = ["Value1", "Value2", "Value3"]
239      *
240      * @param field  The field to check
241      * @param values The values to check the field against
242      * @return The constructed SQL clause; null if not needed
243      */
244     public static String fieldEqualsManyValuesClause(String field,
245                                                      String[] values) {
246         return fieldEqualsManyValuesClause(field, values, true, false);
247     }
248 
249     /***
250      * Constructs a SQL where clause checking if a field equals any of the
251      * values in the array passed in.  For example:
252      * <p/>
253      * ((Field1 == 'Value1') OR (Field1 == 'Value2') OR (Field1 == 'Value3'))
254      * <p/>
255      * where field = "Field1" and value = ["Value1", "Value2", "Value3"]
256      *
257      * @param field         The field to check
258      * @param values        The values to check the field against
259      * @param caseSensitive If true, query is case sensitive
260      * @param not           If true, checks if field doesn't equal any of the values
261      * @return The constructed SQL clause; null if not needed
262      */
263     public static String fieldEqualsManyValuesClause(String field,
264                                                      String[] values,
265                                                      boolean caseSensitive,
266                                                      boolean not) {
267         // check incoming values
268         if (field == null || values == null || values.length == 0) return null;
269 
270         /*
271         return fieldInSetClause(field, values, caseSensitive, not);
272         */
273 
274         // use fieldEqualsValueClause
275         String[] fields = new String[values.length];
276         for (int i = 0; i < fields.length; i++) fields[i] = field;
277         if (not) {
278             return fieldEqualsValueClause(fields, values, false, caseSensitive, true);
279         } else {
280             return fieldEqualsValueClause(fields, values, true, caseSensitive, false);
281         }
282     }
283 
284     /***
285      * Generate a subclause that determines if the values exist for the given fields
286      * of an object. Returns a clause of the form
287      * <p/>
288      * DbObj.FIELDNAME = '<value>'
289      *
290      * @param field The field of the object to look in
291      * @param value The value to look for
292      * @return a String with the clause
293      */
294     public static String fieldEqualsValueClause(String field,
295                                                 String value) {
296         return fieldEqualsValueClause(field, value, true, false);
297     }
298 
299     /***
300      * Generate a subclause that determines if the values exist for the given fields
301      * of an object. Returns a clause of the form
302      * <p/>
303      * DbObj.FIELDNAME = '<value>'
304      *
305      * @param field         The field of the object to look in
306      * @param value         The value to look for
307      * @param caseSensitive If true, query is case sensitive
308      * @param not           If true, query checks if field does NOT equal value
309      * @return a String with the clause
310      */
311     public static String fieldEqualsValueClause(String field,
312                                                 String value,
313                                                 boolean caseSensitive,
314                                                 boolean not) {
315         if (field == null || value == null) return null;
316 
317         StringBuffer clause = new StringBuffer();
318 
319         // strip any characters that could break a clause
320         value = prepEqualsValue(value);
321 
322         // build clause string
323         if (!caseSensitive) {
324             clause.append("UPPER(");
325             clause.append(field);
326             clause.append(") ");
327             if (not) clause.append("!");
328             clause.append("= '");
329             clause.append(value.toUpperCase());
330             clause.append("'");
331         } else {
332             clause.append(field);
333             clause.append(" ");
334             if (not) clause.append("!");
335             clause.append("= '");
336             clause.append(value);
337             clause.append("'");
338         }
339         return clause.toString();
340     }
341 
342     /***
343      * Generate a subclause that determines if the values is null a given field
344      * of an object. Returns a clause of the form
345      * <p/>
346      * DbObj.FIELDNAME = '<value>'
347      *
348      * @param field The field of the object to look in
349      * @param not   If true, query checks if field is NOT null
350      * @return a String with the clause
351      */
352     public static String fieldEqualsNullClause(String field,
353                                                boolean not) {
354         if (field == null) return null;
355 
356         StringBuffer clause = new StringBuffer();
357 
358         // build clause string
359         clause.append(field);
360         clause.append(" IS");
361         if (not) clause.append(" NOT");
362         clause.append(" NULL");
363 
364         return clause.toString();
365     }
366 
367 
368     /***
369      * Generate a subclause that determines if all of the keywords
370      * exist in the given field of an object using AND. Returns a clause
371      * of the form:
372      * <p/>
373      * (<field> LIKE '<keyword1>' AND
374      * <field> LIKE '<keyword2>' AND
375      * <field> LIKE '<keyword3>' AND ... )
376      * <p/>
377      * Returns null if field is null or all keywords to match are null.
378      *
379      * @param keywordArray The keyword array to match
380      * @param field        The field to match
381      * @return a String with the clause
382      */
383     public static String keywordMatchClause(String[] keywordArray,
384                                             String field) {
385         String[] fields = {field};
386         return keywordMatchClause(keywordArray, fields, false);
387     }
388 
389     /***
390      * Generate a subclause that determines if all of the keywords
391      * exist in the given fields of an object using AND. Returns a clause
392      * of the form:
393      * <p/>
394      * <p/>
395      * ((<field1> LIKE '<keyword1>' OR
396      * <field2> LIKE '<keyword1>' OR
397      * <field3> LIKE '<keyword1>') AND
398      * (<field1> LIKE '<keyword2>' OR
399      * <field2> LIKE '<keyword2>' OR
400      * <field3> LIKE '<keyword2>') AND ...)
401      * <p/>
402      * Returns null if all fields are null or all keywords to match are null.
403      *
404      * @param keywordArray the keyword array to match
405      * @param fields       The fields to match
406      * @return a String with the clause
407      */
408     public static String keywordMatchClause(String[] keywordArray,
409                                             String[] fields) {
410         return keywordMatchClause(keywordArray, fields, false);
411     }
412 
413     /***
414      * Generate a subclause that determines if the keywords exist in the given fields
415      * of an object. Uses AND by default, unless the parameters specify to use OR.
416      * Returns a clause of the form:
417      * <p/>
418      * ((<field1> LIKE '<keyword1>' OR
419      * <field2> LIKE '<keyword1>' OR
420      * <field3> LIKE '<keyword1>') AND/OR
421      * (<field1> LIKE '<keyword2>' OR
422      * <field2> LIKE '<keyword2>' OR
423      * <field3> LIKE '<keyword2>') AND/OR ...)
424      * <p/>
425      * Returns null if all fields are null or all keywords to match are null.
426      *
427      * @param keywordArray the keyword string to match
428      * @param fields       The array of fields to match
429      * @param useOr        true if the clause should OR results, otherwise it will AND
430      * @return a String with the clause
431      */
432     private static String keywordMatchClause(String[] keywordArray,
433                                              String[] fields,
434                                              boolean useOr) {
435         if (keywordArray == null) return null;
436 
437         // use AND by default unless told to use OR
438         String conjunction = "AND";
439         if (useOr) conjunction = "OR";
440 
441         StringBuffer clause = new StringBuffer();
442 
443         // loop through keyword array and construct a clause that looks
444         // for a match on all of the keywords.
445         boolean foundOne = false;
446         for (int i = 0; i < keywordArray.length; i++) {
447             // Get match clause
448             String oneMatchClause = keywordMatchClause(keywordArray[i], fields);
449 
450             // Add to main clause if not null
451             if (oneMatchClause != null) {
452                 if (foundOne) {
453                     // more than one keyword to match; add a conjunction
454                     clause.append(" ");
455                     clause.append(conjunction);
456                     clause.append(" ");
457                 }
458                 clause.append(oneMatchClause);
459                 foundOne = true;
460             }
461         }
462 
463         // Return null if empty clause
464         String result = clause.toString();
465         if (result.equals("")) return null;
466 
467         return ("(" + result + ")");
468     }
469 
470     /***
471      * Generate a subclause that determines if the keywords exist in the given fields
472      * of an object. Strips any dangerous characters like single or double
473      * quote or wildcard match (%) from clause. Returns a clause of the form:
474      * <p/>
475      * <field> LIKE '%<keyword1>%'
476      * <p/>
477      * Returns null if keywordToMatch or field is null.
478      *
479      * @param keywordToMatch the keyword string to match
480      * @param field          the field of the object to look in
481      * @return a String with the clause
482      */
483     public static String keywordMatchClause(String keywordToMatch,
484                                             String field) {
485         String[] fields = {field};
486         return keywordMatchClause(keywordToMatch, fields);
487     }
488 
489     /***
490      * Generate a subclause that determines if the keywords exist in the given
491      * fields of an object. Allows unlimited fields to be specified, any of
492      * which can be null. Strips any dangerous characters like single or double
493      * quote or wildcard match (%) from clause. Returns a clause of the form:
494      * <p/>
495      * <field1> LIKE '%<keyword>%'
496      * <field2> LIKE '%<keyword>%'
497      * <field3> LIKE '%<keyword>%' ... )
498      * <p/>
499      * Returns null if all fields are null or keywordToMatch is null.
500      *
501      * @param keywordToMatch The keyword string to match
502      * @param fields         The fields to match
503      * @return a String with the clause
504      */
505     public static String keywordMatchClause(String keywordToMatch,
506                                             String[] fields
507     ) {
508         if (keywordToMatch == null) return null;
509 
510         StringBuffer clause = new StringBuffer();
511 
512         boolean foundOne = false;
513         for (int i = 0; i < fields.length; i++) {
514             String oneField = fields[i];
515             if (oneField != null) {
516                 if (foundOne) clause.append(" OR ");
517                 clause.append(fieldLikeValue(oneField, keywordToMatch));
518                 foundOne = true;
519             }
520         }
521 
522         // Return null if empty clause
523         String result = clause.toString();
524         if (result.equals("")) return null;
525 
526         return ("(" + result + ")");
527     }
528 
529     /***
530      * Returns a REGEXP clause to determine if a field is blank.  This is
531      * MySQL dependent.
532      *
533      * @param field   The field to check
534      * @param inverse If true, checks if field is NOT blank
535      * @return A REGEXP clause
536      */
537     public static String fieldIsBlankClause(String field,
538                                             boolean inverse) {
539         if (field == null) return null;
540         StringBuffer clause = new StringBuffer();
541         clause.append("(LENGTH(LTRIM(RTRIM(");
542         clause.append(field);
543         clause.append(")))");
544         if (inverse) clause.append("!");
545         clause.append("=0)");
546         return clause.toString();
547     }
548 
549     /***
550      * Return a LIKE clause of the form <field> LIKE '%<value>%'
551      * Returns null if value or field is null.
552      *
553      * @param field the database object field
554      * @param value the value to match
555      * @return A LIKE clause
556      */
557     public static String fieldLikeValue(String field,
558                                         String value) {
559         if (value == null || field == null) return null;
560         StringBuffer clause = new StringBuffer();
561 
562         value = prepLikeValue(value);
563 
564         clause.append(field);
565         clause.append(" LIKE '%");
566         clause.append(value);
567         clause.append("%'");
568         return clause.toString();
569     }
570 
571     /***
572      * Takes a field and an alphabetical "chunk" and constructs a query to check
573      * for values for the field passed in starting with characters within that
574      * chunk's (inclusive) range.
575      * <p/>
576      * Valid chunks are three characters in the form A-C, where A is the
577      * beginning of the range, C is the end, and - is any placeholder character.
578      * <p/>
579      * So if field,A-C produces:
580      * <p/>
581      * ((field LIKE 'A%') || (field LIKE 'B%') || (field LIKE 'C%'))
582      * <p/>
583      * If field or alphaChunk is null, returns null.
584      *
585      * @param field        The field to use in the clause
586      * @param alphaChunk   The alphabetical "chunk" to use as a range
587      * @param reverseChunk If true, the sort is reversed
588      * @return The constructed like clause checking for an alphabetical range
589      * @throws ControllerException
590      */
591     private static String advAlphaLikeClause(String field,
592                                              String alphaChunk,
593                                              boolean reverseChunk)
594             throws ControllerException {
595         // Get ranges
596         int dashIndex = alphaChunk.indexOf('-');
597         String begRange = alphaChunk.substring(0, dashIndex);
598         String endRange = alphaChunk.substring(dashIndex + 1);
599         if (reverseChunk) {
600             String temp = begRange;
601             begRange = endRange;
602             endRange = temp;
603         }
604 
605         // Check range is valid
606         if (begRange.compareTo(endRange) == 1) {
607             throw new ControllerException("TI: Bad range passed to advAlphaLikeClause: [" + begRange + "] to [" + endRange + "]");
608         }
609 
610         // Adjust beginning range, if necessary
611         while (endRange.charAt(0) == begRange.charAt(0) &&
612                 endRange.length() > begRange.length()) {
613             begRange += "a";
614         }
615 
616         // If ranges are the same, just return like clause
617         if (begRange.equalsIgnoreCase(endRange)) {
618             return "(" + field + " LIKE '" + begRange + "%')";
619         }
620 
621         // Make like clause
622         String curPrefix = begRange;
623         String likeClause = "((" + field + " LIKE '" + begRange + "%')";
624         while (!curPrefix.equalsIgnoreCase(endRange)) {
625             curPrefix = incAlphaStr(curPrefix);
626             if (curPrefix.charAt(0) == endRange.charAt(0)) {
627                 if (curPrefix.length() > endRange.length()) {
628                     curPrefix = curPrefix.substring(0, endRange.length());
629                 } else if (endRange.charAt(0) == curPrefix.charAt(0)) {
630                     while (endRange.length() > curPrefix.length()) {
631                         curPrefix += "a";
632                     }
633                 }
634             }
635             likeClause += " OR (" + field + " LIKE '" + curPrefix + "%')";
636         }
637         likeClause += ")";
638 
639         return likeClause;
640     }
641 
642     /***
643      * "Increments" an alpha string, rolling Z to A, while preserving case,
644      * e.g. "ABC" becomes "ABD" and "Az" becomes "Ba".
645      *
646      * @param str The string to "increment"
647      * @return The resulting incremented string
648      */
649     private static String incAlphaStr(String str) {
650         int length = str.length();
651         if (length == 0) return "";
652         boolean doBreak = false;
653         for (int i = length - 1; i >= 0; i--) {
654             char c = str.charAt(i);
655             if (c == 'z') {
656                 c = 'a';
657             } else if (c == 'Z') {
658                 c = 'A';
659             } else {
660                 doBreak = true;
661                 c++;
662             }
663             str = str.substring(0, i) + c + str.substring(i + 1, str.length());
664             if (doBreak) break;
665             doBreak = false;
666         }
667         return str;
668     }
669 
670 
671     /***
672      * See comments in other method signature.
673      *
674      * @param field      The field to use in the clause
675      * @param alphaChunk The alphabetical "chunk" to use as a range
676      * @return The constructed like clause checking for an alphabetical range
677      * @throws ControllerException
678      */
679     public static String alphaLikeClause(String field,
680                                          String alphaChunk)
681             throws ControllerException {
682         return alphaLikeClause(field, alphaChunk, false);
683     }
684 
685     /***
686      * Takes a field and an alphabetical "chunk" and constructs a query to check
687      * for values for the field passed in starting with characters within that
688      * chunk's (inclusive) range.
689      * <p/>
690      * Valid chunks are three characters in the form A-C, where A is the
691      * beginning of the range, C is the end, and - is any placeholder character.
692      * <p/>
693      * So if field,A-C produces:
694      * <p/>
695      * ((field LIKE 'A%') || (field LIKE 'B%') || (field LIKE 'C%'))
696      * <p/>
697      * If field or alphaChunk is null, returns null.
698      *
699      * @param field        The field to use in the clause
700      * @param alphaChunk   The alphabetical "chunk" to use as a range
701      * @param reverseChunk If true, the sort is reversed
702      * @return The constructed like clause checking for an alphabetical range
703      * @throws ControllerException
704      */
705     public static String alphaLikeClause(String field,
706                                          String alphaChunk,
707                                          boolean reverseChunk)
708             throws ControllerException {
709         // Return null if one or the other is blank or empty
710         if (StringUtil.isBlankOrNull(field) ||
711                 StringUtil.isBlankOrNull(alphaChunk)) {
712             return null;
713         }
714 
715         // SPECIAL CASE: ignore reverse if chunk is not letters
716         if (!Character.isLetter(alphaChunk.charAt(0))) reverseChunk = false;
717 
718         // If solid range, just return simple like clause
719         int dashIndex = alphaChunk.indexOf('-');
720         if (dashIndex == -1) {
721             return "(" + field + " LIKE '" + alphaChunk + "%')";
722         }
723 
724         // Throw exception if dash isn't in the middle somewhere
725         if (alphaChunk.startsWith("-") || alphaChunk.endsWith("-")) {
726             throw new ControllerException("TI: Bad chunk passed to alphaLikeClause: " + alphaChunk);
727         }
728 
729         // Go to advanced method if chunk is longer than 3 characters
730         if (alphaChunk.length() > 3) {
731             return advAlphaLikeClause(field, alphaChunk, reverseChunk);
732         }
733 
734         // Do simple alpha chunk processing
735         char first = alphaChunk.charAt(0);
736         char last = first;
737         if (alphaChunk.length() >= 3) last = alphaChunk.charAt(2);
738         if (reverseChunk) {
739             char temp = first;
740             first = last;
741             last = temp;
742         }
743         String likeClause = "(";
744         for (char c = first; c <= last; c++) {
745             likeClause += "(" + field + " LIKE '" + c + "%')";
746             if (c != last) likeClause += " OR ";
747         }
748         likeClause += ")";
749         if (likeClause.equals("()")) likeClause = "";
750         return likeClause;
751     }
752 
753 
754     /***
755      * Returns a boolean string for use in a database query, given the database
756      * context found in the request.
757      *
758      * @param dataContext The database context
759      * @param value       The boolean value
760      * @return A boolean value string for use in a database query
761      * @throws DBException
762      */
763     public static String getBoolStr(String dataContext,
764                                     boolean value)
765             throws DBException {
766         try {
767             boolean nativeBoolean = ConfigManager.getContext(dataContext).getJdbc().isNativeBool();
768 
769             if (value) {
770                 if (nativeBoolean) {
771                     return "true";
772                 } else {
773                     return "Y";
774                 }
775             } else {
776                 if (nativeBoolean) {
777                     return "false";
778                 } else {
779                     return "N";
780                 }
781             }
782         } catch (ConfigurationException ce) {
783             throw new DBException(ce);
784         }
785     }
786 
787     /***
788      * Adds necessary fields to fieldsToRetrieve.
789      *
790      * @param fieldsToRetrieve String of fields to retrieve
791      * @param requiredFields   Required fields
792      * @return Appended fieldsToRetrieve string
793      */
794     public static String addAnyRequiredField(String fieldsToRetrieve,
795                                              String[] requiredFields) {
796         for (int i = 0; i < requiredFields.length; i++) {
797             if (fieldsToRetrieve.indexOf(requiredFields[i]) == -1) {
798                 fieldsToRetrieve += "|" + requiredFields[i];
799             }
800         }
801         return fieldsToRetrieve;
802     }
803 
804     /***
805      * Return true if the value passed in is in the alphabetical chunk passed
806      * in.
807      *
808      * @param alphaChunk   The alphabetical chunk
809      * @param value        The value to check
810      * @param reverseChunk If true, the sort is reversed
811      * @return True if the value passed in is in the alphabetical chunk
812      * @throws ControllerException
813      */
814     public static boolean isValueInAlphaChunk(String alphaChunk,
815                                               String value,
816                                               boolean reverseChunk)
817             throws ControllerException {
818         value = value.toLowerCase();
819 
820         // Get ranges
821         int dashIndex = alphaChunk.indexOf('-');
822         String begRange = alphaChunk.substring(0, dashIndex);
823         String endRange = alphaChunk.substring(dashIndex + 1);
824         if (reverseChunk) {
825             String temp = begRange;
826             begRange = endRange;
827             endRange = temp;
828         }
829 
830         // Check range is valid
831         if (begRange.compareTo(endRange) == 1) {
832             throw new ControllerException("TI: Bad range passed to advAlphaLikeClause: [" + begRange + "] to [" + endRange + "]");
833         }
834 
835         // Adjust beginning range, if necessary
836         while (endRange.charAt(0) == begRange.charAt(0) &&
837                 endRange.length() > begRange.length()) {
838             begRange += "a";
839         }
840 
841         // If ranges are the same, just see if value starts with the chunk
842         if (begRange.equalsIgnoreCase(endRange)) {
843             return value.startsWith(begRange.toLowerCase());
844         }
845 
846         // Make like clause
847         String curPrefix = begRange;
848         while (!curPrefix.equalsIgnoreCase(endRange)) {
849             curPrefix = incAlphaStr(curPrefix);
850             if (curPrefix.charAt(0) == endRange.charAt(0)) {
851                 if (curPrefix.length() > endRange.length()) {
852                     curPrefix = curPrefix.substring(0, endRange.length());
853                 } else if (endRange.charAt(0) == curPrefix.charAt(0)) {
854                     while (endRange.length() > curPrefix.length()) {
855                         curPrefix += "a";
856                     }
857                 }
858             }
859             if (value.startsWith(curPrefix.toLowerCase())) return true;
860         }
861 
862         return false;
863     }
864 
865     /***
866      * Generate a subclause that determines if a field has a value in the given
867      * set of values.
868      * <p/>
869      * DbObj.FIELDNAME IN ('<value1>','<value2>' ...)
870      *
871      * @param field  the field name
872      * @param values the array of values
873      * @param not    If true, matches field that aren't equal
874      * @return a String with the clause
875      */
876     public static String fieldInSetClause(String field,
877                                           String[] values,
878                                           boolean not) {
879         // if we don't have the same number of fields as values, abort
880         if (field == null || values == null) return null;
881 
882         StringBuffer clause = new StringBuffer("(");
883         clause.append(field);
884         if (not) clause.append(" NOT");
885         clause.append(" IN (");
886         for (int i = 0; i < values.length; i++) {
887             if (i != 0) clause.append(",");
888             String value = "'" + values[i] + "'";
889             clause.append(value);
890         }
891         clause.append("))");
892         return clause.toString();
893     }
894 }