From 876c4e51578dfa7bd98956d2f07ae7498a70629c Mon Sep 17 00:00:00 2001 From: James Moger Date: Fri, 9 Dec 2011 16:42:59 -0500 Subject: [PATCH] Columns mapped by name in result set instead of index. Disallow multiple primitive bools in a model WITH explicit referencing. --- api/v8.xml | 84 ++++++++++++++++++- docs/01_model_classes.mkd | 2 +- docs/04_examples.mkd | 7 ++ docs/05_releases.mkd | 26 +++++- docs/06_jaqu_comparison.mkd | 1 + src/com/iciql/Db.java | 17 +++- src/com/iciql/Iciql.java | 11 +++ src/com/iciql/Query.java | 18 +++- src/com/iciql/QueryJoin.java | 3 +- src/com/iciql/QueryWhere.java | 5 +- src/com/iciql/TableDefinition.java | 68 ++++++++++++++- tests/com/iciql/test/PrimitivesTest.java | 17 ++++ tests/com/iciql/test/SamplesTest.java | 5 +- .../iciql/test/models/MultipleBoolsModel.java | 40 +++++++++ 14 files changed, 289 insertions(+), 15 deletions(-) create mode 100644 tests/com/iciql/test/models/MultipleBoolsModel.java diff --git a/api/v8.xml b/api/v8.xml index d738d6a..8a2014d 100644 --- a/api/v8.xml +++ b/api/v8.xml @@ -43,7 +43,7 @@ type="java.lang.String" transient="false" volatile="false" - value=""0.7.3"" + value=""0.7.4-SNAPSHOT"" static="true" final="true" deprecated="not deprecated" @@ -54,7 +54,7 @@ type="java.lang.String" transient="false" volatile="false" - value=""2011-12-06"" + value=""PENDING"" static="true" final="true" deprecated="not deprecated" @@ -70,6 +70,17 @@ deprecated="not deprecated" visibility="public" > + + + + + + + + + + + + + + + + + + + + + + + + + + + + VARCHAR *(length > 0)* or CLOB *(length == 0)* java.lang.Booleanboolean -BOOLEAN +BOOLEAN
can only **declare and explicitly reference** one primitive boolean per model
multiple primitives are allowed if not using where/set/on/and/or/groupBy/orderBy(boolean) java.lang.Bytebyte TINYINT diff --git a/docs/04_examples.mkd b/docs/04_examples.mkd index 480e29d..a1273be 100644 --- a/docs/04_examples.mkd +++ b/docs/04_examples.mkd @@ -8,6 +8,13 @@ List<Product> allProducts = db.from(p).select(); Customer c = new Customer(); List<Customer> waCustomers = db.from(c). where(c.region).is("WA").select(); +public static class ProductPrice { + public String productName; + public String category; + @IQColumn(name = "unitPrice") + public Double price; +} + // select with generation of new anonymous inner class List<ProductPrice> productPrices = db.from(p). diff --git a/docs/05_releases.mkd b/docs/05_releases.mkd index 9a7d13e..d4aa8f2 100644 --- a/docs/05_releases.mkd +++ b/docs/05_releases.mkd @@ -6,9 +6,29 @@ **%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 list alternatives to the varargs methods because it was too easy to forget list.toArray() for the varargs methods -List Db.executeQuery(Class modelClass, String sql, List args) -ResultSet executeQuery(String sql, List args) +- Disallow **declaring and explicitly referencing** multiple primitive booleans in a single model.
A runtime exception will be thrown if an attempt to use where/set/on/and/or/groupBy/orderBy(boolean) and your model has multiple mapped primitive boolean fields. +- Added list alternatives to the varargs methods because it was too easy to forget list.toArray()
+*Db.executeQuery(Class<? extends T> modelClass, String sql, List<?> args)*
+*Db.executeQuery(String sql, List<?> args)*
+*Query.where(String fragment, List<?> args)*
+- Fixed inherited JaQu bug related to model classes and wildcard queries (select *).

+Iciql maps resultset columns by the index of the model class field from a list. This assumes that *all* columns in the resultset have a corresponding model field definition. This works fine for most queries because iciql explicitly selects columns from the table (*select alpha, beta...*) when you execute *select()*. The problem is when iciql issues a join or a custom wildcard query and your model does not represent all columns in the resultset: columns and fields fail to correctly line-up.

+The fix for this (building a column index from the resultset by column name lookup) breaks selecting into *some* anonymous inner classes. At issue is that the inner class field names must now match the column names or the fields must be explicitly annotated with the column names.

**Example** (notice *IQColumn* annotation)
+%BEGINCODE% +public static class ProductPrice { + public String productName; + public String category; + @IQColumn(name = "unitPrice") + public Double price; +} + +db....select(new ProductPrice() {{ + productName = p.productName; + category = p.category; + // or unitPrice = p.unitPrice; + price = p.unitPrice; +}} +%ENDCODE% ### Older Releases diff --git a/docs/06_jaqu_comparison.mkd b/docs/06_jaqu_comparison.mkd index 33ca975..9c0f56c 100644 --- a/docs/06_jaqu_comparison.mkd +++ b/docs/06_jaqu_comparison.mkd @@ -10,6 +10,7 @@ This is an overview of the fundamental differences between the original JaQu pro databasesH2, HSQL, Derby, MySQL, and PostreSQLH2 only loggingconsole, SLF4J, or custom loggingconsole logging exceptionsalways includes generated statement in exception, when available-- +column mappingsresult sets built by column nameresult sets built by field index
this can fail for dynamic queries or joins syntax and api dynamic queriesmethods and where clauses for dynamic queries that build iciql objects-- DROPsyntax to drop a table diff --git a/src/com/iciql/Db.java b/src/com/iciql/Db.java index e05ec56..4dc0b5e 100644 --- a/src/com/iciql/Db.java +++ b/src/com/iciql/Db.java @@ -138,6 +138,20 @@ public class Db { throw new IciqlException(e); } } + + /** + * Convenience function to avoid import statements in application code. + */ + public static void activateConsoleLogger() { + IciqlLogger.activateConsoleLogger(); + } + + /** + * Convenience function to avoid import statements in application code. + */ + public static void deactivateConsoleLogger() { + IciqlLogger.deactivateConsoleLogger(); + } public static Db open(String url) { try { @@ -273,9 +287,10 @@ public class Db { List result = new ArrayList(); TableDefinition def = (TableDefinition) define(modelClass); try { + int [] columns = def.mapColumns(rs); while (rs.next()) { T item = Utils.newObject(modelClass); - def.readRow(item, rs); + def.readRow(item, rs, columns); result.add(item); } } catch (SQLException e) { diff --git a/src/com/iciql/Iciql.java b/src/com/iciql/Iciql.java index eaa256a..b13baf4 100644 --- a/src/com/iciql/Iciql.java +++ b/src/com/iciql/Iciql.java @@ -499,6 +499,17 @@ public interface Iciql { EnumType value() default EnumType.NAME; } + /** + * Annotation to define a field that should contain the result a function. + * This annotation ensures that functions mapped in anonymous inner classes + * can still be referenced in the ResultSet after the switch to dynamic + * column-name mapping from fixed position column mapping. + */ + @Retention(RetentionPolicy.RUNTIME) + @Target(ElementType.FIELD) + public @interface IQFunction{ + } + /** * Annotation to define an ignored field. */ diff --git a/src/com/iciql/Query.java b/src/com/iciql/Query.java index b43f774..33b6dfa 100644 --- a/src/com/iciql/Query.java +++ b/src/com/iciql/Query.java @@ -123,9 +123,10 @@ public class Query { appendFromWhere(stat); ResultSet rs = stat.executeQuery(); try { + int[] columns = def.mapColumns(rs); while (rs.next()) { T item = from.newObject(); - from.getAliasDefinition().readRow(item, rs); + def.readRow(item, rs, columns); result.add(item); } } catch (SQLException e) { @@ -150,6 +151,7 @@ public class Query { } public UpdateColumnSet set(boolean field) { + from.getAliasDefinition().checkMultipleBooleans(); return setPrimitive(field); } @@ -269,9 +271,10 @@ public class Query { appendFromWhere(stat); ResultSet rs = stat.executeQuery(); try { + int[] columns = def.mapColumns(rs); while (rs.next()) { X row = Utils.newObject(clazz); - def.readRow(row, rs); + def.readRow(row, rs, columns); result.add(row); } } catch (SQLException e) { @@ -328,6 +331,7 @@ public class Query { * @return a query condition to continue building the condition */ public QueryCondition where(boolean x) { + from.getAliasDefinition().checkMultipleBooleans(); return wherePrimitive(x); } @@ -449,6 +453,10 @@ public class Query { return new QueryWhere(this); } + public QueryWhere where(String fragment, List args) { + return this.where(fragment, args.toArray()); + } + public QueryWhere where(String fragment, Object... args) { conditions.add(new RuntimeToken(fragment, args)); return new QueryWhere(this); @@ -477,6 +485,7 @@ public class Query { } public Query orderBy(boolean field) { + from.getAliasDefinition().checkMultipleBooleans(); return orderByPrimitive(field); } @@ -541,6 +550,7 @@ public class Query { } public Query groupBy(boolean field) { + from.getAliasDefinition().checkMultipleBooleans(); return groupByPrimitive(field); } @@ -737,6 +747,10 @@ public class Query { return db; } + SelectTable getFrom() { + return from; + } + boolean isJoin() { return !joins.isEmpty(); } diff --git a/src/com/iciql/QueryJoin.java b/src/com/iciql/QueryJoin.java index 652d937..6d0484e 100644 --- a/src/com/iciql/QueryJoin.java +++ b/src/com/iciql/QueryJoin.java @@ -32,6 +32,7 @@ public class QueryJoin { } public QueryJoinCondition on(boolean x) { + query.getFrom().getAliasDefinition().checkMultipleBooleans(); return addPrimitive(x); } @@ -59,7 +60,7 @@ public class QueryJoin { return addPrimitive(x); } - private QueryJoinCondition addPrimitive(A x) { + private QueryJoinCondition addPrimitive(A x) { A alias = query.getPrimitiveAliasByValue(x); if (alias == null) { // this will result in an unmapped field exception diff --git a/src/com/iciql/QueryWhere.java b/src/com/iciql/QueryWhere.java index c1e3b03..df93439 100644 --- a/src/com/iciql/QueryWhere.java +++ b/src/com/iciql/QueryWhere.java @@ -42,6 +42,7 @@ public class QueryWhere { * @return a query condition to continue building the condition */ public QueryCondition and(boolean x) { + query.getFrom().getAliasDefinition().checkMultipleBooleans(); return addPrimitive(ConditionAndOr.AND, x); } @@ -111,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) { @@ -141,6 +142,7 @@ public class QueryWhere { * @return a query condition to continue building the condition */ public QueryCondition or(boolean x) { + query.getFrom().getAliasDefinition().checkMultipleBooleans(); return addPrimitive(ConditionAndOr.OR, x); } @@ -273,6 +275,7 @@ public class QueryWhere { * @return the query */ public QueryWhere orderBy(boolean field) { + query.getFrom().getAliasDefinition().checkMultipleBooleans(); return orderByPrimitive(field); } diff --git a/src/com/iciql/TableDefinition.java b/src/com/iciql/TableDefinition.java index 97060f9..1147238 100644 --- a/src/com/iciql/TableDefinition.java +++ b/src/com/iciql/TableDefinition.java @@ -30,6 +30,7 @@ import com.iciql.Iciql.EnumId; import com.iciql.Iciql.EnumType; import com.iciql.Iciql.IQColumn; import com.iciql.Iciql.IQEnum; +import com.iciql.Iciql.IQFunction; import com.iciql.Iciql.IQIgnore; import com.iciql.Iciql.IQIndex; import com.iciql.Iciql.IQIndexes; @@ -73,6 +74,7 @@ public class TableDefinition { String dataType; int length; int scale; + boolean isFunction; boolean isPrimaryKey; boolean isAutoIncrement; boolean trim; @@ -115,6 +117,10 @@ public class TableDefinition { } private Object read(ResultSet rs, int columnIndex) { + if (columnIndex == 0) { + // unmapped column or function field + return null; + } try { return rs.getObject(columnIndex); } catch (SQLException e) { @@ -129,6 +135,7 @@ public class TableDefinition { int tableVersion; List primaryKeyColumnNames; boolean memoryTable; + boolean multiplePrimitiveBools; private boolean createIfRequired = true; private Class clazz; @@ -354,6 +361,7 @@ public class TableDefinition { throw new IciqlException(e, "failed to get default object for {0}", columnName); } + boolean isFunction = f.isAnnotationPresent(IQFunction.class); boolean hasAnnotation = f.isAnnotationPresent(IQColumn.class); if (hasAnnotation) { IQColumn col = f.getAnnotation(IQColumn.class); @@ -385,6 +393,7 @@ public class TableDefinition { fieldDef.scale = scale; fieldDef.trim = trim; fieldDef.nullable = nullable; + fieldDef.isFunction = isFunction; fieldDef.defaultValue = defaultValue; fieldDef.enumType = enumType; fieldDef.dataType = ModelUtils.getDataType(fieldDef); @@ -392,16 +401,32 @@ public class TableDefinition { } } List primaryKey = Utils.newArrayList(); + int primitiveBoolean = 0; for (FieldDefinition fieldDef : fields) { if (fieldDef.isPrimaryKey) { primaryKey.add(fieldDef.columnName); } + if (fieldDef.isPrimitive && fieldDef.field.getType().equals(boolean.class)) { + primitiveBoolean++; + } + } + if (primitiveBoolean > 1) { + multiplePrimitiveBools = true; + IciqlLogger + .warn("Model {0} has multiple primitive booleans! Possible where,set,join clause problem!"); } if (primaryKey.size() > 0) { setPrimaryKey(primaryKey); } } + void checkMultipleBooleans() { + if (multiplePrimitiveBools) { + throw new IciqlException( + "Can not explicitly reference multiple primitive booleans in a model class!"); + } + } + /** * Optionally truncates strings to the maximum length and converts * java.lang.Enum types to Strings or Integers. @@ -698,10 +723,47 @@ public class TableDefinition { } } - void readRow(Object item, ResultSet rs) { + /** + * Most queries executed by iciql have named select lists (select alpha, + * beta where...) but sometimes a wildcard select is executed (select *). + * When a wildcard query is executed on a table that has more columns than + * are mapped in your model object this creates a column mapping issue. JaQu + * assumed that you can always use the integer index of the reflectively + * mapped field definition to determine position in the result set. + * + * This is not always true. + * + * So iciql maps column names to column index in the result set to properly + * map the results of wildcard queries. + * + * @param rs + * @return + */ + int[] mapColumns(ResultSet rs) { + int[] columns = new int[fields.size()]; for (int i = 0; i < fields.size(); i++) { - FieldDefinition def = fields.get(i); - Object o = def.read(rs, i + 1); + try { + FieldDefinition def = fields.get(i); + int columnIndex; + if (def.isFunction) { + // XXX review functions _always_ map after fields? + columnIndex = i + 1; + } else { + columnIndex = rs.findColumn(def.columnName); + } + columns[i] = columnIndex; + } catch (SQLException s) { + throw new IciqlException(s); + } + } + return columns; + } + + void readRow(Object item, ResultSet rs, int[] columns) { + for (int i = 0; i < fields.size(); i++) { + FieldDefinition def = fields.get(i); + int index = columns[i]; + Object o = def.read(rs, index); def.setValue(item, o); } } diff --git a/tests/com/iciql/test/PrimitivesTest.java b/tests/com/iciql/test/PrimitivesTest.java index be2b726..3d3811e 100644 --- a/tests/com/iciql/test/PrimitivesTest.java +++ b/tests/com/iciql/test/PrimitivesTest.java @@ -25,6 +25,8 @@ import java.util.List; import org.junit.Test; import com.iciql.Db; +import com.iciql.IciqlException; +import com.iciql.test.models.MultipleBoolsModel; import com.iciql.test.models.PrimitivesModel; /** @@ -82,4 +84,19 @@ public class PrimitivesTest { db.close(); } + + @Test + public void testMultipleBooleans() { + Db db = IciqlSuite.openNewDb(); + db.insertAll(MultipleBoolsModel.getList()); + + MultipleBoolsModel m = new MultipleBoolsModel(); + try { + db.from(m).where(m.a).is(true).select(); + assertTrue(false); + } catch (IciqlException e) { + assertTrue(true); + } + db.close(); + } } diff --git a/tests/com/iciql/test/SamplesTest.java b/tests/com/iciql/test/SamplesTest.java index 17c2151..5c7ffc8 100644 --- a/tests/com/iciql/test/SamplesTest.java +++ b/tests/com/iciql/test/SamplesTest.java @@ -39,6 +39,8 @@ import org.junit.Test; import com.iciql.Db; import com.iciql.Filter; +import com.iciql.Iciql.IQColumn; +import com.iciql.Iciql.IQFunction; import com.iciql.test.models.ComplexObject; import com.iciql.test.models.Customer; import com.iciql.test.models.Order; @@ -161,6 +163,7 @@ public class SamplesTest { public static class ProductPrice { public String productName; public String category; + @IQColumn(name = "unitPrice") public Double price; } @@ -406,6 +409,7 @@ public class SamplesTest { */ public static class ProductGroup { public String category; + @IQFunction public Long productCount; public String toString() { @@ -432,7 +436,6 @@ public class SamplesTest { productCount = count(); } }); - assertEquals("[Beverages:2, Condiments:5, Meat/Poultry:1, Produce:1, Seafood:1]", list.toString()); } diff --git a/tests/com/iciql/test/models/MultipleBoolsModel.java b/tests/com/iciql/test/models/MultipleBoolsModel.java new file mode 100644 index 0000000..7bc429c --- /dev/null +++ b/tests/com/iciql/test/models/MultipleBoolsModel.java @@ -0,0 +1,40 @@ +package com.iciql.test.models; + +import java.util.Arrays; +import java.util.List; + +import com.iciql.Iciql.IQColumn; +import com.iciql.Iciql.IQTable; + +/** + * Model class to test the runtime exception of too many primitive boolean + * fields in the model. + * + * @author James Moger + * + */ +@IQTable +public class MultipleBoolsModel { + + @IQColumn(autoIncrement = true, primaryKey = true) + public int id; + + @IQColumn + public boolean a; + + @IQColumn + public boolean b; + + public MultipleBoolsModel() { + } + + public MultipleBoolsModel(boolean a, boolean b) { + this.a = a; + this.b = b; + } + + public static List getList() { + return Arrays.asList(new MultipleBoolsModel(true, true), new MultipleBoolsModel(true, false), + new MultipleBoolsModel(true, false), new MultipleBoolsModel(false, false)); + } +} \ No newline at end of file -- 2.39.5