Java > Open Source Codes > org > apache > torque > util > SQLBuilder


1 package org.apache.torque.util;
2
3 /*
4  * Licensed to the Apache Software Foundation (ASF) under one
5  * or more contributor license agreements. See the NOTICE file
6  * distributed with this work for additional information
7  * regarding copyright ownership. The ASF licenses this file
8  * to you under the Apache License, Version 2.0 (the
9  * "License"); you may not use this file except in compliance
10  * with the License. You may obtain a copy of the License at
11  *
12  * http://www.apache.org/licenses/LICENSE-2.0
13  *
14  * Unless required by applicable law or agreed to in writing,
15  * software distributed under the License is distributed on an
16  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
17  * KIND, either express or implied. See the License for the
18  * specific language governing permissions and limitations
19  * under the License.
20  */
21
22 import java.util.HashSet ;
23 import java.util.Iterator ;
24 import java.util.List ;
25 import java.util.Map ;
26 import java.util.Set ;
27
28 import org.apache.commons.lang.StringUtils;
29 import org.apache.commons.logging.Log;
30 import org.apache.commons.logging.LogFactory;
31 import org.apache.torque.Torque;
32 import org.apache.torque.TorqueException;
33 import org.apache.torque.adapter.DB;
34 import org.apache.torque.map.ColumnMap;
35 import org.apache.torque.map.DatabaseMap;
36 import org.apache.torque.util.Criteria.Criterion;
37
38 /**
39  * Factored out code that is used to process SQL tables. This code comes
40  * from BasePeer and is put here to reduce complexity in the BasePeer class.
41  * You should not use the methods here directly!
42  *
43  * @author <a HREF="mailto:hps@intermeta.de">Henning P. Schmiedehausen</a>
44  * @author <a HREF="mailto:fischer@seitenbau.de">Thomas Fischer</a>
45  * @version $Id: SQLBuilder.java 476550 2006-11-18 16:08:37Z tfischer $
46  */
47 public final class SQLBuilder
48 {
49     /** Logging */
50     protected static final Log log = LogFactory.getLog(SQLBuilder.class);
51
52     /** Function Characters */
53     public static final String [] COLUMN_CHARS = {".", "*"};
54     public static final String [] DELIMITERS = {" ", ",", "(", ")", "<", ">"};
55
56     /**
57      * Private constructor to prevent instantiation.
58      *
59      * Class contains only static method ans should therefore not be
60      * instantiated.
61      */
62     private SQLBuilder()
63     {
64     }
65
66     /**
67      * Fully qualify a table name with an optional schema reference
68      *
69      * @param table The table name to use. If null is passed in, null is returned.
70      * @param dbName The name of the database to which this tables belongs.
71      * If null is passed, the default database is used.
72      *
73      * @return The table name to use inside the SQL statement. If null is passed
74      * into this method, null is returned.
75      * @exception TorqueException if an error occurs
76      */
77     public static String getFullTableName(
78             final String table,
79             final String dbName)
80         throws TorqueException
81     {
82         if (table != null)
83         {
84             int dotIndex = table.indexOf(".");
85
86             if (dotIndex == -1) // No schema given
87 {
88                 String targetDBName = (dbName == null)
89                         ? Torque.getDefaultDB()
90                         : dbName;
91
92                 String targetSchema = Torque.getSchema(targetDBName);
93
94                 // If we have a default schema, fully qualify the
95 // table and return.
96 if (StringUtils.isNotEmpty(targetSchema))
97                 {
98                     return new StringBuffer ()
99                             .append(targetSchema)
100                             .append(".")
101                             .append(table)
102                             .toString();
103                 }
104             }
105         }
106
107         return table;
108     }
109
110     /**
111      * Remove a possible schema name from the table name.
112      *
113      * @param table The table name to use
114      *
115      * @return The table name with a possible schema name
116      * stripped off
117      */
118     public static String getUnqualifiedTableName(final String table)
119     {
120         if (table != null)
121         {
122             int dotIndex = table.lastIndexOf("."); // Do we have a dot?
123
124             if (++dotIndex > 0) // Incrementation allows for better test _and_ substring...
125 {
126                 return table.substring(dotIndex);
127             }
128         }
129
130         return table;
131     }
132
133     /**
134      * Removes a possible function name or clause from a column name
135      *
136      * @param name The column name, possibly containing a clause
137      *
138      * @return The column name
139      *
140      * @throws TorqueException If the column name was malformed
141      */
142     private static String removeSQLFunction(final String name)
143             throws TorqueException
144     {
145         // Empty name => return it
146 if (StringUtils.isEmpty(name))
147         {
148             return name;
149         }
150
151         // Find Table.Column
152 int dotIndex = name.indexOf('.');
153         if (dotIndex == -1)
154         {
155             dotIndex = name.indexOf("*");
156         }
157         if (dotIndex == -1)
158         {
159             throw new TorqueException("removeSQLFunction() : Column name "
160                     + name
161                     + " does not contain a . or a *");
162         }
163         String pre = name.substring(0, dotIndex);
164         String post = name.substring(dotIndex + 1, name.length());
165         int startIndex = StringUtils.lastIndexOfAny(pre, DELIMITERS);
166         int endIndex = StringUtils.indexOfAny(post, DELIMITERS);
167         if (startIndex < 0 && endIndex < 0)
168         {
169             return name;
170         }
171         else
172         {
173             if (endIndex < 0)
174             {
175                 endIndex = post.length();
176             }
177             // if startIndex == -1 the formula is correct
178 return name.substring(startIndex + 1, dotIndex + 1 + endIndex);
179         }
180     }
181
182     /**
183      * Returns a table name from an identifier. Each identifier is to be qualified
184      * as [schema.]table.column. This could also contain FUNCTION([schema.]table.column).
185      *
186      * @param name The (possible fully qualified) identifier name
187      *
188      * @return the fully qualified table name
189      *
190      * @throws TorqueException If the identifier name was malformed
191      */
192     public static String getTableName(final String name, final String dbName)
193             throws TorqueException
194     {
195         final String testName = removeSQLFunction(name);
196
197         if (StringUtils.isEmpty(testName))
198         {
199             throwMalformedColumnNameException(
200                     "getTableName",
201                     name);
202         }
203
204         // Everything before the last dot is the table name
205 int rightDotIndex = testName.lastIndexOf('.');
206
207         if (rightDotIndex < 0)
208         {
209             if ("*".equals(testName))
210             {
211                 return null;
212             }
213
214             throwMalformedColumnNameException(
215                     "getTableName",
216                     name);
217         }
218
219         return getFullTableName(testName.substring(0, rightDotIndex), dbName);
220     }
221
222
223
224     /**
225      * Returns a set of all tables and possible aliases referenced
226      * from a criterion. The resulting Set can be directly used to
227      * build a WHERE clause
228      *
229      * @param crit A Criteria object
230      * @param tableCallback A Callback Object
231      * @return A Set of tables.
232      */
233     public static Set getTableSet(
234             final Criteria crit,
235             final TableCallback tableCallback)
236     {
237         HashSet tables = new HashSet ();
238
239         // Loop over all the Criterions
240 for (Iterator it = crit.keySet().iterator(); it.hasNext();)
241         {
242             String key = (String ) it.next();
243             Criteria.Criterion c = crit.getCriterion(key);
244             List tableNames = c.getAllTables();
245
246             // Loop over all Tables referenced in this criterion.
247 for (Iterator it2 = tableNames.iterator(); it2.hasNext();)
248             {
249                 String name = (String ) it2.next();
250                 String aliasName = crit.getTableForAlias(name);
251
252                 // If the tables have an alias, add an "<xxx> AS <yyy> statement"
253 if (StringUtils.isNotEmpty(aliasName))
254                 {
255                     String newName =
256                             new StringBuffer (name.length() + aliasName.length() + 4)
257                             .append(aliasName)
258                             .append(" AS ")
259                             .append(name)
260                             .toString();
261                     name = newName;
262                 }
263                 tables.add(name);
264             }
265
266             if (tableCallback != null)
267             {
268                 tableCallback.process(tables, key, crit);
269             }
270         }
271
272         return tables;
273     }
274
275     /**
276      * Builds a Query clause for Updating and deleting
277      *
278      * @param crit a <code>Criteria</code> value
279      * @param params a <code>List</code> value
280      * @param qc a <code>QueryCallback</code> value
281      * @return a <code>Query</code> value
282      * @exception TorqueException if an error occurs
283      */
284     public static Query buildQueryClause(final Criteria crit,
285             final List params,
286             final QueryCallback qc)
287             throws TorqueException
288     {
289         Query query = new Query();
290
291         final String dbName = crit.getDbName();
292         final DB db = Torque.getDB(dbName);
293         final DatabaseMap dbMap = Torque.getDatabaseMap(dbName);
294
295         JoinBuilder.processJoins(db, dbMap, crit, query);
296         processModifiers(crit, query);
297         processSelectColumns(crit, query, dbName);
298         processAsColumns(crit, query);
299         processCriterions(db, dbMap, dbName, crit, query, params, qc);
300         processGroupBy(crit, query);
301         processHaving(crit, query);
302         processOrderBy(db, dbMap, crit, query);
303         processLimits(crit, query);
304
305         if (log.isDebugEnabled())
306         {
307             log.debug(query.toString());
308         }
309         return query;
310     }
311
312
313     /**
314      * adds the select columns from the criteria to the query
315      * @param criteria the criteria from which the select columns are taken
316      * @param query the query to which the select columns should be added
317      * @throws TorqueException if the select columns can not be processed
318      */
319     private static void processSelectColumns(
320             final Criteria criteria,
321             final Query query,
322             final String dbName)
323         throws TorqueException
324     {
325         UniqueList selectClause = query.getSelectClause();
326         UniqueList select = criteria.getSelectColumns();
327
328         for (int i = 0; i < select.size(); i++)
329         {
330             String identifier = (String ) select.get(i);
331             selectClause.add(identifier);
332             addTableToFromClause(getTableName(identifier, dbName), criteria, query);
333         }
334     }
335
336     /**
337      * adds the As-columns from the criteria to the query.
338      * @param criteria the criteria from which the As-columns are taken
339      * @param query the query to which the As-columns should be added
340      */
341     private static void processAsColumns(
342             final Criteria criteria,
343             final Query query)
344     {
345         UniqueList querySelectClause = query.getSelectClause();
346         Map criteriaAsColumns = criteria.getAsColumns();
347
348         for (Iterator it = criteriaAsColumns.entrySet().iterator(); it.hasNext();)
349         {
350             Map.Entry entry = (Map.Entry ) it.next();
351             String key = (String ) entry.getKey();
352             querySelectClause.add(
353                     new StringBuffer ()
354                     .append(entry.getValue())
355                     .append(SqlEnum.AS)
356                     .append(key)
357                     .toString());
358         }
359     }
360
361     /**
362      * adds the Modifiers from the criteria to the query
363      * @param criteria the criteria from which the Modifiers are taken
364      * @param query the query to which the Modifiers should be added
365      */
366     private static void processModifiers(
367             final Criteria criteria,
368             final Query query)
369     {
370         UniqueList selectModifiers = query.getSelectModifiers();
371         UniqueList modifiers = criteria.getSelectModifiers();
372         for (int i = 0; i < modifiers.size(); i++)
373         {
374             selectModifiers.add(modifiers.get(i));
375         }
376     }
377
378     /**
379      * adds the Criterion-objects from the criteria to the query
380      * @param criteria the criteria from which the Criterion-objects are taken
381      * @param query the query to which the Criterion-objects should be added
382      * @param params the parameters if a prepared statement should be built,
383      * or null if a normal statement should be built.
384      * @throws TorqueException if the Criterion-objects can not be processed
385      */
386     private static void processCriterions(
387             final DB db,
388             final DatabaseMap dbMap,
389             final String dbName,
390             final Criteria crit,
391             final Query query,
392             final List params,
393             final QueryCallback qc)
394         throws TorqueException
395     {
396         UniqueList whereClause = query.getWhereClause();
397
398         for (Iterator it = crit.keySet().iterator(); it.hasNext();)
399         {
400             String key = (String ) it.next();
401             Criteria.Criterion criterion = crit.getCriterion(key);
402             Criteria.Criterion[] someCriteria =
403                     criterion.getAttachedCriterion();
404
405             String table = null;
406             for (int i = 0; i < someCriteria.length; i++)
407             {
408                 String tableName = someCriteria[i].getTable();
409
410                 // add the table to the from clause, if it is not already
411 // contained there
412 // it is important that this piece of code is executed AFTER
413 // the joins are processed
414 addTableToFromClause(getFullTableName(tableName, dbName), crit, query);
415
416                 table = crit.getTableForAlias(tableName);
417                 if (table == null)
418                 {
419                     table = tableName;
420                 }
421
422                 boolean ignoreCase = ((crit.isIgnoreCase() || someCriteria[i].isIgnoreCase())
423                         && (dbMap.getTable(table)
424                                 .getColumn(someCriteria[i].getColumn())
425                                 .getType()
426                                 instanceof String ));
427
428                 someCriteria[i].setIgnoreCase(ignoreCase);
429             }
430
431             criterion.setDB(db);
432             whereClause.add(qc.process(criterion, params));
433         }
434     }
435
436     /**
437      * adds the OrderBy-Columns from the criteria to the query
438      * @param criteria the criteria from which the OrderBy-Columns are taken
439      * @param query the query to which the OrderBy-Columns should be added
440      * @throws TorqueException if the OrderBy-Columns can not be processed
441      */
442     private static void processOrderBy(
443             final DB db,
444             final DatabaseMap dbMap,
445             final Criteria crit,
446             final Query query)
447             throws TorqueException
448     {
449         UniqueList orderByClause = query.getOrderByClause();
450         UniqueList selectClause = query.getSelectClause();
451
452         UniqueList orderBy = crit.getOrderByColumns();
453
454         if (orderBy != null && orderBy.size() > 0)
455         {
456             // Check for each String/Character column and apply
457 // toUpperCase().
458 for (int i = 0; i < orderBy.size(); i++)
459             {
460                 String orderByColumn = (String ) orderBy.get(i);
461
462                 String strippedColumnName
463                         = removeSQLFunction(orderByColumn);
464                 int dotPos = strippedColumnName.lastIndexOf('.');
465                 if (dotPos == -1)
466                 {
467                     throwMalformedColumnNameException(
468                             "order by",
469                             orderByColumn);
470                 }
471
472                 String tableName = strippedColumnName.substring(0, dotPos);
473                 String table = crit.getTableForAlias(tableName);
474                 if (table == null)
475                 {
476                     table = tableName;
477                 }
478
479                 // See if there's a space (between the column list and sort
480 // order in ORDER BY table.column DESC).
481 int spacePos = strippedColumnName.indexOf(' ');
482                 String columnName;
483                 if (spacePos == -1)
484                 {
485                     columnName =
486                             strippedColumnName.substring(dotPos + 1);
487                 }
488                 else
489                 {
490                     columnName = strippedColumnName.substring(
491                             dotPos + 1,
492                             spacePos);
493                 }
494                 ColumnMap column = dbMap.getTable(table).getColumn(columnName);
495
496                 // only ignore case in order by for string columns
497 // which do not have a function around them
498 if (column.getType() instanceof String
499                         && orderByColumn.indexOf('(') == -1)
500                 {
501                     // find space pos relative to orderByColumn
502 spacePos = orderByColumn.indexOf(' ');
503                     if (spacePos == -1)
504                     {
505                         orderByClause.add(
506                                 db.ignoreCaseInOrderBy(orderByColumn));
507                     }
508                     else
509                     {
510                         orderByClause.add(
511                                 db.ignoreCaseInOrderBy(
512                                         orderByColumn.substring(0, spacePos))
513                                 + orderByColumn.substring(spacePos));
514                     }
515                     selectClause.add(
516                             db.ignoreCaseInOrderBy(tableName + '.' + columnName));
517                 }
518                 else
519                 {
520                     orderByClause.add(orderByColumn);
521                 }
522             }
523         }
524     }
525
526     /**
527      * adds the GroupBy-Columns from the criteria to the query
528      * @param criteria the criteria from which the GroupBy-Columns are taken
529      * @param query the query to which the GroupBy-Columns should be added
530      * @throws TorqueException if the GroupBy-Columns can not be processed
531      */
532     private static void processGroupBy(
533             final Criteria crit,
534             final Query query)
535             throws TorqueException
536     {
537         UniqueList groupByClause = query.getGroupByClause();
538         UniqueList groupBy = crit.getGroupByColumns();
539
540         // need to allow for multiple group bys
541 if (groupBy != null)
542         {
543             for (int i = 0; i < groupBy.size(); i++)
544             {
545                 String columnName = (String ) groupBy.get(i);
546                 String column = (String ) crit.getAsColumns().get(columnName);
547
548                 if (column == null)
549                 {
550                     column = columnName;
551                 }
552
553                 if (column.indexOf('.') != -1)
554                 {
555                     groupByClause.add(column);
556                 }
557                 else
558                 {
559                     throwMalformedColumnNameException("group by",
560                             column);
561                 }
562             }
563         }
564     }
565
566     /**
567      * adds the Having-Columns from the criteria to the query
568      * @param criteria the criteria from which the Having-Columns are taken
569      * @param query the query to which the Having-Columns should be added
570      * @throws TorqueException if the Having-Columns can not be processed
571      */
572     private static void processHaving(
573             final Criteria crit,
574             final Query query)
575             throws TorqueException
576     {
577         Criteria.Criterion having = crit.getHaving();
578         if (having != null)
579         {
580             //String groupByString = null;
581 query.setHaving(having.toString());
582         }
583     }
584
585     /**
586      * adds a Limit clause to the query if supported by the database
587      * @param criteria the criteria from which the Limit and Offset values
588      * are taken
589      * @param query the query to which the Limit clause should be added
590      * @throws TorqueException if the Database adapter cannot be obtained
591      */
592     private static void processLimits(
593             final Criteria crit,
594             final Query query)
595             throws TorqueException
596     {
597         int limit = crit.getLimit();
598         int offset = crit.getOffset();
599
600         if (offset > 0 || limit >= 0)
601         {
602             DB db = Torque.getDB(crit.getDbName());
603             db.generateLimits(query, offset, limit);
604         }
605     }
606
607     /**
608      * Throws a TorqueException with the malformed column name error
609      * message. The error message looks like this:<p>
610      *
611      * <code>
612      * Malformed column name in Criteria [criteriaPhrase]:
613      * '[columnName]' is not of the form 'table.column'
614      * </code>
615      *
616      * @param criteriaPhrase a String, one of "select", "join", or "order by"
617      * @param columnName a String containing the offending column name
618      * @throws TorqueException Any exceptions caught during processing will be
619      * rethrown wrapped into a TorqueException.
620      */
621     public static void throwMalformedColumnNameException(
622         final String criteriaPhrase,
623         final String columnName)
624         throws TorqueException
625     {
626         StringBuffer sb = new StringBuffer ()
627                 .append("Malformed column name in Criteria ")
628                 .append(criteriaPhrase)
629                 .append(": '")
630                 .append(StringUtils.isEmpty(columnName) ? "<empty>" : columnName)
631                 .append("' is not of the form 'table.column'");
632
633         throw new TorqueException(sb.toString());
634     }
635
636     /**
637      * Returns the tablename which can be added to a From Clause.
638      * This takes care of any aliases that might be defined.
639      * For example, if an alias "a" for the table AUTHOR is defined
640      * in the Criteria criteria, getTableNameForFromClause("a", criteria)
641      * returns "AUTHOR a".
642      * @param tableName the name of a table
643      * or the alias for a table
644      * @param criteria a criteria object to resolve a possible alias
645      * @return either the tablename itself if tableOrAliasName is not an alias,
646      * or a String of the form "tableName tableOrAliasName"
647      * if tableOrAliasName is an alias for a table name
648      */
649     public static String getTableNameForFromClause(
650             final String tableName,
651             final Criteria criteria)
652     {
653         String shortTableName = getUnqualifiedTableName(tableName);
654
655         // Most of the time, the alias would be for the short name...
656 String aliasName = criteria.getTableForAlias(shortTableName);
657         if (StringUtils.isEmpty(aliasName))
658         {
659             // But we should also check the FQN...
660 aliasName = criteria.getTableForAlias(tableName);
661         }
662
663         if (StringUtils.isNotEmpty(aliasName))
664         {
665             // If the tables have an alias, add an "<xxx> <yyy> statement"
666 // <xxx> AS <yyy> causes problems on oracle
667 return new StringBuffer (
668                     tableName.length() + aliasName.length() + 1)
669                     .append(aliasName)
670                     .append(" ")
671                     .append(tableName)
672                     .toString();
673         }
674
675         return tableName;
676     }
677
678     /**
679      * Checks if the Tablename tableName is already contained in a from clause.
680      * If tableName and the tablenames in fromClause are generated by
681      * getTablenameForFromClause(String, Criteria), (which they usually are),
682      * then different aliases for the same table are treated
683      * as different tables: E.g.
684      * fromClauseContainsTableName(fromClause, "table_a a") returns false if
685      * fromClause contains only another alias for table_a ,
686      * e.g. "table_a aa" and the unaliased tablename "table_a".
687      * Special case: If tableName is null, true is returned.
688      * @param fromClause a list containing only elements of type.
689      * Query.FromElement
690      * @param tableName the tablename to check
691      * @return if the Tablename tableName is already contained in a from clause.
692      * If tableName is null, true is returned.
693      */
694     public static boolean fromClauseContainsTableName(
695             final UniqueList fromClause,
696             final String tableName)
697     {
698         if (tableName == null)
699         {
700             // usually this function is called to see if tableName should be
701 // added to the fromClause. As null should not be added,
702 // true is returned.
703 return true;
704         }
705         for (Iterator it = fromClause.iterator(); it.hasNext();)
706         {
707             Query.FromElement fromElement
708                     = (Query.FromElement) it.next();
709             if (tableName.equals(fromElement.getTableName()))
710             {
711                 return true;
712             }
713         }
714         return false;
715     }
716
717     /**
718      * adds a table to the from clause of a query, if it is not already
719      * contained there.
720      * @param tableOrAliasName the name of a table
721      * or the alias for a table
722      * @param criteria a criteria object to resolve a possible alias
723      * @param query the query where the the tablename should be added
724      * to the from clause
725      * @return the table in the from clause which represents the
726      * supplied tableOrAliasName
727      */
728     private static String addTableToFromClause(
729             final String tableName,
730             final Criteria criteria,
731             Query query)
732     {
733         String tableNameForFromClause
734                 = getTableNameForFromClause(tableName, criteria);
735
736         UniqueList queryFromClause = query.getFromClause();
737
738         // it is important that this piece of code is executed AFTER
739 // the joins are processed
740 if (!fromClauseContainsTableName(
741             queryFromClause,
742             tableNameForFromClause))
743         {
744             Query.FromElement fromElement
745                     = new Query.FromElement(
746                             tableNameForFromClause, null, null);
747             queryFromClause.add(fromElement);
748         }
749         return tableNameForFromClause;
750     }
751
752     /**
753      * Inner Interface that defines the Callback method for
754      * the Table creation loop.
755      */
756     public interface TableCallback
757     {
758         /**
759          * Callback Method for getTableSet()
760          *
761          * @param tables The current table name
762          * @param key The current criterion key.
763          * @param crit The Criteria used in getTableSet()
764          */
765         void process(Set tables, String key, Criteria crit);
766     }
767
768     /**
769      * Inner Interface that defines the Callback method for
770      * the buildQuery Criterion evaluation
771      */
772     public interface QueryCallback
773     {
774         /**
775          * The callback for building a query String
776          *
777          * @param criterion The current criterion
778          * @param params The parameter list passed to buildQueryString()
779          * @return WHERE SQL fragment for this criterion
780          */
781         String process(Criterion criterion, List params);
782     }
783
784 }
785

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags