OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 67 | Rev 93 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
17 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5
 *
6
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9
 * language governing permissions and limitations under the License.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.sql.model;
15
 
16
import org.openconcerto.utils.CollectionUtils;
17
import org.openconcerto.utils.cc.ITransformer;
18
 
19
import java.util.ArrayList;
20
import java.util.Arrays;
21
import java.util.Collection;
22
import java.util.List;
41 ilm 23
import java.util.Map;
24
import java.util.Map.Entry;
17 ilm 25
 
26
import org.apache.commons.collections.functors.InstanceofPredicate;
27
 
28
/**
29
 * Une clause WHERE dans une requete SQL. Une clause peut être facilement combinée avec d'autre,
30
 * exemple : prenomPasVide.and(pasIndéfini).and(age_sup_3.or(assez_grand)).
31
 *
32
 * @author ILM Informatique 27 sept. 2004
33
 */
34
public class Where {
35
 
36
    static public final Where FALSE = Where.createRaw("1=0");
37
    static public final Where TRUE = Where.createRaw("1=1");
38
    static public final String NULL_IS_DATA_EQ = new String("===");
39
    static public final String NULL_IS_DATA_NEQ = new String("IS DISTINCT FROM");
40
 
41
    private static abstract class Combiner {
42
        public final Where combine(Where w1, Where w2) {
43
            if (w1 == null)
44
                return w2;
45
            else
46
                return this.combineNotNull(w1, w2);
47
        }
48
 
49
        protected abstract Where combineNotNull(Where w1, Where w2);
50
    }
51
 
52
    private static Combiner AndCombiner = new Combiner() {
53
        protected Where combineNotNull(Where where1, Where where2) {
54
            return where1.and(where2);
55
        }
56
    };
57
 
58
    private static Combiner OrCombiner = new Combiner() {
59
        protected Where combineNotNull(Where where1, Where where2) {
60
            return where1.or(where2);
61
        }
62
    };
63
 
64
    static private Where combine(Collection<Where> wheres, Combiner c) {
65
        Where res = null;
66
        for (final Where w : wheres) {
67
            res = c.combine(res, w);
68
        }
69
        return res;
70
    }
71
 
72
    static public Where and(Collection<Where> wheres) {
73
        return combine(wheres, AndCombiner);
74
    }
75
 
41 ilm 76
    static public Where and(final SQLTable t, final Map<String, ?> fields) {
77
        final List<Where> res = new ArrayList<Where>(fields.size());
78
        for (final Entry<String, ?> e : fields.entrySet()) {
79
            res.add(new Where(t.getField(e.getKey()), "=", e.getValue()));
80
        }
81
        return and(res);
82
    }
83
 
17 ilm 84
    static public Where or(Collection<Where> wheres) {
85
        return combine(wheres, OrCombiner);
86
    }
87
 
88
    /**
89
     * Permet de faire un ET entre 2 where.
90
     *
91
     * @param where1 le 1er, peut être <code>null</code>.
92
     * @param where2 le 2ème, peut être <code>null</code>.
93
     * @return le ET, peut être <code>null</code>.
94
     */
95
    static public Where and(Where where1, Where where2) {
96
        return AndCombiner.combine(where1, where2);
97
    }
98
 
99
    static public Where isNull(FieldRef ref) {
100
        return new Where(ref, "is", (Object) null);
101
    }
102
 
103
    static public Where isNotNull(FieldRef ref) {
104
        return new Where(ref, "is not", (Object) null);
105
    }
106
 
107
    static public Where createRaw(String clause, FieldRef... refs) {
83 ilm 108
        return createRaw(clause, Arrays.asList(refs));
17 ilm 109
    }
110
 
111
    static public Where createRaw(String clause, Collection<? extends FieldRef> refs) {
83 ilm 112
        if (clause == null)
113
            return null;
17 ilm 114
        return new Where(clause, refs);
115
    }
116
 
117
    /**
118
     * To create complex Where not possible with constructors.
119
     *
120
     * @param pattern a pattern to be passed to {@link SQLSelect#quote(String, Object...)}, eg
121
     *        "EXTRACT(YEAR FROM %n) = 3007".
122
     * @param params the params to be passed to <code>quote()</code>, eg [|MISSION.DATE_DBT|].
123
     * @return a new Where with the result from <code>quote()</code> as its clause, and all
124
     *         <code>FieldRef</code> in params as its fields, eg {EXTRACT(YEAR FROM "DATE_DBT") =
125
     *         3007 , |MISSION.DATE_DBT|}.
126
     */
127
    @SuppressWarnings("unchecked")
128
    static public Where quote(String pattern, Object... params) {
67 ilm 129
        return new Where(SQLSelect.quote(pattern, params), org.apache.commons.collections.CollectionUtils.select(Arrays.asList(params), new InstanceofPredicate(FieldRef.class)));
17 ilm 130
    }
131
 
132
    static private final String comparison(FieldRef ref, String op, String y) {
133
        if (op == NULL_IS_DATA_EQ || op == NULL_IS_DATA_NEQ) {
134
            return ref.getField().getServer().getSQLSystem().getSyntax().getNullIsDataComparison(ref.getFieldRef(), op == NULL_IS_DATA_EQ, y);
135
        } else {
136
            return ref.getFieldRef() + " " + op + " " + y;
137
        }
138
    }
139
 
67 ilm 140
    static private final String getInClause(FieldRef field1, final boolean in, final String inParens) {
141
        final String op = in ? " in (" : " not in (";
142
        return field1.getFieldRef() + op + inParens + ")";
143
    }
144
 
17 ilm 145
    private final List<FieldRef> fields;
146
    private String clause;
147
 
148
    {
149
        this.fields = new ArrayList<FieldRef>();
150
        this.clause = "";
151
    }
152
 
153
    public Where(FieldRef field1, String op, FieldRef field2) {
154
        this.fields.add(field1);
155
        this.fields.add(field2);
156
        this.clause = comparison(field1, op, field2.getFieldRef());
157
    }
158
 
159
    public Where(FieldRef field1, String op, int scalar) {
160
        this(field1, op, (Integer) scalar);
161
    }
162
 
163
    /**
164
     * Construct a clause like "field = 'hi'". Note: this method will try to rewrite "= null" and
165
     * "<> null" to "is null" and "is not null", treating null as a Java <code>null</code> (ie null
166
     * == null) and not as a SQL NULL (NULL != NULL), see PostgreSQL documentation section 9.2.
167
     * Comparison Operators. ATTN new Where(f, "=", null) will call
168
     * {@link #Where(FieldRef, String, FieldRef)}, you have to cast to Object.
169
     *
170
     * @param ref a field.
171
     * @param op an arbitrary operator.
172
     * @param o the object to compare <code>ref</code> to.
173
     */
174
    public Where(FieldRef ref, String op, Object o) {
175
        this.fields.add(ref);
176
        if (o == null) {
177
            if (op.trim().equals("="))
178
                op = "is";
179
            else if (op.trim().equals("<>"))
180
                op = "is not";
181
        }
182
        this.clause = comparison(ref, op, ref.getField().getType().toString(o));
183
    }
184
 
185
    /**
186
     * Crée une clause "field1 in (values)". Some databases won't accept empty values (impossible
187
     * where clause), so we return false.
188
     *
189
     * @param field1 le champs à tester.
190
     * @param values les valeurs.
191
     */
192
    public Where(final FieldRef field1, Collection<?> values) {
193
        this(field1, true, values);
194
    }
195
 
196
    /**
197
     * Construct a clause like "field1 not in (value, ...)".
198
     *
199
     * @param field1 le champs à tester.
200
     * @param in <code>true</code> for "in", <code>false</code> for "not in".
201
     * @param values les valeurs.
202
     */
203
    public Where(final FieldRef field1, final boolean in, Collection<?> values) {
204
        if (values.isEmpty()) {
205
            this.clause = in ? FALSE.getClause() : TRUE.getClause();
206
        } else {
207
            this.fields.add(field1);
67 ilm 208
            this.clause = getInClause(field1, in, CollectionUtils.join(values, ",", new ITransformer<Object, String>() {
17 ilm 209
                @Override
210
                public String transformChecked(Object input) {
211
                    return field1.getField().getType().toString(input);
212
                }
67 ilm 213
            }));
17 ilm 214
        }
215
    }
216
 
67 ilm 217
    public Where(final FieldRef field1, final boolean in, SQLSelect subQuery) {
218
        this.fields.add(field1);
219
        this.clause = getInClause(field1, in, subQuery.asString());
220
    }
221
 
17 ilm 222
    /**
223
     * Crée une clause "field BETWEEN borneInf AND borneSup".
224
     *
225
     * @param ref le champs à tester.
226
     * @param borneInf la valeur minimum.
227
     * @param borneSup la valeur maximum.
228
     */
229
    public Where(FieldRef ref, Object borneInf, Object borneSup) {
230
        final SQLField field1 = ref.getField();
231
        this.fields.add(ref);
232
        this.clause = ref.getFieldRef() + " BETWEEN " + field1.getType().toString(borneInf) + " AND " + field1.getType().toString(borneSup);
233
    }
234
 
235
    /**
236
     * Crée une clause pour que <code>ref</code> soit compris entre <code>bornInf</code> et
237
     * <code>bornSup</code>.
238
     *
239
     * @param ref a field, eg NAME.
240
     * @param borneInf the lower bound, eg "DOE".
241
     * @param infInclusive <code>true</code> if the lower bound should be included, eg
242
     *        <code>false</code> if "DOE" shouldn't match.
243
     * @param borneSup the upper bound, eg "SMITH".
244
     * @param supInclusive <code>true</code> if the upper bound should be included.
245
     */
246
    public Where(FieldRef ref, Object borneInf, boolean infInclusive, Object borneSup, boolean supInclusive) {
247
        this.fields.add(ref);
248
        final String infClause = new Where(ref, infInclusive ? ">=" : ">", borneInf).getClause();
249
        final String supClause = new Where(ref, supInclusive ? "<=" : "<", borneSup).getClause();
250
        this.clause = infClause + " AND " + supClause;
251
    }
252
 
253
    // raw ctor, see static methods
254
    private Where(String clause, Collection<? extends FieldRef> refs) {
255
        this.fields.addAll(refs);
256
        this.clause = clause;
257
    }
258
 
259
    private Where() {
260
        /* Pour combine() */
261
    }
262
 
263
    /**
264
     * Clone un Where.
265
     *
266
     * @param orig l'instance à cloner.
267
     */
268
    public Where(Where orig) {
269
        this(orig.clause, orig.fields);
270
    }
271
 
272
    public Where or(Where w) {
273
        return this.combine(w, "OR");
274
    }
275
 
276
    public Where and(Where w) {
277
        return this.combine(w, "AND");
278
    }
279
 
280
    public Where not() {
281
        final Where res = new Where(this);
282
        res.clause = "NOT (" + this.clause + ")";
283
        return res;
284
    }
285
 
286
    private Where combine(Where w, String op) {
287
        if (w == null)
288
            return this;
289
 
290
        Where res = new Where();
291
        res.fields.addAll(this.fields);
292
        res.fields.addAll(w.fields);
293
 
294
        res.clause = "(" + this.clause + ") " + op + " (" + w.clause + ")";
295
        return res;
296
    }
297
 
298
    /**
299
     * La clause.
300
     *
301
     * @return la clause.
302
     */
303
    public String getClause() {
304
        return this.clause;
305
    }
306
 
307
    /**
308
     * Les champs utilisés dans cette clause.
309
     *
310
     * @return a list of FieldRef.
311
     */
312
    public List<FieldRef> getFields() {
313
        return this.fields;
314
    }
315
 
316
    public String toString() {
317
        return this.getClause();
318
    }
319
 
320
    public boolean equals(Object obj) {
321
        if (obj instanceof Where) {
322
            Where o = ((Where) obj);
323
            return this.getClause().equals(o.getClause()) && this.getFields().equals(o.getFields());
324
        } else
325
            return false;
326
    }
327
 
328
    public int hashCode() {
329
        return this.getClause().hashCode() + this.getFields().hashCode();
330
    }
331
}