WebSVN - OpenConcerto - Diff - Rev 149 and 182 - /trunk/OpenConcerto/src/org/openconcerto/sql/model/Where.java

 /*
  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
+ *
- * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
+ * Copyright 2011-2019 OpenConcerto, by ILM Informatique. All rights reserved.
+ *
  * The contents of this file are subject to the terms of the GNU General Public License Version 3
  * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
  * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
  * language governing permissions and limitations under the License.
  package org.openconcerto.sql.model;
 import org.openconcerto.utils.CollectionUtils;
 import org.openconcerto.utils.StringUtils;
-import org.openconcerto.utils.cc.ITransformer;
 import java.util.ArrayList;
 import java.util.Arrays;
 import java.util.Collection;
 import java.util.Collections;
 import java.util.List;
 import java.util.Map;
 import java.util.Map.Entry;
+import java.util.Objects;
-import org.apache.commons.collections.functors.InstanceofPredicate;
+import java.util.stream.Collectors;
 import net.jcip.annotations.Immutable;
 /**
  * Une clause WHERE dans une requete SQL. Une clause peut être facilement combinée avec d'autre,
     static public Where notInSubqueries(final FieldRef ref, final List<String> subQueries) {
         return subqueries(ref, false, subQueries);
+    }
+    static public Where inValues(final FieldRef ref, final Collection<?> values) {
+        return compareValues(ref, RowComparison.IN, values);
+    }
+    static public Where notInValues(final FieldRef ref, final Collection<?> values) {
+        return compareValues(ref, RowComparison.NOT_IN, values);
+    }
+    static public Where compareValues(final FieldRef ref, final RowComparison cmp, final Collection<?> values) {
+        return compareValues(ref, cmp, NullValue.IS_FORBIDDEN, values);
+    }
+    static public Where compareValues(final FieldRef ref, final RowComparison cmp, final NullValue nullMode, final Collection<?> values) {
+        if (values.isEmpty()) {
+            return cmp == RowComparison.IN ? FALSE : TRUE;
+        }
+        return createRaw(getCompareValuesClause(ref.getFieldRef(), cmp, nullMode, values, ref.getField().getType()), ref);
+    }
     /**
      * Create a Where for a field value contained or not contained in sub-queries.
+     *
      * @param ref the field.
      * @param in <code>true</code> if the field should be contained in the sub-queries.
+    }
     /**
      * To create complex Where not possible with constructors.
+     *
+     * @param s the syntax to use.
-     * @param pattern a pattern to be passed to {@link SQLSelect#quote(String, Object...)}, eg
+     * @param pattern a pattern to be passed to {@link SQLBase#quoteStd(String, Object...)}, eg
      *        "EXTRACT(YEAR FROM %n) = 3007".
      * @param params the params to be passed to <code>quote()</code>, eg [|MISSION.DATE_DBT|].
      * @return a new Where with the result from <code>quote()</code> as its clause, and all
      *         <code>FieldRef</code> in params as its fields, eg {EXTRACT(YEAR FROM "DATE_DBT") =
      *         3007 , |MISSION.DATE_DBT|}.
      */
+    static public Where quote(final SQLSyntax s, final String pattern, final Object... params) {
+        final List<FieldRef> fields = new ArrayList<>();
+        for (final Object param : params) {
-    @SuppressWarnings("unchecked")
+            if (param instanceof FieldRef) {
-    static public Where quote(final String pattern, final Object... params) {
+                fields.add((FieldRef) param);
+            }
+        }
-        return new Where(SQLSelect.quote(pattern, params), org.apache.commons.collections.CollectionUtils.select(Arrays.asList(params), new InstanceofPredicate(FieldRef.class)));
+        return new Where(SQLBase.quote(s, pattern, params), fields);
+    }
     static private final String normalizeOperator(final String op) {
         String res = op.trim();
         if (res.equals("!="))
             res = "<>";
         return res;
+    }
-    static private final String comparison(final FieldRef ref, final String op, final String y) {
+    static public final String comparison(final FieldRef ref, final String op, final String y) {
+        return comparison(null, ref.getField(), ref.getFieldRef(), op, y);
+    }
+    static public final String comparison(final SQLSyntax s, final String x, final String op, final String y) {
+        return comparison(Objects.requireNonNull(s, "Missing syntax"), null, x, op, y);
+    }
+    static private final String comparison(SQLSyntax s, final DBStructureItem<?> syntaxSupplier, final String x, final String op, final String y) {
+        Objects.requireNonNull(op, "Missing operator");
         if (op == NULL_IS_DATA_EQ || op == NULL_IS_DATA_NEQ) {
+            if (s == null)
+                s = syntaxSupplier.getDBSystemRoot().getSyntax();
-            return ref.getField().getDBSystemRoot().getSyntax().getNullIsDataComparison(ref.getFieldRef(), op == NULL_IS_DATA_EQ, y);
+            return s.getNullIsDataComparison(x, op == NULL_IS_DATA_EQ, y);
         } else {
-            return ref.getFieldRef() + " " + op + " " + y;
+            return x + ' ' + op + ' ' + y;
+        }
+    }
     static private final String getInClause(final FieldRef field1, final boolean in, final String inParens) {
+        return getInClause(field1.getFieldRef(), in ? RowComparison.IN : RowComparison.NOT_IN, inParens);
+    }
+    static private final String getInClause(final String expr, final RowComparison cmp, final String inParens) {
-        final String op = in ? " in (" : " not in (";
+        final String op = cmp == RowComparison.IN ? " in (" : " not in (";
-        return field1.getFieldRef() + op + inParens + ")";
+        return expr + op + inParens + ')';
+    }
+    static public enum RowComparison {
+        IN, NOT_IN
+    }
+    static public enum NullValue {
+        IS_DATA, IS_FORBIDDEN, IS_UNKNOWN
+    }
+    static public final String getCompareValuesClause(final String expr, final RowComparison cmp, final Collection<?> values, final SQLType type) {
+        return getCompareValuesClause(expr, cmp, NullValue.IS_FORBIDDEN, values, type);
+    }
+    static public final String getCompareValuesClause(final String expr, final RowComparison cmp, final NullValue nullMode, Collection<?> values, final SQLType type) {
+        final boolean addNull;
+        if (nullMode != NullValue.IS_UNKNOWN && values.contains(null)) {
+            if (nullMode == NullValue.IS_FORBIDDEN)
+                throw new IllegalArgumentException("Values contains a null value : " + values);
+            assert nullMode == NullValue.IS_DATA;
+            addNull = true;
+        } else {
+            addNull = false;
+        }
+        if (addNull) {
+            values = values.stream().filter((i) -> i != null).collect(Collectors.toList());
+        }
+        String res = getInClause(expr, cmp, CollectionUtils.join(values, ",", (input) -> type.toString(input)));
+        if (addNull) {
+            if (cmp == RowComparison.IN) {
+                res = expr + " is null or " + res;
+            } else {
+                res = expr + " is not null and " + res;
+            }
+        }
+        return res;
+    }
     private final List<FieldRef> fields;
     private final String clause;
      * Crée une clause "field1 in (values)". Some databases won't accept empty values (impossible
      * where clause), so we return false.
+     *
      * @param field1 le champs à tester.
      * @param values les valeurs.
+     * @deprecated use {@link #inValues(FieldRef, Collection)}
      */
     public Where(final FieldRef field1, final Collection<?> values) {
         this(field1, true, values);
+    }
      * Construct a clause like "field1 not in (value, ...)".
+     *
      * @param field1 le champs à tester.
      * @param in <code>true</code> for "in", <code>false</code> for "not in".
      * @param values les valeurs.
+     * @deprecated use {@link #inValues(FieldRef, Collection)} or
+     *             {@link #notInValues(FieldRef, Collection)}
      */
     public Where(final FieldRef field1, final boolean in, final Collection<?> values) {
         if (values.isEmpty()) {
             this.fields = Collections.emptyList();
             this.clause = in ? FALSE.getClause() : TRUE.getClause();
         } else {
             this.fields = Collections.singletonList(field1);
-            this.clause = getInClause(field1, in, CollectionUtils.join(values, ",", new ITransformer<Object, String>() {
+            this.clause = getCompareValuesClause(field1.getFieldRef(), in ? RowComparison.IN : RowComparison.NOT_IN, values, field1.getField().getType());
-                @Override
-                public String transformChecked(final Object input) {
-                    return field1.getField().getType().toString(input);
-                }
-            }));
+        }
+    }
     public Where(final FieldRef field1, final boolean in, final SQLSelect subQuery) {
         this.fields = Collections.singletonList(field1);

Dépôt officiel du code source de l'ERP OpenConcerto

svn://code.openconcerto.org/openconcerto

(root)/trunk/OpenConcerto/src/org/openconcerto/sql/model/Where.java - Rev 149 → 182