View Javadoc
1 package org.apache.torque.util; 2 3 /* ==================================================================== 4 * The Apache Software License, Version 1.1 5 * 6 * Copyright (c) 2001-2003 The Apache Software Foundation. All rights 7 * reserved. 8 * 9 * Redistribution and use in source and binary forms, with or without 10 * modification, are permitted provided that the following conditions 11 * are met: 12 * 13 * 1. Redistributions of source code must retain the above copyright 14 * notice, this list of conditions and the following disclaimer. 15 * 16 * 2. Redistributions in binary form must reproduce the above copyright 17 * notice, this list of conditions and the following disclaimer in 18 * the documentation and/or other materials provided with the 19 * distribution. 20 * 21 * 3. The end-user documentation included with the redistribution, 22 * if any, must include the following acknowledgment: 23 * "This product includes software developed by the 24 * Apache Software Foundation (http://www.apache.org/)." 25 * Alternately, this acknowledgment may appear in the software itself, 26 * if and wherever such third-party acknowledgments normally appear. 27 * 28 * 4. The names "Apache" and "Apache Software Foundation" and 29 * "Apache Turbine" must not be used to endorse or promote products 30 * derived from this software without prior written permission. For 31 * written permission, please contact apache@apache.org. 32 * 33 * 5. Products derived from this software may not be called "Apache", 34 * "Apache Turbine", nor may "Apache" appear in their name, without 35 * prior written permission of the Apache Software Foundation. 36 * 37 * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED 38 * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES 39 * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE 40 * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR 41 * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 42 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 43 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF 44 * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND 45 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, 46 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT 47 * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF 48 * SUCH DAMAGE. 49 * ==================================================================== 50 * 51 * This software consists of voluntary contributions made by many 52 * individuals on behalf of the Apache Software Foundation. For more 53 * information on the Apache Software Foundation, please see 54 * <http://www.apache.org/>. 55 */ 56 57 import java.lang.reflect.Array; 58 import java.util.Date; 59 import java.util.HashSet; 60 import java.util.Iterator; 61 import java.util.List; 62 63 import org.apache.commons.lang.StringUtils; 64 import org.apache.torque.TorqueException; 65 import org.apache.torque.adapter.DB; 66 import org.apache.torque.om.DateKey; 67 import org.apache.torque.om.ObjectKey; 68 import org.apache.torque.om.StringKey; 69 70 71 /*** 72 * This class represents a part of an SQL query found in the <code>WHERE</code> 73 * section. For example: 74 * <pre> 75 * table_a.column_a = table_b.column_a 76 * column LIKE 'F%' 77 * table.column < 3 78 * </pre> 79 * This class is used primarily by {@link org.apache.torque.util.BasePeer}. 80 * 81 * @author <a href="mailto:jmcnally@collab.net">John D. McNally</a> 82 * @author <a href="mailto:dlr@finemaltcoding.com">Daniel Rall</a> 83 * @author <a href="mailto:fedor@apache.org">Fedor Karpelevitch</a> 84 * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a> 85 * @version $Id: SqlExpression.java,v 1.24 2003/08/27 22:50:11 mpoeschl Exp $ 86 */ 87 public class SqlExpression 88 { 89 /*** escaped single quote */ 90 private static final char SINGLE_QUOTE = '\''; 91 /*** escaped backslash */ 92 private static final char BACKSLASH = '//'; 93 94 /*** 95 * Used to specify a join on two columns. 96 * 97 * @param column A column in one of the tables to be joined. 98 * @param relatedColumn The column in the other table to be joined. 99 * @return A join expression, e.g. UPPER(table_a.column_a) = 100 * UPPER(table_b.column_b). 101 */ 102 public static String buildInnerJoin(String column, String relatedColumn) 103 { 104 // 'db' can be null because 'ignoreCase' is false. 105 return buildInnerJoin(column, relatedColumn, false, null); 106 } 107 108 /*** 109 * Used to specify a join on two columns. 110 * 111 * @param column A column in one of the tables to be joined. 112 * @param relatedColumn The column in the other table to be joined. 113 * @param ignoreCase If true and columns represent Strings, the appropriate 114 * function defined for the database will be used to ignore 115 * differences in case. 116 * @param db Represents the database in use for vendor-specific functions. 117 * @return A join expression, e.g. UPPER(table_a.column_a) = 118 * UPPER(table_b.column_b). 119 */ 120 public static String buildInnerJoin(String column, 121 String relatedColumn, 122 boolean ignoreCase, 123 DB db) 124 { 125 int addlength = (ignoreCase) ? 25 : 1; 126 StringBuffer sb = new StringBuffer(column.length() 127 + relatedColumn.length() + addlength); 128 buildInnerJoin(column, relatedColumn, ignoreCase, db, sb); 129 return sb.toString(); 130 } 131 132 /*** 133 * Used to specify a join on two columns. 134 * 135 * @param column A column in one of the tables to be joined. 136 * @param relatedColumn The column in the other table to be joined. 137 * @param ignoreCase If true and columns represent Strings, the appropriate 138 * function defined for the database will be used to ignore 139 * differences in case. 140 * @param db Represents the database in use for vendor-specific functions. 141 * @param whereClause A StringBuffer to which the sql expression will be 142 * appended. 143 */ 144 public static void buildInnerJoin(String column, 145 String relatedColumn, 146 boolean ignoreCase, 147 DB db, 148 StringBuffer whereClause) 149 { 150 if (ignoreCase) 151 { 152 whereClause.append(db.ignoreCase(column)) 153 .append('=') 154 .append(db.ignoreCase(relatedColumn)); 155 } 156 else 157 { 158 whereClause.append(column) 159 .append('=') 160 .append(relatedColumn); 161 } 162 } 163 164 165 /*** 166 * Builds a simple SQL expression. 167 * 168 * @param columnName A column. 169 * @param criteria The value to compare the column against. 170 * @param comparison One of =, <, >, ^lt;=, >=, <>, 171 * !=, LIKE, etc. 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 throws TorqueException 181 { 182 // 'db' can be null because 'ignoreCase' is null 183 return build(columnName, criteria, comparison, false, null); 184 } 185 186 /*** 187 * Builds a simple SQL expression. 188 * 189 * @param columnName A column. 190 * @param criteria The value to compare the column against. 191 * @param comparison One of =, <, >, ^lt;=, >=, <>, 192 * !=, LIKE, etc. 193 * @param ignoreCase If true and columns represent Strings, the appropriate 194 * function defined for the database will be used to ignore 195 * differences in case. 196 * @param db Represents the database in use, for vendor specific functions. 197 * @return A simple sql expression, e.g. UPPER(table_a.column_a) 198 * LIKE UPPER('ab%c'). 199 * @throws TorqueException Any exceptions caught during processing will be 200 * rethrown wrapped into a TorqueException. 201 */ 202 public static String build(String columnName, 203 Object criteria, 204 SqlEnum comparison, 205 boolean ignoreCase, 206 DB db) 207 throws TorqueException 208 { 209 int addlength = (ignoreCase ? 40 : 20); 210 StringBuffer sb = new StringBuffer(columnName.length() + addlength); 211 build(columnName, criteria, comparison, ignoreCase, db, sb); 212 return sb.toString(); 213 } 214 215 /*** 216 * Builds a simple SQL expression. 217 * 218 * @param columnName A column. 219 * @param criteria The value to compare the column against. 220 * @param comparison One of =, <, >, ^lt;=, >=, <>, 221 * !=, LIKE, etc. 222 * @param ignoreCase If true and columns represent Strings, the appropriate 223 * function defined for the database will be used to ignore 224 * differences in case. 225 * @param db Represents the database in use, for vendor specific functions. 226 * @param whereClause A StringBuffer to which the sql expression will be 227 * appended. 228 */ 229 public static void build(String columnName, 230 Object criteria, 231 SqlEnum comparison, 232 boolean ignoreCase, 233 DB db, 234 StringBuffer whereClause) 235 { 236 // Allow null criteria 237 // This will result in queries like 238 // insert into table (name, parent) values ('x', null); 239 // 240 241 /* Check to see if the criteria is an ObjectKey 242 * and if the value of that ObjectKey is null. 243 * In that case, criteria should be null. 244 */ 245 246 if (criteria != null && criteria instanceof ObjectKey) 247 { 248 if (((ObjectKey) criteria).getValue() == null) 249 { 250 criteria = null; 251 } 252 } 253 /* If the criteria is null, check to see comparison 254 * is an =, <>, or !=. If so, replace the comparison 255 * with the proper IS or IS NOT. 256 */ 257 258 if (criteria == null) 259 { 260 criteria = "null"; 261 if (comparison.equals(Criteria.EQUAL)) 262 { 263 comparison = Criteria.ISNULL; 264 } 265 else if (comparison.equals(Criteria.NOT_EQUAL)) 266 { 267 comparison = Criteria.ISNOTNULL; 268 } 269 else if (comparison.equals(Criteria.ALT_NOT_EQUAL)) 270 { 271 comparison = Criteria.ISNOTNULL; 272 } 273 } 274 else 275 { 276 if (criteria instanceof String || criteria instanceof StringKey) 277 { 278 criteria = quoteAndEscapeText(criteria.toString(), db); 279 } 280 else if (criteria instanceof Date) 281 { 282 Date dt = (Date) criteria; 283 criteria = db.getDateString(dt); 284 } 285 else if (criteria instanceof DateKey) 286 { 287 Date dt = (Date) ((DateKey) criteria).getValue(); 288 criteria = db.getDateString(dt); 289 } 290 else if (criteria instanceof Boolean) 291 { 292 criteria = db.getBooleanString((Boolean) criteria); 293 } 294 } 295 296 if (comparison.equals(Criteria.LIKE) 297 || comparison.equals(Criteria.NOT_LIKE)) 298 { 299 buildLike(columnName, (String) criteria, comparison, 300 ignoreCase, db, whereClause); 301 } 302 else if (comparison.equals(Criteria.IN) 303 || comparison.equals(Criteria.NOT_IN)) 304 { 305 buildIn(columnName, criteria, comparison, 306 ignoreCase, db, whereClause); 307 } 308 else 309 { 310 // Do not put the upper/lower keyword around IS NULL 311 // or IS NOT NULL 312 if (comparison.equals(Criteria.ISNULL) 313 || comparison.equals(Criteria.ISNOTNULL)) 314 { 315 whereClause.append(columnName) 316 .append(comparison); 317 } 318 else 319 { 320 String columnValue = criteria.toString(); 321 if (ignoreCase && db != null) 322 { 323 columnName = db.ignoreCase(columnName); 324 columnValue = db.ignoreCase(columnValue); 325 } 326 whereClause.append(columnName) 327 .append(comparison) 328 .append(columnValue); 329 } 330 } 331 } 332 333 /*** 334 * Takes a columnName and criteria and builds an SQL phrase based 335 * on whether wildcards are present and the state of the 336 * ignoreCase flag. Multicharacter wildcards % and * may be used 337 * as well as single character wildcards, _ and ?. These 338 * characters can be escaped with \. 339 * 340 * e.g. criteria = "fre%" -> columnName LIKE 'fre%' 341 * -> UPPER(columnName) LIKE UPPER('fre%') 342 * criteria = "50\%" -> columnName = '50%' 343 * 344 * @param columnName A column. 345 * @param criteria The value to compare the column against. 346 * @param comparison Whether to do a LIKE or a NOT LIKE 347 * @param ignoreCase If true and columns represent Strings, the 348 * appropriate function defined for the database will be used to 349 * ignore differences in case. 350 * @param db Represents the database in use, for vendor specific functions. 351 * @return An SQL expression. 352 */ 353 static String buildLike(String columnName, 354 String criteria, 355 SqlEnum comparison, 356 boolean ignoreCase, 357 DB db) 358 { 359 StringBuffer whereClause = new StringBuffer(); 360 buildLike(columnName, criteria, comparison, ignoreCase, db, 361 whereClause); 362 return whereClause.toString(); 363 } 364 365 /*** 366 * Takes a columnName and criteria and builds an SQL phrase based 367 * on whether wildcards are present and the state of the 368 * ignoreCase flag. Multicharacter wildcards % and * may be used 369 * as well as single character wildcards, _ and ?. These 370 * characters can be escaped with \. 371 * 372 * e.g. criteria = "fre%" -> columnName LIKE 'fre%' 373 * -> UPPER(columnName) LIKE UPPER('fre%') 374 * criteria = "50\%" -> columnName = '50%' 375 * 376 * @param columnName A column name. 377 * @param criteria The value to compare the column against. 378 * @param comparison Whether to do a LIKE or a NOT LIKE 379 * @param ignoreCase If true and columns represent Strings, the 380 * appropriate function defined for the database will be used to 381 * ignore differences in case. 382 * @param db Represents the database in use, for vendor specific functions. 383 * @param whereClause A StringBuffer to which the sql expression 384 * will be appended. 385 */ 386 static void buildLike(String columnName, 387 String criteria, 388 SqlEnum comparison, 389 boolean ignoreCase, 390 DB db, 391 StringBuffer whereClause) 392 { 393 // If selection is case insensitive use SQL UPPER() function 394 // on column name. 395 if (ignoreCase) 396 { 397 columnName = db.ignoreCase(columnName); 398 } 399 whereClause.append(columnName); 400 401 // If selection criteria contains wildcards use LIKE otherwise 402 // use = (equals). Wildcards can be escaped by prepending 403 // them with \ (backslash). 404 String equalsOrLike = " = "; 405 int position = 0; 406 StringBuffer sb = new StringBuffer(); 407 while (position < criteria.length()) 408 { 409 char checkWildcard = criteria.charAt(position); 410 411 switch (checkWildcard) 412 { 413 case BACKSLASH: 414 // Determine whether to skip over next character. 415 switch (criteria.charAt(position + 1)) 416 { 417 case '%': 418 case '_': 419 case '*': 420 case '?': 421 case BACKSLASH: 422 position++; 423 break; 424 } 425 break; 426 case '%': 427 case '_': 428 equalsOrLike = comparison.toString(); 429 break; 430 case '*': 431 equalsOrLike = comparison.toString(); 432 checkWildcard = '%'; 433 break; 434 case '?': 435 equalsOrLike = comparison.toString(); 436 checkWildcard = '_'; 437 break; 438 } 439 440 sb.append(checkWildcard); 441 position++; 442 } 443 whereClause.append(equalsOrLike); 444 445 // If selection is case insensitive use SQL UPPER() function 446 // on criteria. 447 String clauseItem = sb.toString(); 448 if (ignoreCase) 449 { 450 clauseItem = db.ignoreCase(clauseItem); 451 } 452 whereClause.append(clauseItem); 453 } 454 455 /*** 456 * Takes a columnName and criteria (which must be an array) and 457 * builds a SQL 'IN' expression taking into account the ignoreCase 458 * flag. 459 * 460 * @param columnName A column. 461 * @param criteria The value to compare the column against. 462 * @param comparison Either " IN " or " NOT IN ". 463 * @param ignoreCase If true and columns represent Strings, the 464 * appropriate function defined for the database will be used to 465 * ignore differences in case. 466 * @param db Represents the database in use, for vendor specific functions. 467 * @return An SQL expression. 468 */ 469 static String buildIn(String columnName, 470 Object criteria, 471 SqlEnum comparison, 472 boolean ignoreCase, 473 DB db) 474 { 475 StringBuffer whereClause = new StringBuffer(); 476 buildIn(columnName, criteria, comparison, 477 ignoreCase, db, whereClause); 478 return whereClause.toString(); 479 } 480 481 /*** 482 * Takes a columnName and criteria (which must be an array) and 483 * builds a SQL 'IN' expression taking into account the ignoreCase 484 * flag. 485 * 486 * @param columnName A column. 487 * @param criteria The value to compare the column against. 488 * @param comparison Either " IN " or " NOT IN ". 489 * @param ignoreCase If true and columns represent Strings, the 490 * appropriate function defined for the database will be used to 491 * ignore differences in case. 492 * @param db Represents the database in use, for vendor specific functions. 493 * @param whereClause A StringBuffer to which the sql expression 494 * will be appended. 495 */ 496 static void buildIn(String columnName, 497 Object criteria, 498 SqlEnum comparison, 499 boolean ignoreCase, 500 DB db, 501 StringBuffer whereClause) 502 { 503 if (ignoreCase) 504 { 505 whereClause.append(db.ignoreCase(columnName)); 506 } 507 else 508 { 509 whereClause.append(columnName); 510 } 511 512 whereClause.append(comparison); 513 HashSet inClause = new HashSet(); 514 if (criteria instanceof List) 515 { 516 Iterator iter = ((List) criteria).iterator(); 517 while (iter.hasNext()) 518 { 519 Object value = iter.next(); 520 521 // The method processInValue() quotes the string 522 // and/or wraps it in UPPER(). 523 inClause.add(processInValue(value, ignoreCase, db)); 524 } 525 } 526 else 527 { 528 // Assume array. 529 for (int i = 0; i < Array.getLength(criteria); i++) 530 { 531 Object value = Array.get(criteria, i); 532 533 // The method processInValue() quotes the string 534 // and/or wraps it in UPPER(). 535 inClause.add(processInValue(value, ignoreCase, db)); 536 } 537 } 538 whereClause.append('(') 539 .append(StringUtils.join(inClause.iterator(), ",")) 540 .append(')'); 541 } 542 543 /*** 544 * Creates an appropriate string for an 'IN' clause from an 545 * object. Adds quoting and/or UPPER() as appropriate. This is 546 * broken out into a seperate method as it is used in two places 547 * in buildIn, depending on whether an array or List is being 548 * looped over. 549 * 550 * @param value The value to process. 551 * @param ignoreCase Coerce the value suitably for ignoring case. 552 * @param db Represents the database in use for vendor specific functions. 553 * @return Processed value as String. 554 */ 555 static String processInValue(Object value, 556 boolean ignoreCase, 557 DB db) 558 { 559 String ret = null; 560 if (value instanceof String) 561 { 562 ret = quoteAndEscapeText((String) value, db); 563 } 564 else 565 { 566 ret = value.toString(); 567 } 568 if (ignoreCase) 569 { 570 ret = db.ignoreCase(ret); 571 } 572 return ret; 573 } 574 575 /*** 576 * Quotes and escapes raw text for placement in a SQL expression. 577 * For simplicity, the text is assumed to be neither quoted nor 578 * escaped. 579 * 580 * @param rawText The <i>unquoted</i>, <i>unescaped</i> text to process. 581 * @param db the db 582 * @return Quoted and escaped text. 583 */ 584 public static String quoteAndEscapeText(String rawText, DB db) 585 { 586 StringBuffer buf = new StringBuffer((int) (rawText.length() * 1.1)); 587 588 // Some databases do not need escaping. 589 String escapeString = new String(); 590 if (db != null && !db.escapeText()) 591 { 592 escapeString = String.valueOf(BACKSLASH); 593 } 594 else 595 { 596 escapeString = String.valueOf(BACKSLASH) 597 + String.valueOf(BACKSLASH); 598 } 599 600 char[] data = rawText.toCharArray(); 601 buf.append(SINGLE_QUOTE); 602 for (int i = 0; i < data.length; i++) 603 { 604 switch (data[i]) 605 { 606 case SINGLE_QUOTE: 607 buf.append(SINGLE_QUOTE).append(SINGLE_QUOTE); 608 break; 609 case BACKSLASH: 610 buf.append(escapeString); 611 break; 612 default: 613 buf.append(data[i]); 614 } 615 } 616 buf.append(SINGLE_QUOTE); 617 618 return buf.toString(); 619 } 620 }

This page was automatically generated by Maven