001: // Copyright (c) 2003-2007, Jodd Team (jodd.sf.net). All Rights Reserved.
002:
003: package jodd.db.orm.sqlgen;
004:
005: import jodd.db.orm.DbEntityDescriptor;
006: import jodd.db.orm.DbOrm;
007: import jodd.db.orm.DbOrmException;
008: import jodd.util.ClassLoaderUtil;
009: import jodd.util.StringUtil;
010: import jodd.util.ArraysUtil;
011: import jodd.bean.BeanUtil;
012:
013: import java.util.HashMap;
014: import java.util.LinkedHashMap;
015: import java.util.Map;
016:
017: /**
018: * SQL templates provides object-oriented and dynamic sql queries. Using sql templates and its macros makes possible to
019: * write SQL query without specifying table and column names, but using their Java references. Using textual macros
020: * may not sound well, however, it provides significantly better performances then to parse complete sql string,
021: * while still having POSQ (plain old Sql query). Moreover, macros are designed in sich way that by simply
022: * removing them, in most cases the result will be executable sql query.
023: *
024: * <p>
025: * All macro names have a '<b>$</b>' sign as a prefix. Colum reference macro is different than others since it uses
026: * the '<b>$</b>' just as a prefix for its name. Therefore, this character <b>must</b> be escaped if it is used
027: * anywhere else in the sql query. Of course, if prepared statements are used, then (probably:) no escaping is
028: * necessary.
029: *
030: * <p>
031: * This is a base class that provides macros for: tables ({@link #parseTables(String)}),
032: * columns list ({@link #parseColumns(String)}) and column reference ({@link #parseReferences(String)}).
033: * These three macros are essential and may be used to build any sql query. Of course, it is not necessary to use
034: * macros, even a mix between standard sql and object-oriented sql is possible.
035: *
036: * <p>
037: * The parsing order of these basic macros is important. First tables must be resolved, to get all table references.
038: * Then column references has to be resolved, since it has different macro specification. Column list
039: * (and all other block macros) can be parsed afterwards, in any order.
040: *
041: * <p>
042: * For enhanced version of templates see {@link DbDynamicSqlTemplate}.
043: */
044: public class DbSqlTemplate implements DbSqlGenerator {
045:
046: // ---------------------------------------------------------------- main
047:
048: protected String template; // string template
049: protected DbOrm dbOrm; // db orm reference
050: protected Map<String, Object> parameters; // sql parameters
051:
052: // ---------------------------------------------------------------- behaviour
053:
054: public static enum ColumnAliasType {
055: TABLE_NAME(1), TABLE_REFERENCE(2), COLUMN_CODE(3);
056: int value;
057:
058: ColumnAliasType(int aliasType) {
059: this .value = aliasType;
060: }
061: }
062:
063: /**
064: * Default value for generating column aliases.
065: * @see #columnAliases
066: */
067: public static boolean DEFAULT_COLUMN_ALIAS;
068:
069: /**
070: * Default column alias type, when they are used.
071: */
072: public static ColumnAliasType DEFAULT_COLUMN_ALIAS_TYPE = ColumnAliasType.COLUMN_CODE;
073:
074: /**
075: * Default value for escaping block macros for all new instances.
076: * @see #escape(boolean)
077: */
078: public static boolean DEFAULT_ESCAPE_MACROS;
079:
080: protected boolean columnAliases;
081: protected ColumnAliasType columnAliasesType;
082: protected boolean escape;
083:
084: /**
085: * Specifies if column aliases should be created during the generation
086: * of column list {@link #parseColumns(String)};
087: */
088: public DbSqlTemplate columnAliases(boolean aliases) {
089: this .columnAliases = aliases;
090: return this ;
091: }
092:
093: /**
094: * Specifies column {@link #columnAliases(boolean)} type and implicitly turns on generation of column aliases.
095: * There are no much reasons for changing default aliases type during the application execution.
096: */
097: public DbSqlTemplate setColumnAliasesType(
098: ColumnAliasType aliasesType) {
099: this .columnAliasesType = aliasesType;
100: this .columnAliases = true;
101: return this ;
102: }
103:
104: /**
105: * Specifies if escaping character is used. By default, escaping character is ignored, since
106: * this gives somewhat better performances. In case of parsing problems, escaping can be turned on.
107: * Moreover, there is a public static variable that defines this behaviour globally.
108: */
109: public DbSqlTemplate escape(boolean escape) {
110: this .escape = escape;
111: return this ;
112: }
113:
114: // ---------------------------------------------------------------- ctors
115:
116: public DbSqlTemplate(String template) {
117: this (template, DbOrm.getInstance());
118: }
119:
120: public DbSqlTemplate(String template, DbOrm dbOrm) {
121: this .template = template;
122: this .dbOrm = dbOrm;
123: this .parameters = new LinkedHashMap<String, Object>();
124: this .columnAliases = DEFAULT_COLUMN_ALIAS;
125: this .escape = DEFAULT_ESCAPE_MACROS;
126: this .columnAliasesType = DEFAULT_COLUMN_ALIAS_TYPE;
127: }
128:
129: // ---------------------------------------------------------------- references
130:
131: /**
132: * Map of object references that are used in templates.
133: */
134: protected Map<String, Object> references;
135:
136: /**
137: * Adds new object reference that are used in template.
138: * Special case is when entity type is specified as an object references.
139: */
140: public DbSqlTemplate use(String name, Object value) {
141: if (references == null) {
142: references = new HashMap<String, Object>();
143: }
144: Object oldValue = references.put(name, value);
145: if (oldValue != null) {
146: throw new DbOrmException(
147: "Object reference with the same name '" + name
148: + "' already exists.");
149: }
150: return this ;
151: }
152:
153: // ---------------------------------------------------------------- tables
154:
155: /**
156: * Table references map. Table reference may be:
157: * <ul>
158: * <li>table alias</li>
159: * <li>simple type name, if no alias specified</li>
160: * <li>object reference, if object reference is used and no alias specified</li>
161: * </ul>
162: */
163: protected Map<String, DbEntityDescriptor> tablesRefs;
164:
165: /**
166: * Internal map for speeding up class loading.
167: */
168: protected static Map<String, Class> loadedClasses;
169:
170: /**
171: * Resolves table macros, allowing using types for table names. Table definition in table macro
172: * specifies a <i>table reference</i> that will be used by other macros when they need to reference a table.
173: *
174: * <p>
175: * The most basic way to define a table is by its class name. In this case, table reference is
176: * a simple class name of specified type. {@link jodd.db.orm.DbOrm} may specify common package prefix
177: * that will be appended before table name only if it starts with a dot ('<b>.</b>'). Examples:<br>
178: * <code>$T{foo.boo.Zoo}</code> will define a reference 'Zoo' and generates: '<code>ZOO Zoo</code>'.<br>
179: * <code>$T{.Zoo}</code> act as above, if package prefix is set to '<code>foo.boo</code>'.
180: *
181: * <p>
182: * As in sql, table definition may contains table alias, which then will be used as a table reference.
183: * Example:<br>
184: * <code>$T{foo.boo.Zoo z}</code> will define a reference 'z' and generates '<code>ZOO z</code>'.<br>
185: *
186: * <p>
187: * Another way to define a table is by using object references, added by {@link #use(String, Object)}.
188: * In this case entity type is objects type (if not already instance of <code>Class</code>). When no explicit
189: * alias is specified, generated table will be aliased with its object reference.
190: *
191: * <p>
192: * When table alias is set to minus '<b>-</b>' then no aliases will be generated (useful for INSERT queries).
193: * This violates the sql standard, but it is used rare,
194: *
195: * <p>
196: * Table macros <b>must</b> be resolved first, before all other macros!
197: *
198: * <p>
199: * Overview:<br>
200: * <ul>
201: * <li>$T{className} : TABLE_NAME className </li>
202: * <li>$T{objectRef} : TABLE_NAME objectRef</li>
203: * <li>$T{anything -} : TABLE_NAME</li>
204: * <li>$T{anything alias} : TABLE_NAME alias</li>
205: * </ul>
206: */
207: public String parseTables(String template) {
208: StringBuilder result = new StringBuilder(template.length());
209: tablesRefs = new HashMap<String, DbEntityDescriptor>();
210: while (true) {
211: String allTables = nextRegion(result, template, "$T{", "}");
212: if (allTables == null) {
213: break;
214: }
215: String[] tables = StringUtil.split(allTables, ",");
216: for (int i = 0; i < tables.length; i++) {
217: //noinspection NonConstantStringShouldBeStringBuffer
218: String tableRef = tables[i].trim();
219: String tableAlias = null;
220:
221: int spaceNdx = tableRef.indexOf(' '); // get table alias if exist
222: if (spaceNdx != -1) {
223: tableAlias = tableRef.substring(spaceNdx + 1)
224: .trim();
225: tableRef = tableRef.substring(0, spaceNdx);
226: }
227: if ((tableRef.length() > 0)
228: && (tableRef.charAt(0) == '.')) { // add package prefix if needed.
229: String packagePrefix = dbOrm.getPackagePrefix();
230: if (packagePrefix != null) {
231: tableRef = packagePrefix + tableRef;
232: }
233: }
234:
235: Class type = null;
236:
237: if (references != null) { // try to resolve type from references
238: Object data = references.get(tableRef);
239: if (data != null) {
240: if (data instanceof Class) {
241: type = (Class) 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<String, Class>();
255: }
256: type = loadedClasses.get(tableRef);
257: if (type == null) {
258: try {
259: type = ClassLoaderUtil.loadClass(tableRef,
260: DbSqlTemplate.class);
261: loadedClasses.put(tableRef, type);
262: } catch (ClassNotFoundException cnfex) {
263: throw new DbOrmException(
264: "Unable to resolve table reference '"
265: + tableRef + "'.", cnfex);
266: }
267: }
268: }
269:
270: DbEntityDescriptor ded = dbOrm.lookup(type);
271: String tableName = ded.getTableName();
272: if (tableAlias != null) {
273: if (tableAlias.equals("-") == true) {
274: tablesRefs.put(tableRef, ded);
275: tableRef = null;
276: } else {
277: tablesRefs.put(tableAlias, ded);
278: tableRef = tableAlias;
279: }
280: } else {
281: tableRef = type.getSimpleName();
282: tablesRefs.put(tableRef, ded);
283: }
284:
285: // generate
286: if (i > 0) {
287: result.append(',').append(' ');
288: }
289: result.append(tableName);
290: if (tableRef != null) {
291: result.append(' ').append(tableRef);
292: }
293: }
294: }
295: return result.toString();
296: }
297:
298: // ---------------------------------------------------------------- columns
299:
300: /**
301: * Column or table aliases.
302: */
303: protected Map<String, String[]> columnData;
304:
305: /**
306: * Column counter for COLUMN_CODE column alias type.
307: */
308: protected int columnCount;
309:
310: /**
311: * Resolves columns lists macros used in SELECT queries. Columns are defined as comma-separated
312: * list of column reference. Column reference consist of table reference and property name.
313: * When columns (i.e. properties) are specified explicitly, this macro just resolves column references
314: * to column names. This is equals to column reference macro ($) except this macro may generate aliases.
315: *
316: * <p>
317: * This macro also expands asterisk column groups to list of all availiable types columns.
318: * Table reference may be either a table alias or object reference.
319: * When just a table reference is specified (no properties or asterisks), macro will generate simple
320: * comma-separated column list, <b>without</b> any aliases and prefixes.
321: *
322: * <p>
323: * Macro optionally defines column aliases. There are several way how column aliases can be defined:
324: * 'TABLE_NAME$COLUMN_NAME', 'tableRef$COLUMN_NAME' or 'col_no_' (default). All these column alias types are
325: * supported by {@link jodd.db.orm.mapper.ResultSetMapper}. This setting should be global for whole application.
326: * It is important which column alias type to use, since the first one is the most informative, but gives
327: * the longest column alias names, and database may complain about its size (e.g. oracle doesn't allow aliases
328: * longer then 30 characters).
329: *
330: * <p>
331: * Generating column aliases may helps when database doesn't support table names for column data
332: * in result sets meta data (such as Oracle), althought this is not neccessary.
333: *
334: * <p>
335: * When group column reference is specified (using asteriks or just reference name) and when table reference
336: * is an object reference, only non-null fields will be read. Adding a plus sign (<b>+</b>) in front of reference
337: * will give all properties (including <code>null</code> ones), without object parsing.
338: *
339: * <p>
340: * Overview:<br>
341: * <ul>
342: * <li>$C{ref.id} : ref.ID (as ALIAS)</li>
343: * <li>$C{ref.*} : ref.ID (as ALIAS), ref.NAME (as ALIAS),...</li>
344: * <li>$C{ref} : ID, NAME - no alias generation!</li>
345: * <li>$C{+objref.*} : dynamic mode, all columns are included</li>
346: * <li>$C{+objref} : dynamic mode, all columns are included</li>
347: * </ul>
348: */
349: protected String parseColumns(String template) {
350: StringBuilder result = new StringBuilder(template.length());
351: while (true) {
352: String allColumns = nextRegion(result, template, "$C{", "}");
353: if (allColumns == null) {
354: break;
355: }
356: String[] columns = StringUtil.split(allColumns, ",");
357: for (int i = 0; i < columns.length; i++) {
358: String column = columns[i].trim();
359: String tableRef; // table reference (prefix)
360: String tableName = null; // table name for aliases
361:
362: boolean resolveObject = true;
363: if (column.startsWith("+") == true) {
364: resolveObject = false;
365: column = column.substring(1);
366: }
367:
368: int dotNdx = column.indexOf('.');
369: if (dotNdx != -1) {
370: // TABLE REFERENCE EXIST
371: tableRef = column.substring(0, dotNdx); // get table ref (prefix)
372: column = column.substring(dotNdx + 1);
373:
374: DbEntityDescriptor ded = tablesRefs.get(tableRef);
375: if (ded == null) {
376: throw new DbOrmException("Table reference '"
377: + tableRef + "' not found for column '"
378: + tableRef + '.' + column + "'.");
379: }
380: if (columnAliases == true) {
381: tableName = ded.getTableName();
382: switch (columnAliasesType) {
383: case TABLE_REFERENCE:
384: case COLUMN_CODE:
385: if (columnData == null) {
386: columnData = new HashMap<String, String[]>();
387: }
388: break;
389: }
390: }
391:
392: // asterisks
393: if (column.equals("*") == true) {
394: String[] columnList = ded.getColumns();
395: if (resolveObject == true) { // resolve objects
396: Object object = null;
397: if (references != null) {
398: object = references.get(tableRef);
399: }
400: if ((object != null)
401: && (object instanceof Class == false)) {
402: String properties[] = ded
403: .getProperties();
404: String[] resultList = new String[columnList.length];
405: int size = 0;
406: for (int j = 0; j < properties.length; j++) {
407: String property = properties[j];
408: Object value = BeanUtil
409: .getDeclaredProperty(
410: object, property);
411: if (value == null) {
412: continue;
413: }
414: resultList[size++] = columnList[j];
415: }
416: columnList = ArraysUtil.resize(
417: resultList, size);
418: }
419: }
420: for (int j = 0; j < columnList.length; j++) {
421: appendColumnName(result, tableRef,
422: tableName, columnList[j], i + j);
423: }
424: continue;
425: }
426: column = ded.getColumnName(column);
427: if (column == null) {
428: throw new DbOrmException(
429: "Unable to find property for column reference '"
430: + columns[i].trim() + "'.");
431: }
432: appendColumnName(result, tableRef, tableName,
433: column, i);
434: } else {
435: // NO TABLE REFERENCE
436: DbEntityDescriptor ded = tablesRefs.get(column);
437: if (ded == null) {
438: throw new DbOrmException("Table reference '"
439: + column + "' not found.");
440: }
441: String[] columnList = ded.getColumns();
442: if (resolveObject == true) { // resolve objects
443: Object object = null;
444: if (references != null) {
445: object = references.get(column);
446: }
447: if ((object != null)
448: && (object instanceof Class == false)) {
449: String properties[] = ded.getProperties();
450: String[] resultList = new String[columnList.length];
451: int size = 0;
452: for (int j = 0; j < properties.length; j++) {
453: String property = properties[j];
454: Object value = BeanUtil
455: .getDeclaredProperty(object,
456: property);
457: if (value == null) {
458: continue;
459: }
460: resultList[size++] = columnList[j];
461: }
462: columnList = ArraysUtil.resize(resultList,
463: size);
464: }
465: }
466: for (int j = 0; j < columnList.length; j++) {
467: String col = columnList[j];
468: if (j > 0) {
469: result.append(',').append(' ');
470: }
471: result.append(col);
472: // if (generateAliases == true) {
473: // result.append(" as ").append(ded.getTableName()).append(col);
474: // }
475: }
476: }
477: }
478: }
479: return result.toString();
480: }
481:
482: /**
483: * Simply appends column name with optional table reference and alias.
484: */
485: protected void appendColumnName(StringBuilder result,
486: String tableRef, String tableName, String column, int i) {
487: if (i > 0) {
488: result.append(',').append(' ');
489: }
490: if (tableRef != null) {
491: result.append(tableRef).append('.');
492: }
493: result.append(column);
494: if (tableName != null) {
495: result.append(" as ");
496: switch (columnAliasesType) {
497: case TABLE_NAME:
498: result.append(tableName).append(
499: dbOrm.getColumnAliasSeparator()).append(column);
500: break;
501: case TABLE_REFERENCE:
502: columnData.put(tableRef, new String[] { tableName });
503: result.append(tableRef).append(
504: dbOrm.getColumnAliasSeparator()).append(column);
505: break;
506: case COLUMN_CODE:
507: String code = "col_" + Integer.toString(columnCount++)
508: + '_';
509: columnData
510: .put(code, new String[] { tableName, column });
511: result.append(code);
512: break;
513: }
514: }
515: }
516:
517: // ---------------------------------------------------------------- references
518:
519: /**
520: * Parse column references that are defined with table reference and property name.
521: * The result is table reference with column name. Since this macro is the most used one,
522: * it uses only a '$' in front of column reference. Escaping '$' is always turned on for this macro.
523: */
524: public String parseReferences(String template) {
525: StringBuilder result = new StringBuilder(template.length());
526: int templateLen = template.length();
527: int lastNdx = 0;
528: while (true) {
529: int ndx = template.indexOf('$', lastNdx);
530: if (ndx == -1) {
531: result.append(template.substring(lastNdx));
532: break;
533: }
534: if ((ndx < templateLen - 2)
535: && (template.charAt(ndx + 2) == '{')) { // other macro found
536: ndx += 2;
537: result.append(template.substring(lastNdx, ndx));
538: lastNdx = ndx;
539: continue;
540: }
541:
542: if ((ndx > 0) && (template.charAt(ndx - 1) == '\\')) { // is it escaped
543: result.append(template.substring(lastNdx, ndx - 1))
544: .append('$');
545: lastNdx = ndx + 1;
546: continue;
547: }
548:
549: result.append(template.substring(lastNdx, ndx));
550: ndx++;
551:
552: int endNdx = ndx; // find macro end
553: while (endNdx < templateLen) {
554: char c = template.charAt(endNdx);
555: if ((c != '.') && (Character.isLetter(c) == false)) {
556: break;
557: }
558: endNdx++;
559: }
560: String column = template.substring(ndx, endNdx);
561: lastNdx = endNdx;
562:
563: // macro found, go on
564: String tableRef = null;
565: int dotNdx = column.indexOf('.');
566: if (dotNdx != -1) {
567: tableRef = column.substring(0, dotNdx);
568: column = column.substring(dotNdx + 1);
569:
570: DbEntityDescriptor ded = tablesRefs.get(tableRef);
571: if (ded == null) {
572: throw new DbOrmException(
573: "Unable to resolve column reference '"
574: + tableRef + '.' + column + "'.");
575: }
576: column = ded.getColumnName(column);
577: }
578: if (tableRef != null) {
579: result.append(tableRef).append('.');
580: }
581: result.append(column);
582: }
583: return result.toString();
584: }
585:
586: // ---------------------------------------------------------------- region walker
587:
588: protected int[] colNdx;
589: protected char escapeChar = '\\';
590: protected String escapeLeftBoundary;
591: protected String escapeRightBoundary;
592:
593: protected String nextRegion(StringBuilder destination,
594: String template, String leftBoundary, String rightBoundary) {
595: return escape == true ? nextRegionWithEscape(destination,
596: template, leftBoundary, rightBoundary)
597: : nextRegionNoEscape(destination, template,
598: leftBoundary, rightBoundary);
599: }
600:
601: protected String nextRegionWithEscape(StringBuilder destination,
602: String template, String leftBoundary, String rightBoundary) {
603: int ndx = 0;
604: if (colNdx != null) {
605: ndx = colNdx[3];
606: } else {
607: escapeLeftBoundary = escapeChar + leftBoundary;
608: escapeRightBoundary = escapeChar + rightBoundary;
609: }
610: colNdx = StringUtil.indexOfRegion(template, leftBoundary,
611: rightBoundary, escapeChar, ndx);
612: if (colNdx == null) {
613: String rest = template.substring(ndx);
614: destination.append(StringUtil.replace(rest,
615: escapeLeftBoundary, leftBoundary));
616: return null;
617: }
618:
619: String rest = template.substring(ndx, colNdx[0]);
620: destination.append(StringUtil.replace(rest, escapeLeftBoundary,
621: leftBoundary));
622: String result = template.substring(colNdx[1], colNdx[2]).trim();
623: result = StringUtil.replace(result, escapeRightBoundary,
624: rightBoundary);
625: return result;
626: }
627:
628: protected String nextRegionNoEscape(StringBuilder destination,
629: String template, String leftBoundary, String rightBoundary) {
630: int ndx = 0;
631: if (colNdx != null) {
632: ndx = colNdx[3];
633: } else {
634: escapeLeftBoundary = escapeChar + leftBoundary;
635: escapeRightBoundary = escapeChar + rightBoundary;
636: }
637: colNdx = StringUtil.indexOfRegion(template, leftBoundary,
638: rightBoundary, escapeChar, ndx);
639: if (colNdx == null) {
640: destination.append(template.substring(ndx));
641: return null;
642: }
643:
644: destination.append(template.substring(ndx, colNdx[0]));
645: return template.substring(colNdx[1], colNdx[2]).trim();
646: }
647:
648: // ---------------------------------------------------------------- interface
649:
650: public String generateQuery() {
651: return parseColumns(parseReferences(parseTables(template)));
652: }
653:
654: public Map<String, Object> getQueryParameters() {
655: return parameters;
656: }
657:
658: public Map<String, String[]> getColumnData() {
659: return columnData;
660: }
661:
662: public Map<String, Object> getQueryReferences() {
663: return references;
664: }
665: }
|