summaryrefslogtreecommitdiffstats
path: root/src
diff options
context:
space:
mode:
authorJames Moger <james.moger@gmail.com>2012-01-04 20:48:04 -0500
committerJames Moger <james.moger@gmail.com>2012-01-04 20:48:04 -0500
commitcc0c4f0e0b632ebfb1635e5ed2fe9a2119240157 (patch)
tree33d6228dfac831b0baf572d788fb77bcf3370606 /src
parent6830a631590ae63b6f80476c0a86b6050a91f953 (diff)
downloadiciql-cc0c4f0e0b632ebfb1635e5ed2fe9a2119240157.tar.gz
iciql-cc0c4f0e0b632ebfb1635e5ed2fe9a2119240157.zip
Added methods to generate static, reusable, parameterized sql statements
Diffstat (limited to 'src')
-rw-r--r--src/com/iciql/Query.java36
-rw-r--r--src/com/iciql/QueryCondition.java35
-rw-r--r--src/com/iciql/QueryWhere.java43
-rw-r--r--src/com/iciql/RuntimeParameter.java49
-rw-r--r--src/com/iciql/SQLDialect.java9
-rw-r--r--src/com/iciql/SQLDialectDefault.java20
-rw-r--r--src/com/iciql/SQLStatement.java41
7 files changed, 227 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)