QueryTest.java :  » Database-ORM » db-ojb » org » apache » ojb » broker » Java Open Source

Java Open Source » Database ORM » db ojb 
db ojb » org » apache » ojb » broker » QueryTest.java
package org.apache.ojb.broker;

/* Copyright 2002-2005 The Apache Software Foundation
 *
 * Licensed 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.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.Vector;
import java.math.BigDecimal;

import org.apache.commons.lang.time.StopWatch;
import org.apache.ojb.broker.accesslayer.sql.SqlGenerator;
import org.apache.ojb.broker.metadata.ClassDescriptor;
import org.apache.ojb.broker.metadata.CollectionDescriptor;
import org.apache.ojb.broker.platforms.PlatformHsqldbImpl;
import org.apache.ojb.broker.query.Criteria;
import org.apache.ojb.broker.query.LikeCriteria;
import org.apache.ojb.broker.query.Query;
import org.apache.ojb.broker.query.QueryByCriteria;
import org.apache.ojb.broker.query.QueryFactory;
import org.apache.ojb.broker.query.ReportQueryByCriteria;
import org.apache.ojb.junit.PBTestCase;

/**
 * Testing the query API.
 * @version $Id: QueryTest.java,v 1.61.2.20 2005/12/21 22:31:23 tomdz Exp $
 */
public class QueryTest extends PBTestCase
{

    // Product groups above high water mark are modified by other testcases,
    // setting this low makes it easier to re-run this test
    private static final Integer PGROUP_ID_HI_WATERMARK =
            new Integer(4000);
    
    /**
     * BrokerTests constructor comment.
     */
    public QueryTest(String name)
    {
        super(name);
    }

    public static void main(String[] args)
    {
        String[] arr = {QueryTest.class.getName()};
        junit.textui.TestRunner.main(arr);
    }

    public void testQueryZero()
    {
        String name = "testQueryZero_" + System.currentTimeMillis();
        ObjectRepository.Group group = new ObjectRepository.Group();
        group.setId(new Integer(0));
        group.setName(name);

        // prepare test
        broker.beginTransaction();
        Query q = QueryFactory.newQuery(group);
        broker.deleteByQuery(q);
        broker.commitTransaction();
        broker.clearCache();

        QueryByCriteria query = new QueryByCriteria(group);
        ObjectRepository.Group newGroup = (ObjectRepository.Group) broker.getObjectByQuery(query);
        assertNull(newGroup);

        broker.beginTransaction();
        broker.store(group);
        broker.commitTransaction();

        ObjectRepository.Group groupTemp = new ObjectRepository.Group();
        groupTemp.setId(new Integer(0));
        Query queryNew = QueryFactory.newQuery(groupTemp);
        newGroup = (ObjectRepository.Group) broker.getObjectByQuery(queryNew);
        assertNotNull(newGroup);
        assertEquals(new Integer(0), newGroup.getId());

        newGroup = (ObjectRepository.Group) broker.getObjectByQuery(queryNew);
        assertNotNull(newGroup);
        assertEquals(new Integer(0), newGroup.getId());

        broker.clearCache();
        newGroup = (ObjectRepository.Group) broker.getObjectByQuery(queryNew);
        assertNotNull(newGroup);
        assertEquals(new Integer(0), newGroup.getId());
    }

    /**
     * Criteria containing other Criteria only
     */
    public void testCriteria()
    {
        String name = "testCriteria_" + System.currentTimeMillis();
        Person p1 = new Person();
        p1.setFirstname("tomm");
        p1.setLastname(name);

        Person p2 = new Person();
        p2.setFirstname("tom");
        p2.setLastname(name);

        Person p3 = new Person();
        p3.setFirstname("xtom");
        p3.setLastname(name);

        broker.beginTransaction();
        broker.store(p1);
        broker.store(p2);
        broker.store(p3);
        broker.commitTransaction();

        Criteria crit1 = new Criteria();
        Criteria crit2 = new Criteria();
        Criteria crit3 = new Criteria();

        crit2.addEqualTo("lastname", name);
        crit2.setNegative(false);

        crit3.addEqualTo("firstname", "tom");
        crit3.setNegative(true);

        crit1.addAndCriteria(crit2);
        crit1.addAndCriteria(crit3);

        Query q = QueryFactory.newQuery(Person.class, crit1);
        Collection results = broker.getCollectionByQuery(q);

        // all persons except tom
        assertEquals(2, results.size());
    }

    /**
     * test EqualTo Criteria
     */
    public void testEqualCriteria()
    {
        Criteria crit = new Criteria();
        crit.addEqualTo("firstname", "tom");
        Query q = QueryFactory.newQuery(Person.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() > 0);
    }

    /**
     * test sql Criteria
     */
    public void testSqlCriteria()
    {
        Criteria crit;
        Query q;
        Collection results;
        
        // sql only
        crit = new Criteria();
        crit.addSql("upper(firstname) = 'TOM' and id = 1");
        q = QueryFactory.newQuery(Person.class, crit);

        results = broker.getCollectionByQuery(q);
        int size1 = results.size();
        
        // sql plus attribute 
        crit = new Criteria();
        crit.addSql("upper(firstname) = 'TOM'");
        crit.addEqualTo("id", new Integer(1));
        q = QueryFactory.newQuery(Person.class, crit);

        results = broker.getCollectionByQuery(q);
        int size2 = results.size();
        
        // attribute plus sql 
        crit = new Criteria();
        crit.addEqualTo("upper(firstname)", "TOM");
        crit.addSql("id = 1");
        q = QueryFactory.newQuery(Person.class, crit);

        results = broker.getCollectionByQuery(q);
        int size3 = results.size();

        assertTrue(size2 == size1);
        assertTrue(size3 == size2);
    }


    /**
     * test OrderBy and Count
     */
    public void testOrderByCount()
    {
        Criteria crit = new Criteria();
        crit.addEqualTo("firstname", "tom");
        QueryByCriteria q = QueryFactory.newQuery(Person.class, crit);
        q.addOrderByAscending("firstname");

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() > 0);
        
        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);
    }

    /**
     * test OrderBy joined column
     */
    public void testOrderByJoined()
    {
        String name = "testOrderByJoined" + System.currentTimeMillis();
        Person p = new Person();
        p.setFirstname("tom");
        p.setLastname(name);

        Project p1 = new Project();
        p1.setTitle(name);
        ArrayList list_1 = new ArrayList();
        list_1.add(p);
        p1.setPersons(list_1);

        Project p2 = new Project();
        p2.setTitle(name);
        ArrayList list_2 = new ArrayList();
        list_2.add(p);
        p2.setPersons(list_2);

        ArrayList list_projects = new ArrayList();
        list_projects.add(p1);
        list_projects.add(p2);
        p.setProjects(list_projects);

        Project p3 = new Project();
        p3.setTitle(name);
        ArrayList list_3 = new ArrayList();
        // empty list
        p3.setPersons(list_3);

        broker.beginTransaction();
        broker.store(p1);
        //broker.store(p2);
        //broker.store(p3);
        broker.commitTransaction();

        Criteria crit = new Criteria();
        crit.addLike("title", name);
        QueryByCriteria q = QueryFactory.newQuery(Project.class, crit);
        q.addOrderByAscending("title");
        q.addOrderByAscending("persons.lastname");
        q.addOrderByAscending("persons.firstname");

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertEquals(2, results.size());

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);
    }

    /**
     * test Subquery get all product groups without articles
     * <p/>
     * test may fail if db does not support sub queries
     */
    public void testSubQuery2()
    {
        Collection results = null;
        String stamp = "" + System.currentTimeMillis();
        int loops = 10;
        // create ProductGroups without article
        broker.beginTransaction();
        for(int i = 0; i < loops; i++)
        {
            ProductGroup pg = new ProductGroup();
            pg.setGroupName("test group " + stamp);
            pg.setDescription("build by QueryTest#testSubQuery2");
            broker.store(pg);
        }
        broker.commitTransaction();

        ReportQueryByCriteria subQuery;
        Criteria subCrit = new Criteria();
        Criteria crit = new Criteria();

        subQuery = QueryFactory.newReportQuery(Article.class, subCrit);
        subQuery.setAttributes(new String[]{"productGroupId"});
        subQuery.setDistinct(true);

        crit.addEqualTo("groupName", "test group " + stamp);
        crit.addNotIn("groupId", subQuery);
        Query q = QueryFactory.newQuery(ProductGroup.class, crit);

        results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertEquals("Result of the query with sub-query does not match", loops, results.size());
    }

    /**
     * test Subquery get all articles with price > avg(price) PROBLEM:
     * avg(price) is NOT extent aware !!
     * <p/>
     * test may fail if db does not support sub queries
     */
    public void testSubQuery1()
    {

        ReportQueryByCriteria subQuery;
        Criteria subCrit = new Criteria();
        Criteria crit = new Criteria();

        subCrit.addLike("articleName", "A%");
        subQuery = QueryFactory.newReportQuery(Article.class, subCrit);
        subQuery.setAttributes(new String[]{"avg(price)"});

        crit.addGreaterOrEqualThan("price", subQuery);
        Query q = QueryFactory.newQuery(Article.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() > 0);

    }

    /**
     * test Subquery get all product groups with more than 10 articles, uses
     * attribute as value ! see testSubQuery4 for a better way
     * <p/>
     * test may fail if db does not support sub queries
     */
    public void testSubQuery3()
    {

        ReportQueryByCriteria subQuery;
        Criteria subCrit = new Criteria();
        Criteria crit = new Criteria();

        subCrit.addEqualToField("productGroupId", Criteria.PARENT_QUERY_PREFIX + "groupId");
        subQuery = QueryFactory.newReportQuery(Article.class, subCrit);
        subQuery.setAttributes(new String[]{"count(productGroupId)"});

        crit.addLessThan("10", subQuery); // MORE than 10 articles, uses
        // attribute as value !
        crit.addLessThan("groupId", PGROUP_ID_HI_WATERMARK);
        Query q = QueryFactory.newQuery(ProductGroup.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertEquals(4, results.size());
    }

    /**
     * test Subquery get all product groups with more than 10 articles
     * <p/>
     * test may fail if db does not support sub queries
     */
    public void testSubQuery4()
    {

        ReportQueryByCriteria subQuery;
        Criteria subCrit = new Criteria();
        Criteria crit = new Criteria();

        subCrit.addEqualToField("productGroupId", Criteria.PARENT_QUERY_PREFIX + "groupId");
        subQuery = QueryFactory.newReportQuery(Article.class, subCrit);
        subQuery.setAttributes(new String[]{"count(productGroupId)"});

        // mkalen: if using String("10") instead of Integer below,
        // PostgreSQL will return 7 (sic!) groups
        crit.addGreaterThan(subQuery, new Integer(10)); // MORE than 10 articles
        crit.addLessThan("groupId", PGROUP_ID_HI_WATERMARK);
        Query q = QueryFactory.newQuery(ProductGroup.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertEquals(4, results.size());
    }

    /**
     * test Like Criteria
     */
    public void testLikeCriteria()
    {

        Criteria crit = new Criteria();
        crit.addLike("firstname", "%o%");
        Query q = QueryFactory.newQuery(Person.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() > 0);

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);
    }

    /**
     * test escaped Like Criteria
     */
    public void testLikeEscapedCriteria1()
    {
        Criteria crit = new Criteria();
        crit.addLike("firstname", "h%\\%");
        Query q = QueryFactory.newQuery(Person.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertEquals(1, results.size());

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);
    }

    /**
     * test escaped Like Criteria with escape character
     */
    public void testLikeEscapedCriteria2()
    {
        LikeCriteria.setEscapeCharacter('|');

        Criteria crit = new Criteria();
        crit.addLike("firstname", "h%|%");
        Query q = QueryFactory.newQuery(Person.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertEquals(1, results.size());

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);
    }

    /**
     * test escaped Like Criteria
     */
    public void testLikeEscapedCriteria3()
    {
        String name = "testLikeEscapedCriteria3()_" + System.currentTimeMillis();
        Person p = new Person();
        p.setFirstname("123%45");
        p.setLastname(name);
        broker.beginTransaction();
        broker.store(p);
        broker.commitTransaction();

        LikeCriteria.setEscapeCharacter('/');
        Criteria crit = new Criteria();
        crit.addEqualTo("lastname", name);
        crit.addLike("firstname", "%/%45");
        Query q = QueryFactory.newQuery(Person.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertEquals(1, results.size());
    }

    /**
     * test Null Criteria
     */
    public void testNullCriteria()
    {
        String name = "testNullCriteria_" + System.currentTimeMillis();
        Person p = new Person();
        p.setLastname(name);
        broker.beginTransaction();
        broker.store(p);
        broker.commitTransaction();

        Criteria crit = new Criteria();
        crit.addIsNull("firstname");
        Criteria crit2 = new Criteria();
        crit2.addLike("lastname", name);
        crit.addAndCriteria(crit2);
        
        Query q = QueryFactory.newQuery(Person.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertEquals(1, results.size());
    }

    /**
     * Add an empty criteria
     */
    public void testEmptyORed() throws Exception
    {
        Collection result;
        Criteria crit1 = new Criteria();
        crit1.addEqualTo("articleName", "Hamlet");
        crit1.addEqualTo("productGroup.description", "Strange Books...");

        Criteria crit2 = new Criteria();

        crit1.addOrCriteria(crit2);
        QueryByCriteria q = QueryFactory.newQuery(Article.class, crit1);

        result = broker.getCollectionByQuery(q);
        assertNotNull(result);

        int count = broker.getCount(q);
        assertEquals(count, result.size());
    }

    /**
     * Add an empty criteria
     */
    public void testEmptyANDed() throws Exception
    {
        Collection result;
        Criteria crit1 = new Criteria();
        crit1.addEqualTo("articleName", "Hamlet");
        crit1.addEqualTo("productGroup.description", "Strange Books...");

        Criteria crit2 = new Criteria();

        crit1.addAndCriteria(crit2);
        QueryByCriteria q = QueryFactory.newQuery(Article.class, crit1);

        result = broker.getCollectionByQuery(q);
        assertNotNull(result);

        int count = broker.getCount(q);
        assertEquals(count, result.size());
    }

    /**
     * test Between Criteria
     */
    public void testBetweenCriteria()
    {

        Criteria crit = new Criteria();
        crit.addBetween("id", new Integer(1), new Integer(5));
        Query q = QueryFactory.newQuery(Person.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() == 5);

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);
    }

    /**
     * test In Criteria
     */
    public void testInCriteria()
    {

        Criteria crit = new Criteria();
        Collection ids = new Vector();
        ids.add(new Integer(1));
        ids.add(new Integer(3));
        ids.add(new Integer(5));

        crit.addIn("id", ids);
        Query q = QueryFactory.newQuery(Person.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() == 3);

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);
    }

    /**
     * Single Path Expression
     */
    public void testPathExpressions()
    {

        Criteria crit = new Criteria();
        crit.addEqualTo("productGroup.groupName", "Liquors");
        Query q = QueryFactory.newQuery(Article.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() > 0);

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);
    }

    /**
     * Multi Path Expression over decomposed m:n
     */
    public void testPathExpressionsMtoNDecomposed()
    {

        Criteria crit = new Criteria();
        crit.addEqualTo("roles.project.title", "HSQLDB");
        // use decomposed m:n
        Query q = QueryFactory.newQuery(Person.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);

    }

    /**
     * Multi Path Expression over nondecomposed m:n
     */
    public void testPathExpressionsMtoN()
    {

        Criteria crit = new Criteria();
        crit.addEqualTo("projects.title", "HSQLDB"); // direct m:n
        Query q = QueryFactory.newQuery(Person.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);

    }

    /**
     * Multi Path Expression over nondecomposed m:n new test case for the 'not
     * unique alias' problem with m:n relationship
     */
    public void testPathExpressionsMtoN2()
    {
        Criteria crit = new Criteria();
        crit.addEqualTo("projects.roles.roleName", "developer");
        crit.addLike("projects.persons.lastname", "%b%");
        Query q = QueryFactory.newQuery(Person.class, crit);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() > 0);
    }

    /**
     * Distinct Query
     */
    public void testDistinct()
    {

        Criteria crit = new Criteria();
        crit.addLike("allArticlesInGroup.articleName", "C%");
        QueryByCriteria q = QueryFactory.newQuery(ProductGroup.class, crit, true);
        q.addOrderByAscending("groupId");

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertEquals(5, results.size());

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);
    }

    /**
     * Distinct Query
     */
    public void testDistinctMultiPk()
    {

        Criteria crit = new Criteria();
        crit.addEqualTo("project_id", new Integer(1));
        QueryByCriteria q = QueryFactory.newQuery(Role.class, crit, true);

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertEquals(3, results.size());

        // compare with count
        int count = broker.getCount(q);

        assertEquals(results.size(), count); // FAILS
    }

    /**
     * Simple ReportQuery returning rows with 3 columns of Person
     */
    public void testReportQuery()
    {
        String name = "testReportQuery_" + System.currentTimeMillis();
        Person p1 = new Person();
        p1.setFirstname("Robert");
        p1.setLastname(name);
        Person p2 = new Person();
        p2.setFirstname("Tom");
        p2.setLastname(name);
        Person p3 = new Person();
        p3.setFirstname("Roger");
        p3.setLastname(name);
        broker.beginTransaction();
        broker.store(p1);
        broker.store(p2);
        broker.store(p3);
        broker.commitTransaction();


        Criteria crit = new Criteria();
        Collection results = new Vector();
        crit.addLike("firstname", "%o%");
        Criteria crit2 = new Criteria();
        crit2.addLike("lastname", name);
        crit.addAndCriteria(crit2);
        ReportQueryByCriteria q = QueryFactory.newReportQuery(Person.class, crit);
        q.setAttributes(new String[]{"id", "firstname", "count(*)"});
        q.addGroupBy(new String[]{"id", "firstname"});

        Iterator iter = broker.getReportQueryIteratorByQuery(q);

        assertNotNull(iter);
        while(iter.hasNext())
        {
            Object[] row = (Object[]) iter.next();
            assertEquals(3, row.length);

            results.add(row);
        }

        assertEquals(3, results.size());

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);
    }

    /**
     * Simple ReportQuery returning rows with max(id) of Person grouped by not selected columns
     */
    public void testReportQueryGroupByNonSelectColumns()
    {
        String name = "testReportQueryGroupByNonSelectColumns_" + System.currentTimeMillis();
        Person p1 = new Person();
        p1.setFirstname("Robert");
        p1.setLastname(name);
        Person p2 = new Person();
        p2.setFirstname("Tom");
        p2.setLastname(name);
        Person p3 = new Person();
        p3.setFirstname("Roger");
        p3.setLastname(name);
        broker.beginTransaction();
        broker.store(p1);
        broker.store(p2);
        broker.store(p3);
        broker.commitTransaction();

        Criteria crit = new Criteria();
        Collection results = new Vector();
        crit.addLike("firstname", "%o%");
        Criteria crit2 = new Criteria();
        crit2.addLike("lastname", name);
        crit.addAndCriteria(crit2);
        ReportQueryByCriteria q = QueryFactory.newReportQuery(Person.class, crit);
        q.setAttributes(new String[]{"max(id)"});
        q.addGroupBy(new String[]{"lastname", "firstname"});

        Iterator iter = broker.getReportQueryIteratorByQuery(q);

        assertNotNull(iter);
        while(iter.hasNext())
        {
            Object[] row = (Object[]) iter.next();
            assertEquals(1, row.length);

            results.add(row);
        }

        assertEquals(3, results.size());

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);
    }

    /**
     * Simple ReportQuery returning rows with 3 columns of Person
     * Type of column data defined by sqltypes
     *
     * @see java.sql.Types
     */
    public void testReportQueryWithJdbcTypes()
    {
        String name = "testReportQuery_" + System.currentTimeMillis();
        Person p1 = new Person();
        p1.setFirstname("Robert");
        p1.setLastname(name);
        Person p2 = new Person();
        p2.setFirstname("Tom");
        p2.setLastname(name);
        Person p3 = new Person();
        p3.setFirstname("Roger");
        p3.setLastname(name);
        broker.beginTransaction();
        broker.store(p1);
        broker.store(p2);
        broker.store(p3);
        broker.commitTransaction();

        int types[] = new int[]{Types.DECIMAL, Types.VARCHAR, Types.BIGINT};
        Criteria crit = new Criteria();
        Collection results = new Vector();
        crit.addLike("firstname", "%o%");
        Criteria crit2 = new Criteria();
        crit2.addLike("lastname", name);
        crit.addAndCriteria(crit2);

        ReportQueryByCriteria q = QueryFactory.newReportQuery(Person.class, crit);
        q.setAttributes(new String[]{"id", "firstname", "count(*)"});
        q.addGroupBy(new String[]{"id", "firstname"});
        q.setJdbcTypes(types);

        Iterator iter = broker.getReportQueryIteratorByQuery(q);

        assertNotNull(iter);
        while(iter.hasNext())
        {
            Object[] row = (Object[]) iter.next();
            assertEquals(3, row.length);

//            assertEquals(row[0].getClass(), BigDecimal.class);
//            assertEquals(row[1].getClass(), String.class);
//            assertEquals(row[2].getClass(), Long.class);

            results.add(row);
        }

        assertEquals(3, results.size());

    }

    /**
     * Simple ReportQuery returning rows with 3 columns of Person
     * needs SQL paging
     */
    public void testReportQueryWithStartAndEnd()
    {
        Criteria crit = new Criteria();
        Collection results = new Vector();
        crit.addLike("firstname", "%o%");
        ReportQueryByCriteria q = QueryFactory.newReportQuery(Person.class, crit);
        q.setAttributes(new String[]{"id", "firstname", "count(*)"});
        q.addGroupBy(new String[]{"id", "firstname"});

        q.setStartAtIndex(3);
        q.setEndAtIndex(5);
        Iterator iter = broker.getReportQueryIteratorByQuery(q);
        assertNotNull(iter);
        while(iter.hasNext())
        {
            results.add(iter.next());
        }
        assertTrue(results.size() == 3);

        results.clear();
        q.setStartAtIndex(1);
        q.setEndAtIndex(5);
        iter = broker.getReportQueryIteratorByQuery(q);
        assertNotNull(iter);
        while(iter.hasNext())
        {
            results.add(iter.next());
        }
        assertTrue(results.size() == 5);
    }

    /**
     * Simple ReportQuery returning rows with 2 columns of Person
     */
    public void testReportQueryExtent()
    {

        Criteria crit = new Criteria();
        Collection results = new Vector();
        crit.addLike("articleName", "%o%");
        ReportQueryByCriteria q = QueryFactory.newReportQuery(Article.class, crit);
        q.setAttributes(new String[]{"articleId", "articleName", "price"});

        Iterator iter = broker.getReportQueryIteratorByQuery(q);

        assertNotNull(iter);
        while(iter.hasNext())
        {
            results.add(iter.next());
        }
        assertTrue(results.size() > 0);

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);
    }

    /**
     * Concrete Class in SubQuery
     */
    public void testSubQueryAgainstConcreteClass()
    {
        String name = "testSubQueryAgainstConcreteClass_" + System.currentTimeMillis();
        Article article = new BookArticle();
        article.setArticleName(name);
        ProductGroup group = new ProductGroup();
        group.setGroupName(name);
        article.setProductGroup(group);

        broker.beginTransaction();
        broker.store(group);
        broker.store(article);
        broker.commitTransaction();

        Criteria critMain = new Criteria();
        Criteria critSub = new Criteria();

        critSub.addEqualTo("articleName", name);
        ReportQueryByCriteria querySub = QueryFactory.newReportQuery(BookArticle.class, critSub);
        querySub.setAttributes(new String[]{"productGroupId"});

        ReportQueryByCriteria queryMain = QueryFactory.newReportQuery(AbstractProductGroup.class, critMain);
        queryMain.setAttributes(new String[]{"groupId", "groupName"});
        critMain.addIn("groupId", querySub);

        Iterator iter = broker.getReportQueryIteratorByQuery(queryMain);
        int result = 0;
        assertNotNull(iter);
        while(iter.hasNext())
        {
            iter.next();
            ++result;
        }
        assertEquals(1, result);

        // compare with count
        int count = broker.getCount(queryMain);
        assertEquals(result, count);
    }

    /**
     * Class with extents in SubQuery.
     * SubQueries are NOT extent aware ! so we have to use two queries.
     */
    public void testSubQueryAgainstExtents()
    {
        String name = "testSubQueryAgainstExtents_" + System.currentTimeMillis();
        Article article = new BookArticle();
        article.setArticleName(name);
        ProductGroup group = new ProductGroup();
        group.setGroupName(name);
        article.setProductGroup(group);

        broker.beginTransaction();
        broker.store(group);
        broker.store(article);
        broker.commitTransaction();

        Criteria critMain = new Criteria();
        Criteria critSub = new Criteria();

        critSub.addEqualTo("articleName", name);
        ReportQueryByCriteria querySub = QueryFactory.newReportQuery(Article.class, critSub);
        querySub.setAttributes(new String[]{"productGroupId"});
        Iterator subIter = broker.getReportQueryIteratorByQuery(querySub);
        Collection subIds = new ArrayList();
        while(subIter.hasNext())
        {
            Object[] id = (Object[]) subIter.next();
            subIds.add(id[0]);
        }

        ReportQueryByCriteria queryMain = QueryFactory.newReportQuery(AbstractProductGroup.class, critMain);
        queryMain.setAttributes(new String[]{"groupId", "groupName"});
        critMain.addIn("groupId", subIds);

        Iterator iter = broker.getReportQueryIteratorByQuery(queryMain);
        int result = 0;
        assertNotNull(iter);
        while(iter.hasNext())
        {
            iter.next();
            ++result;
        }
        assertEquals(1, result);
    
        // compare with count
        int count = broker.getCount(queryMain);
        assertEquals(result, count);
    }

    /**
     * ReportQuery with pathExpression in columns
     */
    public void testReportQueryPathExpression()
    {
        Criteria crit = new Criteria();
        Collection results = new Vector();
        crit.addLike("articleName", "C%");
        ReportQueryByCriteria q = QueryFactory.newReportQuery(Article.class, crit);
        q.setAttributes(new String[]{"productGroup.groupName", "articleId", "articleName", "price"});

        Iterator iter = broker.getReportQueryIteratorByQuery(q);

        assertNotNull(iter);
        while(iter.hasNext())
        {
            results.add(iter.next());
        }
        assertEquals(9, results.size());

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);

    }

    /**
     * ReportQuery returning rows with some "Liquor" data ordered by price
     */
    public void testReportQueryOrderBy()
    {

        Criteria crit = new Criteria();
        Collection results = new Vector();
        crit.addEqualTo("productGroup.groupName", "Liquors");
        ReportQueryByCriteria q = QueryFactory.newReportQuery(Article.class, crit);
        q.setAttributes(new String[]{"articleId", "articleName", "price"});
        q.addOrderByAscending("price");

        Iterator iter = broker.getReportQueryIteratorByQuery(q);
        assertNotNull(iter);
        while(iter.hasNext())
        {
            results.add(iter.next());
        }
        assertTrue(results.size() > 0);

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);

    }

    /**
     * ReportQuery returning rows with some "Liquor" data ordered by productGroup.groupId
     */
    public void testReportQueryOrderByNonSelectColumn()
    {

        Criteria crit = new Criteria();
        Collection results = new Vector();
        crit.addEqualTo("productGroup.groupName", "Liquors");
        ReportQueryByCriteria q = QueryFactory.newReportQuery(Article.class, crit);
        q.setAttributes(new String[]{"articleId", "articleName", "price"});
        q.addOrderByAscending("productGroup.groupId");

        Iterator iter = broker.getReportQueryIteratorByQuery(q);
        assertNotNull(iter);
        while(iter.hasNext())
        {
            results.add(iter.next());
        }
        assertTrue(results.size() > 0);

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);

    }

    /**
     * ReportQuery returning rows with summed stock and price per article group
     * The selected columns point to a class having extents.<br>
     * The query produces a wrong sql, selecting only rows of the top-class
     */
    public void testReportQueryGroupByExtents2()
    {
        Criteria crit = new Criteria();
        Collection results = new Vector();
        ReportQueryByCriteria q = QueryFactory.newReportQuery(ProductGroup.class, crit);
        q.setAttributes(new String[]{"groupName", "sum(allArticlesInGroup.stock)", "sum(allArticlesInGroup.price)"});
        q.addGroupBy("groupName");

        Iterator iter = broker.getReportQueryIteratorByQuery(q);
        
//        SELECT A0.KategorieName,sum(A1.Lagerbestand),sum(A1.Einzelpreis)
//        FROM Kategorien A0
//        LEFT OUTER JOIN artikel A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
//        LEFT OUTER JOIN books A1E2 ON A0.Kategorie_Nr=A1E2.Kategorie_Nr
//        LEFT OUTER JOIN cds A1E1 ON A0.Kategorie_Nr=A1E1.Kategorie_Nr
//        GROUP BY A0.KategorieName
        
        assertNotNull(iter);
        while(iter.hasNext())
        {
            results.add(iter.next());
        }
        assertTrue(results.size() > 0);

        // TODO: resolve
        if (ojbSkipKnownIssueProblem("broker.getCount() vs .getReportQueryIteratorByQuery().size()"))
        {
            return;
        }

        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);   // FAILS !
    }

    /**
     * ReportQuery returning rows with summed stock and price per article group
     * The selected class has the extents.<br>
     * The query returns summed values for each row, so there may be multiple rows
     * for one productgroup because one query for each extent is executed.
     */
    public void testReportQueryGroupByExtents1()
    {
        Criteria crit = new Criteria();
        Collection results = new Vector();
        ReportQueryByCriteria q = QueryFactory.newReportQuery(Article.class, crit);
        q.setAttributes(new String[]{"productGroup.groupName", "sum(stock)", "sum(price)"});
        q.addGroupBy("productGroup.groupName");

        Iterator iter = broker.getReportQueryIteratorByQuery(q);
        
//        SELECT  A1.KategorieName,sum(A0.Lagerbestand),sum(A0.Einzelpreis) 
//        FROM artikel A0 
//        INNER JOIN Kategorien A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
//        GROUP BY A1.KategorieName
//
//        SELECT  A1.KategorieName,sum(A0.Lagerbestand),sum(A0.Einzelpreis) 
//        FROM cds A0 
//        INNER JOIN Kategorien A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
//        GROUP BY A1.KategorieName
//
//        SELECT  A1.KategorieName,sum(A0.Lagerbestand),sum(A0.Einzelpreis) 
//        FROM books A0 
//        INNER JOIN Kategorien A1 ON A0.Kategorie_Nr=A1.Kategorie_Nr
//        GROUP BY A1.KategorieName

        assertNotNull(iter);
        while(iter.hasNext())
        {
            results.add(iter.next());
        }
        assertTrue(results.size() > 0);

        // TODO: resolve
        if (ojbSkipKnownIssueProblem("broker.getCount() vs .getReportQueryIteratorByQuery().size()"))
        {
            return;
        }
        
        // compare with count
        int count = broker.getCount(q);
        assertEquals(results.size(), count);   // FAILS !
    }

    /**
     * Read a CD and then read the ProductGroup for the CD
     */
    public void testInversePathExpression()
    {
        QueryByCriteria query;
        Criteria crit;
        CdArticle cd;
        ProductGroupProxy pg, cdPg;

        crit = new Criteria();
        crit.addEqualTo("articleId", new Integer(200));
        query = new QueryByCriteria(CdArticle.class, crit);
        cd = (CdArticle) broker.getObjectByQuery(query);
        cdPg = (ProductGroupProxy) cd.getProductGroup();

        crit = new Criteria();
        crit.addEqualTo("allArticlesInGroup.articleId", cd.getArticleId());
        query = new QueryByCriteria(ProductGroup.class, crit);
        query.setPathClass("allArticlesInGroup", CdArticle.class);
        pg = (ProductGroupProxy) broker.getObjectByQuery(query);

        // this test can only succeed in singlevm mode:
//        if (!BrokerHelper.isRunningInServerMode())
//        {
//            assertNotNull(pg);
//            assertNotNull(cdPg);
//            assertEquals("ProductGroups should be identical", pg.getRealSubject().toString(), cdPg.getRealSubject().toString());
//        }

        assertNotNull(pg);
        assertNotNull(cdPg);
        assertEquals("ProductGroups should be identical", pg.getRealSubject().toString(), cdPg.getRealSubject().toString());
    }

    /**
     * prefetch Articles for ProductGroupsWithArray, Does not yet work with
     * Arrays
     */
    public void testPrefetchedArraySingleKey()
    {
        ClassDescriptor cldArticle = broker.getClassDescriptor(Article.class);
        Class articleProxy = cldArticle.getProxyClass();

        //
        // use ProductGroup and Articles with disabled Proxy
        //
        broker.clearCache();
        cldArticle.setProxyClass(null);

        Criteria crit = new Criteria();
        crit.addLessOrEqualThan("groupId", new Integer(5));
        QueryByCriteria q = QueryFactory.newQuery(ProductGroupWithArray.class, crit);
        q.addOrderByDescending("groupId");
        q.addPrefetchedRelationship("allArticlesInGroup");

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() > 0);
        ProductGroupWithArray pg = (ProductGroupWithArray) results.toArray()[0];
        int articleSize = pg.getAllArticles().length;
        assertTrue(articleSize != 0);
        String articleString = Arrays.asList(pg.getAllArticles()).toString();

        //
        // use ProductGroupWithArray and Articles with original Proxy settings
        //
        broker.clearCache();
        cldArticle.setProxyClass(articleProxy);


        crit = new Criteria();
        crit.addEqualTo("groupId", new Integer(5));
        q = QueryFactory.newQuery(ProductGroupWithArray.class, crit);
        results = broker.getCollectionByQuery(q);
        ProductGroupWithArray pg2 = (ProductGroupWithArray) results.toArray()[0];
        InterfaceArticle[] articles = pg2.getAllArticles();
        assertNotNull("Array of articles should not be null!", articles);
        assertTrue("Array should contain more than 0 entries!", articles.length != 0);

        // force materialization
        for(int i = 0; i < articles.length; i++)
        {
            articles[i].getArticleName();
        }
        int articleSize2 = articles.length;
        String articleString2 = Arrays.asList(articles).toString();

        //
        // compare prefetched and 'normal' data
        //
        assertEquals("Check size", articleSize, articleSize2);
        assertEquals("Check content", articleString, articleString2);

    }

    /**
     * orderby for prefetch Articles of ProductGroups
     */
    public void testPrefetchedCollectionOrderBy()
    {
        ClassDescriptor cldProductGroup = broker.getClassDescriptor(ProductGroup.class);
        ClassDescriptor cldArticle = broker.getClassDescriptor(Article.class);
        Class productGroupProxy = cldProductGroup.getProxyClass();
        Class articleProxy = cldArticle.getProxyClass();
        CollectionDescriptor cds = cldProductGroup.getCollectionDescriptorByName("allArticlesInGroup");

        //
        // use ProductGroup and Articles with disabled Proxy
        //
        cldProductGroup.setProxyClass(null);
        cldProductGroup.setProxyClassName(null);
        cldArticle.setProxyClass(null);
        cldArticle.setProxyClassName(null);
        broker.getDescriptorRepository().setClassDescriptor(cldProductGroup);
        broker.getDescriptorRepository().setClassDescriptor(cldArticle);
        
        //
        // orderby articleId, ASC
        //
        broker.clearCache();
        cds.getOrderBy().clear();
        cds.addOrderBy("articleId", true);
         
        Criteria crit = new Criteria();
        crit.addLessOrEqualThan("groupId", new Integer(5));
        QueryByCriteria q = QueryFactory.newQuery(ProductGroup.class, crit);
        q.addOrderByDescending("groupId");
        q.addPrefetchedRelationship("allArticlesInGroup");

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() == 5);
        InterfaceProductGroup pg = (InterfaceProductGroup) results.toArray()[1];
        assertNotNull(pg.getAllArticles());
        Object articles[] = pg.getAllArticles().toArray();
        int articleSize = articles.length;
        assertTrue(articleSize == 10);
        Article a1 = (Article) articles[0];
        Article a2 = (Article) articles[9];
        assertTrue(a1.getArticleId().intValue() < a2.getArticleId().intValue());

        //
        // orderby articleId, DESC
        //
        broker.clearCache();
        cds.getOrderBy().clear();
        cds.addOrderBy("articleId", false);

        results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() == 5);
        pg = (InterfaceProductGroup) results.toArray()[1];
        assertNotNull(pg.getAllArticles());
        articles = pg.getAllArticles().toArray();
        articleSize = articles.length;
        assertTrue(articleSize == 10);
        Article b1 = (Article) articles[0];
        Article b2 = (Article) articles[9];
        assertTrue(b1.getArticleId().intValue() > b2.getArticleId().intValue());

        assertEquals(a1.getArticleId(), b2.getArticleId());
        assertEquals(a2.getArticleId(), b1.getArticleId());

        //
        // use ProductGroup and Articles with original Proxy settings
        //
        cldProductGroup.setProxyClass(productGroupProxy);
        cldProductGroup.setProxyClassName(productGroupProxy.getName());
        cldArticle.setProxyClass(articleProxy);
        cldArticle.setProxyClassName(articleProxy.getName());
        broker.getDescriptorRepository().setClassDescriptor(cldProductGroup);
        broker.getDescriptorRepository().setClassDescriptor(cldArticle);
   }

    /**
     * prefetch Articles for ProductGroups
     */
    public void testPrefetchedCollectionSingleKey()
    {
        ClassDescriptor cldProductGroup = broker.getClassDescriptor(ProductGroup.class);
        ClassDescriptor cldArticle = broker.getClassDescriptor(Article.class);
        Class productGroupProxy = cldProductGroup.getProxyClass();
        Class articleProxy = cldArticle.getProxyClass();

        //
        // use ProductGroup and Articles with disabled Proxy
        //
        broker.clearCache();
        cldProductGroup.setProxyClass(null);
        cldProductGroup.setProxyClassName(null);
        cldArticle.setProxyClass(null);
        cldArticle.setProxyClassName(null);
        broker.getDescriptorRepository().setClassDescriptor(cldProductGroup);
        broker.getDescriptorRepository().setClassDescriptor(cldArticle);

        Criteria crit = new Criteria();
        crit.addLessOrEqualThan("groupId", new Integer(5));
        QueryByCriteria q = QueryFactory.newQuery(ProductGroup.class, crit);
        q.addOrderByDescending("groupId");
        q.addPrefetchedRelationship("allArticlesInGroup");

        Collection results = broker.getCollectionByQuery(q);
        assertNotNull(results);
        assertTrue(results.size() > 0);
        InterfaceProductGroup pg = (InterfaceProductGroup) results.toArray()[0];
        assertNotNull(pg.getAllArticles());
        int articleSize = pg.getAllArticles().size();
        String articleString = pg.getAllArticles().toString();

        //
        // use ProductGroup and Articles with original Proxy settings
        //
        broker.clearCache();
        cldProductGroup.setProxyClass(productGroupProxy);
        cldProductGroup.setProxyClassName(productGroupProxy.getName());
        cldArticle.setProxyClass(articleProxy);
        cldArticle.setProxyClassName(articleProxy.getName());
        broker.getDescriptorRepository().setClassDescriptor(cldProductGroup);
        broker.getDescriptorRepository().setClassDescriptor(cldArticle);

        crit = new Criteria();
        crit.addEqualTo("groupId", new Integer(5));
        q = QueryFactory.newQuery(ProductGroup.class, crit);
        results = broker.getCollectionByQuery(q);
        InterfaceProductGroup pg2 = (InterfaceProductGroup) results.toArray()[0];
        // force materialization
        for(Iterator it = pg2.getAllArticles().iterator(); it.hasNext();)
        {
            ((InterfaceArticle) it.next()).getArticleName();
        }
        int articleSize2 = pg2.getAllArticles().size();
        String articleString2 = pg2.getAllArticles().toString();

        //
        // compare prefetched and 'normal' data
        //
        assertEquals("Check size", articleSize, articleSize2);
        assertEquals("Check content", articleString, articleString2);
    }

    /**
     * Test nested joins using pathExpressions
     */
    public void testNestedJoins()
    {
        ArrayList list = new java.util.ArrayList();

        Criteria crit = new Criteria();
        ReportQueryByCriteria q = QueryFactory.newReportQuery(Person.class, crit, true);

        q.setAttributes(new String[]{"roles.roleName", "roles.project.title", "firstname", });

        Iterator iter = broker.getReportQueryIteratorByQuery(q);
        while(iter.hasNext())
        {
            list.add(iter.next());
        }

        assertTrue(list.size() > 0);
    }

    /**
     * Test multiple non nested joins using pathExpressions
     */
    public void testMultipleJoins()
    {
        ArrayList list = new java.util.ArrayList();

        Criteria crit = new Criteria();
        ReportQueryByCriteria q = QueryFactory.newReportQuery(Role.class, crit, true);

        q.setAttributes(new String[]{"roleName", "project.title", "person.firstname", });

        Iterator iter = broker.getReportQueryIteratorByQuery(q);
        while(iter.hasNext())
        {
            list.add(iter.next());
        }

        assertTrue(list.size() > 0);
    }

    /**
     * Test nested joins using pathExpressions *** Fails under hsqldb because
     * of join using multiple keys ***
     */
    public void tesXNestedJoins2()
    {
        ArrayList list = new ArrayList();
        Criteria crit = new Criteria();
        ReportQueryByCriteria q = QueryFactory.newReportQuery(Task.class, crit);

        q.setAttributes(new String[]{"role.roleName", "role.project.title", "role.person.firstname", });

        Iterator iter = broker.getReportQueryIteratorByQuery(q);
        while(iter.hasNext())
        {
            list.add(iter.next());
        }
        assertTrue(list.size() > 0);
    }

    /**
     * prefetch ProductGroups for Articles
     */
    public void testPrefetchedReferencesSingleKey()
    {
        ClassDescriptor cldProductGroup = broker.getClassDescriptor(ProductGroup.class);
        ClassDescriptor cldArticle = broker.getClassDescriptor(Article.class);
        Class productGroupProxy = cldProductGroup.getProxyClass();
        Class articleProxy = cldArticle.getProxyClass();

        //
        // use ProductGroup and Articles with disabled Proxy
        //
        broker.clearCache();
        cldProductGroup.setProxyClass(null);
        cldProductGroup.setProxyClassName(null);
        cldArticle.setProxyClass(null);
        cldArticle.setProxyClassName(null);
        broker.getDescriptorRepository().setClassDescriptor(cldProductGroup);
        broker.getDescriptorRepository().setClassDescriptor(cldArticle);

        Criteria crit = new Criteria();
        crit.addNotNull("productGroupId");
        crit.addLessOrEqualThan("productGroupId", new Integer(5));
        QueryByCriteria q = QueryFactory.newQuery(Article.class, crit);
        q.addOrderByDescending("productGroupId");
        q.addPrefetchedRelationship("productGroup");

        Collection results = broker.getCollectionByQuery(q);
        Set pgs = new HashSet();
        Iterator iter = results.iterator();
        while(iter.hasNext())
        {
            InterfaceArticle a = (InterfaceArticle) iter.next();
            pgs.add(a.getProductGroup().getName());
        }

        assertTrue(pgs.size() > 0);
        String pgsString = pgs.toString();

        //
        // use ProductGroup and Articles with original Proxy settings
        //
        broker.clearCache();
        cldProductGroup.setProxyClass(productGroupProxy);
        cldProductGroup.setProxyClassName(productGroupProxy.getName());
        cldArticle.setProxyClass(articleProxy);
        cldArticle.setProxyClassName(articleProxy.getName());
        broker.getDescriptorRepository().setClassDescriptor(cldProductGroup);
        broker.getDescriptorRepository().setClassDescriptor(cldArticle);

        crit = new Criteria();
        crit.addNotNull("productGroupId");
        crit.addLessOrEqualThan("productGroupId", new Integer(5));
        q = QueryFactory.newQuery(Article.class, crit);
        q.addOrderByDescending("productGroupId");

        results = broker.getCollectionByQuery(q);
        Set pgs2 = new HashSet();
        iter = results.iterator();
        while(iter.hasNext())
        {
            InterfaceArticle a = (InterfaceArticle) iter.next();
            pgs2.add(a.getProductGroup().getName());
        }

        assertTrue(pgs2.size() > 0);
        String pgsString2 = pgs2.toString();

        //
        // compare prefetched and 'normal' data
        //
        assertEquals("Check size", pgs.size(), pgs2.size());
        assertEquals("Check content", pgsString, pgsString2);

    }

    /**
     * test PathExpression pointing to abstract class (InterfaceArticle)
     */
    public void testReportPathExpressionAbstractExtent()
    {
        // TODO: make path expressions extent aware
        if(ojbSkipKnownIssueProblem("Make path expressions extent aware")) return;

        ArrayList list = new ArrayList();
        Criteria crit = new Criteria();
        crit.addEqualTo("groupId", new Integer(5));

        ReportQueryByCriteria q = QueryFactory.newReportQuery(ProductGroupWithAbstractArticles.class, crit, true);
        q.setAttributes(new String[]{"groupId", "groupName", "allArticlesInGroup.articleId", "allArticlesInGroup.articleName"});

        Iterator iter = broker.getReportQueryIteratorByQuery(q);
        while(iter.hasNext())
        {
            list.add(iter.next());
        }

        // 7 Articles, 2 Books, 3 Cds
        //  BRJ: path expression is not yet extent aware
        assertEquals("check size", 12, list.size());
    }

    /**
     * ReportQuery returning rows with some "Liquor" data ordered by productGroup.groupId
     */
    public void testQueryOrderByNonSelectColumn()
    {

        Criteria crit = new Criteria();
        Collection results = new Vector();
        crit.addEqualTo("productGroup.groupName", "Liquors");
        QueryByCriteria q = QueryFactory.newQuery(Article.class, crit);
        q.addOrderByAscending("productGroup.groupId");

        Iterator iter = broker.getIteratorByQuery(q);
        assertNotNull(iter);
        while(iter.hasNext())
        {
            results.add(iter.next());
        }
        assertTrue(results.size() > 0);

    }

    /**
     * test PathExpression pointing to abstract class (InterfaceArticle)
     */
    public void testPathExpressionForAbstractExtent()
    {
        ArrayList list = new java.util.ArrayList();

        Criteria crit = new Criteria();
        crit.addLike("allArticlesInGroup.articleName", "Chef%");
        Criteria crit1 = new Criteria();
        crit1.addEqualTo("allArticlesInGroup.articleName", "Faust");
        crit.addOrCriteria(crit1);

        QueryByCriteria q = QueryFactory.newQuery(ProductGroupWithAbstractArticles.class, crit, true);
        q.addOrderByAscending("groupId");

        Iterator iter = broker.getIteratorByQuery(q);
        while(iter.hasNext())
        {
            list.add(iter.next());
        }

        // Groups 2, 5
        assertEquals("check size", 2, list.size());

        assertEquals("Group 2", 2, ((InterfaceProductGroup) list.get(0)).getId().intValue());
        assertEquals("Group 5", 5, ((InterfaceProductGroup) list.get(1)).getId().intValue());
    }

    /**
     * Test pathExpression and Extents
     */
    public void testReportPathExpressionForExtents1()
    {
        ArrayList list = new java.util.ArrayList();

        Criteria crit = new Criteria();
        crit.addGreaterOrEqualThan("allArticlesInGroup.articleId", new Integer(1));
        crit.addLessOrEqualThan("allArticlesInGroup.articleId", new Integer(5));

        ReportQueryByCriteria q = QueryFactory.newReportQuery(ProductGroup.class, crit, true);
        q.setAttributes(new String[]{"groupId", "groupName", "allArticlesInGroup.articleId"});

        Iterator iter = broker.getReportQueryIteratorByQuery(q);
        while(iter.hasNext())
        {
            Object row;
            Object[] columns;

            assertNotNull("Invalid ReportQueryIterator, hasNext() is true but next() is null",
                    row = iter.next());
            assertTrue("ReportQuery result row is not Object[]",
                    row instanceof Object[]);
            columns = (Object[]) row;
            list.add(columns);

            assertTrue("ReportQuery result row does not contain all expected columns",
                    columns.length == 3);

            /*
            arminw:
            think hsql returns the wrong result or interpret the query in wrong
            way (e.g. using hashcode of values instead values itself), so skip test
            evaluation for this DB
            */
            if(!broker.serviceConnectionManager().getSupportedPlatform().getClass().equals(PlatformHsqldbImpl.class))
            {
//                System.out.println("### " + ((Object[]) obj)[0]
//                        + "  " + ((Object[]) obj)[1]
//                        + "  " + ((Object[]) obj)[2]);
                Object articleId = columns[2];
                int i = -1;
                if (articleId instanceof Integer) {
                    i = ((Integer) articleId).intValue();
                } else if (articleId instanceof BigDecimal) {
                    i = ((BigDecimal) articleId).intValue();
                } else {
                    assertTrue("TODO: Your platforms resulting class for INTEGER (" +
                                articleId.getClass().getName() +
                                ") is not yet supported in testcase.", false);
                }

                assertTrue("i=" + i, i < 6 & i > 0);
            }
        }
    }

    /**
     * Test pathExpression and Extents
     */
    public void testReportPathExpressionForExtents2()
    {
        ArrayList list = new java.util.ArrayList();

        Criteria crit = new Criteria();
        crit.addLike("groupName", "%o%");
        crit.addLike("allArticlesInGroup.articleName", "%\u00f6%"); //unicode for 

        ReportQueryByCriteria q = QueryFactory.newReportQuery(ProductGroup.class, crit, true);
        q.setAttributes(new String[]{"groupId", "groupName"});

        Iterator iter = broker.getReportQueryIteratorByQuery(q);
        while(iter.hasNext())
        {
            list.add(iter.next());
        }

        // Groups: 3 Books , 1 Dairy Product
        assertEquals("check size", 2, list.size());
    }

    /**
     * ReportQuery with Expression in column need to add table alias to the field (price)
     **/
    public void testReportQueryExpressionInStatement()
    {
        // TODO: Resolve attributes of expressions
        if(ojbSkipKnownIssueProblem("Resolve attributes of expressions")) return;

        Criteria crit = new Criteria();
        ReportQueryByCriteria q = QueryFactory.newReportQuery(Article.class, crit);
        q.setAttributes(new String[]{"articleId", "price+10"});
        ClassDescriptor cd = broker.getClassDescriptor(q.getBaseClass());
        SqlGenerator sqlg = broker.serviceSqlGenerator();
        String sql = sqlg.getPreparedSelectStatement(q, cd).getStatement();
        
        assertTrue("Bad query generated. the 'price' field has not table prefix. SQL Output: " + sql, sql
                .equalsIgnoreCase("SELECT A0.Artikel_Nr,A0.Einzelpreis+10 FROM Artikel A0"));
    }
     

    /**
     * Test pathExpression and Extents
     */
    public void testPathExpressionForExtents1()
    {
        ArrayList list = new java.util.ArrayList();

        Criteria crit = new Criteria();
        crit.addEqualTo("allArticlesInGroup.productGroupId", new Integer(5));

        Query q = QueryFactory.newQuery(ProductGroup.class, crit, true);

        Iterator iter = broker.getIteratorByQuery(q);
        while(iter.hasNext())
        {
            list.add(iter.next());
        }

        // ProductGroup 5
        assertEquals("check size", 1, list.size());
    }

    /**
     * Test pathExpression and Extents
     */
    public void testPathExpressionForExtents2()
    {
        ArrayList list = new java.util.ArrayList();

        Criteria crit = new Criteria();
        crit.addLike("upper(allArticlesInGroup.articleName)", "F%");

        Query q = QueryFactory.newQuery(ProductGroup.class, crit, true);

        Iterator iter = broker.getIteratorByQuery(q);
        while(iter.hasNext())
        {
            list.add(iter.next());
        }

        // ProductGroups 4 and 5
        assertEquals("check size", 2, list.size());

    }

    /**
     * Test pathExpression and Extents musicians is only defined in CD
     */
    public void testPathExpressionForExtents3()
    {
        ArrayList list = new java.util.ArrayList();

        Criteria crit = new Criteria();
        crit.addLike("allArticlesInGroup.musicians", "%");

        QueryByCriteria q = QueryFactory.newQuery(ProductGroup.class, crit, true);
        q.setPathClass("allArticlesInGroup", CdArticle.class);

        Iterator iter = broker.getIteratorByQuery(q);
        while(iter.hasNext())
        {
            list.add(iter.next());
        }

        // ProductGroups 5
        assertEquals("check size", 1, list.size());
    }

    /**
     * Test pathExpression and Extents Abstract Base
     */
    public void testPathExpressionForExtents4()
    {
        ArrayList list = new java.util.ArrayList();

        Criteria crit = new Criteria();
        crit.addEqualTo("allArticlesInGroup.productGroupId", new Integer(5));

        Query q = QueryFactory.newQuery(AbstractProductGroup.class, crit, true);

        Iterator iter = broker.getIteratorByQuery(q);
        while(iter.hasNext())
        {
            list.add(iter.next());
        }

        // ProductGroup 5
        assertEquals("check size", 1, list.size());
    }

    /**
     * Test pathExpression and Extents using Alias
     */
    public void testPathExpressionForExtentsAlias()
    {
        ArrayList list = new java.util.ArrayList();

        Criteria crit1 = new Criteria();
        crit1.setAlias("NAMES");
        crit1.addLike("upper(allArticlesInGroup.articleName)", "F%");

        Criteria crit2 = new Criteria();
        crit2.addGreaterOrEqualThan("allArticlesInGroup.stock", new Integer(110));

        crit1.addAndCriteria(crit2);
        Query q = QueryFactory.newQuery(ProductGroup.class, crit1, true);

        Iterator iter = broker.getIteratorByQuery(q);
        while(iter.hasNext())
        {
            list.add(iter.next());
        }

        // ProductGroup 4
        assertEquals("check size", 1, list.size());
    }

    /**
     * Test ReportQuery and Alias
     */
    public void testReportQueryAlias()
    {
        ArrayList list = new java.util.ArrayList();

        Criteria crit1 = new Criteria();
        crit1.setAlias("NAMES");
        crit1.addLike("upper(allArticlesInGroup.articleName)", "F%");

        Criteria crit2 = new Criteria();
        crit2.setAlias("STOCKS");
        crit2.addGreaterOrEqualThan("allArticlesInGroup.stock", new Integer(110));

        crit1.addAndCriteria(crit2);
        ReportQueryByCriteria q = QueryFactory.newReportQuery(ProductGroup.class, crit1);
        q.setAttributes(new String[]{"groupId", "groupName", "STOCKS.allArticlesInGroup.articleName",
                                     "NAMES.allArticlesInGroup.articleName", "NAMES.allArticlesInGroup.stock"});

        // Due to AliasPrefixes ArticleName is taken from A2 and A1, Stock from A1,
        // SELECT A0.Kategorie_Nr,A0.KategorieName,A2.Artikelname,A1.Artikelname,A1.Lagerbestand FROM

        Iterator iter = broker.getReportQueryIteratorByQuery(q);
        while(iter.hasNext())
        {
            list.add(iter.next());
        }

        // ProductGroup 4 with it's Articles
        assertEquals("check size", 1, list.size());
    }

    /**
     * Run a query range test that includes one record less than the total
     * number of records that exist.
     */
    public void testQueryRangeOneLessThanTotal()
    {
        this.runQueryRangeTest(-1);
    }

    /**
     * Run a query range test that includes all of the records that exist.
     */
    public void testQueryRangeAllRecords()
    {
        this.runQueryRangeTest(0);
    }

    /**
     * Run a query range test.
     */
    public void testQueryRangeMassTest()
    {
        String name = "testQueryRangeMassTest_" + System.currentTimeMillis();
        int objCount = 2000;

        broker.beginTransaction();
        for(int i = 0; i < objCount; i++)
        {
            Gourmet a = new Gourmet();
            a.setName(name);
            broker.store(a);
        }
        broker.commitTransaction();

        Criteria crit = new Criteria();
        crit.addEqualTo("name", name);
        QueryByCriteria q = QueryFactory.newQuery(Gourmet.class, crit);
        q.setStartAtIndex(100);
        q.setEndAtIndex(109);

        StopWatch watch = new StopWatch();
        watch.start();
        Collection c = broker.getCollectionByQuery(q);
        watch.stop();
        System.out.println("# Query 10 of " + objCount + " objects take " + watch.getTime() + " ms");
        assertNotNull(c);
        List result = new ArrayList(c);
        assertEquals(10, result.size());

        crit = new Criteria();
        crit.addEqualTo("name", name);
        q = QueryFactory.newQuery(Gourmet.class, crit);
        watch.reset();
        watch.start();
        c = broker.getCollectionByQuery(q);
        watch.stop();
        System.out.println("# Query all " + objCount + " objects take " + watch.getTime() + " ms");
        assertNotNull(c);
        result = new ArrayList(c);
        assertEquals(objCount, result.size());

        broker.beginTransaction();
        for(int i = 0; i < result.size(); i++)
        {
            broker.delete(result.get(i));
        }
        broker.commitTransaction();

        c = broker.getCollectionByQuery(q);
        assertNotNull(c);
        result = new ArrayList(c);
        assertEquals(0, result.size());
    }

    /**
     * Run a query range test that includes one record more than the total
     * number of records that exist.
     */
    public void testQueryRangeOneMoreThanTotal()
    {
        this.runQueryRangeTest(+1);
    }

    /**
     * Run a query range test.
     *
     * @param delta the amount to add to the existing record count when setting
     *              the start/end index for the query that we'll use.
     */
    private void runQueryRangeTest(int delta)
    {

        // How many records are there in the table?
        Query countQuery = QueryFactory.newQuery(ProductGroup.class, null, false);

        // Get the existing record count
        int recordCount = broker.getCollectionByQuery(countQuery).toArray().length;

        // Build a query that will get the range we're looking for.
        Query listQuery = QueryFactory.newQuery(ProductGroup.class, null, false);
        listQuery.setStartAtIndex(1);
        listQuery.setEndAtIndex(recordCount + delta);

        // Get the list.
        Object[] theObjects = broker.getCollectionByQuery(listQuery).toArray();

        // Verify the record count
        if(delta <= 0)
        {
            assertEquals("record count", (recordCount + delta), theObjects.length);
        }
        else
        {
            assertEquals("record count", recordCount, theObjects.length);
        }

        // Verify the query size, fullSize is 0
        // assertEquals("Query size", recordCount, listQuery.fullSize());
    }

    public void testQueryMN_Alias1() throws Exception
    {
        Criteria crit1 = new Criteria();
        Criteria crit2 = new Criteria();
        QueryByCriteria q;
        Collection result;

        broker.clearCache();

        // get persons working for projects OJB and SODA
        crit1.addLike("projects.title", "OJB%");
        crit1.setAlias("alias1");
        crit2.addLike("projects.title", "SODA%");
        crit2.setAlias("alias2");
        crit1.addAndCriteria(crit2);

        q = QueryFactory.newQuery(Person.class, crit1, true);
        result = broker.getCollectionByQuery(q);
        assertNotNull(result);
        assertTrue(result.size() == 2); // bob, tom
    }

    public void testQueryMN_Alias2() throws Exception
    {
        Criteria crit1 = new Criteria();
        Criteria crit2 = new Criteria();
        QueryByCriteria q;
        Collection result1;
        Collection result2;

        broker.clearCache();

        // get persons working for projects OJB (alias should have no effect)
        crit1.setAlias("myAlias");
        crit1.addLike("firstname", "%o%");
        crit1.addLike("projects.title", "OJB%");

        q = QueryFactory.newQuery(Person.class, crit1, true);
        result1 = broker.getCollectionByQuery(q);
        assertNotNull(result1);
        assertTrue(result1.size() == 2); // bob, tom

        // WITHOUT ALIAS
        // get persons working for projects OJB
        crit2.addLike("firstname", "%o%");
        crit2.addLike("projects.title", "OJB%");

        q = QueryFactory.newQuery(Person.class, crit2, true);
        result2 = broker.getCollectionByQuery(q);
        assertNotNull(result2);
        assertTrue(result2.size() == 2); // bob, tom

    }

    public void testQueryMN() throws Exception
    {
        Criteria crit1 = new Criteria();
        Criteria crit2 = new Criteria();
        QueryByCriteria q;
        Collection result;

        broker.clearCache();

        // get persons working for projects OJB _or_ SODA
        crit1.addLike("projects.title", "OJB%");
        crit2.addLike("projects.title", "SODA%");
        crit1.addOrCriteria(crit2);

        q = QueryFactory.newQuery(Person.class, crit1, true);
        result = broker.getCollectionByQuery(q);
        assertNotNull(result);
        assertTrue(result.size() == 5); // bob, tom, cindy, albert ,betrand
    }

    public void testQueryCommutative12() throws Exception
    {
        Collection result;
        Criteria crit1 = new Criteria();
        crit1.addEqualTo("articleName", "Hamlet");
        crit1.addEqualTo("productGroup.description", "Strange Books...");

        Criteria crit2 = new Criteria();
        crit2.addEqualTo("stock", new Integer(32));

        Criteria crit3 = new Criteria();
        crit3.addEqualTo("stock", new Integer(42));

        crit2.addOrCriteria(crit3);
        crit1.addAndCriteria(crit2);
        QueryByCriteria qry12 = QueryFactory.newQuery(Article.class, crit1);
        qry12.setPathOuterJoin("productGroup");

        result = broker.getCollectionByQuery(qry12);
        assertNotNull(result);

        int count = broker.getCount(qry12);
        assertEquals(count, result.size());
    }


    public void testQueryCommutative21()
    {
        Collection result;
        Criteria crit1 = new Criteria();
        crit1.addEqualTo("articleName", "Hamlet");
        crit1.addEqualTo("productGroup.description", "Strange Books...");

        Criteria crit2 = new Criteria();
        crit2.addEqualTo("stock", new Integer(32));

        Criteria crit3 = new Criteria();
        crit3.addEqualTo("stock", new Integer(42));

        crit2.addOrCriteria(crit3);
        crit2.addAndCriteria(crit1);
        QueryByCriteria qry21 = QueryFactory.newQuery(Article.class, crit2);
        qry21.setPathOuterJoin("productGroup");

        result = broker.getCollectionByQuery(qry21);
        assertNotNull(result);

        int count = broker.getCount(qry21);
        assertEquals(count, result.size());
    }

    public void testOuterJoin()
    {
        Article a = new Article();
        a.articleName = "Good stuff";
        a.productGroup = null;
        broker.beginTransaction();
        broker.store(a);
        broker.commitTransaction();
        
        Criteria crit = new Criteria();
        crit.addLike("articleName", "G%");
        crit.addIsNull("productGroup.description");
        
        QueryByCriteria qry1 = QueryFactory.newQuery(Article.class, crit);
        Collection result1 = broker.getCollectionByQuery(qry1);

        QueryByCriteria qry2 = QueryFactory.newQuery(Article.class, crit);
        qry2.setPathOuterJoin("productGroup");
        Collection result2 = broker.getCollectionByQuery(qry2);
        
        assertEquals(0, result1.size());
        assertEquals(1, result2.size());
        
        broker.beginTransaction();
        broker.delete(a);
        broker.commitTransaction();
    }
    
    public void testExtentByInterface()
    {
        String name = "testExtentByInterface_" + System.currentTimeMillis();
        Zoo zoo = new Zoo();
        zoo.setName(name);
        Mammal m1 = new Mammal();
        m1.setName(name);
        Mammal m2 = new Mammal();
        m2.setName(name);
        Reptile r1 = new Reptile();
        r1.setName(name);
        broker.beginTransaction();
        broker.store(zoo);
        m1.setZooId(zoo.getZooId());
        m2.setZooId(zoo.getZooId());
        r1.setZooId(zoo.getZooId());
        broker.store(m1);
        broker.store(m2);
        broker.store(r1);
        broker.commitTransaction();

        Criteria crit = new Criteria();
        crit.addEqualTo("name", name);

        QueryByCriteria queryAnimals = QueryFactory.newQuery(InterfaceAnimal.class, crit);
        QueryByCriteria queryReptile = QueryFactory.newQuery(Reptile.class, crit);
        QueryByCriteria queryMammal = QueryFactory.newQuery(Mammal.class, crit);

        Collection resultA = broker.getCollectionByQuery(queryAnimals);
        Collection resultR = broker.getCollectionByQuery(queryReptile);
        Collection resultM = broker.getCollectionByQuery(queryMammal);

        assertEquals(3, resultA.size());
        assertEquals(1, resultR.size());
        assertEquals(2, resultM.size());

        for(Iterator iterator = resultA.iterator(); iterator.hasNext();)
        {
            InterfaceAnimal a = (InterfaceAnimal) iterator.next();
            assertEquals(name, a.getName());

        }
        for(Iterator iterator = resultR.iterator(); iterator.hasNext();)
        {
            Reptile a = (Reptile) iterator.next();
            assertEquals(name, a.getName());
        }
        for(Iterator iterator = resultM.iterator(); iterator.hasNext();)
        {
            Mammal a = (Mammal) iterator.next();
            assertEquals(name, a.getName());
        }

        Reptile reptile = (Reptile) broker.getObjectByQuery(queryReptile);
        Mammal mammal = (Mammal) broker.getObjectByQuery(queryMammal);
        assertNotNull(reptile);
        assertNotNull(mammal);
    }

}
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.