001: package org.julp.misc.search;
002:
003: import java.io.*;
004: import java.lang.reflect.Method;
005: import java.util.*;
006: import org.jdom.*;
007: import org.jdom.input.*;
008: import org.jdom.xpath.*;
009: import java.net.URL;
010:
011: public class XPathSQLQueryReader implements java.io.Serializable,
012: Cloneable {
013:
014: protected String select;
015: protected String from;
016: protected String where;
017: protected String joins;
018: protected String groupBy;
019: protected String having;
020: protected String orderBy;
021: protected String query;
022: protected String queryId = "";
023: protected String queryFilePath;
024: protected String executable;
025: protected URL queryURL;
026: protected Reader reader;
027: protected Document doc;
028: protected boolean reloadDocument = false;
029: protected boolean distinct = false;
030: protected boolean ansiJoin = false;
031: // if user selected column for adhoc than use this to add tables join(s)
032: protected Map columnConditionMappings;
033: // if user selected column for adhoc than use this to add tables join(s) (non-ansi joins)
034: protected Map columnTablesMapping;
035: // list of column for adhoc
036: protected Set adhocColumns;
037: protected Element ansiJoinElement;
038: protected Element selectElement;
039: protected Element fromElement;
040: protected Element joinsElement;
041: protected Element whereElement;
042: protected Element groupByElement;
043: protected Element havingElement;
044: protected Element orderByElement;
045: protected Element executableElement;
046: protected boolean debug;
047:
048: public XPathSQLQueryReader() {
049: if (Boolean.getBoolean("debug-julp")
050: || Boolean.getBoolean("debug-" + getClass().getName())) {
051: debug = true;
052: }
053: }
054:
055: public void reset() {
056: if (ansiJoinElement != null) {
057: ansiJoin = true;
058: } else {
059: ansiJoin = false;
060: }
061: String attrib = selectElement.getAttributeValue("distinct");
062: if (attrib != null && attrib.equals("true")) {
063: distinct = true;
064: } else {
065: distinct = false;
066: }
067: select = selectElement.getText();
068: from = fromElement.getText();
069: joins = joinsElement.getText();
070: where = whereElement.getText();
071: groupBy = groupByElement.getText();
072: having = havingElement.getText();
073: orderBy = orderByElement.getText();
074: if (columnTablesMapping != null)
075: columnTablesMapping.clear();
076: if (columnConditionMappings != null)
077: columnConditionMappings.clear();
078: adhocColumns = null;
079: }
080:
081: public void resetAll() {
082: from = null;
083: where = null;
084: joins = null;
085: groupBy = null;
086: having = null;
087: orderBy = null;
088: queryId = "";
089: distinct = false;
090: ansiJoin = false;
091: if (columnConditionMappings != null)
092: columnConditionMappings.clear();
093: if (columnTablesMapping != null)
094: columnTablesMapping.clear();
095: adhocColumns = null;
096: executable = null;
097: }
098:
099: public void loadExecutable(String queryId) {
100: if (debug)
101: System.out
102: .println("julp ============= "
103: + new java.util.Date() + " "
104: + this .getClass()
105: + "::loadExecutable()::queryId: " + queryId
106: + " \n");
107: if (!this .queryId.equals(queryId)) {
108: reloadDocument = true;
109: }
110: if (reloadDocument) {
111: resetAll();
112: }
113: if (this .queryId.equals(queryId)) {
114: return;
115: }
116:
117: this .queryId = queryId;
118: try {
119: SAXBuilder builder = new SAXBuilder();
120: if (doc == null || reloadDocument) {
121: if (queryFilePath != null) {
122: doc = builder.build(new File(queryFilePath));
123: } else if (queryURL != null) {
124: doc = builder.build(queryURL);
125: } else if (reader != null) {
126: doc = builder.build(reader);
127: } else {
128: throw new IOException("Queries source is missing");
129: }
130: }
131: executableElement = (Element) XPath.newInstance(
132: "//executable[@id='" + queryId + "']")
133: .selectSingleNode(doc);
134: executable = executableElement.getText();
135: } catch (IOException ioe) {
136: throw new RuntimeException(ioe);
137: } catch (JDOMException jdome) {
138: throw new RuntimeException(jdome);
139: }
140: }
141:
142: public void loadExecutable(String queryId, boolean reloadDocument) {
143: this .reloadDocument = reloadDocument;
144: this .loadExecutable(queryId);
145: }
146:
147: public void loadQuery(String queryId, boolean reloadDocument) {
148: this .reloadDocument = reloadDocument;
149: this .loadQuery(queryId);
150: }
151:
152: public void loadQuery(String queryId) {
153: if (debug)
154: System.out.println("julp ============= "
155: + new java.util.Date() + " " + this .getClass()
156: + "::loadQuery()::queryId: " + queryId + " \n");
157: if (!this .queryId.equals(queryId)) {
158: //reloadDocument = true;
159: resetAll();
160: }
161: if (reloadDocument) {
162: resetAll();
163: }
164: if (this .queryId.equals(queryId)) {
165: reset();
166: return;
167: }
168:
169: this .queryId = queryId;
170: try {
171: SAXBuilder builder = new SAXBuilder();
172: if (doc == null || reloadDocument) {
173: if (queryFilePath != null) {
174: doc = builder.build(new File(queryFilePath));
175: } else if (queryURL != null) {
176: doc = builder.build(queryURL);
177: } else if (reader != null) {
178: doc = builder.build(reader);
179: } else {
180: throw new IOException("Queries source is missing");
181: }
182: }
183: Element queryElement = (Element) XPath.newInstance(
184: "//query[@id='" + queryId + "']").selectSingleNode(
185: doc);
186: if (queryElement == null) {
187: throw new IllegalArgumentException("Query Id: \""
188: + queryId + "\" does not exist");
189: }
190:
191: ansiJoinElement = (Element) XPath.newInstance(
192: "//query[@id='" + queryId + "']/ansi_join")
193: .selectSingleNode(doc);
194: if (ansiJoinElement != null) {
195: ansiJoin = true;
196: } else {
197: ansiJoin = false;
198: }
199: selectElement = (Element) XPath.newInstance(
200: "//query[@id='" + queryId + "']/select")
201: .selectSingleNode(doc);
202: String attrib = selectElement.getAttributeValue("distinct");
203: if (attrib != null && attrib.equals("true")) {
204: distinct = true;
205: } else {
206: distinct = false;
207: }
208: select = selectElement.getText();
209:
210: fromElement = (Element) XPath.newInstance(
211: "//query[@id='" + queryId + "']/from")
212: .selectSingleNode(doc);
213: if (fromElement != null) {
214: from = fromElement.getText();
215: }
216:
217: if (isAnsiJoin()) {
218: joinsElement = (Element) XPath.newInstance(
219: "//query[@id='" + queryId + "']/joins")
220: .selectSingleNode(doc);
221: joins = joinsElement.getText();
222: }
223:
224: whereElement = (Element) XPath.newInstance(
225: "//query[@id='" + queryId + "']/where")
226: .selectSingleNode(doc);
227: if (whereElement != null) {
228: where = whereElement.getText();
229: }
230:
231: groupByElement = (Element) XPath.newInstance(
232: "//query[@id='" + queryId + "']/group_by")
233: .selectSingleNode(doc);
234: if (groupByElement != null) {
235: groupBy = groupByElement.getText();
236: }
237:
238: havingElement = (Element) XPath.newInstance(
239: "//query[@id='" + queryId + "']/having")
240: .selectSingleNode(doc);
241: if (havingElement != null) {
242: having = havingElement.getText();
243: }
244: orderByElement = (Element) XPath.newInstance(
245: "//query[@id='" + queryId + "']/order_by")
246: .selectSingleNode(doc);
247: if (orderByElement != null) {
248: orderBy = orderByElement.getText();
249: }
250: List adhocMappings = XPath.newInstance(
251: "//query[@id='" + queryId + "']/adhoc_mappings/*")
252: .selectNodes(doc);
253: if (adhocMappings != null && !adhocMappings.isEmpty()) {
254: columnConditionMappings = new HashMap();
255: columnTablesMapping = new HashMap();
256: Iterator adhocMappingsIter = adhocMappings.iterator();
257: while (adhocMappingsIter.hasNext()) {
258: Element adhocElement = (Element) adhocMappingsIter
259: .next();
260: Element conditionsElement = adhocElement
261: .getChild("conditions");
262: String conditions = conditionsElement.getText();
263: Element columnsElement = adhocElement
264: .getChild("columns");
265: List columns = columnsElement.getChildren();
266: Iterator columnsIter = columns.iterator();
267: while (columnsIter.hasNext()) {
268: String column = ((Element) columnsIter.next())
269: .getText();
270: columnConditionMappings.put(column, conditions);
271: if (!isAnsiJoin()) {
272: Element adhocTablesElement = adhocElement
273: .getChild("tables");
274: if (adhocTablesElement != null) {
275: String tables = adhocTablesElement
276: .getText();
277: columnTablesMapping.put(column, tables);
278: }
279: }
280: }
281: }
282: }
283: } catch (IOException ioe) {
284: ioe.printStackTrace();
285: throw new RuntimeException(ioe);
286: } catch (JDOMException jdome) {
287: jdome.printStackTrace();
288: throw new RuntimeException(jdome);
289: }
290: }
291:
292: public java.net.URL getQueryURL() {
293: return queryURL;
294: }
295:
296: public void setQueryURL(java.net.URL queryURL) {
297: if (debug)
298: System.out.println("julp ============= "
299: + new java.util.Date() + " " + this .getClass()
300: + "::setQueryURL()::queryURL: " + queryURL + " \n");
301: if (this .queryURL == null || !this .queryURL.equals(queryURL)) {
302: setReloadDocument(true);
303: }
304: this .queryFilePath = null;
305: this .reader = null;
306: this .queryURL = queryURL;
307: }
308:
309: public java.lang.String getQueryFilePath() {
310: return queryFilePath;
311: }
312:
313: public void setQueryFilePath(java.lang.String queryFilePath) {
314: if (debug)
315: System.out.println("julp ============= "
316: + new java.util.Date() + " " + this .getClass()
317: + "::setQueryFilePath()::queryFilePath: "
318: + queryFilePath + " \n");
319: if (this .queryFilePath == null
320: || !this .queryFilePath.equals(queryFilePath)) {
321: setReloadDocument(true);
322: }
323: this .queryURL = null;
324: this .reader = null;
325: this .queryFilePath = queryFilePath;
326: }
327:
328: public Reader getReader() {
329: return reader;
330: }
331:
332: public void setReader(Reader reader) {
333: if (debug)
334: System.out.println("julp ============= "
335: + new java.util.Date() + " " + this .getClass()
336: + "::setReader()::reader: " + reader + " \n");
337: if (this .reader == null || !this .reader.equals(reader)) {
338: setReloadDocument(true);
339: }
340: this .queryURL = null;
341: this .queryFilePath = null;
342: this .reader = reader;
343: }
344:
345: public String getGroupBy() {
346: return (groupBy == null ? "" : groupBy);
347: }
348:
349: public void setGroupBy(String groupBy) {
350: this .groupBy = groupBy;
351: }
352:
353: public String getOrderBy() {
354: return (orderBy == null ? "" : orderBy);
355: }
356:
357: public void setOrderBy(String orderBy) {
358: this .orderBy = orderBy;
359: }
360:
361: public String getWhere() {
362: if (adhocColumns != null) {
363: if (!isAnsiJoin()) {
364: // add "dynamic" WHERE condition(s) to "static", remove dups
365: Set columnConditions = new HashSet();
366: Iterator adhocColumnsIter = adhocColumns.iterator();
367: while (adhocColumnsIter.hasNext()) {
368: String column = (String) adhocColumnsIter.next();
369: String conditions = getColumnConditionMappings(column);
370: if (conditions != null
371: && !conditions.trim().equals("")) {
372: columnConditions.add(conditions.trim());
373: }
374: }
375: StringBuffer sb3 = new StringBuffer();
376: Iterator columnConditionsIter = columnConditions
377: .iterator();
378: while (columnConditionsIter.hasNext()) {
379: String value = (String) columnConditionsIter.next();
380: if (value != null && !value.trim().equals("")) {
381: sb3.append(value).append(" AND\n ");
382: }
383: }
384: if (sb3.length() > 0) {
385: sb3.setLength(sb3.length() - 5);
386: }
387: if (where == null || where.trim().equals("")) {
388: if (sb3.length() > 0) {
389: where = sb3.toString().trim();
390: }
391: } else {
392: if (sb3.length() > 0) {
393: where = where.trim() + " AND\n "
394: + sb3.toString().trim();
395: } else {
396: //where.trim();
397: }
398: }
399: }
400: }
401: return (where == null ? "" : where.trim());
402: }
403:
404: public void setWhere(String where) {
405: this .where = where;
406: }
407:
408: public String getHaving() {
409: return (having == null ? "" : having);
410: }
411:
412: public void setHaving(String having) {
413: this .having = having;
414: }
415:
416: public String getFrom() {
417: if (adhocColumns != null) {
418: // add "dynamic" FROM condition(s) to "static", remove dups
419: StringBuffer sb1 = new StringBuffer();
420: if (from != null) {
421: sb1.append(from);
422: }
423: Iterator adhocColumnsIter = adhocColumns.iterator();
424: while (adhocColumnsIter.hasNext()) {
425: Object adhocTables = getColumnTablesMapping((String) adhocColumnsIter
426: .next());
427: if (adhocTables != null) {
428: sb1.append(", ").append(adhocTables);
429: }
430: }
431: StringTokenizer st = new StringTokenizer(sb1.toString(),
432: ",", false);
433: Set tableNames = new HashSet();
434: while (st.hasMoreTokens()) {
435: String tableName = st.nextToken();
436: tableNames.add(tableName.trim());
437: }
438: StringBuffer sb2 = new StringBuffer();
439: Iterator tablesNameIter = tableNames.iterator();
440: while (tablesNameIter.hasNext()) {
441: sb2.append(tablesNameIter.next()).append(", ");
442: }
443: int idx = sb2.lastIndexOf(",");
444: if (idx > -1) {
445: sb2.deleteCharAt(idx);
446: from = sb2.toString();
447: }
448: }
449: return (from == null ? "" : from);
450: }
451:
452: public void setFrom(String from) {
453: this .from = from;
454: }
455:
456: public boolean isReloadDocument() {
457: return reloadDocument;
458: }
459:
460: public void setReloadDocument(boolean reloadDocument) {
461: this .reloadDocument = reloadDocument;
462: }
463:
464: public boolean isDistinct() {
465: return distinct;
466: }
467:
468: public void setDistinct(boolean distinct) {
469: this .distinct = distinct;
470: }
471:
472: public String getJoins() {
473: if (adhocColumns != null) {
474: if (isAnsiJoin()) {
475: Set columnConditions = new HashSet();
476: Iterator adhocColumnsIter = adhocColumns.iterator();
477: while (adhocColumnsIter.hasNext()) {
478: String column = (String) adhocColumnsIter.next();
479: String conditions = getColumnConditionMappings(column);
480: if (conditions != null
481: && !conditions.trim().equals("")) {
482: columnConditions.add(conditions.trim());
483: }
484: }
485: StringBuffer sb3 = new StringBuffer();
486: Iterator columnConditionsIter = columnConditions
487: .iterator();
488: while (columnConditionsIter.hasNext()) {
489: String value = (String) columnConditionsIter.next();
490: if (value != null && !value.trim().equals("")) {
491: sb3.append(value.trim()).append(" AND\n ");
492: }
493: }
494: if (sb3.length() > 0) {
495: sb3.setLength(sb3.length() - 5);
496: }
497: if (joins == null || joins.trim().equals("")) {
498: joins = sb3.toString();
499: } else {
500: joins = joins.trim() + " " + sb3.toString();
501: }
502: }
503: }
504: return (joins == null ? "" : joins);
505: }
506:
507: public void setJoins(String joins) {
508: this .joins = joins;
509: }
510:
511: public void setQuery(java.lang.String query) {
512: this .query = query;
513: }
514:
515: public String getQuery() {
516: return this .query;
517: }
518:
519: /**
520: * Getter for property select.
521: * @return Value of property select.
522: */
523: public java.lang.String getSelect() {
524: if (isDistinct()) {
525: return " DISTINCT " + select;
526: }
527: return select;
528: }
529:
530: /**
531: * Setter for property select.
532: * @param select New value of property select.
533: */
534: public void setSelect(java.lang.String select) {
535: this .select = select;
536: }
537:
538: /**
539: * Getter for property ansiJoin.
540: * @return Value of property ansiJoin.
541: */
542: public boolean isAnsiJoin() {
543: return ansiJoin;
544: }
545:
546: /**
547: * Setter for property ansiJoin.
548: * @param ansiJoin New value of property ansiJoin.
549: */
550: public void setAnsiJoin(boolean ansiJoin) {
551: this .ansiJoin = ansiJoin;
552: }
553:
554: /**
555: * Getter for property columnConditionMappings.
556: * @return Value of property columnConditionMappings.
557: */
558: public java.util.Map getColumnConditionMappings() {
559: return columnConditionMappings;
560: }
561:
562: public String getColumnConditionMappings(String columnName) {
563: return (columnConditionMappings.containsKey(columnName) ? (String) columnConditionMappings
564: .get(columnName)
565: : "");
566: }
567:
568: /**
569: * Setter for property columnConditionMappings.
570: * @param columnConditionMappings New value of property columnConditionMappings.
571: */
572: public void setColumnConditionMappings(
573: java.util.Map columnConditionMappings) {
574: this .columnConditionMappings = columnConditionMappings;
575: }
576:
577: public void setColumnConditionMappings(String columnName,
578: String conditions) {
579: this .columnConditionMappings.put(columnName, conditions);
580: }
581:
582: /**
583: * Getter for property columnTablesMapping.
584: * @return Value of property columnTablesMapping.
585: */
586: public java.util.Map getColumnTablesMapping() {
587: return columnTablesMapping;
588: }
589:
590: public String getColumnTablesMapping(String columnName) {
591: return (String) columnTablesMapping.get(columnName);
592: }
593:
594: /**
595: * Setter for property columnTablesMapping.
596: * @param columnTablesMapping New value of property columnTablesMapping.
597: */
598: public void setColumnTablesMapping(java.util.Map columnTablesMapping) {
599: this .columnTablesMapping = columnTablesMapping;
600: }
601:
602: public void setColumnTablesMapping(String columnName, String tables) {
603: this .columnTablesMapping.put(columnName, tables);
604: }
605:
606: /**
607: * Getter for property adhocColumns.
608: * @return Value of property adhocColumns.
609: */
610: public java.util.Set getAdhocColumns() {
611: return adhocColumns;
612: }
613:
614: /**
615: * Setter for property adhocColumns.
616: * @param adhocColumns New value of property adhocColumns.
617: */
618: public void setAdhocColumns(java.util.Set adhocColumns) {
619: this .adhocColumns = adhocColumns;
620: }
621:
622: public Element getExecutableElement() {
623: return executableElement;
624: }
625:
626: public void setExecutableElement(Element executableElement) {
627: this .executableElement = executableElement;
628: }
629:
630: public String getExecutable() {
631: return executable;
632: }
633:
634: public void setExecutable(String executable) {
635: this .executable = executable;
636: }
637:
638: public boolean isDebug() {
639: return debug;
640: }
641:
642: public void setDebug(boolean debug) {
643: this .debug = debug;
644: }
645:
646: public String toString() {
647: Object[] EMPTY_READ_ARG = new Object[0];
648: StringBuffer sb = new StringBuffer();
649: Object value = null;
650: Method[] methods = getClass().getMethods();
651: for (int i = 0; i < methods.length; i++) {
652: String methodName = methods[i].getName();
653: if (methodName.equals("") || methodName.equals("getClass")) {
654: continue;
655: }
656: if ((methodName.startsWith("get") || methodName
657: .startsWith("is"))
658: && methods[i].getParameterTypes().length == 0) {
659: try {
660: value = methods[i].invoke(this , EMPTY_READ_ARG);
661: } catch (Throwable t) {
662: continue;
663: }
664: String fieldFirstChar = "";
665: if (methodName.startsWith("is")) {
666: fieldFirstChar = methodName.substring(2, 3)
667: .toLowerCase();
668: sb.append(fieldFirstChar);
669: sb.append(methodName.substring(3));
670: } else if (methodName.startsWith("get")) {
671: fieldFirstChar = methodName.substring(3, 4)
672: .toLowerCase();
673: sb.append(fieldFirstChar);
674: sb.append(methodName.substring(4));
675: }
676: sb.append("=");
677: sb.append((value == null) ? "" : value);
678: sb.append("&");
679: }
680: }
681: return sb.toString();
682: }
683: }
|