db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From b..@apache.org
Subject cvs commit: db-ojb/src/test/org/apache/ojb/broker QueryTest.java
Date Tue, 20 Jul 2004 20:44:59 GMT
brj         2004/07/20 13:44:59

  Modified:    src/java/org/apache/ojb/broker/accesslayer/sql
                        SqlQueryStatement.java SqlSelectStatement.java
               src/test/org/apache/ojb/broker QueryTest.java
  Log:
  - ojb no longer adds orderBy and groupBy columns to the SELECT-clause
  - fixed problem with ALIAS-prefix
  
  Revision  Changes    Path
  1.76      +38 -48    db-ojb/src/java/org/apache/ojb/broker/accesslayer/sql/SqlQueryStatement.java
  
  Index: SqlQueryStatement.java
  ===================================================================
  RCS file: /home/cvs/db-ojb/src/java/org/apache/ojb/broker/accesslayer/sql/SqlQueryStatement.java,v
  retrieving revision 1.75
  retrieving revision 1.76
  diff -u -r1.75 -r1.76
  --- SqlQueryStatement.java	22 May 2004 09:51:25 -0000	1.75
  +++ SqlQueryStatement.java	20 Jul 2004 20:44:59 -0000	1.76
  @@ -47,6 +47,8 @@
    */
   public abstract class SqlQueryStatement implements SqlStatement, JoinSyntaxTypes
   {
  +    private static final String ALIAS_SEPARATOR = ".";
  +    
       private SqlQueryStatement m_parentStatement;
       /** the logger */
       private Logger m_logger;
  @@ -423,7 +425,6 @@
        */
   	protected boolean appendColName(String attr, boolean useOuterJoins, UserAlias aUserAlias,
StringBuffer buf)
       {
  -		// PAW: is this correct for pathClasses ?
   		AttributeInfo attrInfo = getAttributeInfo(attr, useOuterJoins, aUserAlias, getQuery().getPathClasses());
           TableAlias tableAlias = attrInfo.tableAlias;
   
  @@ -448,7 +449,6 @@
   	protected boolean appendColName(String attr, String attrAlias, boolean useOuterJoins,
UserAlias aUserAlias,
               StringBuffer buf)
       {
  -		// PAW: is this correct for pathClasses ?
   		AttributeInfo attrInfo = getAttributeInfo(attr, useOuterJoins, aUserAlias, getQuery().getPathClasses());
           TableAlias tableAlias = attrInfo.tableAlias;
           PathInfo pi = attrInfo.pathInfo;
  @@ -1187,7 +1187,7 @@
   		}
   		else
   		{
  -			return createTableAlias(aCld, hints, aUserAlias + aPath);
  +			return createTableAlias(aCld, hints, aUserAlias + ALIAS_SEPARATOR + aPath);
   		}
       }
   
  @@ -1230,7 +1230,7 @@
   		}
   		else
   		{
  -			return createTableAlias(aTable, aUserAlias + aPath);
  +			return createTableAlias(aTable, aUserAlias + ALIAS_SEPARATOR + aPath);
   		}
       }
   
  @@ -1291,7 +1291,7 @@
           }
           else
           {
  -			return getTableAliasForPath(aUserAlias + aPath);
  +			return getTableAliasForPath(aUserAlias + ALIAS_SEPARATOR + aPath);
           }
       }
   
  @@ -1322,27 +1322,43 @@
   		return (ClassDescriptor) classDescriptors.get(0);
   	}
   
  -    /**
  -     * Appends to the statement the ORDER BY clause for the Query
  -     */
  -    protected void appendOrderByClause(List orderByFields, int[] orderByColumnNumbers,
StringBuffer buf)
  +	/**
  +     * Appends the ORDER BY clause for the Query.
  +     * <br>
  +     * If the orderByField is found in the list of selected fields it's index is added.

  +     * Otherwise it's name is added.
  +	 * @param orderByFields 
  +	 * @param selectedFields the names of the fields in the SELECT clause
  +	 * @param buf
  +	 */
  +    protected void appendOrderByClause(List orderByFields, List selectedFields, StringBuffer
buf)
       {
  -        FieldHelper cf;
   
  -        if (orderByColumnNumbers == null)
  +        if (orderByFields == null || orderByFields.size() == 0)
           {
               return;
           }
  -
  +        
           buf.append(" ORDER BY ");
  -        for (int i = 0; i < orderByColumnNumbers.length; i++)
  +        for (int i = 0; i < orderByFields.size(); i++)
           {
  -            cf = (FieldHelper) orderByFields.get(i);
  +            FieldHelper cf = (FieldHelper) orderByFields.get(i);
  +            int colNumber = selectedFields.indexOf(cf.name);
  +            
               if (i > 0)
               {
                   buf.append(",");
               }
  -            buf.append(orderByColumnNumbers[i]);
  +            
  +            if (colNumber >= 0)
  +            {
  +                buf.append(colNumber + 1);                
  +            }
  +            else
  +            {            
  +                appendColName(cf.name, false, null, buf);
  +            }
  +            
               if (!cf.isAscending)
               {
                   buf.append(" DESC");
  @@ -1351,12 +1367,12 @@
       }
   
       /**
  -     * Appends to the statement the GROUP BY clause for the Query
  +     * Appends the GROUP BY clause for the Query
  +	 * @param groupByFields 
  +	 * @param buf
        */
       protected void appendGroupByClause(List groupByFields, StringBuffer buf)
       {
  -        FieldHelper cf;
  -
           if (groupByFields == null || groupByFields.size() == 0)
           {
               return;
  @@ -1365,11 +1381,13 @@
           buf.append(" GROUP BY ");
           for (int i = 0; i < groupByFields.size(); i++)
           {
  -            cf = (FieldHelper) groupByFields.get(i);
  +            FieldHelper cf = (FieldHelper) groupByFields.get(i);
  + 
               if (i > 0)
               {
                   buf.append(",");
               }
  +
               appendColName(cf.name, false, null, buf);
           }
       }
  @@ -1490,34 +1508,6 @@
           join.appendJoinEqualities(buf);
   
           appendTableWithJoins(join.right, where, buf);
  -    }
  -
  -    /**
  -     * Appends to the statement columns if they are not found among the existingColumns.
  -     * Columns added here use a column-alias "ojb_col_x", x being the number of existing
columns
  -     * @param columns the list of columns represented by Criteria.Field to ensure
  -     * @param existingColumns the list of column names (String) that are already appended
  -     * @return the array of column numbers (base 1)
  -     */
  -    protected int[] ensureColumns(List columns, List existingColumns, StringBuffer buf)
  -    {
  -        FieldHelper cf;
  -        int[] columnNumbers = new int[columns.size()];
  -
  -        for (int i = 0; i < columnNumbers.length; i++)
  -        {
  -            cf = (FieldHelper) columns.get(i);
  -            columnNumbers[i] = existingColumns.indexOf(cf.name);
  -            if (columnNumbers[i] == -1)
  -            {
  -                columnNumbers[i] = existingColumns.size();
  -                existingColumns.add(cf.name);
  -                buf.append(",");
  -                appendColName(cf.name, "ojb_col_" + columnNumbers[i], false, null, buf);
  -            }
  -            columnNumbers[i]++; // columns numbers have base 1
  -        }
  -        return columnNumbers;
       }
   
       /**
  
  
  
  1.23      +10 -21    db-ojb/src/java/org/apache/ojb/broker/accesslayer/sql/SqlSelectStatement.java
  
  Index: SqlSelectStatement.java
  ===================================================================
  RCS file: /home/cvs/db-ojb/src/java/org/apache/ojb/broker/accesslayer/sql/SqlSelectStatement.java,v
  retrieving revision 1.22
  retrieving revision 1.23
  diff -u -r1.22 -r1.23
  --- SqlSelectStatement.java	24 Apr 2004 08:25:38 -0000	1.22
  +++ SqlSelectStatement.java	20 Jul 2004 20:44:59 -0000	1.23
  @@ -136,10 +136,10 @@
           Query query = getQuery();
           boolean first = true;
           List orderByFields = null;
  -        int[] orderByColumnNumbers = null;
           String[] attributes = null;
           String[] joinAttributes = null;
           Iterator it = getJoinTreeToCriteria().entrySet().iterator();
  +        List columnList = new ArrayList();
   
           if (query instanceof ReportQuery)
           {
  @@ -152,7 +152,6 @@
               Map.Entry entry = (Map.Entry) it.next();
               Criteria whereCrit = (Criteria) entry.getValue();
               Criteria havingCrit = query.getHavingCriteria();
  -            List columnList;
               StringBuffer where = new StringBuffer();
               StringBuffer having = new StringBuffer();
               List groupByFields = null;
  @@ -193,11 +192,12 @@
                    * will allow us to load objects with unique mapping fields that are mapped
                    * to the same table.
                    */
  -                columnList = appendListOfColumnsForSelect(getSearchClassDescriptor(), stmt);
  +                
  +                columnList.addAll(appendListOfColumnsForSelect(getSearchClassDescriptor(),
stmt));
               }
               else
               {
  -                columnList = appendListOfColumns(attributes, stmt);
  +                columnList.addAll(appendListOfColumns(attributes, stmt));
               }
   
               // BRJ:
  @@ -209,24 +209,10 @@
               {
                   for (int i = 0; i < joinAttributes.length; i++)
                   {
  -                	// PAW: correct for pathClasses?
  -					//getAttributeInfo(joinAttributes[i], false, null);
   					getAttributeInfo(joinAttributes[i], false, null, getQuery().getPathClasses());
                   }
               }
  -
  -            groupByFields = query.getGroupBy();
  -            if (groupByFields != null && !groupByFields.isEmpty())
  -            {
  -                ensureColumns(groupByFields, columnList, stmt);
  -            }
  -
  -            orderByFields = query.getOrderBy();
  -            if (orderByFields != null && !orderByFields.isEmpty())
  -            {
  -                orderByColumnNumbers = ensureColumns(orderByFields, columnList, stmt);
  -            }
  -
  +          
               /**
                * treeder: going to map superclass tables here, 
                * not sure about the columns, just using all columns for now
  @@ -247,13 +233,16 @@
               {
                   appendSuperClassJoin(cld, cldSuper, stmt, where);
               }
  +
  +            groupByFields = query.getGroupBy();
               
               appendWhereClause(where, whereCrit, stmt);
               appendGroupByClause(groupByFields, stmt);
               appendHavingClause(having, havingCrit, stmt);
           }
   
  -        appendOrderByClause(orderByFields, orderByColumnNumbers, stmt);
  +        orderByFields = query.getOrderBy();
  +        appendOrderByClause(orderByFields, columnList, stmt);
   
           return stmt.toString();
       }
  
  
  
  1.63      +62 -0     db-ojb/src/test/org/apache/ojb/broker/QueryTest.java
  
  Index: QueryTest.java
  ===================================================================
  RCS file: /home/cvs/db-ojb/src/test/org/apache/ojb/broker/QueryTest.java,v
  retrieving revision 1.62
  retrieving revision 1.63
  diff -u -r1.62 -r1.63
  --- QueryTest.java	12 Jul 2004 16:26:26 -0000	1.62
  +++ QueryTest.java	20 Jul 2004 20:44:59 -0000	1.63
  @@ -508,6 +508,36 @@
       }
   
       /**
  +	 * Simple ReportQuery returning rows with max(id) of Person grouped by not selected columns
  +	 */
  +    public void testReportQueryGroupByNonSelectColumns()
  +    {
  +
  +        Criteria crit = new Criteria();
  +        Collection results = new Vector();
  +        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(row.length, 1);
  +
  +            results.add(row);
  +        }
  +
  +        assertEquals(results.size(), 10);
  +
  +        // 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
  @@ -1296,6 +1326,38 @@
           assertEquals("check size", list.size(), 1);
       }
   
  +    /**
  +	 * 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.stock"});
  +
  +        // Due to AliasPrefixes ArticleName is taken from A2, Stock from A1, 
  +        // SELECT A0.Kategorie_Nr,A0.KategorieName,A2.Artikelname,A1.Lagerbestand FROM
Kategorien ...
  +        
  +        Iterator iter = broker.getReportQueryIteratorByQuery(q);
  +        while (iter.hasNext())
  +        {
  +            list.add(iter.next());
  +        }
  +
  +        // ProductGroup 4 with it's Articles
  +        assertEquals("check size", list.size(), 1);
  +    }
  +    
       /**
   	 * Run a query range test that includes one record less than the total
   	 * number of records that exist.
  
  
  

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message