You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@metamodel.apache.org by ka...@apache.org on 2017/05/10 03:54:38 UTC
[36/43] metamodel git commit: METAMODEL-1134: Fixed
METAMODEL-1134: Fixed
Closes #139
Project: http://git-wip-us.apache.org/repos/asf/metamodel/repo
Commit: http://git-wip-us.apache.org/repos/asf/metamodel/commit/41a708f0
Tree: http://git-wip-us.apache.org/repos/asf/metamodel/tree/41a708f0
Diff: http://git-wip-us.apache.org/repos/asf/metamodel/diff/41a708f0
Branch: refs/heads/5.x
Commit: 41a708f04a1189ff23c60fee396f0e2b21e50d35
Parents: 95fa617
Author: Kasper Sørensen <i....@gmail.com>
Authored: Fri Jan 27 20:16:09 2017 -0800
Committer: Kasper Sørensen <i....@gmail.com>
Committed: Fri Jan 27 20:16:09 2017 -0800
----------------------------------------------------------------------
CHANGES.md | 4 ++
.../org/apache/metamodel/query/FilterItem.java | 12 +++-
.../apache/metamodel/query/OperatorType.java | 6 +-
.../metamodel/query/OperatorTypeImpl.java | 74 +++++++++++---------
.../query/builder/AbstractFilterBuilder.java | 24 +++++++
.../builder/AbstractQueryFilterBuilder.java | 21 ++++++
.../metamodel/query/builder/FilterBuilder.java | 22 ++++++
.../apache/metamodel/query/FilterItemTest.java | 28 ++++++++
.../query/builder/WhereBuilderImplTest.java | 28 ++++++++
.../jdbc/dialects/DefaultQueryRewriter.java | 5 +-
.../metamodel/jdbc/JdbcTestTemplates.java | 22 ++++++
11 files changed, 206 insertions(+), 40 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/metamodel/blob/41a708f0/CHANGES.md
----------------------------------------------------------------------
diff --git a/CHANGES.md b/CHANGES.md
index 427ac21..475de9f 100644
--- a/CHANGES.md
+++ b/CHANGES.md
@@ -1,3 +1,7 @@
+### Apache MetaModel [wip]
+
+ * [METAMODEL-1134] - Added NOT IN and NOT LIKE operators to WHERE filters.
+
### Apache MetaModel 4.5.5
* [METAMODEL-1132] - Support native paging on SQL Server and Oracle database.
http://git-wip-us.apache.org/repos/asf/metamodel/blob/41a708f0/core/src/main/java/org/apache/metamodel/query/FilterItem.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/metamodel/query/FilterItem.java b/core/src/main/java/org/apache/metamodel/query/FilterItem.java
index 6fb7578..18e44cf 100644
--- a/core/src/main/java/org/apache/metamodel/query/FilterItem.java
+++ b/core/src/main/java/org/apache/metamodel/query/FilterItem.java
@@ -60,7 +60,7 @@ public class FilterItem extends BaseObject implements QueryItem, Cloneable, IRow
* Private constructor, used for cloning
*/
private FilterItem(SelectItem selectItem, OperatorType operator, Object operand, List<FilterItem> orItems,
- String expression, LogicalOperator logicalOperator) {
+ String expression, LogicalOperator logicalOperator) {
_selectItem = selectItem;
_operator = operator;
_operand = validateOperand(operand);
@@ -103,7 +103,7 @@ public class FilterItem extends BaseObject implements QueryItem, Cloneable, IRow
require("Can only use EQUALS or DIFFERENT_FROM operator with null-operand",
_operator == OperatorType.DIFFERENT_FROM || _operator == OperatorType.EQUALS_TO);
}
- if (_operator == OperatorType.LIKE) {
+ if (_operator == OperatorType.LIKE || _operator == OperatorType.NOT_LIKE) {
ColumnType type = _selectItem.getColumn().getType();
if (type != null) {
require("Can only use LIKE operator with strings", type.isLiteral()
@@ -295,7 +295,7 @@ public class FilterItem extends BaseObject implements QueryItem, Cloneable, IRow
sb.append(operator.toSql());
sb.append(' ');
- if (operator == OperatorType.IN) {
+ if (operator == OperatorType.IN || operator == OperatorType.NOT_IN) {
operand = CollectionUtils.toList(operand);
}
return operand;
@@ -375,9 +375,15 @@ public class FilterItem extends BaseObject implements QueryItem, Cloneable, IRow
} else if (_operator == OperatorType.LIKE) {
WildcardPattern matcher = new WildcardPattern((String) operandValue, '%');
return matcher.matches((String) selectItemValue);
+ } else if (_operator == OperatorType.NOT_LIKE) {
+ WildcardPattern matcher = new WildcardPattern((String) operandValue, '%');
+ return !matcher.matches((String) selectItemValue);
} else if (_operator == OperatorType.IN) {
Set<?> inValues = getInValues();
return inValues.contains(selectItemValue);
+ } else if (_operator == OperatorType.NOT_IN) {
+ Set<?> inValues = getInValues();
+ return !inValues.contains(selectItemValue);
} else {
throw new IllegalStateException("Operator could not be determined");
}
http://git-wip-us.apache.org/repos/asf/metamodel/blob/41a708f0/core/src/main/java/org/apache/metamodel/query/OperatorType.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/metamodel/query/OperatorType.java b/core/src/main/java/org/apache/metamodel/query/OperatorType.java
index 8c267d6..a0daccc 100644
--- a/core/src/main/java/org/apache/metamodel/query/OperatorType.java
+++ b/core/src/main/java/org/apache/metamodel/query/OperatorType.java
@@ -33,6 +33,8 @@ public interface OperatorType extends Serializable {
public static final OperatorType LIKE = new OperatorTypeImpl("LIKE", true);
+ public static final OperatorType NOT_LIKE = new OperatorTypeImpl("NOT LIKE", true);
+
public static final OperatorType GREATER_THAN = new OperatorTypeImpl(">", false);
public static final OperatorType GREATER_THAN_OR_EQUAL = new OperatorTypeImpl(">=", false);
@@ -43,8 +45,10 @@ public interface OperatorType extends Serializable {
public static final OperatorType IN = new OperatorTypeImpl("IN", true);
+ public static final OperatorType NOT_IN = new OperatorTypeImpl("NOT IN", true);
+
public static final OperatorType[] BUILT_IN_OPERATORS = new OperatorType[] { EQUALS_TO, DIFFERENT_FROM, LIKE,
- GREATER_THAN, GREATER_THAN_OR_EQUAL, LESS_THAN, LESS_THAN_OR_EQUAL, IN };
+ NOT_LIKE, GREATER_THAN, GREATER_THAN_OR_EQUAL, LESS_THAN, LESS_THAN_OR_EQUAL, IN, NOT_IN };
/**
* Determines if this operator requires a space delimitor. Operators that are written using letters usually require
http://git-wip-us.apache.org/repos/asf/metamodel/blob/41a708f0/core/src/main/java/org/apache/metamodel/query/OperatorTypeImpl.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/metamodel/query/OperatorTypeImpl.java b/core/src/main/java/org/apache/metamodel/query/OperatorTypeImpl.java
index 0eafe7d..753dc37 100644
--- a/core/src/main/java/org/apache/metamodel/query/OperatorTypeImpl.java
+++ b/core/src/main/java/org/apache/metamodel/query/OperatorTypeImpl.java
@@ -75,40 +75,46 @@ public class OperatorTypeImpl implements OperatorType {
if (sqlType != null) {
sqlType = sqlType.trim().toUpperCase();
switch (sqlType) {
- case "=":
- case "==":
- case "EQ":
- case "EQUALS_TO":
- return OperatorType.EQUALS_TO;
- case "<>":
- case "!=":
- case "NE":
- case "NOT_EQUAL":
- case "NOT_EQUAL_TO":
- case "NOT_EQUALS":
- case "NOT_EQUALS_TO":
- case "DIFFERENT_FROM":
- return OperatorType.DIFFERENT_FROM;
- case ">":
- case "GT":
- case "GREATER_THAN":
- return OperatorType.GREATER_THAN;
- case ">=":
- case "=>":
- case "GREATER_THAN_OR_EQUAL":
- return OperatorType.GREATER_THAN_OR_EQUAL;
- case "IN":
- return OperatorType.IN;
- case "<":
- case "LT":
- case "LESS_THAN":
- return OperatorType.LESS_THAN;
- case "<=":
- case "=<":
- case "LESS_THAN_OR_EQUAL":
- return OperatorType.LESS_THAN_OR_EQUAL;
- case "LIKE":
- return OperatorType.LIKE;
+ case "=":
+ case "==":
+ case "EQ":
+ case "EQUALS_TO":
+ return OperatorType.EQUALS_TO;
+ case "<>":
+ case "!=":
+ case "NE":
+ case "NOT_EQUAL":
+ case "NOT_EQUAL_TO":
+ case "NOT_EQUALS":
+ case "NOT_EQUALS_TO":
+ case "DIFFERENT_FROM":
+ return OperatorType.DIFFERENT_FROM;
+ case ">":
+ case "GT":
+ case "GREATER_THAN":
+ return OperatorType.GREATER_THAN;
+ case ">=":
+ case "=>":
+ case "GREATER_THAN_OR_EQUAL":
+ return OperatorType.GREATER_THAN_OR_EQUAL;
+ case "NOT_IN":
+ case "NOT IN":
+ return OperatorType.NOT_IN;
+ case "IN":
+ return OperatorType.IN;
+ case "<":
+ case "LT":
+ case "LESS_THAN":
+ return OperatorType.LESS_THAN;
+ case "<=":
+ case "=<":
+ case "LESS_THAN_OR_EQUAL":
+ return OperatorType.LESS_THAN_OR_EQUAL;
+ case "LIKE":
+ return OperatorType.LIKE;
+ case "NOT_LIKE":
+ case "NOT LIKE":
+ return OperatorType.NOT_LIKE;
}
}
return null;
http://git-wip-us.apache.org/repos/asf/metamodel/blob/41a708f0/core/src/main/java/org/apache/metamodel/query/builder/AbstractFilterBuilder.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/metamodel/query/builder/AbstractFilterBuilder.java b/core/src/main/java/org/apache/metamodel/query/builder/AbstractFilterBuilder.java
index f14b4af..2cf91ef 100644
--- a/core/src/main/java/org/apache/metamodel/query/builder/AbstractFilterBuilder.java
+++ b/core/src/main/java/org/apache/metamodel/query/builder/AbstractFilterBuilder.java
@@ -64,6 +64,21 @@ public abstract class AbstractFilterBuilder<B> implements FilterBuilder<B> {
}
@Override
+ public B notIn(Collection<?> values) {
+ return applyFilter(new FilterItem(_selectItem, OperatorType.NOT_IN, values));
+ }
+
+ @Override
+ public B notIn(Number... numbers) {
+ return applyFilter(new FilterItem(_selectItem, OperatorType.NOT_IN, numbers));
+ }
+
+ @Override
+ public B notIn(String... strings) {
+ return applyFilter(new FilterItem(_selectItem, OperatorType.NOT_IN, strings));
+ }
+
+ @Override
public B isNull() {
return applyFilter(new FilterItem(_selectItem, OperatorType.EQUALS_TO, null));
}
@@ -460,6 +475,15 @@ public abstract class AbstractFilterBuilder<B> implements FilterBuilder<B> {
}
@Override
+ public B notLike(String string) {
+ if (string == null) {
+ throw new IllegalArgumentException("string cannot be null");
+ }
+ return applyFilter(new FilterItem(_selectItem, OperatorType.NOT_LIKE, string));
+ }
+
+
+ @Override
public B gt(Column column) {
return greaterThan(column);
}
http://git-wip-us.apache.org/repos/asf/metamodel/blob/41a708f0/core/src/main/java/org/apache/metamodel/query/builder/AbstractQueryFilterBuilder.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/metamodel/query/builder/AbstractQueryFilterBuilder.java b/core/src/main/java/org/apache/metamodel/query/builder/AbstractQueryFilterBuilder.java
index 133bce0..6a0c900 100644
--- a/core/src/main/java/org/apache/metamodel/query/builder/AbstractQueryFilterBuilder.java
+++ b/core/src/main/java/org/apache/metamodel/query/builder/AbstractQueryFilterBuilder.java
@@ -57,6 +57,22 @@ abstract class AbstractQueryFilterBuilder<B> extends GroupedQueryBuilderCallback
}
@Override
+ public B notIn(Collection<?> values) {
+ return _filterBuilder.notIn(values);
+ }
+
+ @Override
+ public B notIn(Number... numbers) {
+ return _filterBuilder.notIn(numbers);
+ }
+
+ @Override
+ public B notIn(String... strings) {
+ return _filterBuilder.notIn(strings);
+ }
+
+
+ @Override
public B isNull() {
return _filterBuilder.isNull();
}
@@ -305,6 +321,11 @@ abstract class AbstractQueryFilterBuilder<B> extends GroupedQueryBuilderCallback
}
@Override
+ public B notLike(String string) {
+ return _filterBuilder.notLike(string);
+ }
+
+ @Override
public B gt(Column column) {
return greaterThan(column);
}
http://git-wip-us.apache.org/repos/asf/metamodel/blob/41a708f0/core/src/main/java/org/apache/metamodel/query/builder/FilterBuilder.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/metamodel/query/builder/FilterBuilder.java b/core/src/main/java/org/apache/metamodel/query/builder/FilterBuilder.java
index 8ba1f62..a6cf57b 100644
--- a/core/src/main/java/org/apache/metamodel/query/builder/FilterBuilder.java
+++ b/core/src/main/java/org/apache/metamodel/query/builder/FilterBuilder.java
@@ -57,6 +57,21 @@ public interface FilterBuilder<B> {
public B in(String... strings);
/**
+ * Not in ...
+ */
+ public B notIn(Collection<?> values);
+
+ /**
+ * Not in ...
+ */
+ public B notIn(Number... numbers);
+
+ /**
+ * Not in ...
+ */
+ public B notIn(String... strings);
+
+ /**
* Like ...
*
* (use '%' as wildcard).
@@ -64,6 +79,13 @@ public interface FilterBuilder<B> {
public B like(String string);
/**
+ * Not like ...
+ *
+ * (use '%' as wildcard).
+ */
+ public B notLike(String string);
+
+ /**
* Equal to ...
*/
public B eq(Column column);
http://git-wip-us.apache.org/repos/asf/metamodel/blob/41a708f0/core/src/test/java/org/apache/metamodel/query/FilterItemTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/metamodel/query/FilterItemTest.java b/core/src/test/java/org/apache/metamodel/query/FilterItemTest.java
index 5024797..3b89d9d 100644
--- a/core/src/test/java/org/apache/metamodel/query/FilterItemTest.java
+++ b/core/src/test/java/org/apache/metamodel/query/FilterItemTest.java
@@ -428,6 +428,34 @@ public class FilterItemTest extends TestCase {
assertEquals("foo IN ()", new FilterItem(selectItem, OperatorType.IN, operand).toSql());
}
+ public void testNotInOperandSql() throws Exception {
+ SelectItem selectItem = new SelectItem("foo", "foo");
+ Object operand = new String[] { "foo", "bar" };
+ assertEquals("foo NOT IN ('foo' , 'bar')", new FilterItem(selectItem, OperatorType.NOT_IN, operand).toSql());
+
+ operand = Arrays.asList("foo", "bar", "baz");
+ assertEquals("foo NOT IN ('foo' , 'bar' , 'baz')", new FilterItem(selectItem, OperatorType.NOT_IN, operand).toSql());
+
+ operand = "foo";
+ assertEquals("foo NOT IN ('foo')", new FilterItem(selectItem, OperatorType.NOT_IN, operand).toSql());
+
+ operand = new ArrayList<Object>();
+ assertEquals("foo NOT IN ()", new FilterItem(selectItem, OperatorType.NOT_IN, operand).toSql());
+ }
+
+ public void testNotLikeOperandSql() throws Exception {
+ Column column = new MutableColumn("foo");
+ SelectItem selectItem = new SelectItem(column);
+ String operand = "%foo";
+ assertEquals("foo NOT LIKE '%foo'", new FilterItem(selectItem, OperatorType.NOT_LIKE, operand).toSql());
+
+ operand = "foo%";
+ assertEquals("foo NOT LIKE 'foo%'", new FilterItem(selectItem, OperatorType.NOT_LIKE, operand).toSql());
+
+ operand = "%foo%foo%";
+ assertEquals("foo NOT LIKE '%foo%foo%'", new FilterItem(selectItem, OperatorType.NOT_LIKE, operand).toSql());
+ }
+
public void testInOperandEvaluate() throws Exception {
SelectItem selectItem = new SelectItem(new MutableColumn("foo", ColumnType.VARCHAR, null, 1, null, null, true,
null, false, null));
http://git-wip-us.apache.org/repos/asf/metamodel/blob/41a708f0/core/src/test/java/org/apache/metamodel/query/builder/WhereBuilderImplTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/metamodel/query/builder/WhereBuilderImplTest.java b/core/src/test/java/org/apache/metamodel/query/builder/WhereBuilderImplTest.java
index 68c7f72..f873d7d 100644
--- a/core/src/test/java/org/apache/metamodel/query/builder/WhereBuilderImplTest.java
+++ b/core/src/test/java/org/apache/metamodel/query/builder/WhereBuilderImplTest.java
@@ -49,6 +49,13 @@ public class WhereBuilderImplTest extends TestCase {
query.toSql());
}
+ public void testNotLike() throws Exception {
+ whereBuilder.eq(true).or(col2).notLike("%test%case%");
+
+ assertEquals(" WHERE (col1 = TRUE OR col2 NOT LIKE '%test%case%')",
+ query.toSql());
+ }
+
public void testAnd() throws Exception {
whereBuilder.differentFrom(true).and(col2).eq(1).or(col2).eq(2)
.or(col2).eq(3).and(new MutableColumn("col3")).eq(4);
@@ -65,12 +72,25 @@ public class WhereBuilderImplTest extends TestCase {
query.toSql());
}
+ public void testNotInStringArray() throws Exception {
+ whereBuilder.eq(true).or(col2).notIn("foo", "bar");
+
+ assertEquals(" WHERE (col1 = TRUE OR col2 NOT IN ('foo' , 'bar'))",
+ query.toSql());
+ }
+
public void testInNumberArray() throws Exception {
whereBuilder.eq(true).or(col2).in(3, 1);
assertEquals(" WHERE (col1 = TRUE OR col2 IN (3 , 1))", query.toSql());
}
+ public void testNotInNumberArray() throws Exception {
+ whereBuilder.eq(true).or(col2).notIn(3, 1);
+
+ assertEquals(" WHERE (col1 = TRUE OR col2 NOT IN (3 , 1))", query.toSql());
+ }
+
public void testInCollection() throws Exception {
Collection<?> col = Arrays.asList("foo", "bar");
whereBuilder.eq(true).or(col2).in(col);
@@ -78,4 +98,12 @@ public class WhereBuilderImplTest extends TestCase {
assertEquals(" WHERE (col1 = TRUE OR col2 IN ('foo' , 'bar'))",
query.toSql());
}
+
+ public void testNotInCollection() throws Exception {
+ Collection<?> col = Arrays.asList("foo", "bar");
+ whereBuilder.eq(true).or(col2).notIn(col);
+
+ assertEquals(" WHERE (col1 = TRUE OR col2 NOT IN ('foo' , 'bar'))",
+ query.toSql());
+ }
}
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/metamodel/blob/41a708f0/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DefaultQueryRewriter.java
----------------------------------------------------------------------
diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DefaultQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DefaultQueryRewriter.java
index 0dec7c1..90a2411 100644
--- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DefaultQueryRewriter.java
+++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DefaultQueryRewriter.java
@@ -137,9 +137,10 @@ public class DefaultQueryRewriter extends AbstractQueryRewriter {
return rewriteFilterItemWithOperandLiteral(item, timestampLiteral);
} else if (operand instanceof Iterable || operand.getClass().isArray()) {
// operand is a set of values (typically in combination with an
- // IN operator). Each individual element must be escaped.
+ // IN or NOT IN operator). Each individual element must be escaped.
- assert OperatorType.IN.equals(item.getOperator());
+ assert OperatorType.IN.equals(item.getOperator()) ||
+ OperatorType.NOT_IN.equals(item.getOperator());
@SuppressWarnings("unchecked")
final List<Object> elements = (List<Object>) CollectionUtils.toList(operand);
http://git-wip-us.apache.org/repos/asf/metamodel/blob/41a708f0/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java
----------------------------------------------------------------------
diff --git a/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java b/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java
index f7d0cf2..f216a75 100644
--- a/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java
+++ b/jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java
@@ -205,6 +205,13 @@ public class JdbcTestTemplates {
assertFalse(ds.next());
ds.close();
+ // NOT LIKE
+ ds = dc.query().from(schema.getTableByName("test_table")).selectCount().where("code").notLike("%1").execute();
+ assertTrue(ds.next());
+ assertEquals("2", ds.getRow().getValue(0).toString());
+ assertFalse(ds.next());
+ ds.close();
+
// regular IN (with string)
ds = dc.query().from(schema.getTableByName("test_table")).selectCount().where("code").in("C01", "C02")
.execute();
@@ -220,6 +227,21 @@ public class JdbcTestTemplates {
assertFalse(ds.next());
ds.close();
+ // regular NOT IN (with string)
+ ds = dc.query().from(schema.getTableByName("test_table")).selectCount().where("code").notIn("C01", "C02")
+ .execute();
+ assertTrue(ds.next());
+ assertEquals("1", ds.getRow().getValue(0).toString());
+ assertFalse(ds.next());
+ ds.close();
+
+ // regular NOT IN (with decimals)
+ ds = dc.query().from(schema.getTableByName("test_table")).selectCount().where("id").notIn(1.0, 2.0, 4.0).execute();
+ assertTrue(ds.next());
+ assertEquals("1", ds.getRow().getValue(0).toString());
+ assertFalse(ds.next());
+ ds.close();
+
// irregular IN (with null value) - (currently uses SQL's standard way
// of understanding NULL - see ticket #1058)
Query query = dc.query().from(schema.getTableByName("test_table")).selectCount().where("code")