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