From cc0c4f0e0b632ebfb1635e5ed2fe9a2119240157 Mon Sep 17 00:00:00 2001 From: James Moger Date: Wed, 4 Jan 2012 20:48:04 -0500 Subject: [PATCH] Added methods to generate static, reusable, parameterized sql statements --- src/com/iciql/Query.java | 36 +++++++- src/com/iciql/QueryCondition.java | 35 ++++++++ src/com/iciql/QueryWhere.java | 43 +++++++-- src/com/iciql/RuntimeParameter.java | 49 +++++++++++ src/com/iciql/SQLDialect.java | 9 ++ src/com/iciql/SQLDialectDefault.java | 20 +++++ src/com/iciql/SQLStatement.java | 41 +++++++++ tests/com/iciql/test/RuntimeQueryTest.java | 48 ++++++++++ .../com/iciql/test/models/StaticQueries.java | 87 +++++++++++++++++++ 9 files changed, 362 insertions(+), 6 deletions(-) create mode 100644 src/com/iciql/RuntimeParameter.java create mode 100644 tests/com/iciql/test/models/StaticQueries.java 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 { 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 select(boolean distinct) { List result = Utils.newArrayList(); TableDefinition def = from.getAliasDefinition(); @@ -607,10 +636,15 @@ public class Query { * 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 { return new QueryWhere(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 isParameter() { + query.addConditionToken(new RuntimeParameter(x, CompareType.EQUAL)); + return new QueryWhere(query); + } + + public QueryWhere isNotParameter() { + query.addConditionToken(new RuntimeParameter(x, CompareType.NOT_EQUAL)); + return new QueryWhere(query); + } + + public QueryWhere exceedsParameter() { + query.addConditionToken(new RuntimeParameter(x, CompareType.EXCEEDS)); + return new QueryWhere(query); + } + + public QueryWhere lessThanParameter() { + query.addConditionToken(new RuntimeParameter(x, CompareType.LESS_THAN)); + return new QueryWhere(query); + } + + public QueryWhere atMostParameter() { + query.addConditionToken(new RuntimeParameter(x, CompareType.AT_MOST)); + return new QueryWhere(query); + } + + public QueryWhere likeParameter() { + query.addConditionToken(new RuntimeParameter(x, CompareType.LIKE)); + return new QueryWhere(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 { return addPrimitive(ConditionAndOr.AND, x); } - private QueryCondition addPrimitive(ConditionAndOr condition, A x) { + private QueryCondition addPrimitive(ConditionAndOr condition, A x) { query.addConditionToken(condition); A alias = query.getPrimitiveAliasByValue(x); if (alias == null) { @@ -234,10 +234,6 @@ public class QueryWhere { return this; } - public List 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 { 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 List select(Z x) { + return query.select(x); + } + public List 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 + * the operand type + */ + +class RuntimeParameter implements Token { + + public final static String PARAMETER = ""; + + A x; + CompareType compareType; + + RuntimeParameter(A x, CompareType type) { + this.x = x; + this.compareType = type; + } + + public void appendSQL(SQLStatement stat, Query 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 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; /** @@ -32,6 +36,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(); 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() { + } + } +} -- 2.39.5