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 import java.util.concurrent.atomic.AtomicReference;
45
46
52 public class CustomSQL {
53
54 public static final String DB2_FUNCTION_IS_NOT_NULL =
55 "CAST(? AS VARCHAR(32672)) IS NOT NULL";
56
57 public static final String DB2_FUNCTION_IS_NULL =
58 "CAST(? AS VARCHAR(32672)) IS NULL";
59
60 public static final String INFORMIX_FUNCTION_IS_NOT_NULL =
61 "NOT lportal.isnull(?)";
62
63 public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
64
65 public static final String MYSQL_FUNCTION_IS_NOT_NULL =
66 "IFNULL(?, '1') = '0'";
67
68 public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
69
70 public static final String SYBASE_FUNCTION_IS_NOT_NULL =
71 "ISNULL(?, '1') = '0'";
72
73 public static final String SYBASE_FUNCTION_IS_NULL = "ISNULL(?, '1') = '1'";
74
75 public CustomSQL() throws SQLException {
76 Connection con = DataAccess.getConnection();
77
78 String functionIsNull = PortalUtil.getCustomSQLFunctionIsNull();
79 String functionIsNotNull = PortalUtil.getCustomSQLFunctionIsNotNull();
80
81 try {
82 if (Validator.isNotNull(functionIsNull) &&
83 Validator.isNotNull(functionIsNotNull)) {
84
85 _functionIsNull = functionIsNull;
86 _functionIsNotNull = functionIsNotNull;
87
88 if (_log.isDebugEnabled()) {
89 _log.info(
90 "functionIsNull is manually set to " + functionIsNull);
91 _log.info(
92 "functionIsNotNull is manually set to " +
93 functionIsNotNull);
94 }
95 }
96 else if (con != null) {
97 DatabaseMetaData metaData = con.getMetaData();
98
99 String dbName = GetterUtil.getString(
100 metaData.getDatabaseProductName());
101
102 if (_log.isInfoEnabled()) {
103 _log.info("Database name " + dbName);
104 }
105
106 if (dbName.startsWith("DB2")) {
107 _vendorDB2 = true;
108 _functionIsNull = DB2_FUNCTION_IS_NULL;
109 _functionIsNotNull = DB2_FUNCTION_IS_NOT_NULL;
110
111 if (_log.isInfoEnabled()) {
112 _log.info("Detected DB2 with database name " + dbName);
113 }
114 }
115 else if (dbName.startsWith("Informix")) {
116 _vendorInformix = true;
117 _functionIsNull = INFORMIX_FUNCTION_IS_NULL;
118 _functionIsNotNull = INFORMIX_FUNCTION_IS_NOT_NULL;
119
120 if (_log.isInfoEnabled()) {
121 _log.info(
122 "Detected Informix with database name " + dbName);
123 }
124 }
125 else if (dbName.startsWith("MySQL")) {
126 _vendorMySQL = true;
127
130 if (_log.isInfoEnabled()) {
131 _log.info(
132 "Detected MySQL with database name " + dbName);
133 }
134 }
135 else if (dbName.startsWith("Sybase") || dbName.equals("ASE")) {
136 _vendorSybase = true;
137 _functionIsNull = SYBASE_FUNCTION_IS_NULL;
138 _functionIsNotNull = SYBASE_FUNCTION_IS_NOT_NULL;
139
140 if (_log.isInfoEnabled()) {
141 _log.info(
142 "Detected Sybase with database name " + dbName);
143 }
144 }
145 else if (dbName.startsWith("Oracle")) {
146 _vendorOracle = true;
147
148 if (_log.isInfoEnabled()) {
149 _log.info(
150 "Detected Oracle with database name " + dbName);
151 }
152 }
153 else if (dbName.startsWith("PostgreSQL")) {
154 _vendorPostgreSQL = true;
155
156 if (_log.isInfoEnabled()) {
157 _log.info(
158 "Detected PostgreSQL with database name " + dbName);
159 }
160 }
161 else {
162 if (_log.isDebugEnabled()) {
163 _log.debug(
164 "Unable to detect database with name " + dbName);
165 }
166 }
167 }
168 }
169 catch (Exception e) {
170 _log.error(e, e);
171 }
172 finally {
173 DataAccess.cleanUp(con);
174 }
175
176 _sqlPool = new HashMap<String, String>();
177
178 try {
179 ClassLoader classLoader = getClass().getClassLoader();
180
181 String[] configs = getConfigs();
182
183 for (int i = 0; i < configs.length; i++) {
184 read(classLoader, configs[i]);
185 }
186 }
187 catch (Exception e) {
188 _log.error(e, e);
189 }
190 }
191
192 public String get(String id) {
193 return _sqlPool.get(id);
194 }
195
196
201 public boolean isVendorDB2() {
202 return _vendorDB2;
203 }
204
205
210 public boolean isVendorInformix() {
211 return _vendorInformix;
212 }
213
214
219 public boolean isVendorMySQL() {
220 return _vendorMySQL;
221 }
222
223
231 public boolean isVendorOracle() {
232 return _vendorOracle;
233 }
234
235
240 public boolean isVendorPostgreSQL() {
241 return _vendorPostgreSQL;
242 }
243
244
249 public boolean isVendorSybase() {
250 return _vendorSybase;
251 }
252
253 public String[] keywords(String keywords) {
254 return keywords(keywords, true);
255 }
256
257 public String[] keywords(String keywords, boolean lowerCase) {
258 if (lowerCase) {
259 keywords = keywords.toLowerCase();
260 }
261
262 keywords = keywords.trim();
263
264 String[] keywordsArray = StringUtil.split(keywords, StringPool.SPACE);
265
266 for (int i = 0; i < keywordsArray.length; i++) {
267 String keyword = keywordsArray[i];
268
269 keywordsArray[i] =
270 StringPool.PERCENT + keyword + StringPool.PERCENT;
271 }
272
273 return keywordsArray;
274 }
275
276 public String[] keywords(String[] keywordsArray) {
277 return keywords(keywordsArray, true);
278 }
279
280 public String[] keywords(String[] keywordsArray, boolean lowerCase) {
281 if ((keywordsArray == null) || (keywordsArray.length == 0)) {
282 keywordsArray = new String[] {null};
283 }
284
285 if (lowerCase) {
286 for (int i = 0; i < keywordsArray.length; i++) {
287 keywordsArray[i] = StringUtil.lowerCase(keywordsArray[i]);
288 }
289 }
290
291 return keywordsArray;
292 }
293
294 public String removeGroupBy(String sql) {
295 int x = sql.indexOf(_GROUP_BY_CLAUSE);
296
297 if (x != -1) {
298 int y = sql.indexOf(_ORDER_BY_CLAUSE);
299
300 if (y == -1) {
301 sql = sql.substring(0, x);
302 }
303 else {
304 sql = sql.substring(0, x) + sql.substring(y);
305 }
306 }
307
308 return sql;
309 }
310
311 public String removeOrderBy(String sql) {
312
313
315 AtomicReference<String> sqlAtomicReference =
316 new AtomicReference<String>(sql);
317
318 int pos = sqlAtomicReference.get().indexOf(_ORDER_BY_CLAUSE);
319
320 if (pos != -1) {
321 sql = sqlAtomicReference.get().substring(0, pos);
322 }
323
324
329
330 return sql;
331 }
332
333 public String replaceAndOperator(String sql, boolean andOperator) {
334 String andOrConnector = "OR";
335 String andOrNullCheck = "AND ? IS NOT NULL";
336
337 if (andOperator) {
338 andOrConnector = "AND";
339 andOrNullCheck = "OR ? IS NULL";
340 }
341
342 sql = StringUtil.replace(
343 sql,
344 new String[] {
345 "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
346 },
347 new String[] {
348 andOrConnector, andOrNullCheck
349 });
350
351 if (_vendorPostgreSQL) {
352 sql = StringUtil.replace(
353 sql,
354 new String[] {
355 "Date >= ? AND ? IS NOT NULL",
356 "Date <= ? AND ? IS NOT NULL",
357 "Date >= ? OR ? IS NULL",
358 "Date <= ? OR ? IS NULL"
359 },
360 new String[] {
361 "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
362 "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
363 "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
364 "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL"
365 });
366 }
367
368 sql = replaceIsNull(sql);
369
370 return sql;
371 }
372
373 public String replaceIsNull(String sql) {
374 if (Validator.isNotNull(_functionIsNull)) {
375 sql = StringUtil.replace(
376 sql,
377 new String[] {
378 "? IS NULL", "? IS NOT NULL"
379 },
380 new String[] {
381 _functionIsNull,
382 _functionIsNotNull
383 });
384 }
385
386 return sql;
387 }
388
389 public String replaceKeywords(
390 String sql, String field, int[] values, boolean last) {
391
392 StringBundler oldSql = new StringBundler(6);
393
394 oldSql.append("(");
395 oldSql.append(field);
396 oldSql.append(" = ?)");
397
398 if (!last) {
399 oldSql.append(" [$AND_OR_CONNECTOR$]");
400 }
401
402 if ((values == null) || (values.length == 0)) {
403 return StringUtil.replace(sql, oldSql.toString(), StringPool.BLANK);
404 }
405
406 StringBundler newSql = new StringBundler(values.length * 6 + 3);
407
408 newSql.append("(");
409
410 for (int i = 0; i < values.length; i++) {
411 if (i > 0) {
412 newSql.append(" OR ");
413 }
414
415 newSql.append("(");
416 newSql.append(field);
417 newSql.append(" = ?)");
418 }
419
420 newSql.append(")");
421
422 if (!last) {
423 newSql.append(" [$AND_OR_CONNECTOR$]");
424 }
425
426 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
427 }
428
429 public String replaceKeywords(
430 String sql, String field, String operator, boolean last,
431 String[] values) {
432
433 if (values.length == 0) {
434 return sql;
435 }
436
437 StringBundler oldSql = new StringBundler(6);
438
439 oldSql.append("(");
440 oldSql.append(field);
441 oldSql.append(" ");
442 oldSql.append(operator);
443 oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
444
445 if (!last) {
446 oldSql.append(" [$AND_OR_CONNECTOR$]");
447 }
448
449 StringBundler newSql = new StringBundler(values.length * 6 + 3);
450
451 newSql.append("(");
452
453 for (int i = 0; i < values.length; i++) {
454 if (i > 0) {
455 newSql.append(" OR ");
456 }
457
458 newSql.append("(");
459 newSql.append(field);
460 newSql.append(" ");
461 newSql.append(operator);
462 newSql.append(" ? [$AND_OR_NULL_CHECK$])");
463 }
464
465 newSql.append(")");
466
467 if (!last) {
468 newSql.append(" [$AND_OR_CONNECTOR$]");
469 }
470
471 return StringUtil.replace(sql, oldSql.toString(), newSql.toString());
472 }
473
474 public String replaceGroupBy(String sql, String groupBy) {
475 if (groupBy == null) {
476 return sql;
477 }
478
479 int x = sql.indexOf(_GROUP_BY_CLAUSE);
480
481 if (x != -1) {
482 int y = sql.indexOf(_ORDER_BY_CLAUSE);
483
484 if (y == -1) {
485 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
486 groupBy);
487 }
488 else {
489 sql = sql.substring(0, x + _GROUP_BY_CLAUSE.length()).concat(
490 groupBy).concat(sql.substring(y));
491 }
492 }
493 else {
494 int y = sql.indexOf(_ORDER_BY_CLAUSE);
495
496 if (y == -1) {
497 sql = sql.concat(_GROUP_BY_CLAUSE).concat(groupBy);
498 }
499 else {
500 StringBundler sb = new StringBundler();
501
502 sb.append(sql.substring(0, y));
503 sb.append(_GROUP_BY_CLAUSE);
504 sb.append(groupBy);
505 sb.append(sql.substring(y));
506
507 sql = sb.toString();
508 }
509 }
510
511 return sql;
512 }
513
514 public String replaceOrderBy(String sql, OrderByComparator obc) {
515 if (obc == null) {
516 return sql;
517 }
518
519 return removeOrderBy(sql).concat(_ORDER_BY_CLAUSE).concat(
520 obc.getOrderBy());
521 }
522
523 protected String[] getConfigs() {
524 if (PortalClassLoaderUtil.getClassLoader() ==
525 CustomSQL.class.getClassLoader()) {
526
527 Properties propsUtil = PortalUtil.getPortalProperties();
528
529 return StringUtil.split(
530 propsUtil.getProperty("custom.sql.configs"));
531 }
532 else {
533 return new String[] {"custom-sql/default.xml"};
534 }
535 }
536
537 protected void read(ClassLoader classLoader, String source)
538 throws Exception {
539
540 InputStream is = classLoader.getResourceAsStream(source);
541
542 if (is == null) {
543 return;
544 }
545
546 if (_log.isDebugEnabled()) {
547 _log.debug("Loading " + source);
548 }
549
550 Document document = SAXReaderUtil.read(is);
551
552 Element rootElement = document.getRootElement();
553
554 for (Element sqlElement : rootElement.elements("sql")) {
555 String file = sqlElement.attributeValue("file");
556
557 if (Validator.isNotNull(file)) {
558 read(classLoader, file);
559 }
560 else {
561 String id = sqlElement.attributeValue("id");
562 String content = transform(sqlElement.getText());
563
564 content = replaceIsNull(content);
565
566 _sqlPool.put(id, content);
567 }
568 }
569 }
570
571 protected String transform(String sql) {
572 sql = PortalUtil.transformCustomSQL(sql);
573
574 StringBundler sb = new StringBundler();
575
576 try {
577 UnsyncBufferedReader unsyncBufferedReader =
578 new UnsyncBufferedReader(new UnsyncStringReader(sql));
579
580 String line = null;
581
582 while ((line = unsyncBufferedReader.readLine()) != null) {
583 sb.append(line.trim());
584 sb.append(StringPool.SPACE);
585 }
586
587 unsyncBufferedReader.close();
588 }
589 catch (IOException ioe) {
590 return sql;
591 }
592
593 return sb.toString();
594 }
595
596 private static final String _GROUP_BY_CLAUSE = " GROUP BY ";
597
598 private static final String _ORDER_BY_CLAUSE = " ORDER BY ";
599
600 private static Log _log = LogFactoryUtil.getLog(CustomSQL.class);
601
602 private String _functionIsNotNull;
603 private String _functionIsNull;
604 private Map<String, String> _sqlPool;
605 private boolean _vendorDB2;
606 private boolean _vendorInformix;
607 private boolean _vendorMySQL;
608 private boolean _vendorOracle;
609 private boolean _vendorPostgreSQL;
610 private boolean _vendorSybase;
611
612 }