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