dobbs 02/05/06 22:18:41
Modified: src/java/org/apache/torque/adapter DBMSSQL.java
src/java/org/apache/torque/util BasePeer.java Criteria.java
Query.java
src/test/org/apache/torque/util CriteriaTest.java
Log:
applying patches from Sam Joseph <gaijinATyha.att.ne.jp> with a few
modifications.
1. Add GROUP BY and HAVING functionality to the Criteria class
through addHaving() and addGroupByColumn() methods
2. Adjusts BasePeer so that databases that do not support native
limit syntax, or have weird ones (like MSSQL) will still function
with specified offset and limit parameters
3. Adjusts the MSSQL adapter so that it says it can't support native
limit operations and thus works with 2 above
Revision Changes Path
1.5 +12 -1
jakarta-turbine-torque/src/java/org/apache/torque/adapter/DBMSSQL.java
Index: DBMSSQL.java
===================================================================
RCS file:
/home/cvs/jakarta-turbine-torque/src/java/org/apache/torque/adapter/DBMSSQL.java,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- DBMSSQL.java 12 Apr 2002 05:58:32 -0000 1.4
+++ DBMSSQL.java 7 May 2002 05:18:41 -0000 1.5
@@ -63,7 +63,7 @@
* http://www.inetsoftware.de/
*
* @author <a href="mailto:[EMAIL PROTECTED]";>Gonzalo Diethelm</a>
- * @version $Id: DBMSSQL.java,v 1.4 2002/04/12 05:58:32 mpoeschl Exp $
+ * @version $Id: DBMSSQL.java,v 1.5 2002/05/07 05:18:41 dobbs Exp $
*/
public class DBMSSQL extends DBSybase
{
@@ -72,5 +72,16 @@
*/
protected DBMSSQL()
{
+ }
+
+ /**
+ * This method is used to chek whether the database natively
+ * supports limiting the size of the resultset.
+ *
+ * @return True.
+ */
+ public boolean supportsNativeLimit()
+ {
+ return false;
}
}
1.38 +30 -2
jakarta-turbine-torque/src/java/org/apache/torque/util/BasePeer.java
Index: BasePeer.java
===================================================================
RCS file:
/home/cvs/jakarta-turbine-torque/src/java/org/apache/torque/util/BasePeer.java,v
retrieving revision 1.37
retrieving revision 1.38
diff -u -r1.37 -r1.38
--- BasePeer.java 30 Apr 2002 10:37:07 -0000 1.37
+++ BasePeer.java 7 May 2002 05:18:41 -0000 1.38
@@ -106,7 +106,7 @@
* @author <a href="mailto:[EMAIL PROTECTED]";>Frank Y. Kim</a>
* @author <a href="mailto:[EMAIL PROTECTED]";>John D. McNally</a>
* @author <a href="mailto:[EMAIL PROTECTED]";>Brett McLaughlin</a>
- * @version $Id: BasePeer.java,v 1.37 2002/04/30 10:37:07 mpoeschl Exp $
+ * @version $Id: BasePeer.java,v 1.38 2002/05/07 05:18:41 dobbs Exp $
*/
public abstract class BasePeer implements java.io.Serializable
{
@@ -1055,8 +1055,10 @@
StringStack fromClause = query.getFromClause();
StringStack whereClause = query.getWhereClause();
StringStack orderByClause = query.getOrderByClause();
+ StringStack groupByClause = query.getGroupByClause();
StringStack orderBy = criteria.getOrderByColumns();
+ StringStack groupBy = criteria.getGroupByColumns();
boolean ignoreCase = criteria.isIgnoreCase();
StringStack select = criteria.getSelectColumns();
Hashtable aliases = criteria.getAsColumns();
@@ -1213,7 +1215,29 @@
ignorCase, db) );
}
}
-
+
+ // need to allow for multiple group bys
+ if ( groupBy != null && groupBy.size() > 0)
+ {
+ for (int i=0; i<groupBy.size(); i++)
+ {
+ String groupByColumn = groupBy.get(i);
+ if (groupByColumn.indexOf('.') == -1)
+ {
+ throwMalformedColumnNameException("group by",groupByColumn);
+ }
+
+ groupByClause.add(groupByColumn);
+ }
+ }
+
+ Criteria.Criterion having = criteria.getHaving();
+ if ( having != null )
+ {
+ //String groupByString = null;
+ query.setHaving(having.toString());
+ }
+
if ( orderBy != null && orderBy.size() > 0)
{
// Check for each String/Character column and apply
@@ -1326,6 +1350,8 @@
try
{
results = executeQuery( createQueryString(criteria),
+ criteria.getOffset(),
+ criteria.getLimit(),
criteria.isSingleRecord(), dbCon );
commitTransaction(dbCon);
}
@@ -1339,6 +1365,8 @@
else
{
results = executeQuery( createQueryString(criteria),
+ criteria.getOffset(),
+ criteria.getLimit(),
criteria.getDbName(),
criteria.isSingleRecord() );
}
1.28 +57 -1
jakarta-turbine-torque/src/java/org/apache/torque/util/Criteria.java
Index: Criteria.java
===================================================================
RCS file:
/home/cvs/jakarta-turbine-torque/src/java/org/apache/torque/util/Criteria.java,v
retrieving revision 1.27
retrieving revision 1.28
diff -u -r1.27 -r1.28
--- Criteria.java 30 Apr 2002 10:37:07 -0000 1.27
+++ Criteria.java 7 May 2002 05:18:41 -0000 1.28
@@ -88,7 +88,7 @@
* @author <a href="mailto:[EMAIL PROTECTED]";>Eric Dobbs</a>
* @author <a href="mailto:[EMAIL PROTECTED]";>Henning P. Schmiedehausen</a>
* @author <a href="mailto:[EMAIL PROTECTED]";>Sam Joseph</a>
- * @version $Id: Criteria.java,v 1.27 2002/04/30 10:37:07 mpoeschl Exp $
+ * @version $Id: Criteria.java,v 1.28 2002/05/07 05:18:41 dobbs Exp $
*/
public class Criteria extends Hashtable
{
@@ -158,6 +158,8 @@
private StringStack selectModifiers = new StringStack();
private StringStack selectColumns = new StringStack();
private StringStack orderByColumns = new StringStack();
+ private StringStack groupByColumns = new StringStack();
+ private Criterion having = null;
private Hashtable asColumns = new Hashtable(8);
private ArrayList joinL = null;
private ArrayList joinR = null;
@@ -1728,6 +1730,18 @@
}
/**
+ * Add group by column name.
+ *
+ * @param name The name of the column to group by.
+ * @return A modified Criteria object.
+ */
+ public Criteria addGroupByColumn( String groupBy )
+ {
+ groupByColumns.add( groupBy );
+ return this;
+ }
+
+ /**
* Add order by column name, explicitly specifying ascending.
*
* @param name The name of the column to order by.
@@ -1762,6 +1776,26 @@
}
/**
+ * Get group by columns.
+ *
+ * @return A StringStack with the name of the groupBy clause.
+ */
+ public StringStack getGroupByColumns()
+ {
+ return groupByColumns;
+ }
+
+ /**
+ * Get Having Criterion.
+ *
+ * @return A Criterion that is the having clause.
+ */
+ public Criterion getHaving()
+ {
+ return having;
+ }
+
+ /**
* Remove an object from the criteria.
*
* @param key A String with the key to be removed.
@@ -1864,6 +1898,28 @@
*
*------------------------------------------------------------------------
*/
+
+ /**
+ * This method adds a prepared Criterion object to the Criteria as a having
clause.
+ * You can get a new, empty Criterion object with the
+ * getNewCriterion() method.
+ *
+ * <p>
+ * <code>
+ * Criteria crit = new Criteria();
+ * Criteria.Criterion c = crit.getNewCriterion(BasePeer.ID, new Integer(5),
Criteria.LESS_THAN);
+ * crit.addHaving(c);
+ * </code>
+ *
+ * @param having A Criterion object
+ *
+ * @return A modified Criteria object.
+ */
+ public Criteria addHaving( Criterion having )
+ {
+ this.having = having;
+ return this;
+ }
/**
* This method adds a prepared Criterion object to the Criteria.
1.5 +45 -1
jakarta-turbine-torque/src/java/org/apache/torque/util/Query.java
Index: Query.java
===================================================================
RCS file:
/home/cvs/jakarta-turbine-torque/src/java/org/apache/torque/util/Query.java,v
retrieving revision 1.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- Query.java 1 Mar 2002 14:16:02 -0000 1.4
+++ Query.java 7 May 2002 05:18:41 -0000 1.5
@@ -64,7 +64,8 @@
* is used primarily by BasePeer.
*
* @author <a href="mailto:[EMAIL PROTECTED]";>John D. McNally</a>
- * @version $Id: Query.java,v 1.4 2002/03/01 14:16:02 mpoeschl Exp $
+ * @author <a href="mailto:[EMAIL PROTECTED]";>Sam Joseph</a>
+ * @version $Id: Query.java,v 1.5 2002/05/07 05:18:41 dobbs Exp $
*/
public class Query
{
@@ -74,6 +75,8 @@
private static final String AND = " AND ";
private static final String OR = " OR ";
private static final String ORDER_BY = " ORDER BY ";
+ private static final String GROUP_BY = " GROUP BY ";
+ private static final String HAVING = " HAVING ";
private static final String IN = " IN ";
private static final String BETWEEN = " BETWEEN ";
private static final String LIMIT = " LIMIT ";
@@ -84,6 +87,8 @@
private StringStack fromTables = new StringStack();
private StringStack whereCriteria = new StringStack();
private StringStack orderByColumns = new StringStack();
+ private StringStack groupByColumns = new StringStack();
+ private String having;
private String limit;
private String rowcount;
@@ -147,6 +152,28 @@
}
/**
+ * Retrieve the group by columns buffer in order to specify which
+ * columns are used to group the results of the query.
+ *
+ * @return A StringStack used to add columns to group on.
+ */
+ public StringStack getGroupByClause()
+ {
+ return groupByColumns;
+ }
+
+ /**
+ * Set the having clause. This is used to restrict which rows
+ * are returned.
+ *
+ * @param having A String.
+ */
+ public void setHaving(String having)
+ {
+ this.having = having;
+ }
+
+ /**
* Set the limit number. This is used to limit the number of rows
* returned by a query, and the row where the resultset starts.
*
@@ -169,6 +196,17 @@
}
/**
+ * Get the having clause. This is used to restrict which
+ * rows are returned based on some condition.
+ *
+ * @return A String that is the having clause.
+ */
+ public String getHaving()
+ {
+ return having;
+ }
+
+ /**
* Get the limit number. This is used to limit the number of
* returned by a query in Postgres.
*
@@ -213,6 +251,12 @@
if ( !orderByColumns.empty() )
stmt.append(ORDER_BY)
.append(orderByColumns.toString(", "));
+ if ( !groupByColumns.empty() )
+ stmt.append(GROUP_BY)
+ .append(groupByColumns.toString(", "));
+ if ( having != null )
+ stmt.append(HAVING)
+ .append(having);
if ( limit != null )
stmt.append(LIMIT)
.append(limit);
1.6 +78 -1
jakarta-turbine-torque/src/test/org/apache/torque/util/CriteriaTest.java
Index: CriteriaTest.java
===================================================================
RCS file:
/home/cvs/jakarta-turbine-torque/src/test/org/apache/torque/util/CriteriaTest.java,v
retrieving revision 1.5
retrieving revision 1.6
diff -u -r1.5 -r1.6
--- CriteriaTest.java 29 Apr 2002 17:46:09 -0000 1.5
+++ CriteriaTest.java 7 May 2002 05:18:41 -0000 1.6
@@ -66,7 +66,8 @@
* Test class for Criteria.
*
* @author <a href="mailto:[EMAIL PROTECTED]";>Christopher Elkins</a>
- * @version $Id: CriteriaTest.java,v 1.5 2002/04/29 17:46:09 dobbs Exp $
+ * @author <a href="mailto:[EMAIL PROTECTED]";>Sam Joseph</a>
+ * @version $Id: CriteriaTest.java,v 1.6 2002/05/07 05:18:41 dobbs Exp $
*/
public class CriteriaTest extends BaseTestCase
{
@@ -110,6 +111,79 @@
assertTrue(c.getString(table, column).equals(value));
}
+ /**
+ * test various properties of Criterion and nested criterion
+ */
+ public void testNestedCriterion()
+ {
+ final String table2 = "myTable2";
+ final String column2 = "myColumn2";
+ final String value2 = "myValue2";
+
+ final String table3 = "myTable3";
+ final String column3 = "myColumn3";
+ final String value3 = "myValue3";
+
+ final String table4 = "myTable4";
+ final String column4 = "myColumn4";
+ final String value4 = "myValue4";
+
+ final String table5 = "myTable5";
+ final String column5 = "myColumn5";
+ final String value5 = "myValue5";
+
+ Criteria.Criterion crit2 =
+ c.getNewCriterion(table2,column2,(Object)value2,Criteria.EQUAL);
+ Criteria.Criterion crit3 =
+ c.getNewCriterion(table3,column3,(Object)value3,Criteria.EQUAL);
+ Criteria.Criterion crit4 =
+ c.getNewCriterion(table4,column4,(Object)value4,Criteria.EQUAL);
+ Criteria.Criterion crit5 =
+ c.getNewCriterion(table5,column5,(Object)value5,Criteria.EQUAL);
+
+
+ crit2.and(crit3).or(crit4.and(crit5));
+ String expect = "((myTable2.myColumn2='myValue2' AND
myTable3.myColumn3='myValue3') OR (myTable4.myColumn4='myValue4' AND
myTable5.myColumn5='myValue5'))";
+ String result = crit2.toString();
+ assertEquals(expect,result);
+
+ Criteria.Criterion crit6 =
+ c.getNewCriterion(table2,column2,(Object)value2,Criteria.EQUAL);
+ Criteria.Criterion crit7 =
+ c.getNewCriterion(table3,column3,(Object)value3,Criteria.EQUAL);
+ Criteria.Criterion crit8 =
+ c.getNewCriterion(table4,column4,(Object)value4,Criteria.EQUAL);
+ Criteria.Criterion crit9 =
+ c.getNewCriterion(table5,column5,(Object)value5,Criteria.EQUAL);
+
+
+ crit6.and(crit7).or(crit8).and(crit9);
+ expect = "(((myTable2.myColumn2='myValue2' AND
myTable3.myColumn3='myValue3') OR myTable4.myColumn4='myValue4') AND
myTable5.myColumn5='myValue5')";
+ result = crit6.toString();
+ assertEquals(expect,result);
+
+
+ // should make sure we have tests for all possibilities
+
+ Criteria.Criterion[] crita = crit2.getAttachedCriterion();
+
+ assertEquals(crit2,crita[0]);
+ assertEquals(crit3,crita[1]);
+ assertEquals(crit4,crita[2]);
+ assertEquals(crit5,crita[3]);
+
+ String[] tables = crit2.getAllTables();
+
+ assertEquals(crit2.getTable(),tables[0]);
+ assertEquals(crit3.getTable(),tables[1]);
+ assertEquals(crit4.getTable(),tables[2]);
+ assertEquals(crit5.getTable(),tables[3]);
+
+ // simple confirmations that equality operations work
+ assertTrue(crit2.hashCode() == crit2.hashCode());
+ assertEquals(crit2.toString(),crit2.toString());
+ }
+
public void testBetweenCriterion()
{
Criteria.Criterion cn1 = c.getNewCriterion("INVOICE.COST",
@@ -133,6 +207,9 @@
assertEquals(expect,result);
}
+ /**
+ * Verify that AND and OR criterion are nested correctly.
+ */
public void testPrecedence()
{
Criteria.Criterion cn1 = c.getNewCriterion("INVOICE.COST",
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
