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.dao.orm.QueryDefinition;
019 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
020 import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
021 import com.liferay.portal.kernel.log.Log;
022 import com.liferay.portal.kernel.log.LogFactoryUtil;
023 import com.liferay.portal.kernel.util.CharPool;
024 import com.liferay.portal.kernel.util.GetterUtil;
025 import com.liferay.portal.kernel.util.OrderByComparator;
026 import com.liferay.portal.kernel.util.PortalClassLoaderUtil;
027 import com.liferay.portal.kernel.util.StringBundler;
028 import com.liferay.portal.kernel.util.StringPool;
029 import com.liferay.portal.kernel.util.StringUtil;
030 import com.liferay.portal.kernel.util.Validator;
031 import com.liferay.portal.kernel.workflow.WorkflowConstants;
032 import com.liferay.portal.kernel.xml.Document;
033 import com.liferay.portal.kernel.xml.Element;
034 import com.liferay.portal.kernel.xml.SAXReaderUtil;
035 import com.liferay.portal.util.PortalUtil;
036
037 import java.io.IOException;
038 import java.io.InputStream;
039
040 import java.sql.Connection;
041 import java.sql.DatabaseMetaData;
042 import java.sql.SQLException;
043
044 import java.util.ArrayList;
045 import java.util.HashMap;
046 import java.util.List;
047 import java.util.Map;
048 import java.util.Properties;
049
050
055 public class CustomSQL {
056
057 public static final String DB2_FUNCTION_IS_NOT_NULL =
058 "CAST(? AS VARCHAR(32672)) IS NOT NULL";
059
060 public static final String DB2_FUNCTION_IS_NULL =
061 "CAST(? AS VARCHAR(32672)) IS NULL";
062
063 public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
064 "NOT lportal.isnull(?)";
065
066 public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
067
068 public static final String MYSQL_FUNCTION_IS_NOT_NULL =
069 "IFNULL(?, '1') = '0'";
070
071 public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
072
073 public static final String SYBASE_FUNCTION_IS_NOT_NULL =
074 "CONVERT(VARCHAR,?) IS NOT NULL";
075
076 public static final String SYBASE_FUNCTION_IS_NULL =
077 "CONVERT(VARCHAR,?) IS NULL";
078
079 public CustomSQL() throws SQLException {
080 reloadCustomSQL();
081 }
082
083 public String appendCriteria(String sql, String criteria) {
084 if (Validator.isNull(criteria)) {
085 return sql;
086 }
087
088 if (!criteria.startsWith(StringPool.SPACE)) {
089 criteria = StringPool.SPACE.concat(criteria);
090 }
091
092 if (!criteria.endsWith(StringPool.SPACE)) {
093 criteria = criteria.concat(StringPool.SPACE);
094 }
095
096 int pos = sql.indexOf(_GROUP_BY_CLAUSE);
097
098 if (pos != -1) {
099 return sql.substring(0, pos + 1).concat(criteria).concat(
100 sql.substring(pos + 1));
101 }
102
103 pos = sql.indexOf(_ORDER_BY_CLAUSE);
104
105 if (pos != -1) {
106 return sql.substring(0, pos + 1).concat(criteria).concat(
107 sql.substring(pos + 1));
108 }
109
110 return sql.concat(criteria);
111 }
112
113 public String get(String id) {
114 return _sqlPool.get(id);
115 }
116
117 public String get(String id, QueryDefinition queryDefinition) {
118 return get(id, queryDefinition, StringPool.BLANK);
119 }
120
121 public String get(
122 String id, QueryDefinition queryDefinition, String tableName) {
123
124 String sql = get(id);
125
126 if (!Validator.isBlank(tableName) &&
127 !tableName.endsWith(StringPool.PERIOD)) {
128
129 tableName = tableName.concat(StringPool.PERIOD);
130 }
131
132 if (queryDefinition.getStatus() == WorkflowConstants.STATUS_ANY) {
133 sql = sql.replace(_STATUS_KEYWORD, _STATUS_CONDITION_EMPTY);
134 }
135 else {
136 if (queryDefinition.isExcludeStatus()) {
137 sql = sql.replace(
138 _STATUS_KEYWORD,
139 tableName.concat(_STATUS_CONDITION_INVERSE));
140 }
141 else {
142 sql = sql.replace(
143 _STATUS_KEYWORD,
144 tableName.concat(_STATUS_CONDITION_DEFAULT));
145 }
146 }
147
148 return sql;
149 }
150
151
156 public boolean isVendorDB2() {
157 return _vendorDB2;
158 }
159
160
167 public boolean isVendorHSQL() {
168 return _vendorHSQL;
169 }
170
171
178 public boolean isVendorInformix() {
179 return _vendorInformix;
180 }
181
182
187 public boolean isVendorMySQL() {
188 return _vendorMySQL;
189 }
190
191
201 public boolean isVendorOracle() {
202 return _vendorOracle;
203 }
204
205
212 public boolean isVendorPostgreSQL() {
213 return _vendorPostgreSQL;
214 }
215
216
222 public boolean isVendorSybase() {
223 return _vendorSybase;
224 }
225
226 public String[] keywords(String keywords) {
227 return keywords(keywords, true);
228 }
229
230 public String[] keywords(String keywords, boolean lowerCase) {
231 if (Validator.isNull(keywords)) {
232 return new String[] {null};
233 }
234
235 if (lowerCase) {
236 keywords = keywords.toLowerCase();
237 }
238
239 keywords = keywords.trim();
240
241 List<String> keywordsList = new ArrayList<String>();
242
243 for (int i = 0; i < keywords.length(); i++) {
244 char c = keywords.charAt(i);
245
246 if (c == CharPool.QUOTE) {
247 int pos = i + 1;
248
249 i = keywords.indexOf(CharPool.QUOTE, pos);
250
251 if (i == -1) {
252 i = keywords.length();
253 }
254
255 if (i > pos) {
256 String keyword = keywords.substring(pos, i);
257
258 keywordsList.add(
259 StringUtil.quote(keyword, StringPool.PERCENT));
260 }
261 }
262 else {
263 while (Character.isWhitespace(c)) {
264 i++;
265
266 c = keywords.charAt(i);
267 }
268
269 int pos = i;
270
271 while (!Character.isWhitespace(c)) {
272 i++;
273
274 if (i == keywords.length()) {
275 break;
276 }
277
278 c = keywords.charAt(i);
279 }
280
281 String keyword = keywords.substring(pos, i);
282
283 keywordsList.add(StringUtil.quote(keyword, StringPool.PERCENT));
284 }
285 }
286
287 return keywordsList.toArray(new String[keywordsList.size()]);
288 }
289
290 public String[] keywords(String[] keywordsArray) {
291 return keywords(keywordsArray, true);
292 }
293
294 public String[] keywords(String[] keywordsArray, boolean lowerCase) {
295 if ((keywordsArray == null) || (keywordsArray.length == 0)) {
296 return new String[] {null};
297 }
298
299 if (lowerCase) {
300 for (int i = 0; i < keywordsArray.length; i++) {
301 keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
302 }
303 }
304
305 return keywordsArray;
306 }
307
308 public void reloadCustomSQL() throws SQLException {
309 PortalUtil.initCustomSQL();
310
311 Connection con = DataAccess.getConnection();
312
313 String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
314 String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
315
316 try {
317 if (Validator.isNotNull(functionIsNull) &&
318 Validator.isNotNull(functionIsNotNull)) {
319
320 _functionIsNull = functionIsNull;
321 _functionIsNotNull = functionIsNotNull;
322
323 if (_log.isDebugEnabled()) {
324 _log.debug(
325 "functionIsNull is manually set to " + functionIsNull);
326 _log.debug(
327 "functionIsNotNull is manually set to " +
328 functionIsNotNull);
329 }
330 }
331 else if (con != null) {
332 DatabaseMetaData metaData = con.getMetaData();
333
334 String dbName = GetterUtil.getString(
335 metaData.getDatabaseProductName());
336
337 if (_log.isInfoEnabled()) {
338 _log.info("Database name " + dbName);
339 }
340
341 if (dbName.startsWith("DB2")) {
342 _vendorDB2 = true;
343 _functionIsNull = DB2_FUNCTION_IS_NULL;
344 _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL;
345
346 if (_log.isInfoEnabled()) {
347 _log.info("Detected DB2 with database name " + dbName);
348 }
349 }
350 else if (dbName.startsWith("HSQL")) {
351 _vendorHSQL = true;
352
353 if (_log.isInfoEnabled()) {
354 _log.info("Detected HSQL with database name " + dbName);
355 }
356 }
357 else if (dbName.startsWith("Informix")) {
358 _vendorInformix = true;
359 _functionIsNull = INFORMIX_FUNCTION_IS_NULL;
360 _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL;
361
362 if (_log.isInfoEnabled()) {
363 _log.info(
364 "Detected Informix with database name " + dbName);
365 }
366 }
367 else if (dbName.startsWith("MySQL")) {
368 _vendorMySQL = true;
369
370
371
372 if (_log.isInfoEnabled()) {
373 _log.info(
374 "Detected MySQL with database name " + dbName);
375 }
376 }
377 else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) {
378 _vendorSybase = true;
379 _functionIsNull = SYBASE_FUNCTION_IS_NULL;
380 _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL;
381
382 if (_log.isInfoEnabled()) {
383 _log.info(
384 "Detected Sybase with database name " + dbName);
385 }
386 }
387 else if (dbName.startsWith("Oracle")) {
388 _vendorOracle = true;
389
390 if (_log.isInfoEnabled()) {
391 _log.info(
392 "Detected Oracle with database name " + dbName);
393 }
394 }
395 else if (dbName.startsWith("PostgreSQL")) {
396 _vendorPostgreSQL = true;
397
398 if (_log.isInfoEnabled()) {
399 _log.info(
400 "Detected PostgreSQL with database name " + dbName);
401 }
402 }
403 else {
404 if (_log.isDebugEnabled()) {
405 _log.debug(
406 "Unable to detect database with name " + dbName);
407 }
408 }
409 }
410 }
411 catch (Exception e) {
412 _log.error(e, e);
413 }
414 finally {
415 DataAccess.cleanUp(con);
416 }
417
418 if (_sqlPool == null) {
419 _sqlPool = new HashMap<String, String>();
420 }
421 else {
422 _sqlPool.clear();
423 }
424
425 try {
426 Class<?> clazz = getClass();
427
428 ClassLoader classLoader = clazz.getClassLoader();
429
430 String[] configs = getConfigs();
431
432 for (String _config : configs) {
433 read(classLoader, _config);
434 }
435 }
436 catch (Exception e) {
437 _log.error(e, e);
438 }
439 }
440
441 public String removeGroupBy(String sql) {
442 int x = sql.indexOf(_GROUP_BY_CLAUSE);
443
444 if (x != -1) {
445 int y = sql.indexOf(_ORDER_BY_CLAUSE);
446
447 if (y == -1) {
448 sql = sql.substring(0, x);
449 }
450 else {
451 sql = sql.substring(0, x) + sql.substring(y);
452 }
453 }
454
455 return sql;
456 }
457
458 public String removeOrderBy(String sql) {
459 int pos = sql.indexOf(_ORDER_BY_CLAUSE);
460
461 if (pos != -1) {
462 sql = sql.substring(0, pos);
463 }
464
465 return sql;
466 }
467
468 public String replaceAndOperator(String sql, boolean andOperator) {
469 String andOrConnector = "OR";
470 String andOrNullCheck = "AND ? IS NOT NULL";
471
472 if (andOperator) {
473 andOrConnector = "AND";
474 andOrNullCheck = "OR ? IS NULL";
475 }
476
477 sql = StringUtil.replace(
478 sql,
479 new String[] {
480 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
481 },
482 new String[] {
483 andOrConnector, andOrNullCheck
484 });
485
486 if (_vendorPostgreSQL) {
487 sql = StringUtil.replace(
488 sql,
489 new String[] {
490 "Date >= ? AND ? IS NOT NULL",
491 "Date <= ? AND ? IS NOT NULL", "Date >= ? OR ? IS NULL",
492 "Date <= ? OR ? IS NULL"
493 },
494 new String[] {
495 "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
496 "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
497 "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
498 "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
499 });
500 }
501
502 sql = replaceIsNull(sql);
503
504 return sql;
505 }
506
507 public String replaceGroupBy(String sql, String groupBy) {
508 if (groupBy == null) {
509 return sql;
510 }
511
512 int x = sql.indexOf(_GROUP_BY_CLAUSE);
513
514 if (x != -1) {
515 int y = sql.indexOf(_ORDER_BY_CLAUSE);
516
517 if (y == -1) {
518 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
519 groupBy);
520 }
521 else {
522 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
523 groupBy).concat(sql.substring(y));
524 }
525 }
526 else {
527 int y = sql.indexOf(_ORDER_BY_CLAUSE);
528
529 if (y == -1) {
530 sql = sql.concat(_GROUP_BY_CLAUSE).concat(groupBy);
531 }
532 else {
533 StringBundler sb = new StringBundler();
534
535 sb.append(sql.substring(0, y));
536 sb.append(_GROUP_BY_CLAUSE);
537 sb.append(groupBy);
538 sb.append(sql.substring(y));
539
540 sql = sb.toString();
541 }
542 }
543
544 return sql;
545 }
546
547 public String replaceIsNull(String sql) {
548 if (Validator.isNotNull(_functionIsNull)) {
549 sql = StringUtil.replace(
550 sql,
551 new String[] {
552 "? IS NULL", "? IS NOT NULL"
553 },
554 new String[] {
555 _functionIsNull, _functionIsNotNull
556 });
557 }
558
559 return sql;
560 }
561
562 public String replaceKeywords(
563 String sql, String field, boolean last, int[] values) {
564
565 if ((values != null) && (values.length == 1)) {
566 return sql;
567 }
568
569 StringBundler oldSql = new StringBundler(4);
570
571 oldSql.append(StringPool.OPEN_PARENTHESIS);
572 oldSql.append(field);
573 oldSql.append(" = ?)");
574
575 if (!last) {
576 oldSql.append(" [$AND_OR_CONNECTOR$]");
577 }
578
579 if ((values == null) || (values.length == 0)) {
580 return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
581 }
582
583 StringBundler newSql = new StringBundler(values.length * 4 + 3);
584
585 newSql.append(StringPool.OPEN_PARENTHESIS);
586
587 for (int i = 0; i < values.length; i++) {
588 if (i > 0) {
589 newSql.append(" OR ");
590 }
591
592 newSql.append(StringPool.OPEN_PARENTHESIS);
593 newSql.append(field);
594 newSql.append(" = ?)");
595 }
596
597 newSql.append(StringPool.CLOSE_PARENTHESIS);
598
599 if (!last) {
600 newSql.append(" [$AND_OR_CONNECTOR$]");
601 }
602
603 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
604 }
605
606 public String replaceKeywords(
607 String sql, String field, boolean last, long[] values) {
608
609 if ((values != null) && (values.length == 1)) {
610 return sql;
611 }
612
613 StringBundler oldSql = new StringBundler(4);
614
615 oldSql.append(StringPool.OPEN_PARENTHESIS);
616 oldSql.append(field);
617 oldSql.append(" = ?)");
618
619 if (!last) {
620 oldSql.append(" [$AND_OR_CONNECTOR$]");
621 }
622
623 if ((values == null) || (values.length == 0)) {
624 return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
625 }
626
627 StringBundler newSql = new StringBundler(values.length * 4 + 3);
628
629 newSql.append(StringPool.OPEN_PARENTHESIS);
630
631 for (int i = 0; i < values.length; i++) {
632 if (i > 0) {
633 newSql.append(" OR ");
634 }
635
636 newSql.append(StringPool.OPEN_PARENTHESIS);
637 newSql.append(field);
638 newSql.append(" = ?)");
639 }
640
641 newSql.append(StringPool.CLOSE_PARENTHESIS);
642
643 if (!last) {
644 newSql.append(" [$AND_OR_CONNECTOR$]");
645 }
646
647 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
648 }
649
650 public String replaceKeywords(
651 String sql, String field, String operator, boolean last,
652 String[] values) {
653
654 if ((values != null) && (values.length <= 1)) {
655 return sql;
656 }
657
658 StringBundler oldSql = new StringBundler(6);
659
660 oldSql.append(StringPool.OPEN_PARENTHESIS);
661 oldSql.append(field);
662 oldSql.append(" ");
663 oldSql.append(operator);
664 oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
665
666 if (!last) {
667 oldSql.append(" [$AND_OR_CONNECTOR$]");
668 }
669
670 StringBundler newSql = new StringBundler(values.length * 6 + 3);
671
672 newSql.append(StringPool.OPEN_PARENTHESIS);
673
674 for (int i = 0; i < values.length; i++) {
675 if (i > 0) {
676 newSql.append(" OR ");
677 }
678
679 newSql.append(StringPool.OPEN_PARENTHESIS);
680 newSql.append(field);
681 newSql.append(" ");
682 newSql.append(operator);
683 newSql.append(" ? [$AND_OR_NULL_CHECK$])");
684 }
685
686 newSql.append(StringPool.CLOSE_PARENTHESIS);
687
688 if (!last) {
689 newSql.append(" [$AND_OR_CONNECTOR$]");
690 }
691
692 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
693 }
694
695 public String replaceOrderBy(String sql, OrderByComparator obc) {
696 if (obc == null) {
697 return sql;
698 }
699
700 String orderBy = obc.getOrderBy();
701
702 int pos = sql.indexOf(_ORDER_BY_CLAUSE);
703
704 if ((pos != -1) && (pos < sql.length())) {
705 sql = sql.substring(0, pos + _ORDER_BY_CLAUSE.length()).concat(
706 orderBy);
707 }
708 else {
709 sql = sql.concat(_ORDER_BY_CLAUSE).concat(orderBy);
710 }
711
712 return sql;
713 }
714
715 protected String[] getConfigs() {
716 if (PortalClassLoaderUtil.getClassLoader() ==
717 CustomSQL.class.getClassLoader()) {
718
719 Properties propsUtil = PortalUtil.getPortalProperties();
720
721 return StringUtil.split(
722 propsUtil.getProperty("custom.sql.configs"));
723 }
724 else {
725 return new String[] {"custom-sql/default.xml"};
726 }
727 }
728
729 protected void read(ClassLoader classLoader, String source)
730 throws Exception {
731
732 InputStream is = classLoader.getResourceAsStream(source);
733
734 if (is == null) {
735 return;
736 }
737
738 if (_log.isDebugEnabled()) {
739 _log.debug("Loading " + source);
740 }
741
742 Document document = SAXReaderUtil.read(is);
743
744 Element rootElement = document.getRootElement();
745
746 for (Element sqlElement : rootElement.elements("sql")) {
747 String file = sqlElement.attributeValue("file");
748
749 if (Validator.isNotNull(file)) {
750 read(classLoader, file);
751 }
752 else {
753 String id = sqlElement.attributeValue("id");
754 String content = transform(sqlElement.getText());
755
756 content = replaceIsNull(content);
757
758 _sqlPool.put(id, content);
759 }
760 }
761 }
762
763 protected String transform(String sql) {
764 sql = PortalUtil.transformCustomSQL(sql);
765
766 StringBundler sb = new StringBundler();
767
768 try {
769 UnsyncBufferedReader unsyncBufferedReader =
770 new UnsyncBufferedReader(new UnsyncStringReader(sql));
771
772 String line = null;
773
774 while ((line = unsyncBufferedReader.readLine()) != null) {
775 sb.append(line.trim());
776 sb.append(StringPool.SPACE);
777 }
778
779 unsyncBufferedReader.close();
780 }
781 catch (IOException ioe) {
782 return sql;
783 }
784
785 return sb.toString();
786 }
787
788 private static final String _GROUP_BY_CLAUSE = " GROUP BY ";
789
790 private static final String _ORDER_BY_CLAUSE = " ORDER BY ";
791
792 private static final String _STATUS_CONDITION_DEFAULT = "status = ?";
793
794 private static final String _STATUS_CONDITION_EMPTY =
795 WorkflowConstants.STATUS_ANY + " = ?";
796
797 private static final String _STATUS_CONDITION_INVERSE = "status != ?";
798
799 private static final String _STATUS_KEYWORD = "[$STATUS$]";
800
801 private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
802
803 private String _functionIsNotNull;
804 private String _functionIsNull;
805 private Map<String, String> _sqlPool;
806 private boolean _vendorDB2;
807 private boolean _vendorHSQL;
808 private boolean _vendorInformix;
809 private boolean _vendorMySQL;
810 private boolean _vendorOracle;
811 private boolean _vendorPostgreSQL;
812 private boolean _vendorSybase;
813
814 }