You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@turbine.apache.org by do...@apache.org on 2001/06/22 03:48:17 UTC
cvs commit: jakarta-turbine/proposals/eric/statement BaseSql.java BaseSqlTest.java Sql.java SqlTest.java
dobbs 01/06/21 18:48:16
Modified: proposals/eric/statement Sql.java
Added: proposals/eric/statement BaseSql.java BaseSqlTest.java
Removed: proposals/eric/statement SqlTest.java
Log:
* Added several functions: getCount, getMin, getMax, getAvg, getSum
getUpper, quoteAndEscapeText (modified from method by the same name
in SqlExpression)
* cleaned javadocs -- use @return
* Moved default implementation to BaseSql.java
* Changed Sql.java to an interface
* Renamed SqlTest.java to BaseSqlTest.java
(or more precisely, removed SqlTest.java, added BaseSqlTest.java)
Revision Changes Path
1.4 +146 -313 jakarta-turbine/proposals/eric/statement/Sql.java
Index: Sql.java
===================================================================
RCS file: /home/cvs/jakarta-turbine/proposals/eric/statement/Sql.java,v
retrieving revision 1.3
retrieving revision 1.4
diff -u -r1.3 -r1.4
--- Sql.java 2001/06/21 00:31:50 1.3
+++ Sql.java 2001/06/22 01:48:14 1.4
@@ -55,23 +55,17 @@
*/
import java.lang.StringBuffer;
-import java.lang.UnsupportedOperationException;
import java.util.List;
import java.util.Iterator;
/**
* <p>
- * This class contains default methods to construct SQL statements
- * using method calls. Subclasses should specialize the methods to
- * the SQL dialect of a specific database.
+ * This interface defines methods to construct String fragments of SQL
+ * statements. {@link org.apache.turbine.util.db.statement.BaseSql}
+ * provides a base implementation of this interface. Chaining the
+ * methods allows the creation of arbitrarily complex statements.
* </p>
*
- * <p>
- * This class defines methods that construct String fragments of SQL
- * statements. Chaining the methods allows the creation of
- * arbitrarily complex statements.
- * </p>
- *
* <pre>
* getSelect --> select <em>item1</em>,<em>item2</em>,...,<em>itemN</em>
* getFrom --> from <em>item1</em>,<em>item2</em>,...,<em>itemN</em>
@@ -94,430 +88,269 @@
* getOrderBy --> order by <em>item1</em>,<em>item2</em>,...,<em>itemN</em>
* getGroupBy --> group by <em>item1</em>,<em>item2</em>,...,<em>itemN</em>
* getHaving --> having <em>tree</em>
+ * getCount --> count(<em>item</em>)
+ * getMin --> min(<em>item</em>)
+ * getMax --> max(<em>item</em>)
+ * getAvg --> avg(<em>item</em>)
+ * getSum --> sum(<em>item</em>)
+ * getUpper --> upper(<em>item</em>)
* </pre>
*/
-public class Sql
+public interface Sql
{
- public static String EMPTY = "";
- public static String SELECT = "SELECT ";
- public static String FROM = " FROM ";
- public static String WHERE = " WHERE ";
- public static String ORDER_BY = " ORDER BY ";
- public static String GROUP_BY = " GROUP BY ";
- public static String HAVING = " HAVING ";
- public static String ASC = " ASC";
- public static String DESC = " DESC";
- public static String OPEN_PAREN = "(";
- public static String CLOSE_PAREN = ")";
- public static String EQUALS = "=";
- public static String NOT_EQUALS = "!=";
- public static String GREATER_THAN = ">";
- public static String LESS_THAN = "<";
- public static String GREATER_EQUAL = ">=";
- public static String LESS_EQUAL = "<=";
- public static String IS_NULL = " IS NULL";
- public static String IS_NOT_NULL = " IS NOT NULL";
- public static String IN = " IN ";
- public static String NOT_IN = " NOT IN ";
- public static String LIKE = " LIKE ";
- public static String AND = " AND ";
- public static String OR = " OR ";
- public static String NOT = "NOT ";
- public static String COMMA = ", ";
-
/**
- * the workhorse used by several other methods to construct a
- * String of the form [left][middle][right].
+ * Constructs a logical comparison using the equals operator.
*
- * this is useful for at least the following SQL fragments:
- * <dl>
- * <table>
- * <tr><td>where (foo_id=25)</td>
- * <td><em>middle:</em> foo_id=25</td></tr>
- * <tr><td>set (foo_id=25)</td>
- * <td><em>middle:</em> foo_id=25</td></tr>
- * <tr><td>table.column asc</td>
- * <td><em>left is EMPTY, middle:</em> "table.column"</td></tr>
- * <tr><td>upper (table.column)</td>
- * <td><em>middle:</em> table.column</td></tr>
- * <tr><td>count(*)</td>
- * <td><em>middle:</em> *</td></tr>
- * <tr><td>(table.column=15)</td>
- * <td><em>middle:</em>table.column=15</td></tr>
- * <tr><td>table.column=15</td>
- * <td><em>middle:</em> =</td></tr>
- * <tr><td>uppercase(table.column) like 'FOO%'</td>
- * <td><em>middle:</em> like</td></tr>
- * <tr><td>(table.column>10) AND (table.column<=20)</td>
- * <td><em>middle:</em> AND</td></tr>
- * <tr><td>set table.column=25</td>
- * <td><em>middle:</em> table.column=25, <em>right is EMPTY</em></td></tr>
- * </table>
- * </dl>
- *
- * @param left the String prefix for the object
- * @param right the String suffix for the object
- * @param middle String the thing in the middle
- */
- protected String leftRightMiddle(String left,
- String right,
- String middle)
- {
- StringBuffer sb = new StringBuffer(
- left.length()
- +right.length()
- +middle.length());
- sb.append(left)
- .append(middle)
- .append(right);
- return sb.toString();
- }
-
- /**
- * the workhorse used by several other methods to construct a
- * String of the form
- * [left][item1][connector][item2][connector]...[itemN][right].
- *
- * this is useful for at least the following SQL fragments:
- * insert (columnA, columnB, columnC)
- * values ('one', 'two', 'three')
- * (the following examples use an EMPTY right argument)
- * select columnA, sum(columnB), avg(columnB)
- * from table1 t1, table2 t2
- * order by columnC desc, columnA asc
- * group by columnA
- *
- * @param left String the left side of the list
- * @param right String the right side of the list
- * @param list List a list containing the items
- * @param connector String the list delimiter
- */
- protected String leftRightListConnector(String left,
- String right,
- List list,
- String connector)
- {
- //Fudging the initial size for the string buffer by
- //multiplying the number of items in the list by the
- //length of the connector + 7
- //
- //This could be done precisely by looping through the
- //list and converting each item to a string and summing
- //their lengths, but would that would be any less
- //expensive than letting the StringBuffer resize itself?
- StringBuffer sb = new StringBuffer(
- left.length()+right.length()+( (connector.length() + 7) * list.size() ));
-
- Iterator listIterator = list.iterator();
- sb.append(left);
- if (listIterator.hasNext())
- {
- sb.append(listIterator.next());
- }
- while (listIterator.hasNext())
- {
- sb.append(connector)
- .append(listIterator.next());
- }
- sb.append(right);
- return sb.toString();
- }
-
- /**
- * constructs a logical comparison using the equals operator:
- * (<em>left</em>=<em>right</em>)
- *
* @param left String the left side of the comparison
* @param right String the right side of the comparison
+ * @return (<em>left</em>=<em>right</em>)
*/
- public String getEquals(String left, String right)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, EQUALS));
- }
-
+ public String getEquals(String left, String right);
+
/**
- * constructs a logical comparison using the not equals operator:
- * (<em>left</em>!=<em>right</em>)
+ * Constructs a logical comparison using the not equals operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
+ * @return (<em>left</em>!=<em>right</em>)
*/
- public String getNotEquals(String left, String right)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, NOT_EQUALS));
- }
+ public String getNotEquals(String left, String right);
/**
- * constructs a logical comparison using the less than operator:
- * (<em>left</em><<em>right</em>)
+ * Constructs a logical comparison using the less than operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
+ * @return (<em>left</em><<em>right</em>)
*/
- public String getLessThan(String left, String right)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, LESS_THAN));
- }
-
+ public String getLessThan(String left, String right);
+
/**
- * constructs a logical comparison using the greater than operator:
- * (<em>left</em>><em>right</em>)
+ * Constructs a logical comparison using the greater than operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
+ * @return (<em>left</em>><em>right</em>)
*/
- public String getGreaterThan(String left, String right)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, GREATER_THAN));
- }
-
+ public String getGreaterThan(String left, String right);
+
/**
- * constructs a logical comparison using the less than or equal to
- * operator: (<em>left</em><=<em>right</em>)
+ * Constructs a logical comparison using the less than or equal to
+ * operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
+ * @return (<em>left</em><=<em>right</em>)
*/
- public String getLessEqual(String left, String right)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, LESS_EQUAL));
- }
+ public String getLessEqual(String left, String right);
/**
- * constructs a logical comparison using the greater than or equal
- * to operator: (<em>left</em>>=<em>right</em>)
+ * Constructs a logical comparison using the greater than or equal
+ * to operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
+ * @return (<em>left</em>>=<em>right</em>)
*/
- public String getGreaterEqual(String left, String right)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, GREATER_EQUAL));
- }
+ public String getGreaterEqual(String left, String right);
/**
- * constructs an is null fragment:
- * (<em>left</em> IS NULL)
+ * Constructs an is null fragment.
*
* @param left String the left side of the operator
+ * @return (<em>left</em> IS NULL)
*/
- public String getIsNull (String left)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, EMPTY, IS_NULL));
- }
+ public String getIsNull (String left);
/**
- * constructs an is not null fragment:
- * (<em>left</em> IS NOT NULL)
+ * Constructs an is not null fragment.
*
* @param left String the left side of the operator
+ * @return (<em>left</em> IS NOT NULL)
*/
- public String getIsNotNull (String left)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, EMPTY, IS_NOT_NULL));
- }
+ public String getIsNotNull (String left);
/**
- * constructs an in fragment:
- * (<em>left</em> IN (<em>item1</em>,...))
+ * Constructs an in fragment.
*
* @param left String the left side of the operator
* @param list List the list of items on the right side of the operator
+ * @return (<em>left</em> IN (<em>item1</em>,...))
*/
- public String getIn (String left, List list)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(
- left,
- leftRightListConnector(OPEN_PAREN,
- CLOSE_PAREN,
- list,
- COMMA),
- IN));
- }
+ public String getIn (String left, List list);
/**
- * constructs an not in fragment:
- * (<em>left</em> NOT IN (<em>item1</em>,...))
+ * Constructs an not in fragment.
*
* @param left String the left side of the operator
* @param list List the list of items on the right side of the operator
+ * @return (<em>left</em> NOT IN (<em>item1</em>,...))
*/
- public String getNotIn (String left, List list)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(
- left,
- leftRightListConnector(OPEN_PAREN,
- CLOSE_PAREN,
- list,
- COMMA),
- NOT_IN));
- }
+ public String getNotIn (String left, List list);
/**
- * constructs a logical comparison using the like operator:
- * (<em>left</em> LIKE <em>right</em>)
+ * Constructs a logical comparison using the like operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
+ * @return (<em>left</em> LIKE <em>right</em>)
*/
- public String getLike(String left, String right)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, LIKE));
- }
+ public String getLike(String left, String right);
/**
- * constructs a logical comparison using the and operator:
- * (<em>left</em> AND <em>right</em>)
+ * Constructs a logical comparison using the and operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
+ * @return (<em>left</em> AND <em>right</em>)
*/
- public String getAnd(String left, String right)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, AND));
- }
+ public String getAnd(String left, String right);
/**
- * constructs a logical comparison using the or operator:
- * (<em>left</em> OR <em>right</em>)
+ * Constructs a logical comparison using the or operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
+ * @return (<em>left</em> OR <em>right</em>)
*/
- public String getOr(String left, String right)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, OR));
- }
+ public String getOr(String left, String right);
/**
- * constructs a logical comparison using the not operator:
- * (NOT <em>right</em>)
+ * Constructs a logical comparison using the not operator.
*
* @param right String the right side of the comparison
+ * @return (NOT <em>right</em>)
*/
- public String getNot(String right)
- {
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(EMPTY, right, NOT));
- }
+ public String getNot(String right);
/**
- * constructs an ASC fragment:
- * <em>left</em> ASC
+ * Constructs an ascending fragment.
*
- * @param right String the right side of the comparison
+ * @param left String usually a column name
+ * @return <em>left</em> ASC
*/
- public String getAscending(String left)
- {
- return leftRightMiddle(left, EMPTY, ASC);
- }
+ public String getAscending(String left);
/**
- * constructs an DESC fragment:
- * <em>left</em> DESC
+ * Constructs a descending fragment.
*
- * @param right String the right side of the comparison
+ * @param left String usually a column name
+ * @return <em>left</em> DESC
*/
- public String getDescending(String left)
- {
- return leftRightMiddle(left, EMPTY, DESC);
- }
+ public String getDescending(String left);
/**
- * constructs a select fragment:
- * SELECT <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
+ * Constructs a select fragment.
*
* @param list List the list of items
+ * @return SELECT <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
*/
- public String getSelect(List list)
- {
- return leftRightListConnector(SELECT, EMPTY, list, COMMA);
- }
+ public String getSelect(List list);
/**
- * constructs a from fragment:
- * FROM <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
+ * Constructs a from fragment.
*
* @param list List the list of items
+ * @return FROM <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
*/
- public String getFrom(List list)
- {
- return leftRightListConnector(FROM, EMPTY, list, COMMA);
- }
+ public String getFrom(List list);
/**
- * constructs a where fragment:
- * WHERE <em>tree</em>
+ * Constructs a where fragment.
*
* @param tree String comparisons for the where clause.
* see getAnd(), getOr(), getEqual(), getLessThan(), etc
* for methods to help construct these comparisons
+ * @return WHERE <em>tree</em>
*/
- public String getWhere(String middle)
- {
- return leftRightMiddle(WHERE, EMPTY, middle);
- }
+ public String getWhere(String middle);
/**
- * constructs a having fragment:
- * HAVING <em>tree</em>
+ * Constructs a having fragment.
*
* @param tree String comparisons for the where clause.
* see getAnd(), getOr(), getEqual(), getLessThan(), etc
* for methods to help construct these comparisons
+ * @return HAVING <em>tree</em>
*/
- public String getHaving(String middle)
- {
- return leftRightMiddle(HAVING, EMPTY, middle);
- }
+ public String getHaving(String middle);
/**
- * constructs an order by fragment:
- * ORDER BY <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
+ * Constructs an order by fragment.
*
* @param list List the list of items
+ * @return ORDER BY <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
*/
- public String getOrderBy(List list)
- {
- return leftRightListConnector(ORDER_BY, EMPTY, list, COMMA);
- }
+ public String getOrderBy(List list);
/**
- * constructs a group by fragment:
- * GROUP BY <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
+ * Constructs a group by fragment.
*
* @param list List the list of items
+ * @return GROUP BY <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
*/
- public String getGroupBy(List list)
- {
- return leftRightListConnector(GROUP_BY, EMPTY, list, COMMA);
- }
+ public String getGroupBy(List list);
+ /**
+ * Constructs a count function.
+ *
+ * @param middle String the column to be counted
+ * @return COUNT(<em>middle</em)
+ */
+ public String getCount(String middle);
+
+ /**
+ * Constructs a min function.
+ *
+ * @param middle String the column to be searched for its minimum value
+ * @return MIN(<em>middle</em)
+ */
+ public String getMin(String middle);
+
+ /**
+ * Constructs a max function.
+ *
+ * @param middle String the column to be searched for its maximum value
+ * @return MAX(<em>middle</em)
+ */
+ public String getMax(String middle);
+
+ /**
+ * Constructs a avg function.
+ *
+ * @param middle String the column to be averaged
+ * @return AVG(<em>middle</em)
+ */
+ public String getAvg(String middle);
+
+ /**
+ * Constructs a sum function.
+ *
+ * @param middle String the column to be summed
+ * @return SUM(<em>middle</em)
+ */
+ public String getSum(String middle);
+
+ /**
+ * Constructs an upper function.
+ *
+ * @param middle String the column to be averaged
+ * @return UPPER(<em>middle</em)
+ */
+ public String getUpper(String middle);
+
+ /**
+ * Quotes and escapes raw text for placement in a SQL expression.
+ * For simplicity, the text is assumed to be neither quoted nor
+ * escaped.
+ *
+ * <p>
+ * raw string: <em>O'Malley's Can't be beat!</em><br/>
+ * qutoed and escaped: <em>'O''Malley''s Can''t be beat!'</em><br/>
+ * </p>
+ *
+ * @param rawText The <i>unquoted</i>, <i>unescaped</i> text to process.
+ * @return Quoted and escaped text.
+ */
+ public String quoteAndEscapeText(String rawText);
}
1.1 jakarta-turbine/proposals/eric/statement/BaseSql.java
Index: BaseSql.java
===================================================================
package org.apache.turbine.util.db.statement;
/* ====================================================================
* The Apache Software License, Version 1.1
*
* Copyright (c) 2001 The Apache Software Foundation. All rights
* reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
*
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
*
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in
* the documentation and/or other materials provided with the
* distribution.
*
* 3. The end-user documentation included with the redistribution,
* if any, must include the following acknowledgment:
* "This product includes software developed by the
* Apache Software Foundation (http://www.apache.org/)."
* Alternately, this acknowledgment may appear in the software itself,
* if and wherever such third-party acknowledgments normally appear.
*
* 4. The names "Apache" and "Apache Software Foundation" and
* "Apache Turbine" must not be used to endorse or promote products
* derived from this software without prior written permission. For
* written permission, please contact apache@apache.org.
*
* 5. Products derived from this software may not be called "Apache",
* "Apache Turbine", nor may "Apache" appear in their name, without
* prior written permission of the Apache Software Foundation.
*
* THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
* WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
* OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
* ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
* USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
* OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
* OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
* SUCH DAMAGE.
* ====================================================================
*
* This software consists of voluntary contributions made by many
* individuals on behalf of the Apache Software Foundation. For more
* information on the Apache Software Foundation, please see
* <http://www.apache.org/>.
*/
import java.lang.StringBuffer;
import java.util.List;
import java.util.Iterator;
/**
* <p>
* This class contains default methods to construct SQL statements
* using method calls. Subclasses should specialize the methods to
* the SQL dialect of a specific database.
* </p>
*
* <p>
* This class defines methods that construct String fragments of SQL
* statements. Chaining the methods allows the creation of
* arbitrarily complex statements.
* </p>
*
* <pre>
* getSelect --> select <em>item1</em>,<em>item2</em>,...,<em>itemN</em>
* getFrom --> from <em>item1</em>,<em>item2</em>,...,<em>itemN</em>
* getWhere --> where <em>tree</em>
* getEqual --> (<em>left</em> = <em>right</em>)
* getNotEqual --> (<em>left</em> != <em>right</em>)
* getGreaterThan --> (<em>left</em> > <em>right</em>)
* getGreaterEqual --> (<em>left</em> >= <em>right</em>)
* getLessThan --> (<em>left</em> < <em>right</em>)
* getLessEqual --> (<em>left</em> <= <em>right</em>)
* getNull --> (<em>item</em> is null)
* getNotNull --> (<em>item</em> is not null)
* getIn --> (<em>left</em> in <em>right</em>)
* getNotIn --> (<em>left</em> not in <em>right</em>)
* getAnd --> (<em>left</em> and <em>right</em>)
* getOr --> (<em>left</em> or <em>right</em>)
* getNot --> (not <em>item</em>)
* getAscending --> <em>item</em> ASC
* getDescending --> <em>item</em> DESC
* getOrderBy --> order by <em>item1</em>,<em>item2</em>,...,<em>itemN</em>
* getGroupBy --> group by <em>item1</em>,<em>item2</em>,...,<em>itemN</em>
* getHaving --> having <em>tree</em>
* </pre>
*
* <p>
* Stored procedures can also be implemented. Extend this class in
* your own custom database adaptor. Add methods to construct SQL
* fragments for your stored procedures. The implementations for
* functions (such as getCount(), getSum()...) would be useful
* models to follow for your stored procedures.
* </p>
*/
public class BaseSql
implements Sql
{
public static String EMPTY = "";
public static String SELECT = "SELECT ";
public static String FROM = " FROM ";
public static String WHERE = " WHERE ";
public static String ORDER_BY = " ORDER BY ";
public static String GROUP_BY = " GROUP BY ";
public static String HAVING = " HAVING ";
public static String ASC = " ASC";
public static String DESC = " DESC";
public static String OPEN_PAREN = "(";
public static String CLOSE_PAREN = ")";
public static String EQUALS = "=";
public static String NOT_EQUALS = "!=";
public static String GREATER_THAN = ">";
public static String LESS_THAN = "<";
public static String GREATER_EQUAL = ">=";
public static String LESS_EQUAL = "<=";
public static String IS_NULL = " IS NULL";
public static String IS_NOT_NULL = " IS NOT NULL";
public static String IN = " IN ";
public static String NOT_IN = " NOT IN ";
public static String LIKE = " LIKE ";
public static String AND = " AND ";
public static String OR = " OR ";
public static String NOT = "NOT ";
public static String COUNT = "COUNT";
public static String MIN = "MIN";
public static String MAX = "MAX";
public static String AVG = "AVG";
public static String SUM = "SUM";
public static String UPPER = "UPPER";
public static String COMMA = ", ";
private static final char SINGLE_QUOTE = '\'';
/**
* The workhorse used by several other methods to construct a
* String of the form [left][middle][right].
*
* this is useful for at least the following SQL fragments:
* <dl>
* <table>
* <tr><td>where (foo_id=25)</td>
* <td><em>middle:</em> foo_id=25</td></tr>
* <tr><td>set (foo_id=25)</td>
* <td><em>middle:</em> foo_id=25</td></tr>
* <tr><td>table.column asc</td>
* <td><em>left is EMPTY, middle:</em> "table.column"</td></tr>
* <tr><td>upper (table.column)</td>
* <td><em>middle:</em> table.column</td></tr>
* <tr><td>count(*)</td>
* <td><em>middle:</em> *</td></tr>
* <tr><td>(table.column=15)</td>
* <td><em>middle:</em>table.column=15</td></tr>
* <tr><td>table.column=15</td>
* <td><em>middle:</em> =</td></tr>
* <tr><td>uppercase(table.column) like 'FOO%'</td>
* <td><em>middle:</em> like</td></tr>
* <tr><td>(table.column>10) AND (table.column<=20)</td>
* <td><em>middle:</em> AND</td></tr>
* <tr><td>set table.column=25</td>
* <td><em>middle:</em> table.column=25, <em>right is EMPTY</em></td></tr>
* </table>
* </dl>
*
* @param left the String prefix for the object
* @param right the String suffix for the object
* @param middle String the thing in the middle
* @return [left][middle][right]
*/
protected String leftRightMiddle(String left,
String right,
String middle)
{
StringBuffer sb = new StringBuffer(
left.length()
+right.length()
+middle.length());
sb.append(left)
.append(middle)
.append(right);
return sb.toString();
}
/**
* The workhorse used by several other methods to construct a
* String of the form
* [left][item1][connector][item2][connector]...[itemN][right].
*
* this is useful for at least the following SQL fragments:
* insert (columnA, columnB, columnC)
* values ('one', 'two', 'three')
* (the following examples use an EMPTY right argument)
* select columnA, sum(columnB), avg(columnB)
* from table1 t1, table2 t2
* order by columnC desc, columnA asc
* group by columnA
*
* @param left String the left side of the list
* @param right String the right side of the list
* @param list List a list containing the items
* @param connector String the list delimiter
* @return [left][item1][connector][item2][connector]...[itemN][right]
*/
protected String leftRightListConnector(String left,
String right,
List list,
String connector)
{
//Fudging the initial size for the string buffer by
//multiplying the number of items in the list by the
//length of the connector + 7
//
//This could be done precisely by looping through the
//list and converting each item to a string and summing
//their lengths, but would that would be any less
//expensive than letting the StringBuffer resize itself?
StringBuffer sb = new StringBuffer(
left.length()+right.length()+( (connector.length() + 7) * list.size() ));
Iterator listIterator = list.iterator();
sb.append(left);
if (listIterator.hasNext())
{
sb.append(listIterator.next());
}
while (listIterator.hasNext())
{
sb.append(connector)
.append(listIterator.next());
}
sb.append(right);
return sb.toString();
}
/**
* Constructs a logical comparison using the equals operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
* @return (<em>left</em>=<em>right</em>)
*/
public String getEquals(String left, String right)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(left, right, EQUALS));
}
/**
* Constructs a logical comparison using the not equals operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
* @return (<em>left</em>!=<em>right</em>)
*/
public String getNotEquals(String left, String right)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(left, right, NOT_EQUALS));
}
/**
* Constructs a logical comparison using the less than operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
* @return (<em>left</em><<em>right</em>)
*/
public String getLessThan(String left, String right)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(left, right, LESS_THAN));
}
/**
* Constructs a logical comparison using the greater than operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
* @return (<em>left</em>><em>right</em>)
*/
public String getGreaterThan(String left, String right)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(left, right, GREATER_THAN));
}
/**
* Constructs a logical comparison using the less than or equal to
* operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
* @return (<em>left</em><=<em>right</em>)
*/
public String getLessEqual(String left, String right)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(left, right, LESS_EQUAL));
}
/**
* Constructs a logical comparison using the greater than or equal
* to operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
* @return (<em>left</em>>=<em>right</em>)
*/
public String getGreaterEqual(String left, String right)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(left, right, GREATER_EQUAL));
}
/**
* Constructs an is null fragment.
*
* @param left String the left side of the operator
* @return (<em>left</em> IS NULL)
*/
public String getIsNull (String left)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(left, EMPTY, IS_NULL));
}
/**
* Constructs an is not null fragment.
*
* @param left String the left side of the operator
* @return (<em>left</em> IS NOT NULL)
*/
public String getIsNotNull (String left)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(left, EMPTY, IS_NOT_NULL));
}
/**
* Constructs an in fragment.
*
* @param left String the left side of the operator
* @param list List the list of items on the right side of the operator
* @return (<em>left</em> IN (<em>item1</em>,...))
*/
public String getIn (String left, List list)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(
left,
leftRightListConnector(OPEN_PAREN,
CLOSE_PAREN,
list,
COMMA),
IN));
}
/**
* Constructs an not in fragment.
*
* @param left String the left side of the operator
* @param list List the list of items on the right side of the operator
* @return (<em>left</em> NOT IN (<em>item1</em>,...))
*/
public String getNotIn (String left, List list)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(
left,
leftRightListConnector(OPEN_PAREN,
CLOSE_PAREN,
list,
COMMA),
NOT_IN));
}
/**
* Constructs a logical comparison using the like operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
* @return (<em>left</em> LIKE <em>right</em>)
*/
public String getLike(String left, String right)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(left, right, LIKE));
}
/**
* Constructs a logical comparison using the and operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
* @return (<em>left</em> AND <em>right</em>)
*/
public String getAnd(String left, String right)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(left, right, AND));
}
/**
* Constructs a logical comparison using the or operator.
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
* @return (<em>left</em> OR <em>right</em>)
*/
public String getOr(String left, String right)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(left, right, OR));
}
/**
* Constructs a logical comparison using the not operator.
*
* @param right String the right side of the comparison
* @return (NOT <em>right</em>)
*/
public String getNot(String right)
{
return leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
leftRightMiddle(EMPTY, right, NOT));
}
/**
* Constructs an ascending fragment.
*
* @param left String usually a column name
* @return <em>left</em> ASC
*/
public String getAscending(String left)
{
return leftRightMiddle(left, EMPTY, ASC);
}
/**
* Constructs a descending fragment.
*
* @param left String usually a column name
* @return <em>left</em> DESC
*/
public String getDescending(String left)
{
return leftRightMiddle(left, EMPTY, DESC);
}
/**
* Constructs a select fragment.
*
* @param list List the list of items
* @return SELECT <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
*/
public String getSelect(List list)
{
return leftRightListConnector(SELECT, EMPTY, list, COMMA);
}
/**
* Constructs a from fragment.
*
* @param list List the list of items
* @return FROM <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
*/
public String getFrom(List list)
{
return leftRightListConnector(FROM, EMPTY, list, COMMA);
}
/**
* Constructs a where fragment.
*
* @param tree String comparisons for the where clause.
* see getAnd(), getOr(), getEqual(), getLessThan(), etc
* for methods to help construct these comparisons
* @return WHERE <em>tree</em>
*/
public String getWhere(String middle)
{
return leftRightMiddle(WHERE, EMPTY, middle);
}
/**
* Constructs a having fragment.
*
* @param tree String comparisons for the where clause.
* see getAnd(), getOr(), getEqual(), getLessThan(), etc
* for methods to help construct these comparisons
* @return HAVING <em>tree</em>
*/
public String getHaving(String middle)
{
return leftRightMiddle(HAVING, EMPTY, middle);
}
/**
* Constructs an order by fragment.
*
* @param list List the list of items
* @return ORDER BY <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
*/
public String getOrderBy(List list)
{
return leftRightListConnector(ORDER_BY, EMPTY, list, COMMA);
}
/**
* Constructs a group by fragment.
*
* @param list List the list of items
* @return GROUP BY <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
*/
public String getGroupBy(List list)
{
return leftRightListConnector(GROUP_BY, EMPTY, list, COMMA);
}
/**
* Constructs a count function.
*
* @param middle String the column to be counted
* @return COUNT(<em>middle</em)
*/
public String getCount(String middle)
{
return leftRightMiddle(COUNT,
EMPTY,
leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
middle));
}
/**
* Constructs a min function.
*
* @param middle String the column to be searched for its minimum value
* @return MIN(<em>middle</em)
*/
public String getMin(String middle)
{
return leftRightMiddle(MIN,
EMPTY,
leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
middle));
}
/**
* Constructs a max function.
*
* @param middle String the column to be searched for its maximum value
* @return MAX(<em>middle</em)
*/
public String getMax(String middle)
{
return leftRightMiddle(MAX,
EMPTY,
leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
middle));
}
/**
* Constructs a avg function.
*
* @param middle String the column to be averaged
* @return AVG(<em>middle</em)
*/
public String getAvg(String middle)
{
return leftRightMiddle(AVG,
EMPTY,
leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
middle));
}
/**
* Constructs a sum function.
*
* @param middle String the column to be summed
* @return SUM(<em>middle</em)
*/
public String getSum(String middle)
{
return leftRightMiddle(SUM,
EMPTY,
leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
middle));
}
/**
* Constructs an upper function.
*
* @param middle String the column to be averaged
* @return UPPER(<em>middle</em)
*/
public String getUpper(String middle)
{
return leftRightMiddle(UPPER,
EMPTY,
leftRightMiddle(OPEN_PAREN,
CLOSE_PAREN,
middle));
}
/**
* Quotes and escapes raw text for placement in a SQL expression.
* For simplicity, the text is assumed to be neither quoted nor
* escaped.
*
* <p>
* raw string: <em>O'Malley's Can't be beat!</em><br/>
* qutoed and escaped: <em>'O''Malley''s Can''t be beat!'</em><br/>
* </p>
*
* @param rawText The <i>unquoted</i>, <i>unescaped</i> text to process.
* @return Quoted and escaped text.
*/
public String quoteAndEscapeText(String rawText)
{
StringBuffer buf = new StringBuffer( (int)(rawText.length() * 1.1) );
char[] data = rawText.toCharArray();
buf.append(SINGLE_QUOTE);
for (int i = 0; i < data.length; i++)
{
switch (data[i])
{
case SINGLE_QUOTE:
buf.append(SINGLE_QUOTE).append(SINGLE_QUOTE);
break;
// Some databases need to have backslashes escaped.
// Subclasses can override this method to include
// this case if appropriate.
/*
case BACKSLASH:
buf.append(BACKSLASH).append(BACKSLASH);
break;
*/
default:
buf.append(data[i]);
}
}
buf.append(SINGLE_QUOTE);
return buf.toString();
}
}
1.1 jakarta-turbine/proposals/eric/statement/BaseSqlTest.java
Index: BaseSqlTest.java
===================================================================
package org.apache.turbine.util.db.statement;
/* ====================================================================
* The Apache Software License, Version 1.1
*
* Copyright (c) 2001 The Apache Software Foundation. All rights
* reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
*
* 1. Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
*
* 2. Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in
* the documentation and/or other materials provided with the
* distribution.
*
* 3. The end-user documentation included with the redistribution,
* if any, must include the following acknowledgment:
* "This product includes software developed by the
* Apache Software Foundation (http://www.apache.org/)."
* Alternately, this acknowledgment may appear in the software itself,
* if and wherever such third-party acknowledgments normally appear.
*
* 4. The names "Apache" and "Apache Software Foundation" and
* "Apache Turbine" must not be used to endorse or promote products
* derived from this software without prior written permission. For
* written permission, please contact apache@apache.org.
*
* 5. Products derived from this software may not be called "Apache",
* "Apache Turbine", nor may "Apache" appear in their name, without
* prior written permission of the Apache Software Foundation.
*
* THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
* WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
* OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
* ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
* USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
* OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
* OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
* SUCH DAMAGE.
* ====================================================================
*
* This software consists of voluntary contributions made by many
* individuals on behalf of the Apache Software Foundation. For more
* information on the Apache Software Foundation, please see
* <http://www.apache.org/>.
*/
import java.util.ArrayList;
import junit.framework.Test;
import junit.framework.TestSuite;
import org.apache.turbine.test.BaseTestCase;
import org.apache.turbine.util.db.statement.BaseSql;
/**
* Test class for BaseSql.
*
* @author <a href="mailto:eric@dobbse.net">Eric Dobbs</a>
*/
public class BaseSqlTest extends BaseTestCase
{
BaseSql s;
/**
* Creates a new instance.
*/
public BaseSqlTest(String name)
{
super(name);
s = new BaseSql();
}
/**
* Creates a test suite for this class.
*
* @return A test suite for this class.
*/
public static Test suite()
{
return new TestSuite(BaseSqlTest.class);
}
public void testLeftRightMiddle()
{
String result = s.leftRightMiddle("set (",
")",
"column=30");
String expect = "set (column=30)";
assert(result.equals(expect));
}
public void testLeftRightListConnector()
{
ArrayList list = new ArrayList(3);
list.add("columnA");
list.add("columnB");
list.add("columnC");
String result = s.leftRightListConnector("select ",
"",
list,
", ");
String expect = "select columnA, columnB, columnC";
assert(result.equals(expect));
}
public void testGetEquals()
{
String result = s.getEquals("table.column",
"25");
String expect = "(table.column=25)";
assert(result.equals(expect));
}
public void testGetNotEquals()
{
String result = s.getNotEquals("table.column",
"25");
String expect = "(table.column!=25)";
assert(result.equals(expect));
}
public void testGetLessThan()
{
String result = s.getLessThan("table.column",
"25");
String expect = "(table.column<25)";
assert(result.equals(expect));
}
public void testGetGreaterThan()
{
String result = s.getGreaterThan("table.column",
"25");
String expect = "(table.column>25)";
assert(result.equals(expect));
}
public void testGetLessEqualThan()
{
String result = s.getLessEqual("table.column",
"25");
String expect = "(table.column<=25)";
assert(result.equals(expect));
}
public void testGetGreaterEqual()
{
String result = s.getGreaterEqual("table.column",
"25");
String expect = "(table.column>=25)";
assert(result.equals(expect));
}
public void testGetIsNull()
{
String result = s.getIsNull("this");
String expect = "(this IS NULL)";
assert(result.equals(expect));
}
public void testGetIn()
{
ArrayList list = new ArrayList();
list.add("'foo'");
list.add("'bar'");
list.add("'baz'");
String result = s.getIn("this",list);
String expect = "(this IN ('foo', 'bar', 'baz'))";
assert(result.equals(expect));
}
public void testGetNotIn()
{
ArrayList list = new ArrayList();
list.add("'foo'");
list.add("'bar'");
list.add("'baz'");
String result = s.getNotIn("this",list);
String expect = "(this NOT IN ('foo', 'bar', 'baz'))";
assert(result.equals(expect));
}
public void testGetLike()
{
String result = s.getLike("this",
"'that%'");
String expect = "(this LIKE 'that%')";
assert(result.equals(expect));
}
public void testGetAnd()
{
String result = s.getAnd("this",
"that");
String expect = "(this AND that)";
assert(result.equals(expect));
}
public void testGetOr()
{
String result = s.getOr("this",
"that");
String expect = "(this OR that)";
assert(result.equals(expect));
}
public void testGetNot()
{
String result = s.getNot("this");
String expect = "(NOT this)";
assert(result.equals(expect));
}
public void testGetAscending()
{
String result = s.getAscending("this");
String expect = "this ASC";
assert(result.equals(expect));
}
public void testGetDescending()
{
String result = s.getDescending("this");
String expect = "this DESC";
assert(result.equals(expect));
}
public void testNestedComparison()
{
String result = s.getOr(
s.getAnd(
s.getGreaterThan("table.columnA","10"),
s.getLessEqual("table.columnA","50")),
s.getAnd(
s.getGreaterThan("table.columnB","37"),
s.getLessEqual("table.columnB","42")));
String expect =
"(((table.columnA>10) AND (table.columnA<=50))"
+ " OR ((table.columnB>37) AND (table.columnB<=42)))";
assert(result.equals(expect));
}
public void testGetSelect()
{
ArrayList list = new ArrayList(3);
list.add("columnA");
list.add("columnB");
list.add("columnC");
String result = s.getSelect(list);
String expect = "SELECT columnA, columnB, columnC";
assert(result.equals(expect));
}
public void testGetFrom()
{
ArrayList list = new ArrayList(3);
list.add("tableA");
list.add("tableB");
list.add("tableC");
String result = s.getFrom(list);
String expect = " FROM tableA, tableB, tableC";
assert(result.equals(expect));
}
public void testGetWhere()
{
String result = s.getWhere("(column like '%foo%')");
String expect = " WHERE (column like '%foo%')";
assert(result.equals(expect));
}
public void testGetOrderBy()
{
ArrayList list = new ArrayList();
list.add(s.getAscending("column1"));
list.add(s.getDescending("column2"));
list.add("column3");
String result = s.getOrderBy(list);
String expect = " ORDER BY column1 ASC, column2 DESC, column3";
assert(result.equals(expect));
}
public void testGetGroupBy()
{
ArrayList list = new ArrayList();
list.add(s.getAscending("column1"));
list.add(s.getDescending("column2"));
list.add("column3");
String result = s.getGroupBy(list);
String expect = " GROUP BY column1 ASC, column2 DESC, column3";
assert(result.equals(expect));
}
public void testGetHaving()
{
String result = s.getHaving("(sum(column)>100)");
String expect = " HAVING (sum(column)>100)";
assert(result.equals(expect));
}
public void testGetCount()
{
String result = s.getCount("*");
String expect = "COUNT(*)";
assert(result.equals(expect));
}
public void testGetMin()
{
String result = s.getMin("table.column");
String expect = "MIN(table.column)";
assert(result.equals(expect));
}
public void testGetMax()
{
String result = s.getMax("table.column");
String expect = "MAX(table.column)";
assert(result.equals(expect));
}
public void testGetAvg()
{
String result = s.getAvg("table.column");
String expect = "AVG(table.column)";
assert(result.equals(expect));
}
public void testGetSum()
{
String result = s.getSum("table.column");
String expect = "SUM(table.column)";
assert(result.equals(expect));
}
public void testGetUpper()
{
String result = s.getUpper("table.column");
String expect = "UPPER(table.column)";
assert(result.equals(expect));
}
public void testQuoteAndEscapeText()
{
String result = s.quoteAndEscapeText("O'Malley's Can't be beat!");
String expect = "'O''Malley''s Can''t be beat!'";
assert(result.equals(expect));
}
public void testGetWhereWithComposite()
{
String result = s.getWhere(
s.getOr(
s.getAnd(
s.getGreaterEqual("columnA","25"),
s.getLessEqual("columnA","50")
),
s.getAnd(
s.getGreaterEqual("columnB","10"),
s.getLessEqual("columnB","20")
)
)
);
String expect = " WHERE (((columnA>=25) AND (columnA<=50))"
+ " OR ((columnB>=10) AND (columnB<=20)))";
System.out.println("expect: " + expect);
System.out.println("result: " + result);
assert(result.equals(expect));
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org