You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by br...@apache.org on 2004/07/20 22:44:59 UTC
cvs commit: db-ojb/src/test/org/apache/ojb/broker QueryTest.java
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