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 Fri, 12 Mar 2004 19:29:05 GMT
brj         2004/03/12 11:29:05

  Modified:    src/java/org/apache/ojb/broker/accesslayer/sql
                        SqlSelectStatement.java
               src/java/org/apache/ojb/broker/util BrokerHelper.java
               src/java/org/apache/ojb/broker/query
                        ReportQueryByCriteria.java ReportQuery.java
               src/test/org/apache/ojb/broker QueryTest.java
  Log:
  improved support for count for report-queries
  
  Revision  Changes    Path
  1.19      +48 -32    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.18
  retrieving revision 1.19
  diff -u -r1.18 -r1.19
  --- SqlSelectStatement.java	11 Mar 2004 18:16:08 -0000	1.18
  +++ SqlSelectStatement.java	12 Mar 2004 19:29:05 -0000	1.19
  @@ -45,10 +45,10 @@
        * @param query
        * @param logger
        */
  -	public SqlSelectStatement(Platform pf, ClassDescriptor cld, Query query, Logger logger)
  -	{
  -		super(pf, cld, query, logger);
  -	}
  +    public SqlSelectStatement(Platform pf, ClassDescriptor cld, Query query, Logger logger)
  +    {
  +        super(pf, cld, query, logger);
  +    }
   
       /**
        * Constructor for SqlSelectStatement.
  @@ -88,7 +88,7 @@
           if (fieldDescriptors != null)
           {
               FieldDescriptor field = null;
  -            for (int j = 0; j <fieldDescriptorLength; j++)
  +            for (int j = 0; j < fieldDescriptorLength; j++)
               {
                   field = fieldDescriptors[j];
                   if (i > 0)
  @@ -127,8 +127,7 @@
           return columnList;
       }
   
  -
  -	/**
  +    /**
        * Answer the SELECT-Sql for the Statement
        */
       public String getStatement()
  @@ -139,13 +138,15 @@
           List orderByFields = null;
           int[] orderByColumnNumbers = null;
           String[] columns = null;
  +        String[] joinColumns = null;
           Iterator it = getJoinTreeToCriteria().entrySet().iterator();
   
           if (query instanceof ReportQuery)
           {
  -            columns = ((ReportQuery)query).getColumns();
  +            columns = ((ReportQuery) query).getColumns();
  +            joinColumns = ((ReportQuery) query).getJoinColumns();
           }
  -        
  +
           while (it.hasNext())
           {
               Map.Entry entry = (Map.Entry) it.next();
  @@ -199,6 +200,19 @@
                   columnList = appendListOfColumns(columns, stmt);
               }
   
  +            // BRJ:
  +            // joinColumns are only used to force the building of a join;
  +            // they are not appended to the select-clause !
  +            // these columns are used in COUNT-ReportQueries and
  +            // are taken from the query the COUNT is based on 
  +            if (joinColumns != null && joinColumns.length > 0)
  +            {
  +                for (int i = 0; i < joinColumns.length; i++)
  +                {
  +                    getAttributeInfo(joinColumns[i], false, null);
  +                }
  +            }
  +
               groupByFields = query.getGroupBy();
               if (groupByFields != null && !groupByFields.isEmpty())
               {
  @@ -212,48 +226,50 @@
               }
   
               /**
  -             * treeder
  -             * going to map superclass tables here, not sure about the columns, just using
all columns for now
  -             *
  +             * treeder: going to map superclass tables here, 
  +             * not sure about the columns, just using all columns for now
                */
               ClassDescriptor cld = super.getBaseClassDescriptor();
               ClassDescriptor cldSuper = null;
  -            if(cld.getSuperClass() != null)
  +            if (cld.getSuperClass() != null)
               {
                   // then we have a super class so join tables
                   cldSuper = cld.getRepository().getDescriptorFor(cld.getSuperClass());
                   appendSuperClassColumns(cld, cldSuper, stmt);
  -
               }
   
               stmt.append(" FROM ");
   
               appendTableWithJoins(getRoot(), where, stmt);
  -            if(cld.getSuperClass() != null){
  +            if (cld.getSuperClass() != null)
  +            {
                   appendSuperClassJoin(cld, cldSuper, stmt, where);
               }
  -            appendWhereClause(where,whereCrit, stmt);
  +            
  +            appendWhereClause(where, whereCrit, stmt);
               appendGroupByClause(groupByFields, stmt);
  -            appendHavingClause(having,havingCrit, stmt);
  +            appendHavingClause(having, havingCrit, stmt);
           }
  -        
  +
           appendOrderByClause(orderByFields, orderByColumnNumbers, stmt);
   
           return stmt.toString();
       }
   
  -
  -    private void appendSuperClassJoin(ClassDescriptor cld, ClassDescriptor cldSuper, StringBuffer
stmt, StringBuffer where)
  +    private void appendSuperClassJoin(ClassDescriptor cld, ClassDescriptor cldSuper, StringBuffer
stmt,
  +            StringBuffer where)
       {
           stmt.append(",").append(cldSuper.getFullTableName());
  -        if(where != null && where.length() > 0){
  +        if (where != null && where.length() > 0)
  +        {
               where.append(" AND ");
           }
           // get reference field in super class
           // TODO: do not use the superclassfield anymore, just assume that the id is the
same in both tables - @see PBroker.storeToDb
           int superFieldRef = cld.getSuperClassFieldRef();
           FieldDescriptor refField = cld.getFieldDescriptorByIndex(superFieldRef);
  -        where.append(cldSuper.getFullTableName()).append(".").append(cldSuper.getAutoIncrementFields()[0].getColumnName());
  +        where.append(cldSuper.getFullTableName()).append(".").append(
  +                cldSuper.getAutoIncrementFields()[0].getColumnName());
           where.append(" = ");
           where.append(cld.getFullTableName()).append(".").append(refField.getColumnName());
   
  @@ -265,17 +281,17 @@
           for (int i = 0; i < fields.length; i++)
           {
               FieldDescriptor field = fields[i];
  -             if (i > 0)
  -                {
  -                    buf.append(",");
  -                }
  -                buf.append(cldSuper.getFullTableName());
  -                buf.append(".");
  -                buf.append(field.getColumnName());
  -                //columnList.add(field.getAttributeName());
  +            if (i > 0)
  +            {
  +                buf.append(",");
  +            }
  +            buf.append(cldSuper.getFullTableName());
  +            buf.append(".");
  +            buf.append(field.getColumnName());
  +            //columnList.add(field.getAttributeName());
   
           }
   
       }
   
  -}
  +}
  \ No newline at end of file
  
  
  
  1.39      +90 -37    db-ojb/src/java/org/apache/ojb/broker/util/BrokerHelper.java
  
  Index: BrokerHelper.java
  ===================================================================
  RCS file: /home/cvs/db-ojb/src/java/org/apache/ojb/broker/util/BrokerHelper.java,v
  retrieving revision 1.38
  retrieving revision 1.39
  diff -u -r1.38 -r1.39
  --- BrokerHelper.java	11 Mar 2004 18:16:14 -0000	1.38
  +++ BrokerHelper.java	12 Mar 2004 19:29:05 -0000	1.39
  @@ -15,8 +15,12 @@
    * limitations under the License.
    */
   
  -import java.util.StringTokenizer;
  +import java.sql.PreparedStatement;
  +import java.sql.ResultSet;
  +import java.sql.SQLException;
  +import java.util.Iterator;
   import java.util.Map;
  +import java.util.StringTokenizer;
   import java.util.WeakHashMap;
   
   import org.apache.ojb.broker.Identity;
  @@ -24,28 +28,26 @@
   import org.apache.ojb.broker.PersistenceBroker;
   import org.apache.ojb.broker.PersistenceBrokerException;
   import org.apache.ojb.broker.PersistenceBrokerSQLException;
  -import org.apache.ojb.broker.query.Query;
  -import org.apache.ojb.broker.query.QueryBySQL;
  -import org.apache.ojb.broker.query.QueryByCriteria;
  -import org.apache.ojb.broker.query.ReportQueryByCriteria;
  -import org.apache.ojb.broker.query.Criteria;
  -import org.apache.ojb.broker.query.MtoNQuery;
  -import org.apache.ojb.broker.query.ReportQueryByMtoNCriteria;
  -import org.apache.ojb.broker.core.ValueContainer;
   import org.apache.ojb.broker.accesslayer.IndirectionHandler;
   import org.apache.ojb.broker.accesslayer.StatementManagerIF;
   import org.apache.ojb.broker.accesslayer.sql.SqlExistStatement;
  +import org.apache.ojb.broker.core.ValueContainer;
   import org.apache.ojb.broker.metadata.ClassDescriptor;
   import org.apache.ojb.broker.metadata.FieldDescriptor;
  +import org.apache.ojb.broker.metadata.FieldHelper;
   import org.apache.ojb.broker.metadata.MetadataException;
   import org.apache.ojb.broker.metadata.MetadataManager;
   import org.apache.ojb.broker.metadata.fieldaccess.PersistentField;
  -import org.apache.ojb.broker.util.sequence.SequenceManagerException;
  +import org.apache.ojb.broker.platforms.Platform;
  +import org.apache.ojb.broker.query.Criteria;
  +import org.apache.ojb.broker.query.MtoNQuery;
  +import org.apache.ojb.broker.query.Query;
  +import org.apache.ojb.broker.query.QueryByCriteria;
  +import org.apache.ojb.broker.query.QueryBySQL;
  +import org.apache.ojb.broker.query.ReportQueryByCriteria;
  +import org.apache.ojb.broker.query.ReportQueryByMtoNCriteria;
   import org.apache.ojb.broker.util.logging.LoggerFactory;
  -//#ifdef JDK13
  -import java.sql.PreparedStatement;
  -import java.sql.ResultSet;
  -import java.sql.SQLException;
  +import org.apache.ojb.broker.util.sequence.SequenceManagerException;
   //#else
   /*
   import com.develop.java.lang.reflect.Proxy;
  @@ -451,20 +453,25 @@
       {
           if (aQuery instanceof QueryBySQL)
           {
  -            return getCountQuery((QueryBySQL)aQuery);
  +            return getQueryBySqlCount((QueryBySQL) aQuery);
  +        }
  +        else if (aQuery instanceof ReportQueryByCriteria)
  +        {
  +            return getReportQueryByCriteriaCount((ReportQueryByCriteria) aQuery);
           }
           else
           {
  -            return getCountQuery((QueryByCriteria)aQuery);
  +            return getQueryByCriteriaCount((QueryByCriteria) aQuery);
           }
       }
   
       /**
        * Create a Count-Query for QueryBySQL
  +     * 
        * @param aQuery
        * @return
        */
  -    private Query getCountQuery(QueryBySQL aQuery)
  +    private Query getQueryBySqlCount(QueryBySQL aQuery)
       {
           String countSql = aQuery.getSql();
   
  @@ -483,53 +490,66 @@
           return new QueryBySQL(aQuery.getSearchClass(), countSql);
       }
   
  -
       /**
        * Create a Count-Query for QueryByCriteria
  +     * 
        * @param aQuery
        * @return
        */
  -    private Query getCountQuery(QueryByCriteria aQuery)
  +    private Query getQueryByCriteriaCount(QueryByCriteria aQuery)
       {
           Class searchClass = aQuery.getSearchClass();
           ReportQueryByCriteria countQuery;
  -        Criteria countCrit = null;
  -        String[] columns;
  -
  -        // build a ReportQuery based on query orderby needs to be cleared
  -        if (aQuery.getCriteria() != null)
  -        {
  -            countCrit = aQuery.getCriteria().copy(false, false, false);
  -        }
  +        Criteria countCrit = aQuery.getCriteria();
  +        String[] columns = new String[1];
   
           if (aQuery.isDistinct())
           {
               //
               // BRJ: Count distinct is dbms dependent
               // hsql/sapdb: select count (distinct(person_id || project_id)) from person_project
  -            // mysql: select count (distinct person_id,project_id)  from person_project
  +            // mysql: select count (distinct person_id,project_id) from person_project
  +            //
  +            // concatenation of pk-columns is a simple way to obtain a single column
  +            // but concatenation is also dbms dependent:
  +            //
  +            // SELECT count(distinct concat(row1, row2, row3)) mysql
  +            // SELECT count(distinct (row1 || row2 || row3)) ansi
  +            // SELECT count(distinct (row1 + row2 + row3)) ms sql-server
               //
               FieldDescriptor[] pkFields = m_broker.getClassDescriptor(searchClass).getPkFields();
  -            columns = new String[pkFields.length];
  +            String[] keyColumns = new String[pkFields.length];
   
  -            for (int i = 0; i < pkFields.length; i++)
  +            if (pkFields.length > 1)
               {
  -                columns[i] = "count(distinct " + pkFields[i].getAttributeName() + ")";
  +                // TODO: Use ColumnName. This is a temporary solution because 
  +                // we cannot yet resolve multiple columns in the same attribute. 
  +                for (int i = 0; i < pkFields.length; i++)
  +                {
  +                    keyColumns[i] = pkFields[i].getColumnName();
  +                }
               }
  +            else
  +            {
  +                for (int i = 0; i < pkFields.length; i++)
  +                {
  +                    keyColumns[i] = pkFields[i].getAttributeName();
  +                }
  +            }
  +
  +            columns[0] = "count(distinct " + getPlatform().concatenate(keyColumns) + ")";
           }
           else
           {
  -            // BRJ: use count(*) if query not distinct
  -            columns = new String[1];
               columns[0] = "count(*)";
           }
   
  -
           // BRJ: we have to preserve indirection table !
           if (aQuery instanceof MtoNQuery)
           {
  -            MtoNQuery mnQuery = (MtoNQuery)aQuery;
  -            ReportQueryByMtoNCriteria mnReportQuery = new ReportQueryByMtoNCriteria(searchClass,
columns, countCrit);
  +            MtoNQuery mnQuery = (MtoNQuery) aQuery;
  +            ReportQueryByMtoNCriteria mnReportQuery = new ReportQueryByMtoNCriteria(searchClass,
  +                    columns, countCrit);
               mnReportQuery.setIndirectionTable(mnQuery.getIndirectionTable());
   
               countQuery = mnReportQuery;
  @@ -542,7 +562,40 @@
           return countQuery;
       }
   
  +    /**
  +     * Create a Count-Query for ReportQueryByCriteria
  +     * 
  +     * @param aQuery
  +     * @return
  +     */
  +    private Query getReportQueryByCriteriaCount(ReportQueryByCriteria aQuery)
  +    {
  +        ReportQueryByCriteria countQuery = (ReportQueryByCriteria) getQueryByCriteriaCount(aQuery);
   
  +        // BRJ: keep the original columns to build the Join
  +        countQuery.setJoinColumns(aQuery.getColumns());
  +
  +        // BRJ: we have to preserve groupby information
  +        Iterator iter = aQuery.getGroupBy().iterator();
  +        while (iter.hasNext())
  +        {
  +            countQuery.addGroupBy((FieldHelper) iter.next());
  +        }
  +
  +        return countQuery;
  +    }
  +
  +    /**
  +     * answer the platform
  +     * 
  +     * @return the platform
  +     */
  +    private Platform getPlatform()
  +    {
  +        return m_broker.serviceSqlGenerator().getPlatform();
  +    }
  +    
  +    
       /*
       NOTE: use WeakHashMap to allow reclaiming of no longer used ClassDescriptor
       instances
  
  
  
  1.7       +24 -5     db-ojb/src/java/org/apache/ojb/broker/query/ReportQueryByCriteria.java
  
  Index: ReportQueryByCriteria.java
  ===================================================================
  RCS file: /home/cvs/db-ojb/src/java/org/apache/ojb/broker/query/ReportQueryByCriteria.java,v
  retrieving revision 1.6
  retrieving revision 1.7
  diff -u -r1.6 -r1.7
  --- ReportQueryByCriteria.java	11 Mar 2004 18:16:13 -0000	1.6
  +++ ReportQueryByCriteria.java	12 Mar 2004 19:29:05 -0000	1.7
  @@ -20,17 +20,20 @@
    * Query for Reports. 
    * Supports selection of a subset of columns.
    * 
  - * @author <a href="mailto:jbraeuchi@hotmail.com">Jakob Braeuchi</a>
  + * @author <a href="mailto:jbraeuchi@gmx.ch">Jakob Braeuchi</a>
    * @version $Id$
    */
   public class ReportQueryByCriteria extends QueryByCriteria implements ReportQuery
   {
  -	// BRJ: define the columns to be selected for reports
  +	// define the columns to be selected for reports
   	private String[] m_columns = null;
   
  -    // BRJ: define the Jdbc-Types of the columns to be selected for reports
  +    // define the Jdbc-Types of the columns to be selected for reports
       private int[] m_jdbcTypes = null;
  -    
  +
  +    // define the additional columns to be used for the join
  +    private String[] m_joinColumns = null;
  +
   	/**
   	 * Constructor for ReportQueryByCriteria.
   	 * @param targetClass
  @@ -110,6 +113,22 @@
           this.m_jdbcTypes = jdbcTypes;
       }
   
  +    /**
  +     * @return Returns the joinColumns.
  +     */
  +    public String[] getJoinColumns()
  +    {
  +        return m_joinColumns;
  +    }
  +
  +    /**
  +     * @param joinColumns The joinColumns to set.
  +     */
  +    public void setJoinColumns(String[] joinColumns)
  +    {
  +        m_joinColumns = joinColumns;
  +    }
  +    
       /**
        * @see java.lang.Object#toString()
        */
  
  
  
  1.4       +12 -4     db-ojb/src/java/org/apache/ojb/broker/query/ReportQuery.java
  
  Index: ReportQuery.java
  ===================================================================
  RCS file: /home/cvs/db-ojb/src/java/org/apache/ojb/broker/query/ReportQuery.java,v
  retrieving revision 1.3
  retrieving revision 1.4
  diff -u -r1.3 -r1.4
  --- ReportQuery.java	11 Mar 2004 18:16:13 -0000	1.3
  +++ ReportQuery.java	12 Mar 2004 19:29:05 -0000	1.4
  @@ -19,7 +19,7 @@
   /**
    * Interface for ReportQueries
    * 
  - * @author <a href="mailto:jbraeuchi@hotmail.com">Jakob Braeuchi</a>
  + * @author <a href="mailto:jbraeuchi@gmx.ch">Jakob Braeuchi</a>
    * @version $Id$
    */
   
  @@ -29,7 +29,7 @@
   	 * Gets the columns used for the Report.
   	 * @return Returns a String[]
   	 */
  -	public String[] getColumns();
  +	String[] getColumns();
       
       /**
        * Gets the Jdbc-Types of the columns used for the Report.
  @@ -37,5 +37,13 @@
        * @return Returns an int[] of Jdbc-Types
        * @see java.sql.Types
        */
  -    public int[] getJdbcTypes();    
  +    int[] getJdbcTypes();  
  +    
  +    /**
  +     * Gets the additional columns used for building the Join.
  +     * These Columns are not appended to the select-clause.
  +     * @return Returns a String[]
  +     */
  +    String[] getJoinColumns();
  +    
   }
  
  
  
  1.52      +13 -1     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.51
  retrieving revision 1.52
  diff -u -r1.51 -r1.52
  --- QueryTest.java	21 Feb 2004 21:41:45 -0000	1.51
  +++ QueryTest.java	12 Mar 2004 19:29:05 -0000	1.52
  @@ -573,6 +573,10 @@
           }
           assertTrue(results.size() > 0);
   
  +        // compare with count
  +        int count = broker.getCount(q);
  +        assertEquals(results.size(), count);  
  +        
       }
   
       /**
  @@ -597,7 +601,7 @@
   
           // compare with count
           int count = broker.getCount(q);
  -//        assertEquals(results.size(), count);  
  +        assertEquals(results.size(), count);  
           
       }
       
  @@ -622,6 +626,10 @@
           }
           assertTrue(results.size() > 0);
   
  +        // compare with count
  +        int count = broker.getCount(q);
  +        assertEquals(results.size(), count);  
  +        
       }
   
       /**
  @@ -644,6 +652,10 @@
               results.add(iter.next());
           }
           assertTrue(results.size() > 0);
  +
  +        // compare with count
  +        int count = broker.getCount(q);
  +        assertEquals(results.size(), count); 
   
       }
       
  
  
  

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