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