KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > jodd > db > orm > sqlgen > DbSqlTemplate


1 // Copyright (c) 2003-2007, Jodd Team (jodd.sf.net). All Rights Reserved.
2

3 package jodd.db.orm.sqlgen;
4
5 import jodd.db.orm.DbEntityDescriptor;
6 import jodd.db.orm.DbOrm;
7 import jodd.db.orm.DbOrmException;
8 import jodd.util.ClassLoaderUtil;
9 import jodd.util.StringUtil;
10 import jodd.util.ArraysUtil;
11 import jodd.bean.BeanUtil;
12
13 import java.util.HashMap JavaDoc;
14 import java.util.LinkedHashMap JavaDoc;
15 import java.util.Map JavaDoc;
16
17
18 /**
19  * SQL templates provides object-oriented and dynamic sql queries. Using sql templates and its macros makes possible to
20  * write SQL query without specifying table and column names, but using their Java references. Using textual macros
21  * may not sound well, however, it provides significantly better performances then to parse complete sql string,
22  * while still having POSQ (plain old Sql query). Moreover, macros are designed in sich way that by simply
23  * removing them, in most cases the result will be executable sql query.
24  *
25  * <p>
26  * All macro names have a '<b>$</b>' sign as a prefix. Colum reference macro is different than others since it uses
27  * the '<b>$</b>' just as a prefix for its name. Therefore, this character <b>must</b> be escaped if it is used
28  * anywhere else in the sql query. Of course, if prepared statements are used, then (probably:) no escaping is
29  * necessary.
30  *
31  * <p>
32  * This is a base class that provides macros for: tables ({@link #parseTables(String)}),
33  * columns list ({@link #parseColumns(String)}) and column reference ({@link #parseReferences(String)}).
34  * These three macros are essential and may be used to build any sql query. Of course, it is not necessary to use
35  * macros, even a mix between standard sql and object-oriented sql is possible.
36  *
37  * <p>
38  * The parsing order of these basic macros is important. First tables must be resolved, to get all table references.
39  * Then column references has to be resolved, since it has different macro specification. Column list
40  * (and all other block macros) can be parsed afterwards, in any order.
41  *
42  * <p>
43  * For enhanced version of templates see {@link DbDynamicSqlTemplate}.
44  */

45 public class DbSqlTemplate implements DbSqlGenerator {
46
47     // ---------------------------------------------------------------- main
48

49     protected String JavaDoc template; // string template
50
protected DbOrm dbOrm; // db orm reference
51
protected Map JavaDoc<String JavaDoc, Object JavaDoc> parameters; // sql parameters
52

53     // ---------------------------------------------------------------- behaviour
54

55     public static enum ColumnAliasType {
56         TABLE_NAME(1),
57         TABLE_REFERENCE(2),
58         COLUMN_CODE(3);
59         int value;
60         ColumnAliasType(int aliasType) {
61             this.value = aliasType;
62         }
63     }
64
65     /**
66      * Default value for generating column aliases.
67      * @see #columnAliases
68      */

69     public static boolean DEFAULT_COLUMN_ALIAS = false;
70
71     /**
72      * Default column alias type, when they are used.
73      */

74     public static ColumnAliasType DEFAULT_COLUMN_ALIAS_TYPE = ColumnAliasType.COLUMN_CODE;
75
76     /**
77      * Default value for escaping block macros for all new instances.
78      * @see #escape(boolean)
79      */

80     public static boolean DEFAULT_ESCAPE_MACROS = false;
81
82     protected boolean columnAliases;
83     protected ColumnAliasType columnAliasesType;
84     protected boolean escape;
85
86     /**
87      * Specifies if column aliases should be created during the generation
88      * of column list {@link #parseColumns(String)};
89      */

90     public DbSqlTemplate columnAliases(boolean aliases) {
91         this.columnAliases = aliases;
92         return this;
93     }
94
95     /**
96      * Specifies column {@link #columnAliases(boolean)} type and implicitly turns on generation of column aliases.
97      * There are no much reasons for changing default aliases type during the application execution.
98      */

99     public DbSqlTemplate setColumnAliasesType(ColumnAliasType aliasesType) {
100         this.columnAliasesType = aliasesType;
101         this.columnAliases = true;
102         return this;
103     }
104
105     /**
106      * Specifies if escaping character is used. By default, escaping character is ignored, since
107      * this gives somewhat better performances. In case of parsing problems, escaping can be turned on.
108      * Moreover, there is a public static variable that defines this behaviour globally.
109      */

110     public DbSqlTemplate escape(boolean escape) {
111         this.escape = escape;
112         return this;
113     }
114
115
116     // ---------------------------------------------------------------- ctors
117

118     public DbSqlTemplate(String JavaDoc template) {
119         this(template, DbOrm.getInstance());
120     }
121
122     public DbSqlTemplate(String JavaDoc template, DbOrm dbOrm) {
123         this.template = template;
124         this.dbOrm = dbOrm;
125         this.parameters = new LinkedHashMap JavaDoc<String JavaDoc, Object JavaDoc>();
126         this.columnAliases = DEFAULT_COLUMN_ALIAS;
127         this.escape = DEFAULT_ESCAPE_MACROS;
128         this.columnAliasesType = DEFAULT_COLUMN_ALIAS_TYPE;
129     }
130
131     // ---------------------------------------------------------------- references
132

133     /**
134      * Map of object references that are used in templates.
135      */

136     protected Map JavaDoc<String JavaDoc, Object JavaDoc> references;
137
138     /**
139      * Adds new object reference that are used in template.
140      * Special case is when entity type is specified as an object references.
141      */

142     public DbSqlTemplate use(String JavaDoc name, Object JavaDoc value) {
143         if (references == null) {
144             references = new HashMap JavaDoc<String JavaDoc, Object JavaDoc>();
145         }
146         Object JavaDoc oldValue = references.put(name, value);
147         if (oldValue != null) {
148             throw new DbOrmException("Object reference with the same name '" + name + "' already exists.");
149         }
150         return this;
151     }
152
153
154     // ---------------------------------------------------------------- tables
155

156     /**
157      * Table references map. Table reference may be:
158      * <ul>
159      * <li>table alias</li>
160      * <li>simple type name, if no alias specified</li>
161      * <li>object reference, if object reference is used and no alias specified</li>
162      * </ul>
163      */

164     protected Map JavaDoc<String JavaDoc, DbEntityDescriptor> tablesRefs;
165
166     /**
167      * Internal map for speeding up class loading.
168      */

169     protected static Map JavaDoc<String JavaDoc, Class JavaDoc> loadedClasses;
170
171
172     /**
173      * Resolves table macros, allowing using types for table names. Table definition in table macro
174      * specifies a <i>table reference</i> that will be used by other macros when they need to reference a table.
175      *
176      * <p>
177      * The most basic way to define a table is by its class name. In this case, table reference is
178      * a simple class name of specified type. {@link jodd.db.orm.DbOrm} may specify common package prefix
179      * that will be appended before table name only if it starts with a dot ('<b>.</b>'). Examples:<br>
180      * <code>$T{foo.boo.Zoo}</code> will define a reference 'Zoo' and generates: '<code>ZOO Zoo</code>'.<br>
181      * <code>$T{.Zoo}</code> act as above, if package prefix is set to '<code>foo.boo</code>'.
182      *
183      * <p>
184      * As in sql, table definition may contains table alias, which then will be used as a table reference.
185      * Example:<br>
186      * <code>$T{foo.boo.Zoo z}</code> will define a reference 'z' and generates '<code>ZOO z</code>'.<br>
187      *
188      * <p>
189      * Another way to define a table is by using object references, added by {@link #use(String, Object)}.
190      * In this case entity type is objects type (if not already instance of <code>Class</code>). When no explicit
191      * alias is specified, generated table will be aliased with its object reference.
192      *
193      * <p>
194      * When table alias is set to minus '<b>-</b>' then no aliases will be generated (useful for INSERT queries).
195      * This violates the sql standard, but it is used rare,
196      *
197      * <p>
198      * Table macros <b>must</b> be resolved first, before all other macros!
199      *
200      * <p>
201      * Overview:<br>
202      * <ul>
203      * <li>$T{className} : TABLE_NAME className </li>
204      * <li>$T{objectRef} : TABLE_NAME objectRef</li>
205      * <li>$T{anything -} : TABLE_NAME</li>
206      * <li>$T{anything alias} : TABLE_NAME alias</li>
207      * </ul>
208      */

209     public String JavaDoc parseTables(String JavaDoc template) {
210         StringBuilder JavaDoc result = new StringBuilder JavaDoc(template.length());
211         tablesRefs = new HashMap JavaDoc<String JavaDoc, DbEntityDescriptor>();
212         while (true) {
213             String JavaDoc allTables = nextRegion(result, template, "$T{", "}");
214             if (allTables == null) {
215                 break;
216             }
217             String JavaDoc[] tables = StringUtil.split(allTables, ",");
218             for (int i = 0; i < tables.length; i++) {
219                 //noinspection NonConstantStringShouldBeStringBuffer
220
String JavaDoc tableRef = tables[i].trim();
221                 String JavaDoc tableAlias = null;
222
223                 int spaceNdx = tableRef.indexOf(' '); // get table alias if exist
224
if (spaceNdx != -1) {
225                     tableAlias = tableRef.substring(spaceNdx + 1).trim();
226                     tableRef = tableRef.substring(0, spaceNdx);
227                 }
228                 if ((tableRef.length() > 0) && (tableRef.charAt(0) == '.')) { // add package prefix if needed.
229
String JavaDoc packagePrefix = dbOrm.getPackagePrefix();
230                     if (packagePrefix != null) {
231                         tableRef = packagePrefix + tableRef;
232                     }
233                 }
234
235                 Class JavaDoc type = null;
236
237                 if (references != null) { // try to resolve type from references
238
Object JavaDoc data = references.get(tableRef);
239                     if (data != null) {
240                         if (data instanceof Class JavaDoc) {
241                             type = (Class JavaDoc) data;
242                         } else {
243                             type = data.getClass();
244                         }
245                         if (tableAlias == null) {
246                             tableAlias = tableRef;
247                         }
248                     }
249                 }
250
251                 // try to load type if still unknown
252
if (type == null) {
253                     if (loadedClasses == null) {
254                         loadedClasses = new HashMap JavaDoc<String JavaDoc, Class JavaDoc>();
255                     }
256                     type = loadedClasses.get(tableRef);
257                     if (type == null) {
258                         try {
259                             type = ClassLoaderUtil.loadClass(tableRef, DbSqlTemplate.class);
260                             loadedClasses.put(tableRef, type);
261                         } catch (ClassNotFoundException JavaDoc cnfex) {
262                             throw new DbOrmException("Unable to resolve table reference '" + tableRef + "'.", cnfex);
263                         }
264                     }
265                 }
266
267                 DbEntityDescriptor ded = dbOrm.lookup(type);
268                 String JavaDoc tableName = ded.getTableName();
269                 if (tableAlias != null) {
270                     if (tableAlias.equals("-") == true) {
271                         tablesRefs.put(tableRef, ded);
272                         tableRef = null;
273                     } else {
274                         tablesRefs.put(tableAlias, ded);
275                         tableRef = tableAlias;
276                     }
277                 } else {
278                     tableRef = type.getSimpleName();
279                     tablesRefs.put(tableRef, ded);
280                 }
281
282
283                 // generate
284
if (i > 0) {
285                     result.append(',').append(' ');
286                 }
287                 result.append(tableName);
288                 if (tableRef != null) {
289                     result.append(' ').append(tableRef);
290                 }
291             }
292         }
293         return result.toString();
294     }
295
296     // ---------------------------------------------------------------- columns
297

298     /**
299      * Column or table aliases.
300      */

301     protected Map JavaDoc<String JavaDoc, String JavaDoc[]> columnData;
302
303     /**
304      * Column counter for COLUMN_CODE column alias type.
305      */

306     protected int columnCount = 0;
307
308     /**
309      * Resolves columns lists macros used in SELECT queries. Columns are defined as comma-separated
310      * list of column reference. Column reference consist of table reference and property name.
311      * When columns (i.e. properties) are specified explicitly, this macro just resolves column references
312      * to column names. This is equals to column reference macro ($) except this macro may generate aliases.
313      *
314      * <p>
315      * This macro also expands asterisk column groups to list of all availiable types columns.
316      * Table reference may be either a table alias or object reference.
317      * When just a table reference is specified (no properties or asterisks), macro will generate simple
318      * comma-separated column list, <b>without</b> any aliases and prefixes.
319      *
320      * <p>
321      * Macro optionally defines column aliases. There are several way how column aliases can be defined:
322      * 'TABLE_NAME$COLUMN_NAME', 'tableRef$COLUMN_NAME' or 'col_no_' (default). All these column alias types are
323      * supported by {@link jodd.db.orm.mapper.ResultSetMapper}. This setting should be global for whole application.
324      * It is important which column alias type to use, since the first one is the most informative, but gives
325      * the longest column alias names, and database may complain about its size (e.g. oracle doesn't allow aliases
326      * longer then 30 characters).
327      *
328      * <p>
329      * Generating column aliases may helps when database doesn't support table names for column data
330      * in result sets meta data (such as Oracle), althought this is not neccessary.
331      *
332      * <p>
333      * When group column reference is specified (using asteriks or just reference name) and when table reference
334      * is an object reference, only non-null fields will be read. Adding a plus sign (<b>+</b>) in front of reference
335      * will give all properties (including <code>null</code> ones), without object parsing.
336      *
337      * <p>
338      * Overview:<br>
339      * <ul>
340      * <li>$C{ref.id} : ref.ID (as ALIAS)</li>
341      * <li>$C{ref.*} : ref.ID (as ALIAS), ref.NAME (as ALIAS),...</li>
342      * <li>$C{ref} : ID, NAME - no alias generation!</li>
343      * <li>$C{+objref.*} : dynamic mode, all columns are included</li>
344      * <li>$C{+objref} : dynamic mode, all columns are included</li>
345      * </ul>
346      */

347     protected String JavaDoc parseColumns(String JavaDoc template) {
348         StringBuilder JavaDoc result = new StringBuilder JavaDoc(template.length());
349         while (true) {
350             String JavaDoc allColumns = nextRegion(result, template, "$C{", "}");
351             if (allColumns == null) {
352                 break;
353             }
354             String JavaDoc[] columns = StringUtil.split(allColumns, ",");
355             for (int i = 0; i < columns.length; i++) {
356                 String JavaDoc column = columns[i].trim();
357                 String JavaDoc tableRef; // table reference (prefix)
358
String JavaDoc tableName = null; // table name for aliases
359

360                 boolean resolveObject = true;
361                 if (column.startsWith("+") == true) {
362                     resolveObject = false;
363                     column = column.substring(1);
364                 }
365
366                 int dotNdx = column.indexOf('.');
367                 if (dotNdx != -1) {
368                     // TABLE REFERENCE EXIST
369
tableRef = column.substring(0, dotNdx); // get table ref (prefix)
370
column = column.substring(dotNdx + 1);
371                     
372                     DbEntityDescriptor ded = tablesRefs.get(tableRef);
373                     if (ded == null) {
374                         throw new DbOrmException("Table reference '" + tableRef + "' not found for column '"
375                                     + tableRef + '.' + column + "'.");
376                     }
377                     if (columnAliases == true) {
378                         tableName = ded.getTableName();
379                         switch (columnAliasesType) {
380                             case TABLE_REFERENCE:
381                             case COLUMN_CODE:
382                                 if (columnData == null) {
383                                     columnData = new HashMap JavaDoc<String JavaDoc, String JavaDoc[]>();
384                                 }
385                                 break;
386                         }
387                     }
388
389                     // asterisks
390
if (column.equals("*") == true) {
391                         String JavaDoc[] columnList = ded.getColumns();
392                         if (resolveObject == true) { // resolve objects
393
Object JavaDoc object = null;
394                             if (references != null) {
395                                 object = references.get(tableRef);
396                             }
397                             if ((object != null) && (object instanceof Class JavaDoc == false)) {
398                                 String JavaDoc properties[] = ded.getProperties();
399                                 String JavaDoc[] resultList = new String JavaDoc[columnList.length];
400                                 int size = 0;
401                                 for (int j = 0; j < properties.length; j++) {
402                                     String JavaDoc property = properties[j];
403                                     Object JavaDoc value = BeanUtil.getDeclaredProperty(object, property);
404                                     if (value == null) {
405                                         continue;
406                                     }
407                                     resultList[size++] = columnList[j];
408                                 }
409                                 columnList = ArraysUtil.resize(resultList, size);
410                             }
411                         }
412                         for (int j = 0; j < columnList.length; j++) {
413                             appendColumnName(result, tableRef, tableName, columnList[j], i + j);
414                         }
415                         continue;
416                     }
417                     column = ded.getColumnName(column);
418                     if (column == null) {
419                         throw new DbOrmException("Unable to find property for column reference '" + columns[i].trim() + "'.");
420                     }
421                     appendColumnName(result, tableRef, tableName, column, i);
422                 } else {
423                     // NO TABLE REFERENCE
424
DbEntityDescriptor ded = tablesRefs.get(column);
425                     if (ded == null) {
426                         throw new DbOrmException("Table reference '" + column + "' not found.");
427                     }
428                     String JavaDoc[] columnList = ded.getColumns();
429                     if (resolveObject == true) { // resolve objects
430
Object JavaDoc object = null;
431                         if (references != null) {
432                             object = references.get(column);
433                         }
434                         if ((object != null) && (object instanceof Class JavaDoc == false)) {
435                             String JavaDoc properties[] = ded.getProperties();
436                             String JavaDoc[] resultList = new String JavaDoc[columnList.length];
437                             int size = 0;
438                             for (int j = 0; j < properties.length; j++) {
439                                 String JavaDoc property = properties[j];
440                                 Object JavaDoc value = BeanUtil.getDeclaredProperty(object, property);
441                                 if (value == null) {
442                                     continue;
443                                 }
444                                 resultList[size++] = columnList[j];
445                             }
446                             columnList = ArraysUtil.resize(resultList, size);
447                         }
448                     }
449                     for (int j = 0; j < columnList.length; j++) {
450                         String JavaDoc col = columnList[j];
451                         if (j > 0) {
452                             result.append(',').append(' ');
453                         }
454                         result.append(col);
455 // if (generateAliases == true) {
456
// result.append(" as ").append(ded.getTableName()).append(col);
457
// }
458
}
459                 }
460             }
461         }
462         return result.toString();
463     }
464
465     /**
466      * Simply appends column name with optional table reference and alias.
467      */

468     protected void appendColumnName(StringBuilder JavaDoc result, String JavaDoc tableRef, String JavaDoc tableName, String JavaDoc column, int i) {
469         if (i > 0) {
470             result.append(',').append(' ');
471         }
472         if (tableRef != null) {
473             result.append(tableRef).append('.');
474         }
475         result.append(column);
476         if (tableName != null) {
477             result.append(" as ");
478             switch (columnAliasesType) {
479                 case TABLE_NAME:
480                     result.append(tableName).append(dbOrm.getColumnAliasSeparator()).append(column);
481                     break;
482                 case TABLE_REFERENCE:
483                     columnData.put(tableRef, new String JavaDoc[] {tableName});
484                     result.append(tableRef).append(dbOrm.getColumnAliasSeparator()).append(column);
485                     break;
486                 case COLUMN_CODE:
487                     String JavaDoc code = "col_" + Integer.toString(columnCount++) + '_';
488                     columnData.put(code, new String JavaDoc[] {tableName, column});
489                     result.append(code);
490                     break;
491             }
492         }
493     }
494
495
496     // ---------------------------------------------------------------- references
497

498     /**
499      * Parse column references that are defined with table reference and property name.
500      * The result is table reference with column name. Since this macro is the most used one,
501      * it uses only a '$' in front of column reference. Escaping '$' is always turned on for this macro.
502      */

503     public String JavaDoc parseReferences(String JavaDoc template) {
504         StringBuilder JavaDoc result = new StringBuilder JavaDoc(template.length());
505         int templateLen = template.length();
506         int lastNdx = 0;
507         while (true) {
508             int ndx = template.indexOf('$', lastNdx);
509             if (ndx == -1) {
510                 result.append(template.substring(lastNdx));
511                 break;
512             }
513             if ((ndx < templateLen - 2) && (template.charAt(ndx + 2) == '{')) { // other macro found
514
ndx += 2;
515                     result.append(template.substring(lastNdx, ndx));
516                     lastNdx = ndx;
517                     continue;
518             }
519
520             if ((ndx > 0) && (template.charAt(ndx - 1) == '\\')) { // is it escaped
521
result.append(template.substring(lastNdx, ndx - 1)).append('$');
522                 lastNdx = ndx + 1;
523                 continue;
524             }
525
526             result.append(template.substring(lastNdx, ndx));
527             ndx++;
528             
529             int endNdx = ndx; // find macro end
530
while (endNdx < templateLen) {
531                 char c = template.charAt(endNdx);
532                 if ((c != '.') && (Character.isLetter(c) == false)) {
533                     break;
534                 }
535                 endNdx++;
536             }
537             String JavaDoc column = template.substring(ndx, endNdx);
538             lastNdx = endNdx;
539
540             // macro found, go on
541
String JavaDoc tableRef = null;
542             int dotNdx = column.indexOf('.');
543             if (dotNdx != -1) {
544                 tableRef = column.substring(0, dotNdx);
545                 column = column.substring(dotNdx + 1);
546
547                 DbEntityDescriptor ded = tablesRefs.get(tableRef);
548                 if (ded == null) {
549                     throw new DbOrmException("Unable to resolve column reference '" + tableRef + '.' + column + "'.");
550                 }
551                 column = ded.getColumnName(column);
552             }
553             if (tableRef != null) {
554                 result.append(tableRef).append('.');
555             }
556             result.append(column);
557         }
558         return result.toString();
559     }
560
561
562     // ---------------------------------------------------------------- region walker
563

564     protected int[] colNdx;
565     protected char escapeChar = '\\';
566     protected String JavaDoc escapeLeftBoundary;
567     protected String JavaDoc escapeRightBoundary;
568
569     protected String JavaDoc nextRegion(StringBuilder JavaDoc destination, String JavaDoc template, String JavaDoc leftBoundary, String JavaDoc rightBoundary) {
570         return escape == true ?
571                 nextRegionWithEscape(destination, template, leftBoundary, rightBoundary)
572                 :
573                 nextRegionNoEscape(destination, template, leftBoundary, rightBoundary);
574     }
575
576     protected String JavaDoc nextRegionWithEscape(StringBuilder JavaDoc destination, String JavaDoc template, String JavaDoc leftBoundary, String JavaDoc rightBoundary) {
577         int ndx = 0;
578         if (colNdx != null) {
579             ndx = colNdx[3];
580         } else {
581             escapeLeftBoundary = escapeChar + leftBoundary;
582             escapeRightBoundary = escapeChar + rightBoundary;
583         }
584         colNdx = StringUtil.indexOfRegion(template, leftBoundary, rightBoundary, escapeChar, ndx);
585         if (colNdx == null) {
586             String JavaDoc rest = template.substring(ndx);
587             destination.append(StringUtil.replace(rest, escapeLeftBoundary, leftBoundary));
588             return null;
589         }
590
591         String JavaDoc rest = template.substring(ndx, colNdx[0]);
592         destination.append(StringUtil.replace(rest, escapeLeftBoundary, leftBoundary));
593         String JavaDoc result = template.substring(colNdx[1], colNdx[2]).trim();
594         result = StringUtil.replace(result, escapeRightBoundary, rightBoundary);
595         return result;
596     }
597
598     protected String JavaDoc nextRegionNoEscape(StringBuilder JavaDoc destination, String JavaDoc template, String JavaDoc leftBoundary, String JavaDoc rightBoundary) {
599         int ndx = 0;
600         if (colNdx != null) {
601             ndx = colNdx[3];
602         } else {
603             escapeLeftBoundary = escapeChar + leftBoundary;
604             escapeRightBoundary = escapeChar + rightBoundary;
605         }
606         colNdx = StringUtil.indexOfRegion(template, leftBoundary, rightBoundary, escapeChar, ndx);
607         if (colNdx == null) {
608             destination.append(template.substring(ndx));
609             return null;
610         }
611
612         destination.append(template.substring(ndx, colNdx[0]));
613         return template.substring(colNdx[1], colNdx[2]).trim();
614     }
615
616
617     // ---------------------------------------------------------------- interface
618

619     public String JavaDoc generateQuery() {
620         return parseColumns(parseReferences(parseTables(template)));
621     }
622
623     public Map JavaDoc<String JavaDoc, Object JavaDoc> getQueryParameters() {
624         return parameters;
625     }
626
627     public Map JavaDoc<String JavaDoc, String JavaDoc[]> getColumnData() {
628         return columnData;
629     }
630 }
631
Popular Tags