1
22
23 package com.liferay.util.dao.orm;
24
25 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
26 import com.liferay.portal.kernel.log.Log;
27 import com.liferay.portal.kernel.log.LogFactoryUtil;
28 import com.liferay.portal.kernel.util.GetterUtil;
29 import com.liferay.portal.kernel.util.OrderByComparator;
30 import com.liferay.portal.kernel.util.PortalClassLoaderUtil;
31 import com.liferay.portal.kernel.util.StringPool;
32 import com.liferay.portal.kernel.util.StringUtil;
33 import com.liferay.portal.kernel.util.Validator;
34 import com.liferay.portal.kernel.xml.Document;
35 import com.liferay.portal.kernel.xml.Element;
36 import com.liferay.portal.kernel.xml.SAXReaderUtil;
37 import com.liferay.portal.util.PortalUtil;
38
39 import java.io.BufferedReader;
40 import java.io.IOException;
41 import java.io.StringReader;
42
43 import java.sql.Connection;
44 import java.sql.DatabaseMetaData;
45 import java.sql.SQLException;
46
47 import java.util.HashMap;
48 import java.util.Iterator;
49 import java.util.Map;
50 import java.util.Properties;
51
52
59 public class CustomSQL {
60
61 public static final String DB2_FUNCTION_IS_NULL =
62 "CAST(? AS VARCHAR(32672)) IS NULL";
63
64 public static final String DB2_FUNCTION_IS_NOT_NULL =
65 "CAST(? AS VARCHAR(32672)) IS NOT NULL";
66
67 public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
68
69 public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
70 "NOT " + INFORMIX_FUNCTION_IS_NULL;
71
72 public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
73
74 public static final String MYSQL_FUNCTION_IS_NOT_NULL =
75 "IFNULL(?, '1') = '0'";
76
77 public static final String SYBASE_FUNCTION_IS_NULL = "ISNULL(?, '1') = '1'";
78
79 public static final String SYBASE_FUNCTION_IS_NOT_NULL =
80 "ISNULL(?, '1') = '0'";
81
82 public CustomSQL() throws SQLException {
83 Connection con = DataAccess.getConnection();
84
85 String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
86 String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
87
88 try {
89 if (Validator.isNotNull(functionIsNull) &&
90 Validator.isNotNull(functionIsNotNull)) {
91
92 _functionIsNull = functionIsNull;
93 _functionIsNotNull = functionIsNotNull;
94
95 if (_log.isDebugEnabled()) {
96 _log.info(
97 "functionIsNull is manually set to " + functionIsNull);
98 _log.info(
99 "functionIsNotNull is manually set to " +
100 functionIsNotNull);
101 }
102 }
103 else if (con != null) {
104 DatabaseMetaData metaData = con.getMetaData();
105
106 String dbName = GetterUtil.getString(
107 metaData.getDatabaseProductName());
108
109 if (_log.isInfoEnabled()) {
110 _log.info("Database name " + dbName);
111 }
112
113 if (dbName.startsWith("DB2")) {
114 _vendorDB2 = true;
115 _functionIsNull = DB2_FUNCTION_IS_NULL;
116 _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL;
117
118 if (_log.isInfoEnabled()) {
119 _log.info("Detected DB2 with database name " + dbName);
120 }
121 }
122 else if (dbName.startsWith("Informix")) {
123 _vendorInformix = true;
124 _functionIsNull = INFORMIX_FUNCTION_IS_NULL;
125 _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL;
126
127 if (_log.isInfoEnabled()) {
128 _log.info(
129 "Detected Informix with database name " + dbName);
130 }
131 }
132 else if (dbName.startsWith("MySQL")) {
133 _vendorMySQL = true;
134
137 if (_log.isInfoEnabled()) {
138 _log.info(
139 "Detected MySQL with database name " + dbName);
140 }
141 }
142 else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) {
143 _vendorSybase = true;
144 _functionIsNull = SYBASE_FUNCTION_IS_NULL;
145 _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL;
146
147 if (_log.isInfoEnabled()) {
148 _log.info(
149 "Detected Sybase with database name " + dbName);
150 }
151 }
152 else if (dbName.startsWith("Oracle")) {
153 _vendorOracle = true;
154
155 if (_log.isInfoEnabled()) {
156 _log.info(
157 "Detected Oracle with database name " + dbName);
158 }
159 }
160 else if (dbName.startsWith("PostgreSQL")) {
161 _vendorPostgreSQL = true;
162
163 if (_log.isInfoEnabled()) {
164 _log.info(
165 "Detected PostgreSQL with database name " + dbName);
166 }
167 }
168 else {
169 if (_log.isDebugEnabled()) {
170 _log.debug(
171 "Unable to detect database with name " + dbName);
172 }
173 }
174 }
175 }
176 catch (Exception e) {
177 _log.error(e, e);
178 }
179 finally {
180 DataAccess.cleanUp(con);
181 }
182
183 _sqlPool = new HashMap<String, String>();
184
185 try {
186 ClassLoader classLoader = getClass().getClassLoader();
187
188 String[] configs = getConfigs();
189
190 for (int i = 0; i < configs.length; i++) {
191 read(classLoader, configs[i]);
192 }
193 }
194 catch (Exception e) {
195 _log.error(e, e);
196 }
197 }
198
199 public String get(String id) {
200 return _sqlPool.get(id);
201 }
202
203
208 public boolean isVendorDB2() {
209 return _vendorDB2;
210 }
211
212
217 public boolean isVendorInformix() {
218 return _vendorInformix;
219 }
220
221
226 public boolean isVendorMySQL() {
227 return _vendorMySQL;
228 }
229
230
239 public boolean isVendorOracle() {
240 return _vendorOracle;
241 }
242
243
248 public boolean isVendorPostgreSQL() {
249 return _vendorPostgreSQL;
250 }
251
252
257 public boolean isVendorSybase() {
258 return _vendorSybase;
259 }
260
261 public String[] keywords(String keywords) {
262 return keywords(keywords, true);
263 }
264
265 public String[] keywords(String keywords, boolean lowerCase) {
266 if (lowerCase) {
267 keywords = keywords.toLowerCase();
268 }
269
270 keywords = keywords.trim();
271
272 String[] keywordsArray = StringUtil.split(keywords, StringPool.SPACE);
273
274 for (int i = 0; i < keywordsArray.length; i++) {
275 String keyword = keywordsArray[i];
276
277 keywordsArray[i] =
278 StringPool.PERCENT + keyword + StringPool.PERCENT;
279 }
280
281 return keywordsArray;
282 }
283
284 public String[] keywords(String[] keywordsArray) {
285 return keywords(keywordsArray, true);
286 }
287
288 public String[] keywords(String[] keywordsArray, boolean lowerCase) {
289 if ((keywordsArray == null) || (keywordsArray.length == 0)) {
290 keywordsArray = new String[] {null};
291 }
292
293 if (lowerCase) {
294 for (int i = 0; i < keywordsArray.length; i++) {
295 keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
296 }
297 }
298
299 return keywordsArray;
300 }
301
302 public String replaceAndOperator(String sql, boolean andOperator) {
303 String andOrConnector = "OR";
304 String andOrNullCheck = "AND ? IS NOT NULL";
305
306 if (andOperator) {
307 andOrConnector = "AND";
308 andOrNullCheck = "OR ? IS NULL";
309 }
310
311 sql = StringUtil.replace(
312 sql,
313 new String[] {
314 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
315 },
316 new String[] {
317 andOrConnector, andOrNullCheck
318 });
319
320 if (_vendorPostgreSQL) {
321 sql = StringUtil.replace(
322 sql,
323 new String[] {
324 "Date >= ? AND ? IS NOT NULL",
325 "Date <= ? AND ? IS NOT NULL",
326 "Date >= ? OR ? IS NULL",
327 "Date <= ? OR ? IS NULL"
328 },
329 new String[] {
330 "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
331 "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
332 "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
333 "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
334 });
335 }
336
337 sql = replaceIsNull(sql);
338
339 return sql;
340 }
341
342 public String replaceIsNull(String sql) {
343 if (Validator.isNotNull(_functionIsNull)) {
344 sql = StringUtil.replace(
345 sql,
346 new String[] {
347 "? IS NULL", "? IS NOT NULL"
348 },
349 new String[] {
350 _functionIsNull,
351 _functionIsNotNull
352 });
353 }
354
355 return sql;
356 }
357
358 public String replaceKeywords(
359 String sql, String field, String operator, boolean last,
360 String[] values) {
361
362 if (values.length == 0) {
363 return sql;
364 }
365
366 StringBuilder oldSql = new StringBuilder();
367
368 oldSql.append("(");
369 oldSql.append(field);
370 oldSql.append(" ");
371 oldSql.append(operator);
372 oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
373
374 if (!last) {
375 oldSql.append(" [$AND_OR_CONNECTOR$]");
376 }
377
378 StringBuilder newSql = new StringBuilder();
379
380 newSql.append("(");
381
382 for (int i = 0; i < values.length; i++) {
383 if (i > 0) {
384 newSql.append(" OR ");
385 }
386
387 newSql.append("(");
388 newSql.append(field);
389 newSql.append(" ");
390 newSql.append(operator);
391 newSql.append(" ? [$AND_OR_NULL_CHECK$])");
392 }
393
394 newSql.append(")");
395
396 if (!last) {
397 newSql.append(" [$AND_OR_CONNECTOR$]");
398 }
399
400 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
401 }
402
403 public String removeOrderBy(String sql) {
404 int pos = sql.indexOf(" ORDER BY ");
405
406 if (pos != -1) {
407 sql = sql.substring(0, pos);
408 }
409
410 return sql;
411 }
412
413 public String replaceOrderBy(String sql, OrderByComparator obc) {
414 if (obc == null) {
415 return sql;
416 }
417
418 StringBuilder sb = new StringBuilder();
419
420 sb.append(removeOrderBy(sql));
421 sb.append(" ORDER BY ");
422 sb.append(obc.getOrderBy());
423
424 return sb.toString();
425 }
426
427 protected String[] getConfigs() {
428 if (PortalClassLoaderUtil.getClassLoader() ==
429 CustomSQL.class.getClassLoader()) {
430
431 Properties propsUtil = PortalUtil.getPortalProperties();
432
433 return StringUtil.split(
434 propsUtil.getProperty("custom.sql.configs"));
435 }
436 else {
437 return new String[] {"custom-sql/default.xml"};
438 }
439 }
440
441 protected void read(ClassLoader classLoader, String source)
442 throws Exception {
443
444 String xml = null;
445
446 try {
447 xml = StringUtil.read(classLoader, source);
448 }
449 catch (Exception e) {
450 _log.warn("Cannot load " + source);
451 }
452
453 if (xml == null) {
454 return;
455 }
456
457 if (_log.isDebugEnabled()) {
458 _log.debug("Loading " + source);
459 }
460
461 Document doc = SAXReaderUtil.read(new StringReader(xml));
462
463 Element root = doc.getRootElement();
464
465 Iterator<Element> itr = root.elements("sql").iterator();
466
467 while (itr.hasNext()) {
468 Element sql = itr.next();
469
470 String file = sql.attributeValue("file");
471
472 if (Validator.isNotNull(file)) {
473 read(classLoader, file);
474 }
475 else {
476 String id = sql.attributeValue("id");
477 String content = transform(sql.getText());
478
479 content = replaceIsNull(content);
480
481 _sqlPool.put(id, content);
482 }
483 }
484 }
485
486 protected String transform(String sql) {
487 sql = PortalUtil.transformCustomSQL(sql);
488
489 StringBuilder sb = new StringBuilder();
490
491 try {
492 BufferedReader br = new BufferedReader(new StringReader(sql));
493
494 String line = null;
495
496 while ((line = br.readLine()) != null) {
497 sb.append(line.trim());
498 sb.append(StringPool.SPACE);
499 }
500
501 br.close();
502 }
503 catch (IOException ioe) {
504 return sql;
505 }
506
507 return sb.toString();
508 }
509
510 private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
511
512 private boolean _vendorDB2;
513 private boolean _vendorInformix;
514 private boolean _vendorMySQL;
515 private boolean _vendorOracle;
516 private boolean _vendorPostgreSQL;
517 private boolean _vendorSybase;
518 private String _functionIsNull;
519 private String _functionIsNotNull;
520 private Map<String, String> _sqlPool;
521
522 }