SqlExpression


1   package org.apache.torque.util;
2   
3   /*
4    * Licensed to the Apache Software Foundation (ASF) under one
5    * or more contributor license agreements.  See the NOTICE file
6    * distributed with this work for additional information
7    * regarding copyright ownership.  The ASF licenses this file
8    * to you under the Apache License, Version 2.0 (the
9    * "License"); you may not use this file except in compliance
10   * with the License.  You may obtain a copy of the License at
11   *
12   *   http://www.apache.org/licenses/LICENSE-2.0
13   *
14   * Unless required by applicable law or agreed to in writing,
15   * software distributed under the License is distributed on an
16   * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
17   * KIND, either express or implied.  See the License for the
18   * specific language governing permissions and limitations
19   * under the License.
20   */
21  
22  import java.lang.reflect.Array  ;
23  import java.util.Date  ;
24  import java.util.HashSet  ;
25  import java.util.Iterator  ;
26  import java.util.List  ;
27  
28  import org.apache.commons.lang.StringUtils;
29  import org.apache.torque.TorqueException;
30  import org.apache.torque.adapter.DB;
31  import org.apache.torque.om.DateKey;
32  import org.apache.torque.om.ObjectKey;
33  import org.apache.torque.om.StringKey;
34  
35  
36  /**
37   * This class represents a part of an SQL query found in the <code>WHERE</code>
38   * section.  For example:
39   * <pre>
40   * table_a.column_a = table_b.column_a
41   * column LIKE 'F%'
42   * table.column < 3
43   * </pre>
44   * This class is used primarily by {@link org.apache.torque.util.BasePeer}.
45   *
46   * @author <a HREF="mailto:jmcnally@collab.net">John D. McNally</a>
47   * @author <a HREF="mailto:dlr@finemaltcoding.com">Daniel Rall</a>
48   * @author <a HREF="mailto:fedor@apache.org">Fedor Karpelevitch</a>
49   * @author <a HREF="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
50   * @version $Id: SqlExpression.java 476550 2006-11-18 16:08:37Z tfischer $
51   */
52  public final class SqlExpression
53  {
54      /** escaped single quote */
55      private static final char SINGLE_QUOTE = '\'';
56      /** escaped backslash */
57      private static final char BACKSLASH = '\\';
58  
59      /**
60       * Private constructor to prevent instantiation.
61       *
62       * Class contains only static method ans should therefore not be
63       * instantiated.
64       */
65      private SqlExpression()
66      {
67      }
68  
69      /**
70       * Used to specify a join on two columns.
71       *
72       * @param column A column in one of the tables to be joined.
73       * @param relatedColumn The column in the other table to be joined.
74       * @return A join expression, e.g. UPPER(table_a.column_a) =
75       *         UPPER(table_b.column_b).
76       */
77      public static String   buildInnerJoin(String   column, String   relatedColumn)
78      {
79          // 'db' can be null because 'ignoreCase' is false.
80          return buildInnerJoin(column, relatedColumn, false, null);
81      }
82  
83      /**
84       * Used to specify a join on two columns.
85       *
86       * @param column A column in one of the tables to be joined.
87       * @param relatedColumn The column in the other table to be joined.
88       * @param ignoreCase If true and columns represent Strings, the appropriate
89       *        function defined for the database will be used to ignore
90       *        differences in case.
91       * @param db Represents the database in use for vendor-specific functions.
92       * @return A join expression, e.g. UPPER(table_a.column_a) =
93       *         UPPER(table_b.column_b).
94       */
95      public static String   buildInnerJoin(String   column,
96                                           String   relatedColumn,
97                                           boolean ignoreCase,
98                                           DB db)
99      {
100         int addlength = (ignoreCase) ? 25 : 1;
101         StringBuffer   sb = new StringBuffer  (column.length()
102                 + relatedColumn.length() + addlength);
103         buildInnerJoin(column, relatedColumn, ignoreCase, db, sb);
104         return sb.toString();
105     }
106 
107     /**
108      * Used to specify a join on two columns.
109      *
110      * @param column A column in one of the tables to be joined.
111      * @param relatedColumn The column in the other table to be joined.
112      * @param ignoreCase If true and columns represent Strings, the appropriate
113      *        function defined for the database will be used to ignore
114      *        differences in case.
115      * @param db Represents the database in use for vendor-specific functions.
116      * @param whereClause A StringBuffer to which the sql expression will be
117      *        appended.
118      */
119     public static void buildInnerJoin(String   column,
120                                        String   relatedColumn,
121                                        boolean ignoreCase,
122                                        DB db,
123                                        StringBuffer   whereClause)
124     {
125         if (ignoreCase)
126         {
127             whereClause.append(db.ignoreCase(column))
128                     .append('=')
129                     .append(db.ignoreCase(relatedColumn));
130         }
131         else
132         {
133             whereClause.append(column)
134                     .append('=')
135                     .append(relatedColumn);
136         }
137     }
138 
139 
140     /**
141      * Builds a simple SQL expression.
142      *
143      * @param columnName A column.
144      * @param criteria The value to compare the column against.
145      * @param comparison One of =, &lt;, &gt;, ^lt;=, &gt;=, &lt;&gt;,
146      *        !=, LIKE, etc.
147      * @return A simple SQL expression, e.g. UPPER(table_a.column_a)
148      *         LIKE UPPER('ab%c').
149      * @throws TorqueException Any exceptions caught during processing will be
150      *         rethrown wrapped into a TorqueException.
151      */
152     public static String   build(String   columnName,
153                                 Object   criteria,
154                                 SqlEnum comparison)
155         throws TorqueException
156     {
157         // 'db' can be null because 'ignoreCase' is null
158         return build(columnName, criteria, comparison, false, null);
159     }
160 
161     /**
162      * Builds a simple SQL expression.
163      *
164      * @param columnName A column.
165      * @param criteria The value to compare the column against.
166      * @param comparison One of =, &lt;, &gt;, ^lt;=, &gt;=, &lt;&gt;,
167      *        !=, LIKE, etc.
168      * @param ignoreCase If true and columns represent Strings, the appropriate
169      *        function defined for the database will be used to ignore
170      *        differences in case.
171      * @param db Represents the database in use, for vendor specific functions.
172      * @return A simple sql expression, e.g. UPPER(table_a.column_a)
173      *         LIKE UPPER('ab%c').
174      * @throws TorqueException Any exceptions caught during processing will be
175      *         rethrown wrapped into a TorqueException.
176      */
177     public static String   build(String   columnName,
178                                 Object   criteria,
179                                 SqlEnum comparison,
180                                 boolean ignoreCase,
181                                 DB db)
182         throws TorqueException
183     {
184         int addlength = (ignoreCase ? 40 : 20);
185         StringBuffer   sb = new StringBuffer  (columnName.length() + addlength);
186         build(columnName, criteria, comparison, ignoreCase, db, sb);
187         return sb.toString();
188     }
189 
190     /**
191      * Builds a simple SQL expression.
192      *
193      * @param columnName A column.
194      * @param criteria The value to compare the column against.
195      * @param comparison One of =, &lt;, &gt;, ^lt;=, &gt;=, &lt;&gt;,
196      *        !=, LIKE, etc.
197      * @param ignoreCase If true and columns represent Strings, the appropriate
198      *        function defined for the database will be used to ignore
199      *        differences in case.
200      * @param db Represents the database in use, for vendor specific functions.
201      * @param whereClause A StringBuffer to which the sql expression will be
202      *        appended.
203      */
204     public static void build(String   columnName,
205                               Object   criteria,
206                               SqlEnum comparison,
207                               boolean ignoreCase,
208                               DB db,
209                               StringBuffer   whereClause)
210             throws TorqueException
211     {
212         // Allow null criteria
213         // This will result in queries like
214         // insert into table (name, parent) values ('x', null);
215         //
216 
217         /* Check to see if the criteria is an ObjectKey
218          * and if the value of that ObjectKey is null.
219          * In that case, criteria should be null.
220          */
221 
222         if (criteria != null && criteria instanceof ObjectKey)
223         {
224             if (((ObjectKey) criteria).getValue() == null)
225             {
226                 criteria = null;
227             }
228         }
229         /*  If the criteria is null, check to see comparison
230          *  is an =, <>, or !=.  If so, replace the comparison
231          *  with the proper IS or IS NOT.
232          */
233 
234         if (criteria == null)
235         {
236             criteria = "null";
237             if (comparison.equals(Criteria.EQUAL))
238             {
239                 comparison = Criteria.ISNULL;
240             }
241             else if (comparison.equals(Criteria.NOT_EQUAL))
242             {
243                 comparison = Criteria.ISNOTNULL;
244             }
245             else if (comparison.equals(Criteria.ALT_NOT_EQUAL))
246             {
247                 comparison = Criteria.ISNOTNULL;
248             }
249         }
250         else
251         {
252             if (criteria instanceof String   || criteria instanceof StringKey)
253             {
254                 criteria = quoteAndEscapeText(criteria.toString(), db);
255             }
256             else if (criteria instanceof Date  )
257             {
258                 Date   dt = (Date  ) criteria;
259                 criteria = db.getDateString(dt);
260             }
261             else if (criteria instanceof DateKey)
262             {
263                 Date   dt = (Date  ) ((DateKey) criteria).getValue();
264                 criteria = db.getDateString(dt);
265             }
266             else if (criteria instanceof Boolean  )
267             {
268                 criteria = db.getBooleanString((Boolean  ) criteria);
269             }
270             else if (criteria instanceof Criteria)
271             {
272                  Query subquery = SQLBuilder.buildQueryClause(
273                         (Criteria) criteria,
274                         null,
275                         new SQLBuilder.QueryCallback() {
276                             public String   process(
277                                     Criteria.Criterion criterion,
278                                     List   params)
279                             {
280                                 return criterion.toString();
281                             }
282                 });
283                 if (comparison.equals(Criteria.IN)
284                         || comparison.equals(Criteria.NOT_IN))
285                 {
286                     // code below takes care of adding brackets
287                     criteria = subquery.toString();
288                 }
289                 else
290                 {
291                     criteria = "(" + subquery.toString() + ")";
292                 }
293             }
294         }
295 
296         if (comparison.equals(Criteria.LIKE)
297                 || comparison.equals(Criteria.NOT_LIKE)
298                 || comparison.equals(Criteria.ILIKE)
299                 || comparison.equals(Criteria.NOT_ILIKE))
300         {
301             buildLike(columnName, (String  ) criteria, comparison,
302                        ignoreCase, db, whereClause);
303         }
304         else if (comparison.equals(Criteria.IN)
305                 || comparison.equals(Criteria.NOT_IN))
306         {
307             buildIn(columnName, criteria, comparison,
308                      ignoreCase, db, whereClause);
309         }
310         else
311         {
312             // Do not put the upper/lower keyword around IS NULL
313             //  or IS NOT NULL
314             if (comparison.equals(Criteria.ISNULL)
315                     || comparison.equals(Criteria.ISNOTNULL))
316             {
317                 whereClause.append(columnName)
318                         .append(comparison);
319             }
320             else
321             {
322                 String   columnValue = criteria.toString();
323                 if (ignoreCase && db != null)
324                 {
325                     columnName = db.ignoreCase(columnName);
326                     columnValue = db.ignoreCase(columnValue);
327                 }
328                 whereClause.append(columnName)
329                         .append(comparison)
330                         .append(columnValue);
331             }
332         }
333     }
334 
335     /**
336      * Takes a columnName and criteria and builds an SQL phrase based
337      * on whether wildcards are present and the state of the
338      * ignoreCase flag.  Multicharacter wildcards % and * may be used
339      * as well as single character wildcards, _ and ?.  These
340      * characters can be escaped with \.
341      *
342      * e.g. criteria = "fre%" -> columnName LIKE 'fre%'
343      *                        -> UPPER(columnName) LIKE UPPER('fre%')
344      *      criteria = "50\%" -> columnName = '50%'
345      *
346      * @param columnName A column.
347      * @param criteria The value to compare the column against.
348      * @param comparison Whether to do a LIKE or a NOT LIKE
349      * @param ignoreCase If true and columns represent Strings, the
350      * appropriate function defined for the database will be used to
351      * ignore differences in case.
352      * @param db Represents the database in use, for vendor specific functions.
353      * @return An SQL expression.
354      */
355     static String   buildLike(String   columnName,
356                              String   criteria,
357                              SqlEnum comparison,
358                              boolean ignoreCase,
359                              DB db)
360     {
361         StringBuffer   whereClause = new StringBuffer  ();
362         buildLike(columnName, criteria, comparison, ignoreCase, db,
363                    whereClause);
364         return whereClause.toString();
365     }
366 
367     /**
368      * Takes a columnName and criteria and builds an SQL phrase based
369      * on whether wildcards are present and the state of the
370      * ignoreCase flag.  Multicharacter wildcards % and * may be used
371      * as well as single character wildcards, _ and ?.  These
372      * characters can be escaped with \.
373      *
374      * e.g. criteria = "fre%" -> columnName LIKE 'fre%'
375      *                        -> UPPER(columnName) LIKE UPPER('fre%')
376      *      criteria = "50\%" -> columnName = '50%'
377      *
378      * @param columnName A column name.
379      * @param criteria The value to compare the column against.
380      * @param comparison Whether to do a LIKE or a NOT LIKE
381      * @param ignoreCase If true and columns represent Strings, the
382      * appropriate function defined for the database will be used to
383      * ignore differences in case.
384      * @param db Represents the database in use, for vendor specific functions.
385      * @param whereClause A StringBuffer to which the sql expression
386      * will be appended.
387      */
388     static void buildLike(String   columnName,
389                            String   criteria,
390                            SqlEnum comparison,
391                            boolean ignoreCase,
392                            DB db,
393                            StringBuffer   whereClause)
394     {
395         // If selection criteria contains wildcards use LIKE otherwise
396         // use = (equals).  Wildcards can be escaped by prepending
397         // them with \ (backslash). However, if we switch from
398         // like to equals, we need to remove the escape characters.
399         // from the wildcards.
400         // So we need two passes: The first replaces * and ? by % and _,
401         // and checks whether we switch to equals,
402         // the second removes escapes if we have switched to equals.
403         int position = 0;
404         StringBuffer   sb = new StringBuffer  ();
405         boolean replaceWithEquals = true;
406         while (position < criteria.length())
407         {
408             char checkWildcard = criteria.charAt(position);
409 
410             switch (checkWildcard)
411             {
412             case BACKSLASH:
413                 // if text is escaped, all backslashes are already escaped,
414                 // so the next character after the backslash is the doubled
415                 // backslash from escaping.
416                 int charsToProceed = db.escapeText() ? 2 : 1;
417                 if (position + charsToProceed >= criteria.length())
418                 {
419                     charsToProceed = criteria.length() - position - 1;
420                 }
421                 else if (criteria.charAt(position + charsToProceed) == BACKSLASH
422                         && db.escapeText())
423                 {
424                     // the escaped backslash is also escaped,
425                     // so we need to proceed another character
426                     charsToProceed += 1;
427                 }
428                 sb.append(criteria.substring(
429                         position,
430                         position + charsToProceed));
431                 position += charsToProceed;
432                 // code below copies escaped character into sb
433                 checkWildcard = criteria.charAt(position);
434                 break;
435             case '%':
436             case '_':
437                 replaceWithEquals = false;
438                 break;
439             case '*':
440                 replaceWithEquals = false;
441                 checkWildcard = '%';
442                 break;
443             case '?':
444                 replaceWithEquals = false;
445                 checkWildcard = '_';
446                 break;
447             }
448 
449             sb.append(checkWildcard);
450             position++;
451         }
452         criteria = sb.toString();
453 
454         if (ignoreCase)
455         {
456             if (db.useIlike() && !replaceWithEquals)
457             {
458                 if (SqlEnum.LIKE.equals(comparison))
459                 {
460                     comparison = SqlEnum.ILIKE;
461                 }
462                 else if (SqlEnum.NOT_LIKE.equals(comparison))
463                 {
464                     comparison = SqlEnum.NOT_ILIKE;
465                 }
466             }
467             else
468             {
469                 // no native case insensitive like is offered by the DB,
470                 // or the LIKE was replaced with equals.
471                 // need to ignore case manually.
472                 columnName = db.ignoreCase(columnName);
473             }
474         }
475         whereClause.append(columnName);
476 
477         if (replaceWithEquals)
478         {
479             if (comparison.equals(Criteria.NOT_LIKE)
480                     || comparison.equals(Criteria.NOT_ILIKE))
481             {
482                 whereClause.append(" ").append(Criteria.NOT_EQUAL).append(" ");
483             }
484             else
485             {
486                 whereClause.append(" ").append(Criteria.EQUAL).append(" ");
487             }
488 
489             // remove escape backslashes from String
490             position = 0;
491             sb = new StringBuffer  ();
492             while (position < criteria.length())
493             {
494                 char checkWildcard = criteria.charAt(position);
495 
496                 if (checkWildcard == BACKSLASH)
497                 {
498                     // if text is escaped, all backslashes are already escaped,
499                     // so the next character after the backslash is the doubled
500                     // backslash from escaping.
501                     int charsToSkip = db.escapeText() ? 2 : 1;
502                     if (position + charsToSkip >= criteria.length())
503                     {
504                         charsToSkip = criteria.length() - position - 1;
505                     }
506                     else if (criteria.charAt(position + charsToSkip)
507                                 == BACKSLASH
508                             && db.escapeText())
509                     {
510                         // the escaped backslash is also escaped,
511                         // so we need to skip another character
512                         // but add the escaped backslash to sb
513                         // so that the escaping remains.
514                         sb.append(BACKSLASH);
515                         charsToSkip += 1;
516                     }
517                     position += charsToSkip;
518                     // code below copies escaped character into sb
519                     checkWildcard = criteria.charAt(position);
520                 }
521                 sb.append(checkWildcard);
522                 position++;
523             }
524             criteria = sb.toString();
525        }
526         else
527         {
528             whereClause.append(comparison);
529         }
530 
531         // If selection is case insensitive use SQL UPPER() function
532         // on criteria.
533         if (ignoreCase && (!(db.useIlike()) || replaceWithEquals))
534         {
535             criteria = db.ignoreCase(criteria);
536         }
537         whereClause.append(criteria);
538 
539         if (!replaceWithEquals && db.useEscapeClauseForLike())
540         {
541             whereClause.append(SqlEnum.ESCAPE)
542                        .append("'\\'");
543         }
544     }
545 
546     /**
547      * Takes a columnName and criteria (which must be an array) and
548      * builds a SQL 'IN' expression taking into account the ignoreCase
549      * flag.
550      *
551      * @param columnName A column.
552      * @param criteria The value to compare the column against.
553      * @param comparison Either " IN " or " NOT IN ".
554      * @param ignoreCase If true and columns represent Strings, the
555      * appropriate function defined for the database will be used to
556      * ignore differences in case.
557      * @param db Represents the database in use, for vendor specific functions.
558      * @return An SQL expression.
559      */
560     static String   buildIn(String   columnName,
561                           Object   criteria,
562                           SqlEnum comparison,
563                           boolean ignoreCase,
564                           DB db)
565     {
566         StringBuffer   whereClause = new StringBuffer  ();
567         buildIn(columnName, criteria, comparison,
568                 ignoreCase, db, whereClause);
569         return whereClause.toString();
570     }
571 
572     /**
573      * Takes a columnName and criteria (which must be an array) and
574      * builds a SQL 'IN' expression taking into account the ignoreCase
575      * flag.
576      *
577      * @param columnName A column.
578      * @param criteria The value to compare the column against.
579      * @param comparison Either " IN " or " NOT IN ".
580      * @param ignoreCase If true and columns represent Strings, the
581      * appropriate function defined for the database will be used to
582      * ignore differences in case.
583      * @param db Represents the database in use, for vendor specific functions.
584      * @param whereClause A StringBuffer to which the sql expression
585      * will be appended.
586      */
587     static void buildIn(String   columnName,
588                         Object   criteria,
589                         SqlEnum comparison,
590                         boolean ignoreCase,
591                         DB db,
592                         StringBuffer   whereClause)
593     {
594         if (ignoreCase)
595         {
596             whereClause.append(db.ignoreCase(columnName));
597         }
598         else
599         {
600             whereClause.append(columnName);
601         }
602 
603         whereClause.append(comparison);
604         HashSet   inClause = new HashSet  ();
605         if (criteria instanceof List  )
606         {
607             Iterator   iter = ((List  ) criteria).iterator();
608             while (iter.hasNext())
609             {
610                 Object   value = iter.next();
611 
612                 // The method processInValue() quotes the string
613                 // and/or wraps it in UPPER().
614                 inClause.add(processInValue(value, ignoreCase, db));
615             }
616         }
617         else if (criteria instanceof String  )
618         {
619             // subquery
620             inClause.add(criteria);
621         }
622         else
623         {
624             // Assume array.
625             for (int i = 0; i < Array.getLength(criteria); i++)
626             {
627                 Object   value = Array.get(criteria, i);
628 
629                 // The method processInValue() quotes the string
630                 // and/or wraps it in UPPER().
631                 inClause.add(processInValue(value, ignoreCase, db));
632             }
633         }
634         whereClause.append('(')
635                    .append(StringUtils.join(inClause.iterator(), ","))
636                    .append(')');
637     }
638 
639     /**
640      * Creates an appropriate string for an 'IN' clause from an
641      * object.  Adds quoting and/or UPPER() as appropriate.  This is
642      * broken out into a seperate method as it is used in two places
643      * in buildIn, depending on whether an array or List is being
644      * looped over.
645      *
646      * @param value The value to process.
647      * @param ignoreCase Coerce the value suitably for ignoring case.
648      * @param db Represents the database in use for vendor specific functions.
649      * @return Processed value as String.
650      */
651     static String   processInValue(Object   value,
652                                  boolean ignoreCase,
653                                  DB db)
654     {
655         String   ret = null;
656         if (value instanceof String  )
657         {
658             ret = quoteAndEscapeText((String  ) value, db);
659         }
660         else
661         {
662             ret = value.toString();
663         }
664         if (ignoreCase)
665         {
666             ret = db.ignoreCase(ret);
667         }
668         return ret;
669     }
670 
671     /**
672      * Quotes and escapes raw text for placement in a SQL expression.
673      * For simplicity, the text is assumed to be neither quoted nor
674      * escaped.
675      *
676      * @param rawText The <i>unquoted</i>, <i>unescaped</i> text to process.
677      * @param db the db
678      * @return Quoted and escaped text.
679      */
680     public static String   quoteAndEscapeText(String   rawText, DB db)
681     {
682         StringBuffer   buf = new StringBuffer  ((int) (rawText.length() * 1.1));
683 
684         // Some databases do not need escaping.
685         String   escapeString;
686         if (db != null && !db.escapeText())
687         {
688             escapeString = String.valueOf(BACKSLASH);
689         }
690         else
691         {
692             escapeString = String.valueOf(BACKSLASH)
693                     + String.valueOf(BACKSLASH);
694         }
695 
696         char[] data = rawText.toCharArray();
697         buf.append(SINGLE_QUOTE);
698         for (int i = 0; i < data.length; i++)
699         {
700             switch (data[i])
701             {
702             case SINGLE_QUOTE:
703                 buf.append(SINGLE_QUOTE).append(SINGLE_QUOTE);
704                 break;
705             case BACKSLASH:
706                 buf.append(escapeString);
707                 break;
708             default:
709                 buf.append(data[i]);
710             }
711         }
712         buf.append(SINGLE_QUOTE);
713 
714         return buf.toString();
715     }
716 }
717
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags