dobbs 01/06/20 17:31:52
Modified: proposals/eric/statement Sql.java SqlTest.java
Log:
* cleaned up javadocs
* consolidated a couple of the workhorse methods
* renamed twoArgsListAndConnector to leftRightListConnector
* added several more methods: getIsNull(), getIsNotNull(),
getIn(), getNotIn(), getOrderBy(),getGroupBy(),getAscending(),
getDescending(), getHaving()
This is a pretty complete initial pass. Next steps:
Move current implementation into BaseSql.java
Change Sql.java to an interface
Create SqlFactory
Create at least one database adaptor extending BaseSql.java
Revision Changes Path
1.3 +258 -102 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.2
retrieving revision 1.3
diff -u -r1.2 -r1.3
--- Sql.java 2001/06/18 04:35:32 1.2
+++ Sql.java 2001/06/21 00:31:50 1.3
@@ -60,31 +60,42 @@
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>
*
- * Select --> select <item1>,<item2>,...,<itemN>
- * From --> from <item1>,<item2>,...,<itemN>
- * Where --> where <object>
- * Equal --> (<item1> = <item2>)
- * NotEqual --> (<item1> != <item2>)
- * GreaterThan --> (<item1> > <item2>)
- * GreaterEqual --> (<item1> >= <item2>)
- * LessThan --> (<item1> < <item2>)
- * LessEqual --> (<item1> <= <item2>)
- * Null --> (<object> is null)
- * NotNull --> (<object> is not null)
- * In --> (<item1> in <item2>)
- * NotIn --> (<item1> not in <item2>)
- * And --> (<item1> and <item2>)
- * Or --> (<item1> or <item2>)
- * Not --> (not <object>)
- * Ascending --> <object> ASC
- * Descending --> <object> DESC
- * OrderBy --> order by <item1>,<item2>,...,<itemN>
- * GroupBy --> group by <item1>,<item2>,...,<itemN>
- * */
+ * <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>
+ */
public class Sql
{
public static String EMPTY = "";
@@ -94,8 +105,8 @@
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 ASC = " ASC";
+ public static String DESC = " DESC";
public static String OPEN_PAREN = "(";
public static String CLOSE_PAREN = ")";
public static String EQUALS = "=";
@@ -104,9 +115,14 @@
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 = ", ";
/**
@@ -114,66 +130,51 @@
* String of the form [left][middle][right].
*
* this is useful for at least the following SQL fragments:
- * where (foo_id=25)
- * set (foo_id=25)
- * table.column asc
- * upper (table.column)
- * count(*)
+ * <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 Object the thing in the middle
+ * @param middle String the thing in the middle
*/
protected String leftRightMiddle(String left,
String right,
- Object middle)
+ String middle)
{
- String middleString = middle.toString();
StringBuffer sb = new StringBuffer(
left.length()
+right.length()
- +middleString.length());
+ +middle.length());
sb.append(left)
- .append(middleString)
+ .append(middle)
.append(right);
return sb.toString();
}
/**
* the workhorse used by several other methods to construct a
- * String of the form ([left][connector][right]).
- *
- * this is useful for at least the following SQL fragments:
- * (table.column=15)
- * (uppercase(table.column) like 'FOO%')
- * ((table.column>10) AND (table.column<=20))
- * set table.column=25
- *
- * @param left Object the left side of the comparison
- * @param right Object the right side of the comparison
- * @param connector String the comparison operator */
- protected String twoArgsAndConnector(Object left,
- Object right,
- String connector)
- {
- String leftString = left.toString();
- String rightString = right.toString();
- StringBuffer sb = new StringBuffer(
- OPEN_PAREN.length()
- +leftString.length()
- +connector.length()
- +rightString.length()
- +CLOSE_PAREN.length());
- sb.append(OPEN_PAREN)
- .append(leftString)
- .append(connector)
- .append(rightString)
- .append(CLOSE_PAREN);
- return sb.toString();
- }
-
- /**
- * the workhorse used by several other methods to construct a
* String of the form
* [left][item1][connector][item2][connector]...[itemN][right].
*
@@ -191,7 +192,7 @@
* @param list List a list containing the items
* @param connector String the list delimiter
*/
- protected String twoArgsListAndConnector(String left,
+ protected String leftRightListConnector(String left,
String right,
List list,
String connector)
@@ -224,144 +225,299 @@
/**
* constructs a logical comparison using the equals operator:
- * ([left]=[right])
+ * (<em>left</em>=<em>right</em>)
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
*/
- public String getEquals(Object left, Object right)
+ public String getEquals(String left, String right)
{
- return twoArgsAndConnector(left, right, EQUALS);
+ return leftRightMiddle(OPEN_PAREN,
+ CLOSE_PAREN,
+ leftRightMiddle(left, right, EQUALS));
}
/**
* constructs a logical comparison using the not equals operator:
- * ([left]!=[right])
+ * (<em>left</em>!=<em>right</em>)
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
*/
- public String getNotEquals(Object left, Object right)
+ public String getNotEquals(String left, String right)
{
- return twoArgsAndConnector(left, right, NOT_EQUALS);
+ return leftRightMiddle(OPEN_PAREN,
+ CLOSE_PAREN,
+ leftRightMiddle(left, right, NOT_EQUALS));
}
/**
* constructs a logical comparison using the less than operator:
- * ([left]<[right])
+ * (<em>left</em><<em>right</em>)
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
*/
- public String getLessThan(Object left, Object right)
+ public String getLessThan(String left, String right)
{
- return twoArgsAndConnector(left, right, LESS_THAN);
+ return leftRightMiddle(OPEN_PAREN,
+ CLOSE_PAREN,
+ leftRightMiddle(left, right, LESS_THAN));
}
/**
* constructs a logical comparison using the greater than operator:
- * ([left]>[right])
+ * (<em>left</em>><em>right</em>)
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
*/
- public String getGreaterThan(Object left, Object right)
+ public String getGreaterThan(String left, String right)
{
- return twoArgsAndConnector(left, right, GREATER_THAN);
+ return leftRightMiddle(OPEN_PAREN,
+ CLOSE_PAREN,
+ leftRightMiddle(left, right, GREATER_THAN));
}
/**
* constructs a logical comparison using the less than or equal to
- * operator: ([left]<=[right])
+ * 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
*/
- public String getLessEqual(Object left, Object right)
+ public String getLessEqual(String left, String right)
{
- return twoArgsAndConnector(left, right, LESS_EQUAL);
+ return leftRightMiddle(OPEN_PAREN,
+ CLOSE_PAREN,
+ leftRightMiddle(left, right, LESS_EQUAL));
}
/**
* constructs a logical comparison using the greater than or equal
- * to operator: ([left]>=[right])
+ * to 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
*/
- public String getGreaterEqual(Object left, Object right)
+ public String getGreaterEqual(String left, String right)
{
- return twoArgsAndConnector(left, right, GREATER_EQUAL);
+ return leftRightMiddle(OPEN_PAREN,
+ CLOSE_PAREN,
+ leftRightMiddle(left, right, GREATER_EQUAL));
}
/**
+ * constructs an is null fragment:
+ * (<em>left</em> IS NULL)
+ *
+ * @param left String the left side of the operator
+ */
+ public String getIsNull (String left)
+ {
+ return leftRightMiddle(OPEN_PAREN,
+ CLOSE_PAREN,
+ leftRightMiddle(left, EMPTY, IS_NULL));
+ }
+
+ /**
+ * constructs an is not null fragment:
+ * (<em>left</em> IS NOT NULL)
+ *
+ * @param left String the left side of the operator
+ */
+ public String getIsNotNull (String left)
+ {
+ return leftRightMiddle(OPEN_PAREN,
+ CLOSE_PAREN,
+ leftRightMiddle(left, EMPTY, IS_NOT_NULL));
+ }
+
+ /**
+ * constructs an in fragment:
+ * (<em>left</em> IN (<em>item1</em>,...))
+ *
+ * @param left String the left side of the operator
+ * @param list List the list of items on the right side of the operator
+ */
+ 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:
+ * (<em>left</em> NOT IN (<em>item1</em>,...))
+ *
+ * @param left String the left side of the operator
+ * @param list List the list of items on the right side of the operator
+ */
+ 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:
- * ([left] LIKE [right])
+ * (<em>left</em> LIKE <em>right</em>)
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
*/
- public String getLike(Object left, Object right)
+ public String getLike(String left, String right)
{
- return twoArgsAndConnector(left, right, LIKE);
+ return leftRightMiddle(OPEN_PAREN,
+ CLOSE_PAREN,
+ leftRightMiddle(left, right, LIKE));
}
/**
* constructs a logical comparison using the and operator:
- * ([left] AND [right])
+ * (<em>left</em> AND <em>right</em>)
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
*/
- public String getAnd(Object left, Object right)
+ public String getAnd(String left, String right)
{
- return twoArgsAndConnector(left, right, AND);
+ return leftRightMiddle(OPEN_PAREN,
+ CLOSE_PAREN,
+ leftRightMiddle(left, right, AND));
}
/**
* constructs a logical comparison using the or operator:
- * ([left] OR [right])
+ * (<em>left</em> OR <em>right</em>)
*
* @param left String the left side of the comparison
* @param right String the right side of the comparison
*/
- public String getOr(Object left, Object right)
+ public String getOr(String left, String right)
{
- return twoArgsAndConnector(left, right, OR);
+ return leftRightMiddle(OPEN_PAREN,
+ CLOSE_PAREN,
+ leftRightMiddle(left, right, OR));
}
/**
+ * constructs a logical comparison using the not operator:
+ * (NOT <em>right</em>)
+ *
+ * @param right String the right side of the comparison
+ */
+ public String getNot(String right)
+ {
+ return leftRightMiddle(OPEN_PAREN,
+ CLOSE_PAREN,
+ leftRightMiddle(EMPTY, right, NOT));
+ }
+
+ /**
+ * constructs an ASC fragment:
+ * <em>left</em> ASC
+ *
+ * @param right String the right side of the comparison
+ */
+ public String getAscending(String left)
+ {
+ return leftRightMiddle(left, EMPTY, ASC);
+ }
+
+ /**
+ * constructs an DESC fragment:
+ * <em>left</em> DESC
+ *
+ * @param right String the right side of the comparison
+ */
+ public String getDescending(String left)
+ {
+ return leftRightMiddle(left, EMPTY, DESC);
+ }
+
+ /**
* constructs a select fragment:
- * SELECT [item1], [item2], ..., [itemN]
+ * SELECT <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
*
* @param list List the list of items
*/
public String getSelect(List list)
{
- return twoArgsListAndConnector(SELECT, EMPTY, list, COMMA);
+ return leftRightListConnector(SELECT, EMPTY, list, COMMA);
}
/**
* constructs a from fragment:
- * FROM [item1], [item2], ..., [itemN]
+ * FROM <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
*
* @param list List the list of items
*/
public String getFrom(List list)
{
- return twoArgsListAndConnector(FROM, EMPTY, list, COMMA);
+ return leftRightListConnector(FROM, EMPTY, list, COMMA);
}
/**
* constructs a where fragment:
- * WHERE [middle]
+ * WHERE <em>tree</em>
*
- * @param middle Object comparisons for the where clause
- * @see getAnd(), getOr(), getEqual(), getLessThan(), etc
+ * @param tree String comparisons for the where clause.
+ * see getAnd(), getOr(), getEqual(), getLessThan(), etc
* for methods to help construct these comparisons
*/
- public String getWhere(Object middle)
+ public String getWhere(String middle)
{
return leftRightMiddle(WHERE, EMPTY, middle);
}
+
+ /**
+ * constructs a having fragment:
+ * HAVING <em>tree</em>
+ *
+ * @param tree String comparisons for the where clause.
+ * see getAnd(), getOr(), getEqual(), getLessThan(), etc
+ * for methods to help construct these comparisons
+ */
+ public String getHaving(String middle)
+ {
+ return leftRightMiddle(HAVING, EMPTY, middle);
+ }
+
+ /**
+ * constructs an order by fragment:
+ * ORDER BY <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
+ *
+ * @param list List the list of items
+ */
+ public String getOrderBy(List list)
+ {
+ return leftRightListConnector(ORDER_BY, EMPTY, list, COMMA);
+ }
+
+ /**
+ * constructs a group by fragment:
+ * GROUP BY <em>item1</em>, <em>item2</em>, ..., <em>itemN</em>
+ *
+ * @param list List the list of items
+ */
+ public String getGroupBy(List list)
+ {
+ return leftRightListConnector(GROUP_BY, EMPTY, list, COMMA);
+ }
+
}
1.3 +84 -18 jakarta-turbine/proposals/eric/statement/SqlTest.java
Index: SqlTest.java
===================================================================
RCS file: /home/cvs/jakarta-turbine/proposals/eric/statement/SqlTest.java,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -r1.2 -r1.3
--- SqlTest.java 2001/06/18 04:35:33 1.2
+++ SqlTest.java 2001/06/21 00:31:50 1.3
@@ -100,25 +100,16 @@
assert(result.equals(expected));
}
- public void testTwoArgsAndConnector()
+ public void testLeftRightListConnector()
{
- String result = s.twoArgsAndConnector("table.column",
- "25",
- "<");
- String expected = "(table.column<25)";
- assert(result.equals(expected));
- }
-
- public void testTwoArgsListAndConnector()
- {
ArrayList list = new ArrayList(3);
list.add("columnA");
list.add("columnB");
list.add("columnC");
- String result = s.twoArgsListAndConnector("select ",
- "",
- list,
- ", ");
+ String result = s.leftRightListConnector("select ",
+ "",
+ list,
+ ", ");
String expected = "select columnA, columnB, columnC";
assert(result.equals(expected));
}
@@ -171,6 +162,35 @@
assert(result.equals(expected));
}
+ public void testGetIsNull()
+ {
+ String result = s.getIsNull("this");
+ String expected = "(this IS NULL)";
+ assert(result.equals(expected));
+ }
+
+ public void testGetIn()
+ {
+ ArrayList list = new ArrayList();
+ list.add("'foo'");
+ list.add("'bar'");
+ list.add("'baz'");
+ String result = s.getIn("this",list);
+ String expected = "(this IN ('foo', 'bar', 'baz'))";
+ assert(result.equals(expected));
+ }
+
+ public void testGetNotIn()
+ {
+ ArrayList list = new ArrayList();
+ list.add("'foo'");
+ list.add("'bar'");
+ list.add("'baz'");
+ String result = s.getNotIn("this",list);
+ String expected = "(this NOT IN ('foo', 'bar', 'baz'))";
+ assert(result.equals(expected));
+ }
+
public void testGetLike()
{
String result = s.getLike("this",
@@ -195,6 +215,27 @@
assert(result.equals(expected));
}
+ public void testGetNot()
+ {
+ String result = s.getNot("this");
+ String expected = "(NOT this)";
+ assert(result.equals(expected));
+ }
+
+ public void testGetAscending()
+ {
+ String result = s.getAscending("this");
+ String expected = "this ASC";
+ assert(result.equals(expected));
+ }
+
+ public void testGetDescending()
+ {
+ String result = s.getDescending("this");
+ String expected = "this DESC";
+ assert(result.equals(expected));
+ }
+
public void testNestedComparison()
{
String result = s.getOr(
@@ -218,10 +259,6 @@
list.add("columnC");
String result = s.getSelect(list);
String expected = "SELECT columnA, columnB, columnC";
-
- System.out.println("expect: " + expected);
- System.out.println("result: " + result);
-
assert(result.equals(expected));
}
@@ -241,10 +278,39 @@
{
String result = s.getWhere("(column like '%foo%')");
String expected = " WHERE (column like '%foo%')";
+ assert(result.equals(expected));
+ }
+
+ 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 expected = " ORDER BY column1 ASC, column2 DESC, column3";
+ assert(result.equals(expected));
+ }
+ 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 expected = " GROUP BY column1 ASC, column2 DESC, column3";
+
System.out.println("expect: " + expected);
System.out.println("result: " + result);
+ assert(result.equals(expected));
+ }
+
+ public void testGetHaving()
+ {
+ String result = s.getHaving("(sum(column)>100)");
+ String expected = " HAVING (sum(column)>100)";
assert(result.equals(expected));
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
