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