SQLTemplate


1   /*****************************************************************
2    *   Licensed to the Apache Software Foundation (ASF) under one
3    *  or more contributor license agreements.  See the NOTICE file
4    *  distributed with this work for additional information
5    *  regarding copyright ownership.  The ASF licenses this file
6    *  to you under the Apache License, Version 2.0 (the
7    *  "License"); you may not use this file except in compliance
8    *  with the License.  You may obtain a copy of the License at
9    *
10   *    http://www.apache.org/licenses/LICENSE-2.0
11   *
12   *  Unless required by applicable law or agreed to in writing,
13   *  software distributed under the License is distributed on an
14   *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
15   *  KIND, either express or implied.  See the License for the
16   *  specific language governing permissions and limitations
17   *  under the License.
18   ****************************************************************/
19  
20  package org.apache.cayenne.query;
21  
22  import java.util.Collection  ;
23  import java.util.Collections  ;
24  import java.util.HashMap  ;
25  import java.util.Iterator  ;
26  import java.util.Map  ;
27  
28  import org.apache.cayenne.map.DataMap;
29  import org.apache.cayenne.map.DbEntity;
30  import org.apache.cayenne.map.EntityResolver;
31  import org.apache.cayenne.map.ObjEntity;
32  import org.apache.cayenne.map.Procedure;
33  import org.apache.cayenne.map.QueryBuilder;
34  import org.apache.cayenne.util.Util;
35  import org.apache.cayenne.util.XMLEncoder;
36  import org.apache.cayenne.util.XMLSerializable;
37  import org.apache.commons.collections.IteratorUtils;
38  import org.apache.commons.collections.Transformer;
39  
40  /**
41   * A query that executes unchanged (except for template preprocessing) "raw" SQL specified
42   * by the user.
43   * <h3>Template Script</h3>
44   * <p>
45   * SQLTemplate stores a dynamic template for the SQL query that supports parameters and
46   * customization using Velocity scripting language. The most straightforward use of
47   * scripting abilities is to build parameterized queries. For example:
48   * </p>
49   * 
50   * <pre>
51   *                  SELECT ID, NAME FROM SOME_TABLE WHERE NAME LIKE $a
52   * </pre>
53   * 
54   * <p>
55   * <i>For advanced scripting options see "Scripting SQLTemplate" chapter in the User
56   * Guide. </i>
57   * </p>
58   * <h3>Per-Database Template Customization</h3>
59   * <p>
60   * SQLTemplate has a {@link #getDefaultTemplate() default template script}, but also it
61   * allows to configure multiple templates and switch them dynamically. This way a single
62   * query can have multiple "dialects" specific to a given database.
63   * </p>
64   * <h3>Parameter Sets</h3>
65   * <p>
66   * SQLTemplate supports multiple sets of parameters, so a single query can be executed
67   * multiple times with different parameters. "Scrolling" through parameter list is done by
68   * calling {@link #parametersIterator()}. This iterator goes over parameter sets,
69   * returning a Map on each call to "next()"
70   * </p>
71   * 
72   * @since 1.1
73   * @author Andrus Adamchik
74   */
75  public class SQLTemplate extends AbstractQuery implements ParameterizedQuery,
76          XMLSerializable {
77  
78      static final String   COLUMN_NAME_CAPITALIZATION_PROPERTY = "cayenne.SQLTemplate.columnNameCapitalization";
79  
80      /**
81       * @since 3.0
82       */
83      public static final String   UPPERCASE_COLUMN_NAMES = "upper";
84  
85      /**
86       * @since 3.0
87       */
88      public static final String   LOWERCASE_COLUMN_NAMES = "lower";
89  
90      private static final Transformer nullMapTransformer = new Transformer() {
91  
92          public Object   transform(Object   input) {
93              return (input != null) ? input : Collections.EMPTY_MAP;
94          }
95      };
96  
97      protected String   defaultTemplate;
98      protected Map   templates;
99      protected Map  [] parameters;
100     protected String   columnNamesCapitalization;
101 
102     SQLTemplateMetadata selectInfo = new SQLTemplateMetadata();
103 
104     /**
105      * Creates an empty SQLTemplate. Note this constructor does not specify the "root" of
106      * the query, so a user must call "setRoot" later to make sure SQLTemplate can be
107      * executed.
108      * 
109      * @since 1.2
110      */
111     public SQLTemplate() {
112     }
113 
114     /**
115      * @since 1.2
116      */
117     public SQLTemplate(DataMap rootMap, String   defaultTemplate) {
118         setDefaultTemplate(defaultTemplate);
119         setRoot(rootMap);
120     }
121 
122     /**
123      * @since 1.2
124      */
125     public SQLTemplate(ObjEntity rootEntity, String   defaultTemplate) {
126         setDefaultTemplate(defaultTemplate);
127         setRoot(rootEntity);
128     }
129 
130     /**
131      * @since 1.2
132      */
133     public SQLTemplate(Class   rootClass, String   defaultTemplate) {
134         setDefaultTemplate(defaultTemplate);
135         setRoot(rootClass);
136     }
137 
138     /**
139      * @since 1.2
140      */
141     public SQLTemplate(DbEntity rootEntity, String   defaultTemplate) {
142         setDefaultTemplate(defaultTemplate);
143         setRoot(rootEntity);
144     }
145 
146     /**
147      * @since 1.2
148      */
149     public SQLTemplate(String   objEntityName, String   defaultTemplate) {
150         setRoot(objEntityName);
151         setDefaultTemplate(defaultTemplate);
152     }
153 
154     /**
155      * @since 1.2
156      */
157     public QueryMetadata getMetaData(EntityResolver resolver) {
158         selectInfo.resolve(root, resolver, this);
159         return selectInfo;
160     }
161 
162     /**
163      * Calls <em>sqlAction(this)</em> on the visitor.
164      * 
165      * @since 1.2
166      */
167     public SQLAction createSQLAction(SQLActionVisitor visitor) {
168         return visitor.sqlAction(this);
169     }
170 
171     /**
172      * Prints itself as XML to the provided PrintWriter.
173      * 
174      * @since 1.1
175      */
176     public void encodeAsXML(XMLEncoder encoder) {
177         encoder.print("<query name=\"");
178         encoder.print(getName());
179         encoder.print("\" factory=\"");
180         encoder.print("org.apache.cayenne.map.SQLTemplateBuilder");
181 
182         String   rootString = null;
183         String   rootType = null;
184 
185         if (root instanceof String  ) {
186             rootType = QueryBuilder.OBJ_ENTITY_ROOT;
187             rootString = root.toString();
188         }
189         else if (root instanceof ObjEntity) {
190             rootType = QueryBuilder.OBJ_ENTITY_ROOT;
191             rootString = ((ObjEntity) root).getName();
192         }
193         else if (root instanceof DbEntity) {
194             rootType = QueryBuilder.DB_ENTITY_ROOT;
195             rootString = ((DbEntity) root).getName();
196         }
197         else if (root instanceof Procedure) {
198             rootType = QueryBuilder.PROCEDURE_ROOT;
199             rootString = ((Procedure) root).getName();
200         }
201         else if (root instanceof Class  ) {
202             rootType = QueryBuilder.JAVA_CLASS_ROOT;
203             rootString = ((Class  ) root).getName();
204         }
205         else if (root instanceof DataMap) {
206             rootType = QueryBuilder.DATA_MAP_ROOT;
207             rootString = ((DataMap) root).getName();
208         }
209 
210         if (rootType != null) {
211             encoder.print("\" root=\"");
212             encoder.print(rootType);
213             encoder.print("\" root-name=\"");
214             encoder.print(rootString);
215         }
216 
217         encoder.println("\">");
218 
219         encoder.indent(1);
220 
221         selectInfo.encodeAsXML(encoder);
222 
223         if (getColumnNamesCapitalization() != null) {
224             encoder.printProperty(
225                     COLUMN_NAME_CAPITALIZATION_PROPERTY,
226                     getColumnNamesCapitalization());
227         }
228 
229         // encode default SQL
230         if (defaultTemplate != null) {
231             encoder.print("<sql><![CDATA[");
232             encoder.print(defaultTemplate);
233             encoder.println("]]></sql>");
234         }
235 
236         // encode adapter SQL
237         if (templates != null && !templates.isEmpty()) {
238             Iterator   it = templates.entrySet().iterator();
239             while (it.hasNext()) {
240                 Map.Entry   entry = (Map.Entry  ) it.next();
241                 Object   key = entry.getKey();
242                 Object   value = entry.getValue();
243 
244                 if (key != null && value != null) {
245                     String   sql = value.toString().trim();
246                     if (sql.length() > 0) {
247                         encoder.print("<sql adapter-class=\"");
248                         encoder.print(key.toString());
249                         encoder.print("\"><![CDATA[");
250                         encoder.print(sql);
251                         encoder.println("]]></sql>");
252                     }
253                 }
254             }
255         }
256 
257         // TODO: support parameter encoding
258 
259         encoder.indent(-1);
260         encoder.println("</query>");
261     }
262 
263     /**
264      * Initializes query parameters using a set of properties.
265      * 
266      * @since 1.1
267      */
268     public void initWithProperties(Map   properties) {
269         // must init defaults even if properties are empty
270         selectInfo.initWithProperties(properties);
271 
272         if (properties == null) {
273             properties = Collections.EMPTY_MAP;
274         }
275 
276         Object   columnNamesCapitalization = properties
277                 .get(COLUMN_NAME_CAPITALIZATION_PROPERTY);
278         this.columnNamesCapitalization = (columnNamesCapitalization != null)
279                 ? columnNamesCapitalization.toString()
280                 : null;
281     }
282 
283     /**
284      * Returns an iterator over parameter sets. Each element returned from the iterator is
285      * a java.util.Map.
286      */
287     public Iterator   parametersIterator() {
288         return (parameters == null || parameters.length == 0) ? IteratorUtils
289                 .emptyIterator() : IteratorUtils.transformedIterator(IteratorUtils
290                 .arrayIterator(parameters), nullMapTransformer);
291     }
292 
293     /**
294      * Returns the number of parameter sets.
295      */
296     public int parametersSize() {
297         return (parameters != null) ? parameters.length : 0;
298     }
299 
300     /**
301      * Returns a new query built using this query as a prototype and a new set of
302      * parameters.
303      */
304     public SQLTemplate queryWithParameters(Map   parameters) {
305         return queryWithParameters(new Map  [] {
306             parameters
307         });
308     }
309 
310     /**
311      * Returns a new query built using this query as a prototype and a new set of
312      * parameters.
313      */
314     public SQLTemplate queryWithParameters(Map  [] parameters) {
315         // create a query replica
316         SQLTemplate query = new SQLTemplate();
317 
318         query.setRoot(root);
319         query.setDefaultTemplate(getDefaultTemplate());
320 
321         if (templates != null) {
322             query.templates = new HashMap  (templates);
323         }
324 
325         query.selectInfo.copyFromInfo(this.selectInfo);
326         query.setParameters(parameters);
327 
328         // The following algorithm is for building the new query name based
329         // on the original query name and a hashcode of the map of parameters.
330         // This way the query clone can take advantage of caching. Fixes
331         // problem reported in CAY-360.
332 
333         if (!Util.isEmptyString(name)) {
334             StringBuffer   buffer = new StringBuffer  (name);
335 
336             if (parameters != null) {
337                 for (int i = 0; i < parameters.length; i++) {
338                     if (!parameters[i].isEmpty()) {
339                         buffer.append(parameters[i].hashCode());
340                     }
341                 }
342             }
343 
344             query.setName(buffer.toString());
345         }
346 
347         return query;
348     }
349 
350     /**
351      * Creates and returns a new SQLTemplate built using this query as a prototype and
352      * substituting template parameters with the values from the map.
353      * 
354      * @since 1.1
355      */
356     public Query createQuery(Map   parameters) {
357         return queryWithParameters(parameters);
358     }
359 
360     public String   getCachePolicy() {
361         return selectInfo.getCachePolicy();
362     }
363 
364     public void setCachePolicy(String   policy) {
365         this.selectInfo.setCachePolicy(policy);
366     }
367 
368     /**
369      * @since 3.0
370      */
371     public String  [] getCacheGroups() {
372         return selectInfo.getCacheGroups();
373     }
374 
375     /**
376      * @since 3.0
377      */
378     public void setCacheGroups(String  [] cachGroups) {
379         this.selectInfo.setCacheGroups(cachGroups);
380     }
381 
382     public int getFetchLimit() {
383         return selectInfo.getFetchLimit();
384     }
385 
386     public void setFetchLimit(int fetchLimit) {
387         this.selectInfo.setFetchLimit(fetchLimit);
388     }
389 
390     public int getPageSize() {
391         return selectInfo.getPageSize();
392     }
393 
394     public void setPageSize(int pageSize) {
395         selectInfo.setPageSize(pageSize);
396     }
397 
398     public void setFetchingDataRows(boolean flag) {
399         selectInfo.setFetchingDataRows(flag);
400     }
401 
402     public boolean isFetchingDataRows() {
403         return selectInfo.isFetchingDataRows();
404     }
405 
406     public boolean isRefreshingObjects() {
407         return selectInfo.isRefreshingObjects();
408     }
409 
410     public void setRefreshingObjects(boolean flag) {
411         selectInfo.setRefreshingObjects(flag);
412     }
413 
414     public boolean isResolvingInherited() {
415         return selectInfo.isResolvingInherited();
416     }
417 
418     public void setResolvingInherited(boolean b) {
419         selectInfo.setResolvingInherited(b);
420     }
421 
422     /**
423      * Returns default SQL template for this query.
424      */
425     public String   getDefaultTemplate() {
426         return defaultTemplate;
427     }
428 
429     /**
430      * Sets default SQL template for this query.
431      */
432     public void setDefaultTemplate(String   string) {
433         defaultTemplate = string;
434     }
435 
436     /**
437      * Returns a template for key, or a default template if a template for key is not
438      * found.
439      */
440     public synchronized String   getTemplate(String   key) {
441         if (templates == null) {
442             return defaultTemplate;
443         }
444 
445         String   template = (String  ) templates.get(key);
446         return (template != null) ? template : defaultTemplate;
447     }
448 
449     /**
450      * Returns template for key, or null if there is no template configured for this key.
451      * Unlike {@link #getTemplate(String)}this method does not return a default template
452      * as a failover strategy, rather it returns null.
453      */
454     public synchronized String   getCustomTemplate(String   key) {
455         return (templates != null) ? (String  ) templates.get(key) : null;
456     }
457 
458     /**
459      * Adds a SQL template string for a given key. Note the the keys understood by Cayenne
460      * must be fully qualified adapter class names. This way the framework can related
461      * current DataNode to the right template. E.g.
462      * "org.apache.cayenne.dba.oracle.OracleAdapter" is a key that should be used to setup
463      * an Oracle-specific template.
464      * 
465      * @see #setDefaultTemplate(String)
466      */
467     public synchronized void setTemplate(String   key, String   template) {
468         if (templates == null) {
469             templates = new HashMap  ();
470         }
471 
472         templates.put(key, template);
473     }
474 
475     public synchronized void removeTemplate(String   key) {
476         if (templates != null) {
477             templates.remove(key);
478         }
479     }
480 
481     /**
482      * Returns a collection of configured template keys.
483      */
484     public synchronized Collection   getTemplateKeys() {
485         return (templates != null) ? Collections.unmodifiableCollection(templates
486                 .keySet()) : Collections.EMPTY_LIST;
487     }
488 
489     /**
490      * Utility method to get the first set of parameters, since most queries will only
491      * have one.
492      */
493     public Map   getParameters() {
494         Map   map = (parameters != null && parameters.length > 0) ? parameters[0] : null;
495         return (map != null) ? map : Collections.EMPTY_MAP;
496     }
497 
498     /**
499      * Utility method to initialize query with only a single set of parameters. Useful,
500      * since most queries will only have one set. Internally calls
501      * {@link #setParameters(Map[])}.
502      */
503     public void setParameters(Map   map) {
504         setParameters(map != null ? new Map  [] {
505             map
506         } : null);
507     }
508 
509     public void setParameters(Map  [] parameters) {
510 
511         if (parameters == null) {
512             this.parameters = null;
513         }
514         else {
515             // clone parameters to ensure that we don't have immutable maps that are not
516             // serializable with Hessian...
517             this.parameters = new Map  [parameters.length];
518             for (int i = 0; i < parameters.length; i++) {
519                 this.parameters[i] = parameters[i] != null
520                         ? new HashMap  (parameters[i])
521                         : new HashMap  ();
522             }
523         }
524     }
525 
526     /**
527      * @since 1.2
528      */
529     public PrefetchTreeNode getPrefetchTree() {
530         return selectInfo.getPrefetchTree();
531     }
532 
533     /**
534      * Adds a prefetch.
535      * 
536      * @since 1.2
537      */
538     public PrefetchTreeNode addPrefetch(String   prefetchPath) {
539         // by default use JOINT_PREFETCH_SEMANTICS
540         return selectInfo.addPrefetch(
541                 prefetchPath,
542                 PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
543     }
544 
545     /**
546      * @since 1.2
547      */
548     public void removePrefetch(String   prefetch) {
549         selectInfo.removePrefetch(prefetch);
550     }
551 
552     /**
553      * Adds all prefetches from a provided collection.
554      * 
555      * @since 1.2
556      */
557     public void addPrefetches(Collection   prefetches) {
558         selectInfo.addPrefetches(prefetches, PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
559     }
560 
561     /**
562      * Clears all prefetches.
563      * 
564      * @since 1.2
565      */
566     public void clearPrefetches() {
567         selectInfo.clearPrefetches();
568     }
569 
570     /**
571      * Returns a column name capitalization policy applied to selecting queries. This is
572      * used to simplify mapping of the queries like "SELECT * FROM ...", ensuring that a
573      * chosen Cayenne column mapping strategy (e.g. all column names in uppercase) is
574      * portable across database engines that can have varying default capitalization.
575      * Default (null) value indicates that column names provided in result set are used
576      * unchanged.
577      * 
578      * @since 3.0
579      */
580     public String   getColumnNamesCapitalization() {
581         return columnNamesCapitalization;
582     }
583 
584     /**
585      * Sets a column name capitalization policy applied to selecting queries. This is used
586      * to simplify mapping of the queries like "SELECT * FROM ...", ensuring that a chosen
587      * Cayenne column mapping strategy (e.g. all column names in uppercase) is portable
588      * across database engines that can have varying default capitalization. Default
589      * (null) value indicates that column names provided in result set are used unchanged.
590      * <p/> Note that while a non-default setting is useful for queries that do not rely
591      * on a #result directive to describe columns, it works for all SQLTemplates the same
592      * way.
593      * 
594      * @param columnNameCapitalization Can be null of one of
595      *            {@link #LOWERCASE_COLUMN_NAMES} or {@link #UPPERCASE_COLUMN_NAMES}.
596      * @since 3.0
597      */
598     public void setColumnNamesCapitalization(String   columnNameCapitalization) {
599         this.columnNamesCapitalization = columnNameCapitalization;
600     }
601 
602     /**
603      * Sets an optional explicit mapping of the result set. If result set mapping is
604      * specified, the result of SQLTemplate may not be a normal list of Persistent objects
605      * or DataRows, instead it will follow the {@link SQLResultSetMapping} rules.
606      * 
607      * @since 3.0
608      */
609     public void setResultSetMapping(SQLResultSetMapping resultSetMapping) {
610         selectInfo.setResultSetMapping(resultSetMapping);
611     }
612 }
613
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags