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