001: /*
002: * Licensed to the Apache Software Foundation (ASF) under one or more
003: * contributor license agreements. See the NOTICE file distributed with
004: * this work for additional information regarding copyright ownership.
005: * The ASF licenses this file to You under the Apache License, Version 2.0
006: * (the "License"); you may not use this file except in compliance with
007: * the License. You may obtain a copy of the License at
008: *
009: * http://www.apache.org/licenses/LICENSE-2.0
010: *
011: * Unless required by applicable law or agreed to in writing, software
012: * distributed under the License is distributed on an "AS IS" BASIS,
013: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: * See the License for the specific language governing permissions and
015: * limitations under the License.
016: *
017: * $Header:$
018: */
019: package org.apache.beehive.netui.databinding.datagrid.runtime.sql;
020:
021: import org.apache.beehive.netui.util.internal.InternalStringBuilder;
022:
023: import java.util.Map;
024: import java.util.LinkedHashMap;
025: import java.util.List;
026: import java.sql.DatabaseMetaData;
027: import java.sql.SQLException;
028:
029: import org.apache.beehive.netui.databinding.datagrid.api.filter.FilterOperation;
030: import org.apache.beehive.netui.databinding.datagrid.api.filter.FilterOperationHint;
031: import org.apache.beehive.netui.databinding.datagrid.api.filter.FilterTypeHint;
032: import org.apache.beehive.netui.databinding.datagrid.api.filter.Filter;
033: import org.apache.beehive.netui.databinding.datagrid.api.sort.SortDirection;
034: import org.apache.beehive.netui.databinding.datagrid.api.sort.Sort;
035: import org.apache.beehive.netui.databinding.datagrid.api.exceptions.IllegalFilterException;
036: import org.apache.beehive.netui.util.Bundle;
037: import org.apache.beehive.netui.util.logging.Logger;
038:
039: /**
040: * <p>
041: * This class provides support for the SQL query language for a data grid's {@link Sort} and {@link Filter} JavaBeans.
042: * Support is provided here for obtaining a list of supported SQL filter operations. There is also support for
043: * generating two kinds of SQL statements:
044: * <ul>
045: * <li>ORDER BY clause given a {@link List} of {@link Sort} beans</li>
046: * <li>WHERE clause given a {@link List} of {@link Filter} beans</li>
047: * </ul>
048: * </p>
049: */
050: public final class SQLSupport {
051:
052: private static final Logger LOGGER = Logger
053: .getInstance(SQLSupport.class);
054: private static final String EMPTY = "";
055: private static final FilterOperation[] FILTER_OPERATIONS;
056: private static final SQLSupportConfig DEFAULT_SQL_SUPPORT_CONFIG = SQLSupportConfigFactory
057: .getInstance();
058:
059: static {
060: FILTER_OPERATIONS = new FilterOperation[] {
061: new FilterOperation(0, "*", "filter.sql.none",
062: FilterOperationHint.NONE),
063: new FilterOperation(1, "eq", "filter.sql.equal",
064: FilterOperationHint.EQUAL),
065: new FilterOperation(2, "ne", "filter.sql.notequal",
066: FilterOperationHint.NOT_EQUAL),
067: new FilterOperation(3, "gt", "filter.sql.greaterthan",
068: FilterOperationHint.GREATER_THAN),
069: new FilterOperation(4, "lt", "filter.sql.lessthan",
070: FilterOperationHint.LESS_THAN),
071: new FilterOperation(5, "ge",
072: "filter.sql.greaterthanorequal",
073: FilterOperationHint.GREATER_THAN_OR_EQUAL),
074: new FilterOperation(6, "le",
075: "filter.sql.lessthanorequal",
076: FilterOperationHint.LESS_THAN_OR_EQUAL),
077: new FilterOperation(7, "in", "filter.sql.isoneof",
078: FilterOperationHint.IS_ONE_OF),
079: new FilterOperation(8, "startswith",
080: "filter.sql.startswith",
081: FilterOperationHint.STARTS_WITH),
082: new FilterOperation(9, "contains",
083: "filter.sql.contains",
084: FilterOperationHint.CONTAINS),
085: new FilterOperation(10, "isempty",
086: "filter.sql.isempty",
087: FilterOperationHint.IS_EMPTY),
088: new FilterOperation(11, "isnotempty",
089: "filter.sql.isnotempty",
090: FilterOperationHint.IS_NOT_EMPTY) };
091: }
092:
093: private static final FilterOperation DEFAULT_STRING_FILTER_OPERATION = FILTER_OPERATIONS[9];
094: private static final FilterOperation DEFAULT_NONSTRING_FILTER_OPERATION = FILTER_OPERATIONS[1];
095:
096: /**
097: * Get an instance of this class configured using a default {@link SQLSupportConfig}.
098: *
099: * @return a SQLSupport instance
100: */
101: public static SQLSupport getInstance() {
102: return getInstance(DEFAULT_SQL_SUPPORT_CONFIG);
103: }
104:
105: /**
106: * Get an instance of this class configured using a {@link SQLSupportConfig} that has been configured using
107: * the provided {@link DatabaseMetaData}.
108: * @param databaseMetaData the database metadata used to configure a {@link SQLSupportConfig} object
109: * @return a SQLSupport instance
110: * @throws SQLException when an error occurs reading from {@link DatabaseMetaData}
111: */
112: public static SQLSupport getInstance(
113: DatabaseMetaData databaseMetaData) throws SQLException {
114: SQLSupportConfig config = SQLSupportConfigFactory
115: .getInstance(databaseMetaData);
116: return getInstance(config);
117: }
118:
119: /**
120: * Get an instance of this class configured using the provided {@link SQLSupportConfig}. The caller
121: * should create and appropriately initialize the config object.
122: * @param config the config object use to configure a SQLSupport instance
123: * @return a SQLSupport instance
124: */
125: public static SQLSupport getInstance(SQLSupportConfig config) {
126: SQLSupport sqlSupport = new SQLSupport();
127: sqlSupport.configure(config);
128: return sqlSupport;
129: }
130:
131: /**
132: * Get the readable string labels for a filter operation. This {@link Map} contains a set of
133: * filter operation abbreviations mapped to a label for that filter operation. The abbreviations
134: * can be used to lookup the correct filter operation. This method accepts values enumerated
135: * in {@link FilterTypeHint} and available via
136: * {@link org.apache.beehive.netui.databinding.datagrid.api.filter.FilterTypeHint#getHint()}.
137: *
138: * @param typeHint the type hint whose matching operations to lookup
139: * @return Map a {@link Map} of String abbreviations to readable string names for the operation
140: */
141: public static Map lookupFilterOperationLabels(String typeHint) {
142: LinkedHashMap ops = new LinkedHashMap/*<String, String>*/();
143:
144: /* todo: i18n */
145: /* todo: caching of the filterOps for a given type hint */
146: ops.put(FILTER_OPERATIONS[0].getAbbreviation(), "No Filter");
147: ops.put(FILTER_OPERATIONS[1].getAbbreviation(), "Equals");
148: ops.put(FILTER_OPERATIONS[2].getAbbreviation(), "Not Equal");
149: ops.put(FILTER_OPERATIONS[3].getAbbreviation(), "Greater Than");
150: ops.put(FILTER_OPERATIONS[4].getAbbreviation(), "Less Than");
151: ops.put(FILTER_OPERATIONS[5].getAbbreviation(),
152: "Is Greater Than or Equal To");
153: ops.put(FILTER_OPERATIONS[6].getAbbreviation(),
154: "Is Less Than or Equal To");
155:
156: if (!(FilterTypeHint.DATE.equals(FilterTypeHint
157: .getTypeHint(typeHint)))) {
158: ops.put(FILTER_OPERATIONS[7].getAbbreviation(),
159: "Is One Of (eg: 1;2;3)");
160: }
161:
162: if (typeHint == null
163: || FilterTypeHint.STRING.equals(FilterTypeHint
164: .getTypeHint(typeHint))) {
165: ops.put(FILTER_OPERATIONS[8].getAbbreviation(),
166: "Starts With");
167: ops.put(FILTER_OPERATIONS[9].getAbbreviation(), "Contains");
168: ops
169: .put(FILTER_OPERATIONS[10].getAbbreviation(),
170: "Is Empty");
171: ops.put(FILTER_OPERATIONS[11].getAbbreviation(),
172: "Is Not Empty");
173: }
174:
175: return ops;
176: }
177:
178: /* todo: method returning filter labels given a FitlerTypeHint */
179: /* todo: method returning the FILTER_OPERATIONS */
180: /* todo: method returning FILTER_OPERATIONS given a FitlerTypeHint */
181:
182: /**
183: * <p>
184: * Lookup the default filter operation's abbreviation given a filter type hint abbreviation. The type hint
185: * should be obtained via {@link org.apache.beehive.netui.databinding.datagrid.api.filter.FilterTypeHint#getHint()}.
186: * The default filter operations for a given FilterTypeHint string maps to the following FilterOperationHint.
187: * The string returned is the associated FilterOperation's abbreviation
188: * <table>
189: * <tr><td>Type Hint</td><td></td></tr>
190: * <tr><td>{@link FilterTypeHint#DATE}</td><td>{@link FilterOperationHint#EQUAL}</td></tr>
191: * <tr><td>{@link FilterTypeHint#NUMERIC}</td><td>{@link FilterOperationHint#EQUAL}</td></tr>
192: * <tr><td>{@link FilterTypeHint#STRING}</td><td>{@link FilterOperationHint#CONTAINS}</td></tr>
193: * </table>
194: * </p>
195: * @param typeHint the type hint whose default operation to lookup
196: * @return String the abbreviation
197: */
198: public static String lookoupDefaultFilterOperationAbbreviation(
199: String typeHint) {
200: FilterOperation fOp = DEFAULT_NONSTRING_FILTER_OPERATION;
201: if (typeHint == null
202: || FilterTypeHint.STRING.equals(FilterTypeHint
203: .getTypeHint(typeHint)))
204: fOp = DEFAULT_STRING_FILTER_OPERATION;
205:
206: return fOp.getAbbreviation();
207: }
208:
209: /**
210: * Lookup a filter operation given a filter operation abbreviation. The abbreviation should be obtained
211: * via {@link org.apache.beehive.netui.databinding.datagrid.api.filter.FilterOperation#getAbbreviation()}.
212: * @param abbrev
213: * @return the filter operation
214: */
215: public static final FilterOperation mapFilterAbbreviationToOperation(
216: String abbrev) {
217: for (int i = 0; i < FILTER_OPERATIONS.length; i++) {
218: FilterOperation fOp = FILTER_OPERATIONS[i];
219: if (fOp.getAbbreviation().equals(abbrev))
220: return fOp;
221: }
222: return null;
223: }
224:
225: /**
226: * Map a {@link FilterOperationHint} to a SQL-specific {@link FilterOperation}. When using SQL as a query
227: * language, all of the operations defined in {@link FilterOperationHint} should be supported.
228: *
229: * @param hint the hint
230: * @return the {@link FilterOperation} matching the given hint.
231: */
232: public static final FilterOperation mapFilterHintToOperation(
233: FilterOperationHint hint) {
234: for (int i = 0; i < FILTER_OPERATIONS.length; i++) {
235: FilterOperation op = FILTER_OPERATIONS[i];
236: if (op.getOperationHint().equals(hint))
237: return op;
238: }
239: return null;
240: }
241:
242: private SQLSupportConfig _config = null;
243:
244: /**
245: * Private constructor. All access to this should be done through the static factory methods
246: * on the class.
247: */
248: private SQLSupport() {
249: }
250:
251: /**
252: * Set the {@link SQLSupportConfig} object useed to configure the SQL statements produced by this class.
253: * @param config the config object
254: */
255: public void configure(SQLSupportConfig config) {
256: _config = config;
257: }
258:
259: /**
260: * <p>
261: * Create a SQL order fragment from the list of {@link Sort} objects. This fragment does not begin with
262: * ORDER BY and is just the <i>fragment</i> for such a clause. If the given list of
263: * sorts contains a sort with sort expression "foo" and sort direction {@link SortDirection#DESCENDING},
264: * the generated SQL statement will appear as:
265: * <pre>
266: * foo DESC
267: * </pre>
268: * </p>
269: * @param sorts the list of {@link Sort} objects
270: * @return the generated SQL statement order fragment or an emtpy string if there are no sorts
271: */
272: public final String createOrderByFragment(List/*<Sort>*/sorts) {
273: if (sorts == null || sorts.size() == 0)
274: return EMPTY;
275:
276: InternalStringBuilder sql = new InternalStringBuilder();
277: internalCreateOrderByFragment(sql, sorts);
278: return sql.toString();
279: }
280:
281: /**
282: * <p>
283: * Create a SQL ORDER BY clause from the list of {@link Sort} objects. This fragment begins with
284: * ORDER BY. If the given list of sorts contains a sort with sort expression "foo" and sort direction
285: * {@link SortDirection#DESCENDING}, the generated SQL statement will appear as:
286: * <pre>
287: * ORDER BY foo DESC
288: * </pre>
289: * </p>
290: * @param sorts the list of {@link Sort} objects
291: * @return the generated SQL ORDER BY clause or an emtpy string if there are no sorts
292: */
293: public final String createOrderByClause(List/*<Sort>*/sorts) {
294: if (sorts == null || sorts.size() == 0)
295: return EMPTY;
296:
297: InternalStringBuilder sql = new InternalStringBuilder(64);
298: sql.append("ORDER BY ");
299: internalCreateOrderByFragment(sql, sorts);
300: return sql.toString();
301: }
302:
303: /**
304: * <p>
305: * Create a SQL WHERE clause from the list of {@link Filter} objects. This fragment does not begin with
306: * WHERE. If the given list of sorts contains a Filter with filter expression "foo", operation equals,
307: * and value '42', the generated SQL statement will appear as:
308: * <pre>
309: * foo = 42
310: * </pre>
311: * When multiple Filters in the list, the filters will be AND'ed together in the generated SQL statement.
312: * </p>
313: * @param filters the list of {@link Filter} objects
314: * @return the generated SQL where clause fragment or an emtpy string if there are no filters
315: */
316: public String createWhereFragment(List/*<Filter>*/filters) {
317: if (filters == null || filters.size() == 0)
318: return EMPTY;
319:
320: InternalStringBuilder sql = new InternalStringBuilder(64);
321: internalCreateWhereFragment(sql, filters);
322: return sql.toString();
323: }
324:
325: /**
326: * <p>
327: * Create a SQL WHERE clause from the list of {@link Filter} objects. This fragment begins with
328: * WHERE. If the given list of sorts contains a Filter with filter expression "foo", operation equals,
329: * and value '42', the generated SQL statement will appear as:
330: * <pre>
331: * WHERE foo = 42
332: * </pre>
333: * When multiple Filters in the list, the filters will be AND'ed together in the generated SQL statement.
334: * </p>
335: * @param filters the list of {@link Filter} objects
336: * @return the generated SQL WHERE clause or an emtpy string if there are no filters
337: */
338: public String createWhereClause(List/*<Filter>*/filters) {
339: if (filters == null || filters.size() == 0)
340: return EMPTY;
341:
342: InternalStringBuilder sql = new InternalStringBuilder();
343: sql.append("WHERE ");
344: internalCreateWhereFragment(sql, filters);
345: return sql.toString();
346: }
347:
348: /**
349: *
350: * @param sql
351: * @param sorts
352: */
353: private void internalCreateOrderByFragment(
354: InternalStringBuilder sql, List/*<Sort>*/sorts) {
355: for (int i = 0; i < sorts.size(); i++) {
356: Sort sort = (Sort) sorts.get(i);
357: if (i > 0)
358: sql.append(", ");
359: sql.append(sort.getSortExpression());
360: if (sort.getDirection() == SortDirection.DESCENDING)
361: sql.append(" DESC");
362: }
363: }
364:
365: /**
366: *
367: * @param sql
368: * @param filters
369: */
370: private void internalCreateWhereFragment(InternalStringBuilder sql,
371: List/*<Filter>*/filters) {
372:
373: for (int i = 0; i < filters.size(); i++) {
374: Filter filter = (Filter) filters.get(i);
375:
376: if (filter == null)
377: continue;
378:
379: FilterOperation fOp = filter.getOperation();
380: FilterOperationHint fOpHint = null;
381: String fExpr = filter.getFilterExpression();
382: if (fOp == null && filter.getOperationHint() != null) {
383: fOpHint = filter.getOperationHint();
384: fOp = mapFilterHintToOperation(fOpHint);
385: } else {
386: fOpHint = filter.getOperation().getOperationHint();
387: }
388:
389: if (fOp == null) {
390: String message = Bundle.getErrorString(
391: "DataGridFilter_NoFilterOperation",
392: new Object[] { filter.getFilterExpression() });
393: LOGGER.error(message);
394: throw new IllegalFilterException(message);
395: }
396:
397: /* todo: feature. pluggable conjunctions AND and OR here */
398: if (i > 0)
399: sql.append(" AND ");
400:
401: if (filter.getValue() == null) {
402: if (fOpHint == FilterOperationHint.EQUAL) {
403: sql.append("(");
404: sql.append(fExpr);
405: sql.append(" IS NULL)");
406: } else if (fOpHint == FilterOperationHint.NOT_EQUAL) {
407: sql.append("(");
408: sql.append(fExpr);
409: sql.append(" IS NOT NULL)");
410: }
411: }
412:
413: switch (fOpHint.getValue()) {
414: case FilterOperationHint.INT_STARTS_WITH:
415: case FilterOperationHint.INT_CONTAINS: {
416: boolean bEscape = _config.getSupportsLikeEscapeClause();
417: String strValue = bEscape ? convertSQLPattern(filter
418: .getValue()) : filter.getValue().toString();
419: strValue = convertSQLString(strValue);
420: sql.append("(").append(fExpr).append(" LIKE '");
421: if (fOpHint == FilterOperationHint.CONTAINS)
422: sql.append("%");
423: sql.append(strValue).append("%'");
424: if (bEscape)
425: sql.append(" ESCAPE '\\'");
426: sql.append(')');
427: break;
428: }
429: case FilterOperationHint.INT_IS_NOT_EMPTY: {
430: sql.append("(").append(fExpr).append(" IS NOT NULL)");
431: break;
432: }
433: case FilterOperationHint.INT_IS_EMPTY: {
434: sql.append("(").append(fExpr).append(" IS NULL)");
435: break;
436: }
437: case FilterOperationHint.INT_EQUAL:
438: case FilterOperationHint.INT_LESS_THAN:
439: case FilterOperationHint.INT_LESS_THAN_OR_EQUAL:
440: case FilterOperationHint.INT_GREATER_THAN:
441: case FilterOperationHint.INT_GREATER_THAN_OR_EQUAL:
442: case FilterOperationHint.INT_NOT_EQUAL: {
443: /* todo: conider using SQLFragment from the DatabaseControl here. */
444: sql.append("(");
445: sql.append(fExpr);
446: sql.append(lookupOperator(fOpHint));
447: addParameter(sql, filter.getValue(), filter
448: .getTypeHint());
449: if (fOpHint == FilterOperationHint.NOT_EQUAL) {
450: sql.append(" OR ");
451: sql.append(fExpr);
452: sql.append(" IS NULL");
453: }
454: sql.append(")");
455: break;
456: }
457: case FilterOperationHint.INT_IS_ONE_OF: {
458: Object[] arr;
459: if (filter.getValue().getClass().isArray())
460: arr = (Object[]) filter.getValue();
461: else
462: arr = new Object[] { filter.getValue() };
463:
464: if (arr.length == 0)
465: break;
466:
467: sql.append("(");
468: sql.append(fExpr);
469: sql.append(" IN (");
470: String comma = "";
471: for (int j = 0; j < arr.length; j++) {
472: sql.append(comma);
473: /* todo: date handling. probably some type normalization required here */
474: addParameter(sql, arr[i], filter.getTypeHint());
475: comma = ",";
476: }
477: sql.append("))");
478: break;
479: }
480: default:
481: throw new IllegalFilterException(Bundle.getErrorString(
482: "DataGridFilter_UnknownFilterOperation",
483: new Object[] { fOp }));
484: }
485: }
486: }
487:
488: /**
489: *
490: * @param o
491: * @return
492: */
493: private String convertSQLPattern(Object o) {
494: if (o == null)
495: return EMPTY;
496: else {
497: String s = o.toString();
498: s = s.replaceAll("\\\\", "\\\\\\\\");
499: s = s.replaceAll("%", "\\\\%");
500: s = s.replaceAll("_", "\\\\_");
501: return s;
502: }
503: }
504:
505: /**
506: *
507: * @param o
508: * @return
509: */
510: private String convertSQLString(Object o) {
511: if (o == null)
512: return EMPTY;
513: else
514: return (o.toString()).replaceAll("'", "''");
515: }
516:
517: /**
518: *
519: * @param sql
520: * @param value
521: * @param typeHint
522: */
523: private void addParameter(InternalStringBuilder sql, Object value,
524: FilterTypeHint typeHint) {
525: if (typeHint == FilterTypeHint.STRING)
526: sql.append(_config.getQuoteChar()).append(value).append(
527: _config.getQuoteChar());
528: else
529: sql.append(value);
530: }
531:
532: /**
533: *
534: * @param op
535: * @return
536: */
537: private String lookupOperator(FilterOperationHint op) {
538: switch (op.getValue()) {
539: case FilterOperationHint.INT_EQUAL:
540: return "=";
541: case FilterOperationHint.INT_NOT_EQUAL:
542: return "!=";
543: case FilterOperationHint.INT_GREATER_THAN:
544: return ">";
545: case FilterOperationHint.INT_LESS_THAN:
546: return "<";
547: case FilterOperationHint.INT_GREATER_THAN_OR_EQUAL:
548: return ">=";
549: case FilterOperationHint.INT_LESS_THAN_OR_EQUAL:
550: return "<=";
551: default:
552: assert false : "lookupOperation received an invalid FilterOperation: "
553: + op;
554: }
555: return null;
556: }
557: }
|