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