add support condition oneOf / noneOf

var-length parameterized IN(?, ?, ...), NOT IN(?, ?, ...) support

7 files changed, 190 insertions, 23 deletions

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<A> implements Token {

 	CompareType compareType;

 	A x, y, z;

+	Iterable<A> 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<A> i, CompareType compareType) {

+		this(x,  null, null, i, compareType);

+	}

+

+	Condition(A x, A y, A z, Iterable<A> i, CompareType compareType) {

 		this.compareType = compareType;

 		this.x = x;

 		this.y = y;

 		this.z = z;

+		this.i = i;

 	}

 

+	@SuppressWarnings("unchecked")

 	public <T> void appendSQL(SQLStatement stat, Query<T> 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<Object>)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<T> {
 		addParameter(stat, alias, valueRight);

 	}

 

+	public void appendSQL(SQLStatement stat, Object alias, Iterable<Object> 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<T> 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<T, A> {
 		return new QueryWhere<T>(query);
 	}
 
+	public QueryWhere<T> oneOf(A... a) {
+		return oneOf(Utils.newArrayIterable(a));
+	}
+
+	public QueryWhere<T> oneOf(Iterable<A> i) {
+		query.addConditionToken(new Condition<A>(x, i, CompareType.IN));
+		return new QueryWhere<T>(query);
+	}
+
+	public QueryWhere<T> noneOf(A... a) {
+		return noneOf(Utils.newArrayIterable(a));
+	}
+
+	public QueryWhere<T> noneOf(Iterable<A> i) {
+		query.addConditionToken(new Condition<A>(x, i, CompareType.NOT_IN));
+		return new QueryWhere<T>(query);
+	}
+
 	public QueryWhere<T> is(A y) {
 		query.addConditionToken(new Condition<A>(x, y, CompareType.EQUAL));
 		return new QueryWhere<T>(query);
@@ -52,12 +72,12 @@ public class QueryCondition<T, A> {
 	}
 
 	public QueryWhere<T> isNull() {
-		query.addConditionToken(new Condition<A>(x, null, CompareType.IS_NULL));
+		query.addConditionToken(new Condition<A>(x, CompareType.IS_NULL));
 		return new QueryWhere<T>(query);
 	}
 
 	public QueryWhere<T> isNotNull() {
-		query.addConditionToken(new Condition<A>(x, null, CompareType.IS_NOT_NULL));
+		query.addConditionToken(new Condition<A>(x, CompareType.IS_NOT_NULL));
 		return new QueryWhere<T>(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 <T> Iterable<T> newArrayIterable(final T[] a) {

+		return Arrays.asList(a);

+	}

+

 	public static <T> ArrayList<T> newArrayList() {

 		return new ArrayList<T>();

 	}

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.

  * <p>

@@ -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<String>() {{
+							this.add("a");
+						}})
+						.toSQL(),
+				"SELECT * FROM Customer WHERE customerId IN('a')");
+		assertEquals(
+				db.from(c)
+						.where(c.customerId).oneOf(new ArrayList<String>() {{
+							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<String>() {{
+							this.add("a");
+						}})
+						.toSQL(),
+				"SELECT * FROM Customer WHERE customerId NOT IN('a')");
+		assertEquals(
+				db.from(c)
+						.where(c.customerId).noneOf(new ArrayList<String>() {{
+							this.add("a");
+							this.add("b");
+						}})
+						.toSQL(),
+				"SELECT * FROM Customer WHERE customerId NOT IN('a', 'b')");
+	}
+
+}