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: /*
051: * QueryTest.java
052: *
053: * Created on April 1, 2002, 5:47 PM
054: */
055:
056: package org.jaffa.persistence.blackboxtests;
057:
058: import junit.framework.TestCase;
059: import org.jaffa.persistence.domainobjects.*;
060: import org.jaffa.persistence.*;
061: import java.util.*;
062: import org.jaffa.datatypes.DateTime;
063:
064: /** Tests for performing queries through the Jaffa Persistence Engine.
065: *
066: * @author GautamJ
067: */
068: public class QueryTest extends TestCase {
069:
070: private UOW m_uow = null;
071:
072: /** Creates new QueryTest
073: * @param name The name of the test case.
074: */
075: public QueryTest(String name) {
076: super (name);
077: }
078:
079: /** Sets up the fixture, by creating the UOW. This method is called before a test is executed.
080: */
081: protected void setUp() {
082: try {
083: m_uow = new UOW();
084: } catch (Exception e) {
085: e.printStackTrace();
086: fail("Failed to create a UOW: " + e.toString());
087: }
088: }
089:
090: /** Tears down the fixture, by closing the UOW. This method is called after a test is executed.
091: */
092: protected void tearDown() {
093: try {
094: if (m_uow != null)
095: m_uow.rollback();
096: m_uow = null;
097: } catch (Exception e) {
098: e.printStackTrace();
099: fail("Failed to rollback a UOW: " + e.toString());
100: }
101: }
102:
103: /** Tests the sql:
104: * Select * from item where item_id like 'Z-TEST%'
105: * and condition like '%TESTSYCD%'
106: * and part = 'Z-TESTPART-01’
107: * and sc is not null
108: * and qty > 1 and qty < 3
109: * and key_ref like '%TEST-KEY-REF'
110: * order by item_id
111: * This should return 3 item records
112: */
113: public void testBasicQuery() {
114: try {
115: Criteria c = new Criteria();
116: c.setTable(ItemMeta.getName());
117: c.addCriteria(ItemMeta.ITEM_ID,
118: Criteria.RELATIONAL_BEGINS_WITH, "Z-TEST");
119: c.addCriteria(ItemMeta.CONDITION, Criteria.RELATIONAL_LIKE,
120: "TESTSYCD");
121: c.addCriteria(ItemMeta.PART, "Z-TESTPART-01");
122: c.addCriteria(ItemMeta.SC, Criteria.RELATIONAL_IS_NOT_NULL);
123: c.addCriteria(ItemMeta.QTY,
124: Criteria.RELATIONAL_GREATER_THAN, new Double(1));
125: c.addCriteria(ItemMeta.QTY,
126: Criteria.RELATIONAL_SMALLER_THAN, new Double(3));
127: c.addCriteria(ItemMeta.KEY_REF,
128: Criteria.RELATIONAL_ENDS_WITH, "TEST-KEY-REF");
129: c.addOrderBy(ItemMeta.ITEM_ID, Criteria.ORDER_BY_ASC);
130:
131: Iterator i = m_uow.query(c).iterator();
132: Item item = null;
133: item = ((Item) i.next());
134: assertEquals("Z-TESTITEM-01", item.getItemId());
135: assertEquals("SOME SC", item.getSc());
136: assertEquals("Z-TESTPART-01", item.getPart());
137: assertEquals("Z-TESTPRIME-01", item.getPrime());
138: assertEquals("Z-TESTSYCD-01", item.getCondition());
139: assertEquals(2, item.getQty().intValue());
140: assertEquals(new DateTime(2003, DateTime.SEPTEMBER, 10, 20,
141: 30, 40, 0), item.getCreatedDatetime());
142:
143: item = ((Item) i.next());
144: assertEquals("Z-TESTITEM-02", item.getItemId());
145: assertEquals("SOME SC", item.getSc());
146: assertEquals("Z-TESTPART-01", item.getPart());
147: assertEquals("Z-TESTPRIME-01", item.getPrime());
148: assertEquals("Z-TESTSYCD-01", item.getCondition());
149: assertEquals(2, item.getQty().intValue());
150: assertEquals(new DateTime(2003, DateTime.SEPTEMBER, 10),
151: item.getCreatedDatetime());
152:
153: item = ((Item) i.next());
154: assertEquals("Z-TESTITEM-03", item.getItemId());
155: assertEquals("SOME SC", item.getSc());
156: assertEquals("Z-TESTPART-01", item.getPart());
157: assertEquals("Z-TESTPRIME-01", item.getPrime());
158: assertEquals("Z-TESTSYCD-01", item.getCondition());
159: assertEquals(2, item.getQty().intValue());
160: assertNull(item.getCreatedDatetime());
161:
162: } catch (Exception e) {
163: e.printStackTrace();
164: fail();
165: }
166: }
167:
168: /** Tests the sql:
169: * Select * from item where item_id = received_item_id
170: * and status_2 != status_3
171: * and item_id >= 'Z-TESTITEM-01'
172: * and item_id <= 'Z-TESTITEM-02'
173: * order by item_id desc
174: * This should return 2 item records
175: */
176: public void testDualFieldQuery() {
177: try {
178: Criteria c = new Criteria();
179: c.setTable(ItemMeta.getName());
180: c.addDualCriteria(ItemMeta.ITEM_ID,
181: ItemMeta.RECEIVED_ITEM_ID);
182: c.addDualCriteria(ItemMeta.STATUS2,
183: Criteria.RELATIONAL_NOT_EQUALS, ItemMeta.STATUS3);
184: c.addCriteria(ItemMeta.ITEM_ID,
185: Criteria.RELATIONAL_GREATER_THAN_EQUAL_TO,
186: "Z-TESTITEM-01");
187: c.addCriteria(ItemMeta.ITEM_ID,
188: Criteria.RELATIONAL_SMALLER_THAN_EQUAL_TO,
189: "Z-TESTITEM-02");
190: c.addOrderBy(ItemMeta.ITEM_ID, Criteria.ORDER_BY_DESC);
191:
192: Iterator i = m_uow.query(c).iterator();
193: assertEquals("Z-TESTITEM-02", ((Item) i.next()).getItemId());
194: assertEquals("Z-TESTITEM-01", ((Item) i.next()).getItemId());
195: } catch (Exception e) {
196: e.printStackTrace();
197: fail();
198: }
199: }
200:
201: /** Tests the sql:
202: * Select * from item Where
203: * ( (status_1 is null or status_2 is null or status_3 = 'A') and condition like 'Z-TESTSYCD%' )
204: * or (item_id like 'Z-TESTITEM%')
205: * order by item_id
206: * This should return 3 item records
207: */
208: public void testOrAndQuery() {
209: try {
210: AtomicCriteria ac1 = new AtomicCriteria();
211: ac1.addCriteria(ItemMeta.STATUS1,
212: Criteria.RELATIONAL_IS_NULL);
213: ac1.addOrCriteria(ItemMeta.STATUS2,
214: Criteria.RELATIONAL_IS_NULL);
215: ac1.addOrCriteria(ItemMeta.STATUS3, "A");
216:
217: AtomicCriteria ac2 = new AtomicCriteria();
218: ac2.addAtomic(ac1);
219: ac2.addCriteria(ItemMeta.CONDITION,
220: Criteria.RELATIONAL_BEGINS_WITH, "Z-TESTSYCD");
221:
222: Criteria c = new Criteria();
223: c.setTable(ItemMeta.getName());
224: c.addAtomic(ac2);
225: c.addOrCriteria(ItemMeta.ITEM_ID,
226: Criteria.RELATIONAL_BEGINS_WITH, "Z-TESTITEM");
227: c.addOrderBy(ItemMeta.ITEM_ID, Criteria.ORDER_BY_ASC);
228:
229: Iterator i = m_uow.query(c).iterator();
230: assertEquals("Z-TESTITEM-01", ((Item) i.next()).getItemId());
231: assertEquals("Z-TESTITEM-02", ((Item) i.next()).getItemId());
232: assertEquals("Z-TESTITEM-03", ((Item) i.next()).getItemId());
233: } catch (Exception e) {
234: e.printStackTrace();
235: fail();
236: }
237: }
238:
239: /** Tests the sql:
240: * Select * from item Where exists
241: * (select 1 from cat1 Where cat1.part = item.part and cat1.noun = 'Z-TESTNOUN%')
242: * order by item_id
243: * This should return 3 item records
244: */
245: public void testInnerJoinQuery() {
246: try {
247:
248: Criteria c1 = new Criteria();
249: c1.setTable(PartMeta.getName());
250: c1.addInnerCriteria(PartMeta.PART, ItemMeta.PART);
251: c1.addCriteria(PartMeta.NOUN,
252: Criteria.RELATIONAL_BEGINS_WITH, "Z-TESTNOUN");
253:
254: Criteria c2 = new Criteria();
255: c2.setTable(ItemMeta.getName());
256: c2.addAggregate(c1);
257: c2.addOrderBy(ItemMeta.ITEM_ID, Criteria.ORDER_BY_ASC);
258:
259: Iterator i = m_uow.query(c2).iterator();
260: assertEquals("Z-TESTITEM-01", ((Item) i.next()).getItemId());
261: assertEquals("Z-TESTITEM-02", ((Item) i.next()).getItemId());
262: assertEquals("Z-TESTITEM-03", ((Item) i.next()).getItemId());
263: } catch (Exception e) {
264: e.printStackTrace();
265: fail();
266: }
267: }
268:
269: /** Tests the sql:
270: * Select * from item Where condition = 'Z-TESTSYCD-01'
271: * and exists (select 1 from cat1 Where cat1.part = item.part
272: * and exists(select 1 from syci where syci.category_instrument = cat1.category_instrument
273: * and category_instrument like 'Z%' and support_equip_b='T') )
274: * order by item_id
275: * This should return 3 item records
276: */
277: public void testNestedInnerJoinQuery() {
278: try {
279:
280: Criteria c0 = new Criteria();
281: c0.setTable(CategoryOfInstrumentMeta.getName());
282: c0.addInnerCriteria(
283: CategoryOfInstrumentMeta.CATEGORY_INSTRUMENT,
284: PartMeta.CATEGORY_INSTRUMENT);
285: c0.addCriteria(
286: CategoryOfInstrumentMeta.CATEGORY_INSTRUMENT,
287: Criteria.RELATIONAL_BEGINS_WITH, "Z");
288: c0.addCriteria(CategoryOfInstrumentMeta.SUPPORT_EQUIP,
289: Boolean.TRUE);
290:
291: Criteria c1 = new Criteria();
292: c1.setTable(PartMeta.getName());
293: c1.addAggregate(c0);
294: c1.addInnerCriteria(PartMeta.PART, ItemMeta.PART);
295:
296: Criteria c2 = new Criteria();
297: c2.setTable(ItemMeta.getName());
298: c2.addCriteria(ItemMeta.CONDITION, "Z-TESTSYCD-01");
299: c2.addAggregate(c1);
300: c2.addOrderBy(ItemMeta.ITEM_ID, Criteria.ORDER_BY_ASC);
301:
302: Iterator i = m_uow.query(c2).iterator();
303: assertEquals("Z-TESTITEM-01", ((Item) i.next()).getItemId());
304: assertEquals("Z-TESTITEM-02", ((Item) i.next()).getItemId());
305: assertEquals("Z-TESTITEM-03", ((Item) i.next()).getItemId());
306: } catch (Exception e) {
307: e.printStackTrace();
308: fail();
309: }
310: }
311:
312: /** Tests the sql:
313: * Select * from item
314: * and exists (select 1 from cat1 Where cat1.part = item.part and cat1.noun = 'Z-TESTNOUN%')
315: * and exists (select 1 from sycd where sycd.condition = item.condition and description like 'Z%')
316: * order by item_id
317: * This should return 3 item records
318: */
319: public void testTwoInnerJoinsQuery() {
320: try {
321:
322: Criteria c1 = new Criteria();
323: c1.setTable(PartMeta.getName());
324: c1.addInnerCriteria(PartMeta.PART, ItemMeta.PART);
325: c1.addCriteria(PartMeta.NOUN,
326: Criteria.RELATIONAL_BEGINS_WITH, "Z-TESTNOUN");
327:
328: Criteria c2 = new Criteria();
329: c2.setTable(ConditionMeta.getName());
330: c2.addInnerCriteria(ConditionMeta.CONDITION,
331: ItemMeta.CONDITION);
332: c2.addCriteria(ConditionMeta.DESCRIPTION,
333: Criteria.RELATIONAL_BEGINS_WITH, "Z");
334:
335: Criteria c = new Criteria();
336: c.setTable(ItemMeta.getName());
337: c.addAggregate(c1);
338: c.addAggregate(c2);
339: c.addOrderBy(ItemMeta.ITEM_ID, Criteria.ORDER_BY_ASC);
340:
341: Iterator i = m_uow.query(c).iterator();
342: assertEquals("Z-TESTITEM-01", ((Item) i.next()).getItemId());
343: assertEquals("Z-TESTITEM-02", ((Item) i.next()).getItemId());
344: assertEquals("Z-TESTITEM-03", ((Item) i.next()).getItemId());
345: } catch (Exception e) {
346: e.printStackTrace();
347: fail();
348: }
349: }
350:
351: /** Tests the sql:
352: * Select * from catz where part='Z-TESTPART-01'
353: * This should return 1 catz record
354: */
355: public void testLongQuery() {
356: try {
357: Criteria c = new Criteria();
358: c.setTable(PartRemarksMeta.getName());
359: c.addCriteria(PartRemarksMeta.PART, "Z-TESTPART-01");
360: Iterator i = m_uow.query(c).iterator();
361: assertEquals(Wrapper.CATZ_REMARKS, ((PartRemarks) i.next())
362: .getRemarks());
363: } catch (Exception e) {
364: e.printStackTrace();
365: fail();
366: }
367: }
368:
369: /** Tests the sql:
370: * Select * from item Where
371: * created_datetime = to_date('2003-09-10 20:30:40', 'yyyy-MM-dd hh24:mi:ss')
372: * or created_datetime = to_date('2003-09-10', 'yyyy-MM-dd')
373: * or created_datetime is null
374: * order by item_id
375: * This should return 3 item records
376: */
377: public void testDateTimeQuery() {
378: try {
379: Criteria c = new Criteria();
380: c.addCriteria(ItemMeta.CREATED_DATETIME, new DateTime(2003,
381: DateTime.SEPTEMBER, 10, 20, 30, 40, 0));
382: c.addOrCriteria(ItemMeta.CREATED_DATETIME, new DateTime(
383: 2003, DateTime.SEPTEMBER, 10));
384: c.addOrCriteria(ItemMeta.CREATED_DATETIME,
385: Criteria.RELATIONAL_IS_NULL);
386: c.setTable(ItemMeta.getName());
387: c.addOrderBy(ItemMeta.ITEM_ID, Criteria.ORDER_BY_ASC);
388:
389: Iterator i = m_uow.query(c).iterator();
390: Item item = (Item) i.next();
391: assertEquals("Z-TESTITEM-01", item.getItemId());
392: assertEquals(new DateTime(2003, DateTime.SEPTEMBER, 10, 20,
393: 30, 40, 0), item.getCreatedDatetime());
394:
395: item = ((Item) i.next());
396: assertEquals("Z-TESTITEM-02", item.getItemId());
397: assertEquals(new DateTime(2003, DateTime.SEPTEMBER, 10),
398: item.getCreatedDatetime());
399:
400: item = ((Item) i.next());
401: assertNull(item.getCreatedDatetime());
402: } catch (Exception e) {
403: e.printStackTrace();
404: fail();
405: }
406: }
407:
408: /** Creates 2 ZZ_JUT_INSTRUMENT records with the prefix 'Z-|%_'
409: * It then Tests the sql:
410: * Select * from ZZ_JUT_INSTRUMENT where CategoryInstrument like 'Z-|||%|_%' escape '|'
411: * This should return 2 ZZ_JUT_INSTRUMENT records.
412: * The records are then deleted.
413: */
414: public void testQueryWithEscapeCharacters() {
415: try {
416: // create a couple of CategoryOfInstrument with CategoryInstrument having the prefix 'Z-TESTCI-|%_'
417: String prefix = "Z-|%_";
418:
419: CategoryOfInstrument coi1 = (CategoryOfInstrument) m_uow
420: .newPersistentInstance(CategoryOfInstrument.class);
421: coi1.updateCategoryInstrument(prefix + '1');
422: CategoryOfInstrument coi2 = (CategoryOfInstrument) m_uow
423: .newPersistentInstance(CategoryOfInstrument.class);
424: coi2.updateCategoryInstrument(prefix + '2');
425: m_uow.add(coi1);
426: m_uow.add(coi2);
427: m_uow.commit();
428:
429: // now retrieve them
430: m_uow = new UOW();
431: Criteria c = new Criteria();
432: c.setTable(CategoryOfInstrumentMeta.getName());
433: c.addCriteria(CategoryOfInstrumentMeta.CATEGORY_INSTRUMENT,
434: Criteria.RELATIONAL_BEGINS_WITH, prefix);
435: Collection col = m_uow.query(c);
436: assertEquals(2, col.size());
437:
438: // now delete the records
439: for (Iterator itr = col.iterator(); itr.hasNext();)
440: m_uow.delete((IPersistent) itr.next());
441: m_uow.commit();
442:
443: } catch (Exception e) {
444: e.printStackTrace();
445: fail();
446: }
447: }
448: }
|