From 342840e8c3ce94aaf14dd71b3246058393096058 Mon Sep 17 00:00:00 2001 From: James Moger Date: Tue, 24 Jan 2012 08:19:31 -0500 Subject: [PATCH] Added toParameter() to update/set commands Also allow generation of parameterized update statements for reuse. --- api/v13.xml | 6388 ++++++++++++++++++++ docs/04_examples.mkd | 5 + docs/05_releases.mkd | 6 + src/com/iciql/Constants.java | 6 +- src/com/iciql/Db.java | 17 +- src/com/iciql/Query.java | 62 +- src/com/iciql/UpdateColumnSet.java | 15 +- tests/com/iciql/test/RuntimeQueryTest.java | 15 + 8 files changed, 6482 insertions(+), 32 deletions(-) create mode 100644 api/v13.xml diff --git a/api/v13.xml b/api/v13.xml new file mode 100644 index 0000000..4dcd2aa --- /dev/null +++ b/api/v13.xml @@ -0,0 +1,6388 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/docs/04_examples.mkd b/docs/04_examples.mkd index 1f605e7..822bea5 100644 --- a/docs/04_examples.mkd +++ b/docs/04_examples.mkd @@ -55,6 +55,11 @@ db.from(p).set(p.productName).to("updated") .increment(p.unitPrice).by(3.14) .increment(p.unitsInStock).by(2) .where(p.productId).is(1).update(); + +// reusable, parameterized update query +String q = db.from(p).set(p.productName).toParameter().where(p.productId).is(1).toSQL(); +db.executeUpdate(q, "Lettuce"); + %ENDCODE% ## Merge Statements diff --git a/docs/05_releases.mkd b/docs/05_releases.mkd index 40f6cb8..fc90b54 100644 --- a/docs/05_releases.mkd +++ b/docs/05_releases.mkd @@ -6,6 +6,12 @@ **%VERSION%** ([zip](http://code.google.com/p/iciql/downloads/detail?name=%ZIP%)|[jar](http://code.google.com/p/iciql/downloads/detail?name=%JAR%))   *released %BUILDDATE%* +- Added toParameter() option for SET commands and allow generating parameterized UPDATE statements
+String q = db.from(t).set(t.timestamp).toParameter().where(t.id).is(5).toSQL();
+db.executeUpdate(q, new Date()); + +**0.7.8**   *released 2012-01-11* + - Replaced non-threadsafe counter used for assigning AS identifiers in JOIN statements with an AtomicInteger - Prevent negative rollover of the AS counter - Added optional alias parameter to *Query.toSQL* and *QueryWhere.toSQL* to force generated statement to prefix an AS identifier or, alternatively, the tablename. diff --git a/src/com/iciql/Constants.java b/src/com/iciql/Constants.java index 5fd1dec..3653cde 100644 --- a/src/com/iciql/Constants.java +++ b/src/com/iciql/Constants.java @@ -25,14 +25,14 @@ public class Constants { // The build script extracts this exact line so be careful editing it // and only use A-Z a-z 0-9 .-_ in the string. - public static final String VERSION = "0.7.8"; + public static final String VERSION = "0.7.9"; // The build script extracts this exact line so be careful editing it // and only use A-Z a-z 0-9 .-_ in the string. - public static final String VERSION_DATE = "2012-01-11"; + public static final String VERSION_DATE = "2012-01-24"; // The build script extracts this exact line so be careful editing it // and only use A-Z a-z 0-9 .-_ in the string. - public static final String API_CURRENT = "12"; + public static final String API_CURRENT = "13"; } diff --git a/src/com/iciql/Db.java b/src/com/iciql/Db.java index bfc3c73..16cf386 100644 --- a/src/com/iciql/Db.java +++ b/src/com/iciql/Db.java @@ -572,11 +572,22 @@ public class Db { * the SQL statement * @return the update count */ - public int executeUpdate(String sql) { + public int executeUpdate(String sql, Object... args) { Statement stat = null; try { - stat = conn.createStatement(); - int updateCount = stat.executeUpdate(sql); + int updateCount; + if (args.length == 0) { + stat = conn.createStatement(); + updateCount = stat.executeUpdate(sql); + } else { + PreparedStatement ps = conn.prepareStatement(sql); + int i = 1; + for (Object arg : args) { + ps.setObject(i++, arg); + } + updateCount = ps.executeUpdate(); + stat = ps; + } return updateCount; } catch (SQLException e) { throw new IciqlException(e); diff --git a/src/com/iciql/Query.java b/src/com/iciql/Query.java index fb193a0..aa0ce4b 100644 --- a/src/com/iciql/Query.java +++ b/src/com/iciql/Query.java @@ -160,32 +160,46 @@ public class Query { */ public String toSQL(boolean distinct, K k) { SQLStatement stat = new SQLStatement(getDb()); - stat.appendSQL("SELECT "); - if (distinct) { - stat.appendSQL("DISTINCT "); - } - if (k != null) { - SelectTable sel = getSelectTable(k); - if (sel == null) { - // unknown alias, use wildcard - IciqlLogger.warn("Alias {0} is not defined in the statement!", k.getClass()); - stat.appendSQL("*"); - } else if (isJoin()) { - // join query, use AS alias - String as = sel.getAs(); - stat.appendSQL(as + ".*"); - } else { - // schema.table.* - String schema = sel.getAliasDefinition().schemaName; - String table = sel.getAliasDefinition().tableName; - String as = getDb().getDialect().prepareTableName(schema, table); - stat.appendSQL(as + ".*"); + if (updateColumnDeclarations.size() > 0) { + stat.appendSQL("UPDATE "); + from.appendSQL(stat); + stat.appendSQL(" SET "); + int i = 0; + for (UpdateColumn declaration : updateColumnDeclarations) { + if (i++ > 0) { + stat.appendSQL(", "); + } + declaration.appendSQL(stat); } + appendWhere(stat); } else { - // alias unspecified, use wildcard - stat.appendSQL("*"); - } - appendFromWhere(stat); + stat.appendSQL("SELECT "); + if (distinct) { + stat.appendSQL("DISTINCT "); + } + if (k != null) { + SelectTable sel = getSelectTable(k); + if (sel == null) { + // unknown alias, use wildcard + IciqlLogger.warn("Alias {0} is not defined in the statement!", k.getClass()); + stat.appendSQL("*"); + } else if (isJoin()) { + // join query, use AS alias + String as = sel.getAs(); + stat.appendSQL(as + ".*"); + } else { + // schema.table.* + String schema = sel.getAliasDefinition().schemaName; + String table = sel.getAliasDefinition().tableName; + String as = getDb().getDialect().prepareTableName(schema, table); + stat.appendSQL(as + ".*"); + } + } else { + // alias unspecified, use wildcard + stat.appendSQL("*"); + } + appendFromWhere(stat); + } return stat.toSQL().trim(); } diff --git a/src/com/iciql/UpdateColumnSet.java b/src/com/iciql/UpdateColumnSet.java index 8c30982..a961480 100644 --- a/src/com/iciql/UpdateColumnSet.java +++ b/src/com/iciql/UpdateColumnSet.java @@ -31,6 +31,7 @@ public class UpdateColumnSet implements UpdateColumn { private Query query; private A x; private A y; + private boolean isParameter; UpdateColumnSet(Query query, A x) { this.query = query; @@ -43,10 +44,20 @@ public class UpdateColumnSet implements UpdateColumn { return query; } + public Query toParameter() { + query.addUpdateColumnDeclaration(this); + isParameter = true; + return query; + } + public void appendSQL(SQLStatement stat) { query.appendSQL(stat, null, x); - stat.appendSQL("="); - query.appendSQL(stat, x, y); + stat.appendSQL(" = "); + if (isParameter) { + query.appendSQL(stat, x, RuntimeParameter.PARAMETER); + } else { + query.appendSQL(stat, x, y); + } } } diff --git a/tests/com/iciql/test/RuntimeQueryTest.java b/tests/com/iciql/test/RuntimeQueryTest.java index bb43a4f..c23527f 100644 --- a/tests/com/iciql/test/RuntimeQueryTest.java +++ b/tests/com/iciql/test/RuntimeQueryTest.java @@ -83,6 +83,21 @@ public class RuntimeQueryTest { assertEquals("SELECT * FROM StaticQueryTest1 WHERE myTimestamp = '" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(refDate) + "' AND myTimestamp = ?", q8); } + @Test + public void testRuntimeSet() { + 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)); + + StaticQueries.StaticModel1 m = new StaticQueries.StaticModel1(); + String q = db.from(m).set(m.myTimestamp).toParameter().where(m.id).isParameter().toSQL(); + db.close(); + + assertEquals("UPDATE StaticQueryTest1 SET myTimestamp = ? WHERE id = ?", q); + } + @Test public void testRuntimeSelectWildcards() { Db db = IciqlSuite.openNewDb(); -- 2.39.5