Java > Open Source Codes > org > apache > torque > util > CriteriaTest


1 package org.apache.torque.util;
2
3 /*
4  * Licensed to the Apache Software Foundation (ASF) under one
5  * or more contributor license agreements. See the NOTICE file
6  * distributed with this work for additional information
7  * regarding copyright ownership. The ASF licenses this file
8  * to you under the Apache License, Version 2.0 (the
9  * "License"); you may not use this file except in compliance
10  * with the License. You may obtain a copy of the License at
11  *
12  * http://www.apache.org/licenses/LICENSE-2.0
13  *
14  * Unless required by applicable law or agreed to in writing,
15  * software distributed under the License is distributed on an
16  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
17  * KIND, either express or implied. See the License for the
18  * specific language governing permissions and limitations
19  * under the License.
20  */
21
22 import java.util.Calendar ;
23 import java.util.Date ;
24 import java.util.List ;
25 import java.util.Map ;
26
27 import org.apache.commons.configuration.BaseConfiguration;
28 import org.apache.commons.configuration.Configuration;
29 import org.apache.commons.lang.SerializationUtils;
30 import org.apache.torque.BaseTestCase;
31 import org.apache.torque.Torque;
32 import org.apache.torque.TorqueException;
33 import org.apache.torque.adapter.DBFactory;
34 import org.apache.torque.map.ColumnMap;
35 import org.apache.torque.map.DatabaseMap;
36 import org.apache.torque.map.TableMap;
37 import org.apache.torque.util.Criteria.Criterion;
38 import org.apache.torque.util.Criteria.Join;
39
40 /**
41  * Test class for Criteria.
42  *
43  * @author <a HREF="mailto:celkins@scardini.com">Christopher Elkins</a>
44  * @author <a HREF="mailto:sam@neurogrid.com">Sam Joseph</a>
45  * @author <a HREF="mailto:seade@backstagetech.com.au">Scott Eade</a>
46  * @version $Id: CriteriaTest.java 473821 2006-11-11 22:37:25Z tv $
47  */
48 public class CriteriaTest extends BaseTestCase
49 {
50
51     /** The criteria to use in the test. */
52     private Criteria c;
53
54     /**
55      * Creates a new instance.
56      *
57      * @param name the name of the test to run
58      */
59     public CriteriaTest(String name)
60     {
61         super(name);
62     }
63
64     /**
65      * Initializes the criteria.
66      */
67     public void setUp()
68     {
69         super.setUp();
70         c = new Criteria();
71     }
72
73     /**
74      * Test basic adding of strings.
75      */
76     public void testAddString()
77     {
78         final String table = "myTable";
79         final String column = "myColumn";
80         final String value = "myValue";
81
82         // Add the string
83 c.add(table, column, (Object ) value);
84
85         // Verify that the key exists
86 assertTrue(c.containsKey(table, column));
87
88         // Verify that what we get out is what we put in
89 assertTrue(c.getString(table, column).equals(value));
90     }
91
92     /**
93      * test various properties of Criterion and nested criterion
94      */
95     public void testNestedCriterion()
96     {
97         final String table2 = "myTable2";
98         final String column2 = "myColumn2";
99         final String value2 = "myValue2";
100
101         final String table3 = "myTable3";
102         final String column3 = "myColumn3";
103         final String value3 = "myValue3";
104
105         final String table4 = "myTable4";
106         final String column4 = "myColumn4";
107         final String value4 = "myValue4";
108
109         final String table5 = "myTable5";
110         final String column5 = "myColumn5";
111         final String value5 = "myValue5";
112
113         Criteria.Criterion crit2 =
114             c.getNewCriterion(table2, column2, (Object ) value2, Criteria.EQUAL);
115         Criteria.Criterion crit3 =
116             c.getNewCriterion(table3, column3, (Object ) value3, Criteria.EQUAL);
117         Criteria.Criterion crit4 =
118             c.getNewCriterion(table4, column4, (Object ) value4, Criteria.EQUAL);
119         Criteria.Criterion crit5 =
120             c.getNewCriterion(table5, column5, (Object ) value5, Criteria.EQUAL);
121
122         crit2.and(crit3).or(crit4.and(crit5));
123         String expect =
124             "((myTable2.myColumn2='myValue2' "
125                 + "AND myTable3.myColumn3='myValue3') "
126             + "OR (myTable4.myColumn4='myValue4' "
127                 + "AND myTable5.myColumn5='myValue5'))";
128         String result = crit2.toString();
129         assertEquals(expect, result);
130
131         Criteria.Criterion crit6 =
132             c.getNewCriterion(table2, column2, (Object ) value2, Criteria.EQUAL);
133         Criteria.Criterion crit7 =
134             c.getNewCriterion(table3, column3, (Object ) value3, Criteria.EQUAL);
135         Criteria.Criterion crit8 =
136             c.getNewCriterion(table4, column4, (Object ) value4, Criteria.EQUAL);
137         Criteria.Criterion crit9 =
138             c.getNewCriterion(table5, column5, (Object ) value5, Criteria.EQUAL);
139
140         crit6.and(crit7).or(crit8).and(crit9);
141         expect =
142             "(((myTable2.myColumn2='myValue2' "
143                     + "AND myTable3.myColumn3='myValue3') "
144                 + "OR myTable4.myColumn4='myValue4') "
145                     + "AND myTable5.myColumn5='myValue5')";
146         result = crit6.toString();
147         assertEquals(expect, result);
148
149         // should make sure we have tests for all possibilities
150
151         Criteria.Criterion[] crita = crit2.getAttachedCriterion();
152
153         assertEquals(crit2, crita[0]);
154         assertEquals(crit3, crita[1]);
155         assertEquals(crit4, crita[2]);
156         assertEquals(crit5, crita[3]);
157
158         List tables = crit2.getAllTables();
159
160         assertEquals(crit2.getTable(), tables.get(0));
161         assertEquals(crit3.getTable(), tables.get(1));
162         assertEquals(crit4.getTable(), tables.get(2));
163         assertEquals(crit5.getTable(), tables.get(3));
164
165         // simple confirmations that equality operations work
166 assertTrue(crit2.hashCode() == crit2.hashCode());
167         assertEquals(crit2.toString(), crit2.toString());
168     }
169
170     /**
171      * Tests &lt;= and =&gt;.
172      */
173     public void testBetweenCriterion()
174     {
175         Criteria.Criterion cn1 =
176             c.getNewCriterion(
177                 "INVOICE.COST",
178                 new Integer (1000),
179                 Criteria.GREATER_EQUAL);
180         Criteria.Criterion cn2 =
181             c.getNewCriterion(
182                 "INVOICE.COST",
183                 new Integer (5000),
184                 Criteria.LESS_EQUAL);
185         c.add(cn1.and(cn2));
186         String expect =
187             "SELECT FROM INVOICE WHERE "
188             + "(INVOICE.COST>=1000 AND INVOICE.COST<=5000)";
189         String result = null;
190         try
191         {
192             result = BasePeer.createQueryString(c);
193         }
194         catch (TorqueException e)
195         {
196             fail("TorqueException thrown in BasePeer.createQueryString()");
197         }
198
199         assertEquals(expect, result);
200     }
201
202     /**
203      * Verify that AND and OR criterion are nested correctly.
204      */
205     public void testPrecedence()
206     {
207         Criteria.Criterion cn1 =
208             c.getNewCriterion("INVOICE.COST", "1000", Criteria.GREATER_EQUAL);
209         Criteria.Criterion cn2 =
210             c.getNewCriterion("INVOICE.COST", "2000", Criteria.LESS_EQUAL);
211         Criteria.Criterion cn3 =
212             c.getNewCriterion("INVOICE.COST", "8000", Criteria.GREATER_EQUAL);
213         Criteria.Criterion cn4 =
214             c.getNewCriterion("INVOICE.COST", "9000", Criteria.LESS_EQUAL);
215         c.add(cn1.and(cn2));
216         c.or(cn3.and(cn4));
217
218         String expect =
219             "SELECT FROM INVOICE WHERE "
220             + "((INVOICE.COST>='1000' AND INVOICE.COST<='2000') "
221             + "OR (INVOICE.COST>='8000' AND INVOICE.COST<='9000'))";
222
223         String result = null;
224         try
225         {
226             result = BasePeer.createQueryString(c);
227         }
228         catch (TorqueException e)
229         {
230             fail("TorqueException thrown in BasePeer.createQueryString()");
231         }
232
233         assertEquals(expect, result);
234     }
235
236     /**
237      * Test Criterion.setIgnoreCase().
238      * As the output is db specific the test just prints the result to
239      * System.out
240      */
241     public void testCriterionIgnoreCase()
242     {
243         Criteria myCriteria = new Criteria();
244
245         Criteria.Criterion expected = myCriteria.getNewCriterion(
246                 "TABLE.COLUMN", (Object )"FoObAr", Criteria.LIKE);
247         Criteria.Criterion result = expected.setIgnoreCase(true);
248         assertEquals("Criterion mis-match after calling setIgnoreCase(true)",
249                      expected.toString(), result.toString());
250     }
251
252     /**
253      * Test that true is evaluated correctly.
254      */
255     public void testBoolean()
256     {
257         Criteria c = new Criteria().add("TABLE.COLUMN", true);
258
259         String expect = "SELECT FROM TABLE WHERE TABLE.COLUMN=1";
260
261         String result = null;
262         try
263         {
264             result = BasePeer.createQueryString(c);
265         }
266         catch (TorqueException e)
267         {
268             fail("TorqueException thrown in BasePeer.createQueryString()");
269         }
270
271         assertEquals(expect, result);
272
273         // test the postgresql variation
274 c = new Criteria();
275         Criteria.Criterion cc =
276             c.getNewCriterion("TABLE.COLUMN", Boolean.TRUE, Criteria.EQUAL);
277
278         Configuration conf = new BaseConfiguration();
279         conf.addProperty("driver", "org.postgresql.Driver");
280         try
281         {
282             cc.setDB(DBFactory.create("org.postgresql.Driver"));
283         }
284         catch (Exception e)
285         {
286             fail("Exception thrown in DBFactory");
287         }
288
289         assertEquals("TABLE.COLUMN=TRUE", cc.toString());
290     }
291
292     /**
293      * testcase for addDate()
294      */
295     public void testAddDate()
296     {
297         Criteria c = new Criteria();
298         c.addDate("TABLE.DATE_COLUMN", 2003, 0, 22);
299
300         String expect = "SELECT FROM TABLE WHERE TABLE.DATE_COLUMN='20030122000000'";
301
302         String result = null;
303         try
304         {
305             result = BasePeer.createQueryString(c);
306         }
307         catch (TorqueException e)
308         {
309             e.printStackTrace();
310             fail("TorqueException thrown in BasePeer.createQueryString()");
311         }
312         assertEquals(expect, result);
313     }
314
315     /**
316      * testcase for andDate()
317      * issue TORQUE-42
318      */
319     public void testAndDate()
320     {
321         Criteria c = new Criteria();
322         c.addDate("TABLE.DATE_COLUMN", 2003, 0, 22, Criteria.GREATER_THAN);
323         c.andDate("TABLE.DATE_COLUMN", 2004, 0, 22, Criteria.LESS_THAN);
324
325         String expect = "SELECT FROM TABLE WHERE (TABLE.DATE_COLUMN>'20030122000000' AND TABLE.DATE_COLUMN<'20040122000000')";
326
327         String result = null;
328         try
329         {
330             result = BasePeer.createQueryString(c);
331         }
332         catch (TorqueException e)
333         {
334             e.printStackTrace();
335             fail("TorqueException thrown in BasePeer.createQueryString()");
336         }
337         assertEquals(expect, result);
338     }
339
340     /**
341      * testcase for add(Date)
342      */
343     public void testDateAdd()
344     {
345         Calendar cal = Calendar.getInstance();
346         cal.set(2003, 0, 22, 0, 0, 0);
347         Date date = cal.getTime();
348         Criteria c = new Criteria();
349         c.add("TABLE.DATE_COLUMN", date);
350
351         String expect = "SELECT FROM TABLE WHERE TABLE.DATE_COLUMN='20030122000000'";
352
353         String result = null;
354         try
355         {
356             result = BasePeer.createQueryString(c);
357         }
358         catch (TorqueException e)
359         {
360             e.printStackTrace();
361             fail("TorqueException thrown in BasePeer.createQueryString()");
362         }
363         assertEquals(expect, result);
364     }
365
366     public void testCurrentDate()
367     {
368         Criteria c = new Criteria()
369                 .add("TABLE.DATE_COLUMN", Criteria.CURRENT_DATE)
370                 .add("TABLE.TIME_COLUMN", Criteria.CURRENT_TIME);
371
372         String expect = "SELECT FROM TABLE WHERE TABLE.TIME_COLUMN=CURRENT_TIME AND TABLE.DATE_COLUMN=CURRENT_DATE";
373
374         String result = null;
375         try
376         {
377             result = BasePeer.createQueryString(c);
378         }
379         catch (TorqueException e)
380         {
381             e.printStackTrace();
382             fail("TorqueException thrown in BasePeer.createQueryString()");
383         }
384
385         assertEquals(expect,result);
386     }
387
388     public void testCountAster()
389     {
390         Criteria c = new Criteria()
391                 .addSelectColumn("COUNT(*)")
392                 .add("TABLE.DATE_COLUMN", Criteria.CURRENT_DATE)
393                 .add("TABLE.TIME_COLUMN", Criteria.CURRENT_TIME);
394
395         String expect = "SELECT COUNT(*) FROM TABLE WHERE TABLE.TIME_COLUMN=CURRENT_TIME AND TABLE.DATE_COLUMN=CURRENT_DATE";
396
397         String result = null;
398         try
399         {
400             result = BasePeer.createQueryString(c);
401         }
402         catch (TorqueException e)
403         {
404             e.printStackTrace();
405             fail("TorqueException thrown in BasePeer.createQueryString()");
406         }
407
408         assertEquals(expect,result);
409
410     }
411
412     /**
413      * This test case has been written to try out the fix applied to resolve
414      * TRQS73 - i.e. ensuring that Criteria.toString() does not alter any limit
415      * or offset that may be stored in the Criteria object. This testcase
416      * could actually pass without the fix if the database in use does not
417      * support native limits and offsets.
418      */
419     public void testCriteriaToStringOffset()
420     {
421         Criteria c = new Criteria()
422                 .add("TABLE.DATE_COLUMN", Criteria.CURRENT_DATE)
423                 .setOffset(3)
424                 .setLimit(5);
425
426         String toStringExpect = "Criteria:: TABLE.DATE_COLUMN<=>TABLE.DATE_COLUMN=CURRENT_DATE: "
427                 + "\nCurrent Query SQL (may not be complete or applicable): "
428                 + "SELECT FROM TABLE WHERE TABLE.DATE_COLUMN=CURRENT_DATE LIMIT 3, 5";
429
430         String cString = c.toString();
431         //System.out.println(cString);
432 assertEquals(toStringExpect, cString);
433
434         // Note that this is intentially the same as above as the behaviour is
435 // only observed on subsequent invocations of toString().
436 cString = c.toString();
437         //System.out.println(cString);
438 assertEquals(toStringExpect, cString);
439     }
440
441     /**
442      * This test case has been written to try out the fix applied to resolve
443      * TRQS73 - i.e. ensuring that Criteria.toString() does not alter any limit
444      * or offset that may be stored in the Criteria object. This testcase
445      * could actually pass without the fix if the database in use does not
446      * support native limits and offsets.
447      */
448     public void testCriteriaToStringLimit()
449     {
450         Criteria c = new Criteria()
451                 .add("TABLE.DATE_COLUMN", Criteria.CURRENT_DATE)
452                 .setLimit(5);
453
454         String toStringExpect = "Criteria:: TABLE.DATE_COLUMN<=>TABLE.DATE_COLUMN=CURRENT_DATE: "
455                 + "\nCurrent Query SQL (may not be complete or applicable): "
456                 + "SELECT FROM TABLE WHERE TABLE.DATE_COLUMN=CURRENT_DATE LIMIT 5";
457
458         String cString = c.toString();
459         //System.out.println(cString);
460 assertEquals(toStringExpect, cString);
461
462         // Note that this is intentially the same as above as the behaviour is
463 // only observed on subsequent invocations of toString().
464 cString = c.toString();
465         //System.out.println(cString);
466 assertEquals(toStringExpect, cString);
467     }
468
469     /**
470      * This test case verifies if the Criteria.LIKE comparison type will
471      * get replaced through Criteria.EQUAL if there are no SQL wildcards
472      * in the given value.
473      */
474     public void testLikeWithoutWildcards()
475     {
476         Criteria c = new Criteria();
477         c.add("TABLE.COLUMN", (Object ) "no wildcards", Criteria.LIKE);
478
479         String expect = "SELECT FROM TABLE WHERE TABLE.COLUMN = 'no wildcards'";
480
481         String result = null;
482         try
483         {
484             result = BasePeer.createQueryString(c);
485         }
486         catch (TorqueException e)
487         {
488             e.printStackTrace();
489             fail("TorqueException thrown in BasePeer.createQueryString()");
490         }
491
492         assertEquals(expect, result);
493     }
494
495     /**
496      * This test case verifies if the Criteria.NOT_LIKE comparison type will
497      * get replaced through Criteria.NOT_EQUAL if there are no SQL wildcards
498      * in the given value.
499      */
500     public void testNotLikeWithoutWildcards()
501     {
502         Criteria c = new Criteria();
503         c.add("TABLE.COLUMN", (Object ) "no wildcards", Criteria.NOT_LIKE);
504
505         String firstExpect = "SELECT FROM TABLE WHERE TABLE.COLUMN != 'no wildcards'";
506         String secondExpect = "SELECT FROM TABLE WHERE TABLE.COLUMN <> 'no wildcards'";
507
508         String result = null;
509         try
510         {
511             result = BasePeer.createQueryString(c);
512         }
513         catch (TorqueException e)
514         {
515             e.printStackTrace();
516             fail("TorqueException thrown in BasePeer.createQueryString()");
517         }
518
519         assertTrue(result.equals(firstExpect) || result.equals(secondExpect));
520     }
521
522     /**
523      * Test that serialization works.
524      */
525     public void testSerialization()
526     {
527         c.setOffset(10);
528         c.setLimit(11);
529         c.setIgnoreCase(true);
530         c.setSingleRecord(true);
531         c.setCascade(true);
532         c.setDbName("myDB");
533         c.setAll();
534         c.setDistinct();
535         c.addSelectColumn("Author.NAME");
536         c.addSelectColumn("Author.AUTHOR_ID");
537         c.addDescendingOrderByColumn("Author.NAME");
538         c.addAscendingOrderByColumn("Author.AUTHOR_ID");
539         c.addAlias("Writer", "Author");
540         c.addAsColumn("AUTHOR_NAME", "Author.NAME");
541         c.addJoin("Author.AUTHOR_ID", "Book.AUTHOR_ID", Criteria.INNER_JOIN);
542         c.add("Author.NAME", (Object ) "author%", Criteria.LIKE);
543
544         // Some direct Criterion checks
545 Criterion cn = c.getCriterion("Author.NAME");
546         cn.setIgnoreCase(true);
547         assertEquals("author%", cn.getValue());
548         assertEquals(Criteria.LIKE, cn.getComparison());
549         Criterion cnDirectClone = (Criterion) SerializationUtils.clone(cn);
550         assertEquals(cn, cnDirectClone);
551
552         // Clone the object
553 Criteria cClone = (Criteria) SerializationUtils.clone(c);
554
555         // Check the clone
556 assertEquals(c.size(), cClone.size());
557         assertEquals(10, cClone.getOffset());
558         assertEquals(c.getOffset(), cClone.getOffset());
559         assertEquals(11, cClone.getLimit());
560         assertEquals(c.getLimit(), cClone.getLimit());
561         assertEquals(true, cClone.isIgnoreCase());
562         assertEquals(c.isIgnoreCase(), cClone.isIgnoreCase());
563         assertEquals(true, cClone.isSingleRecord());
564         assertEquals(c.isSingleRecord(), cClone.isSingleRecord());
565         assertEquals(true, cClone.isCascade());
566         assertEquals(c.isCascade(), cClone.isCascade());
567         assertEquals("myDB", cClone.getDbName());
568         assertEquals(c.getDbName(), cClone.getDbName());
569         List selectModifiersClone = cClone.getSelectModifiers();
570         assertTrue(selectModifiersClone.contains(Criteria.ALL.toString()));
571         assertTrue(selectModifiersClone.contains(Criteria.DISTINCT.toString()));
572         assertEquals(c.getSelectModifiers(), cClone.getSelectModifiers());
573         List selectColumnsClone = cClone.getSelectColumns();
574         assertTrue(selectColumnsClone.contains("Author.NAME"));
575         assertTrue(selectColumnsClone.contains("Author.AUTHOR_ID"));
576         assertEquals(c.getSelectColumns(), cClone.getSelectColumns());
577         List orderByColumnsClone = cClone.getOrderByColumns();
578         assertTrue(orderByColumnsClone.contains("Author.NAME DESC"));
579         assertTrue(orderByColumnsClone.contains("Author.AUTHOR_ID ASC"));
580         assertEquals(c.getOrderByColumns(), cClone.getOrderByColumns());
581         Map aliasesClone = cClone.getAliases();
582         assertTrue(aliasesClone.containsKey("Writer"));
583         assertEquals("Author", aliasesClone.get("Writer"));
584         assertEquals(c.getAliases(), cClone.getAliases());
585         Map asColumnsClone = cClone.getAsColumns();
586         assertTrue(asColumnsClone.containsKey("AUTHOR_NAME"));
587         assertEquals("Author.NAME", asColumnsClone.get("AUTHOR_NAME"));
588         assertEquals(c.getAsColumns(), cClone.getAsColumns());
589
590         // Check Joins
591 List joinsClone = cClone.getJoins();
592         Join joinClone = (Join) joinsClone.get(0);
593         assertEquals("Author.AUTHOR_ID", joinClone.getLeftColumn());
594         assertEquals("Book.AUTHOR_ID", joinClone.getRightColumn());
595         assertEquals(Criteria.INNER_JOIN, joinClone.getJoinType());
596         assertEquals(c.getJoins(), cClone.getJoins());
597
598         // Some Criterion checks
599 Criterion cnClone = cClone.getCriterion("Author.NAME");
600         assertEquals("author%", cnClone.getValue());
601         assertEquals(Criteria.LIKE, cnClone.getComparison());
602         assertEquals(cn.isIgnoreCase(), cnClone.isIgnoreCase());
603
604         // Confirm that equals() checks all of the above.
605 assertEquals(c, cClone);
606
607         // Check hashCode() too.
608 assertEquals(c.hashCode(), cClone.hashCode());
609     }
610
611     /**
612      * Checks whether orderBy works.
613      */
614     public void testOrderBy() throws TorqueException
615     {
616         // we need a rudementary databaseMap for this test case to work
617 DatabaseMap dbMap = Torque.getDatabaseMap(Torque.getDefaultDB());
618
619         TableMap tableMap = new TableMap("AUTHOR", dbMap);
620         dbMap.addTable(tableMap);
621
622         ColumnMap columnMap = new ColumnMap("NAME", tableMap);
623         columnMap.setType("");
624         tableMap.addColumn(columnMap);
625
626         columnMap = new ColumnMap("AUTHOR_ID", tableMap);
627         columnMap.setType(new Integer (0));
628         tableMap.addColumn(columnMap);
629
630         // check that alias'ed tables are referenced by their alias
631 // name when added to the select clause.
632 Criteria criteria = new Criteria();
633         criteria.addSelectColumn("AUTHOR.NAME");
634         criteria.addAlias("a", "AUTHOR");
635         criteria.addJoin(
636                 "AUTHOR.AUTHOR_ID",
637                 "a." + "AUTHOR_ID");
638         criteria.addAscendingOrderByColumn(
639                 "a.NAME");
640
641         String result = BasePeer.createQueryString(criteria);
642         assertEquals("SELECT AUTHOR.NAME, a.NAME "
643                     + "FROM AUTHOR, AUTHOR a "
644                     + "WHERE AUTHOR.AUTHOR_ID=a.AUTHOR_ID "
645                     + "ORDER BY a.NAME ASC",
646                 result);
647     }
648
649 }
650

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags