Java > Open Source Codes > org > apache > torque > DataTest


1 package org.apache.torque;
2
3 /*
4  * Copyright 2001-2004 The Apache Software Foundation.
5  *
6  * Licensed under the Apache License, Version 2.0 (the "License")
7  * you may not use this file except in compliance with the License.
8  * You may obtain a copy of the License at
9  *
10  * http://www.apache.org/licenses/LICENSE-2.0
11  *
12  * Unless required by applicable law or agreed to in writing, software
13  * distributed under the License is distributed on an "AS IS" BASIS,
14  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15  * See the License for the specific language governing permissions and
16  * limitations under the License.
17  */
18
19 import java.sql.Connection ;
20 import java.text.DateFormat ;
21 import java.text.SimpleDateFormat ;
22 import java.util.Arrays ;
23 import java.util.Date ;
24 import java.util.HashMap ;
25 import java.util.Iterator ;
26 import java.util.List ;
27 import java.util.Map ;
28
29 import org.apache.commons.logging.Log;
30 import org.apache.commons.logging.LogFactory;
31 import org.apache.torque.adapter.DBHypersonicSQL;
32 import org.apache.torque.adapter.DBOracle;
33 import org.apache.torque.om.StringKey;
34 import org.apache.torque.test.A;
35 import org.apache.torque.test.APeer;
36 import org.apache.torque.test.Author;
37 import org.apache.torque.test.AuthorPeer;
38 import org.apache.torque.test.BitTest;
39 import org.apache.torque.test.BitTestPeer;
40 import org.apache.torque.test.BlobTest;
41 import org.apache.torque.test.BlobTestPeer;
42 import org.apache.torque.test.Book;
43 import org.apache.torque.test.BookPeer;
44 import org.apache.torque.test.BooleanCheck;
45 import org.apache.torque.test.BooleanCheckPeer;
46 import org.apache.torque.test.ClobTest;
47 import org.apache.torque.test.ClobTestPeer;
48 import org.apache.torque.test.DateTest;
49 import org.apache.torque.test.DateTestPeer;
50 import org.apache.torque.test.IntegerPk;
51 import org.apache.torque.test.LargePk;
52 import org.apache.torque.test.LargePkPeer;
53 import org.apache.torque.test.MultiPk;
54 import org.apache.torque.test.MultiPkForeignKey;
55 import org.apache.torque.test.MultiPkPeer;
56 import org.apache.torque.test.NullValueTable;
57 import org.apache.torque.util.BasePeer;
58 import org.apache.torque.util.CountHelper;
59 import org.apache.torque.util.Criteria;
60
61 import com.workingdogs.village.Record;
62
63 /**
64  * Runtime tests.
65  *
66  * @author <a HREF="mailto:seade@backstagetech.com.au">Scott Eade</a>
67  * @author <a HREF="mailto:mpoeschl@marmot.at">Martin Poeschl</a>
68  * @author <a HREF="mailto:fischer@seitenbau.de">Thomas Fischer</a>
69  * @version $Id: DataTest.java,v 1.28 2005/07/13 21:12:07 tfischer Exp $
70  */
71 public class DataTest extends BaseRuntimeTestCase
72 {
73     private static Log log = LogFactory.getLog(DataTest.class);;
74     
75     /**
76      * Creates a new instance.
77      */
78     public DataTest(String name)
79     {
80         super(name);
81     }
82
83     public void setUp()
84     {
85         super.setUp();
86     }
87
88     /**
89      * test whether we can connect to the database at all
90      * @throws Exception if no connection can be established
91      */
92     public void testConnect() throws Exception
93     {
94         Connection connection = null;
95         try
96         {
97             connection = Torque.getConnection();
98             connection.close();
99             connection = null;
100         }
101         finally
102         {
103             if (connection != null)
104             {
105                 connection.close();
106             }
107         }
108     }
109     
110     /**
111      * does some inserts.
112      * @throws Exception if the test fails
113      */
114     public void testInsertData() throws Exception
115     {
116         // insert books and authors
117 for (int i = 1; i <= 10; i++)
118         {
119             Author author = new Author();
120             author.setName("Author " + i);
121             author.save();
122             assertTrue("authorId should not be 0 after insert",
123                     author.getAuthorId() != 0);
124
125             for (int j = 1; j <= 10; j++)
126             {
127                 Book book = new Book();
128                 book.setAuthor(author);
129                 book.setTitle("Book " + j + " - Author " + i);
130                 book.setIsbn("unknown");
131                 book.save();
132             }
133         }
134         // clean booleancheck table (because insert uses fixed keys)
135 Criteria criteria = new Criteria();
136         criteria.add(BooleanCheckPeer.TEST_KEY, (Object ) null, Criteria.NOT_EQUAL);
137         BooleanCheckPeer.doDelete(criteria);
138         
139         BooleanCheck bc = new BooleanCheck();
140         bc.setTestKey("t1");
141         bc.setBintValue(true);
142         bc.setBcharValue(true);
143         bc.save();
144         bc = new BooleanCheck();
145         bc.setTestKey("f1");
146         bc.setBintValue(false);
147         bc.setBcharValue(false);
148         bc.save();
149     }
150
151     /**
152      * multiple pk test (TRQ12)
153      * @throws Exception if the test fails
154      */
155     public void testMultiplePk() throws Exception
156     {
157         // clean table
158 Criteria criteria = new Criteria();
159         criteria.add(MultiPkPeer.PK1, (Object ) null, Criteria.NOT_EQUAL);
160         MultiPkPeer.doDelete(criteria);
161         
162         // do test
163 MultiPk mpk = new MultiPk();
164         mpk.setPrimaryKey("Svarchar:N5:Schar:");
165         mpk.save();
166     }
167
168     private static final String [] validTitles = {
169         "Book 7 - Author 8", "Book 6 - Author 8", "Book 7 - Author 7",
170         "Book 6 - Author 7", "Book 7 - Author 6", "Book 6 - Author 6",
171         "Book 7 - Author 5", "Book 6 - Author 5", "Book 7 - Author 4",
172         "Book 6 - Author 4"};
173
174     /**
175      * test limit/offset which was broken for oracle (TRQ47)
176      * @throws Exception if the test fails
177      */
178     public void testLimitOffset() throws Exception
179     {
180         Map titleMap = new HashMap ();
181         for (int j = 0; j < validTitles.length; j++)
182         {
183             titleMap.put(validTitles[j], null);
184         }
185
186         Criteria crit = new Criteria();
187         Criteria.Criterion c = crit.getNewCriterion(BookPeer.TITLE,
188                 (Object ) "Book 6 - Author 1", Criteria.GREATER_EQUAL);
189         c.and(crit.getNewCriterion(BookPeer.TITLE,
190                 (Object ) "Book 8 - Author 3", Criteria.LESS_EQUAL));
191         crit.add(c);
192         crit.addDescendingOrderByColumn(BookPeer.BOOK_ID);
193         crit.setLimit(10);
194         crit.setOffset(5);
195         List books = BookPeer.doSelect(crit);
196         assertTrue("List should have 10 books, not " + books.size(),
197                 books.size() == 10);
198         for (Iterator i = books.iterator(); i.hasNext();)
199         {
200             String title = ((Book) i.next()).getTitle();
201             assertTrue("Incorrect title: " + title,
202                     titleMap.containsKey(title));
203         }
204     }
205     
206     /**
207      * Checks whether the setSingleRecord() method in criteria works
208      */
209     public void testSingleRecord() throws Exception
210     {
211         Criteria criteria = new Criteria();
212         criteria.setSingleRecord(true);
213         criteria.setLimit(1);
214         criteria.setOffset(5);
215         List books = BookPeer.doSelect(criteria);
216         assertTrue("List should have 1 books, not " + books.size(),
217                 books.size() == 1);
218         
219         criteria.clear();
220         criteria.setSingleRecord(true);
221         criteria.setLimit(2);
222         try
223         {
224             books = BookPeer.doSelect(criteria);
225             fail("doSelect should have failed "
226                     + "because two records were selected "
227                     + " and one was expected");
228         }
229         catch (TorqueException e)
230         {
231         }
232     }
233     
234     /**
235      * tests whether null values can be processed successfully by datadump
236      * For this, a row containing null values is inserted here,
237      * the actual test is done later
238      * @throws Exception if inserting the test data fails
239      */
240     public void testDataDump() throws Exception
241     {
242         NullValueTable nvt = new NullValueTable();
243         nvt.setNumber1(1);
244         nvt.setNumber3(3);
245         nvt.setText1("text");
246         nvt.setNumberObj1(new Integer (1));
247         nvt.save();
248     }
249     
250     /**
251      * test boolean values
252      * @throws Exception if the test fails
253      */
254     public void testBooleanValues() throws Exception
255     {
256         BooleanCheck bc = BooleanCheckPeer.retrieveByPK(new StringKey("t1"));
257         assertTrue("BOOLEANINT should be true but is: "
258                 + bc.getBintValue(), bc.getBintValue());
259         assertTrue("BOOLEANCHAR should be true but is: "
260                 + bc.getBcharValue(), bc.getBcharValue());
261         bc = BooleanCheckPeer.retrieveByPK(new StringKey("f1"));
262         assertFalse("BOOLEANINT should be false but is: "
263                 + bc.getBintValue(), bc.getBintValue());
264         assertFalse("BOOLEANCHAR should be false but is: "
265                 + bc.getBcharValue(), bc.getBcharValue());
266     }
267     
268     /**
269      * Tests whether column type BIT can be written and read correctly
270      * and works in criteria as expected
271      * @throws Exception if the test fails
272      */
273     public void testBitType() throws Exception
274     {
275         if (Torque.getDB(Torque.getDefaultDB()) instanceof DBOracle)
276         {
277             log.error("testBitType(): BIT is known not to work with Oracle");
278             // failing is "expected", so exit without error
279 return;
280         }
281         
282         // clean table
283 Criteria criteria = new Criteria();
284         criteria.add(BitTestPeer.ID, (Object ) null, Criteria.NOT_EQUAL);
285         BitTestPeer.doDelete(criteria);
286         
287         // insert Data
288 BitTest bitTest = new BitTest();
289         bitTest.setId("t1");
290         bitTest.setBitValue(true);
291         bitTest.save();
292         bitTest = new BitTest();
293         bitTest.setId("f1");
294         bitTest.setBitValue(false);
295         bitTest.save();
296
297         // read data
298 bitTest = BitTestPeer.retrieveByPK(new StringKey("t1"));
299         assertTrue("BIT should be true but is: "
300                 + bitTest.getBitValue(), bitTest.getBitValue());
301         
302         bitTest = BitTestPeer.retrieveByPK(new StringKey("f1"));
303         assertFalse("BIT should be false but is: "
304                 + bitTest.getBitValue(), bitTest.getBitValue());
305         
306         // query data
307 criteria.clear();
308         criteria.add(BitTestPeer.BIT_VALUE, new Boolean (true));
309         List bitTestList = BitTestPeer.doSelect(criteria);
310         assertTrue("Should have read 1 dataset "
311                 + "but read " + bitTestList.size(),
312                 bitTestList.size() == 1);
313         bitTest = (BitTest) bitTestList.get(0);
314         // use trim() for testkey because some databases will return the
315 // testkey filled up with blanks, as it is defined as char(10)
316 assertTrue("Primary key of data set should be t1 but is "
317                 + bitTest.getId().trim(),
318                 "t1".equals(bitTest.getId().trim()));
319
320         criteria.clear();
321         criteria.add(BitTestPeer.BIT_VALUE, new Boolean (false));
322         bitTestList = BitTestPeer.doSelect(criteria);
323         assertTrue("Should have read 1 dataset "
324                 + "but read " + bitTestList.size(),
325                 bitTestList.size() == 1);
326         bitTest = (BitTest) bitTestList.get(0);
327         assertTrue("Primary key of data set should be f1 but is "
328                 + bitTest.getId().trim(),
329                 "f1".equals(bitTest.getId().trim()));
330
331     }
332     
333     /**
334      * check whether we can select from boolean columns
335      * @throws Exception if the test fails
336      */
337     public void testBooleanSelects() throws Exception
338     {
339         Criteria criteria = new Criteria();
340         criteria.add(BooleanCheckPeer.BCHAR_VALUE, new Boolean (true));
341         criteria.add(BooleanCheckPeer.BINT_VALUE, new Boolean (true));
342         List booleanCheckList = BooleanCheckPeer.doSelect(criteria);
343         assertTrue("Should have read 1 dataset with both values true "
344                 + "but read " + booleanCheckList.size(),
345                 booleanCheckList.size() == 1);
346         BooleanCheck booleanCheck = (BooleanCheck) booleanCheckList.get(0);
347         // use trim() for testkey because some databases will return the
348 // testkey filled up with blanks, as it is defined as char(10)
349 assertTrue("Primary key of data set should be t1 but is "
350                 + booleanCheck.getTestKey().trim(),
351                 "t1".equals(booleanCheck.getTestKey().trim()));
352         
353         criteria.clear();
354         criteria.add(BooleanCheckPeer.BCHAR_VALUE, new Boolean (false));
355         criteria.add(BooleanCheckPeer.BINT_VALUE, new Boolean (false));
356         booleanCheckList = BooleanCheckPeer.doSelect(criteria);
357         assertTrue("Should have read 1 dataset with both values false "
358                 + "but read " + booleanCheckList.size(),
359                 booleanCheckList.size() == 1);
360         booleanCheck = (BooleanCheck) booleanCheckList.get(0);
361         assertTrue("Primary key of data set should be f1 but is "
362                 + booleanCheck.getTestKey().trim(),
363                 "f1".equals(booleanCheck.getTestKey().trim()));
364     }
365
366     /**
367      * test whether delete works as expected
368      * @throws Exception if the test fails
369      */
370     public void testDelete() throws Exception
371     {
372         cleanBookstore();
373         
374         Author author = new Author();
375         author.setName("Name");
376         author.save();
377
378         Book book = new Book();
379         book.setTitle("title");
380         book.setAuthor(author);
381         book.setIsbn("ISBN");
382         book.save();
383
384         // delete without matching data
385 Criteria criteria = new Criteria();
386         criteria.add(
387                 AuthorPeer.AUTHOR_ID,
388                 author.getAuthorId(),
389                 Criteria.NOT_EQUAL);
390         AuthorPeer.doDelete(criteria);
391         List authorResult = AuthorPeer.doSelect(new Criteria());
392         assertTrue("deleted too many records", authorResult.size() == 1);
393
394         BookPeer.doDelete(book);
395         List bookResult = BookPeer.doSelect(new Criteria());
396         authorResult = AuthorPeer.doSelect(new Criteria());
397         // check that the book has disappeared
398 assertTrue("delete by object failed",
399             bookResult.size() == 0);
400         // check that the underlying author has not been deleted
401 assertTrue("delete by object deleted in cascade",
402             authorResult.size() == 1);
403
404         // delete with matching data
405 criteria.clear();
406         criteria.add(AuthorPeer.AUTHOR_ID, author.getAuthorId());
407         AuthorPeer.doDelete(criteria);
408         authorResult = AuthorPeer.doSelect(new Criteria());
409         assertTrue("deleted not enough records",
410             authorResult.size() == 0);
411     }
412     
413     /**
414      * test special cases in the select clause
415      * @throws Exception if the test fails
416      */
417     public void testSelectClause() throws Exception
418     {
419         // test double functions in select columns
420 Criteria criteria = new Criteria();
421         criteria.addSelectColumn("count(distinct(" + BookPeer.BOOK_ID + "))");
422         List result = BookPeer.doSelectVillageRecords(criteria);
423         
424         // test qualifiers in function in select columns
425 criteria = new Criteria();
426         criteria.addSelectColumn("count(distinct " + BookPeer.BOOK_ID + ")");
427         result = BookPeer.doSelectVillageRecords(criteria);
428     }
429     
430     /**
431      * test the behaviour if a connection is supplied to access the database,
432      * but it is null. All methods on the user level should be able to
433      * handle this.
434      */
435     public void testNullConnection() throws Exception
436     {
437         Criteria criteria = new Criteria();
438         List result = BookPeer.doSelectVillageRecords(criteria, null);
439         
440         criteria = new Criteria();
441         criteria.add(BookPeer.BOOK_ID, (Long ) null, Criteria.NOT_EQUAL);
442         BookPeer.doDelete(criteria, null);
443         
444         Author author = new Author();
445         author.setName("name");
446         author.save((Connection ) null);
447     }
448     
449     /**
450      * test joins
451      * @throws Exception if the test fails
452      */
453     public void testJoins() throws Exception
454     {
455         cleanBookstore();
456
457         // insert test data
458 Author author = new Author();
459         author.setName("Author with one book");
460         author.save();
461         Book book = new Book();
462         book.setAuthor(author);
463         book.setTitle("Book 1");
464         book.setIsbn("unknown");
465         book.save();
466       
467         author = new Author();
468         author.setName("Author without book");
469         author.save();
470
471         author = new Author();
472         author.setName("Author with three books");
473         author.save();
474         for (int bookNr = 2; bookNr <=4; bookNr++)
475         {
476             book = new Book();
477             book.setAuthor(author);
478             book.setTitle("Book " + bookNr);
479             book.setIsbn("unknown");
480             book.save();
481         }
482
483         // test left join
484 Criteria criteria = new Criteria();
485         criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID,
486                 Criteria.LEFT_JOIN);
487         List authorList = AuthorPeer.doSelect(criteria);
488         // Here we get 5 authors:
489 // the author with one book, the author without books,
490 // and three times the author with three books
491 if (authorList.size() != 5)
492         {
493             fail("author left join book : "
494                      + "incorrect numbers of authors found : "
495                      + authorList.size()
496                      + ", should be 5");
497         }
498       
499         // test inner join
500 criteria = new Criteria();
501         criteria.addJoin(
502                 AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID,
503                 Criteria.INNER_JOIN);
504         authorList = AuthorPeer.doSelect(criteria);
505         // Here we get 4 authors:
506 // the author with one book,
507 // and three times the author with three books
508 if (authorList.size() != 4)
509         {
510             fail("author left join book : "
511                      + "incorrect numbers of authors found : "
512                      + authorList.size()
513                      + ", should be 4");
514         }
515
516         if (Torque.getDB(Torque.getDefaultDB()) instanceof DBHypersonicSQL)
517         {
518             log.error("testJoins(): Right joins are not supported by HSQLDB");
519             // failing is "expected", so exit without error
520 return;
521         }
522
523         // test right join
524 criteria = new Criteria();
525         criteria.addJoin(
526                 BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID,
527                 Criteria.RIGHT_JOIN);
528         authorList = AuthorPeer.doSelect(criteria);
529         // Here we get 4 authors:
530 // the author with one book, the author without books,
531 // and three times the author with three books
532 if (authorList.size() != 5)
533         {
534             fail("book right join author "
535                      + "incorrect numbers of authors found : "
536                      + authorList.size()
537                      + ", should be 5");
538         }
539         
540         // test double join with aliases
541 criteria = new Criteria();
542         criteria.addAlias("b", BookPeer.TABLE_NAME);
543         criteria.addJoin(
544                 BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID,
545                 Criteria.RIGHT_JOIN);
546         criteria.addJoin(
547                 AuthorPeer.AUTHOR_ID,
548                 "b." + getRawColumnName(BookPeer.AUTHOR_ID),
549                 Criteria.LEFT_JOIN);
550         authorList = AuthorPeer.doSelect(criteria);
551         // Here we get 11 authors:
552 // the author with one book, the author without books,
553 // and nine times the author with three books
554 if (authorList.size() != 11)
555         {
556             fail("book right join author left join book b: "
557                      + "incorrect numbers of authors found : "
558                      + authorList.size()
559                      + ", should be 11");
560         }
561         
562         // test double join with aliases and "reversed" second join
563 criteria = new Criteria();
564         criteria.addAlias("b", BookPeer.TABLE_NAME);
565         criteria.addJoin(BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID,
566                 Criteria.RIGHT_JOIN);
567         criteria.addJoin(
568                 "b." + getRawColumnName(BookPeer.AUTHOR_ID),
569                 AuthorPeer.AUTHOR_ID,
570                 Criteria.RIGHT_JOIN);
571         authorList = AuthorPeer.doSelect(criteria);
572         // Here we get 11 authors:
573 // the author with one book, the author without books,
574 // and nine times the author with three books
575 if (authorList.size() != 11)
576         {
577             fail("book right join author left join book b (reversed): "
578                      + "incorrect numbers of authors found : "
579                      + authorList.size()
580                      + ", should be 11");
581         }
582     }
583     
584     
585     /**
586      * Test joins using the XPeer.DoSelectJoinYYY methods
587      * @throws Exception if the Test fails
588      */
589     public void testDoSelectJoinY() throws Exception
590     {
591         // using the test data from testJoins()
592 Criteria criteria = new Criteria();
593         criteria.addAscendingOrderByColumn(BookPeer.TITLE);
594         List books = MyBookPeer.doSelectJoinAuthor(criteria);
595         assertTrue("books should contain 4 books but contains "
596                 + books.size(), books.size() == 4);
597         Book bookTwo = (Book) books.get(1);
598         Book bookThree = (Book) books.get(2);
599         assertTrue ("the authors of BookTwo and BookThree"
600                 + " should point to the same instance",
601                 bookTwo.getAuthor() == bookThree.getAuthor());
602     }
603
604     /**
605      * test the order by, especially in joins and with aliases
606      * @throws Exception if the test fails
607      */
608     public void testOrderBy() throws Exception
609     {
610         cleanBookstore();
611
612         // insert test data
613 Author firstAuthor = new Author();
614         firstAuthor.setName("Author 1");
615         firstAuthor.save();
616         Book book = new Book();
617         book.setAuthor(firstAuthor);
618         book.setTitle("Book 1");
619         book.setIsbn("unknown");
620         book.save();
621             
622         Author secondAuthor = new Author();
623         secondAuthor.setName("Author 2");
624         secondAuthor.save();
625         for (int bookNr = 2; bookNr <=4; bookNr++)
626         {
627             book = new Book();
628             book.setAuthor(secondAuthor);
629             book.setTitle("Book " + bookNr);
630             book.setIsbn("unknown");
631             book.save();
632         }
633         
634         // test simple ascending order by
635 Criteria criteria = new Criteria();
636         criteria.addAscendingOrderByColumn(BookPeer.TITLE);
637         List bookList = BookPeer.doSelect(criteria);
638         if (bookList.size() != 4)
639         {
640             fail("Ascending Order By: "
641                      + "incorrect numbers of books found : "
642                      + bookList.size()
643                      + ", should be 4");
644         }
645         if (! "Book 1".equals(((Book) bookList.get(0)).getTitle()))
646         {
647             fail("Ascending Order By: "
648                      + "Title of first Book is "
649                      + ((Book) bookList.get(0)).getTitle()
650                      + ", should be \"Book 1\"");
651         }
652         if (! "Book 4".equals(((Book) bookList.get(3)).getTitle()))
653         {
654             fail("Ascending Order By: "
655                      + "Title of fourth Book is "
656                      + ((Book) bookList.get(3)).getTitle()
657                      + ", should be \"Book 4\"");
658         }
659         
660         // test simple descending order by
661 criteria = new Criteria();
662         criteria.addDescendingOrderByColumn(BookPeer.TITLE);
663         bookList = BookPeer.doSelect(criteria);
664         if (bookList.size() != 4)
665         {
666             fail("Descending Order By: "
667                      + "incorrect numbers of books found : "
668                      + bookList.size()
669                      + ", should be 4");
670         }
671         if (! "Book 1".equals(((Book) bookList.get(3)).getTitle()))
672         {
673             fail("Descending Order By: "
674                      + "Title of fourth Book is "
675                      + ((Book) bookList.get(3)).getTitle()
676                      + ", should be \"Book 1\"");
677         }
678         if (! "Book 4".equals(((Book) bookList.get(0)).getTitle()))
679         {
680             fail("Descending Order By: "
681                      + "Title of first Book is "
682                      + ((Book) bookList.get(0)).getTitle()
683                      + ", should be \"Book 4\"");
684         }
685         
686         // test ordering by Aliases and in joins
687 criteria = new Criteria();
688         criteria.addAlias("b", BookPeer.TABLE_NAME);
689         criteria.addJoin(BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID);
690         criteria.addJoin(
691                 AuthorPeer.AUTHOR_ID,
692                 "b." + getRawColumnName(BookPeer.AUTHOR_ID));
693         criteria.addAscendingOrderByColumn(
694                 "b." + getRawColumnName(BookPeer.TITLE));
695         criteria.addDescendingOrderByColumn(BookPeer.TITLE);
696         // the retrieved columns are
697 // author book b
698 // author1 book1 book1
699 // author2 book4 book2
700 // author2 book3 book2
701 // author2 book2 book2
702 // author2 book4 book3
703 // ...
704 bookList = BookPeer.doSelect(criteria);
705         if (bookList.size() != 10)
706         {
707             fail("ordering by Aliases: "
708                      + "incorrect numbers of books found : "
709                      + bookList.size()
710                      + ", should be 10");
711         }
712         if (!"Book 4".equals(((Book)bookList.get(1)).getTitle()))
713         {
714             fail("ordering by Aliases: "
715                      + "Title of second Book is "
716                      + ((Book) bookList.get(1)).getTitle()
717                      + ", should be \"Book 4\"");
718         }
719         if (!"Book 3".equals(((Book)bookList.get(2)).getTitle()))
720         {
721             fail("ordering by Aliases: "
722                      + "Title of third Book is "
723                      + ((Book) bookList.get(2)).getTitle()
724                      + ", should be \"Book 3\"");
725         }
726         
727         criteria = new Criteria();
728         criteria.addAlias("b", BookPeer.TABLE_NAME);
729         criteria.addJoin(BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID);
730         criteria.addJoin(
731                 AuthorPeer.AUTHOR_ID,
732                 "b." + getRawColumnName(BookPeer.AUTHOR_ID));
733         criteria.addAscendingOrderByColumn(BookPeer.TITLE);
734         criteria.addDescendingOrderByColumn(
735                 "b." + getRawColumnName(BookPeer.TITLE));
736         // the retrieved columns are
737 // author book b
738 // author1 book1 book1
739 // author2 book2 book4
740 // author2 book2 book3
741 // author2 book2 book2
742 // author2 book3 book4
743 // ...
744 bookList = BookPeer.doSelect(criteria);
745         if (bookList.size() != 10)
746         {
747             fail("ordering by Aliases (2): "
748                      + "incorrect numbers of books found : "
749                      + bookList.size()
750                      + ", should be 10");
751         }
752         if (!"Book 2".equals(((Book)bookList.get(1)).getTitle()))
753         {
754             fail("ordering by Aliases (2, PS): "
755                      + "Title of second Book is "
756                      + ((Book) bookList.get(1)).getTitle()
757                      + ", should be \"Book 2\"");
758         }
759         if (!"Book 2".equals(((Book)bookList.get(2)).getTitle()))
760         {
761             fail("ordering by Aliases (2, PS): "
762                      + "Title of third Book is "
763                      + ((Book) bookList.get(2)).getTitle()
764                      + ", should be \"Book 2\"");
765         }
766         
767         // test usage of Expressions in order by
768 criteria = new Criteria();
769         criteria.addAscendingOrderByColumn("UPPER(" + BookPeer.TITLE + ")");
770         criteria.setIgnoreCase(true);
771         BookPeer.doSelect(criteria);
772     }
773     
774     
775     /**
776      * Tests whether ignoreCase works correctly
777      * @throws Exception if the test fails
778      */
779     public void testIgnoreCase() throws Exception
780     {
781         cleanBookstore();
782
783         Author author = new Author();
784         author.setName("AuTHor");
785         author.save();
786         
787         Criteria criteria = new Criteria();
788         criteria.add(AuthorPeer.NAME, author.getName().toLowerCase());
789         criteria.setIgnoreCase(true);
790         List result = AuthorPeer.doSelect(criteria);
791         if (result.size() != 1)
792         {
793             fail("Size of result is not 1, but " + result.size());
794         }
795     }
796     
797     /**
798      * tests whether AsColumns produce valid SQL code
799      * @throws Exception if the test fails
800      */
801     public void testAsColumn() throws Exception
802     {
803         Criteria criteria = new Criteria();
804         criteria.addAsColumn("ALIASNAME", AuthorPeer.NAME);
805         // we need an additional column to select from,
806 // to indicate the table we want use
807 criteria.addSelectColumn(AuthorPeer.AUTHOR_ID);
808         BasePeer.doSelect(criteria);
809     }
810     
811     /**
812      * Test whether same column name in different tables
813      * are handled correctly
814      * @throws Exception if the test fails
815      */
816     public void testSameColumnName() throws Exception
817     {
818         cleanBookstore();
819         Author author = new Author();
820         author.setName("Name");
821         author.save();
822         
823         author = new Author();
824         author.setName("NotCorrespondingName");
825         author.save();
826         
827         Book book = new Book();
828         book.setTitle("Name");
829         book.setAuthor(author);
830         book.setIsbn("unknown");
831         book.save();
832         
833         Criteria criteria = new Criteria();
834         criteria.addJoin(BookPeer.TITLE, AuthorPeer.NAME);
835         BookPeer.addSelectColumns(criteria);
836         AuthorPeer.addSelectColumns(criteria);
837         // basically a BaseBookPeer.setDbName(criteria);
838 // and BasePeer.doSelect(criteria);
839 List villageRecords = BookPeer.doSelectVillageRecords(criteria);
840         Record record = (Record) villageRecords.get(0);
841         book = new Book();
842         BookPeer.populateObject(record, 1, book);
843         author = new Author();
844         AuthorPeer.populateObject(record, BookPeer.numColumns + 1, author);
845
846         if (book.getAuthorId() == author.getAuthorId()) {
847             fail("wrong Ids read");
848         }
849     }
850     
851     /**
852      * Tests the date, time and datetime accuracy.
853      * At the moment, no upper limit for the accuracy is checked,
854      * the differences are printed to stdout.
855      * @throws Exception if the test fails
856      */
857     public void testDateTime() throws Exception
858     {
859         // clean Date table
860 Criteria criteria = new Criteria();
861         criteria.add(
862                 DateTestPeer.DATE_TEST_ID,
863                 (Long ) null,
864                 Criteria.NOT_EQUAL);
865         DateTestPeer.doDelete(criteria);
866         
867         // insert new DateTest object to db
868 DateTest dateTest = new DateTest();
869         Date now = new Date ();
870         dateTest.setDateValue(now);
871         dateTest.setTimeValue(now);
872         dateTest.setTimestampValue(now);
873         dateTest.save();
874         DateFormat dateFormat = new SimpleDateFormat ();
875         System.out.println(
876                 "testDateTime() : set date to : "
877                 + dateFormat.format(now));
878         
879         // reload dateTest from db
880 DateTest loadedDateTest
881                 = DateTestPeer.retrieveByPK(dateTest.getPrimaryKey());
882         
883         System.out.println(
884                 "testDateTime() : retrieved date : "
885                 + dateFormat.format(loadedDateTest.getDateValue()));
886         System.out.println(
887                 "testDateTime() : retrieved time : "
888                 + dateFormat.format(loadedDateTest.getTimeValue()));
889         System.out.println(
890                 "testDateTime() : retrieved timestamp : "
891                 + dateFormat.format(loadedDateTest.getTimestampValue()));
892
893         // compute time differences between reloaded and original object
894 long dateDifference
895                 = dateTest.getDateValue().getTime()
896                     - loadedDateTest.getDateValue().getTime();
897         long timeDifference
898                 = dateTest.getTimeValue().getTime()
899                     - loadedDateTest.getTimeValue().getTime();
900         long timestampDifference
901                 = dateTest.getTimestampValue().getTime()
902                     - loadedDateTest.getTimestampValue().getTime();
903         
904         System.out.println(
905                 "testDateTime() : Date difference (ms): "
906                 + dateDifference);
907         System.out.println(
908                 "testDateTime() : Time difference (ms): "
909                 + timeDifference);
910         System.out.println(
911                 "testDateTime() : Timestamp difference (ms): "
912                 + timestampDifference);
913     }
914     
915     /**
916      * tests whether large primary keys are inserted and read correctly
917      * @throws Exception if the test fails
918      */
919     public void testLargePk() throws Exception
920     {
921         // clean LargePk table
922 Criteria criteria = new Criteria();
923         criteria.add(
924                 LargePkPeer.LARGE_PK_ID,
925                 (Long ) null,
926                 Criteria.NOT_EQUAL);
927         LargePkPeer.doDelete(criteria);
928
929         long longId = 8771507845873286l;
930         LargePk largePk = new LargePk();
931         largePk.setLargePkId(longId);
932         largePk.setName("testLargePk");
933         largePk.save();
934         
935         List largePkList = LargePkPeer.doSelect(new Criteria());
936         LargePk readLargePk = (LargePk) largePkList.get(0);
937         assertTrue("the inserted Id, " + largePk.getLargePkId()
938                 + " , and the read id, " + readLargePk.getLargePkId()
939                 + " , should be equal",
940                 readLargePk.getLargePkId() == largePk.getLargePkId());
941         assertTrue("the inserted Id, " + largePk.getLargePkId()
942                 + " , should be equal to " + longId,
943                 longId == largePk.getLargePkId());
944     }
945     
946     /**
947      * Tests the CountHelper class
948      * @throws Exception if the test fails
949      */
950     public void testCountHelper() throws Exception
951     {
952         cleanBookstore();
953         Author author = new Author();
954         author.setName("Name");
955         author.save();
956         
957         author = new Author();
958         author.setName("Name2");
959         author.save();
960         
961         author = new Author();
962         author.setName("Name");
963         author.save();
964         
965         Criteria criteria = new Criteria();
966         int count = new CountHelper().count(
967                 criteria,
968                 null,
969                 AuthorPeer.AUTHOR_ID);
970         
971         if (count != 3) {
972             fail("counted " + count + " datasets, should be 3 ");
973         }
974                     
975         criteria = new Criteria();
976         criteria.setDistinct();
977         count = new CountHelper().count(criteria, null, AuthorPeer.NAME);
978         
979         if (count != 2) {
980             fail("counted " + count + " distinct datasets, should be 2 ");
981         }
982
983         criteria = new Criteria();
984         criteria.add(AuthorPeer.NAME, "Name2");
985         count = new CountHelper().count(criteria);
986         
987         if (count != 1) {
988             fail("counted " + count + " datasets with name Name2,"
989                  + " should be 1 ");
990         }
991     }
992     
993     
994     /**
995      * Tests whether we can handle multiple primary keys some of which are
996      * also foreign keys
997      * @throws Exception if the test fails
998      */
999     public void testMultiplePrimaryForeignKey() throws Exception
1000    {
1001        IntegerPk integerPk = new IntegerPk();
1002        integerPk.save();
1003        MultiPkForeignKey multiPkForeignKey = new MultiPkForeignKey();
1004        multiPkForeignKey.setId(10);
1005        multiPkForeignKey.setIntegerPk(integerPk);
1006        multiPkForeignKey.save();
1007        integerPk.save();
1008    }
1009        
1010    /**
1011     * Tests inserting single quotes in Strings.
1012     * This may not crash now, but in a later task like datasql,
1013     * so the data has to be inserted in a table which does not get cleaned
1014     * during the runtime test.
1015     * @throws Exception if inserting the test data fails
1016     */
1017    public void testSingleQuotes() throws Exception
1018    {
1019        // clean A table
1020 Criteria criteria = new Criteria();
1021        criteria.add(APeer.A_ID, (Long ) null, Criteria.NOT_EQUAL);
1022        APeer.doDelete(criteria);
1023        
1024        A a = new A();
1025        a.setName("has Single ' Quote");
1026        a.save();
1027    }
1028    
1029    
1030    /**
1031     * check that blob cloumns can be read and written correctly
1032     * @throws Exception if the test fails
1033     */
1034    public void testBlobs() throws Exception
1035    {
1036        // clean BlobTest table
1037 {
1038            Criteria criteria = new Criteria();
1039            criteria.add(
1040                    BlobTestPeer.ID,
1041                    (Long ) null,
1042                    Criteria.NOT_EQUAL);
1043            BlobTestPeer.doDelete(criteria);
1044        }
1045
1046        // create a new BlobTest Object with large blob and clob values
1047 // and save it
1048 BlobTest blobTest = new BlobTest();
1049        {
1050            int length = 100000;
1051            byte[] bytes = new byte[length];
1052            StringBuffer chars = new StringBuffer ();
1053            String charTemplate = "1234567890abcdefghijklmnopqrstuvwxyz";
1054            for (int i = 0; i < length; ++i)
1055            {
1056                bytes[i] = new Integer (i % 256).byteValue();
1057                chars.append(charTemplate.charAt(i % charTemplate.length()));
1058            }
1059            blobTest.setBlobValue(bytes);
1060        }
1061        blobTest.save();
1062        
1063        // read the BlobTests from the database
1064 // and check the values against the original values
1065 List blobTestList = BlobTestPeer.doSelect(new Criteria());
1066        assertTrue("blobTestList should contain 1 object but contains "
1067                + blobTestList.size(),
1068                blobTestList.size() == 1);
1069        
1070        BlobTest readBlobTest = (BlobTest) blobTestList.get(0);
1071        assertTrue("read and written blobs should be equal. "
1072                + "Size of read blob is"
1073                + readBlobTest.getBlobValue().length
1074                + " size of written blob is "
1075                + blobTest.getBlobValue().length,
1076                Arrays.equals(
1077                        blobTest.getBlobValue(),
1078                        readBlobTest.getBlobValue()));
1079    }
1080        
1081        
1082    /**
1083     * check that clob cloumns can be read and written correctly
1084     * @throws Exception if the test fails
1085     */
1086    public void testClobs() throws Exception
1087    {
1088        // clean ClobTest table
1089 {
1090            Criteria criteria = new Criteria();
1091            criteria.add(
1092                    ClobTestPeer.ID,
1093                    (Long ) null,
1094                    Criteria.NOT_EQUAL);
1095            ClobTestPeer.doDelete(criteria);
1096        }
1097
1098        // create a new ClobTest Object with a large clob value
1099 // and save it
1100 ClobTest clobTest = new ClobTest();
1101        {
1102            int length = 10000;
1103            StringBuffer chars = new StringBuffer ();
1104            String charTemplate = "1234567890abcdefghijklmnopqrstuvwxyz";
1105            for (int i = 0; i < length; ++i)
1106            {
1107                 chars.append(charTemplate.charAt(i % charTemplate.length()));
1108            }
1109            clobTest.setClobValue(chars.toString());
1110        }
1111        clobTest.save();
1112        
1113        // read the ClobTests from the database
1114 // and check the values against the original values
1115 List clobTestList = ClobTestPeer.doSelect(new Criteria());
1116        assertTrue("clobTestList should contain 1 object but contains "
1117                + clobTestList.size(),
1118                clobTestList.size() == 1);
1119        
1120        ClobTest readClobTest = (ClobTest) clobTestList.get(0);
1121        assertTrue("read and written clobs should be equal",
1122                clobTest.getClobValue().equals(readClobTest.getClobValue()));
1123    }
1124    
1125    /**
1126     * Test whether we can execute queries as prepared statements
1127     * @throws Exception
1128     */
1129    public void testPreparedStatements() throws Exception
1130    {
1131        // clean LargePk table
1132 Criteria criteria = new Criteria();
1133        criteria.add(
1134                LargePkPeer.LARGE_PK_ID,
1135                (Long ) null,
1136                Criteria.NOT_EQUAL);
1137        LargePkPeer.doDelete(criteria);
1138
1139        LargePk largePk = new LargePk();
1140        largePk.setLargePkId(1);
1141        largePk.setName("testLargePk");
1142        largePk.save();
1143        
1144        largePk = new LargePk();
1145        largePk.setLargePkId(2);
1146        largePk.setName("testLargePk");
1147        largePk.save();
1148        
1149        criteria = new Criteria();
1150        criteria.add(LargePkPeer.LARGE_PK_ID, 2, Criteria.LESS_THAN);
1151        LargePkPeer.addSelectColumns(criteria);
1152        List result = BasePeer.doPSSelect(criteria);
1153        assertTrue("Size of largePk list should be 1 but is "
1154                + result.size(),
1155                result.size() == 1);
1156    }
1157    
1158    /**
1159     * Tests whether shutdown complains about anything
1160     * @throws TorqueException if shutdown does not exit cleanly
1161     */
1162    public void testShutdown() throws TorqueException
1163    {
1164        Torque.shutdown();
1165    }
1166
1167    /**
1168     * Deletes all authors and books in the bookstore tables
1169     * @throws Exception if the bookstore could not be cleaned
1170     */
1171    protected void cleanBookstore() throws Exception
1172    {
1173        Criteria criteria = new Criteria();
1174        criteria.add(BookPeer.BOOK_ID, (Long ) null, Criteria.NOT_EQUAL);
1175        BookPeer.doDelete(criteria);
1176
1177        criteria.clear();
1178        criteria.add(
1179                AuthorPeer.AUTHOR_ID,
1180                (Long ) null, Criteria.NOT_EQUAL);
1181        AuthorPeer.doDelete(criteria);
1182    }
1183
1184    
1185    /**
1186     * Strips the schema and table name from a fully qualified colum name
1187     * This is useful for creating Query with aliases, as the constants
1188     * for the colum names in the data objects are fully qualified.
1189     * @param fullyQualifiedColumnName the fully qualified column name, not null
1190     * @return the column name stripped from the table (and schema) prefixes
1191     */
1192    public static String getRawColumnName(String fullyQualifiedColumnName)
1193    {
1194        int dotPosition = fullyQualifiedColumnName.lastIndexOf(".");
1195        if (dotPosition == -1)
1196        {
1197            return fullyQualifiedColumnName;
1198        }
1199        String result = fullyQualifiedColumnName.substring(
1200                dotPosition + 1,
1201                fullyQualifiedColumnName.length());
1202        return result;
1203    }
1204    
1205    
1206    /**
1207     * Subclass of BookPeer to make the doSelectJoinAuthors() visible
1208     */
1209    static class MyBookPeer extends BookPeer
1210    {
1211        public static List doSelectJoinAuthor(Criteria criteria)
1212                throws TorqueException
1213        {
1214            return BookPeer.doSelectJoinAuthor(criteria);
1215        }
1216    }
1217}
1218

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags