dobbs 01/06/24 07:38:24
Modified: proposals/eric/statement BaseSql.java BaseSqlTest.java
Sql.java
Log:
* renamed leftRightMiddle method to leftMiddleRight
(thanks to Mike Haberman for the suggestion)
* added getInsert, getValues, getUpdate, getSet, getDelete methods
Revision Changes Path
1.3 +223 -127 jakarta-turbine/proposals/eric/statement/BaseSql.java
Index: BaseSql.java
===================================================================
RCS file: /home/cvs/jakarta-turbine/proposals/eric/statement/BaseSql.java,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -r1.2 -r1.3
--- BaseSql.java 2001/06/22 21:49:45 1.2
+++ BaseSql.java 2001/06/24 14:38:23 1.3
@@ -72,6 +72,11 @@
* </p>
*
* <pre>
+ * getInsert --> insert into <em>left</em>
(<em>item1</em>,...,<em>itemN</em>)
+ * getValues --> values (<em>item1</em>,...,<em>itemN</em>)
+ * getUpdate --> update <em>left</em> <em>item1</em>,...,<em>itemN</em>
+ * getSet --> set <em>left</em>=<em>right</em>
+ * getDelete --> delete from <em>left</em>
* 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>
@@ -93,6 +98,12 @@
* 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>
*
* <p>
@@ -106,39 +117,44 @@
public class BaseSql
implements Sql
{
- public static String EMPTY = "";
- public static String SELECT = "SELECT ";
- public static String SET = "SET ";
- 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 EMPTY = "";
+ public static String SPACE = " ";
+ public static String INSERT = "INSERT INTO ";
+ public static String VALUES = " VALUES ";
+ public static String UPDATE = "UPDATE ";
+ public static String DELETE = "DELETE FROM ";
+ public static String SELECT = "SELECT ";
+ public static String SET = "SET ";
+ 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 = ", ";
+ 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 = '\'';
/**
@@ -172,26 +188,26 @@
* </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
+ * @param right the String suffix for the object
* @return [left][middle][right]
*/
- protected String leftRightMiddle(String left,
- String right,
- String middle)
+ protected String leftMiddleRight(String left,
+ String middle,
+ String right)
{
StringBuffer sb = new StringBuffer(left.length()
+right.length()
+middle.length());
- leftRightMiddle(sb, left, right, middle);
+ leftMiddleRight(sb, left, middle, right);
return sb.toString();
}
- protected String leftRightMiddle(StringBuffer sb,
+ protected String leftMiddleRight(StringBuffer sb,
String left,
- String right,
- String middle)
+ String middle,
+ String right)
{
sb.append(left).append(middle).append(right);
return sb.toString();
@@ -266,9 +282,9 @@
*/
public String getEquals(String left, String right)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, EQUALS));
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(left, EQUALS, right),
+ CLOSE_PAREN);
}
/**
@@ -280,9 +296,9 @@
*/
public String getNotEquals(String left, String right)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, NOT_EQUALS));
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(left, NOT_EQUALS, right),
+ CLOSE_PAREN);
}
/**
@@ -294,9 +310,9 @@
*/
public String getLessThan(String left, String right)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, LESS_THAN));
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(left, LESS_THAN, right),
+ CLOSE_PAREN);
}
/**
@@ -308,9 +324,9 @@
*/
public String getGreaterThan(String left, String right)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, GREATER_THAN));
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(left, GREATER_THAN, right),
+ CLOSE_PAREN);
}
/**
@@ -323,9 +339,9 @@
*/
public String getLessEqual(String left, String right)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, LESS_EQUAL));
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(left, LESS_EQUAL, right),
+ CLOSE_PAREN);
}
/**
@@ -338,9 +354,9 @@
*/
public String getGreaterEqual(String left, String right)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, GREATER_EQUAL));
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(left, GREATER_EQUAL, right),
+ CLOSE_PAREN);
}
/**
@@ -351,9 +367,9 @@
*/
public String getIsNull (String left)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, EMPTY, IS_NULL));
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(left, IS_NULL, EMPTY),
+ CLOSE_PAREN);
}
/**
@@ -364,9 +380,9 @@
*/
public String getIsNotNull (String left)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, EMPTY, IS_NOT_NULL));
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(left, IS_NOT_NULL, EMPTY),
+ CLOSE_PAREN);
}
/**
@@ -378,15 +394,15 @@
*/
public String getIn (String left, List list)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(
- left,
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(
+ left,
+ IN,
leftRightListConnector(OPEN_PAREN,
CLOSE_PAREN,
list,
- COMMA),
- IN));
+ COMMA)),
+ CLOSE_PAREN);
}
/**
@@ -398,15 +414,15 @@
*/
public String getNotIn (String left, List list)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(
left,
+ NOT_IN,
leftRightListConnector(OPEN_PAREN,
CLOSE_PAREN,
list,
- COMMA),
- NOT_IN));
+ COMMA)),
+ CLOSE_PAREN);
}
/**
@@ -418,9 +434,9 @@
*/
public String getLike(String left, String right)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, LIKE));
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(left, LIKE, right),
+ CLOSE_PAREN);
}
/**
@@ -432,9 +448,9 @@
*/
public String getAnd(String left, String right)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, AND));
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(left, AND, right),
+ CLOSE_PAREN);
}
/**
@@ -446,9 +462,9 @@
*/
public String getOr(String left, String right)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(left, right, OR));
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(left, OR, right),
+ CLOSE_PAREN);
}
/**
@@ -459,9 +475,9 @@
*/
public String getNot(String right)
{
- return leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- leftRightMiddle(EMPTY, right, NOT));
+ return leftMiddleRight(OPEN_PAREN,
+ leftMiddleRight(EMPTY, NOT, right),
+ CLOSE_PAREN);
}
/**
@@ -472,7 +488,7 @@
*/
public String getAscending(String left)
{
- return leftRightMiddle(left, EMPTY, ASC);
+ return leftMiddleRight(left, ASC, EMPTY);
}
/**
@@ -482,8 +498,88 @@
* @return <em>left</em> DESC
*/
public String getDescending(String left)
+ {
+ return leftMiddleRight(left, DESC, EMPTY);
+ }
+
+ /**
+ * Constructs an insert fragment.
+ *
+ * @param left String the table to insert values into
+ * @param list List the list of column names
+ * @return INSERT INTO <em>left</em> (<em>item1</em>, <em>item2</em>, ...,
<em>itemN</em>)
+ */
+ public String getInsert(String left, List list)
+ {
+ return leftMiddleRight(leftMiddleRight(INSERT,
+ left,
+ SPACE),
+ leftRightListConnector(OPEN_PAREN,
+ CLOSE_PAREN,
+ list,
+ COMMA),
+ EMPTY);
+ }
+
+ /**
+ * Constructs a values fragment.
+ *
+ * @param list List the list of values
+ * @return VALUES (<em>item1</em>, <em>item2</em>, ..., <em>itemN</em>)
+ */
+ public String getValues(List list)
+ {
+ return leftMiddleRight(VALUES,
+ leftRightListConnector(OPEN_PAREN,
+ CLOSE_PAREN,
+ list,
+ COMMA),
+ EMPTY);
+ }
+
+ /**
+ * Constructs an update fragment.
+ *
+ * @param item String the table to be updated
+ * @param list List a list of set statements (see below)
+ * @return UPDATE left <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
+ * where <em>item</em> is a set fragment (see below)
+ */
+ public String getUpdate(String item, List list)
+ {
+ return leftMiddleRight(UPDATE,
+ item,
+ leftRightListConnector(SPACE,
+ EMPTY,
+ list,
+ COMMA));
+ }
+
+ /**
+ * Constructs a set statement.
+ *
+ * @param left String the column to be set
+ * @param right String the value to be assigned
+ * @return SET <em>middle</em>=<em>value</em>
+ */
+ public String getSet(String left, String right)
+ {
+ return leftMiddleRight(SET,
+ leftMiddleRight(left,
+ EQUALS,
+ right),
+ EMPTY);
+ }
+
+ /**
+ * Constructs a delete fragment.
+ *
+ * @param left String the table from which rows will be deleted
+ * @return DELETE FROM <em>left</em>
+ */
+ public String getDelete(String left)
{
- return leftRightMiddle(left, EMPTY, DESC);
+ return leftMiddleRight(DELETE, left, EMPTY);
}
/**
@@ -518,7 +614,7 @@
*/
public String getWhere(String middle)
{
- return leftRightMiddle(WHERE, EMPTY, middle);
+ return leftMiddleRight(WHERE, middle, EMPTY);
}
/**
@@ -531,7 +627,7 @@
*/
public String getHaving(String middle)
{
- return leftRightMiddle(HAVING, EMPTY, middle);
+ return leftMiddleRight(HAVING, middle, EMPTY);
}
/**
@@ -560,90 +656,90 @@
* Constructs a count function.
*
* @param middle String the column to be counted
- * @return COUNT(<em>middle</em)
+ * @return COUNT(<em>middle</em>)
*/
public String getCount(String middle)
{
- return leftRightMiddle(COUNT,
- EMPTY,
- leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- middle));
+ return leftMiddleRight(COUNT,
+ leftMiddleRight(OPEN_PAREN,
+ middle,
+ CLOSE_PAREN),
+ EMPTY);
}
/**
* Constructs a min function.
*
* @param middle String the column to be searched for its minimum value
- * @return MIN(<em>middle</em)
+ * @return MIN(<em>middle</em>)
*/
public String getMin(String middle)
{
- return leftRightMiddle(MIN,
- EMPTY,
- leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- middle));
+ return leftMiddleRight(MIN,
+ leftMiddleRight(OPEN_PAREN,
+ middle,
+ CLOSE_PAREN),
+ EMPTY);
}
/**
* Constructs a max function.
*
* @param middle String the column to be searched for its maximum value
- * @return MAX(<em>middle</em)
+ * @return MAX(<em>middle</em>)
*/
public String getMax(String middle)
{
- return leftRightMiddle(MAX,
- EMPTY,
- leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- middle));
+ return leftMiddleRight(MAX,
+ leftMiddleRight(OPEN_PAREN,
+ middle,
+ CLOSE_PAREN),
+ EMPTY);
}
/**
* Constructs a avg function.
*
* @param middle String the column to be averaged
- * @return AVG(<em>middle</em)
+ * @return AVG(<em>middle</em>)
*/
public String getAvg(String middle)
{
- return leftRightMiddle(AVG,
- EMPTY,
- leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- middle));
+ return leftMiddleRight(AVG,
+ leftMiddleRight(OPEN_PAREN,
+ middle,
+ CLOSE_PAREN),
+ EMPTY);
}
/**
* Constructs a sum function.
*
* @param middle String the column to be summed
- * @return SUM(<em>middle</em)
+ * @return SUM(<em>middle</em>)
*/
public String getSum(String middle)
{
- return leftRightMiddle(SUM,
- EMPTY,
- leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- middle));
+ return leftMiddleRight(SUM,
+ leftMiddleRight(OPEN_PAREN,
+ middle,
+ CLOSE_PAREN),
+ EMPTY);
}
/**
* Constructs an upper function.
*
* @param middle String the column to be averaged
- * @return UPPER(<em>middle</em)
+ * @return UPPER(<em>middle</em>)
*/
public String getUpper(String middle)
{
- return leftRightMiddle(UPPER,
- EMPTY,
- leftRightMiddle(OPEN_PAREN,
- CLOSE_PAREN,
- middle));
+ return leftMiddleRight(UPPER,
+ leftMiddleRight(OPEN_PAREN,
+ middle,
+ CLOSE_PAREN),
+ EMPTY);
}
/**
1.2 +67 -16 jakarta-turbine/proposals/eric/statement/BaseSqlTest.java
Index: BaseSqlTest.java
===================================================================
RCS file: /home/cvs/jakarta-turbine/proposals/eric/statement/BaseSqlTest.java,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- BaseSqlTest.java 2001/06/22 01:48:14 1.1
+++ BaseSqlTest.java 2001/06/24 14:38:24 1.2
@@ -91,11 +91,11 @@
return new TestSuite(BaseSqlTest.class);
}
- public void testLeftRightMiddle()
+ public void testLeftMiddleRight()
{
- String result = s.leftRightMiddle("set (",
- ")",
- "column=30");
+ String result = s.leftMiddleRight("set (",
+ "column=30",
+ ")");
String expect = "set (column=30)";
assert(result.equals(expect));
}
@@ -218,7 +218,7 @@
public void testGetNot()
{
String result = s.getNot("this");
- String expect = "(NOT this)";
+ String expect = "( NOT this)";
assert(result.equals(expect));
}
@@ -236,21 +236,53 @@
assert(result.equals(expect));
}
- public void testNestedComparison()
+ public void testGetInsert()
{
- 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)))";
+ ArrayList list = new ArrayList();
+ list.add("column1");
+ list.add("column2");
+ list.add("column3");
+ String result = s.getInsert("table",list);
+ String expect = "INSERT INTO table (column1, column2, column3)";
assert(result.equals(expect));
}
+ public void testGetValues()
+ {
+ ArrayList list = new ArrayList();
+ list.add(s.quoteAndEscapeText("value1"));
+ list.add(s.quoteAndEscapeText("value2"));
+ list.add(s.quoteAndEscapeText("value3"));
+ String result = s.getValues(list);
+ String expect = " VALUES ('value1', 'value2', 'value3')";
+ assert(result.equals(expect));
+ }
+
+
+ public void testGetUpdate()
+ {
+ ArrayList list = new ArrayList();
+ list.add(s.getSet("column1","value1"));
+ list.add(s.getSet("column2","value2"));
+ String result = s.getUpdate("table",list);
+ String expect = "UPDATE table SET column1=value1, SET column2=value2";
+ assert(result.equals(expect));
+ }
+
+ public void testGetSet()
+ {
+ String result = s.getSet("column","value");
+ String expect = "SET column=value";
+ assert(result.equals(expect));
+ }
+
+ public void testGetDelete()
+ {
+ String result = s.getDelete("table");
+ String expect = "DELETE FROM table";
+ assert(result.equals(expect));
+ }
+
public void testGetSelect()
{
ArrayList list = new ArrayList(3);
@@ -349,6 +381,10 @@
{
String result = s.getUpper("table.column");
String expect = "UPPER(table.column)";
+
+ System.out.println("expect: " + expect);
+ System.out.println("result: " + result);
+
assert(result.equals(expect));
}
@@ -356,6 +392,21 @@
{
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 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));
}
1.5 +49 -0 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.4
retrieving revision 1.5
diff -u -r1.4 -r1.5
--- Sql.java 2001/06/22 01:48:14 1.4
+++ Sql.java 2001/06/24 14:38:24 1.5
@@ -67,6 +67,11 @@
* </p>
*
* <pre>
+ * getInsert --> insert into <em>left</em>
(<em>item1</em>,...,<em>itemN</em>)
+ * getValues --> values (<em>item1</em>,...,<em>itemN</em>)
+ * getUpdate --> update <em>left</em> <em>item1</em>,...,<em>itemN</em>
+ * getSet --> set <em>left</em>=<em>right</em>
+ * getDelete --> delete from <em>left</em>
* 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>
@@ -238,6 +243,50 @@
* @return <em>left</em> DESC
*/
public String getDescending(String left);
+
+ /**
+ * Constructs an insert fragment.
+ *
+ * @param left String the table to insert values into
+ * @param list List the list of column names
+ * @return INSERT INTO <em>left</em> (<em>item1</em>, <em>item2</em>, ...,
<em>itemN</em>)
+ */
+ public String getInsert(String left, List list);
+
+ /**
+ * Constructs a values fragment.
+ *
+ * @param list List the list of values
+ * @return VALUES (<em>item1</em>, <em>item2</em>, ..., <em>itemN</em>)
+ */
+ public String getValues(List list);
+
+ /**
+ * Constructs an update fragment.
+ *
+ * @param item String the table to be updated
+ * @param list List a list of set statements (see below)
+ * @return UPDATE left <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
+ * where <em>item</em> is a set fragment (see below)
+ */
+ public String getUpdate(String item, List list);
+
+ /**
+ * Constructs a set statement.
+ *
+ * @param left String the column to be set
+ * @param right String the value to be assigned
+ * @return SET <em>middle</em>=<em>value</em>
+ */
+ public String getSet(String left, String right);
+
+ /**
+ * Constructs a delete fragment.
+ *
+ * @param left String the table from which rows will be deleted
+ * @return DELETE FROM <em>left</em>
+ */
+ public String getDelete(String left);
/**
* Constructs a select fragment.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
