1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 package org.exoplatform.services.wcm.utils;
18
19 import java.util.Calendar;
20
21 import org.apache.commons.lang.StringUtils;
22 import org.exoplatform.commons.utils.ISO8601;
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37 public class SQLQueryBuilder extends AbstractQueryBuilder {
38
39
40
41
42
43
44
45 public void isNull(String propertyName, LOGICAL condition) {
46 if(condition == LOGICAL.AND)
47 propertiesClause.append(" AND").append(propertyName).append("IS NULL ");
48 else if(condition == LOGICAL.OR)
49 propertiesClause.append(" OR").append(propertyName).append("IS NULL ");
50 else
51 propertiesClause.append(propertyName).append("IS NULL ");
52 }
53
54
55
56
57
58
59
60
61 public void isNotNull(String propertyName, LOGICAL condition) {
62 if(condition == LOGICAL.AND)
63 propertiesClause.append("AND ").append(propertyName).append(" IS NOT NULL ");
64 else if(condition == LOGICAL.OR)
65 propertiesClause.append("OR ").append(propertyName).append(" IS NOT NULL ");
66 else
67 propertiesClause.append(propertyName).append(" IS NOT NULL ");
68 }
69
70
71
72
73
74
75
76
77 public void lessThan(String propertyName, String value, LOGICAL condition) {
78 comparison(propertyName,value,condition,"<");
79 }
80
81
82
83
84
85
86
87
88 public void greaterThan(String propName, String value, LOGICAL condition) {
89 comparison(propName,value,condition,">");
90 }
91
92
93
94
95
96
97
98
99 public void lessThanOrEqual(String propName, String value, LOGICAL condition) {
100 comparison(propName,value,condition,"<=");
101 }
102
103
104
105
106
107
108
109
110 public void greaterOrEqual(String propName,String value, LOGICAL condition) {
111 comparison(propName,value,condition,">=");
112 }
113
114
115
116
117
118
119
120
121 public void equal(String propName, String value , LOGICAL condition) {
122 comparison(propName,value,condition,"=");
123 }
124
125
126
127
128
129
130
131
132 public void notEqual(String propName, String value, LOGICAL condition) {
133 if(condition == LOGICAL.AND)
134 propertiesClause.append("AND ").append(propName).append(" <> '").append(value).append("' ");
135 else if(condition == LOGICAL.OR)
136 propertiesClause.append("OR ").append(propName).append(" <> '").append(value).append("' ");
137 else
138 propertiesClause.append(propName).append(" <> '").append(value).append("' ");
139 }
140
141
142
143
144
145
146
147
148
149 private void comparison(String propName, String value, LOGICAL condition, String symbol) {
150 if(condition == LOGICAL.AND)
151 propertiesClause.append("AND ").append(propName).append(" ").append(symbol).append(" '").append(value).append("' ");
152 else if(condition == LOGICAL.OR)
153 propertiesClause.append("OR ").append(propName).append(" ").append(symbol).append(" '").append(value).append("' ");
154 else
155 propertiesClause.append(propName).append(" ").append(symbol).append(" '").append(value).append("' ");
156 }
157
158
159
160
161
162
163
164
165 public void like(String propName, String value, LOGICAL condition) {
166 if(condition == LOGICAL.AND)
167 propertiesClause.append("AND ").append(propName).append(" LIKE '").append(value).append("%' ");
168 else if(condition == LOGICAL.OR)
169 propertiesClause.append("OR ").append(propName).append(" LIKE '").append(value).append("%' ");
170 else
171 propertiesClause.append(propName).append(" LIKE '").append(value).append("%' ");
172 }
173
174
175
176
177
178
179
180
181 public void reference(String propName, String value, LOGICAL condition) {
182 }
183
184
185
186
187
188
189
190
191 public void beforeDate(String propName, String comparedDate, LOGICAL condition) {
192 Calendar calendar = ISO8601.parse(comparedDate);
193 String time = calendar.getTime().toString();
194 if(condition == LOGICAL.AND)
195 propertiesClause.append("AND ").append(propName).append(" <= '").append(time).append("' ");
196 else if(condition == LOGICAL.OR)
197 propertiesClause.append("OR ").append(propName).append(" <= '").append(time).append("' ");
198 else
199
200 propertiesClause.append(propName).append(" <= '").append(time).append("' ");
201 }
202
203
204
205
206
207
208
209
210 public void afterDate(String propName, String comparedDate, LOGICAL condition) {
211 Calendar calendar = ISO8601.parse(comparedDate);
212 String time = calendar.getTime().toString();
213 if(condition == LOGICAL.AND)
214 propertiesClause.append("AND ").append(propName).append(" >= '").append(time).append("' ");
215 else if(condition == LOGICAL.OR)
216 propertiesClause.append("OR ").append(propName).append(" >= '").append(time).append("' ");
217 else
218 propertiesClause.append(propName).append(" >= '").append(time).append("' ");
219 }
220
221
222
223
224
225
226
227
228 public void betweenDates(String propName, String startDate, String endDate, LOGICAL condition) {
229 String startTime = ISO8601.parse(startDate).getTime().toString();
230 String endTime = ISO8601.parse(endDate).getTime().toString();
231 if (condition == LOGICAL.AND)
232 propertiesClause.append("AND ")
233 .append(propName)
234 .append(" between TIMESTAMP '")
235 .append(startTime)
236 .append("' and TIMESTAMP '")
237 .append(endTime)
238 .append("' ");
239 else if (condition == LOGICAL.OR)
240 propertiesClause.append("OR ")
241 .append(propName)
242 .append(" between TIMESTAMP '")
243 .append(startTime)
244 .append("' and TIMESTAMP '")
245 .append(endTime)
246 .append("' ");
247 else
248 propertiesClause.append(propName)
249 .append(" between TIMESTAMP '")
250 .append(startTime)
251 .append("' and TIMESTAMP '")
252 .append(endTime)
253 .append("' ");
254 }
255
256 public void betweenDates(String propName, Calendar startDate, Calendar endDate, LOGICAL condition) {
257 String startTime = ISO8601.format(startDate);
258 String endTime = ISO8601.format(endDate);
259 if (condition == LOGICAL.AND)
260 propertiesClause.append("AND ")
261 .append(propName)
262 .append(" between TIMESTAMP '")
263 .append(startTime)
264 .append("' and TIMESTAMP '")
265 .append(endTime)
266 .append("' ");
267 else if (condition == LOGICAL.OR)
268 propertiesClause.append("OR ")
269 .append(propName)
270 .append(" between TIMESTAMP '")
271 .append(startTime)
272 .append("' and TIMESTAMP '")
273 .append(endTime)
274 .append("' ");
275 else
276 propertiesClause.append(propName)
277 .append(" between TIMESTAMP '")
278 .append(startTime)
279 .append("' and TIMESTAMP '")
280 .append(endTime)
281 .append("' ");
282 }
283
284
285
286
287
288
289
290
291 public void setQueryPath(String path, PATH_TYPE pathtype) {
292 if(StringUtils.isEmpty(path)) return;
293 if (PATH_TYPE.EXACT == pathtype) {
294 if (path.indexOf("[%]") > 0)
295 pathClause = new StringBuilder().append("jcr:path LIKE '").append(path).append("' ");
296 else
297 pathClause = new StringBuilder().append("jcr:path = '").append(path).append("' ");
298 } else if (PATH_TYPE.CHILDNODES == pathtype) {
299 pathClause = new StringBuilder().append("jcr:path LIKE '")
300 .append(path)
301 .append("/%'")
302 .append("AND NOT jcr:path like '")
303 .append(path)
304 .append("/%/%' ");
305 } else if (PATH_TYPE.DECENDANTS == pathtype) {
306 pathClause = new StringBuilder().append("jcr:path LIKE '").append(path).append("/%' ");
307 } else if (PATH_TYPE.DECENDANTS_OR_SELFT == pathtype) {
308 pathClause = new StringBuilder().append("jcr:path LIKE '")
309 .append(path)
310 .append("'")
311 .append("OR jcr:path LIKE '")
312 .append(path)
313 .append("/%' ");
314 }
315 }
316
317
318
319
320
321
322
323
324 public void contains(String scope, String term, LOGICAL condition) {
325 if (scope == null)
326 scope = ".";
327 if (LOGICAL.AND == condition)
328 containsClause.append("AND CONTAINS(").append(scope).append(",'").append(term).append("') ");
329 else if (LOGICAL.OR == condition)
330 containsClause.append("OR CONTAINS(").append(scope).append(",'").append(term).append("') ");
331 else
332 containsClause.append("CONTAINS(").append(scope).append(",'").append(term).append("') ");
333 }
334
335
336
337
338
339
340
341
342 public void notContains(String scope, String term, LOGICAL condition) {
343 if (scope == null)
344 scope = ".";
345 if (LOGICAL.AND == condition)
346 containsClause.append("AND NOT CONTAINS(")
347 .append(scope)
348 .append(",'")
349 .append(term)
350 .append("') ");
351 else if (LOGICAL.OR == condition)
352 containsClause.append("OR NOT CONTAINS(")
353 .append(scope)
354 .append(",'")
355 .append(term)
356 .append("') ");
357 else
358 containsClause.append("NOT CONTAINS(").append(scope).append(",'").append(term).append("') ");
359 }
360
361
362
363
364 public void fromNodeTypes(String[] nodetypes) {
365 if(nodetypes == null) {
366 fromClause = new StringBuilder("FROM nt:base");
367 return;
368 }
369 fromClause = new StringBuilder("FROM ");
370 for(int i = 0; i<nodetypes.length; i++) {
371 fromClause.append(nodetypes[i]);
372 if(i<nodetypes.length-1)
373 fromClause.append(",");
374 }
375 }
376
377
378
379
380 public void selectTypes(String[] returnTypes) {
381 if(returnTypes == null) {
382 selectClause = new StringBuilder("SELECT * ");
383 return;
384 }
385 selectClause = new StringBuilder("SELECT ");
386 for(int i = 0; i<returnTypes.length; i++) {
387 selectClause.append(returnTypes[i]);
388 if(i<returnTypes.length-1)
389 selectClause.append(",");
390 selectClause.append(" ");
391 }
392 }
393
394
395
396
397
398
399
400 public void orderBy(String properyName, ORDERBY orderby) {
401 if(orderByClause.length()>0)
402 orderByClause = orderByClause.append(", ");
403 if(ORDERBY.ASC == orderby)
404 orderByClause.append(properyName).append(" ASC");
405 else
406 orderByClause.append(properyName).append(" DESC");
407 }
408
409
410
411
412 public void excerpt(boolean enable) {
413 if(enable)
414 excerptClause = new StringBuilder("excerpt(.)");
415 }
416
417
418
419
420 public void spellCheck(String value) {
421 }
422
423
424
425
426
427
428
429 public void openGroup(LOGICAL logical) {
430 if(LOGICAL.AND == logical)
431 propertiesClause = propertiesClause.append("AND( ");
432 else if(LOGICAL.OR == logical)
433 propertiesClause = propertiesClause.append("OR( ");
434 else if(LOGICAL.AND_NOT == logical)
435 propertiesClause = propertiesClause.append("AND NOT(");
436 else if(LOGICAL.OR_NOT == logical)
437 propertiesClause = propertiesClause.append("OR NOT(");
438 else
439 propertiesClause = propertiesClause.append("( ");
440 }
441
442
443
444
445 public void closeGroup() {
446 propertiesClause = propertiesClause.append(")");
447 }
448
449
450
451
452 public String createQueryStatement() {
453 StringBuffer statement = new StringBuffer();
454 statement = statement.append(selectClause.toString())
455 .append(fromClause.toString())
456 .append(" WHERE ");
457 if (containsClause.length() > 0) {
458 statement = statement.append(containsClause.toString());
459 if (pathClause.length() > 0) {
460 statement = statement.append("AND ").append(pathClause.toString());
461 }
462 } else {
463 if (pathClause.length() > 0) {
464 statement = statement.append(pathClause.toString());
465 }
466 }
467 if (propertiesClause.length() > 0) {
468 String propertiesStr = propertiesClause.toString();
469 if((propertiesStr.startsWith("AND") || propertiesStr.startsWith(" AND")) &&
470 statement.toString().endsWith("WHERE "))
471 propertiesStr = propertiesStr.substring(propertiesStr.lastIndexOf("AND") + 1, propertiesStr.length());
472 statement = statement.append(propertiesStr);
473 }
474 if (orderByClause.length() > 0) {
475 statement = statement.append("ORDER BY ").append(orderByClause.toString());
476 }
477 return statement.toString();
478 }
479
480
481
482
483
484
485
486 public void merge(AbstractQueryBuilder other) {
487 }
488
489 public void queryByNodeName(String rootPath, String nodeName) {
490 pathClause = new StringBuilder().append(" jcr:path LIKE '")
491 .append(rootPath)
492 .append("/%/")
493 .append(nodeName)
494 .append("' ")
495 .append(" or jcr:path like '")
496 .append(rootPath)
497 .append("/")
498 .append(nodeName)
499 .append("' ");
500 }
501 }