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 "ISNULL(?, '1') = '0'";
071
072 public static final String SYBASE_FUNCTION_IS_NULL = "ISNULL(?, '1') = '1'";
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 (lowerCase) {
295 keywords = keywords.toLowerCase();
296 }
297
298 keywords = keywords.trim();
299
300 String[] keywordsArray = StringUtil.split(keywords, StringPool.SPACE);
301
302 for (int i = 0; i < keywordsArray.length; i++) {
303 String keyword = keywordsArray[i];
304
305 keywordsArray[i] =
306 StringPool.PERCENT + keyword + StringPool.PERCENT;
307 }
308
309 return keywordsArray;
310 }
311
312 public String[] keywords(String[] keywordsArray) {
313 return keywords(keywordsArray, true);
314 }
315
316 public String[] keywords(String[] keywordsArray, boolean lowerCase) {
317 if ((keywordsArray == null) || (keywordsArray.length == 0)) {
318 keywordsArray = new String[] {null};
319 }
320
321 if (lowerCase) {
322 for (int i = 0; i < keywordsArray.length; i++) {
323 keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
324 }
325 }
326
327 return keywordsArray;
328 }
329
330 public String removeGroupBy(String sql) {
331 int x = sql.indexOf(_GROUP_BY_CLAUSE);
332
333 if (x != -1) {
334 int y = sql.indexOf(_ORDER_BY_CLAUSE);
335
336 if (y == -1) {
337 sql = sql.substring(0, x);
338 }
339 else {
340 sql = sql.substring(0, x) + sql.substring(y);
341 }
342 }
343
344 return sql;
345 }
346
347 public String removeOrderBy(String sql) {
348
349
350
351 AtomicReference<String> sqlAtomicReference =
352 new AtomicReference<String>(sql);
353
354 int pos = sqlAtomicReference.get().indexOf(_ORDER_BY_CLAUSE);
355
356 if (pos != -1) {
357 sql = sqlAtomicReference.get().substring(0, pos);
358 }
359
360
365
366 return sql;
367 }
368
369 public String replaceAndOperator(String sql, boolean andOperator) {
370 String andOrConnector = "OR";
371 String andOrNullCheck = "AND ? IS NOT NULL";
372
373 if (andOperator) {
374 andOrConnector = "AND";
375 andOrNullCheck = "OR ? IS NULL";
376 }
377
378 sql = StringUtil.replace(
379 sql,
380 new String[] {
381 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
382 },
383 new String[] {
384 andOrConnector, andOrNullCheck
385 });
386
387 if (_vendorPostgreSQL) {
388 sql = StringUtil.replace(
389 sql,
390 new String[] {
391 "Date >= ? AND ? IS NOT NULL",
392 "Date <= ? AND ? IS NOT NULL",
393 "Date >= ? OR ? IS NULL",
394 "Date <= ? OR ? IS NULL"
395 },
396 new String[] {
397 "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
398 "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
399 "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
400 "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
401 });
402 }
403
404 sql = replaceIsNull(sql);
405
406 return sql;
407 }
408
409 public String replaceIsNull(String sql) {
410 if (Validator.isNotNull(_functionIsNull)) {
411 sql = StringUtil.replace(
412 sql,
413 new String[] {
414 "? IS NULL", "? IS NOT NULL"
415 },
416 new String[] {
417 _functionIsNull,
418 _functionIsNotNull
419 });
420 }
421
422 return sql;
423 }
424
425 public String replaceKeywords(
426 String sql, String field, boolean last, int[] values) {
427
428 StringBundler oldSql = new StringBundler(4);
429
430 oldSql.append("(");
431 oldSql.append(field);
432 oldSql.append(" = ?)");
433
434 if (!last) {
435 oldSql.append(" [$AND_OR_CONNECTOR$]");
436 }
437
438 if ((values == null) || (values.length == 0)) {
439 return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
440 }
441
442 StringBundler newSql = new StringBundler(values.length * 4 + 3);
443
444 newSql.append("(");
445
446 for (int i = 0; i < values.length; i++) {
447 if (i > 0) {
448 newSql.append(" OR ");
449 }
450
451 newSql.append("(");
452 newSql.append(field);
453 newSql.append(" = ?)");
454 }
455
456 newSql.append(")");
457
458 if (!last) {
459 newSql.append(" [$AND_OR_CONNECTOR$]");
460 }
461
462 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
463 }
464
465 public String replaceKeywords(
466 String sql, String field, String operator, boolean last,
467 String[] values) {
468
469 if (values.length == 0) {
470 return sql;
471 }
472
473 StringBundler oldSql = new StringBundler(6);
474
475 oldSql.append("(");
476 oldSql.append(field);
477 oldSql.append(" ");
478 oldSql.append(operator);
479 oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
480
481 if (!last) {
482 oldSql.append(" [$AND_OR_CONNECTOR$]");
483 }
484
485 StringBundler newSql = new StringBundler(values.length * 6 + 3);
486
487 newSql.append("(");
488
489 for (int i = 0; i < values.length; i++) {
490 if (i > 0) {
491 newSql.append(" OR ");
492 }
493
494 newSql.append("(");
495 newSql.append(field);
496 newSql.append(" ");
497 newSql.append(operator);
498 newSql.append(" ? [$AND_OR_NULL_CHECK$])");
499 }
500
501 newSql.append(")");
502
503 if (!last) {
504 newSql.append(" [$AND_OR_CONNECTOR$]");
505 }
506
507 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
508 }
509
510 public String replaceGroupBy(String sql, String groupBy) {
511 if (groupBy == null) {
512 return sql;
513 }
514
515 int x = sql.indexOf(_GROUP_BY_CLAUSE);
516
517 if (x != -1) {
518 int y = sql.indexOf(_ORDER_BY_CLAUSE);
519
520 if (y == -1) {
521 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
522 groupBy);
523 }
524 else {
525 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
526 groupBy).concat(sql.substring(y));
527 }
528 }
529 else {
530 int y = sql.indexOf(_ORDER_BY_CLAUSE);
531
532 if (y == -1) {
533 sql = sql.concat(_GROUP_BY_CLAUSE).concat(groupBy);
534 }
535 else {
536 StringBundler sb = new StringBundler();
537
538 sb.append(sql.substring(0, y));
539 sb.append(_GROUP_BY_CLAUSE);
540 sb.append(groupBy);
541 sb.append(sql.substring(y));
542
543 sql = sb.toString();
544 }
545 }
546
547 return sql;
548 }
549
550 public String replaceOrderBy(String sql, OrderByComparator obc) {
551 if (obc == null) {
552 return sql;
553 }
554
555 return removeOrderBy(sql).concat(_ORDER_BY_CLAUSE).concat(
556 obc.getOrderBy());
557 }
558
559 protected String[] getConfigs() {
560 if (PortalClassLoaderUtil.getClassLoader() ==
561 CustomSQL.class.getClassLoader()) {
562
563 Properties propsUtil = PortalUtil.getPortalProperties();
564
565 return StringUtil.split(
566 propsUtil.getProperty("custom.sql.configs"));
567 }
568 else {
569 return new String[] {"custom-sql/default.xml"};
570 }
571 }
572
573 protected void read(ClassLoader classLoader, String source)
574 throws Exception {
575
576 InputStream is = classLoader.getResourceAsStream(source);
577
578 if (is == null) {
579 return;
580 }
581
582 if (_log.isDebugEnabled()) {
583 _log.debug("Loading " + source);
584 }
585
586 Document document = SAXReaderUtil.read(is);
587
588 Element rootElement = document.getRootElement();
589
590 for (Element sqlElement : rootElement.elements("sql")) {
591 String file = sqlElement.attributeValue("file");
592
593 if (Validator.isNotNull(file)) {
594 read(classLoader, file);
595 }
596 else {
597 String id = sqlElement.attributeValue("id");
598 String content = transform(sqlElement.getText());
599
600 content = replaceIsNull(content);
601
602 _sqlPool.put(id, content);
603 }
604 }
605 }
606
607 protected String transform(String sql) {
608 sql = PortalUtil.transformCustomSQL(sql);
609
610 StringBundler sb = new StringBundler();
611
612 try {
613 UnsyncBufferedReader unsyncBufferedReader =
614 new UnsyncBufferedReader(new UnsyncStringReader(sql));
615
616 String line = null;
617
618 while ((line = unsyncBufferedReader.readLine()) != null) {
619 sb.append(line.trim());
620 sb.append(StringPool.SPACE);
621 }
622
623 unsyncBufferedReader.close();
624 }
625 catch (IOException ioe) {
626 return sql;
627 }
628
629 return sb.toString();
630 }
631
632 private static final String _GROUP_BY_CLAUSE = " GROUP BY ";
633
634 private static final String _ORDER_BY_CLAUSE = " ORDER BY ";
635
636 private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
637
638 private String _functionIsNotNull;
639 private String _functionIsNull;
640 private Map<String, String> _sqlPool;
641 private boolean _vendorDB2;
642 private boolean _vendorInformix;
643 private boolean _vendorMySQL;
644 private boolean _vendorOracle;
645 private boolean _vendorPostgreSQL;
646 private boolean _vendorSybase;
647
648 }