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