Java tutorial
package org.apache.torque; /* * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you under the Apache License, Version 2.0 (the * "License"); you may not use this file except in compliance * with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, * software distributed under the License is distributed on an * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY * KIND, either express or implied. See the License for the * specific language governing permissions and limitations * under the License. */ import java.sql.Connection; import java.sql.ResultSet; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Set; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.torque.adapter.Adapter; import org.apache.torque.adapter.DerbyAdapter; import org.apache.torque.adapter.MssqlAdapter; import org.apache.torque.adapter.MysqlAdapter; import org.apache.torque.criteria.Criteria; import org.apache.torque.criteria.CriteriaInterface; import org.apache.torque.criteria.Criterion; import org.apache.torque.om.mapper.CompositeMapper; import org.apache.torque.om.mapper.IntegerMapper; import org.apache.torque.om.mapper.RecordMapper; import org.apache.torque.test.InheritanceClassnameTestChild1; import org.apache.torque.test.InheritanceClassnameTestChild2; import org.apache.torque.test.dbobject.Author; import org.apache.torque.test.dbobject.BigintType; import org.apache.torque.test.dbobject.Book; import org.apache.torque.test.dbobject.CompPkContainsFk; import org.apache.torque.test.dbobject.IfcTable; import org.apache.torque.test.dbobject.InheritanceChildB; import org.apache.torque.test.dbobject.InheritanceChildC; import org.apache.torque.test.dbobject.InheritanceChildD; import org.apache.torque.test.dbobject.InheritanceClassnameTest; import org.apache.torque.test.dbobject.InheritanceTest; import org.apache.torque.test.dbobject.IntegerType; import org.apache.torque.test.dbobject.LocalIfcTable; import org.apache.torque.test.dbobject.LocalTestInterface; import org.apache.torque.test.dbobject.MultiPk; import org.apache.torque.test.dbobject.Nopk; import org.apache.torque.test.dbobject.OIntegerPk; import org.apache.torque.test.dbobject.VarcharType; import org.apache.torque.test.peer.AuthorPeer; import org.apache.torque.test.peer.BigintTypePeer; import org.apache.torque.test.peer.BookPeer; import org.apache.torque.test.peer.CompPkContainsFkPeer; import org.apache.torque.test.peer.IfcTablePeer; import org.apache.torque.test.peer.InheritanceClassnameTestPeer; import org.apache.torque.test.peer.InheritanceTestPeer; import org.apache.torque.test.peer.IntegerTypePeer; import org.apache.torque.test.peer.LocalIfcTablePeer; import org.apache.torque.test.peer.MultiPkPeer; import org.apache.torque.test.peer.NopkPeer; import org.apache.torque.test.peer.VarcharTypePeer; import org.apache.torque.test.recordmapper.AuthorRecordMapper; import org.apache.torque.test.recordmapper.BookRecordMapper; import org.apache.torque.util.BasePeer; import org.apache.torque.util.CountHelper; import org.apache.torque.util.Transaction; /** * Runtime tests. * * @author <a href="mailto:seade@backstagetech.com.au">Scott Eade</a> * @author <a href="mailto:mpoeschl@marmot.at">Martin Poeschl</a> * @author <a href="mailto:fischer@seitenbau.de">Thomas Fischer</a> * @author <a href="mailto:patrick.carl@web.de">Patrick Carl</a> * @author <a href="mailto:tv@apache.org">Thomas Vandahl</a> * @version $Id: DataTest.java 1439631 2013-01-28 21:03:33Z tfischer $ */ public class DataTest extends BaseDatabaseTestCase { private static Log log = LogFactory.getLog(DataTest.class); /** * test whether we can connect to the database at all * @throws Exception if no connection can be established */ public void testConnect() throws Exception { Connection connection = null; try { connection = Torque.getConnection(); connection.close(); connection = null; } finally { if (connection != null) { connection.close(); } } } /** * multiple pk test (TRQ12) * @throws Exception if the test fails */ public void testMultiplePk() throws Exception { // clean table Criteria criteria = new Criteria(); criteria.where(MultiPkPeer.PK1, (Object) null, Criteria.NOT_EQUAL); MultiPkPeer.doDelete(criteria); // do test MultiPk mpk = new MultiPk(); mpk.setPrimaryKey("Svarchar:N5:Schar:N3:N-42:N3:N4:N5:N6:D9999999999:"); mpk.save(); // TODO assert saved values } private static final String[] validTitles = { "Book 6 - Author 4", "Book 6 - Author 5", "Book 6 - Author 6", "Book 6 - Author 7", "Book 6 - Author 8", "Book 7 - Author 4", "Book 7 - Author 5", "Book 7 - Author 6", "Book 7 - Author 7", "Book 7 - Author 8" }; /** * test limit/offset * * @throws Exception if the test fails */ public void testLimitOffset() throws Exception { cleanBookstore(); insertBookstoreData(); Set<String> titleSet = new HashSet<String>(); for (int j = 0; j < validTitles.length; j++) { titleSet.add(validTitles[j]); } Criteria crit = new Criteria(); Criterion c = new Criterion(BookPeer.TITLE, "Book 6 - Author 1", Criteria.GREATER_EQUAL); c.and(new Criterion(BookPeer.TITLE, "Book 8 - Author 3", Criteria.LESS_EQUAL)); crit.where(c); crit.addDescendingOrderByColumn(BookPeer.BOOK_ID); crit.setLimit(10); crit.setOffset(5); List<Book> books = BookPeer.doSelect(crit); assertTrue("List should have 10 books, not " + books.size(), books.size() == 10); for (Book book : books) { String title = book.getTitle(); assertTrue("Incorrect title: " + title, titleSet.contains(title)); } // Test limit of zero works crit = new Criteria(); crit.setLimit(0); try { books = BookPeer.doSelect(crit); assertTrue("List should have 0 books, not " + books.size(), books.size() == 0); } catch (TorqueException e) { if (defaultAdapter.supportsNativeLimit()) { if (!(defaultAdapter instanceof DerbyAdapter)) { throw e; } else { log.error("testLimitOffset(): " + "A limit of 0 is not supported for Derby"); } } else { log.error("testLimitOffset(): " + "A limit of 0 is not supported for Databases " + "without native limit support"); } } // check that Offset also works without limit crit = new Criteria(); crit.setOffset(5); books = BookPeer.doSelect(crit); assertTrue("List should have 95 books, not " + books.size(), books.size() == 95); // Check that limiting also works if a table with an equal column name // is joined. This is problematic for oracle, see TORQUE-10. crit = new Criteria(); crit.setLimit(10); crit.setOffset(5); books = BookPeer.doSelectJoinAuthor(crit); assertTrue("List should have 10 books, not " + books.size(), books.size() == 10); } /** * Checks whether the setSingleRecord() method in criteria works */ public void testSingleRecord() throws Exception { Criteria criteria = new Criteria(); criteria.setSingleRecord(true); criteria.setLimit(1); criteria.setOffset(5); List<Book> books = BookPeer.doSelect(criteria); assertTrue("List should have 1 books, not " + books.size(), books.size() == 1); criteria = new Criteria(); criteria.setSingleRecord(true); criteria.setLimit(2); try { books = BookPeer.doSelect(criteria); fail("doSelect should have failed " + "because two records were selected " + " and one was expected"); } catch (TorqueException e) { } } /** * Tests whether selects work correctly if the value <code>null</code> * is used. * @throws Exception if the test fails */ public void testNullSelects() throws Exception { // clean table VarcharTypePeer.doDelete(new Criteria()); IntegerTypePeer.doDelete(new Criteria()); // add test data VarcharType varcharType = new VarcharType(); varcharType.setId("text2"); varcharType.setVarcharValue("text2"); varcharType.save(); varcharType = new VarcharType(); varcharType.setId("text"); varcharType.save(); IntegerType integerTypeNotNull = new IntegerType(); integerTypeNotNull.setIntegerObjectValue(1); integerTypeNotNull.save(); IntegerType integerTypeNull = new IntegerType(); integerTypeNull.save(); // check for comparison NOT_EQUAL and value null Criteria criteria = new Criteria(); criteria.where(VarcharTypePeer.ID, null, Criteria.NOT_EQUAL).and(VarcharTypePeer.VARCHAR_VALUE, null, Criteria.NOT_EQUAL); List<VarcharType> varcharResult = VarcharTypePeer.doSelect(criteria); assertEquals(1, varcharResult.size()); assertEquals("text2", varcharResult.get(0).getId()); criteria = new Criteria(); criteria.where(IntegerTypePeer.ID, null, Criteria.NOT_EQUAL).and(IntegerTypePeer.INTEGER_OBJECT_VALUE, null, Criteria.NOT_EQUAL); List<IntegerType> integerResult = IntegerTypePeer.doSelect(criteria); assertEquals(1, integerResult.size()); assertEquals(integerTypeNotNull.getId(), integerResult.get(0).getId()); // check for comparison EQUAL and value null criteria = new Criteria(); criteria.where(VarcharTypePeer.VARCHAR_VALUE, null, Criteria.EQUAL); varcharResult = VarcharTypePeer.doSelect(criteria); assertEquals(1, varcharResult.size()); assertEquals("text", varcharResult.get(0).getId()); criteria = new Criteria(); criteria.where(IntegerTypePeer.INTEGER_OBJECT_VALUE, null, Criteria.EQUAL); integerResult = IntegerTypePeer.doSelect(criteria); assertEquals(1, integerResult.size()); assertEquals(integerTypeNull.getId(), integerResult.get(0).getId()); } /** * Test whether an update works and whether it only affects the * specified record. * @throws Exception if anything in the test goes wrong. */ public void testUpdate() throws Exception { cleanBookstore(); Author otherAuthor = new Author(); otherAuthor.setName("OtherName"); otherAuthor.save(); Author author = new Author(); author.setName("Name"); author.save(); // Test doUpdate methods in Peer explicitly Connection connection = Transaction.begin(AuthorPeer.DATABASE_NAME); author.setName("NewName2"); AuthorPeer.doUpdate(author); Transaction.commit(connection); Criteria criteria = new Criteria(); criteria.addAscendingOrderByColumn(AuthorPeer.NAME); List<Author> authors = AuthorPeer.doSelect(criteria); assertEquals("List should contain 2 authors", 2, authors.size()); assertEquals("First Author's name should be \"NewName2\"", "NewName2", authors.get(0).getName()); assertEquals("Second Author's name should be \"OtherName\"", "OtherName", authors.get(1).getName()); author.setName("NewName3"); AuthorPeer.doUpdate(author); criteria = new Criteria(); criteria.addAscendingOrderByColumn(AuthorPeer.NAME); authors = AuthorPeer.doSelect(criteria); assertEquals("List should contain 2 authors", 2, authors.size()); assertEquals("First Author's name should be \"NewName3\"", "NewName3", authors.get(0).getName()); assertEquals("Second Author's name should be \"OtherName\"", "OtherName", authors.get(1).getName()); Nopk nopk = new Nopk(); nopk.setName("name"); nopk.save(); // check the doPupdate Peer methods throw exceptions on a modified // object without primary keys try { NopkPeer.doUpdate(new Nopk()); fail("A Torque exception should be thrown (2)"); } catch (TorqueException e) { } connection = Transaction.begin(NopkPeer.DATABASE_NAME); try { NopkPeer.doUpdate(new Nopk(), connection); fail("A Torque exception should be thrown (3)"); } catch (TorqueException e) { } Transaction.safeRollback(connection); } /** * test special cases in the select clause * @throws Exception if the test fails */ public void testSelectClause() throws Exception { // test double functions in select columns Criteria criteria = new Criteria(); criteria.addSelectColumn(new ColumnImpl("count(distinct(" + BookPeer.BOOK_ID + "))")); BasePeer.doSelect(criteria, new IntegerMapper()); // test qualifiers in function in select columns criteria = new Criteria(); criteria.addSelectColumn(new ColumnImpl("count(distinct " + BookPeer.BOOK_ID + ")")); BasePeer.doSelect(criteria, new IntegerMapper()); } /** * test if a select from the "default" database works * @throws Exception (NPE) if the test fails */ public void testSelectFromDefault() throws Exception { Criteria criteria = new Criteria("default"); criteria.addSelectColumn(BookPeer.BOOK_ID); BasePeer.doSelect(criteria, new IntegerMapper()); } /** * Test the behaviour if a connection is supplied to access the database, * but it is null. All methods on the user level should be fail * because these methods will be only needed if a method should be executed * in a transaction context. If one assumes that a transaction is open * (connection is not null), but it is not (connection == null), * it is a bad idea to silently start one as this behaviour is very * difficult to tell from the correct one. A clean failure is much easier * to test for. */ public void testNullConnection() throws Exception { try { Criteria criteria = new Criteria(); AuthorPeer.doSelect(criteria, new IntegerMapper(), null); fail("NullPointerException expected"); } catch (NullPointerException e) { //expected } try { Criteria criteria = new Criteria(); criteria.where(BookPeer.BOOK_ID, (Long) null, Criteria.NOT_EQUAL); BookPeer.doDelete(criteria, (Connection) null); fail("NullPointerException expected"); } catch (NullPointerException e) { //expected } try { Author author = new Author(); author.setName("name"); author.save((Connection) null); fail("TorqueException expected"); } catch (TorqueException e) { //expected assertEquals("connection is null", e.getMessage()); } } /** * test the order by, especially in joins and with aliases * @throws Exception if the test fails */ public void testOrderBy() throws Exception { cleanBookstore(); // insert test data Author firstAuthor = new Author(); firstAuthor.setName("Author 1"); firstAuthor.save(); Book book = new Book(); book.setAuthor(firstAuthor); book.setTitle("Book 1"); book.setIsbn("unknown"); book.save(); Author secondAuthor = new Author(); secondAuthor.setName("Author 2"); secondAuthor.save(); for (int bookNr = 2; bookNr <= 4; bookNr++) { book = new Book(); book.setAuthor(secondAuthor); book.setTitle("Book " + bookNr); book.setIsbn("unknown"); book.save(); } // test simple ascending order by Criteria criteria = new Criteria(); criteria.addAscendingOrderByColumn(BookPeer.TITLE); List<Book> bookList = BookPeer.doSelect(criteria); if (bookList.size() != 4) { fail("Ascending Order By: " + "incorrect numbers of books found : " + bookList.size() + ", should be 4"); } if (!"Book 1".equals(bookList.get(0).getTitle())) { fail("Ascending Order By: " + "Title of first Book is " + bookList.get(0).getTitle() + ", should be \"Book 1\""); } if (!"Book 4".equals(bookList.get(3).getTitle())) { fail("Ascending Order By: " + "Title of fourth Book is " + bookList.get(3).getTitle() + ", should be \"Book 4\""); } // test simple descending order by criteria = new Criteria(); criteria.addDescendingOrderByColumn(BookPeer.TITLE); bookList = BookPeer.doSelect(criteria); if (bookList.size() != 4) { fail("Descending Order By: " + "incorrect numbers of books found : " + bookList.size() + ", should be 4"); } if (!"Book 1".equals(bookList.get(3).getTitle())) { fail("Descending Order By: " + "Title of fourth Book is " + bookList.get(3).getTitle() + ", should be \"Book 1\""); } if (!"Book 4".equals((bookList.get(0)).getTitle())) { fail("Descending Order By: " + "Title of first Book is " + bookList.get(0).getTitle() + ", should be \"Book 4\""); } criteria = new Criteria(); criteria.addAlias("b", BookPeer.TABLE_NAME); criteria.addJoin(BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID); criteria.addJoin(AuthorPeer.AUTHOR_ID, new ColumnImpl("b." + BookPeer.AUTHOR_ID.getColumnName())); criteria.addAscendingOrderByColumn(new ColumnImpl("b." + BookPeer.TITLE.getColumnName())); criteria.addDescendingOrderByColumn(BookPeer.TITLE); // the retrieved columns are // author book b // author1 book1 book1 // author2 book4 book2 // author2 book3 book2 // author2 book2 book2 // author2 book4 book3 // ... bookList = BookPeer.doSelect(criteria); if (bookList.size() != 10) { fail("ordering by Aliases: " + "incorrect numbers of books found : " + bookList.size() + ", should be 10"); } if (!"Book 4".equals(bookList.get(1).getTitle())) { fail("ordering by Aliases: " + "Title of second Book is " + bookList.get(1).getTitle() + ", should be \"Book 4\""); } if (!"Book 3".equals(bookList.get(2).getTitle())) { fail("ordering by Aliases: " + "Title of third Book is " + bookList.get(2).getTitle() + ", should be \"Book 3\""); } criteria = new Criteria(); criteria.addAlias("b", BookPeer.TABLE_NAME); criteria.addJoin(BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID); criteria.addJoin(AuthorPeer.AUTHOR_ID, new ColumnImpl("b." + BookPeer.AUTHOR_ID.getColumnName())); criteria.addAscendingOrderByColumn(BookPeer.TITLE); criteria.addDescendingOrderByColumn(new ColumnImpl("b." + BookPeer.TITLE.getColumnName())); // the retrieved columns are // author book b // author1 book1 book1 // author2 book2 book4 // author2 book2 book3 // author2 book2 book2 // author2 book3 book4 // ... bookList = BookPeer.doSelect(criteria); if (bookList.size() != 10) { fail("ordering by Aliases (2): " + "incorrect numbers of books found : " + bookList.size() + ", should be 10"); } if (!"Book 2".equals(bookList.get(1).getTitle())) { fail("ordering by Aliases (2, PS): " + "Title of second Book is " + bookList.get(1).getTitle() + ", should be \"Book 2\""); } if (!"Book 2".equals(bookList.get(2).getTitle())) { fail("ordering by Aliases (2, PS): " + "Title of third Book is " + bookList.get(2).getTitle() + ", should be \"Book 2\""); } // test usage of Expressions in order by criteria = new Criteria(); criteria.addAscendingOrderByColumn(new ColumnImpl("UPPER(" + BookPeer.TITLE + ")")); criteria.setIgnoreCase(true); BookPeer.doSelect(criteria); } /** * Tests whether ignoreCase works correctly * @throws Exception if the test fails */ public void testIgnoreCase() throws Exception { cleanBookstore(); // check ignore case in selects Author author = new Author(); author.setName("AuTHor"); author.save(); Criteria criteria = new Criteria(); criteria.where(AuthorPeer.NAME, author.getName().toLowerCase()); criteria.setIgnoreCase(true); List<Author> result = AuthorPeer.doSelect(criteria); assertTrue("Size of result is not 1, but " + result.size(), result.size() == 1); // LIKE treatment might be different (e.g. postgres), so check extra criteria = new Criteria(); criteria.where(AuthorPeer.NAME, author.getName().toLowerCase().replace('r', '%'), Criteria.LIKE); criteria.setIgnoreCase(true); result = AuthorPeer.doSelect(criteria); assertTrue("Size of result is not 1, but " + result.size(), result.size() == 1); // Test ignore case in criterion criteria = new Criteria(); Criterion criterion1 = new Criterion(AuthorPeer.NAME, author.getName().toLowerCase(), Criteria.EQUAL); criterion1.setIgnoreCase(true); Criterion criterion2 = new Criterion(AuthorPeer.AUTHOR_ID, null, Criteria.NOT_EQUAL); criterion1.and(criterion2); result = AuthorPeer.doSelect(criteria); // ignore case should not be set either in Criteria // nor in other criterions assertFalse(criteria.isIgnoreCase()); assertFalse(criterion2.isIgnoreCase()); assertTrue("Size of result is not 1, but " + result.size(), result.size() == 1); // Test ignore case in attached criterion criteria = new Criteria(); criterion1 = new Criterion(AuthorPeer.AUTHOR_ID, null, Criteria.NOT_EQUAL); criterion2 = new Criterion(AuthorPeer.NAME, author.getName().toLowerCase(), Criteria.EQUAL); criterion2.setIgnoreCase(true); criterion1.and(criterion2); result = AuthorPeer.doSelect(criteria); // ignore case should not be set either in Criteria // nor in other criterions assertFalse(criteria.isIgnoreCase()); assertFalse(criterion1.isIgnoreCase()); assertTrue("Size of result is not 1, but " + result.size(), result.size() == 1); // ignore case in "in" query { criteria = new Criteria(); Set<String> names = new HashSet<String>(); names.add(author.getName().toLowerCase()); criteria.where(AuthorPeer.NAME, names, Criteria.IN); criteria.setIgnoreCase(true); result = AuthorPeer.doSelect(criteria); assertEquals("Expected result of size 1 but got " + result.size(), result.size(), 1); } // Check that case is not ignored if ignoreCase is not set // This is known not to work for mysql author = new Author(); author.setName("author"); author.save(); Adapter adapter = Torque.getAdapter(Torque.getDefaultDB()); if (adapter instanceof MysqlAdapter || adapter instanceof MssqlAdapter) { log.error("testIgnoreCase(): " + "Case sensitive comparisons are known not to work" + " with Mysql and MSSQL"); // failing is "expected", so bypass without error } else { criteria = new Criteria(); criteria.where(AuthorPeer.NAME, author.getName()); result = AuthorPeer.doSelect(criteria); assertTrue("Size of result is not 1, but " + result.size(), result.size() == 1); // again check LIKE treatment criteria = new Criteria(); criteria.where(AuthorPeer.NAME, author.getName().replace('r', '%'), Criteria.LIKE); result = AuthorPeer.doSelect(criteria); assertTrue("Size of result is not 1, but " + result.size(), result.size() == 1); // Test different ignore cases in criterions criteria = new Criteria(); criterion1 = new Criterion(AuthorPeer.NAME, author.getName().toLowerCase(), Criteria.NOT_EQUAL); criterion2 = new Criterion(AuthorPeer.NAME, author.getName().toLowerCase(), Criteria.EQUAL); criterion2.setIgnoreCase(true); criterion1.and(criterion2); criteria.where(criterion1); result = AuthorPeer.doSelect(criteria); assertTrue("Size of result is not 1, but " + result.size(), result.size() == 1); // ignore case in "in" query { criteria = new Criteria(); Set<String> names = new HashSet<String>(); names.add(author.getName()); criteria.where(AuthorPeer.NAME, names, Criteria.IN); result = AuthorPeer.doSelect(criteria); assertEquals("Expected result of size 1 but got " + result.size(), result.size(), 1); } } cleanBookstore(); author = new Author(); author.setName("AA"); author.save(); author = new Author(); author.setName("BB"); author.save(); author = new Author(); author.setName("ba"); author.save(); author = new Author(); author.setName("ab"); author.save(); // check ignoreCase in Criteria criteria = new Criteria(); criteria.setIgnoreCase(true); criteria.addAscendingOrderByColumn(AuthorPeer.NAME); result = AuthorPeer.doSelect(criteria); assertTrue("Size of result is not 4, but " + result.size(), result.size() == 4); assertEquals(result.get(0).getName(), "AA"); assertEquals(result.get(1).getName(), "ab"); assertEquals(result.get(2).getName(), "ba"); assertEquals(result.get(3).getName(), "BB"); // check ignoreCase in orderBy criteria = new Criteria(); criteria.addAscendingOrderByColumn(AuthorPeer.NAME, true); result = AuthorPeer.doSelect(criteria); assertTrue("Size of result is not 4, but " + result.size(), result.size() == 4); assertEquals(result.get(0).getName(), "AA"); assertEquals(result.get(1).getName(), "ab"); assertEquals(result.get(2).getName(), "ba"); assertEquals(result.get(3).getName(), "BB"); } /** * tests whether AsColumns produce valid SQL code * @throws Exception if the test fails */ public void testAsColumn() throws Exception { Criteria criteria = new Criteria(); criteria.addAsColumn("ALIASNAME", AuthorPeer.NAME); // we need an additional column to select from, // to indicate the table we want use criteria.addSelectColumn(AuthorPeer.AUTHOR_ID); BasePeer.doSelect(criteria, new DoNothingMapper()); } /** * Test whether same column name in different tables * are handled correctly * @throws Exception if the test fails */ public void testSameColumnName() throws Exception { cleanBookstore(); Author author = new Author(); author.setName("Name"); author.save(); author = new Author(); author.setName("NotCorrespondingName"); author.save(); Book book = new Book(); book.setTitle("Name"); book.setAuthor(author); book.setIsbn("unknown"); book.save(); Criteria criteria = new Criteria(); criteria.addJoin(BookPeer.TITLE, AuthorPeer.NAME); BookPeer.addSelectColumns(criteria); AuthorPeer.addSelectColumns(criteria); // basically a BaseBookPeer.setDbName(criteria); // and BasePeer.doSelect(criteria); CompositeMapper mapper = new CompositeMapper(); mapper.addMapper(new BookRecordMapper(), 0); mapper.addMapper(new AuthorRecordMapper(), BookPeer.numColumns); List<List<Object>> queryResult = BookPeer.doSelect(criteria, mapper); List<Object> mappedRow = queryResult.get(0); book = (Book) mappedRow.get(0); author = (Author) mappedRow.get(1); if (book.getAuthorId() == author.getAuthorId()) { fail("wrong Ids read"); } } /** * tests whether large primary keys are inserted and read correctly * @throws Exception if the test fails */ public void testLargePk() throws Exception { if (defaultAdapter instanceof MssqlAdapter) { log.error("testLargePk(): " + "MSSQL does not support inserting defined PK values"); return; } BigintTypePeer.doDelete(new Criteria()); long longId = 8771507845873286l; BigintType bigintType = new BigintType(); bigintType.setId(longId); bigintType.save(); List<BigintType> bigintTypeList = BigintTypePeer.doSelect(new Criteria()); BigintType readBigintType = bigintTypeList.get(0); assertEquals(bigintType.getId(), readBigintType.getId()); assertEquals(longId, readBigintType.getId()); } /** * tests whether large bigint values are inserted and read correctly * @throws Exception if the test fails */ public void testLargeValue() throws Exception { BigintTypePeer.doDelete(new Criteria()); long longValue = 8771507845873286l; BigintType bigintType = new BigintType(); bigintType.setBigintValue(longValue); bigintType.save(); List<BigintType> bigintTypeList = BigintTypePeer.doSelect(new Criteria()); BigintType readBigintType = bigintTypeList.get(0); assertEquals(bigintType.getId(), readBigintType.getId()); assertEquals(longValue, readBigintType.getBigintValue()); } /** * Tests the CountHelper class * @throws Exception if the test fails */ public void testCountHelper() throws Exception { cleanBookstore(); Author author = new Author(); author.setName("Name"); author.save(); author = new Author(); author.setName("Name2"); author.save(); author = new Author(); author.setName("Name"); author.save(); Criteria criteria = new Criteria(); int count = new CountHelper().count(criteria, null, AuthorPeer.AUTHOR_ID); if (count != 3) { fail("counted " + count + " datasets, should be 3 "); } criteria = new Criteria(); criteria.setDistinct(); count = new CountHelper().count(criteria, null, AuthorPeer.NAME); if (count != 2) { fail("counted " + count + " distinct datasets, should be 2 "); } criteria = new Criteria(); criteria.where(AuthorPeer.NAME, "Name2"); count = new CountHelper().count(criteria); if (count != 1) { fail("counted " + count + " datasets with name Name2," + " should be 1 "); } } /** * Tests whether we can handle multiple primary keys some of which are * also foreign keys * @throws Exception if the test fails */ public void testMultiplePrimaryForeignKey() throws Exception { ForeignKeySchemaData.clearTablesInDatabase(); OIntegerPk oIntegerPk = new OIntegerPk(); oIntegerPk.save(); CompPkContainsFk compPkContainsFk = new CompPkContainsFk(); compPkContainsFk.setId1(oIntegerPk.getId()); compPkContainsFk.setId2("test"); compPkContainsFk.save(); List<CompPkContainsFk> selectedList = CompPkContainsFkPeer.doSelect(new Criteria()); assertEquals(1, selectedList.size()); CompPkContainsFk selected = selectedList.get(0); assertEquals(oIntegerPk.getId(), selected.getId1()); assertEquals("test", selected.getId2()); } /** * Tests inserting single quotes in Strings. * This may not crash now, but in a later task like datasql, * so the data has to be inserted in a table which does not get cleaned * during the runtime test. * @throws Exception if inserting the test data fails */ public void testSingleQuotes() throws Exception { cleanBookstore(); Author author = new Author(); author.setName("has Single ' Quote"); author.save(); } /** * Test whether equals() is working correctly * @throws Exception */ public void testEquals() throws Exception { Author author = new Author(); author.setAuthorId(1000); Book book = new Book(); book.setBookId(1000); Book bookNotEqual = new Book(); bookNotEqual.setBookId(2000); Book bookEqual = new Book(); bookEqual.setBookId(1000); assertFalse("Author and Book should not be equal", author.equals(book)); assertTrue("Book compared with itself should be equal", book.equals(book)); assertTrue("Book compared with book with same id should be equal", book.equals(bookEqual)); assertFalse("Book compared with book with different id " + "should not be equal", book.equals(bookNotEqual)); } /** * Tests whether a table implementing an interface actually * returns an instance of this interface * @throws Exception if the test fails */ public void testInterface() throws Exception { Criteria criteria = new Criteria(); criteria.where(IfcTablePeer.ID, -1, Criteria.NOT_EQUAL); IfcTablePeer.doDelete(criteria); IfcTable ifc = new IfcTable(); assertTrue("IfcTable should be an instance of TestInterface", ifc instanceof TestInterface); ifc.setID(1); ifc.setName("John Doe"); ifc.save(); List<IfcTable> results = IfcTablePeer.doSelect(new Criteria()); for (IfcTable ifcTable : results) { assertTrue("IfcTablePeer.doSelect should return" + " instances of TestInterface", ifcTable instanceof TestInterface); } LocalIfcTable localIfc = new LocalIfcTable(); assertTrue("LocalIfcTable should be an instance of LocalTestInterface", localIfc instanceof LocalTestInterface); List<LocalIfcTable> results2 = LocalIfcTablePeer.doSelect(new Criteria()); for (LocalIfcTable readLocalIfcTable : results2) { assertTrue("IfcTable2Peer.doSelect should return" + " instances of LocalTestInterface", readLocalIfcTable instanceof LocalTestInterface); } } public void testInheritanceWithKeys() throws Exception { // make sure that the InheritanceTest table is empty before the test Criteria criteria = new Criteria(); criteria.where(InheritanceTestPeer.INHERITANCE_TEST, (Object) null, Criteria.ISNOTNULL); InheritanceTestPeer.doDelete(criteria); criteria = new Criteria(); criteria.where(InheritanceTestPeer.INHERITANCE_TEST, (Object) null, Criteria.ISNOTNULL); assertEquals(0, new CountHelper().count(criteria)); // create & save test data InheritanceTest inheritanceTest = new InheritanceTest(); inheritanceTest.setPayload("payload1"); inheritanceTest.save(); InheritanceChildB inheritanceChildB = new InheritanceChildB(); inheritanceChildB.setPayload("payload 2"); inheritanceChildB.save(); InheritanceChildC inheritanceChildC = new InheritanceChildC(); inheritanceChildC.setPayload("payload 3"); inheritanceChildC.save(); InheritanceChildD inheritanceChildD = new InheritanceChildD(); inheritanceChildD.setPayload("payload 4"); inheritanceChildD.save(); // Check that all objects are saved into the InheritanceTest table criteria = new Criteria(); criteria.where(InheritanceTestPeer.INHERITANCE_TEST, null, Criteria.ISNOTNULL); assertEquals("InheritanceTestTable should contain 4 rows", 4, new CountHelper().count(criteria)); criteria = new Criteria(); criteria.addAscendingOrderByColumn(InheritanceTestPeer.INHERITANCE_TEST); // Check that the class of the object is retained when loading List<InheritanceTest> inheritanceObjects = InheritanceTestPeer.doSelect(criteria); assertEquals(InheritanceTest.class, inheritanceObjects.get(0).getClass()); assertEquals(InheritanceChildB.class, inheritanceObjects.get(1).getClass()); assertEquals(InheritanceChildC.class, inheritanceObjects.get(2).getClass()); assertEquals(InheritanceChildD.class, inheritanceObjects.get(3).getClass()); } public void testInheritanceWithClassname() throws Exception { // make sure that the InheritanceTest table is empty before the test Criteria criteria = new Criteria(); InheritanceClassnameTestPeer.doDelete(criteria); criteria = new Criteria(); criteria.where(InheritanceClassnameTestPeer.INHERITANCE_TEST, null, Criteria.ISNOTNULL); assertEquals(0, new CountHelper().count(criteria)); // create & save test data InheritanceClassnameTest inheritanceClassnameTest = new InheritanceClassnameTest(); inheritanceClassnameTest.setPayload("0 parent"); inheritanceClassnameTest.save(); InheritanceClassnameTestChild1 inheritanceClassnameChild1 = new InheritanceClassnameTestChild1(); inheritanceClassnameChild1.setPayload("1 child"); inheritanceClassnameChild1.save(); InheritanceClassnameTestChild2 inheritanceClassnameChild2 = new InheritanceClassnameTestChild2(); inheritanceClassnameChild2.setPayload("2 child"); inheritanceClassnameChild2.save(); // Check that all objects are saved into the InheritanceTest table criteria = new Criteria(); criteria.where(InheritanceClassnameTestPeer.INHERITANCE_TEST, null, Criteria.ISNOTNULL); assertEquals("InheritanceClassnameTest table should contain 3 rows", 3, new CountHelper().count(criteria)); criteria = new Criteria(); criteria.addAscendingOrderByColumn(InheritanceClassnameTestPeer.PAYLOAD); // Check that the class of the object is retained when loading List<InheritanceClassnameTest> inheritanceObjects = InheritanceClassnameTestPeer.doSelect(criteria); assertEquals(InheritanceClassnameTest.class, inheritanceObjects.get(0).getClass()); assertEquals("0 parent", inheritanceObjects.get(0).getPayload()); assertEquals(InheritanceClassnameTestChild1.class, inheritanceObjects.get(1).getClass()); assertEquals("1 child", inheritanceObjects.get(1).getPayload()); assertEquals(InheritanceClassnameTestChild2.class, inheritanceObjects.get(2).getClass()); assertEquals("2 child", inheritanceObjects.get(2).getPayload()); } /** * Checks whether selects with unqualified column names work. * * @throws Exception if a problem occurs. */ public void testUnqualifiedColumnNames() throws Exception { cleanBookstore(); Author author = new Author(); author.setName("Joshua Bloch"); author.save(); Criteria criteria = new Criteria(); criteria.where(AuthorPeer.AUTHOR_ID, (Object) null, Criteria.NOT_EQUAL); criteria.and(new ColumnImpl("name"), "Joshua Bloch", Criteria.EQUAL); List<Author> authors = AuthorPeer.doSelect(criteria); assertEquals(1, authors.size()); } public void testLikeClauseEscaping() throws Exception { String[] authorNames = { "abc", "bbc", "a_c", "a%c", "a\\c", "a\"c", "a'c", "a?c", "a*c" }; Map<String, String> likeResults = new HashMap<String, String>(); likeResults.put("a\\_c", "a_c"); likeResults.put("a\\_%", "a_c"); likeResults.put("%\\_c", "a_c"); likeResults.put("a\\%c", "a%c"); likeResults.put("a\\%%", "a%c"); likeResults.put("%\\%c", "a%c"); likeResults.put("a\\\\c", "a\\c"); likeResults.put("a\\\\%", "a\\c"); likeResults.put("%\\\\c", "a\\c"); likeResults.put("a\\*c", "a*c"); likeResults.put("a\\*%", "a*c"); likeResults.put("%\\*c", "a*c"); likeResults.put("a\\?c", "a?c"); likeResults.put("a\\?%", "a?c"); likeResults.put("%\\?c", "a?c"); likeResults.put("a\"c", "a\"c"); likeResults.put("a\"%", "a\"c"); likeResults.put("%\"c", "a\"c"); likeResults.put("a'c", "a'c"); likeResults.put("a'%", "a'c"); likeResults.put("%'c", "a'c"); cleanBookstore(); // Save authors for (int i = 0; i < authorNames.length; ++i) { Author author = new Author(); author.setName(authorNames[i]); author.save(); } // Check authors are in the database for (int i = 0; i < authorNames.length; ++i) { Criteria criteria = new Criteria(); criteria.where(AuthorPeer.NAME, authorNames[i]); List<Author> authorList = AuthorPeer.doSelect(criteria); assertEquals("AuthorList should contain one author" + " when querying for " + authorNames[i], 1, authorList.size()); Author author = authorList.get(0); assertEquals("Name of author should be " + authorNames[i], authorNames[i], author.getName()); } for (Map.Entry<String, String> likeResult : likeResults.entrySet()) { Criteria criteria = new Criteria(); criteria.where(AuthorPeer.NAME, likeResult.getKey(), Criteria.LIKE); List<Author> authorList; try { authorList = AuthorPeer.doSelect(criteria); } catch (Exception e) { throw new Exception("error rxecuting select using like content " + likeResult.getKey(), e); } assertEquals("AuthorList should contain one author" + " when querying for " + likeResult.getKey(), 1, authorList.size()); Author author = authorList.get(0); assertEquals("Name of author should be " + likeResult.getValue() + " when querying for " + likeResult.getKey(), likeResult.getValue(), author.getName()); } // check that case insensitivity is maintained if // a like is replaced with an equals (no wildcard present) // This might be a problem for databases which use ILIKE Criteria criteria = new Criteria(); criteria.where(AuthorPeer.NAME, "AbC", Criteria.LIKE); criteria.setIgnoreCase(true); List<Author> authorList = AuthorPeer.doSelect(criteria); assertEquals("AuthorList should contain one author", 1, authorList.size()); Author author = authorList.get(0); assertEquals("Name of author should be abc", "abc", author.getName()); // check that the escape clause (where needed) also works // with limit, offset and order by criteria = new Criteria(); Criterion criterion1 = new Criterion(AuthorPeer.NAME, "b%", Criteria.LIKE); Criterion criterion2 = new Criterion(AuthorPeer.NAME, "a\\%%", Criteria.LIKE); Criterion criterion3 = new Criterion(AuthorPeer.NAME, "cbc", Criteria.LIKE); criteria.where(criterion1.or(criterion2).or(criterion3)); criteria.addAscendingOrderByColumn(AuthorPeer.NAME); criteria.setOffset(1); criteria.setLimit(1); authorList = AuthorPeer.doSelect(criteria); assertEquals("AuthorList should contain one author", 1, authorList.size()); author = authorList.get(0); assertEquals("Name of author should be bbc", "bbc", author.getName()); } /** * Strips the schema and table name from a fully qualified colum name * This is useful for creating Query with aliases, as the constants * for the colum names in the data objects are fully qualified. * @param fullyQualifiedColumnName the fully qualified column name, not null * @return the column name stripped from the table (and schema) prefixes */ public static String getRawColumnName(String fullyQualifiedColumnName) { int dotPosition = fullyQualifiedColumnName.lastIndexOf("."); if (dotPosition == -1) { return fullyQualifiedColumnName; } String result = fullyQualifiedColumnName.substring(dotPosition + 1, fullyQualifiedColumnName.length()); return result; } static class DoNothingMapper implements RecordMapper<Object> { public Object processRow(ResultSet resultSet, int rowOffset, CriteriaInterface<?> criteria) throws TorqueException { return null; } } }