From dd21bce82da9617ff8f748f8567b0329aca07b83 Mon Sep 17 00:00:00 2001 From: Sotaro SUZUKI Date: Wed, 1 Oct 2014 15:32:06 +0900 Subject: add support condition oneOf / noneOf var-length parameterized IN(?, ?, ...), NOT IN(?, ?, ...) support --- src/main/java/com/iciql/CompareType.java | 2 +- src/main/java/com/iciql/Condition.java | 29 ++++++-- src/main/java/com/iciql/Query.java | 16 ++++ src/main/java/com/iciql/QueryCondition.java | 24 +++++- src/main/java/com/iciql/util/Utils.java | 5 ++ src/test/java/com/iciql/test/IciqlSuite.java | 30 ++++---- src/test/java/com/iciql/test/OneOfTest.java | 107 +++++++++++++++++++++++++++ 7 files changed, 190 insertions(+), 23 deletions(-) create mode 100644 src/test/java/com/iciql/test/OneOfTest.java (limited to 'src') diff --git a/src/main/java/com/iciql/CompareType.java b/src/main/java/com/iciql/CompareType.java index 84e29fe..87ce77e 100644 --- a/src/main/java/com/iciql/CompareType.java +++ b/src/main/java/com/iciql/CompareType.java @@ -24,7 +24,7 @@ package com.iciql; enum CompareType { EQUAL("=", true), EXCEEDS(">", true), AT_LEAST(">=", true), LESS_THAN("<", true), AT_MOST("<=", true), NOT_EQUAL( "<>", true), IS_NOT_NULL("IS NOT NULL", false), IS_NULL("IS NULL", false), LIKE("LIKE", true), BETWEEN( - "BETWEEN", true); + "BETWEEN", true), IN("IN", true), NOT_IN("NOT IN", true); private String text; private boolean hasRightExpression; diff --git a/src/main/java/com/iciql/Condition.java b/src/main/java/com/iciql/Condition.java index 17cb117..0ed1d06 100644 --- a/src/main/java/com/iciql/Condition.java +++ b/src/main/java/com/iciql/Condition.java @@ -27,28 +27,47 @@ package com.iciql; class Condition implements Token { CompareType compareType; A x, y, z; + Iterable i; + + Condition(A x, CompareType compareType) { + this(x, null, null, null, compareType); + } Condition(A x, A y, CompareType compareType) { - this(x, y, null, compareType); + this(x, y, null, null, compareType); } Condition(A x, A y, A z, CompareType compareType) { + this(x, y, z, null, compareType); + } + + Condition(A x, Iterable i, CompareType compareType) { + this(x, null, null, i, compareType); + } + + Condition(A x, A y, A z, Iterable i, CompareType compareType) { this.compareType = compareType; this.x = x; this.y = y; this.z = z; + this.i = i; } + @SuppressWarnings("unchecked") public void appendSQL(SQLStatement stat, Query query) { query.appendSQL(stat, null, x); stat.appendSQL(" "); stat.appendSQL(compareType.getString()); if (compareType.hasRightExpression()) { - stat.appendSQL(" "); - if (z == null) { - query.appendSQL(stat, x, y); + if (i == null) { + stat.appendSQL(" "); + if (z == null) { + query.appendSQL(stat, x, y); + } else { + query.appendSQL(stat, x, y, z, compareType); + } } else { - query.appendSQL(stat, x, y, z, compareType); + query.appendSQL(stat, x, (Iterable)i, compareType); } } } diff --git a/src/main/java/com/iciql/Query.java b/src/main/java/com/iciql/Query.java index 45de08b..5f29edf 100644 --- a/src/main/java/com/iciql/Query.java +++ b/src/main/java/com/iciql/Query.java @@ -792,6 +792,22 @@ public class Query { addParameter(stat, alias, valueRight); } + public void appendSQL(SQLStatement stat, Object alias, Iterable values, + CompareType compareType) { + boolean first = true; + stat.appendSQL("("); + for (Object value : values) { + if (first) { + first = false; + } else { + stat.appendSQL(", "); + } + stat.appendSQL("?"); + addParameter(stat, alias, value); + } + stat.appendSQL(")"); + } + private void addParameter(SQLStatement stat, Object alias, Object value) { if (alias != null && value.getClass().isEnum()) { SelectColumn col = getColumnByReference(alias); diff --git a/src/main/java/com/iciql/QueryCondition.java b/src/main/java/com/iciql/QueryCondition.java index 9613b1b..cef95c1 100644 --- a/src/main/java/com/iciql/QueryCondition.java +++ b/src/main/java/com/iciql/QueryCondition.java @@ -17,6 +17,8 @@ package com.iciql; +import com.iciql.util.Utils; + /** * This class represents a query with an incomplete condition. * @@ -41,6 +43,24 @@ public class QueryCondition { return new QueryWhere(query); } + public QueryWhere oneOf(A... a) { + return oneOf(Utils.newArrayIterable(a)); + } + + public QueryWhere oneOf(Iterable i) { + query.addConditionToken(new Condition(x, i, CompareType.IN)); + return new QueryWhere(query); + } + + public QueryWhere noneOf(A... a) { + return noneOf(Utils.newArrayIterable(a)); + } + + public QueryWhere noneOf(Iterable i) { + query.addConditionToken(new Condition(x, i, CompareType.NOT_IN)); + return new QueryWhere(query); + } + public QueryWhere is(A y) { query.addConditionToken(new Condition(x, y, CompareType.EQUAL)); return new QueryWhere(query); @@ -52,12 +72,12 @@ public class QueryCondition { } public QueryWhere isNull() { - query.addConditionToken(new Condition(x, null, CompareType.IS_NULL)); + query.addConditionToken(new Condition(x, CompareType.IS_NULL)); return new QueryWhere(query); } public QueryWhere isNotNull() { - query.addConditionToken(new Condition(x, null, CompareType.IS_NOT_NULL)); + query.addConditionToken(new Condition(x, CompareType.IS_NOT_NULL)); return new QueryWhere(query); } diff --git a/src/main/java/com/iciql/util/Utils.java b/src/main/java/com/iciql/util/Utils.java index 77110b8..bc636d1 100644 --- a/src/main/java/com/iciql/util/Utils.java +++ b/src/main/java/com/iciql/util/Utils.java @@ -28,6 +28,7 @@ import java.math.BigInteger; import java.sql.Blob; import java.sql.Clob; import java.util.ArrayList; +import java.util.Arrays; import java.util.Collection; import java.util.Collections; import java.util.HashMap; @@ -78,6 +79,10 @@ public class Utils { } } + public static Iterable newArrayIterable(final T[] a) { + return Arrays.asList(a); + } + public static ArrayList newArrayList() { return new ArrayList(); } diff --git a/src/test/java/com/iciql/test/IciqlSuite.java b/src/test/java/com/iciql/test/IciqlSuite.java index a474404..9c9ba39 100644 --- a/src/test/java/com/iciql/test/IciqlSuite.java +++ b/src/test/java/com/iciql/test/IciqlSuite.java @@ -77,7 +77,7 @@ import com.iciql.util.Utils; /** * JUnit 4 iciql test suite. - * + * * By default this test suite will run against the H2 database. You can change * this by switching the DEFAULT_TEST_DB value. *

@@ -87,11 +87,11 @@ import com.iciql.util.Utils; * NOTE: If you want to test against MySQL or PostgreSQL you must create an * "iciql" database and allow user "sa" password "sa" complete control of that * database. - * + * */ @RunWith(Suite.class) @SuiteClasses({ AliasMapTest.class, AnnotationsTest.class, BooleanModelTest.class, ClobTest.class, - ConcurrencyTest.class, EnumsTest.class, ModelsTest.class, PrimitivesTest.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 }) public class IciqlSuite { @@ -140,7 +140,7 @@ public class IciqlSuite { /** * Open a new Db object. All connections are cached and re-used to eliminate * embedded database startup costs. - * + * * @return a fresh Db object */ public static Db openNewDb() { @@ -196,7 +196,7 @@ public class IciqlSuite { /** * Open the current database. - * + * * @return the current database */ public static Db openCurrentDb() { @@ -208,7 +208,7 @@ public class IciqlSuite { /** * Returns the name of the underlying database engine for the Db object. - * + * * @param db * @return the database engine name */ @@ -223,7 +223,7 @@ public class IciqlSuite { /** * Returns true if the underlying database engine is Derby. - * + * * @param db * @return true if underlying database engine is Derby */ @@ -233,7 +233,7 @@ public class IciqlSuite { /** * Returns true if the underlying database engine is H2. - * + * * @param db * @return true if underlying database engine is H2 */ @@ -243,7 +243,7 @@ public class IciqlSuite { /** * Returns true if the underlying database engine is MySQL. - * + * * @param db * @return true if underlying database engine is MySQL */ @@ -253,7 +253,7 @@ public class IciqlSuite { /** * Gets the default schema of the underlying database engine. - * + * * @param db * @return the default schema */ @@ -272,7 +272,7 @@ public class IciqlSuite { /** * Main entry point for the test suite. Executing this method will run the * test suite on all registered databases. - * + * * @param args * @throws Exception */ @@ -491,7 +491,7 @@ public class IciqlSuite { /** * Start an HSQL tcp server. - * + * * @return an HSQL server instance * @throws Exception */ @@ -503,7 +503,7 @@ public class IciqlSuite { // set up the rest of properties // alternative to the above is - org.hsqldb.Server server = new org.hsqldb.Server(); + org.hsqldb.Server server = new org.hsqldb.Server(); server.setProperties(p); server.setLogWriter(null); server.setErrWriter(null); @@ -513,7 +513,7 @@ public class IciqlSuite { /** * Start the H2 tcp server. - * + * * @return an H2 server instance * @throws Exception */ @@ -555,7 +555,7 @@ public class IciqlSuite { } int getStatementRate() { - return Double.valueOf(((double) statements) / (runtime / 1000d)).intValue(); + return Double.valueOf((statements) / (runtime / 1000d)).intValue(); } String describeDatabase() { diff --git a/src/test/java/com/iciql/test/OneOfTest.java b/src/test/java/com/iciql/test/OneOfTest.java new file mode 100644 index 0000000..2ca7cc7 --- /dev/null +++ b/src/test/java/com/iciql/test/OneOfTest.java @@ -0,0 +1,107 @@ +/* + * Copyright (c) 2009-2014, Architector Inc., Japan + * All rights reserved. + * + * 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.test; + +import static org.junit.Assert.assertEquals; +import java.util.ArrayList; +import org.junit.After; +import org.junit.Before; +import org.junit.Test; +import com.iciql.Db; +import com.iciql.test.models.Customer; +import com.iciql.test.models.PrimitivesModel; + +public class OneOfTest { + + private Db db; + + @Before + public void setUp() { + db = IciqlSuite.openNewDb(); + } + + @After + public void tearDown() { + db.close(); + } + + @SuppressWarnings("serial") + @Test + public void oneOfTest() { + PrimitivesModel p = new PrimitivesModel(); + assertEquals( + db.from(p) + .where(p.myInteger).oneOf(0) + .toSQL(), + "SELECT * FROM PrimitivesTest WHERE myInteger IN(0)"); + assertEquals( + db.from(p) + .where(p.myInteger).oneOf(0, 1) + .toSQL(), + "SELECT * FROM PrimitivesTest WHERE myInteger IN(0, 1)"); + Customer c = new Customer(); + assertEquals( + db.from(c) + .where(c.customerId).oneOf(new ArrayList() {{ + this.add("a"); + }}) + .toSQL(), + "SELECT * FROM Customer WHERE customerId IN('a')"); + assertEquals( + db.from(c) + .where(c.customerId).oneOf(new ArrayList() {{ + this.add("a"); + this.add("b"); + }}) + .toSQL(), + "SELECT * FROM Customer WHERE customerId IN('a', 'b')"); + } + + @SuppressWarnings("serial") + @Test + public void noneOfTest() { + PrimitivesModel p = new PrimitivesModel(); + assertEquals( + db.from(p) + .where(p.myInteger).noneOf(0) + .toSQL(), + "SELECT * FROM PrimitivesTest WHERE myInteger NOT IN(0)"); + assertEquals( + db.from(p) + .where(p.myInteger).noneOf(0, 1) + .toSQL(), + "SELECT * FROM PrimitivesTest WHERE myInteger NOT IN(0, 1)"); + Customer c = new Customer(); + assertEquals( + db.from(c) + .where(c.customerId).noneOf(new ArrayList() {{ + this.add("a"); + }}) + .toSQL(), + "SELECT * FROM Customer WHERE customerId NOT IN('a')"); + assertEquals( + db.from(c) + .where(c.customerId).noneOf(new ArrayList() {{ + this.add("a"); + this.add("b"); + }}) + .toSQL(), + "SELECT * FROM Customer WHERE customerId NOT IN('a', 'b')"); + } + +} -- cgit v1.2.3