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;
}
}
}