From 1e4fc9cc867b925d82410f5ab3d5091987d60c4a Mon Sep 17 00:00:00 2001 From: James Moger Date: Wed, 22 Oct 2014 20:40:52 -0400 Subject: [PATCH] Implement execution tests of nested conditions and documentation --- releases.moxie | 4 +- src/site/model_classes.mkd | 4 +- src/site/usage.mkd | 40 ++++++++++++++++++- src/test/java/com/iciql/test/IciqlSuite.java | 2 +- ...onsTest.java => NestedConditionsTest.java} | 33 +++++++++++++-- src/test/java/com/iciql/test/OneOfTest.java | 32 ++++++++++++++- .../java/com/iciql/test/models/Customer.java | 12 ++++-- 7 files changed, 112 insertions(+), 15 deletions(-) rename src/test/java/com/iciql/test/{StackableConditionsTest.java => NestedConditionsTest.java} (88%) diff --git a/releases.moxie b/releases.moxie index b81afc4..47e06d3 100644 --- a/releases.moxie +++ b/releases.moxie @@ -12,9 +12,9 @@ r21: { fixes: - Return null NPE in selectFirst() if list is empty (pr-5) - Fix Moxie toolkit download URL (pr-6) - - Be more careful with primitive numeric type rollovers + - Be more careful with primitive numeric type rollovers (pr-6) changes: - - Revised EnumId interface to support generic types + - Revised EnumId interface to support generic types (pr-6) additions: - Add syntax for IN and NOT IN (pr-7) - Add support for nested AND/OR conditions (pr-8) diff --git a/src/site/model_classes.mkd b/src/site/model_classes.mkd index ea91bb0..cb21dcd 100644 --- a/src/site/model_classes.mkd +++ b/src/site/model_classes.mkd @@ -67,7 +67,7 @@ can be used for all iciql expressions INT
EnumType.ORDINAL
can only declare and explicitly reference one instance of each enum type per model
multiple instances of an enum type within a model is allowed if not using where/set/on/and/or/groupBy/orderBy(enum) java.lang.Enum implements
com.iciql.Iciql.EnumId.enumId() -INT
EnumType.ENUMID
can only declare and explicitly reference one instance of each enum type per model
multiple instances of an enum type within a model is allowed if not using where/set/on/and/or/groupBy/orderBy(enum) +variable
EnumType.ENUMID
can only declare and explicitly reference one instance of each enum type per model
multiple instances of an enum type within a model is allowed if not using where/set/on/and/or/groupBy/orderBy(enum) Partially Supported Types
can not be directly referenced in an expression @@ -383,4 +383,4 @@ public class Product { public Product() { } } ----JAVA--- \ No newline at end of file +---JAVA--- diff --git a/src/site/usage.mkd b/src/site/usage.mkd index b1f4c48..8b54dce 100644 --- a/src/site/usage.mkd +++ b/src/site/usage.mkd @@ -69,6 +69,44 @@ List allProducts = db.buildObjects(Product.class, rs); JdbcUtils.closeSilently(rs, true); ---JAVA--- +### Compound Conditions + +It is possible to specify type-safe compound where clauses using nested `And` and `Or` statements. + +---JAVA--- +final Customer model = new Customer(); + +List regionals = + db.from(model).where(model.customerId).isNotNull() + .and(model.region).isNotNull() + .and(new Or(db, model) {{ + or(model.region).is("LA"); + or(model.region).is("CA"); + }}); + +List regionalType1s = + db.from(model).where(new And(db, model) {{ + and(model.type).is(1); + and(new Or(db, model) {{ + or(model.region).is("CA"); + or(model.region).is("LA"); + }}); + }}); + +---JAVA--- + +### Finding Matches for a List of Values + +You can use SQL's *IN* WHERE clause with the `oneOf` or `noneOf` conditions. + +---JAVA--- +final Customer model = new Customer(); + +List regionals = db.from(model).where(model.region).oneOf("CA", "LA"); +List others = db.from(model).where(model.region).noneOf("CA", "LA"); + +---JAVA--- + ### Read-only Views View model classes can inherit their field definitions from a parent table model class. @@ -232,4 +270,4 @@ for (int i = 0; i < 5; i++) { ---JAVA--- - \ No newline at end of file + diff --git a/src/test/java/com/iciql/test/IciqlSuite.java b/src/test/java/com/iciql/test/IciqlSuite.java index 0273399..c5d7ce8 100644 --- a/src/test/java/com/iciql/test/IciqlSuite.java +++ b/src/test/java/com/iciql/test/IciqlSuite.java @@ -93,7 +93,7 @@ import com.iciql.util.Utils; @SuiteClasses({ AliasMapTest.class, AnnotationsTest.class, BooleanModelTest.class, ClobTest.class, ConcurrencyTest.class, EnumsTest.class, ModelsTest.class, PrimitivesTest.class, OneOfTest.class, RuntimeQueryTest.class, SamplesTest.class, UpdateTest.class, UpgradesTest.class, JoinTest.class, - UUIDTest.class, ViewsTest.class, ForeignKeyTest.class, TransactionTest.class, StackableConditionsTest.class }) + UUIDTest.class, ViewsTest.class, ForeignKeyTest.class, TransactionTest.class, NestedConditionsTest.class }) public class IciqlSuite { private static final TestDb[] TEST_DBS = { diff --git a/src/test/java/com/iciql/test/StackableConditionsTest.java b/src/test/java/com/iciql/test/NestedConditionsTest.java similarity index 88% rename from src/test/java/com/iciql/test/StackableConditionsTest.java rename to src/test/java/com/iciql/test/NestedConditionsTest.java index f812339..48bcfb3 100644 --- a/src/test/java/com/iciql/test/StackableConditionsTest.java +++ b/src/test/java/com/iciql/test/NestedConditionsTest.java @@ -20,6 +20,10 @@ package com.iciql.test; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertTrue; +import java.util.List; +import java.util.Set; +import java.util.TreeSet; + import org.junit.After; import org.junit.Before; import org.junit.Test; @@ -31,7 +35,7 @@ import com.iciql.IciqlException; import com.iciql.QueryWhere; import com.iciql.test.models.Customer; -public class StackableConditionsTest { +public class NestedConditionsTest { enum Region { JP, FR @@ -42,6 +46,7 @@ public class StackableConditionsTest { @Before public void setUp() { db = IciqlSuite.openNewDb(); + db.insertAll(Customer.getList()); } @After @@ -68,9 +73,8 @@ public class StackableConditionsTest { return query.toSQL(); } - @SuppressWarnings("serial") @Test - public void andOrTest() { + public void andOrSyntaxTest() { String Customer = db.getDialect().prepareTableName(null, "Customer"); String customerId = db.getDialect().prepareColumnName("customerId"); String region = db.getDialect().prepareColumnName("region"); @@ -155,7 +159,7 @@ public class StackableConditionsTest { } @Test - public void fluentTest() { + public void fluentSyntaxTest() { String Customer = db.getDialect().prepareTableName(null, "Customer"); String customerId = db.getDialect().prepareColumnName("customerId"); String region = db.getDialect().prepareColumnName("region"); @@ -194,4 +198,25 @@ public class StackableConditionsTest { Customer, customerId, region, region, region)); } + @Test + public void compoundConditionsTest() { + final Customer c = new Customer(); + List matches = db.from(c) + .where(c.customerId).like("A%") + .and(c.region).isNotNull() + .and(new Or(db, c) {{ + or(c.region).is("LA"); + or(c.region).is("CA"); + }}).select(); + + assertEquals(2, matches.size()); + + Set ids = new TreeSet(); + for (Customer customer : matches) { + ids.add(customer.customerId); + } + assertEquals("[ANTON, ASLAN]", ids.toString()); + + } + } diff --git a/src/test/java/com/iciql/test/OneOfTest.java b/src/test/java/com/iciql/test/OneOfTest.java index e534262..c4aa90b 100644 --- a/src/test/java/com/iciql/test/OneOfTest.java +++ b/src/test/java/com/iciql/test/OneOfTest.java @@ -20,6 +20,9 @@ package com.iciql.test; import static org.junit.Assert.assertEquals; import java.util.ArrayList; +import java.util.List; +import java.util.Set; +import java.util.TreeSet; import org.junit.After; import org.junit.Before; @@ -36,6 +39,7 @@ public class OneOfTest { @Before public void setUp() { db = IciqlSuite.openNewDb(); + db.insertAll(Customer.getList()); } @After @@ -45,7 +49,7 @@ public class OneOfTest { @SuppressWarnings("serial") @Test - public void oneOfTest() { + public void oneOfSyntaxTest() { String PrimitivesTest = db.getDialect().prepareTableName(null, "PrimitivesTest"); String Customer = db.getDialect().prepareTableName(null, "Customer"); String myInteger = db.getDialect().prepareColumnName("myInteger"); @@ -82,7 +86,7 @@ public class OneOfTest { @SuppressWarnings("serial") @Test - public void noneOfTest() { + public void noneOfSyntaxTest() { String PrimitivesTest = db.getDialect().prepareTableName(null, "PrimitivesTest"); String Customer = db.getDialect().prepareTableName(null, "Customer"); String myInteger = db.getDialect().prepareColumnName("myInteger"); @@ -117,4 +121,28 @@ public class OneOfTest { .toSQL()); } + public void noneOfTest() { + Customer c = new Customer(); + List meAndny = db.from(c).where(c.region).noneOf("WA", "CA", "LA").select(); + assertEquals(2, meAndny.size()); + + Set regions = new TreeSet(); + for (Customer customer : meAndny) { + regions.add(customer.region); + } + assertEquals("[ME, NY]", regions.toString()); + } + + public void oneOfTest() { + Customer c = new Customer(); + List meAndny = db.from(c).where(c.region).oneOf("ME", "NY").select(); + assertEquals(2, meAndny.size()); + + Set regions = new TreeSet(); + for (Customer customer : meAndny) { + regions.add(customer.region); + } + assertEquals("[ME, NY]", regions.toString()); + } + } diff --git a/src/test/java/com/iciql/test/models/Customer.java b/src/test/java/com/iciql/test/models/Customer.java index ae8e40d..4270623 100644 --- a/src/test/java/com/iciql/test/models/Customer.java +++ b/src/test/java/com/iciql/test/models/Customer.java @@ -44,14 +44,20 @@ public class Customer { this.region = region; } + @Override public String toString() { return customerId; } public static List getList() { - Customer[] list = { new Customer("ALFKI", "WA"), new Customer("ANATR", "WA"), - new Customer("ANTON", "CA") }; - return Arrays.asList(list); + return Arrays.asList( + new Customer("ALFKI", "WA"), + new Customer("ANATR", "WA"), + new Customer("ASLAN", "CA"), + new Customer("ANTON", "CA"), + new Customer("BROWN", "LA"), + new Customer("SMITH", "NY"), + new Customer("JONES", "ME")); } } -- 2.39.5