1
14
15 package com.liferay.util.dao.orm;
16
17 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
18 import com.liferay.portal.kernel.io.unsync.UnsyncBufferedReader;
19 import com.liferay.portal.kernel.io.unsync.UnsyncStringReader;
20 import com.liferay.portal.kernel.log.Log;
21 import com.liferay.portal.kernel.log.LogFactoryUtil;
22 import com.liferay.portal.kernel.util.GetterUtil;
23 import com.liferay.portal.kernel.util.OrderByComparator;
24 import com.liferay.portal.kernel.util.PortalClassLoaderUtil;
25 import com.liferay.portal.kernel.util.StringBundler;
26 import com.liferay.portal.kernel.util.StringPool;
27 import com.liferay.portal.kernel.util.StringUtil;
28 import com.liferay.portal.kernel.util.Validator;
29 import com.liferay.portal.kernel.xml.Document;
30 import com.liferay.portal.kernel.xml.Element;
31 import com.liferay.portal.kernel.xml.SAXReaderUtil;
32 import com.liferay.portal.util.PortalUtil;
33
34 import java.io.IOException;
35 import java.io.InputStream;
36
37 import java.sql.Connection;
38 import java.sql.DatabaseMetaData;
39 import java.sql.SQLException;
40
41 import java.util.HashMap;
42 import java.util.Map;
43 import java.util.Properties;
44
45
51 public class CustomSQL {
52
53 public static final String DB2_FUNCTION_IS_NOT_NULL =
54 "CAST(? AS VARCHAR(32672)) IS NOT NULL";
55
56 public static final String DB2_FUNCTION_IS_NULL =
57 "CAST(? AS VARCHAR(32672)) IS NULL";
58
59 public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
60 "NOT lportal.isnull(?)";
61
62 public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
63
64 public static final String MYSQL_FUNCTION_IS_NOT_NULL =
65 "IFNULL(?, '1') = '0'";
66
67 public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
68
69 public static final String SYBASE_FUNCTION_IS_NOT_NULL =
70 "ISNULL(?, '1') = '0'";
71
72 public static final String SYBASE_FUNCTION_IS_NULL = "ISNULL(?, '1') = '1'";
73
74 public CustomSQL() throws SQLException {
75 Connection con = DataAccess.getConnection();
76
77 String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
78 String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
79
80 try {
81 if (Validator.isNotNull(functionIsNull) &&
82 Validator.isNotNull(functionIsNotNull)) {
83
84 _functionIsNull = functionIsNull;
85 _functionIsNotNull = functionIsNotNull;
86
87 if (_log.isDebugEnabled()) {
88 _log.info(
89 "functionIsNull is manually set to " + functionIsNull);
90 _log.info(
91 "functionIsNotNull is manually set to " +
92 functionIsNotNull);
93 }
94 }
95 else if (con != null) {
96 DatabaseMetaData metaData = con.getMetaData();
97
98 String dbName = GetterUtil.getString(
99 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
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 (int i = 0; i < configs.length; i++) {
183 read(classLoader, configs[i]);
184 }
185 }
186 catch (Exception e) {
187 _log.error(e, e);
188 }
189 }
190
191 public String get(String id) {
192 return _sqlPool.get(id);
193 }
194
195
200 public boolean isVendorDB2() {
201 return _vendorDB2;
202 }
203
204
209 public boolean isVendorInformix() {
210 return _vendorInformix;
211 }
212
213
218 public boolean isVendorMySQL() {
219 return _vendorMySQL;
220 }
221
222
230 public boolean isVendorOracle() {
231 return _vendorOracle;
232 }
233
234
239 public boolean isVendorPostgreSQL() {
240 return _vendorPostgreSQL;
241 }
242
243
248 public boolean isVendorSybase() {
249 return _vendorSybase;
250 }
251
252 public String[] keywords(String keywords) {
253 return keywords(keywords, true);
254 }
255
256 public String[] keywords(String keywords, boolean lowerCase) {
257 if (lowerCase) {
258 keywords = keywords.toLowerCase();
259 }
260
261 keywords = keywords.trim();
262
263 String[] keywordsArray = StringUtil.split(keywords, StringPool.SPACE);
264
265 for (int i = 0; i < keywordsArray.length; i++) {
266 String keyword = keywordsArray[i];
267
268 keywordsArray[i] =
269 StringPool.PERCENT + keyword + StringPool.PERCENT;
270 }
271
272 return keywordsArray;
273 }
274
275 public String[] keywords(String[] keywordsArray) {
276 return keywords(keywordsArray, true);
277 }
278
279 public String[] keywords(String[] keywordsArray, boolean lowerCase) {
280 if ((keywordsArray == null) || (keywordsArray.length == 0)) {
281 keywordsArray = new String[] {null};
282 }
283
284 if (lowerCase) {
285 for (int i = 0; i < keywordsArray.length; i++) {
286 keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
287 }
288 }
289
290 return keywordsArray;
291 }
292
293 public String removeGroupBy(String sql) {
294 int x = sql.indexOf(_GROUP_BY_CLAUSE);
295
296 if (x != -1) {
297 int y = sql.indexOf(_ORDER_BY_CLAUSE);
298
299 if (y == -1) {
300 sql = sql.substring(0, x);
301 }
302 else {
303 sql = sql.substring(0, x) + sql.substring(y);
304 }
305 }
306
307 return sql;
308 }
309
310 public String removeOrderBy(String sql) {
311 int pos = sql.indexOf(_ORDER_BY_CLAUSE);
312
313 if (pos != -1) {
314 sql = sql.substring(0, pos);
315 }
316
317 return sql;
318 }
319
320 public String replaceAndOperator(String sql, boolean andOperator) {
321 String andOrConnector = "OR";
322 String andOrNullCheck = "AND ? IS NOT NULL";
323
324 if (andOperator) {
325 andOrConnector = "AND";
326 andOrNullCheck = "OR ? IS NULL";
327 }
328
329 sql = StringUtil.replace(
330 sql,
331 new String[] {
332 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
333 },
334 new String[] {
335 andOrConnector, andOrNullCheck
336 });
337
338 if (_vendorPostgreSQL) {
339 sql = StringUtil.replace(
340 sql,
341 new String[] {
342 "Date >= ? AND ? IS NOT NULL",
343 "Date <= ? AND ? IS NOT NULL",
344 "Date >= ? OR ? IS NULL",
345 "Date <= ? OR ? IS NULL"
346 },
347 new String[] {
348 "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
349 "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
350 "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
351 "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
352 });
353 }
354
355 sql = replaceIsNull(sql);
356
357 return sql;
358 }
359
360 public String replaceIsNull(String sql) {
361 if (Validator.isNotNull(_functionIsNull)) {
362 sql = StringUtil.replace(
363 sql,
364 new String[] {
365 "? IS NULL", "? IS NOT NULL"
366 },
367 new String[] {
368 _functionIsNull,
369 _functionIsNotNull
370 });
371 }
372
373 return sql;
374 }
375
376 public String replaceKeywords(
377 String sql, String field, boolean last, int[] values) {
378
379 StringBundler oldSql = new StringBundler(4);
380
381 oldSql.append("(");
382 oldSql.append(field);
383 oldSql.append(" = ?)");
384
385 if (!last) {
386 oldSql.append(" [$AND_OR_CONNECTOR$]");
387 }
388
389 if ((values == null) || (values.length == 0)) {
390 return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
391 }
392
393 StringBundler newSql = new StringBundler(values.length * 4 + 3);
394
395 newSql.append("(");
396
397 for (int i = 0; i < values.length; i++) {
398 if (i > 0) {
399 newSql.append(" OR ");
400 }
401
402 newSql.append("(");
403 newSql.append(field);
404 newSql.append(" = ?)");
405 }
406
407 newSql.append(")");
408
409 if (!last) {
410 newSql.append(" [$AND_OR_CONNECTOR$]");
411 }
412
413 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
414 }
415
416 public String replaceKeywords(
417 String sql, String field, boolean last, long[] values) {
418
419 StringBundler oldSql = new StringBundler(4);
420
421 oldSql.append("(");
422 oldSql.append(field);
423 oldSql.append(" = ?)");
424
425 if (!last) {
426 oldSql.append(" [$AND_OR_CONNECTOR$]");
427 }
428
429 if ((values == null) || (values.length == 0)) {
430 return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
431 }
432
433 StringBundler newSql = new StringBundler(values.length * 4 + 3);
434
435 newSql.append("(");
436
437 for (int i = 0; i < values.length; i++) {
438 if (i > 0) {
439 newSql.append(" OR ");
440 }
441
442 newSql.append("(");
443 newSql.append(field);
444 newSql.append(" = ?)");
445 }
446
447 newSql.append(")");
448
449 if (!last) {
450 newSql.append(" [$AND_OR_CONNECTOR$]");
451 }
452
453 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
454 }
455
456 public String replaceKeywords(
457 String sql, String field, String operator, boolean last,
458 String[] values) {
459
460 if (values.length == 0) {
461 return sql;
462 }
463
464 StringBundler oldSql = new StringBundler(6);
465
466 oldSql.append("(");
467 oldSql.append(field);
468 oldSql.append(" ");
469 oldSql.append(operator);
470 oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
471
472 if (!last) {
473 oldSql.append(" [$AND_OR_CONNECTOR$]");
474 }
475
476 StringBundler newSql = new StringBundler(values.length * 6 + 3);
477
478 newSql.append("(");
479
480 for (int i = 0; i < values.length; i++) {
481 if (i > 0) {
482 newSql.append(" OR ");
483 }
484
485 newSql.append("(");
486 newSql.append(field);
487 newSql.append(" ");
488 newSql.append(operator);
489 newSql.append(" ? [$AND_OR_NULL_CHECK$])");
490 }
491
492 newSql.append(")");
493
494 if (!last) {
495 newSql.append(" [$AND_OR_CONNECTOR$]");
496 }
497
498 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
499 }
500
501 public String replaceGroupBy(String sql, String groupBy) {
502 if (groupBy == null) {
503 return sql;
504 }
505
506 int x = sql.indexOf(_GROUP_BY_CLAUSE);
507
508 if (x != -1) {
509 int y = sql.indexOf(_ORDER_BY_CLAUSE);
510
511 if (y == -1) {
512 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
513 groupBy);
514 }
515 else {
516 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
517 groupBy).concat(sql.substring(y));
518 }
519 }
520 else {
521 int y = sql.indexOf(_ORDER_BY_CLAUSE);
522
523 if (y == -1) {
524 sql = sql.concat(_GROUP_BY_CLAUSE).concat(groupBy);
525 }
526 else {
527 StringBundler sb = new StringBundler();
528
529 sb.append(sql.substring(0, y));
530 sb.append(_GROUP_BY_CLAUSE);
531 sb.append(groupBy);
532 sb.append(sql.substring(y));
533
534 sql = sb.toString();
535 }
536 }
537
538 return sql;
539 }
540
541 public String replaceOrderBy(String sql, OrderByComparator obc) {
542 if (obc == null) {
543 return sql;
544 }
545
546 String orderBy = obc.getOrderBy();
547
548 int pos = sql.indexOf(_ORDER_BY_CLAUSE);
549
550 if ((pos != -1) && (pos < sql.length())) {
551 sql = sql.substring(0, pos + _ORDER_BY_CLAUSE.length()).concat(
552 orderBy);
553 }
554 else {
555 sql = sql.concat(_ORDER_BY_CLAUSE).concat(orderBy);
556 }
557
558 return sql;
559 }
560
561 protected String[] getConfigs() {
562 if (PortalClassLoaderUtil.getClassLoader() ==
563 CustomSQL.class.getClassLoader()) {
564
565 Properties propsUtil = PortalUtil.getPortalProperties();
566
567 return StringUtil.split(
568 propsUtil.getProperty("custom.sql.configs"));
569 }
570 else {
571 return new String[] {"custom-sql/default.xml"};
572 }
573 }
574
575 protected void read(ClassLoader classLoader, String source)
576 throws Exception {
577
578 InputStream is = classLoader.getResourceAsStream(source);
579
580 if (is == null) {
581 return;
582 }
583
584 if (_log.isDebugEnabled()) {
585 _log.debug("Loading " + source);
586 }
587
588 Document document = SAXReaderUtil.read(is);
589
590 Element rootElement = document.getRootElement();
591
592 for (Element sqlElement : rootElement.elements("sql")) {
593 String file = sqlElement.attributeValue("file");
594
595 if (Validator.isNotNull(file)) {
596 read(classLoader, file);
597 }
598 else {
599 String id = sqlElement.attributeValue("id");
600 String content = transform(sqlElement.getText());
601
602 content = replaceIsNull(content);
603
604 _sqlPool.put(id, content);
605 }
606 }
607 }
608
609 protected String transform(String sql) {
610 sql = PortalUtil.transformCustomSQL(sql);
611
612 StringBundler sb = new StringBundler();
613
614 try {
615 UnsyncBufferedReader unsyncBufferedReader =
616 new UnsyncBufferedReader(new UnsyncStringReader(sql));
617
618 String line = null;
619
620 while ((line = unsyncBufferedReader.readLine()) != null) {
621 sb.append(line.trim());
622 sb.append(StringPool.SPACE);
623 }
624
625 unsyncBufferedReader.close();
626 }
627 catch (IOException ioe) {
628 return sql;
629 }
630
631 return sb.toString();
632 }
633
634 private static final String _GROUP_BY_CLAUSE = " GROUP BY ";
635
636 private static final String _ORDER_BY_CLAUSE = " ORDER BY ";
637
638 private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
639
640 private String _functionIsNotNull;
641 private String _functionIsNull;
642 private Map<String, String> _sqlPool;
643 private boolean _vendorDB2;
644 private boolean _vendorInformix;
645 private boolean _vendorMySQL;
646 private boolean _vendorOracle;
647 private boolean _vendorPostgreSQL;
648 private boolean _vendorSybase;
649
650 }