1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 package info.magnolia.cms.gui.controlx.search;
35
36 import info.magnolia.cms.gui.query.DateSearchQueryParameter;
37 import info.magnolia.cms.gui.query.SearchQueryExpression;
38 import info.magnolia.cms.gui.query.SearchQueryOperator;
39 import info.magnolia.cms.gui.query.StringSearchQueryParameter;
40
41 import java.text.DecimalFormat;
42 import java.util.Date;
43 import java.util.TimeZone;
44
45 import org.apache.commons.lang.StringUtils;
46 import org.apache.commons.lang.time.DateFormatUtils;
47
48
49
50
51
52
53
54 public class QueryBuilder {
55
56
57
58
59 private RepositorySearchListModel model;
60
61
62
63
64 private boolean useJCROrderBy = false;
65
66
67
68
69
70 public QueryBuilder(RepositorySearchListModel model) {
71 this.model = model;
72 }
73
74
75
76
77
78 public String getSQLStatement() {
79 StringBuffer statement = new StringBuffer("select * from ");
80 statement.append(this.model.getNodeType());
81
82 String where = buildWhereClause();
83 if (where.length() > 0) {
84 statement.append(" where ").append(where);
85 }
86 String orderBy = buildOrderByClause();
87 if (orderBy.length() > 0) {
88 statement.append(" order by ").append(orderBy);
89 }
90
91 return statement.toString();
92 }
93
94
95
96
97 protected String buildWhereClause() {
98 StringBuffer where = new StringBuffer();
99 if (StringUtils.isNotEmpty(this.model.getSearchPath())) {
100 where.append(" jcr:path like '");
101 where.append(this.model.getSearchPath());
102 where.append("%'");
103 }
104 if(this.model.getQuery() !=null){
105 where.append(buildWhereClause(this.model.getQuery().getRootExpression()));
106 }
107 return where.toString();
108 }
109
110
111
112
113 protected String buildOrderByClause() {
114 StringBuffer orderBy = new StringBuffer();
115 if(useJCROrderBy){
116 if (StringUtils.isNotEmpty(this.model.getGroupBy()) && StringUtils.isNotEmpty(this.model.getSortBy())) {
117 orderBy.append(this.model.getGroupBy());
118 orderBy.append(" ");
119 orderBy.append(this.model.getGroupByOrder());
120 orderBy.append(", ");
121 orderBy.append(this.model.getSortBy());
122 orderBy.append(" ");
123 orderBy.append(this.model.getSortByOrder());
124 }
125 else if (StringUtils.isNotEmpty(this.model.getGroupBy()) && StringUtils.isEmpty(this.model.getSortBy())) {
126 orderBy.append(this.model.getGroupBy());
127 orderBy.append(" ");
128 orderBy.append(this.model.getGroupByOrder());
129 }
130 else if (StringUtils.isEmpty(this.model.getGroupBy()) && StringUtils.isNotEmpty(this.model.getSortBy())) {
131 orderBy.append(this.model.getSortBy());
132 orderBy.append(" ");
133 orderBy.append(this.model.getSortByOrder());
134 }
135 }
136 return orderBy.toString();
137 }
138
139
140
141
142
143 protected String buildWhereClause(SearchQueryExpression expression) {
144 StringBuffer where = new StringBuffer();
145 if (expression != null) {
146 where.append(buildWhereClause(expression.getLeft()));
147 where.append(" ");
148 where.append(toJCRExpression(expression));
149 where.append(buildWhereClause(expression.getRight()));
150 }
151 return where.toString();
152 }
153
154
155
156
157
158
159 protected String toJCRExpression(SearchQueryExpression expression) {
160 if (expression instanceof SearchQueryOperator) {
161
162 return StringUtils.defaultString(((SearchQueryOperator) expression).getOperator());
163 }
164 else if (expression instanceof StringSearchQueryParameter) {
165 return toStringJCRExpression((StringSearchQueryParameter) expression);
166 }
167 else if (expression instanceof DateSearchQueryParameter) {
168 return toDateJCRExpression((DateSearchQueryParameter) expression);
169 }
170 return StringUtils.EMPTY;
171 }
172
173
174
175
176
177
178 protected String toDateJCRExpression(DateSearchQueryParameter param) {
179 Date date = param.getValue();
180 if (param.getConstraint().equalsIgnoreCase(DateSearchQueryParameter.TODAY)) {
181 date = new Date();
182 }
183
184 StringBuffer buffer = new StringBuffer();
185 buffer.append(param.getName());
186 if (param.getConstraint().equalsIgnoreCase(DateSearchQueryParameter.BEFORE)) {
187 buffer.append(" <= ");
188 }
189 else if (param.getConstraint().equalsIgnoreCase(DateSearchQueryParameter.AFTER)) {
190 buffer.append(" >= ");
191 }
192 else if (param.getConstraint().equalsIgnoreCase(DateSearchQueryParameter.IS)) {
193 buffer.append(" = ");
194 }
195
196 buffer.append("TIMESTAMP '");
197 buffer.append(DateFormatUtils.format(date, "yyyy-MM-dd"));
198 buffer.append("T00:00:00.000");
199
200 TimeZone timezone = TimeZone.getDefault();
201 int milis = Math.abs(timezone.getRawOffset());
202 if (milis == 0) {
203 buffer.append("Z");
204 }
205 else {
206 if (timezone.getRawOffset() > 0) {
207 buffer.append("+");
208 }
209 else {
210 buffer.append("-");
211 }
212
213 int hours = milis / (1000 * 60 * 60);
214 int minutes = (milis - hours * 1000 * 60 * 60) / (1000 * 60);
215 DecimalFormat format = new DecimalFormat("00");
216 buffer.append(format.format(hours)).append(":").append(format.format(minutes));
217 }
218 buffer.append("'");
219 return buffer.toString();
220 }
221
222
223
224
225
226 protected String toStringJCRExpression(StringSearchQueryParameter param) {
227 if (param.getConstraint().equals(StringSearchQueryParameter.CONTAINS)) {
228 return "contains(" + param.getName() + ",'" + param.getValue() + "*')";
229 }
230
231 else if (param.getConstraint().equals(StringSearchQueryParameter.CONTAINS_NOT)) {
232 return "not contains(" + param.getName() + ",'*" + param.getValue() + "*')";
233 }
234
235 else if (param.getConstraint().equals(StringSearchQueryParameter.ENDS)) {
236 return "contains(" + param.getName() + ",'*" + param.getValue() + "')";
237 }
238
239 else if (param.getConstraint().equals(StringSearchQueryParameter.STARTS)) {
240 return "contains(" + param.getName() + ",'" + param.getValue() + "*')";
241 }
242
243 else if (param.getConstraint().equals(StringSearchQueryParameter.IS)) {
244 return param.getName() + " = '" + param.getValue() + "'";
245 }
246
247 else if (param.getConstraint().equals(StringSearchQueryParameter.IS_NOT)) {
248 return param.getName() + " <> '" + param.getValue() + "'";
249 }
250 else {
251 return param.getName() + " " + param.getConstraint() + " '" + param.getValue() + "'";
252 }
253 }
254
255
256 public boolean isUseJCROrderBy() {
257 return this.useJCROrderBy;
258 }
259
260
261 public void setUseJCROrderBy(boolean useJCROrderBy) {
262 this.useJCROrderBy = useJCROrderBy;
263 }
264
265 }