Added methods to generate static, reusable, parameterized sql statements

9 files changed, 362 insertions, 6 deletions

diff --git a/src/com/iciql/Query.java b/src/com/iciql/Query.java
index 37987ea..a8aa8b2 100644
--- a/src/com/iciql/Query.java
+++ b/src/com/iciql/Query.java
@@ -115,6 +115,35 @@ public class Query<T> {
 		return stat.getSQL().trim();

 	}

 

+	/**

+	 * toSQL returns a static string version of the query with runtime variables

+	 * properly encoded. This method is also useful when combined with the where

+	 * clause methods like isParameter() or atLeastParameter() which allows

+	 * iciql to generate re-usable parameterized string statements.

+	 * 

+	 * @return the sql query as plain text

+	 */

+	public String toSQL() {

+		return toSQL(false);

+	}

+	

+	/**

+	 * toSQL returns a static string version of the query with runtime variables

+	 * properly encoded. This method is also useful when combined with the where

+	 * clause methods like isParameter() or atLeastParameter() which allows

+	 * iciql to generate re-usable parameterized string statements.

+	 * 

+	 * @param distinct

+	 *            if true SELECT DISTINCT is used for the query

+	 * @return the sql query as plain text

+	 */	

+	public String toSQL(boolean distinct) {

+		SQLStatement stat = getSelectStatement(distinct);

+		stat.appendSQL("*");

+		appendFromWhere(stat);

+		return stat.toSQL().trim();

+	}

+

 	private List<T> select(boolean distinct) {

 		List<T> result = Utils.newArrayList();

 		TableDefinition<T> def = from.getAliasDefinition();

@@ -607,10 +636,15 @@ public class Query<T> {
 	 *            the value

 	 */

 	public void appendSQL(SQLStatement stat, Object alias, Object value) {

-		if (value == Function.count()) {

+		if (Function.count() == value) {

 			stat.appendSQL("COUNT(*)");

 			return;

 		}

+		if (RuntimeParameter.PARAMETER == value) {

+			stat.appendSQL("?");

+			addParameter(stat, alias, value);

+			return;

+		}

 		Token token = Db.getToken(value);

 		if (token != null) {

 			token.appendSQL(stat, this);

diff --git a/src/com/iciql/QueryCondition.java b/src/com/iciql/QueryCondition.java
index 583f26d..2595531 100644
--- a/src/com/iciql/QueryCondition.java
+++ b/src/com/iciql/QueryCondition.java
@@ -85,4 +85,39 @@ public class QueryCondition<T, A> {
 		return new QueryWhere<T>(query);
 	}
 
+	/*
+	 * These method allows you to generate "x=?", "x!=?", etc where conditions.
+	 * Parameter substitution must be done manually later with db.executeQuery.
+	 * This allows for building re-usable SQL string statements from your model
+	 * classes.
+	 */
+	public QueryWhere<T> isParameter() {
+		query.addConditionToken(new RuntimeParameter<A>(x, CompareType.EQUAL));
+		return new QueryWhere<T>(query);
+	}
+
+	public QueryWhere<T> isNotParameter() {
+		query.addConditionToken(new RuntimeParameter<A>(x, CompareType.NOT_EQUAL));
+		return new QueryWhere<T>(query);
+	}
+
+	public QueryWhere<T> exceedsParameter() {
+		query.addConditionToken(new RuntimeParameter<A>(x, CompareType.EXCEEDS));
+		return new QueryWhere<T>(query);
+	}
+
+	public QueryWhere<T> lessThanParameter() {
+		query.addConditionToken(new RuntimeParameter<A>(x, CompareType.LESS_THAN));
+		return new QueryWhere<T>(query);
+	}
+
+	public QueryWhere<T> atMostParameter() {
+		query.addConditionToken(new RuntimeParameter<A>(x, CompareType.AT_MOST));
+		return new QueryWhere<T>(query);
+	}
+
+	public QueryWhere<T> likeParameter() {
+		query.addConditionToken(new RuntimeParameter<A>(x, CompareType.LIKE));
+		return new QueryWhere<T>(query);
+	}
 }
diff --git a/src/com/iciql/QueryWhere.java b/src/com/iciql/QueryWhere.java
index df93439..0a07c40 100644
--- a/src/com/iciql/QueryWhere.java
+++ b/src/com/iciql/QueryWhere.java
@@ -112,7 +112,7 @@ public class QueryWhere<T> {
 		return addPrimitive(ConditionAndOr.AND, x);

 	}

 

-	private <A> QueryCondition<T, A> addPrimitive(ConditionAndOr condition, A x) {		

+	private <A> QueryCondition<T, A> addPrimitive(ConditionAndOr condition, A x) {

 		query.addConditionToken(condition);

 		A alias = query.getPrimitiveAliasByValue(x);

 		if (alias == null) {

@@ -234,10 +234,6 @@ public class QueryWhere<T> {
 		return this;

 	}

 

-	public <X, Z> List<X> select(Z x) {

-		return query.select(x);

-	}

-

 	public String getSQL() {

 		SQLStatement stat = new SQLStatement(query.getDb());

 		stat.appendSQL("SELECT *");

@@ -245,6 +241,43 @@ public class QueryWhere<T> {
 		return stat.getSQL().trim();

 	}

 

+	/**

+	 * toSQL returns a static string version of the query with runtime variables

+	 * properly encoded. This method is also useful when combined with the where

+	 * clause methods like isParameter() or atLeastParameter() which allows

+	 * iciql to generate re-usable parameterized string statements.

+	 * 

+	 * @return the sql query as plain text

+	 */

+	public String toSQL() {

+		return this.toSQL(false);

+	}

+

+	/**

+	 * toSQL returns a static string version of the query with runtime variables

+	 * properly encoded. This method is also useful when combined with the where

+	 * clause methods like isParameter() or atLeastParameter() which allows

+	 * iciql to generate re-usable parameterized string statements.

+	 * 

+	 * @param distinct

+	 *            if true SELECT DISTINCT is used for the query

+	 * @return the sql query as plain text

+	 */

+	public String toSQL(boolean distinct) {

+		SQLStatement stat = new SQLStatement(query.getDb());

+		if (distinct) {

+			stat.appendSQL("SELECT DISTINCT *");

+		} else {

+			stat.appendSQL("SELECT *");

+		}

+		query.appendFromWhere(stat);

+		return stat.toSQL().trim();

+	}

+

+	public <X, Z> List<X> select(Z x) {

+		return query.select(x);

+	}

+

 	public <X, Z> List<X> selectDistinct(Z x) {

 		return query.selectDistinct(x);

 	}

diff --git a/src/com/iciql/RuntimeParameter.java b/src/com/iciql/RuntimeParameter.java
new file mode 100644
index 0000000..0fbedba
--- /dev/null
+++ b/src/com/iciql/RuntimeParameter.java
@@ -0,0 +1,49 @@
+/*

+ * Copyright 2012 James Moger.

+ *

+ * Licensed under the Apache License, Version 2.0 (the "License");

+ * you may not use this file except in compliance with the License.

+ * You may obtain a copy of the License at

+ *

+ *     http://www.apache.org/licenses/LICENSE-2.0

+ *

+ * Unless required by applicable law or agreed to in writing, software

+ * distributed under the License is distributed on an "AS IS" BASIS,

+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+ * See the License for the specific language governing permissions and

+ * limitations under the License.

+ */

+

+package com.iciql;

+

+/**

+ * A runtime parameter is used to generate x=? conditions so that iciql can

+ * build re-usable dynamic queries with parameter substitution done manually at

+ * runtime.

+ * 

+ * @param <A>

+ *            the operand type

+ */

+

+class RuntimeParameter<A> implements Token {

+	

+	public final static String PARAMETER = "";

+	

+	A x;

+	CompareType compareType;

+

+	RuntimeParameter(A x, CompareType type) {

+		this.x = x;

+		this.compareType = type;

+	}

+

+	public <T> void appendSQL(SQLStatement stat, Query<T> query) {

+		query.appendSQL(stat, null, x);

+		stat.appendSQL(" ");

+		stat.appendSQL(compareType.getString());

+		if (compareType.hasRightExpression()) {

+			stat.appendSQL(" ");

+			query.appendSQL(stat, x, PARAMETER);

+		}

+	}

+}

diff --git a/src/com/iciql/SQLDialect.java b/src/com/iciql/SQLDialect.java
index 7c29d61..28f5566 100644
--- a/src/com/iciql/SQLDialect.java
+++ b/src/com/iciql/SQLDialect.java
@@ -125,4 +125,13 @@ public interface SQLDialect {
 	 * @return preferred DATETIME class
 	 */
 	Class<? extends java.util.Date> getDateTimeClass();
+
+	/**
+	 * When building static string statements this method flattens an object to
+	 * a string representation suitable for a static string statement.
+	 * 
+	 * @param o
+	 * @return the string equivalent of this object
+	 */
+	String prepareParameter(Object o);
 }
diff --git a/src/com/iciql/SQLDialectDefault.java b/src/com/iciql/SQLDialectDefault.java
index 193079f..617bffb 100644
--- a/src/com/iciql/SQLDialectDefault.java
+++ b/src/com/iciql/SQLDialectDefault.java
@@ -20,6 +20,7 @@ package com.iciql;
 import java.sql.DatabaseMetaData;

 import java.sql.SQLException;

 import java.text.MessageFormat;

+import java.text.SimpleDateFormat;

 

 import com.iciql.TableDefinition.FieldDefinition;

 import com.iciql.TableDefinition.IndexDefinition;

@@ -31,6 +32,9 @@ import com.iciql.util.StringUtils;
  * Default implementation of an SQL dialect.

  */

 public class SQLDialectDefault implements SQLDialect {

+	

+	final String LITERAL = "'";

+

 	float databaseVersion;

 	String databaseName;

 	String productVersion;

@@ -266,4 +270,20 @@ public class SQLDialectDefault implements SQLDialect {
 			stat.appendSQL(" OFFSET " + offset);

 		}

 	}

+	

+	@Override

+	public String prepareParameter(Object o) {

+		if (o instanceof String) {

+			return LITERAL + o.toString().replace(LITERAL, "''") + LITERAL;

+		} else if (o instanceof Character) {

+			return LITERAL + o.toString() + LITERAL;

+		} else if (o instanceof java.sql.Time) {

+			return LITERAL + new SimpleDateFormat("HH:mm:ss").format(o) + LITERAL;

+		} else if (o instanceof java.sql.Date) {

+			return LITERAL + new SimpleDateFormat("yyyy-MM-dd").format(o) + LITERAL;

+		} else if (o instanceof java.util.Date) {

+			return LITERAL + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(o) + LITERAL;

+		}

+		return o.toString();

+	}

 }
\ No newline at end of file
diff --git a/src/com/iciql/SQLStatement.java b/src/com/iciql/SQLStatement.java
index a33fe6c..69e26ed 100644
--- a/src/com/iciql/SQLStatement.java
+++ b/src/com/iciql/SQLStatement.java
@@ -21,6 +21,7 @@ import java.sql.PreparedStatement;
 import java.sql.ResultSet;

 import java.sql.SQLException;

 import java.util.ArrayList;

+import java.util.StringTokenizer;

 

 import com.iciql.util.JdbcUtils;

 

@@ -57,6 +58,12 @@ public class SQLStatement {
 		return appendSQL(db.getDialect().prepareColumnName(column));

 	}

 

+	/**

+	 * getSQL returns a simple string representation of the parameterized

+	 * statement which will be used later, internally, with prepareStatement.

+	 * 

+	 * @return a simple sql statement

+	 */

 	String getSQL() {

 		if (sql == null) {

 			sql = buff.toString();

@@ -64,6 +71,40 @@ public class SQLStatement {
 		return sql;

 	}

 

+	/**

+	 * toSQL creates a static sql statement with the referenced parameters

+	 * encoded in the statement.

+	 * 

+	 * @return a complete sql statement

+	 */

+	String toSQL() {

+		if (sql == null) {

+			sql = buff.toString();

+		}

+		if (params.size() == 0) {

+			return sql;

+		}

+		StringBuilder sb = new StringBuilder();

+		// TODO this needs to me more sophisticated

+		StringTokenizer st = new StringTokenizer(sql, "?", false);

+		int i = 0;

+		while (st.hasMoreTokens()) {

+			sb.append(st.nextToken());

+			if (i < params.size()) {

+				Object o = params.get(i);

+				if (RuntimeParameter.PARAMETER == o) {

+					// dynamic parameter

+					sb.append('?');

+				} else {

+					// static parameter

+					sb.append(db.getDialect().prepareParameter(o));

+				}

+				i++;

+			}

+		}

+		return sb.toString();

+	}

+

 	public SQLStatement addParameter(Object o) {

 		// Automatically convert java.util.Date to java.sql.Timestamp

 		// if the dialect requires java.sql.Timestamp objects (e.g. Derby)

diff --git a/tests/com/iciql/test/RuntimeQueryTest.java b/tests/com/iciql/test/RuntimeQueryTest.java
index bf6f20d..9b306df 100644
--- a/tests/com/iciql/test/RuntimeQueryTest.java
+++ b/tests/com/iciql/test/RuntimeQueryTest.java
@@ -19,12 +19,16 @@ import static org.junit.Assert.assertEquals;
 

 import java.sql.ResultSet;

 import java.sql.SQLException;

+import java.text.SimpleDateFormat;

 import java.util.List;

 

+import org.junit.Assume;

 import org.junit.Test;

 

 import com.iciql.Db;

+import com.iciql.test.models.EnumModels.Tree;

 import com.iciql.test.models.Product;

+import com.iciql.test.models.StaticQueries;

 import com.iciql.util.JdbcUtils;

 

 /**

@@ -33,6 +37,50 @@ import com.iciql.util.JdbcUtils;
 public class RuntimeQueryTest {

 

 	@Test

+	public void testParameters() {

+		Db db = IciqlSuite.openNewDb();

+		

+		// do not test non-H2 databases because dialects will get in the way

+		// e.g. column quoting, etc

+		Assume.assumeTrue(IciqlSuite.isH2(db));

+

+		Product p = new Product();

+		String q1 = db.from(p).where(p.unitsInStock).isParameter().and(p.productName).likeParameter().orderBy(p.productId).toSQL();

+		String q2 = db.from(p).where(p.unitsInStock).lessThan(100).and(p.productName).like("test").or(p.productName).likeParameter().orderBy(p.productId).toSQL();

+		

+		StaticQueries.StaticModel1 m1 = new StaticQueries.StaticModel1();

+		String q3 = db.from(m1).where(m1.myTree).is(Tree.MAPLE).and(m1.myTree).isParameter().toSQL();

+		

+		StaticQueries.StaticModel2 m2 = new StaticQueries.StaticModel2();

+		String q4 = db.from(m2).where(m2.myTree).is(Tree.MAPLE).and(m2.myTree).isParameter().toSQL();

+

+		StaticQueries.StaticModel3 m3 = new StaticQueries.StaticModel3();

+		String q5 = db.from(m3).where(m3.myTree).is(Tree.MAPLE).and(m3.myTree).isParameter().toSQL();

+

+		long now = System.currentTimeMillis();

+		java.sql.Date aDate = new java.sql.Date(now);

+		java.sql.Time aTime = new java.sql.Time(now);

+		java.sql.Timestamp aTimestamp = new java.sql.Timestamp(now);

+		

+		String q6 = db.from(m1).where(m1.myDate).is(aDate).and(m1.myDate).isParameter().toSQL();

+		String q7 = db.from(m1).where(m1.myTime).is(aTime).and(m1.myTime).isParameter().toSQL();

+		String q8 = db.from(m1).where(m1.myTimestamp).is(aTimestamp).and(m1.myTimestamp).isParameter().toSQL();

+

+		db.close();

+		assertEquals("SELECT * FROM Product WHERE unitsInStock = ? AND productName LIKE ?  ORDER BY productId", q1);

+		assertEquals("SELECT * FROM Product WHERE unitsInStock < 100 AND productName LIKE 'test' OR productName LIKE ?  ORDER BY productId", q2);

+		

+		assertEquals("SELECT * FROM StaticQueryTest1 WHERE myTree = 'MAPLE' AND myTree = ?", q3);

+		assertEquals("SELECT * FROM StaticQueryTest2 WHERE myTree = 50 AND myTree = ?", q4);

+		assertEquals("SELECT * FROM StaticQueryTest3 WHERE myTree = 4 AND myTree = ?", q5);

+

+		java.util.Date refDate = new java.util.Date(now);

+		assertEquals("SELECT * FROM StaticQueryTest1 WHERE myDate = '" + new SimpleDateFormat("yyyy-MM-dd").format(refDate) + "' AND myDate = ?", q6);

+		assertEquals("SELECT * FROM StaticQueryTest1 WHERE myTime = '" + new SimpleDateFormat("HH:mm:ss").format(refDate) + "' AND myTime = ?", q7);

+		assertEquals("SELECT * FROM StaticQueryTest1 WHERE myTimestamp = '" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(refDate) + "' AND myTimestamp = ?", q8);

+	}

+	

+	@Test

 	public void testRuntimeQuery() {

 		Db db = IciqlSuite.openNewDb();

 		db.insertAll(Product.getList());

diff --git a/tests/com/iciql/test/models/StaticQueries.java b/tests/com/iciql/test/models/StaticQueries.java
new file mode 100644
index 0000000..09f84e6
--- /dev/null
+++ b/tests/com/iciql/test/models/StaticQueries.java
@@ -0,0 +1,87 @@
+/*

+ * Copyright 2011 James Moger.

+ *

+ * Licensed under the Apache License, Version 2.0 (the "License");

+ * you may not use this file except in compliance with the License.

+ * You may obtain a copy of the License at

+ *

+ *     http://www.apache.org/licenses/LICENSE-2.0

+ *

+ * Unless required by applicable law or agreed to in writing, software

+ * distributed under the License is distributed on an "AS IS" BASIS,

+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+ * See the License for the specific language governing permissions and

+ * limitations under the License.

+ */

+package com.iciql.test.models;

+

+import java.sql.Timestamp;

+

+import com.iciql.Iciql.EnumType;

+import com.iciql.Iciql.IQColumn;

+import com.iciql.Iciql.IQEnum;

+import com.iciql.Iciql.IQTable;

+import com.iciql.test.models.EnumModels.Tree;

+

+/**

+ * Static query models.

+ */

+public class StaticQueries {

+

+	@IQTable(name = "StaticQueryTest1")

+	public static class StaticModel1 {

+

+		@IQColumn(primaryKey = true, autoIncrement = true)

+		public Integer id;

+

+		@IQColumn

+		@IQEnum(EnumType.NAME)

+		public Tree myTree;

+

+		@IQColumn

+		public String myString;

+

+		@IQColumn

+		public Boolean myBool;

+

+		@IQColumn

+		public Timestamp myTimestamp;

+

+		@IQColumn

+		public java.sql.Date myDate;

+

+		@IQColumn

+		public java.sql.Time myTime;

+

+		public StaticModel1() {

+		}

+	}

+

+	@IQTable(name = "StaticQueryTest2")

+	public static class StaticModel2 {

+

+		@IQColumn(primaryKey = true, autoIncrement = true)

+		public Integer id;

+

+		@IQColumn

+		@IQEnum(EnumType.ENUMID)

+		public Tree myTree;

+

+		public StaticModel2() {

+		}

+	}

+

+	@IQTable(name = "StaticQueryTest3")

+	public static class StaticModel3 {

+

+		@IQColumn(primaryKey = true, autoIncrement = true)

+		public Integer id;

+

+		@IQColumn

+		@IQEnum(EnumType.ORDINAL)

+		public Tree myTree;

+

+		public StaticModel3() {

+		}

+	}

+}