001
014
015 package com.liferay.util.dao.orm;
016
017 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
019 import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
020 import com.liferay.portal.kernel.log.Log;
021 import com.liferay.portal.kernel.log.LogFactoryUtil;
022 import com.liferay.portal.kernel.util.GetterUtil;
023 import com.liferay.portal.kernel.util.OrderByComparator;
024 import com.liferay.portal.kernel.util.PortalClassLoaderUtil;
025 import com.liferay.portal.kernel.util.StringBundler;
026 import com.liferay.portal.kernel.util.StringPool;
027 import com.liferay.portal.kernel.util.StringUtil;
028 import com.liferay.portal.kernel.util.Validator;
029 import com.liferay.portal.kernel.xml.Document;
030 import com.liferay.portal.kernel.xml.Element;
031 import com.liferay.portal.kernel.xml.SAXReaderUtil;
032 import com.liferay.portal.util.PortalUtil;
033
034 import java.io.IOException;
035 import java.io.InputStream;
036
037 import java.sql.Connection;
038 import java.sql.DatabaseMetaData;
039 import java.sql.SQLException;
040
041 import java.util.HashMap;
042 import java.util.Map;
043 import java.util.Properties;
044 import java.util.concurrent.atomic.AtomicReference;
045
046
051 public class CustomSQL {
052
053 public static final String DB2_FUNCTION_IS_NOT_NULL =
054 "CAST(? AS VARCHAR(32672)) IS NOT NULL";
055
056 public static final String DB2_FUNCTION_IS_NULL =
057 "CAST(? AS VARCHAR(32672)) IS NULL";
058
059 public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
060 "NOT lportal.isnull(?)";
061
062 public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
063
064 public static final String MYSQL_FUNCTION_IS_NOT_NULL =
065 "IFNULL(?, '1') = '0'";
066
067 public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
068
069 public static final String SYBASE_FUNCTION_IS_NOT_NULL =
070 "ISNULL(?, '1') = '0'";
071
072 public static final String SYBASE_FUNCTION_IS_NULL = "ISNULL(?, '1') = '1'";
073
074 public CustomSQL() throws SQLException {
075 Connection con = DataAccess.getConnection();
076
077 String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
078 String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
079
080 try {
081 if (Validator.isNotNull(functionIsNull) &&
082 Validator.isNotNull(functionIsNotNull)) {
083
084 _functionIsNull = functionIsNull;
085 _functionIsNotNull = functionIsNotNull;
086
087 if (_log.isDebugEnabled()) {
088 _log.info(
089 "functionIsNull is manually set to " + functionIsNull);
090 _log.info(
091 "functionIsNotNull is manually set to " +
092 functionIsNotNull);
093 }
094 }
095 else if (con != null) {
096 DatabaseMetaData metaData = con.getMetaData();
097
098 String dbName = GetterUtil.getString(
099 metaData.getDatabaseProductName());
100
101 if (_log.isInfoEnabled()) {
102 _log.info("Database name " + dbName);
103 }
104
105 if (dbName.startsWith("DB2")) {
106 _vendorDB2 = true;
107 _functionIsNull = DB2_FUNCTION_IS_NULL;
108 _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL;
109
110 if (_log.isInfoEnabled()) {
111 _log.info("Detected DB2 with database name " + dbName);
112 }
113 }
114 else if (dbName.startsWith("Informix")) {
115 _vendorInformix = true;
116 _functionIsNull = INFORMIX_FUNCTION_IS_NULL;
117 _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL;
118
119 if (_log.isInfoEnabled()) {
120 _log.info(
121 "Detected Informix with database name " + dbName);
122 }
123 }
124 else if (dbName.startsWith("MySQL")) {
125 _vendorMySQL = true;
126
127
128
129 if (_log.isInfoEnabled()) {
130 _log.info(
131 "Detected MySQL with database name " + dbName);
132 }
133 }
134 else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) {
135 _vendorSybase = true;
136 _functionIsNull = SYBASE_FUNCTION_IS_NULL;
137 _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL;
138
139 if (_log.isInfoEnabled()) {
140 _log.info(
141 "Detected Sybase with database name " + dbName);
142 }
143 }
144 else if (dbName.startsWith("Oracle")) {
145 _vendorOracle = true;
146
147 if (_log.isInfoEnabled()) {
148 _log.info(
149 "Detected Oracle with database name " + dbName);
150 }
151 }
152 else if (dbName.startsWith("PostgreSQL")) {
153 _vendorPostgreSQL = true;
154
155 if (_log.isInfoEnabled()) {
156 _log.info(
157 "Detected PostgreSQL with database name " + dbName);
158 }
159 }
160 else {
161 if (_log.isDebugEnabled()) {
162 _log.debug(
163 "Unable to detect database with name " + dbName);
164 }
165 }
166 }
167 }
168 catch (Exception e) {
169 _log.error(e, e);
170 }
171 finally {
172 DataAccess.cleanUp(con);
173 }
174
175 _sqlPool = new HashMap<String, String>();
176
177 try {
178 ClassLoader classLoader = getClass().getClassLoader();
179
180 String[] configs = getConfigs();
181
182 for (String _config : configs) {
183 read(classLoader, _config);
184 }
185 }
186 catch (Exception e) {
187 _log.error(e, e);
188 }
189 }
190
191 public String appendCriteria(String sql, String criteria) {
192 if (Validator.isNull(criteria)) {
193 return sql;
194 }
195
196 if (!criteria.startsWith(StringPool.SPACE)) {
197 criteria = StringPool.SPACE.concat(criteria);
198 }
199
200 if (!criteria.endsWith(StringPool.SPACE)) {
201 criteria = criteria.concat(StringPool.SPACE);
202 }
203
204 int pos = sql.indexOf(_GROUP_BY_CLAUSE);
205
206 if (pos != -1) {
207 return sql.substring(0, pos + 1).concat(criteria).concat(
208 sql.substring(pos + 1));
209 }
210
211 pos = sql.indexOf(_ORDER_BY_CLAUSE);
212
213 if (pos != -1) {
214 return sql.substring(0, pos + 1).concat(criteria).concat(
215 sql.substring(pos + 1));
216 }
217
218 return sql.concat(criteria);
219 }
220
221 public String get(String id) {
222 return _sqlPool.get(id);
223 }
224
225
230 public boolean isVendorDB2() {
231 return _vendorDB2;
232 }
233
234
241 public boolean isVendorInformix() {
242 return _vendorInformix;
243 }
244
245
250 public boolean isVendorMySQL() {
251 return _vendorMySQL;
252 }
253
254
264 public boolean isVendorOracle() {
265 return _vendorOracle;
266 }
267
268
275 public boolean isVendorPostgreSQL() {
276 return _vendorPostgreSQL;
277 }
278
279
285 public boolean isVendorSybase() {
286 return _vendorSybase;
287 }
288
289 public String[] keywords(String keywords) {
290 return keywords(keywords, true);
291 }
292
293 public String[] keywords(String keywords, boolean lowerCase) {
294 if (lowerCase) {
295 keywords = keywords.toLowerCase();
296 }
297
298 keywords = keywords.trim();
299
300 String[] keywordsArray = StringUtil.split(keywords, StringPool.SPACE);
301
302 for (int i = 0; i < keywordsArray.length; i++) {
303 String keyword = keywordsArray[i];
304
305 keywordsArray[i] =
306 StringPool.PERCENT + keyword + StringPool.PERCENT;
307 }
308
309 return keywordsArray;
310 }
311
312 public String[] keywords(String[] keywordsArray) {
313 return keywords(keywordsArray, true);
314 }
315
316 public String[] keywords(String[] keywordsArray, boolean lowerCase) {
317 if ((keywordsArray == null) || (keywordsArray.length == 0)) {
318 keywordsArray = new String[] {null};
319 }
320
321 if (lowerCase) {
322 for (int i = 0; i < keywordsArray.length; i++) {
323 keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
324 }
325 }
326
327 return keywordsArray;
328 }
329
330 public String removeGroupBy(String sql) {
331 int x = sql.indexOf(_GROUP_BY_CLAUSE);
332
333 if (x != -1) {
334 int y = sql.indexOf(_ORDER_BY_CLAUSE);
335
336 if (y == -1) {
337 sql = sql.substring(0, x);
338 }
339 else {
340 sql = sql.substring(0, x) + sql.substring(y);
341 }
342 }
343
344 return sql;
345 }
346
347 public String removeOrderBy(String sql) {
348
349
350
351 AtomicReference<String> sqlAtomicReference =
352 new AtomicReference<String>(sql);
353
354 int pos = sqlAtomicReference.get().indexOf(_ORDER_BY_CLAUSE);
355
356 if (pos != -1) {
357 sql = sqlAtomicReference.get().substring(0, pos);
358 }
359
360
365
366 return sql;
367 }
368
369 public String replaceAndOperator(String sql, boolean andOperator) {
370 String andOrConnector = "OR";
371 String andOrNullCheck = "AND ? IS NOT NULL";
372
373 if (andOperator) {
374 andOrConnector = "AND";
375 andOrNullCheck = "OR ? IS NULL";
376 }
377
378 sql = StringUtil.replace(
379 sql,
380 new String[] {
381 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
382 },
383 new String[] {
384 andOrConnector, andOrNullCheck
385 });
386
387 if (_vendorPostgreSQL) {
388 sql = StringUtil.replace(
389 sql,
390 new String[] {
391 "Date >= ? AND ? IS NOT NULL",
392 "Date <= ? AND ? IS NOT NULL",
393 "Date >= ? OR ? IS NULL",
394 "Date <= ? OR ? IS NULL"
395 },
396 new String[] {
397 "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
398 "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
399 "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
400 "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
401 });
402 }
403
404 sql = replaceIsNull(sql);
405
406 return sql;
407 }
408
409 public String replaceIsNull(String sql) {
410 if (Validator.isNotNull(_functionIsNull)) {
411 sql = StringUtil.replace(
412 sql,
413 new String[] {
414 "? IS NULL", "? IS NOT NULL"
415 },
416 new String[] {
417 _functionIsNull,
418 _functionIsNotNull
419 });
420 }
421
422 return sql;
423 }
424
425 public String replaceKeywords(
426 String sql, String field, boolean last, int[] values) {
427
428 StringBundler oldSql = new StringBundler(4);
429
430 oldSql.append("(");
431 oldSql.append(field);
432 oldSql.append(" = ?)");
433
434 if (!last) {
435 oldSql.append(" [$AND_OR_CONNECTOR$]");
436 }
437
438 if ((values == null) || (values.length == 0)) {
439 return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
440 }
441
442 StringBundler newSql = new StringBundler(values.length * 4 + 3);
443
444 newSql.append("(");
445
446 for (int i = 0; i < values.length; i++) {
447 if (i > 0) {
448 newSql.append(" OR ");
449 }
450
451 newSql.append("(");
452 newSql.append(field);
453 newSql.append(" = ?)");
454 }
455
456 newSql.append(")");
457
458 if (!last) {
459 newSql.append(" [$AND_OR_CONNECTOR$]");
460 }
461
462 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
463 }
464
465 public String replaceKeywords(
466 String sql, String field, boolean last, long[] values) {
467
468 StringBundler oldSql = new StringBundler(4);
469
470 oldSql.append("(");
471 oldSql.append(field);
472 oldSql.append(" = ?)");
473
474 if (!last) {
475 oldSql.append(" [$AND_OR_CONNECTOR$]");
476 }
477
478 if ((values == null) || (values.length == 0)) {
479 return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
480 }
481
482 StringBundler newSql = new StringBundler(values.length * 4 + 3);
483
484 newSql.append("(");
485
486 for (int i = 0; i < values.length; i++) {
487 if (i > 0) {
488 newSql.append(" OR ");
489 }
490
491 newSql.append("(");
492 newSql.append(field);
493 newSql.append(" = ?)");
494 }
495
496 newSql.append(")");
497
498 if (!last) {
499 newSql.append(" [$AND_OR_CONNECTOR$]");
500 }
501
502 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
503 }
504
505 public String replaceKeywords(
506 String sql, String field, String operator, boolean last,
507 String[] values) {
508
509 if (values.length == 0) {
510 return sql;
511 }
512
513 StringBundler oldSql = new StringBundler(6);
514
515 oldSql.append("(");
516 oldSql.append(field);
517 oldSql.append(" ");
518 oldSql.append(operator);
519 oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
520
521 if (!last) {
522 oldSql.append(" [$AND_OR_CONNECTOR$]");
523 }
524
525 StringBundler newSql = new StringBundler(values.length * 6 + 3);
526
527 newSql.append("(");
528
529 for (int i = 0; i < values.length; i++) {
530 if (i > 0) {
531 newSql.append(" OR ");
532 }
533
534 newSql.append("(");
535 newSql.append(field);
536 newSql.append(" ");
537 newSql.append(operator);
538 newSql.append(" ? [$AND_OR_NULL_CHECK$])");
539 }
540
541 newSql.append(")");
542
543 if (!last) {
544 newSql.append(" [$AND_OR_CONNECTOR$]");
545 }
546
547 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
548 }
549
550 public String replaceGroupBy(String sql, String groupBy) {
551 if (groupBy == null) {
552 return sql;
553 }
554
555 int x = sql.indexOf(_GROUP_BY_CLAUSE);
556
557 if (x != -1) {
558 int y = sql.indexOf(_ORDER_BY_CLAUSE);
559
560 if (y == -1) {
561 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
562 groupBy);
563 }
564 else {
565 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
566 groupBy).concat(sql.substring(y));
567 }
568 }
569 else {
570 int y = sql.indexOf(_ORDER_BY_CLAUSE);
571
572 if (y == -1) {
573 sql = sql.concat(_GROUP_BY_CLAUSE).concat(groupBy);
574 }
575 else {
576 StringBundler sb = new StringBundler();
577
578 sb.append(sql.substring(0, y));
579 sb.append(_GROUP_BY_CLAUSE);
580 sb.append(groupBy);
581 sb.append(sql.substring(y));
582
583 sql = sb.toString();
584 }
585 }
586
587 return sql;
588 }
589
590 public String replaceOrderBy(String sql, OrderByComparator obc) {
591 if (obc == null) {
592 return sql;
593 }
594
595 return removeOrderBy(sql).concat(_ORDER_BY_CLAUSE).concat(
596 obc.getOrderBy());
597 }
598
599 protected String[] getConfigs() {
600 if (PortalClassLoaderUtil.getClassLoader() ==
601 CustomSQL.class.getClassLoader()) {
602
603 Properties propsUtil = PortalUtil.getPortalProperties();
604
605 return StringUtil.split(
606 propsUtil.getProperty("custom.sql.configs"));
607 }
608 else {
609 return new String[] {"custom-sql/default.xml"};
610 }
611 }
612
613 protected void read(ClassLoader classLoader, String source)
614 throws Exception {
615
616 InputStream is = classLoader.getResourceAsStream(source);
617
618 if (is == null) {
619 return;
620 }
621
622 if (_log.isDebugEnabled()) {
623 _log.debug("Loading " + source);
624 }
625
626 Document document = SAXReaderUtil.read(is);
627
628 Element rootElement = document.getRootElement();
629
630 for (Element sqlElement : rootElement.elements("sql")) {
631 String file = sqlElement.attributeValue("file");
632
633 if (Validator.isNotNull(file)) {
634 read(classLoader, file);
635 }
636 else {
637 String id = sqlElement.attributeValue("id");
638 String content = transform(sqlElement.getText());
639
640 content = replaceIsNull(content);
641
642 _sqlPool.put(id, content);
643 }
644 }
645 }
646
647 protected String transform(String sql) {
648 sql = PortalUtil.transformCustomSQL(sql);
649
650 StringBundler sb = new StringBundler();
651
652 try {
653 UnsyncBufferedReader unsyncBufferedReader =
654 new UnsyncBufferedReader(new UnsyncStringReader(sql));
655
656 String line = null;
657
658 while ((line = unsyncBufferedReader.readLine()) != null) {
659 sb.append(line.trim());
660 sb.append(StringPool.SPACE);
661 }
662
663 unsyncBufferedReader.close();
664 }
665 catch (IOException ioe) {
666 return sql;
667 }
668
669 return sb.toString();
670 }
671
672 private static final String _GROUP_BY_CLAUSE = " GROUP BY ";
673
674 private static final String _ORDER_BY_CLAUSE = " ORDER BY ";
675
676 private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
677
678 private String _functionIsNotNull;
679 private String _functionIsNull;
680 private Map<String, String> _sqlPool;
681 private boolean _vendorDB2;
682 private boolean _vendorInformix;
683 private boolean _vendorMySQL;
684 private boolean _vendorOracle;
685 private boolean _vendorPostgreSQL;
686 private boolean _vendorSybase;
687
688 }