summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--src/main/java/com/iciql/NestedConditions.java (renamed from src/main/java/com/iciql/Conditions.java)20
-rw-r--r--src/main/java/com/iciql/Query.java76
-rw-r--r--src/main/java/com/iciql/QueryWhere.java93
-rw-r--r--src/site/usage.mkd36
-rw-r--r--src/test/java/com/iciql/test/NestedConditionsTest.java132
5 files changed, 211 insertions, 146 deletions
diff --git a/src/main/java/com/iciql/Conditions.java b/src/main/java/com/iciql/NestedConditions.java
index 1699641..5c92a86 100644
--- a/src/main/java/com/iciql/Conditions.java
+++ b/src/main/java/com/iciql/NestedConditions.java
@@ -1,11 +1,11 @@
/*
* 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
@@ -17,9 +17,9 @@
package com.iciql;
-public abstract class Conditions<T> {
+public abstract class NestedConditions<T> {
- public static class And<T> extends Conditions<T> {
+ public static class And<T> extends NestedConditions<T> {
public And(Db db, T alias) {
super(db, alias);
@@ -58,20 +58,20 @@ public abstract class Conditions<T> {
}
protected QueryWhere<T> and(And<T> conditions) {
- where.andOpenTrue();
+ where.andOpen();
where.query.addConditionToken(conditions.where.query);
return where.close();
}
protected QueryWhere<T> and(Or<T> conditions) {
- where.andOpenFalse();
+ where.andOpen();
where.query.addConditionToken(conditions.where.query);
return where.close();
}
}
- public static class Or<T> extends Conditions<T> {
+ public static class Or<T> extends NestedConditions<T> {
public Or(Db db, T alias) {
super(db, alias);
@@ -110,13 +110,13 @@ public abstract class Conditions<T> {
}
protected QueryWhere<T> or(And<T> conditions) {
- where.orOpenTrue();
+ where.orOpen();
where.query.addConditionToken(conditions.where.query);
return where.close();
}
protected QueryWhere<T> or(Or<T> conditions) {
- where.orOpenFalse();
+ where.orOpen();
where.query.addConditionToken(conditions.where.query);
return where.close();
}
@@ -125,7 +125,7 @@ public abstract class Conditions<T> {
QueryWhere<T> where;
- private Conditions(Db db, T alias) {
+ private NestedConditions(Db db, T alias) {
where = new QueryWhere<T>(Query.rebuild(db, alias));
}
diff --git a/src/main/java/com/iciql/Query.java b/src/main/java/com/iciql/Query.java
index 7edd8fc..f22215c 100644
--- a/src/main/java/com/iciql/Query.java
+++ b/src/main/java/com/iciql/Query.java
@@ -27,17 +27,18 @@ import java.util.Arrays;
import java.util.HashMap;
import java.util.IdentityHashMap;
import java.util.List;
-import com.iciql.Conditions.And;
-import com.iciql.Conditions.Or;
+
+import com.iciql.NestedConditions.And;
+import com.iciql.NestedConditions.Or;
import com.iciql.Iciql.EnumType;
import com.iciql.bytecode.ClassReader;
-import com.iciql.util.JdbcUtils;
import com.iciql.util.IciqlLogger;
+import com.iciql.util.JdbcUtils;
import com.iciql.util.Utils;
/**
* This class represents a query.
- *
+ *
* @param <T>
* the return type
*/
@@ -62,7 +63,7 @@ public class Query<T> {
/**
* from() is a static factory method to build a Query object.
- *
+ *
* @param db
* @param alias
* @return a query object
@@ -119,13 +120,13 @@ public class Query<T> {
List<X> list = limit(1).select(x);
return list.isEmpty() ? null : list.get(0);
}
-
+
public <X> void createView(Class<X> viewClass) {
TableDefinition<X> viewDef = db.define(viewClass);
-
+
SQLStatement fromWhere = new SQLStatement(db);
appendFromWhere(fromWhere, false);
-
+
SQLStatement stat = new SQLStatement(db);
db.getDialect().prepareCreateView(stat, viewDef, fromWhere.toSQL());
IciqlLogger.create(stat.toSQL());
@@ -149,7 +150,7 @@ public class Query<T> {
* 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.
- *
+ *
* @return the sql query as plain text
*/
public String toSQL() {
@@ -161,7 +162,7 @@ public class Query<T> {
* 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
* @return the sql query as plain text
@@ -175,7 +176,7 @@ public class Query<T> {
* 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
@@ -228,7 +229,7 @@ public class Query<T> {
stat.appendSQL("*");
}
appendFromWhere(stat);
- }
+ }
return stat.toSQL().trim();
}
@@ -239,7 +240,7 @@ public class Query<T> {
stat.appendColumn(columnName);
appendFromWhere(stat);
return stat.toSQL();
- }
+ }
private List<T> select(boolean distinct) {
List<T> result = Utils.newArrayList();
@@ -456,7 +457,7 @@ public class Query<T> {
/**
* Begin a primitive boolean field condition clause.
- *
+ *
* @param x
* the primitive boolean field to query
* @return a query condition to continue building the condition
@@ -468,7 +469,7 @@ public class Query<T> {
/**
* Begin a primitive short field condition clause.
- *
+ *
* @param x
* the primitive short field to query
* @return a query condition to continue building the condition
@@ -479,7 +480,7 @@ public class Query<T> {
/**
* Begin a primitive short field condition clause.
- *
+ *
* @param x
* the primitive short field to query
* @return a query condition to continue building the condition
@@ -490,7 +491,7 @@ public class Query<T> {
/**
* Begin a primitive int field condition clause.
- *
+ *
* @param x
* the primitive int field to query
* @return a query condition to continue building the condition
@@ -501,7 +502,7 @@ public class Query<T> {
/**
* Begin a primitive long field condition clause.
- *
+ *
* @param x
* the primitive long field to query
* @return a query condition to continue building the condition
@@ -512,7 +513,7 @@ public class Query<T> {
/**
* Begin a primitive float field condition clause.
- *
+ *
* @param x
* the primitive float field to query
* @return a query condition to continue building the condition
@@ -523,7 +524,7 @@ public class Query<T> {
/**
* Begin a primitive double field condition clause.
- *
+ *
* @param x
* the primitive double field to query
* @return a query condition to continue building the condition
@@ -534,7 +535,7 @@ public class Query<T> {
/**
* Begins a primitive field condition clause.
- *
+ *
* @param value
* @return a query condition to continue building the condition
*/
@@ -549,7 +550,7 @@ public class Query<T> {
/**
* Begin an Object field condition clause.
- *
+ *
* @param x
* the mapped object to query
* @return a query condition to continue building the condition
@@ -622,7 +623,7 @@ public class Query<T> {
/**
* Sets the Limit and Offset of a query.
- *
+ *
* @return the query
*/
@@ -682,7 +683,7 @@ public class Query<T> {
/**
* Order by a number of columns.
- *
+ *
* @param expressions
* the columns
* @return the query
@@ -753,7 +754,7 @@ public class Query<T> {
/**
* INTERNAL
- *
+ *
* @param stat
* the statement
* @param alias
@@ -796,7 +797,7 @@ public class Query<T> {
/**
* INTERNAL
- *
+ *
* @param stat
* the statement
* @param alias
@@ -879,17 +880,30 @@ public class Query<T> {
}
if (!conditions.isEmpty()) {
stat.appendSQL(" WHERE ");
+
+ boolean skipNextConjunction = false;
+
for (Token token : conditions) {
+
+ if (skipNextConjunction && token instanceof ConditionAndOr) {
+ skipNextConjunction = false;
+ continue;
+ }
+
token.appendSQL(stat, this);
stat.appendSQL(" ");
+
+ if (ConditionOpenClose.OPEN == token) {
+ skipNextConjunction = true;
+ }
}
}
}
-
+
void appendFromWhere(SQLStatement stat) {
appendFromWhere(stat, true);
}
-
+
void appendFromWhere(SQLStatement stat, boolean log) {
stat.appendSQL(" FROM ");
from.appendSQL(stat);
@@ -927,7 +941,7 @@ public class Query<T> {
/**
* Join another table.
- *
+ *
* @param alias
* an alias for the table to join
* @return the joined query
@@ -977,7 +991,7 @@ public class Query<T> {
/**
* This method returns a mapped Object field by its reference.
- *
+ *
* @param obj
* @return
*/
@@ -988,7 +1002,7 @@ public class Query<T> {
/**
* This method returns the alias of a mapped primitive field by its value.
- *
+ *
* @param obj
* @return
*/
diff --git a/src/main/java/com/iciql/QueryWhere.java b/src/main/java/com/iciql/QueryWhere.java
index 5ea8bdb..731fc0b 100644
--- a/src/main/java/com/iciql/QueryWhere.java
+++ b/src/main/java/com/iciql/QueryWhere.java
@@ -19,12 +19,12 @@ package com.iciql;
import java.util.List;
-import com.iciql.Conditions.And;
-import com.iciql.Conditions.Or;
+import com.iciql.NestedConditions.And;
+import com.iciql.NestedConditions.Or;
/**
* This class represents a query with a condition.
- *
+ *
* @param <T>
* the return type
*/
@@ -39,7 +39,7 @@ public class QueryWhere<T> {
/**
* Specify an AND condition with a mapped primitive boolean.
- *
+ *
* @param x
* the primitive boolean field to query
* @return a query condition to continue building the condition
@@ -51,7 +51,7 @@ public class QueryWhere<T> {
/**
* Specify an AND condition with a mapped primitive byte.
- *
+ *
* @param x
* the primitive byte field to query
* @return a query condition to continue building the condition
@@ -62,7 +62,7 @@ public class QueryWhere<T> {
/**
* Specify an AND condition with a mapped primitive short.
- *
+ *
* @param x
* the primitive short field to query
* @return a query condition to continue building the condition
@@ -73,7 +73,7 @@ public class QueryWhere<T> {
/**
* Specify an AND condition with a mapped primitive int.
- *
+ *
* @param x
* the primitive int field to query
* @return a query condition to continue building the condition
@@ -84,7 +84,7 @@ public class QueryWhere<T> {
/**
* Specify an AND condition with a mapped primitive long.
- *
+ *
* @param x
* the primitive long field to query
* @return a query condition to continue building the condition
@@ -95,7 +95,7 @@ public class QueryWhere<T> {
/**
* Specify an AND condition with a mapped primitive float.
- *
+ *
* @param x
* the primitive float field to query
* @return a query condition to continue building the condition
@@ -106,7 +106,7 @@ public class QueryWhere<T> {
/**
* Specify an AND condition with a mapped primitive double.
- *
+ *
* @param x
* the primitive double field to query
* @return a query condition to continue building the condition
@@ -127,7 +127,7 @@ public class QueryWhere<T> {
/**
* Specify an AND condition with a mapped Object field.
- *
+ *
* @param x
* the Object field to query
* @return a query condition to continue building the condition
@@ -139,28 +139,24 @@ public class QueryWhere<T> {
}
public QueryWhere<T> and(And<T> conditions) {
- andOpenTrue();
+ andOpen();
query.addConditionToken(conditions.where.query);
return close();
}
public QueryWhere<T> and(Or<T> conditions) {
- andOpenFalse();
+ andOpen();
query.addConditionToken(conditions.where.query);
return close();
}
- public QueryWhere<T> andOpenTrue() {
- return open(ConditionAndOr.AND, true);
- }
-
- public QueryWhere<T> andOpenFalse() {
- return open(ConditionAndOr.AND, false);
+ public QueryWhere<T> andOpen() {
+ return open(ConditionAndOr.AND);
}
/**
* Specify an OR condition with a mapped primitive boolean.
- *
+ *
* @param x
* the primitive boolean field to query
* @return a query condition to continue building the condition
@@ -172,7 +168,7 @@ public class QueryWhere<T> {
/**
* Specify an OR condition with a mapped primitive byte.
- *
+ *
* @param x
* the primitive byte field to query
* @return a query condition to continue building the condition
@@ -183,7 +179,7 @@ public class QueryWhere<T> {
/**
* Specify an OR condition with a mapped primitive short.
- *
+ *
* @param x
* the primitive short field to query
* @return a query condition to continue building the condition
@@ -194,7 +190,7 @@ public class QueryWhere<T> {
/**
* Specify an OR condition with a mapped primitive int.
- *
+ *
* @param x
* the primitive int field to query
* @return a query condition to continue building the condition
@@ -205,7 +201,7 @@ public class QueryWhere<T> {
/**
* Specify an OR condition with a mapped primitive long.
- *
+ *
* @param x
* the primitive long field to query
* @return a query condition to continue building the condition
@@ -216,7 +212,7 @@ public class QueryWhere<T> {
/**
* Specify an OR condition with a mapped primitive float.
- *
+ *
* @param x
* the primitive float field to query
* @return a query condition to continue building the condition
@@ -227,7 +223,7 @@ public class QueryWhere<T> {
/**
* Specify an OR condition with a mapped primitive double.
- *
+ *
* @param x
* the primitive double field to query
* @return a query condition to continue building the condition
@@ -238,7 +234,7 @@ public class QueryWhere<T> {
/**
* Specify an OR condition with a mapped Object field.
- *
+ *
* @param x
* the Object field to query
* @return a query condition to continue building the condition
@@ -250,29 +246,24 @@ public class QueryWhere<T> {
}
public QueryWhere<T> or(And<T> conditions) {
- orOpenTrue();
+ orOpen();
query.addConditionToken(conditions.where.query);
return close();
}
public QueryWhere<T> or(Or<T> conditions) {
- orOpenFalse();
+ orOpen();
query.addConditionToken(conditions.where.query);
return close();
}
- public QueryWhere<T> orOpenTrue() {
- return open(ConditionAndOr.OR, true);
- }
-
- public QueryWhere<T> orOpenFalse() {
- return open(ConditionAndOr.OR, false);
+ public QueryWhere<T> orOpen() {
+ return open(ConditionAndOr.OR);
}
- private QueryWhere<T> open(ConditionAndOr andOr, Boolean condition) {
+ private QueryWhere<T> open(ConditionAndOr andOr) {
query.addConditionToken(andOr);
query.addConditionToken(ConditionOpenClose.OPEN);
- query.addConditionToken(new Function("", condition));
return this;
}
@@ -303,7 +294,7 @@ public class QueryWhere<T> {
* 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.
- *
+ *
* @return the sql query as plain text
*/
public String toSQL() {
@@ -315,7 +306,7 @@ public class QueryWhere<T> {
* 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
* @return the sql query as plain text
@@ -329,7 +320,7 @@ public class QueryWhere<T> {
* 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
@@ -344,12 +335,12 @@ public class QueryWhere<T> {
public <K> String toSQL(boolean distinct, K k) {
return query.toSQL(distinct, k);
}
-
+
public <Z> SubQuery<T, Z> subQuery(Z x) {
return new SubQuery<T, Z>(query, x);
}
- public SubQuery<T, Boolean> subQuery(boolean x) {
+ public SubQuery<T, Boolean> subQuery(boolean x) {
return subQuery(query.getPrimitiveAliasByValue(x));
}
@@ -376,7 +367,7 @@ public class QueryWhere<T> {
public SubQuery<T, Double> subQuery(double x) {
return subQuery(query.getPrimitiveAliasByValue(x));
}
-
+
public <X, Z> List<X> select(Z x) {
return query.select(x);
}
@@ -402,7 +393,7 @@ public class QueryWhere<T> {
public List<T> selectDistinct() {
return query.selectDistinct();
}
-
+
public void createView(Class<?> viewClass) {
query.createView(viewClass);
}
@@ -413,7 +404,7 @@ public class QueryWhere<T> {
/**
* Order by primitive boolean field
- *
+ *
* @param field
* a primitive boolean field
* @return the query
@@ -425,7 +416,7 @@ public class QueryWhere<T> {
/**
* Order by primitive byte field
- *
+ *
* @param field
* a primitive byte field
* @return the query
@@ -436,7 +427,7 @@ public class QueryWhere<T> {
/**
* Order by primitive short field
- *
+ *
* @param field
* a primitive short field
* @return the query
@@ -451,7 +442,7 @@ public class QueryWhere<T> {
/**
* Order by primitive long field
- *
+ *
* @param field
* a primitive long field
* @return the query
@@ -462,7 +453,7 @@ public class QueryWhere<T> {
/**
* Order by primitive float field
- *
+ *
* @param field
* a primitive float field
* @return the query
@@ -473,7 +464,7 @@ public class QueryWhere<T> {
/**
* Order by primitive double field
- *
+ *
* @param field
* a primitive double field
* @return the query
@@ -495,7 +486,7 @@ public class QueryWhere<T> {
/**
* Order by a number of Object columns.
- *
+ *
* @param expressions
* the order by expressions
* @return the query
diff --git a/src/site/usage.mkd b/src/site/usage.mkd
index 8b54dce..b09f91b 100644
--- a/src/site/usage.mkd
+++ b/src/site/usage.mkd
@@ -69,28 +69,36 @@ List<Product> allProducts = db.buildObjects(Product.class, rs);
JdbcUtils.closeSilently(rs, true);
---JAVA---
-### Compound Conditions
+### Compound Nested Conditions
-It is possible to specify type-safe compound where clauses using nested `And` and `Or` statements.
+It is possible to specify type-safe compound nested conditions in your WHERE clauses using `And` and `Or` statements.
---JAVA---
final Customer model = new Customer();
+// SELECT * FROM CUSTOMERS
+// WHERE customerId IS NOT NULL
+// AND region IS NOT NULL
+// AND (
+// region = 'LA' OR region = 'CA'
+// )
List<Customer> regionals =
- db.from(model).where(model.customerId).isNotNull()
- .and(model.region).isNotNull()
- .and(new Or<Customer>(db, model) {{
- or(model.region).is("LA");
- or(model.region).is("CA");
- }});
+ db.from(model)
+ .where(model.customerId).isNotNull()
+ .and(model.region).isNotNull()
+ .and(new Or<Customer>(db, model) {{
+ or(model.region).is("LA");
+ or(model.region).is("CA");
+ }});
List<Customer> regionalType1s =
- db.from(model).where(new And<Customer>(db, model) {{
- and(model.type).is(1);
- and(new Or<Customer>(db, model) {{
- or(model.region).is("CA");
- or(model.region).is("LA");
- }});
+ db.from(model)
+ .where(new And<Customer>(db, model) {{
+ and(model.type).is(1);
+ and(new Or<Customer>(db, model) {{
+ or(model.region).is("CA");
+ or(model.region).is("LA");
+ }});
}});
---JAVA---
diff --git a/src/test/java/com/iciql/test/NestedConditionsTest.java b/src/test/java/com/iciql/test/NestedConditionsTest.java
index 48bcfb3..7a20468 100644
--- a/src/test/java/com/iciql/test/NestedConditionsTest.java
+++ b/src/test/java/com/iciql/test/NestedConditionsTest.java
@@ -28,10 +28,10 @@ import org.junit.After;
import org.junit.Before;
import org.junit.Test;
-import com.iciql.Conditions.And;
-import com.iciql.Conditions.Or;
import com.iciql.Db;
import com.iciql.IciqlException;
+import com.iciql.NestedConditions.And;
+import com.iciql.NestedConditions.Or;
import com.iciql.QueryWhere;
import com.iciql.test.models.Customer;
@@ -60,13 +60,15 @@ public class NestedConditionsTest {
model = new Customer();
query = db.from(model).whereTrue();
- if (customerIds != null) {
- query.andOpenFalse();
+
+ if (customerIds != null && customerIds.length > 0) {
+ query.andOpen();
for (String value : customerIds) {
query.or(model.customerId).is(value);
}
query.close();
}
+
if (region != null) {
query.and(model.region).is(region.name());
}
@@ -80,37 +82,35 @@ public class NestedConditionsTest {
String region = db.getDialect().prepareColumnName("region");
assertEquals(
- search(null, (String[]) null),
- String.format("SELECT * FROM %s WHERE (true)",
- Customer));
+ String.format("SELECT * FROM %s WHERE (true)", Customer),
+ search(null, (String[]) null));
assertEquals(
- search(null, new String[0]),
- String.format("SELECT * FROM %s WHERE (true) AND ( (false) )",
- Customer));
+ String.format("SELECT * FROM %s WHERE (true)", Customer),
+ search(null, new String[0]));
assertEquals(
- search(null, "0001"),
- String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' )",
- Customer, customerId));
+ String.format("SELECT * FROM %s WHERE (true) AND ( %s = '0001' )",
+ Customer, customerId),
+ search(null, "0001"));
assertEquals(
- search(null, "0001", "0002"),
- String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' OR %s = '0002' )",
- Customer, customerId, customerId));
+ String.format("SELECT * FROM %s WHERE (true) AND ( %s = '0001' OR %s = '0002' )",
+ Customer, customerId, customerId),
+ search(null, "0001", "0002"));
assertEquals(
- search(Region.JP, (String[]) null),
String.format("SELECT * FROM %s WHERE (true) AND %s = 'JP'",
- Customer, region));
+ Customer, region),
+ search(Region.JP, (String[]) null));
assertEquals(
- search(Region.JP, new String[0]),
- String.format("SELECT * FROM %s WHERE (true) AND ( (false) ) AND %s = 'JP'",
- Customer, region));
+ String.format("SELECT * FROM %s WHERE (true) AND %s = 'JP'",
+ Customer, region),
+ search(Region.JP, new String[0]));
assertEquals(
- search(Region.JP, "0001"),
- String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' ) AND %s = 'JP'",
- Customer, customerId, region));
+ String.format("SELECT * FROM %s WHERE (true) AND ( %s = '0001' ) AND %s = 'JP'",
+ Customer, customerId, region),
+ search(Region.JP, "0001"));
assertEquals(
- search(Region.JP, "0001", "0002"),
- String.format("SELECT * FROM %s WHERE (true) AND ( (false) OR %s = '0001' OR %s = '0002' ) AND %s = 'JP'",
- Customer, customerId, customerId, region));
+ String.format("SELECT * FROM %s WHERE (true) AND ( %s = '0001' OR %s = '0002' ) AND %s = 'JP'",
+ Customer, customerId, customerId, region),
+ search(Region.JP, "0001", "0002"));
}
@Test
@@ -121,7 +121,7 @@ public class NestedConditionsTest {
try {
db.from(model)
.where(model.customerId).is("0001")
- .andOpenFalse()
+ .andOpen()
.or(model.region).is("FR")
.or(model.region).is("JP")
.close()
@@ -131,10 +131,11 @@ public class NestedConditionsTest {
catch (IciqlException error) {
assertTrue(false);
}
+
try {
db.from(model)
.where(model.customerId).is("0001")
- .andOpenFalse()
+ .andOpen()
.or(model.region).is("FR")
.or(model.region).is("JP")
.toSQL();
@@ -143,10 +144,11 @@ public class NestedConditionsTest {
catch (IciqlException error) {
assertTrue(true);
}
+
try {
db.from(model)
.where(model.customerId).is("0001")
- .andOpenFalse()
+ .andOpen()
.or(model.region).is("FR")
.or(model.region).is("JP")
.close()
@@ -165,37 +167,87 @@ public class NestedConditionsTest {
String region = db.getDialect().prepareColumnName("region");
final Customer model = new Customer();
+
assertEquals(
+ String.format("SELECT * FROM %s WHERE (true) AND %s = '0001' AND ( %s = 'CA' OR %s = 'LA' )",
+ Customer, customerId, region, region),
+
db.from(model).where(new And<Customer>(db, model) {{
+
and(model.customerId).is("0001");
and(new Or<Customer>(db, model) {{
or(model.region).is("CA");
or(model.region).is("LA");
}});
- }}).toSQL(),
- String.format("SELECT * FROM %s WHERE (true) AND %s = '0001' AND ( (false) OR %s = 'CA' OR %s = 'LA' )",
- Customer, customerId, region, region));
+
+ }})
+
+ .toSQL());
+
assertEquals(
+ String.format("SELECT * FROM %s WHERE (false) OR %s = '0001' OR ( %s = '0002' AND %s = 'LA' )",
+ Customer, customerId, customerId, region),
+
db.from(model).where(new Or<Customer>(db, model) {{
+
or(model.customerId).is("0001");
+
or(new And<Customer>(db, model) {{
and(model.customerId).is("0002");
and(model.region).is("LA");
}});
- }}).toSQL(),
- String.format("SELECT * FROM %s WHERE (false) OR %s = '0001' OR ( (true) AND %s = '0002' AND %s = 'LA' )",
- Customer, customerId, customerId, region));
+
+ }})
+
+ .toSQL());
+
assertEquals(
+ String.format("SELECT * FROM %s WHERE (false) OR ( %s = '0001' AND %s = 'WA' ) OR ( %s = '0002' AND %s = 'LA' )",
+ Customer, customerId, region, customerId, region),
+
+ db.from(model).where(new Or<Customer>(db, model) {{
+
+ or(new And<Customer>(db, model) {{
+ and(model.customerId).is("0001");
+ and(model.region).is("WA");
+ }});
+
+ or(new And<Customer>(db, model) {{
+ and(model.customerId).is("0002");
+ and(model.region).is("LA");
+ }});
+
+ }})
+
+ .toSQL());
+
+ assertEquals(
+ String.format("SELECT * FROM %s WHERE %s = '0001' OR ( %s = '0002' AND %s = 'LA' )",
+ Customer, customerId, customerId, region),
+
+ db.from(model).where(model.customerId).is("0001")
+
+ .or(new And<Customer>(db, model) {{
+ and(model.customerId).is("0002");
+ and(model.region).is("LA");
+ }})
+
+ .toSQL());
+
+
+ assertEquals(
+ String.format("SELECT * FROM %s WHERE %s IS NOT NULL AND ( %s = 'LA' OR %s = 'CA' OR %s = 'WA' )",
+ Customer, customerId, region, region, region),
db.from(model)
.where(model.customerId).isNotNull()
+
.and(new Or<Customer>(db, model) {{
or(model.region).is("LA");
or(model.region).is("CA");
+ or(model.region).is("WA");
}})
- .and(model.region).isNotNull()
- .toSQL(),
- String.format("SELECT * FROM %s WHERE %s IS NOT NULL AND ( (false) OR %s = 'LA' OR %s = 'CA' ) AND %s IS NOT NULL",
- Customer, customerId, region, region, region));
+
+ .toSQL());
}
@Test