KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > beehive > netui > databinding > datagrid > runtime > sql > SQLSupport


1 /*
2  * Copyright 2004 The Apache Software Foundation.
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  * http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  *
16  * $Header:$
17  */

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 JavaDoc;
23 import java.util.LinkedHashMap JavaDoc;
24 import java.util.List JavaDoc;
25 import java.sql.DatabaseMetaData JavaDoc;
26 import java.sql.SQLException JavaDoc;
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 /**
39  * <p>
40  * This class provides support for the SQL query language for a data grid's {@link Sort} and {@link Filter} JavaBeans.
41  * Support is provided here for obtaining a list of supported SQL filter operations. There is also support for
42  * generating two kinds of SQL statements:
43  * <ul>
44  * <li>ORDER BY clause given a {@link List} of {@link Sort} beans</li>
45  * <li>WHERE clause given a {@link List} of {@link Filter} beans</li>
46  * </ul>
47  * </p>
48  */

49 public final class SQLSupport {
50
51     private static final Logger LOGGER = Logger.getInstance(SQLSupport.class);
52     private static final String JavaDoc 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     /**
77      * Get an instance of this class configured using a default {@link SQLSupportConfig}.
78      *
79      * @return a SQLSupport instance
80      */

81     public static SQLSupport getInstance() {
82         return getInstance(DEFAULT_SQL_SUPPORT_CONFIG);
83     }
84
85     /**
86      * Get an instance of this class configured using a {@link SQLSupportConfig} that has been configured using
87      * the provided {@link DatabaseMetaData}.
88      * @param databaseMetaData the database metadata used to configure a {@link SQLSupportConfig} object
89      * @return a SQLSupport instance
90      * @throws SQLException when an error occurs reading from {@link DatabaseMetaData}
91      */

92     public static SQLSupport getInstance(DatabaseMetaData JavaDoc databaseMetaData)
93             throws SQLException JavaDoc {
94         SQLSupportConfig config = SQLSupportConfigFactory.getInstance(databaseMetaData);
95         return getInstance(config);
96     }
97
98     /**
99      * Get an instance of this class configured using the provided {@link SQLSupportConfig}. The caller
100      * should create and appropriately initialize the config object.
101      * @param config the config object use to configure a SQLSupport instance
102      * @return a SQLSupport instance
103      */

104     public static SQLSupport getInstance(SQLSupportConfig config) {
105         SQLSupport sqlSupport = new SQLSupport();
106         sqlSupport.configure(config);
107         return sqlSupport;
108     }
109
110     /**
111      * Get the readable string labels for a filter operation. This {@link Map} contains a set of
112      * filter operation abbreviations mapped to a label for that filter operation. The abbreviations
113      * can be used to lookup the correct filter operation. This method accepts values enumerated
114      * in {@link FilterTypeHint} and available via
115      * {@link org.apache.beehive.netui.databinding.datagrid.api.filter.FilterTypeHint#getHint()}.
116      *
117      * @param typeHint the type hint whose matching operations to lookup
118      * @return Map a {@link Map} of String abbreviations to readable string names for the operation
119      */

120     public static Map JavaDoc lookupFilterOperationLabels(String JavaDoc typeHint) {
121         LinkedHashMap JavaDoc ops = new LinkedHashMap JavaDoc/*<String, String>*/();
122
123         /* todo: i18n */
124         /* todo: caching of the filterOps for a given type hint */
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     /* todo: method returning filter labels given a FitlerTypeHint */
148     /* todo: method returning the FILTER_OPERATIONS */
149     /* todo: method returning FILTER_OPERATIONS given a FitlerTypeHint */
150
151     /**
152      * <p>
153      * Lookup the default filter operation's abbreviation given a filter type hint abbreviation. The type hint
154      * should be obtained via {@link org.apache.beehive.netui.databinding.datagrid.api.filter.FilterTypeHint#getHint()}.
155      * The default filter operations for a given FilterTypeHint string maps to the following FilterOperationHint.
156      * The string returned is the associated FilterOperation's abbreviation
157      * <table>
158      * <tr><td>Type Hint</td><td></td></tr>
159      * <tr><td>{@link FilterTypeHint#DATE}</td><td>{@link FilterOperationHint#EQUAL}</td></tr>
160      * <tr><td>{@link FilterTypeHint#NUMERIC}</td><td>{@link FilterOperationHint#EQUAL}</td></tr>
161      * <tr><td>{@link FilterTypeHint#STRING}</td><td>{@link FilterOperationHint#CONTAINS}</td></tr>
162      * </table>
163      * </p>
164      * @param typeHint the type hint whose default operation to lookup
165      * @return String the abbreviation
166      */

167     public static String JavaDoc lookoupDefaultFilterOperationAbbreviation(String JavaDoc 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     /**
176      * Lookup a filter operation given a filter operation abbreviation. The abbreviation should be obtained
177      * via {@link org.apache.beehive.netui.databinding.datagrid.api.filter.FilterOperation#getAbbreviation()}.
178      * @param abbrev
179      * @return the filter operation
180      */

181     public static final FilterOperation mapFilterAbbreviationToOperation(String JavaDoc 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     /**
191      * Map a {@link FilterOperationHint} to a SQL-specific {@link FilterOperation}. When using SQL as a query
192      * language, all of the operations defined in {@link FilterOperationHint} should be supported.
193      *
194      * @param hint the hint
195      * @return the {@link FilterOperation} matching the given hint.
196      */

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     /**
209      * Private constructor. All access to this should be done through the static factory methods
210      * on the class.
211      */

212     private SQLSupport() {
213     }
214
215     /**
216      * Set the {@link SQLSupportConfig} object useed to configure the SQL statements produced by this class.
217      * @param config the config object
218      */

219     public void configure(SQLSupportConfig config) {
220         _config = config;
221     }
222
223     /**
224      * <p>
225      * Create a SQL order fragment from the list of {@link Sort} objects. This fragment does not begin with
226      * ORDER BY and is just the <i>fragment</i> for such a clause. If the given list of
227      * sorts contains a sort with sort expression "foo" and sort direction {@link SortDirection#DESCENDING},
228      * the generated SQL statement will appear as:
229      * <pre>
230      * foo DESC
231      * </pre>
232      * </p>
233      * @param sorts the list of {@link Sort} objects
234      * @return the generated SQL statement order fragment or an emtpy string if there are no sorts
235      */

236     public final String JavaDoc createOrderByFragment(List JavaDoc/*<Sort>*/ 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     /**
246      * <p>
247      * Create a SQL ORDER BY clause from the list of {@link Sort} objects. This fragment begins with
248      * ORDER BY. If the given list of sorts contains a sort with sort expression "foo" and sort direction
249      * {@link SortDirection#DESCENDING}, the generated SQL statement will appear as:
250      * <pre>
251      * ORDER BY foo DESC
252      * </pre>
253      * </p>
254      * @param sorts the list of {@link Sort} objects
255      * @return the generated SQL ORDER BY clause or an emtpy string if there are no sorts
256      */

257     public final String JavaDoc createOrderByClause(List JavaDoc/*<Sort>*/ 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     /**
268      * <p>
269      * Create a SQL WHERE clause from the list of {@link Filter} objects. This fragment does not begin with
270      * WHERE. If the given list of sorts contains a Filter with filter expression "foo", operation equals,
271      * and value '42', the generated SQL statement will appear as:
272      * <pre>
273      * foo = 42
274      * </pre>
275      * When multiple Filters in the list, the filters will be AND'ed together in the generated SQL statement.
276      * </p>
277      * @param filters the list of {@link Filter} objects
278      * @return the generated SQL where clause fragment or an emtpy string if there are no filters
279      */

280     public String JavaDoc createWhereFragment(List JavaDoc/*<Filter>*/ 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     /**
290      * <p>
291      * Create a SQL WHERE clause from the list of {@link Filter} objects. This fragment begins with
292      * WHERE. If the given list of sorts contains a Filter with filter expression "foo", operation equals,
293      * and value '42', the generated SQL statement will appear as:
294      * <pre>
295      * WHERE foo = 42
296      * </pre>
297      * When multiple Filters in the list, the filters will be AND'ed together in the generated SQL statement.
298      * </p>
299      * @param filters the list of {@link Filter} objects
300      * @return the generated SQL WHERE clause or an emtpy string if there are no filters
301      */

302     public String JavaDoc createWhereClause(List JavaDoc/*<Filter>*/ 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     /**
313      *
314      * @param sql
315      * @param sorts
316      */

317     private void internalCreateOrderByFragment(InternalStringBuilder sql, List JavaDoc/*<Sort>*/ 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     /**
329      *
330      * @param sql
331      * @param filters
332      */

333     private void internalCreateWhereFragment(InternalStringBuilder sql, List JavaDoc/*<Filter>*/ 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 JavaDoc 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 JavaDoc message = Bundle.getErrorString("DataGridFilter_NoFilterOperation", new Object JavaDoc[]{filter.getFilterExpression()});
354                 LOGGER.error(message);
355                 throw new IllegalFilterException(message);
356             }
357
358             /* todo: feature. pluggable conjunctions AND and OR here */
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 JavaDoc 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                         /* todo: conider using SQLFragment from the DatabaseControl here. */
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 JavaDoc[] arr;
424                         if(filter.getValue().getClass().isArray())
425                             arr = (Object JavaDoc[])filter.getValue();
426                         else
427                             arr = new Object JavaDoc[]{filter.getValue()};
428
429                         if(arr.length == 0)
430                             break;
431
432                         sql.append("(");
433                         sql.append(fExpr);
434                         sql.append(" IN (");
435                         String JavaDoc comma = "";
436                         for(int j = 0; j < arr.length; j++) {
437                             sql.append(comma);
438                             /* todo: date handling. probably some type normalization required here */
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 JavaDoc[]{fOp}));
447             }
448         }
449     }
450
451     /**
452      *
453      * @param o
454      * @return
455      */

456     private String JavaDoc convertSQLPattern(Object JavaDoc o) {
457         if(o == null)
458             return EMPTY;
459         else {
460             String JavaDoc s = o.toString();
461             s = s.replaceAll("\\\\", "\\\\\\\\");
462             s = s.replaceAll("%", "\\\\%");
463             s = s.replaceAll("_", "\\\\_");
464             return s;
465         }
466     }
467
468     /**
469      *
470      * @param o
471      * @return
472      */

473     private String JavaDoc convertSQLString(Object JavaDoc o) {
474         if(o == null)
475             return EMPTY;
476         else
477             return (o.toString()).replaceAll("'", "''");
478     }
479
480     /**
481      *
482      * @param sql
483      * @param value
484      * @param typeHint
485      */

486     private void addParameter(InternalStringBuilder sql, Object JavaDoc 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     /**
494      *
495      * @param op
496      * @return
497      */

498     private String JavaDoc 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