METAMODEL-1134: Fixed
authorKasper Sørensen <i.am.kasper.sorensen@gmail.com>
Sat, 28 Jan 2017 04:16:09 +0000 (20:16 -0800)
committerKasper Sørensen <i.am.kasper.sorensen@gmail.com>
Sat, 28 Jan 2017 04:16:09 +0000 (20:16 -0800)
Closes #139

CHANGES.md
core/src/main/java/org/apache/metamodel/query/FilterItem.java
core/src/main/java/org/apache/metamodel/query/OperatorType.java
core/src/main/java/org/apache/metamodel/query/OperatorTypeImpl.java
core/src/main/java/org/apache/metamodel/query/builder/AbstractFilterBuilder.java
core/src/main/java/org/apache/metamodel/query/builder/AbstractQueryFilterBuilder.java
core/src/main/java/org/apache/metamodel/query/builder/FilterBuilder.java
core/src/test/java/org/apache/metamodel/query/FilterItemTest.java
core/src/test/java/org/apache/metamodel/query/builder/WhereBuilderImplTest.java
jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/DefaultQueryRewriter.java
jdbc/src/test/java/org/apache/metamodel/jdbc/JdbcTestTemplates.java

index 427ac21..475de9f 100644 (file)
@@ -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.
index 6fb7578..18e44cf 100644 (file)
@@ -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");
         }
index 8c267d6..a0daccc 100644 (file)
@@ -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
index 0eafe7d..753dc37 100644 (file)
@@ -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;
index f14b4af..2cf91ef 100644 (file)
@@ -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);
     }
index 133bce0..6a0c900 100644 (file)
@@ -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);
     }
index 8ba1f62..a6cf57b 100644 (file)
@@ -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);
index 5024797..3b89d9d 100644 (file)
@@ -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));
index 68c7f72..f873d7d 100644 (file)
@@ -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
index 0dec7c1..90a2411 100644 (file)
@@ -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);
index f7d0cf2..f216a75 100644 (file)
@@ -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")