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
045
050 public class CustomSQL {
051
052 public static final String DB2_FUNCTION_IS_NOT_NULL =
053 "CAST(? AS VARCHAR(32672)) IS NOT NULL";
054
055 public static final String DB2_FUNCTION_IS_NULL =
056 "CAST(? AS VARCHAR(32672)) IS NULL";
057
058 public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
059 "NOT lportal.isnull(?)";
060
061 public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
062
063 public static final String MYSQL_FUNCTION_IS_NOT_NULL =
064 "IFNULL(?, '1') = '0'";
065
066 public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
067
068 public static final String SYBASE_FUNCTION_IS_NOT_NULL =
069 "CONVERT(VARCHAR,?) IS NOT NULL";
070
071 public static final String SYBASE_FUNCTION_IS_NULL =
072 "CONVERT(VARCHAR,?) IS NULL";
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 (Validator.isNull(keywords)) {
295 return new String[] {null};
296 }
297
298 if (lowerCase) {
299 keywords = keywords.toLowerCase();
300 }
301
302 keywords = keywords.trim();
303
304 String[] keywordsArray = keywords.split("\\s+");
305
306 for (int i = 0; i < keywordsArray.length; i++) {
307 String keyword = keywordsArray[i];
308
309 keywordsArray[i] =
310 StringPool.PERCENT + keyword + StringPool.PERCENT;
311 }
312
313 return keywordsArray;
314 }
315
316 public String[] keywords(String[] keywordsArray) {
317 return keywords(keywordsArray, true);
318 }
319
320 public String[] keywords(String[] keywordsArray, boolean lowerCase) {
321 if ((keywordsArray == null) || (keywordsArray.length == 0)) {
322 return new String[] {null};
323 }
324
325 if (lowerCase) {
326 for (int i = 0; i < keywordsArray.length; i++) {
327 keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
328 }
329 }
330
331 return keywordsArray;
332 }
333
334 public String removeGroupBy(String sql) {
335 int x = sql.indexOf(_GROUP_BY_CLAUSE);
336
337 if (x != -1) {
338 int y = sql.indexOf(_ORDER_BY_CLAUSE);
339
340 if (y == -1) {
341 sql = sql.substring(0, x);
342 }
343 else {
344 sql = sql.substring(0, x) + sql.substring(y);
345 }
346 }
347
348 return sql;
349 }
350
351 public String removeOrderBy(String sql) {
352 int pos = sql.indexOf(_ORDER_BY_CLAUSE);
353
354 if (pos != -1) {
355 sql = sql.substring(0, pos);
356 }
357
358 return sql;
359 }
360
361 public String replaceAndOperator(String sql, boolean andOperator) {
362 String andOrConnector = "OR";
363 String andOrNullCheck = "AND ? IS NOT NULL";
364
365 if (andOperator) {
366 andOrConnector = "AND";
367 andOrNullCheck = "OR ? IS NULL";
368 }
369
370 sql = StringUtil.replace(
371 sql,
372 new String[] {
373 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
374 },
375 new String[] {
376 andOrConnector, andOrNullCheck
377 });
378
379 if (_vendorPostgreSQL) {
380 sql = StringUtil.replace(
381 sql,
382 new String[] {
383 "Date >= ? AND ? IS NOT NULL",
384 "Date <= ? AND ? IS NOT NULL",
385 "Date >= ? OR ? IS NULL",
386 "Date <= ? OR ? IS NULL"
387 },
388 new String[] {
389 "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
390 "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
391 "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
392 "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
393 });
394 }
395
396 sql = replaceIsNull(sql);
397
398 return sql;
399 }
400
401 public String replaceIsNull(String sql) {
402 if (Validator.isNotNull(_functionIsNull)) {
403 sql = StringUtil.replace(
404 sql,
405 new String[] {
406 "? IS NULL", "? IS NOT NULL"
407 },
408 new String[] {
409 _functionIsNull,
410 _functionIsNotNull
411 });
412 }
413
414 return sql;
415 }
416
417 public String replaceKeywords(
418 String sql, String field, boolean last, int[] values) {
419
420 if ((values != null) && (values.length == 1)) {
421 return sql;
422 }
423
424 StringBundler oldSql = new StringBundler(4);
425
426 oldSql.append("(");
427 oldSql.append(field);
428 oldSql.append(" = ?)");
429
430 if (!last) {
431 oldSql.append(" [$AND_OR_CONNECTOR$]");
432 }
433
434 if ((values == null) || (values.length == 0)) {
435 return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
436 }
437
438 StringBundler newSql = new StringBundler(values.length * 4 + 3);
439
440 newSql.append("(");
441
442 for (int i = 0; i < values.length; i++) {
443 if (i > 0) {
444 newSql.append(" OR ");
445 }
446
447 newSql.append("(");
448 newSql.append(field);
449 newSql.append(" = ?)");
450 }
451
452 newSql.append(")");
453
454 if (!last) {
455 newSql.append(" [$AND_OR_CONNECTOR$]");
456 }
457
458 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
459 }
460
461 public String replaceKeywords(
462 String sql, String field, boolean last, long[] values) {
463
464 if ((values != null) && (values.length == 1)) {
465 return sql;
466 }
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 != null) && (values.length <= 1)) {
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 String orderBy = obc.getOrderBy();
596
597 int pos = sql.indexOf(_ORDER_BY_CLAUSE);
598
599 if ((pos != -1) && (pos < sql.length())) {
600 sql = sql.substring(0, pos + _ORDER_BY_CLAUSE.length()).concat(
601 orderBy);
602 }
603 else {
604 sql = sql.concat(_ORDER_BY_CLAUSE).concat(orderBy);
605 }
606
607 return sql;
608 }
609
610 protected String[] getConfigs() {
611 if (PortalClassLoaderUtil.getClassLoader() ==
612 CustomSQL.class.getClassLoader()) {
613
614 Properties propsUtil = PortalUtil.getPortalProperties();
615
616 return StringUtil.split(
617 propsUtil.getProperty("custom.sql.configs"));
618 }
619 else {
620 return new String[] {"custom-sql/default.xml"};
621 }
622 }
623
624 protected void read(ClassLoader classLoader, String source)
625 throws Exception {
626
627 InputStream is = classLoader.getResourceAsStream(source);
628
629 if (is == null) {
630 return;
631 }
632
633 if (_log.isDebugEnabled()) {
634 _log.debug("Loading " + source);
635 }
636
637 Document document = SAXReaderUtil.read(is);
638
639 Element rootElement = document.getRootElement();
640
641 for (Element sqlElement : rootElement.elements("sql")) {
642 String file = sqlElement.attributeValue("file");
643
644 if (Validator.isNotNull(file)) {
645 read(classLoader, file);
646 }
647 else {
648 String id = sqlElement.attributeValue("id");
649 String content = transform(sqlElement.getText());
650
651 content = replaceIsNull(content);
652
653 _sqlPool.put(id, content);
654 }
655 }
656 }
657
658 protected String transform(String sql) {
659 sql = PortalUtil.transformCustomSQL(sql);
660
661 StringBundler sb = new StringBundler();
662
663 try {
664 UnsyncBufferedReader unsyncBufferedReader =
665 new UnsyncBufferedReader(new UnsyncStringReader(sql));
666
667 String line = null;
668
669 while ((line = unsyncBufferedReader.readLine()) != null) {
670 sb.append(line.trim());
671 sb.append(StringPool.SPACE);
672 }
673
674 unsyncBufferedReader.close();
675 }
676 catch (IOException ioe) {
677 return sql;
678 }
679
680 return sb.toString();
681 }
682
683 private static final String _GROUP_BY_CLAUSE = " GROUP BY ";
684
685 private static final String _ORDER_BY_CLAUSE = " ORDER BY ";
686
687 private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
688
689 private String _functionIsNotNull;
690 private String _functionIsNull;
691 private Map<String, String> _sqlPool;
692 private boolean _vendorDB2;
693 private boolean _vendorInformix;
694 private boolean _vendorMySQL;
695 private boolean _vendorOracle;
696 private boolean _vendorPostgreSQL;
697 private boolean _vendorSybase;
698
699 }