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.am.kasper.soren...@gmail.com> Authored: Fri Jan 27 20:16:09 2017 -0800 Committer: Kasper Sørensen <i.am.kasper.soren...@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")
