001: // Copyright (c) 2003-2007, Jodd Team (jodd.sf.net). All Rights Reserved.
002:
003: package jodd.db.orm.sqlgen;
004:
005: import jodd.bean.BeanUtil;
006: import jodd.db.orm.DbEntityDescriptor;
007: import jodd.db.orm.DbOrm;
008: import jodd.db.orm.DbOrmException;
009: import jodd.introspector.DefaultIntrospector;
010: import jodd.util.StringUtil;
011:
012: import java.lang.reflect.Field;
013:
014: /**
015: * Enhanced {@link DbSqlTemplate}. Gives some more macros for dynamic queries.
016: *
017: * <p>
018: * Macro references follows this rules:
019: * <ul>
020: * <li>objref : objref.ID=:className.propertyName (only non-null)</li>
021: * <li>+objref : objref.ID=:className.propertyName (including non-null)</li>*
022: * <li>alias.objref : alias.ID=:className.propertyName</li>
023: * <li>.objref : ID=:className.propertyName</li>
024: * </ul>
025: */
026: public class DbDynamicSqlTemplate extends DbSqlTemplate {
027:
028: public DbDynamicSqlTemplate(String template) {
029: this (template, DbOrm.getInstance());
030: }
031:
032: public DbDynamicSqlTemplate(String template, DbOrm dbOrm) {
033: super (template, dbOrm);
034: }
035:
036: // ---------------------------------------------------------------- overrides
037:
038: /**
039: * @see DbSqlTemplate#use(String, Object)
040: */
041: @Override
042: public DbDynamicSqlTemplate use(String name, Object value) {
043: super .use(name, value);
044: return this ;
045: }
046:
047: /**
048: * @see DbSqlTemplate#columnAliases
049: */
050: @Override
051: public DbDynamicSqlTemplate columnAliases(boolean aliases) {
052: super .columnAliases(aliases);
053: return this ;
054: }
055:
056: /**
057: * @see DbSqlTemplate#setColumnAliasesType(jodd.db.orm.sqlgen.DbSqlTemplate.ColumnAliasType)
058: */
059: @Override
060: public DbDynamicSqlTemplate setColumnAliasesType(
061: ColumnAliasType aliasesType) {
062: super .setColumnAliasesType(aliasesType);
063: return this ;
064: }
065:
066: /**
067: * @see DbSqlTemplate#escape(boolean)
068: */
069: @Override
070: public DbDynamicSqlTemplate escape(boolean escape) {
071: super .escape(escape);
072: return this ;
073: }
074:
075: // ---------------------------------------------------------------- where
076:
077: /**
078: * Parses where conditions based on specified object reference. When just object reference
079: * is specified, each generated column will have table name as prefix. Example:<br>
080: * <code>#W{where foo}</code> may generate: '<code>where foo.ID=:foo.id</code>'.<br>
081: * If specified object reference is existing table reference, columns will be prefixed
082: * with table references instead of table names.
083: *
084: * <p>
085: * By adding a table ref prefix to object reference, generated columns names will contain
086: * this table ref (i.e. alias):<br>
087: * <code>#W{where b.foo}</code> may generate: '<code>where b.ID=:foo.id</code>'.<br>
088: * Note that existance of table alias is <b>NOT</b> checked, because it is regular not to use
089: * table macros, if one doesn't want so. When alias is missing, but a 'dot' exists:<br>
090: * <code>#W{where .foo}</code> may generate: '<code>where ID=:foo.id</code>'.<br>
091: *
092: * <p>
093: * If reference starts with '!' sign, finder mode is turned on. Note that there is no sence
094: * to have a finder for columns including non-null ones.
095: *
096: * <p>
097: * Optionally, macro can have an word in front of values list, that specifies
098: * an optional sql keyword that will be inserted only if there is at least one
099: * generated condition.
100: */
101: public String parseWhere(String template) {
102: StringBuilder result = new StringBuilder(template.length());
103: while (true) {
104: String allConditions = nextRegion(result, template, "$W{",
105: "}");
106: if (allConditions == null) {
107: break;
108: }
109: String prefix = null; // get prefix word
110: int spaceNdx = allConditions.indexOf(' ');
111: if (spaceNdx != -1) {
112: prefix = allConditions.substring(0, spaceNdx);
113: allConditions = allConditions.substring(spaceNdx + 1)
114: .trim();
115: }
116:
117: String[] conds = StringUtil.split(allConditions, ",");
118: int count = 0;
119: for (String condition : conds) {
120: condition = condition.trim();
121:
122: boolean useFinders = false;
123: if (condition.startsWith("!") == true) {
124: condition = condition.substring(1);
125: useFinders = true;
126: }
127:
128: String tableAlias; // get optional table alias, will never be null
129: int dotNdx = condition.indexOf('.');
130: if (dotNdx != -1) {
131: tableAlias = condition.substring(0, dotNdx);
132: condition = condition.substring(dotNdx + 1);
133: } else {
134: // if (tablesRefs.get(condition) != null) {
135: // tableAlias = condition;
136: // }
137: tableAlias = condition;
138: }
139:
140: Object data = null;
141: if (references != null) {
142: data = references.get(condition);
143: }
144: if (data == null) {
145: throw new DbOrmException(
146: "Unable to find object reference '"
147: + condition + "'.");
148: }
149: if (useFinders == false) {
150: count = where(result, prefix, data, tableAlias,
151: count);
152: } else {
153: count = whereWithFinders(result, prefix, data,
154: tableAlias, count);
155: }
156: }
157: }
158: return result.toString();
159: }
160:
161: public static String[][] operators = new String[][] {
162: new String[] { "Like", " like " },
163: new String[] { "Greater", ">" },
164: new String[] { "Less", "<" },
165: new String[] { "GreaterEqual", ">=" },
166: new String[] { "LessEqual", "<=" },
167: new String[] { "Not", "<>" }, };
168:
169: protected int where(StringBuilder result, String prefix,
170: Object object, String tableAlias, int count) {
171: Class type = object.getClass();
172: DbEntityDescriptor ded = dbOrm.lookup(type);
173:
174: String className = StringUtil
175: .uncapitalize(type.getSimpleName());
176: String[] allProperties = ded.getProperties();
177: String[] allColumns = ded.getColumns();
178:
179: for (int i = 0; i < allProperties.length; i++) {
180: String property = allProperties[i];
181:
182: Object value = BeanUtil.getDeclaredProperty(object,
183: property);
184: if (value == null) {
185: continue;
186: }
187:
188: // generate
189: String propertyName = className + '.' + property;
190: parameters.put(propertyName, value);
191: if ((count == 0) && (prefix != null)) {
192: result.append(prefix).append(' ');
193: } else {
194: result.append(" and ");
195: }
196: count++;
197: if (tableAlias.length() != 0) {
198: result.append(tableAlias).append('.');
199: }
200: result.append(allColumns[i]).append('=').append(':')
201: .append(propertyName);
202: }
203: return count;
204: }
205:
206: protected int whereWithFinders(StringBuilder result, String prefix,
207: Object object, String tableAlias, int count) {
208: Class type = object.getClass();
209: DbEntityDescriptor ded = dbOrm.lookup(type);
210: String className = StringUtil
211: .uncapitalize(type.getSimpleName());
212:
213: Field[] allFields = DefaultIntrospector.lookup(type)
214: .getAllFields(true);
215:
216: for (Field field : allFields) {
217:
218: String operator = "=";
219: String lookupFieldName = field.getName();
220: for (String[] op : operators) {
221: if (lookupFieldName.endsWith(op[0])) {
222: lookupFieldName = lookupFieldName.substring(0,
223: lookupFieldName.length() - op[0].length());
224: operator = op[1];
225: break;
226: }
227: }
228:
229: // value
230: Object value = BeanUtil.getDeclaredProperty(object, field
231: .getName());
232: if (value == null) {
233: continue;
234: }
235:
236: // column name
237: String columnName = ded.getColumnName(lookupFieldName);
238: if (columnName == null) {
239: continue;
240: }
241:
242: // generate
243: String propertyName = className + '.' + field.getName();
244: parameters.put(propertyName, value);
245: if ((count == 0) && (prefix != null)) {
246: result.append(prefix).append(' ');
247: } else {
248: result.append(" and ");
249: }
250: count++;
251: if (tableAlias.length() != 0) {
252: result.append(tableAlias).append('.');
253: }
254: result.append(columnName).append(operator).append(':')
255: .append(propertyName);
256: }
257: return count;
258: }
259:
260: // ---------------------------------------------------------------- values
261:
262: /**
263: * Returns comma-separated list of columns for specified object reference.
264: * If reference starts with "+" all columns will be returned.
265: */
266: public String parseValues(String template) {
267: StringBuilder result = new StringBuilder(template.length());
268: while (true) {
269: String valueRef = nextRegion(result, template, "$V{", "}");
270: if (valueRef == null) {
271: break;
272: }
273:
274: boolean allProperties = false;
275: if (valueRef.startsWith("+")) {
276: allProperties = true;
277: valueRef = valueRef.substring(1);
278: }
279:
280: Object data = null;
281: if (references != null) {
282: data = references.get(valueRef);
283: }
284: if (data == null) {
285: throw new DbOrmException(
286: "Unable to find object reference '" + valueRef
287: + "'.");
288: }
289:
290: Class type = data.getClass();
291: String className = StringUtil.uncapitalize(type
292: .getSimpleName());
293: DbEntityDescriptor ded = dbOrm.lookup(type);
294: String[] properties = ded.getProperties();
295: int size = 0;
296: for (String property : properties) {
297: Object value = BeanUtil.getDeclaredProperty(data,
298: property);
299: if ((allProperties == false) && (value == null)) {
300: continue;
301: }
302: if (size > 0) {
303: result.append(',').append(' ');
304: }
305: size++;
306: result.append(':');
307: String propertyName = className + '.' + property;
308: result.append(propertyName);
309: parameters.put(propertyName, value);
310: }
311: }
312: return result.toString();
313: }
314:
315: // ---------------------------------------------------------------- update
316:
317: /**
318: * Generates comma-separated assignment pairs for specified object reference.
319: * If reference starts with "+" all columns will be returned.
320: */
321: public String parseUpdate(String template) {
322: StringBuilder result = new StringBuilder(template.length());
323: while (true) {
324: String valueRef = nextRegion(result, template, "$U{", "}");
325: if (valueRef == null) {
326: break;
327: }
328: boolean allProperties = false;
329: if (valueRef.startsWith("+")) {
330: allProperties = true;
331: valueRef = valueRef.substring(1);
332: }
333:
334: String tableRef = null;
335: int dotNdx = valueRef.indexOf('.');
336: if (dotNdx != -1) {
337: tableRef = valueRef.substring(0, dotNdx);
338: valueRef = valueRef.substring(dotNdx + 1);
339: }
340:
341: Object data = null;
342: if (references != null) {
343: data = references.get(valueRef);
344: }
345: if (data == null) {
346: throw new DbOrmException(
347: "Unable to find object reference '" + valueRef
348: + "'.");
349: }
350:
351: Class type = data.getClass();
352: String className = StringUtil.uncapitalize(type
353: .getSimpleName());
354: DbEntityDescriptor ded = dbOrm.lookup(type);
355: String[] properties = ded.getProperties();
356: String[] columns = ded.getColumns();
357: int size = 0;
358: for (int i = 0; i < properties.length; i++) {
359: String property = properties[i];
360: Object value = BeanUtil.getDeclaredProperty(data,
361: property);
362: if ((allProperties == false) && (value == null)) {
363: continue;
364: }
365: if (size > 0) {
366: result.append(',').append(' ');
367: }
368: size++;
369: if (tableRef != null) {
370: if (tableRef.length() != 0) {
371: result.append(tableRef).append('.');
372: }
373: }
374: result.append(columns[i]).append('=');
375: result.append(':');
376: String propertyName = className + '.' + property;
377: result.append(propertyName);
378: parameters.put(propertyName, value);
379: }
380: }
381: return result.toString();
382: }
383:
384: // ---------------------------------------------------------------- interface
385:
386: @Override
387: public String generateQuery() {
388: return parseUpdate(parseValues(parseWhere(super.generateQuery())));
389: }
390:
391: }
|