From 2e077918649d398dce0948cb3feeb61b925ee8a4 Mon Sep 17 00:00:00 2001 From: James Moger Date: Wed, 11 Jan 2012 09:20:51 -0500 Subject: [PATCH] Generate SELECT T0.* type statements * Fixed negative rollover bug in the AS counter. * Replaced the non-threadsafe AS counter with an AtomicInteger. * Added an optional alias parameter to Query.toSQL() and QueryWhere.toSQL() to force SELECT T0.* select lists * Fixed bug with Query.select(Z z) which assumed that z is always an anonymous inner class. --- api/v12.xml | 6364 ++++++++++++++++++++ docs/05_releases.mkd | 9 + src/com/iciql/Constants.java | 6 +- src/com/iciql/Query.java | 72 +- src/com/iciql/QueryWhere.java | 32 +- src/com/iciql/SelectTable.java | 3 +- src/com/iciql/TableDefinition.java | 30 +- src/com/iciql/util/Utils.java | 13 + tests/com/iciql/test/RuntimeQueryTest.java | 43 + tests/com/iciql/test/SamplesTest.java | 2 + 10 files changed, 6548 insertions(+), 26 deletions(-) create mode 100644 api/v12.xml diff --git a/api/v12.xml b/api/v12.xml new file mode 100644 index 0000000..d985d11 --- /dev/null +++ b/api/v12.xml @@ -0,0 +1,6364 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/docs/05_releases.mkd b/docs/05_releases.mkd index 685120b..40f6cb8 100644 --- a/docs/05_releases.mkd +++ b/docs/05_releases.mkd @@ -6,6 +6,15 @@ **%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%* +- 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. + - Query.toSQL(boolean distinct, K alias) + - QueryWhere.toSQL(boolean distinct, K alias) +- Fixed bug in Query.select(Z z) which assumed that Z must always be an anonymous inner class which may not always be true. This allows for specifying an existing alias to force table or identifier usage in the generated select list. This is very useful for DISTINCT JOIN statements where only the columns of the primary table are of interest. + +**0.7.7**   *released 2012-01-05* + - added *Query.toSQL()* and *QueryWhere.toSQL()* methods which, when combined with the following new methods, allows for generation of a parameterized, static sql string to be reused with a dynamic query or a PreparedStatement. - QueryCondition.isParameter() - QueryCondition.atLeastParameter() diff --git a/src/com/iciql/Constants.java b/src/com/iciql/Constants.java index 85a6738..5fd1dec 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.7"; + public static final String VERSION = "0.7.8"; // 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-05"; + public static final String VERSION_DATE = "2012-01-11"; // 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 = "11"; + public static final String API_CURRENT = "12"; } diff --git a/src/com/iciql/Query.java b/src/com/iciql/Query.java index e656c0d..fb193a0 100644 --- a/src/com/iciql/Query.java +++ b/src/com/iciql/Query.java @@ -126,7 +126,7 @@ public class Query { 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 @@ -136,10 +136,55 @@ public class Query { * @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("*"); + return toSQL(distinct, null); + } + + /** + * 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 + * @param k + * k is used to select only the columns of the specified alias + * for an inner join statement. An example of a generated + * statement is: SELECT DISTINCT t1.* FROM sometable AS t1 INNER + * JOIN othertable AS t2 ON t1.id = t2.id WHERE t2.flag = true + * without the alias parameter the statement would start with + * SELECT DISTINCT * FROM... + * @return the sql query as plain text + */ + 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 + ".*"); + } + } else { + // alias unspecified, use wildcard + stat.appendSQL("*"); + } appendFromWhere(stat); return stat.toSQL().trim(); } @@ -289,7 +334,11 @@ public class Query { if (Utils.isSimpleType(clazz)) { return selectSimple((X) x, distinct); } - clazz = clazz.getSuperclass(); + Class enclosingClass = clazz.getEnclosingClass(); + if (enclosingClass != null) { + // anonymous inner class + clazz = clazz.getSuperclass(); + } return select((Class) clazz, (X) x, distinct); } @@ -794,6 +843,19 @@ public class Query { return !joins.isEmpty(); } + SelectTable getSelectTable(Object alias) { + if (from.getAlias() == alias) { + return from; + } else { + for (SelectTable join : joins) { + if (join.getAlias() == alias) { + return join; + } + } + } + return null; + } + /** * This method returns a mapped Object field by its reference. * diff --git a/src/com/iciql/QueryWhere.java b/src/com/iciql/QueryWhere.java index 7503ea1..31228c9 100644 --- a/src/com/iciql/QueryWhere.java +++ b/src/com/iciql/QueryWhere.java @@ -252,7 +252,7 @@ public class QueryWhere { * @return the sql query as plain text */ public String toSQL() { - return this.toSQL(false); + return query.toSQL(false); } /** @@ -266,14 +266,28 @@ public class QueryWhere { * @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(); + return query.toSQL(distinct); + } + + /** + * 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 + * @param k + * k is used to select only the columns of the specified alias + * for an inner join statement. An example of a generated + * statement is: SELECT DISTINCT t1.* FROM sometable AS t1 INNER + * JOIN othertable AS t2 ON t1.id = t2.id WHERE t2.flag = true + * without the alias parameter the statement would start with + * SELECT DISTINCT * FROM... + * @return the sql query as plain text + */ + public String toSQL(boolean distinct, K k) { + return query.toSQL(distinct, k); } public List select(Z x) { diff --git a/src/com/iciql/SelectTable.java b/src/com/iciql/SelectTable.java index 7c5017c..37b42c4 100644 --- a/src/com/iciql/SelectTable.java +++ b/src/com/iciql/SelectTable.java @@ -30,7 +30,6 @@ import com.iciql.util.Utils; class SelectTable { - private static int asCounter; private Query query; private Class clazz; private T current; @@ -47,7 +46,7 @@ class SelectTable { this.outerJoin = outerJoin; aliasDef = (TableDefinition) db.getTableDefinition(alias.getClass()); clazz = Utils.getClass(alias); - as = "T" + asCounter++; + as = "T" + Utils.nextAsCount(); } T getAlias() { diff --git a/src/com/iciql/TableDefinition.java b/src/com/iciql/TableDefinition.java index b85b941..1d9a98a 100644 --- a/src/com/iciql/TableDefinition.java +++ b/src/com/iciql/TableDefinition.java @@ -418,16 +418,16 @@ public class TableDefinition { "Can not explicitly reference a primitive boolean if there are multiple boolean fields in your model class!"); } } - + void checkMultipleEnums(Object o) { - if (o == null) { - return; - } + if (o == null) { + return; + } Class clazz = o.getClass(); if (!clazz.isEnum()) { return; } - + int fieldCount = 0; for (FieldDefinition fieldDef : fields) { Class targetType = fieldDef.field.getType(); @@ -435,10 +435,11 @@ public class TableDefinition { fieldCount++; } } - + if (fieldCount > 1) { throw new IciqlException( - "Can not explicitly reference {0} because there are {1} {0} fields in your model class!", clazz.getSimpleName(), fieldCount); + "Can not explicitly reference {0} because there are {1} {0} fields in your model class!", + clazz.getSimpleName(), fieldCount); } } @@ -820,10 +821,25 @@ public class TableDefinition { } void appendSelectList(SQLStatement stat, Query query, X x) { + // select t0.col1, t0.col2, t0.col3... + // select table1.col1, table1.col2, table1.col3... + String selectDot = ""; + SelectTable sel = query.getSelectTable(x); + if (sel != null) { + if (query.isJoin()) { + selectDot = sel.getAs() + "."; + } else { + String sn = sel.getAliasDefinition().schemaName; + String tn = sel.getAliasDefinition().tableName; + selectDot = query.getDb().getDialect().prepareTableName(sn, tn) + "."; + } + } + for (int i = 0; i < fields.size(); i++) { if (i > 0) { stat.appendSQL(", "); } + stat.appendSQL(selectDot); FieldDefinition def = fields.get(i); if (def.isPrimitive) { Object obj = def.getValue(x); diff --git a/src/com/iciql/util/Utils.java b/src/com/iciql/util/Utils.java index 44792c7..77110b8 100644 --- a/src/com/iciql/util/Utils.java +++ b/src/com/iciql/util/Utils.java @@ -35,6 +35,7 @@ import java.util.HashSet; import java.util.IdentityHashMap; import java.util.Map; import java.util.UUID; +import java.util.concurrent.atomic.AtomicInteger; import java.util.concurrent.atomic.AtomicLong; import com.iciql.Iciql.EnumId; @@ -47,10 +48,22 @@ import com.iciql.IciqlException; public class Utils { public static final AtomicLong COUNTER = new AtomicLong(0); + + public static final AtomicInteger AS_COUNTER = new AtomicInteger(0); private static final boolean MAKE_ACCESSIBLE = true; private static final int BUFFER_BLOCK_SIZE = 4 * 1024; + + public static synchronized int nextAsCount() { + // prevent negative values and use a threadsafe counter + int count = AS_COUNTER.incrementAndGet(); + if (count == Integer.MAX_VALUE) { + count = 0; + AS_COUNTER.set(count); + } + return count; + } @SuppressWarnings("unchecked") public static Class getClass(X x) { diff --git a/tests/com/iciql/test/RuntimeQueryTest.java b/tests/com/iciql/test/RuntimeQueryTest.java index 9b306df..bb43a4f 100644 --- a/tests/com/iciql/test/RuntimeQueryTest.java +++ b/tests/com/iciql/test/RuntimeQueryTest.java @@ -19,6 +19,7 @@ import static org.junit.Assert.assertEquals; import java.sql.ResultSet; import java.sql.SQLException; +import java.text.MessageFormat; import java.text.SimpleDateFormat; import java.util.List; @@ -26,10 +27,12 @@ import org.junit.Assume; import org.junit.Test; import com.iciql.Db; +import com.iciql.QueryWhere; import com.iciql.test.models.EnumModels.Tree; import com.iciql.test.models.Product; import com.iciql.test.models.StaticQueries; import com.iciql.util.JdbcUtils; +import com.iciql.util.Utils; /** * Tests the runtime dynamic query function. @@ -80,6 +83,46 @@ 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 testRuntimeSelectWildcards() { + 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 m1 = new StaticQueries.StaticModel1(); + StaticQueries.StaticModel2 m2 = new StaticQueries.StaticModel2(); + StaticQueries.StaticModel2 m3 = new StaticQueries.StaticModel2(); + + int t0 = Utils.AS_COUNTER.get() + 1; + int t1 = t0 + 1; + + QueryWhere where = db.from(m1).innerJoin(m2).on(m1.id).is(m2.id).where(m2.myTree).is(Tree.MAPLE); + String q1 = where.toSQL(false); + String q2 = where.toSQL(true); + String q3 = where.toSQL(false, m1); + String q4 = where.toSQL(true, m1); + String q5 = where.toSQL(false, m2); + String q6 = where.toSQL(true, m2); + + // test unused alias + String q7 = where.toSQL(true, m3); + + db.close(); + + assertEquals(MessageFormat.format("SELECT * FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50", t0, t1), q1); + assertEquals(MessageFormat.format("SELECT DISTINCT * FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50", t0, t1), q2); + + assertEquals(MessageFormat.format("SELECT T{0,number,0}.* FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50", t0, t1), q3); + assertEquals(MessageFormat.format("SELECT DISTINCT T{0,number,0}.* FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50", t0, t1), q4); + + assertEquals(MessageFormat.format("SELECT T{1,number,0}.* FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50", t0, t1), q5); + assertEquals(MessageFormat.format("SELECT DISTINCT T{1,number,0}.* FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50", t0, t1), q6); + + assertEquals(MessageFormat.format("SELECT DISTINCT * FROM StaticQueryTest1 AS T{0,number,0} INNER JOIN StaticQueryTest2 AS T{1,number,0} ON T{0,number,0}.id = T{1,number,0}.id WHERE T{1,number,0}.myTree = 50", t0, t1), q7); + } + @Test public void testRuntimeQuery() { Db db = IciqlSuite.openNewDb(); diff --git a/tests/com/iciql/test/SamplesTest.java b/tests/com/iciql/test/SamplesTest.java index 5179d79..49a64f5 100644 --- a/tests/com/iciql/test/SamplesTest.java +++ b/tests/com/iciql/test/SamplesTest.java @@ -105,8 +105,10 @@ public class SamplesTest { Product p = new Product(); List soldOutProducts = db.from(p).where(p.unitsInStock).is(0).orderBy(p.productId).select(); + List soldOutProducts2 = db.from(p).where(p.unitsInStock).is(0).orderBy(p.productId).select(p); assertEquals("[Chef Anton's Gumbo Mix: 0]", soldOutProducts.toString()); + assertEquals(soldOutProducts.toString(), soldOutProducts2.toString()); } @Test -- 2.39.5