001: package simpleorm.core;
002:
003: import simpleorm.properties.*;
004: import java.util.StringTokenizer;
005:
006: /*
007: * Copyright (c) 2002 Southern Cross Software Queensland (SCSQ). All rights
008: * reserved. See COPYRIGHT.txt included in this distribution.
009: */
010:
011: /** Provides the non-SQL query interface. This is still a thin layer
012: on SQL, but provides more run time type checking at the price of
013: slightly longer queries. There is also a very small performance overhead.<p>
014:
015: For example:-
016:
017: <xmp>
018: SResultSet res = Employee.meta.newQuery()
019: .gt(Employee.NAME, "'J'")
020: //.and() // AND is implied.
021: .eq(Employee.DEPARTMENT, myDept)
022: .decending(Employee.NAME) // ie. Order By
023: .execute();
024: while (res.hasNext()) {...
025: </xmp>
026:
027: is equivalent to
028:
029: <xmp>
030: SPreparedStatement stmt = Employee.meta.select(
031: "NAME > 'j' AND DEPT_ID = ?", "NAME DESC");
032: stmt.setInt(1, myDept.getInt(DEPT_ID));
033: SResultSet res = stmt.execute();
034: while (res.hasNext()) {...
035: </xmp>
036:
037: This API is a little unusual in that there is only one SQuery instance
038: created, and each of the methods update its state and then return
039: <code>this</code>.<p>
040:
041: The raw* methods can be used to poke any string into the SQL Where or
042: Order By clauses in the order they appear. But more commonly the raw
043: methods are called by higher level methods such as <code>eq</code> and
044: <code>and</code>.<p>
045:
046: todo Some of this logic should be moved to sdriver, and integrated with its selectSQL etc.<p>
047:
048: */
049:
050: public class SQuery implements SConstants {
051: SRecordMeta record = null;
052:
053: SArrayList joinTables = new SArrayList(2);
054: StringBuffer where = new StringBuffer(100);
055: SArrayList queryParameters = new SArrayList(2);
056: StringBuffer orderBy = new StringBuffer(40);
057: boolean needsConjunction = false;
058: boolean reverseJoin = false;
059: boolean orderByJoinedColumn = false;
060:
061: long sqy_bitSet;
062: SFieldMeta[] selectList = null;
063:
064: SDriver driver = SConnection.getDriver();
065:
066: /** SQueries are created by SRecordMeta.newQuery(). */
067: protected SQuery(SRecordMeta record, long sqy_bitSet,
068: SFieldMeta[] selectList) {
069: this .record = record;
070: this .sqy_bitSet = sqy_bitSet;
071: this .selectList = selectList;
072: }
073:
074: public SRecordMeta getSRecordMeta() {
075: return record;
076: }
077:
078: /**
079: * Return true if this query will use 'SELECT DISTINCT'. This is automatically
080: * added if the following two conditions are true:
081: * <ol>
082: * <li>This query uses a join on a reference FROM another table. For example,
083: * a query on department that has a join to employee (note that without the
084: * 'DISTINCT' keyword, multiple rows would typically be returned for each department)
085: * <li>There are no order-by columns on fields other than the query table. SQL
086: * syntax forces all order-by columns to also be in the select list, if DISTINCT
087: * is used.
088: * </ol>
089: */
090: public boolean isDistinct() {
091: return reverseJoin && !orderByJoinedColumn;
092: }
093:
094: /** Execute the query, set the previously specified query values,
095: and return the result set.*/
096: public SResultSet execute() {
097: return execute(new SPreparedStatement());
098: }
099:
100: public SResultSet execute(SPreparedStatement stmt) {
101: stmt.setJoinTables((SRecordMeta[]) joinTables
102: .toArray(new SRecordMeta[0]));
103: stmt.setDistinct(isDistinct());
104: record.select(where.length() == 0 ? null : where.toString(),
105: orderBy.length() == 0 ? null : orderBy.toString(),
106: sqy_bitSet, selectList, stmt);
107:
108: for (int px = 0; px < queryParameters.size(); px++) {
109: stmt.setObject(px + 1, queryParameters.get(px)); // ## may cause problems some JDBC
110: }
111: return stmt.execute();
112: }
113:
114: /** Add term to the where clase, eg. "BUDGET > ?". Not normally called directly. */
115: public SQuery rawClause(String term) {
116: if (term.trim().equals("(")) {
117: if (needsConjunction)
118: and();
119: }
120:
121: where.append(term);
122: where.append(" ");
123: return this ;
124: }
125:
126: /** Add a clause to the OrderBy statement, eg. "NAME DESC". Commas
127: are added automatically.*/
128: public SQuery rawOrderBy(String orderBy) {
129: if (this .orderBy.length() > 0)
130: this .orderBy.append(", ");
131: this .orderBy.append(orderBy);
132: return this ;
133: }
134:
135: /** Add a clause to the OrderBy statement. Commas
136: are added automatically.*/
137: public SQuery rawOrderBy(SFieldMeta field, boolean ascending) {
138: verifyFieldOkToUse(field);
139:
140: if (field instanceof SFieldReference) {
141: SFieldMeta[] fields = ((SFieldReference) field).foreignKeyFields;
142: for (int ii = 0; ii < fields.length; ii++) {
143: rawOrderBy(fields[ii], ascending);
144: }
145: } else {
146: StringBuffer orderByElement = new StringBuffer();
147:
148: orderByElement.append(field.sRecordMeta
149: .getProperty(STABLE_NAME));
150: orderByElement.append(".");
151: String col = driver.quoteColumn(field
152: .getString(SCOLUMN_NAME));
153: orderByElement.append(col);
154:
155: if (!ascending) {
156: orderByElement.append(" DESC");
157: }
158:
159: rawOrderBy(orderByElement.toString());
160:
161: if (field.sRecordMeta != record) {
162: orderByJoinedColumn = true;
163: }
164: }
165:
166: return this ;
167: }
168:
169: /**
170: * Throws exception if field is not from this table, or any of the joined tables
171: */
172: private void verifyFieldOkToUse(SFieldMeta field) {
173: if (field.sRecordMeta != record
174: && !joinTables.contains(field.sRecordMeta)) {
175: throw new SException.Error("Field " + field
176: + " is not from record " + field.sRecordMeta);
177: }
178: }
179:
180: /** Add a parameter value to the internal array. These will be
181: <code>setObject</code> later after the prepared statement is
182: created but before it is executed.*/
183: public SQuery rawParameter(Object parameter) {
184: queryParameters.add(parameter);
185: return this ;
186: }
187:
188: /** Adds the field's columnName. */
189: public SQuery rawField(SFieldMeta field) {
190: verifyFieldOkToUse(field);
191:
192: where.append(field.sRecordMeta.getProperty(STABLE_NAME));
193: where.append(".");
194: String quoted = driver.quoteColumn(field
195: .getString(SCOLUMN_NAME));
196: where.append(quoted);
197: where.append(" ");
198:
199: return this ;
200: }
201:
202: /** Generates <code>field relop ?</code> and then subsequently sets the
203: parameter value. Eg. <p>
204:
205: <code>fieldRelopParameter(Employee.Name, "=", myName)</code><p>
206: */
207: public SQuery fieldRelopParameter(SFieldMeta field, String relop,
208: Object value) {
209:
210: // If object passed in is really an SFieldMeta, use the method that is appropriate
211: if (value instanceof SFieldMeta)
212: return this .fieldRelopParameter(field, relop,
213: (SFieldMeta) value);
214:
215: if (field instanceof SFieldReference)
216: throw new SException.Error(
217: "Attempt to query reference field " + field);
218:
219: if (needsConjunction)
220: and();
221:
222: rawField(field);
223: rawClause(relop);
224: rawClause("?");
225: rawParameter(value);
226:
227: needsConjunction = true;
228:
229: return this ;
230: }
231:
232: /** Generates <code>field1 relop field2</code>
233:
234: E.g.
235: <code>fieldRelopParameter(Order.QuantityRequired, "=", Order.QuantityReceived)</code><p>
236: Mainly useful for Joins.
237: */
238: public SQuery fieldRelopParameter(SFieldMeta field1, String relop,
239: SFieldMeta field2) {
240:
241: if (field1 instanceof SFieldReference)
242: throw new SException.Error(
243: "Attempt to query reference field " + field1);
244: if (field2 instanceof SFieldReference)
245: throw new SException.Error(
246: "Attempt to query reference field " + field2);
247:
248: if (needsConjunction)
249: and();
250:
251: rawField(field1);
252: rawClause(relop);
253: rawField(field2);
254:
255: needsConjunction = true;
256:
257: return this ;
258: }
259:
260: /** Generates <code>field clause</code>, ie the clause string is poked
261: literally into the query. Eg. <p>
262: <code>fieldQuery(Employee.Name, "= 'Fred'")</code><p>
263:
264: Use fieldRelopParameter instead for
265: parameters determined at run time as blindly concatenating
266: strings is dangerous.
267: */
268: public SQuery fieldQuery(SFieldMeta field, String clause) {
269: if (field instanceof SFieldReference)
270: throw new SException.Error(
271: "Attempt to query reference field " + field);
272:
273: if (needsConjunction)
274: and();
275:
276: rawField(field);
277: rawClause(clause);
278:
279: needsConjunction = true;
280:
281: return this ;
282: }
283:
284: /**
285: * Join to another table, based on the specified reference. <p>
286: *
287: * The INNER join works in both directions. So either reference may
288: * be from a table that is already in the query to a new table, or
289: * from a new table to a table that is already in the query.<p>
290: *
291: * This method updates both the from and where claues. After a call
292: * to this method is made, you can simply refer to fields from the
293: * joined table in subsequent calls to the relative operators such
294: * as eq().<p>
295: *
296: * This provides an alternative to just using subselects. The effect is
297: * almost the same except for inner join issues.<p>
298: *
299: * You can call join() multiple times to join several tables together. This will
300: * work as long as each subsequent join() call makes a connection from the
301: * existing set of tables to a new table.<p>
302: *
303: * A single table may not be joined multiple times, so there can be
304: * no ambiguity as to which join path to use to access a field.<p>
305: *
306: * There is an example in BasicTests.queryTest().<p>
307: */
308: public SQuery join(SFieldReference reference) {
309: SRecordMeta joinTable = null;
310: if (joinTables.contains(reference.sRecordMeta) || // Reference is TO new table
311: reference.sRecordMeta == record) {
312: joinTable = reference.referencedRecord;
313: } else if (joinTables.contains(reference.referencedRecord) || // Reference is FROM new table
314: reference.referencedRecord == record) {
315: joinTable = reference.sRecordMeta;
316:
317: // If a join is made on a reference FROM another table to THIS table, this
318: // opens up the possibility of multiple rows being returned (e.g. if querying
319: // dept, and a join to employee is done, multiple rows would be returned for
320: // each department). To avoid this, we will use 'SELECT DISTINCT'
321: reverseJoin = true;
322: } else {
323: throw new SException.Error(
324: "The following joing reference is not connected "
325: + "to current set of joined tables by either end of the reference: "
326: + reference);
327: }
328:
329: if (joinTables.contains(joinTable) || joinTable.equals(record)) {
330: // Don't join back to self, or to a table already in join list
331: throw new SException.Error("Table " + joinTable
332: + " Cannot be joined twice.");
333: } else {
334: joinTables.add(joinTable);
335:
336: // append joining statements to where clause
337: rawClause("(");
338: eqJoinReference((SFieldReference) reference);
339: rawClause(")");
340: }
341:
342: return this ;
343: }
344:
345: /**
346: * Generate the conjunction of equalities for the foreign key columns recursively.
347: * Used for joins
348: */
349: private void eqJoinReference(SFieldReference ref) {
350: for (int ii = 0; ii < ref.foreignKeyFields.length; ii++) {
351: SFieldMeta fkey = ref.foreignKeyFields[ii];
352: if (fkey instanceof SFieldReference)
353: eqJoinReference((SFieldReference) fkey);
354: else
355: fieldRelopParameter(fkey, "=", fkey.referencedKeyField);
356: }
357: }
358:
359: /** Compares ref with value, recurively. operator is normally "=", but can be "NOT NULL" etc.*/
360: private void opReference(SFieldReference ref,
361: SRecordInstance value, String operator, boolean disjoin,
362: boolean diadic) {
363: for (int rx = 0; rx < ref.foreignKeyFields.length; rx++) {
364: SFieldMeta fkey = ref.foreignKeyFields[rx];
365: if (fkey instanceof SFieldReference) {
366: opReference((SFieldReference) fkey, value, operator,
367: disjoin, diadic);
368: } else {
369: SFieldMeta refed = fkey.referencedKeyField;
370: if (disjoin && needsConjunction)
371: or();
372: if (diadic) {
373: //Object val = value.fieldValues[refed.fieldIndex];
374: Object val = value.getObject(refed);
375: fieldRelopParameter(fkey, operator, val);
376: } else
377: fieldQuery(fkey, operator);
378: }
379: }
380: }
381:
382: SQuery eqNeAux(SFieldMeta field, Object value, boolean isEq) {
383: if (value == null)
384: throw new SException.Error("Use isNull to test for nulls "
385: + field);
386: String op = isEq ? "=" : "<>";
387: if (!(field instanceof SFieldReference)) {
388: return fieldRelopParameter(field, op, value);
389: } else {
390: if (!(value instanceof SRecordInstance))
391: throw new SException.Error("value " + value
392: + " must be an SRecordInstance for reference "
393: + field + " building " + this );
394: SFieldReference refFld = (SFieldReference) field;
395: SRecordMeta refedRec = refFld.referencedRecord;
396: if (((SRecordInstance) value).getMeta() != refedRec)
397: throw new SException.Error("Value " + value
398: + " must be a " + refedRec + " building "
399: + this );
400: rawClause("(");
401: opReference((SFieldReference) field,
402: (SRecordInstance) value, op, !isEq, true);
403: rawClause(")");
404: return this ;
405: }
406: }
407:
408: SQuery nullAux(SFieldMeta field, boolean isNull) {
409: String op = isNull ? "IS NULL" : "IS NOT NULL";
410: if (!(field instanceof SFieldReference)) {
411: return fieldQuery(field, op);
412: } else {
413: SFieldReference refFld = (SFieldReference) field;
414: rawClause("(");
415: opReference((SFieldReference) field, null, op, isNull,
416: false);
417: rawClause(")");
418: return this ;
419: }
420: }
421:
422: /** Normally just adds <code>fieldRelopParameter(field, "=",
423: value)</code>.<p>
424:
425: If field is a reference it recursively expands the foreign
426: keys, and value must be an instance of the same record type.<p>
427:
428: value must not be null, you need the special case IS NULL test.
429: (It would be possible to optimize this here, but what about
430: field == field where one of them is null -- that would be
431: inconsistent.)<p>
432: */
433: public SQuery eq(SFieldMeta field, Object value) {
434: return eqNeAux(field, value, true);
435: }
436:
437: public SQuery eq(SFieldMeta field, int value) {
438: return eq(field, SJSharp.newInteger(value));
439: // Need to create the object to store in the array.
440: // Later could add arrays of int etc.
441: // If only Java had fixnums!
442: }
443:
444: public SQuery eq(SFieldMeta field, long value) {
445: return eq(field, SJSharp.newLong(value));
446: }
447:
448: public SQuery eq(SFieldMeta field, double value) {
449: return eq(field, SJSharp.newDouble(value));
450: }
451:
452: public SQuery eq(SFieldMeta field1, SFieldMeta field2) {
453: return fieldRelopParameter(field1, "=", field2);
454: }
455:
456: /**
457: * True if boolean field == writeFieldValue(value).
458: * Ie. value is converted from bool to "Y"/"N" etc.
459: */
460: public SQuery equivalent(SFieldBoolean field, boolean value) {
461: return eq(field, field.writeFieldValue(value ? Boolean.TRUE
462: : Boolean.FALSE));
463: }
464:
465: /**
466: * shortcut for equivalent(field, true);
467: */
468: public SQuery isTrue(SFieldBoolean field) {
469: return equivalent(field, true);
470: }
471:
472: public SQuery isFalse(SFieldBoolean field) {
473: return equivalent(field, false);
474: }
475:
476: public SQuery ne(SFieldMeta field, int value) {
477: return ne(field, SJSharp.newInteger(value));
478: }
479:
480: /** Just adds <code>fieldRelopParameter(field, "<>", value)</code>.<p>
481:
482: Note that there are few methods <code>ne(SfieldMeta, int)</code>
483: etc. This is because 5 relops * 5 data types would require 25
484: methods! Java 1.5 boxing will (finally) make this unnecessary anyway. */
485: public SQuery ne(SFieldMeta field, Object value) {
486: return eqNeAux(field, value, false);
487: }
488:
489: public SQuery ne(SFieldMeta field, long value) {
490: return ne(field, SJSharp.newLong(value));
491: }
492:
493: public SQuery ne(SFieldMeta field, double value) {
494: return ne(field, SJSharp.newDouble(value));
495: }
496:
497: public SQuery ne(SFieldMeta field1, SFieldMeta field2) {
498: return fieldRelopParameter(field1, "<>", field2);
499: }
500:
501: /** Just adds <code>fieldQuery(field, "IS NULL")</code>*/
502: public SQuery isNull(SFieldMeta field) {
503: return nullAux(field, true);
504: }
505:
506: /** Just adds <code>fieldQuery(field, "IS NULL")</code>*/
507: public SQuery isNotNull(SFieldMeta field) {
508: return nullAux(field, false);
509: }
510:
511: /** Just adds <code>fieldRelopParameter(field, ">", value)</code>*/
512: public SQuery gt(SFieldMeta field, Object value) {
513: return fieldRelopParameter(field, ">", value);
514: }
515:
516: public SQuery gt(SFieldMeta field, int value) {
517: return gt(field, SJSharp.newInteger(value));
518: }
519:
520: public SQuery gt(SFieldMeta field, long value) {
521: return gt(field, SJSharp.newLong(value));
522: }
523:
524: public SQuery gt(SFieldMeta field, double value) {
525: return gt(field, SJSharp.newDouble(value));
526: }
527:
528: public SQuery gt(SFieldMeta field1, SFieldMeta field2) {
529: return fieldRelopParameter(field1, ">", field2);
530: }
531:
532: /** Just adds <code>fieldRelopParameter(field, "<", value)</code>*/
533: public SQuery lt(SFieldMeta field, Object value) {
534: return fieldRelopParameter(field, "<", value);
535: }
536:
537: public SQuery lt(SFieldMeta field, int value) {
538: return lt(field, SJSharp.newInteger(value));
539: }
540:
541: public SQuery lt(SFieldMeta field, long value) {
542: return lt(field, SJSharp.newLong(value));
543: }
544:
545: public SQuery lt(SFieldMeta field, double value) {
546: return lt(field, SJSharp.newDouble(value));
547: }
548:
549: public SQuery lt(SFieldMeta field1, SFieldMeta field2) {
550: return fieldRelopParameter(field1, "<", field2);
551: }
552:
553: /** Just adds <code>fieldRelopParameter(field, "<=", value)</code>*/
554: public SQuery le(SFieldMeta field, Object value) {
555: return fieldRelopParameter(field, "<=", value);
556: }
557:
558: public SQuery le(SFieldMeta field, int value) {
559: return le(field, SJSharp.newInteger(value));
560: }
561:
562: public SQuery le(SFieldMeta field, long value) {
563: return le(field, SJSharp.newLong(value));
564: }
565:
566: public SQuery le(SFieldMeta field, double value) {
567: return le(field, SJSharp.newDouble(value));
568: }
569:
570: public SQuery le(SFieldMeta field1, SFieldMeta field2) {
571: return fieldRelopParameter(field1, "<=", field2);
572: }
573:
574: /** Just adds <code>fieldRelopParameter(field, ">=", value)</code>*/
575: public SQuery ge(SFieldMeta field, Object value) {
576: return fieldRelopParameter(field, ">=", value);
577: }
578:
579: public SQuery ge(SFieldMeta field, int value) {
580: return ge(field, SJSharp.newInteger(value));
581: }
582:
583: public SQuery ge(SFieldMeta field, long value) {
584: return ge(field, SJSharp.newLong(value));
585: }
586:
587: public SQuery ge(SFieldMeta field, double value) {
588: return ge(field, SJSharp.newDouble(value));
589: }
590:
591: public SQuery ge(SFieldMeta field1, SFieldMeta field2) {
592: return fieldRelopParameter(field1, ">=", field2);
593: }
594:
595: /** Use or clause to simulate the in clause */
596: public SQuery in(SFieldMeta field, Object[] values) {
597: preIn(field);
598: for (int i = 0; i < values.length; i++) {
599: oneIn(i, field, values[i]);
600: }
601: rawClause(")");
602: return this ;
603: }
604:
605: /** Use or clause to simulate the in clause */
606: public SQuery in(SFieldMeta field, int[] values) {
607: preIn(field);
608: for (int i = 0; i < values.length; i++) {
609: oneIn(i, field, SJSharp.newInteger(values[i]));
610: }
611: rawClause(")");
612: needsConjunction = true;
613: return this ;
614: }
615:
616: /** value is tokenized into a list of values.
617: */
618: public SQuery in(SFieldMeta field, String value) {
619: preIn(field);
620: StringTokenizer tokens = new StringTokenizer(value);
621: for (int i = 0; tokens.hasMoreTokens(); i++) {
622: oneIn(i, field, tokens.nextToken());
623: }
624: rawClause(")");
625: needsConjunction = true;
626: return this ;
627: }
628:
629: private void preIn(SFieldMeta field) {
630: if (needsConjunction)
631: and();
632: rawField(field);
633: rawClause(" IN (");
634: }
635:
636: private void oneIn(int idx, SFieldMeta field, Object value) {
637: if (idx > 0)
638: rawClause(", ");
639: rawClause("?");
640: rawParameter(value);
641: }
642:
643: /** Just adds <code>fieldRelopParameter(field, "like", value)</code>*/
644: public SQuery like(SFieldMeta field, Object value) {
645: return fieldRelopParameter(field, "like", value);
646: }
647:
648: /**
649: * @deprecated Use the method with only a single argument
650: */
651: public SQuery isNull(SFieldMeta field, Object value) {
652: return isNull(field);
653: }
654:
655: /**
656: * @deprecated Use the method with only a single argument
657: */
658: public SQuery isNotNull(SFieldMeta field, Object value) {
659: return isNotNull(field);
660: }
661:
662: /**
663: * Adds <code>rawClause("AND");
664: * <p> This method is optional. If you call neither and() nor or() between statements
665: * (e.g. eq()), then a call to and() is assumed, and is done for you.
666: * */
667: public SQuery and() {
668: needsConjunction = false;
669: return rawClause("AND");
670: }
671:
672: /** Adds <code>rawClause("OR"); */
673: public SQuery or() {
674: needsConjunction = false;
675: return rawClause("OR");
676: }
677:
678: /** Adds <code>rawClause("NOT"); */
679: public SQuery not() {
680: return rawClause("NOT");
681: }
682:
683: /**
684: * Inserts "(" into the query
685: * @author Pierre Awaragi
686: */
687: public SQuery combineBegin() {
688: return rawClause("(");
689: }
690:
691: /**
692: * Inserts ")" into the query.
693: * @author Pierre Awaragi
694: */
695: public SQuery combineEnd() {
696: return rawClause(")");
697: }
698:
699: /** <code>rawOrderBy(field.columnName)</code> */
700: public SQuery ascending(SFieldMeta field) {
701: return rawOrderBy(field, true);
702: }
703:
704: /** <code>rawOrderBy(field.columnName) DESC</code> */
705: public SQuery descending(SFieldMeta field) {
706: return rawOrderBy(field, false);
707: }
708:
709: public String toString() {
710: return "[SQuery " + record + " " + where + " BY " + orderBy
711: + "]";
712: }
713:
714: /**
715: * Substitute '?' in the sql query with the values in the parameters array.
716: * Used to create meaningful SQL strings for logging purposes ONLY.
717: */
718: static String substitute(String qry, SArrayList parameters) {
719: StringBuffer buffer = new StringBuffer();
720: int fromIndex = 0;
721: for (int ii = 0; ii < parameters.size(); ii++) {
722: int index = qry.indexOf('?', fromIndex);
723: buffer.append(qry.substring(fromIndex, index));
724: buffer.append(quote(parameters.get(ii)));
725: fromIndex = index + 1;
726: }
727:
728: buffer.append(qry.substring(fromIndex));
729:
730: return buffer.toString();
731: }
732:
733: private static String quote(Object parameter) {
734: if (parameter == null) {
735: return "NULL";
736: }
737:
738: if (parameter instanceof Number) {
739: return parameter.toString();
740: }
741:
742: return "'" + parameter + "'";
743: }
744: }
|