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