001: /*
002: * ====================================================================
003: * JAFFA - Java Application Framework For All
004: *
005: * Copyright (C) 2002 JAFFA Development Group
006: *
007: * This library is free software; you can redistribute it and/or
008: * modify it under the terms of the GNU Lesser General Public
009: * License as published by the Free Software Foundation; either
010: * version 2.1 of the License, or (at your option) any later version.
011: *
012: * This library is distributed in the hope that it will be useful,
013: * but WITHOUT ANY WARRANTY; without even the implied warranty of
014: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
015: * Lesser General Public License for more details.
016: *
017: * You should have received a copy of the GNU Lesser General Public
018: * License along with this library; if not, write to the Free Software
019: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
020: *
021: * Redistribution and use of this software and associated documentation ("Software"),
022: * with or without modification, are permitted provided that the following conditions are met:
023: * 1. Redistributions of source code must retain copyright statements and notices.
024: * Redistributions must also contain a copy of this document.
025: * 2. Redistributions in binary form must reproduce the above copyright notice,
026: * this list of conditions and the following disclaimer in the documentation
027: * and/or other materials provided with the distribution.
028: * 3. The name "JAFFA" must not be used to endorse or promote products derived from
029: * this Software without prior written permission. For written permission,
030: * please contact mail to: jaffagroup@yahoo.com.
031: * 4. Products derived from this Software may not be called "JAFFA" nor may "JAFFA"
032: * appear in their names without prior written permission.
033: * 5. Due credit should be given to the JAFFA Project (http://jaffa.sourceforge.net).
034: *
035: * THIS SOFTWARE IS PROVIDED "AS IS" AND ANY EXPRESSED OR IMPLIED
036: * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
037: * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
038: * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
039: * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
040: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
041: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
042: * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
043: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
044: * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
045: * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
046: * SUCH DAMAGE.
047: * ====================================================================
048: */
049:
050: package org.jaffa.persistence.engines.jdbcengine.querygenerator;
051:
052: import org.apache.log4j.Logger;
053: import java.util.*;
054: import java.io.IOException;
055: import org.jaffa.persistence.Criteria;
056: import org.jaffa.persistence.AtomicCriteria;
057: import org.jaffa.persistence.engines.jdbcengine.configservice.ConfigurationService;
058: import org.jaffa.persistence.engines.jdbcengine.configservice.ClassMetaData;
059: import org.jaffa.persistence.engines.jdbcengine.variants.Variant;
060:
061: /** Use the helper method to generate SQL statements for quering the database.
062: */
063: public class QueryStatementHelper {
064:
065: private static final Logger log = Logger
066: .getLogger(QueryStatementHelper.class);
067:
068: // some constants that are used internally
069: private static final String SELECT = "SELECT";
070: private static final String DISTINCT = "DISTINCT";
071: private static final String FROM = "FROM";
072: private static final String WHERE = "WHERE";
073: private static final String AND = "AND";
074: private static final String OR = "OR";
075: private static final String IS_NULL = "IS NULL";
076: private static final String IS_NOT_NULL = "IS NOT NULL";
077: private static final String LIKE = "LIKE";
078: private static final String ORDER_BY = "ORDER BY";
079: private static final String ASC = "ASC";
080: private static final String DESC = "DESC";
081: private static final String TABLE_NAME_PREFIX = "T";
082:
083: private static Map c_fieldListCache = new WeakHashMap();
084:
085: /** This parses a <code>Criteria</code> object for constructing relevant SQL.
086: * @param criteria the object containing query data.
087: * @param engineType The engine type as defined in init.xml
088: * @throws IOException if any error occurs while extracting the String from the criteria.
089: * @return a String representing SQL statement.
090: */
091: public static String getStatement(Criteria criteria,
092: String engineType) throws IOException {
093: ClassMetaData meta = ConfigurationService.getInstance()
094: .getMetaData(criteria.getTable());
095:
096: // This buffer will hold the SQL
097: StringBuffer buf = new StringBuffer(SELECT);
098:
099: // This will ensure distinct records are returned during JOINs
100: if (criteria.getAggregates() != null
101: && criteria.getAggregates().size() > 0) {
102: buf.append(' ');
103: buf.append(DISTINCT);
104: }
105:
106: // Add the field-list
107: buf.append(' ');
108: buf.append(getFieldList(meta));
109:
110: // Create 2 buffers for holding the From and Where clauses.
111: // And then invoke the recursive 'parse' routine to fill up the 2 buffers
112: StringBuffer fromBuf = new StringBuffer();
113: StringBuffer whereBuf = new StringBuffer();
114: doFromAndWhere(criteria, meta, null, null, null, new Counter(),
115: fromBuf, whereBuf, engineType);
116:
117: // Add the From clause
118: if (fromBuf.length() > 0) {
119: buf.append(' ');
120: buf.append(FROM);
121: buf.append(' ');
122: buf.append(fromBuf.toString());
123: }
124:
125: // Added for supplying Locking 'Hints' used by MS-Sql-Server
126: if (criteria.getLocking() == Criteria.LOCKING_PARANOID)
127: buf
128: .append(' ')
129: .append(
130: Variant
131: .getProperty(
132: engineType,
133: Variant.PROP_LOCK_CONSTRUCT_IN_FROM_SELECT_STATEMENT));
134:
135: // Add the Where clause
136: if (whereBuf.length() > 0) {
137: buf.append(' ');
138: buf.append(WHERE);
139: buf.append(' ');
140: buf.append(whereBuf.toString());
141: }
142:
143: // Append the ordering information, if any
144: Collection orderBys = criteria.getOrderBys();
145: if (orderBys != null && orderBys.size() > 0) {
146: buf.append(' ');
147: buf.append(ORDER_BY);
148: buf.append(' ');
149: boolean firstPass = true;
150: for (Iterator itr = orderBys.iterator(); itr.hasNext();) {
151: if (firstPass)
152: firstPass = false;
153: else
154: buf.append(',');
155: Criteria.OrderBy orderBy = (Criteria.OrderBy) itr
156: .next();
157: buf.append(meta.getTable());
158: buf.append('.');
159: buf
160: .append(meta.getSqlName(orderBy
161: .getOrderByElement()));
162: if (orderBy.getOrdering() == Criteria.ORDER_BY_ASC) {
163: buf.append(' ');
164: buf.append(ASC);
165: } else {
166: buf.append(' ');
167: buf.append(DESC);
168: }
169: }
170: }
171:
172: // check the locking strategy
173: if (criteria.getLocking() == Criteria.LOCKING_PARANOID) {
174: buf.append(' ');
175: buf.append(Variant.getProperty(engineType,
176: Variant.PROP_LOCK_CONSTRUCT_IN_SELECT_STATEMENT));
177: }
178:
179: return buf.toString();
180: }
181:
182: /** Recursive method which parses a <code>Criteria</code> object for constructing relevant SQL.
183: * It fills the 2 input buffers with relevant SQL
184: */
185: private static void doFromAndWhere(Criteria criteria,
186: ClassMetaData meta, Criteria parentCriteria,
187: ClassMetaData parentMeta, String parentTableName,
188: Counter tableCounter, StringBuffer fromBuf,
189: StringBuffer whereBuf, String engineType)
190: throws IOException {
191: String tableName = null;
192: if (tableCounter.getCount() == 0) {
193: tableName = meta.getTable();
194: } else {
195: tableName = TABLE_NAME_PREFIX + tableCounter.getCount();
196: }
197:
198: // Append table name
199: if (fromBuf.length() > 0)
200: fromBuf.append(',');
201: fromBuf.append(meta.getTable());
202: if (!tableName.equals(meta.getTable())) {
203: fromBuf.append(' ');
204: fromBuf.append(tableName);
205: }
206:
207: // Now do the where clause
208: doWhere(criteria, meta, tableName, parentMeta, parentTableName,
209: whereBuf, engineType);
210:
211: // Check for aggregates
212: Collection aggregates = criteria.getAggregates();
213: if (aggregates != null) {
214: for (Iterator itr = aggregates.iterator(); itr.hasNext();) {
215: tableCounter.increment();
216: Criteria aggregate = (Criteria) itr.next();
217: doFromAndWhere(aggregate, ConfigurationService
218: .getInstance()
219: .getMetaData(aggregate.getTable()), criteria,
220: meta, tableName, tableCounter, fromBuf,
221: whereBuf, engineType);
222: }
223: }
224: }
225:
226: private static void doWhere(Criteria criteria, ClassMetaData meta,
227: String tableName, ClassMetaData parentMeta,
228: String parentTableName, StringBuffer whereBuf,
229: String engineType) throws IOException {
230: // Here we handle the join fields
231: Collection inners = criteria.getInners();
232: if (inners != null) {
233: for (Iterator i = inners.iterator(); i.hasNext();) {
234: if (whereBuf.length() > 0) {
235: whereBuf.append(' ');
236: whereBuf.append(AND);
237: whereBuf.append(' ');
238: }
239: Criteria.CriteriaEntry innerCriteriaEntry = (Criteria.CriteriaEntry) i
240: .next();
241: String tfield = meta.getSqlName(innerCriteriaEntry
242: .getName());
243: int operator = innerCriteriaEntry.getOperator();
244: String ofield = parentMeta
245: .getSqlName((String) innerCriteriaEntry
246: .getValue());
247:
248: // Now we need to set the two fields up with their proper prefixes
249: tfield = tableName + '.' + tfield;
250: ofield = parentTableName + '.' + ofield;
251: whereBuf.append(parseDualOperator(tfield, operator,
252: ofield));
253: }
254: }
255:
256: // This handles the normal fields
257: Collection criteraEntries = criteria.getCriteriaEntries();
258: if (criteraEntries != null) {
259: for (Iterator i = criteraEntries.iterator(); i.hasNext();) {
260: Criteria.CriteriaEntry criteriaEntry = (Criteria.CriteriaEntry) i
261: .next();
262: if (whereBuf.length() > 0) {
263: whereBuf.append(' ');
264: if (criteriaEntry.isLogicAND())
265: whereBuf.append(AND);
266: else
267: whereBuf.append(OR);
268: whereBuf.append(' ');
269: }
270: whereBuf.append('(');
271: whereBuf.append(getClause(criteriaEntry, meta,
272: tableName, engineType));
273: whereBuf.append(')');
274: }
275: }
276: }
277:
278: private static String getClause(
279: Criteria.CriteriaEntry criteriaEntry, ClassMetaData meta,
280: String tableName, String engineType) throws IOException {
281: StringBuffer buf = new StringBuffer();
282: if (criteriaEntry instanceof Criteria.AtomicCriteriaEntry) {
283: AtomicCriteria atomicCriteria = ((Criteria.AtomicCriteriaEntry) criteriaEntry)
284: .getEntry();
285: Collection criteriaEntries = atomicCriteria
286: .getCriteriaEntries();
287: if (criteriaEntries != null) {
288: boolean firstPass = true;
289: for (Iterator i = criteriaEntries.iterator(); i
290: .hasNext();) {
291: Criteria.CriteriaEntry ce = (Criteria.CriteriaEntry) i
292: .next();
293: if (firstPass)
294: firstPass = false;
295: else {
296: buf.append(' ');
297: buf.append(logicLookAhead(ce));
298: buf.append(' ');
299: }
300: buf.append('(');
301: buf.append(getClause(ce, meta, tableName,
302: engineType));
303: buf.append(')');
304: }
305: }
306: } else {
307: String name = meta.getSqlName(criteriaEntry.getName());
308: int operator = criteriaEntry.getOperator();
309: if (criteriaEntry.getDual()) {
310: String name2 = meta.getSqlName((String) criteriaEntry
311: .getValue());
312: buf.append(parseDualOperator(tableName + '.' + name,
313: operator, tableName + '.' + name2));
314: } else {
315: Object value = criteriaEntry.getValue();
316: String typeName = meta.getSqlType(criteriaEntry
317: .getName());
318: buf.append(parseOperator(tableName + '.' + name,
319: operator, value, typeName, engineType));
320: }
321: }
322:
323: return buf.toString();
324: }
325:
326: private static String logicLookAhead(
327: Criteria.CriteriaEntry criteriaEntry) {
328: if (criteriaEntry instanceof Criteria.AtomicCriteriaEntry) {
329: AtomicCriteria atomicCriteria = ((Criteria.AtomicCriteriaEntry) criteriaEntry)
330: .getEntry();
331: if (atomicCriteria.getOrLogic())
332: return OR;
333: else
334: return AND;
335: } else {
336: if (criteriaEntry.getLogic() == Criteria.CriteriaEntry.LOGICAL_AND)
337: return AND;
338: else
339: return OR;
340: }
341: }
342:
343: private static String parseOperator(String name, int operator,
344: Object value, String typeName, String engineType)
345: throws IOException {
346: StringBuffer buf = new StringBuffer(name);
347: buf.append(' ');
348: switch (operator) {
349: case Criteria.RELATIONAL_EQUALS:
350: buf.append('=');
351: buf.append(' ');
352: buf.append(DataTranslator.getDml(value, typeName,
353: engineType));
354: break;
355: case Criteria.RELATIONAL_NOT_EQUALS:
356: buf.append('!');
357: buf.append('=');
358: buf.append(' ');
359: buf.append(DataTranslator.getDml(value, typeName,
360: engineType));
361: break;
362: case Criteria.RELATIONAL_GREATER_THAN:
363: buf.append('>');
364: buf.append(' ');
365: buf.append(DataTranslator.getDml(value, typeName,
366: engineType));
367: break;
368: case Criteria.RELATIONAL_GREATER_THAN_EQUAL_TO:
369: buf.append('>');
370: buf.append('=');
371: buf.append(' ');
372: buf.append(DataTranslator.getDml(value, typeName,
373: engineType));
374: break;
375: case Criteria.RELATIONAL_SMALLER_THAN:
376: buf.append('<');
377: buf.append(' ');
378: buf.append(DataTranslator.getDml(value, typeName,
379: engineType));
380: break;
381: case Criteria.RELATIONAL_SMALLER_THAN_EQUAL_TO:
382: buf.append('<');
383: buf.append('=');
384: buf.append(' ');
385: buf.append(DataTranslator.getDml(value, typeName,
386: engineType));
387: break;
388: case Criteria.RELATIONAL_IS_NULL:
389: buf.append(IS_NULL);
390: break;
391: case Criteria.RELATIONAL_IS_NOT_NULL:
392: buf.append(IS_NOT_NULL);
393: break;
394: case Criteria.RELATIONAL_BEGINS_WITH:
395: buf.append(LIKE);
396: buf.append(' ');
397: buf.append(DataTranslator.getBeginsWithDml(value, typeName,
398: engineType));
399: break;
400: case Criteria.RELATIONAL_ENDS_WITH:
401: buf.append(LIKE);
402: buf.append(' ');
403: buf.append(DataTranslator.getEndsWithDml(value, typeName,
404: engineType));
405: break;
406: case Criteria.RELATIONAL_LIKE:
407: buf.append(LIKE);
408: buf.append(' ');
409: buf.append(DataTranslator.getLikeDml(value, typeName,
410: engineType));
411: break;
412: default:
413: String str = "Illegal operator passed - " + operator;
414: log.error(str);
415: throw new IllegalArgumentException(str);
416: }
417: return buf.toString();
418: }
419:
420: private static String parseDualOperator(String name, int operator,
421: String name2) {
422: StringBuffer buf = new StringBuffer(name);
423: buf.append(' ');
424: switch (operator) {
425: case Criteria.RELATIONAL_EQUALS:
426: buf.append('=');
427: break;
428: case Criteria.RELATIONAL_NOT_EQUALS:
429: buf.append('!');
430: buf.append('=');
431: break;
432: case Criteria.RELATIONAL_GREATER_THAN:
433: buf.append('>');
434: break;
435: case Criteria.RELATIONAL_GREATER_THAN_EQUAL_TO:
436: buf.append('>');
437: buf.append('=');
438: break;
439: case Criteria.RELATIONAL_SMALLER_THAN:
440: buf.append('<');
441: break;
442: case Criteria.RELATIONAL_SMALLER_THAN_EQUAL_TO:
443: buf.append('<');
444: buf.append('=');
445: break;
446: default:
447: String str = "Illegal operator passed - " + operator;
448: log.error(str);
449: throw new IllegalArgumentException(str);
450: }
451: buf.append(' ');
452: buf.append(name2);
453: return buf.toString();
454: }
455:
456: private static String getFieldList(ClassMetaData classMetaData) {
457: String fieldList = (String) c_fieldListCache.get(classMetaData
458: .getClassName());
459: if (fieldList == null) {
460: StringBuffer buf = new StringBuffer();
461: boolean first = true;
462: for (Iterator i = classMetaData.getAllKeyFieldNames()
463: .keySet().iterator(); i.hasNext();) {
464: if (first)
465: first = false;
466: else
467: buf.append(',');
468: buf.append(classMetaData.getTable() + '.'
469: + classMetaData.getSqlName((String) i.next()));
470: }
471: for (Iterator i = classMetaData.getAttributes().keySet()
472: .iterator(); i.hasNext();) {
473: if (first)
474: first = false;
475: else
476: buf.append(',');
477: buf.append(classMetaData.getTable() + '.'
478: + classMetaData.getSqlName((String) i.next()));
479: }
480: synchronized (c_fieldListCache) {
481: //check again before inserting into cache
482: fieldList = (String) c_fieldListCache.get(classMetaData
483: .getClassName());
484: if (fieldList == null) {
485: fieldList = buf.toString();
486: c_fieldListCache.put(classMetaData.getClassName(),
487: fieldList);
488: }
489: }
490: }
491: return fieldList;
492: }
493:
494: private static class Counter {
495: int m_value = 0;
496:
497: void increment() {
498: ++m_value;
499: }
500:
501: int getCount() {
502: return m_value;
503: }
504: }
505: }
|