1 18 package org.apache.beehive.netui.databinding.datagrid.runtime.sql; 19 20 import org.apache.beehive.netui.util.internal.InternalStringBuilder; 21 22 import java.util.Map ; 23 import java.util.LinkedHashMap ; 24 import java.util.List ; 25 import java.sql.DatabaseMetaData ; 26 import java.sql.SQLException ; 27 28 import org.apache.beehive.netui.databinding.datagrid.api.filter.FilterOperation; 29 import org.apache.beehive.netui.databinding.datagrid.api.filter.FilterOperationHint; 30 import org.apache.beehive.netui.databinding.datagrid.api.filter.FilterTypeHint; 31 import org.apache.beehive.netui.databinding.datagrid.api.filter.Filter; 32 import org.apache.beehive.netui.databinding.datagrid.api.sort.SortDirection; 33 import org.apache.beehive.netui.databinding.datagrid.api.sort.Sort; 34 import org.apache.beehive.netui.databinding.datagrid.api.exceptions.IllegalFilterException; 35 import org.apache.beehive.netui.util.Bundle; 36 import org.apache.beehive.netui.util.logging.Logger; 37 38 49 public final class SQLSupport { 50 51 private static final Logger LOGGER = Logger.getInstance(SQLSupport.class); 52 private static final String EMPTY = ""; 53 private static final FilterOperation[] FILTER_OPERATIONS; 54 private static final SQLSupportConfig DEFAULT_SQL_SUPPORT_CONFIG = SQLSupportConfigFactory.getInstance(); 55 56 static { 57 FILTER_OPERATIONS = new FilterOperation[]{ 58 new FilterOperation(0, "*", "filter.sql.none", FilterOperationHint.NONE), 59 new FilterOperation(1, "eq", "filter.sql.equal", FilterOperationHint.EQUAL), 60 new FilterOperation(2, "ne", "filter.sql.notequal", FilterOperationHint.NOT_EQUAL), 61 new FilterOperation(3, "gt", "filter.sql.greaterthan", FilterOperationHint.GREATER_THAN), 62 new FilterOperation(4, "lt", "filter.sql.lessthan", FilterOperationHint.LESS_THAN), 63 new FilterOperation(5, "ge", "filter.sql.greaterthanorequal", FilterOperationHint.GREATER_THAN_OR_EQUAL), 64 new FilterOperation(6, "le", "filter.sql.lessthanorequal", FilterOperationHint.LESS_THAN_OR_EQUAL), 65 new FilterOperation(7, "in", "filter.sql.isoneof", FilterOperationHint.IS_ONE_OF), 66 new FilterOperation(8, "startswith", "filter.sql.startswith", FilterOperationHint.STARTS_WITH), 67 new FilterOperation(9, "contains", "filter.sql.contains", FilterOperationHint.CONTAINS), 68 new FilterOperation(10, "isempty", "filter.sql.isempty", FilterOperationHint.IS_EMPTY), 69 new FilterOperation(11, "isnotempty", "filter.sql.isnotempty", FilterOperationHint.IS_NOT_EMPTY) 70 }; 71 } 72 73 private static final FilterOperation DEFAULT_STRING_FILTER_OPERATION = FILTER_OPERATIONS[9]; 74 private static final FilterOperation DEFAULT_NONSTRING_FILTER_OPERATION = FILTER_OPERATIONS[1]; 75 76 81 public static SQLSupport getInstance() { 82 return getInstance(DEFAULT_SQL_SUPPORT_CONFIG); 83 } 84 85 92 public static SQLSupport getInstance(DatabaseMetaData databaseMetaData) 93 throws SQLException { 94 SQLSupportConfig config = SQLSupportConfigFactory.getInstance(databaseMetaData); 95 return getInstance(config); 96 } 97 98 104 public static SQLSupport getInstance(SQLSupportConfig config) { 105 SQLSupport sqlSupport = new SQLSupport(); 106 sqlSupport.configure(config); 107 return sqlSupport; 108 } 109 110 120 public static Map lookupFilterOperationLabels(String typeHint) { 121 LinkedHashMap ops = new LinkedHashMap (); 122 123 124 125 ops.put(FILTER_OPERATIONS[0].getAbbreviation(), "No Filter"); 126 ops.put(FILTER_OPERATIONS[1].getAbbreviation(), "Equals"); 127 ops.put(FILTER_OPERATIONS[2].getAbbreviation(), "Not Equal"); 128 ops.put(FILTER_OPERATIONS[3].getAbbreviation(), "Greater Than"); 129 ops.put(FILTER_OPERATIONS[4].getAbbreviation(), "Less Than"); 130 ops.put(FILTER_OPERATIONS[5].getAbbreviation(), "Is Greater Than or Equal To"); 131 ops.put(FILTER_OPERATIONS[6].getAbbreviation(), "Is Less Than or Equal To"); 132 133 if(!(FilterTypeHint.DATE.equals(FilterTypeHint.getTypeHint(typeHint)))) { 134 ops.put(FILTER_OPERATIONS[7].getAbbreviation(), "Is One Of (eg: 1;2;3)"); 135 } 136 137 if(typeHint == null || FilterTypeHint.STRING.equals(FilterTypeHint.getTypeHint(typeHint))) { 138 ops.put(FILTER_OPERATIONS[8].getAbbreviation(), "Starts With"); 139 ops.put(FILTER_OPERATIONS[9].getAbbreviation(), "Contains"); 140 ops.put(FILTER_OPERATIONS[10].getAbbreviation(), "Is Empty"); 141 ops.put(FILTER_OPERATIONS[11].getAbbreviation(), "Is Not Empty"); 142 } 143 144 return ops; 145 } 146 147 148 149 150 151 167 public static String lookoupDefaultFilterOperationAbbreviation(String typeHint) { 168 FilterOperation fOp = DEFAULT_NONSTRING_FILTER_OPERATION; 169 if(typeHint == null || FilterTypeHint.STRING.equals(FilterTypeHint.getTypeHint(typeHint))) 170 fOp = DEFAULT_STRING_FILTER_OPERATION; 171 172 return fOp.getAbbreviation(); 173 } 174 175 181 public static final FilterOperation mapFilterAbbreviationToOperation(String abbrev) { 182 for(int i = 0; i < FILTER_OPERATIONS.length; i++) { 183 FilterOperation fOp = FILTER_OPERATIONS[i]; 184 if(fOp.getAbbreviation().equals(abbrev)) 185 return fOp; 186 } 187 return null; 188 } 189 190 197 public static final FilterOperation mapFilterHintToOperation(FilterOperationHint hint) { 198 for(int i = 0; i < FILTER_OPERATIONS.length; i++) { 199 FilterOperation op = FILTER_OPERATIONS[i]; 200 if(op.getOperationHint().equals(hint)) 201 return op; 202 } 203 return null; 204 } 205 206 private SQLSupportConfig _config = null; 207 208 212 private SQLSupport() { 213 } 214 215 219 public void configure(SQLSupportConfig config) { 220 _config = config; 221 } 222 223 236 public final String createOrderByFragment(List sorts) { 237 if(sorts == null || sorts.size() == 0) 238 return EMPTY; 239 240 InternalStringBuilder sql = new InternalStringBuilder(); 241 internalCreateOrderByFragment(sql, sorts); 242 return sql.toString(); 243 } 244 245 257 public final String createOrderByClause(List sorts) { 258 if(sorts == null || sorts.size() == 0) 259 return EMPTY; 260 261 InternalStringBuilder sql = new InternalStringBuilder(64); 262 sql.append("ORDER BY "); 263 internalCreateOrderByFragment(sql, sorts); 264 return sql.toString(); 265 } 266 267 280 public String createWhereFragment(List filters) { 281 if(filters == null || filters.size() == 0) 282 return EMPTY; 283 284 InternalStringBuilder sql = new InternalStringBuilder(64); 285 internalCreateWhereFragment(sql, filters); 286 return sql.toString(); 287 } 288 289 302 public String createWhereClause(List filters) { 303 if(filters == null || filters.size() == 0) 304 return EMPTY; 305 306 InternalStringBuilder sql = new InternalStringBuilder(); 307 sql.append("WHERE "); 308 internalCreateWhereFragment(sql, filters); 309 return sql.toString(); 310 } 311 312 317 private void internalCreateOrderByFragment(InternalStringBuilder sql, List sorts) { 318 for(int i = 0; i < sorts.size(); i++) { 319 Sort sort = (Sort)sorts.get(i); 320 if(i > 0) 321 sql.append(", "); 322 sql.append(sort.getSortExpression()); 323 if(sort.getDirection() == SortDirection.DESCENDING) 324 sql.append(" DESC"); 325 } 326 } 327 328 333 private void internalCreateWhereFragment(InternalStringBuilder sql, List filters) { 334 335 for(int i = 0; i < filters.size(); i++) { 336 Filter filter = (Filter)filters.get(i); 337 338 if(filter == null) 339 continue; 340 341 FilterOperation fOp = filter.getOperation(); 342 FilterOperationHint fOpHint = null; 343 String fExpr = filter.getFilterExpression(); 344 if(fOp == null && filter.getOperationHint() != null) { 345 fOpHint = filter.getOperationHint(); 346 fOp = mapFilterHintToOperation(fOpHint); 347 } 348 else { 349 fOpHint = filter.getOperation().getOperationHint(); 350 } 351 352 if(fOp == null) { 353 String message = Bundle.getErrorString("DataGridFilter_NoFilterOperation", new Object []{filter.getFilterExpression()}); 354 LOGGER.error(message); 355 throw new IllegalFilterException(message); 356 } 357 358 359 if(i > 0) 360 sql.append(" AND "); 361 362 if(filter.getValue() == null) { 363 if(fOpHint == FilterOperationHint.EQUAL) { 364 sql.append("("); 365 sql.append(fExpr); 366 sql.append(" IS NULL)"); 367 } 368 else if(fOpHint == FilterOperationHint.NOT_EQUAL) { 369 sql.append("("); 370 sql.append(fExpr); 371 sql.append(" IS NOT NULL)"); 372 } 373 } 374 375 switch(fOpHint.getValue()) { 376 case FilterOperationHint.INT_STARTS_WITH: 377 case FilterOperationHint.INT_CONTAINS: 378 { 379 boolean bEscape = _config.getSupportsLikeEscapeClause(); 380 String strValue = bEscape ? convertSQLPattern(filter.getValue()) : filter.getValue().toString(); 381 strValue = convertSQLString(strValue); 382 sql.append("(").append(fExpr).append(" LIKE '"); 383 if(fOpHint == FilterOperationHint.CONTAINS) 384 sql.append("%"); 385 sql.append(strValue).append("%'"); 386 if(bEscape) 387 sql.append(" ESCAPE '\\'"); 388 sql.append(')'); 389 break; 390 } 391 case FilterOperationHint.INT_IS_NOT_EMPTY: 392 { 393 sql.append("(").append(fExpr).append(" IS NOT NULL)"); 394 break; 395 } 396 case FilterOperationHint.INT_IS_EMPTY: 397 { 398 sql.append("(").append(fExpr).append(" IS NULL)"); 399 break; 400 } 401 case FilterOperationHint.INT_EQUAL: 402 case FilterOperationHint.INT_LESS_THAN: 403 case FilterOperationHint.INT_LESS_THAN_OR_EQUAL: 404 case FilterOperationHint.INT_GREATER_THAN: 405 case FilterOperationHint.INT_GREATER_THAN_OR_EQUAL: 406 case FilterOperationHint.INT_NOT_EQUAL: 407 { 408 409 sql.append("("); 410 sql.append(fExpr); 411 sql.append(lookupOperator(fOpHint)); 412 addParameter(sql, filter.getValue(), filter.getTypeHint()); 413 if(fOpHint == FilterOperationHint.NOT_EQUAL) { 414 sql.append(" OR "); 415 sql.append(fExpr); 416 sql.append(" IS NULL"); 417 } 418 sql.append(")"); 419 break; 420 } 421 case FilterOperationHint.INT_IS_ONE_OF: 422 { 423 Object [] arr; 424 if(filter.getValue().getClass().isArray()) 425 arr = (Object [])filter.getValue(); 426 else 427 arr = new Object []{filter.getValue()}; 428 429 if(arr.length == 0) 430 break; 431 432 sql.append("("); 433 sql.append(fExpr); 434 sql.append(" IN ("); 435 String comma = ""; 436 for(int j = 0; j < arr.length; j++) { 437 sql.append(comma); 438 439 addParameter(sql, arr[i], filter.getTypeHint()); 440 comma = ","; 441 } 442 sql.append("))"); 443 break; 444 } 445 default: 446 throw new IllegalFilterException(Bundle.getErrorString("DataGridFilter_UnknownFilterOperation", new Object []{fOp})); 447 } 448 } 449 } 450 451 456 private String convertSQLPattern(Object o) { 457 if(o == null) 458 return EMPTY; 459 else { 460 String s = o.toString(); 461 s = s.replaceAll("\\\\", "\\\\\\\\"); 462 s = s.replaceAll("%", "\\\\%"); 463 s = s.replaceAll("_", "\\\\_"); 464 return s; 465 } 466 } 467 468 473 private String convertSQLString(Object o) { 474 if(o == null) 475 return EMPTY; 476 else 477 return (o.toString()).replaceAll("'", "''"); 478 } 479 480 486 private void addParameter(InternalStringBuilder sql, Object value, FilterTypeHint typeHint) { 487 if(typeHint == FilterTypeHint.STRING) 488 sql.append(_config.getQuoteChar()).append(value).append(_config.getQuoteChar()); 489 else 490 sql.append(value); 491 } 492 493 498 private String lookupOperator(FilterOperationHint op) { 499 switch(op.getValue()) { 500 case FilterOperationHint.INT_EQUAL: 501 return "="; 502 case FilterOperationHint.INT_NOT_EQUAL: 503 return "!="; 504 case FilterOperationHint.INT_GREATER_THAN: 505 return ">"; 506 case FilterOperationHint.INT_LESS_THAN: 507 return "<"; 508 case FilterOperationHint.INT_GREATER_THAN_OR_EQUAL: 509 return ">="; 510 case FilterOperationHint.INT_LESS_THAN_OR_EQUAL: 511 return "<="; 512 default: 513 assert false : "lookupOperation received an invalid FilterOperation: " + op; 514 } 515 return null; 516 } 517 } 518 | Popular Tags |