001: /**
002: * Copyright (c) 2000-2008 Liferay, Inc. All rights reserved.
003: *
004: * Permission is hereby granted, free of charge, to any person obtaining a copy
005: * of this software and associated documentation files (the "Software"), to deal
006: * in the Software without restriction, including without limitation the rights
007: * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
008: * copies of the Software, and to permit persons to whom the Software is
009: * furnished to do so, subject to the following conditions:
010: *
011: * The above copyright notice and this permission notice shall be included in
012: * all copies or substantial portions of the Software.
013: *
014: * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
015: * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
016: * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
017: * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
018: * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
019: * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
020: * SOFTWARE.
021: */package com.liferay.util.dao.hibernate;
022:
023: import com.liferay.portal.kernel.util.GetterUtil;
024: import com.liferay.portal.kernel.util.OrderByComparator;
025: import com.liferay.portal.kernel.util.StringMaker;
026: import com.liferay.portal.kernel.util.StringPool;
027: import com.liferay.portal.kernel.util.StringUtil;
028: import com.liferay.portal.kernel.util.Validator;
029: import com.liferay.util.CollectionFactory;
030: import com.liferay.util.dao.DataAccess;
031:
032: import java.io.BufferedReader;
033: import java.io.IOException;
034: import java.io.StringReader;
035:
036: import java.sql.Connection;
037: import java.sql.DatabaseMetaData;
038:
039: import java.util.Iterator;
040: import java.util.Map;
041:
042: import org.apache.commons.logging.Log;
043: import org.apache.commons.logging.LogFactory;
044:
045: import org.dom4j.Document;
046: import org.dom4j.Element;
047: import org.dom4j.io.SAXReader;
048:
049: /**
050: * <a href="CustomSQLUtil.java.html"><b><i>View Source</i></b></a>
051: *
052: * @author Brian Wing Shun Chan
053: * @author Bruno Farache
054: *
055: */
056: public abstract class CustomSQLUtil {
057:
058: public static final String DB2_FUNCTION_IS_NULL = "CAST(? AS VARCHAR(32672)) IS NULL";
059:
060: public static final String DB2_FUNCTION_IS_NOT_NULL = "CAST(? AS VARCHAR(32672)) IS NOT NULL";
061:
062: public static final String INFORMIX_FUNCTION_IS_NULL = "lportal.isnull(?)";
063:
064: public static final String INFORMIX_FUNCTION_IS_NOT_NULL = "NOT "
065: + INFORMIX_FUNCTION_IS_NULL;
066:
067: public static final String MYSQL_FUNCTION_IS_NULL = "IFNULL(?, '1') = '1'";
068:
069: public static final String MYSQL_FUNCTION_IS_NOT_NULL = "IFNULL(?, '1') = '0'";
070:
071: public static final String SYBASE_FUNCTION_IS_NULL = "ISNULL(?, '1') = '1'";
072:
073: public static final String SYBASE_FUNCTION_IS_NOT_NULL = "ISNULL(?, '1') = '0'";
074:
075: public CustomSQLUtil(String functionIsNull, String functionIsNotNull) {
076: this (null, functionIsNull, functionIsNotNull);
077: }
078:
079: public CustomSQLUtil(Connection con, String functionIsNull,
080: String functionIsNotNull) {
081:
082: try {
083: if (Validator.isNotNull(functionIsNull)
084: && Validator.isNotNull(functionIsNotNull)) {
085:
086: _functionIsNull = functionIsNull;
087: _functionIsNotNull = functionIsNotNull;
088:
089: if (_log.isDebugEnabled()) {
090: _log.info("functionIsNull is manually set to "
091: + functionIsNull);
092: _log.info("functionIsNotNull is manually set to "
093: + functionIsNotNull);
094: }
095: } else if (con != null) {
096: DatabaseMetaData metaData = con.getMetaData();
097:
098: String dbName = GetterUtil.getString(metaData
099: .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 "
112: + dbName);
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
121: .info("Detected Informix with database name "
122: + dbName);
123: }
124: } else if (dbName.startsWith("MySQL")) {
125: _vendorMySQL = true;
126: //_functionIsNull = MYSQL_FUNCTION_IS_NULL;
127: //_functionIsNotNull = MYSQL_FUNCTION_IS_NOT_NULL;
128:
129: if (_log.isInfoEnabled()) {
130: _log.info("Detected MySQL with database name "
131: + dbName);
132: }
133: } else if (dbName.startsWith("Sybase")
134: || 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("Detected Sybase with database name "
141: + dbName);
142: }
143: } else if (dbName.startsWith("Oracle")) {
144: _vendorOracle = true;
145:
146: if (_log.isInfoEnabled()) {
147: _log.info("Detected Oracle with database name "
148: + dbName);
149: }
150: } else if (dbName.startsWith("PostgreSQL")) {
151: _vendorPostgreSQL = true;
152:
153: if (_log.isInfoEnabled()) {
154: _log
155: .info("Detected PostgreSQL with database name "
156: + dbName);
157: }
158: } else {
159: if (_log.isDebugEnabled()) {
160: _log
161: .debug("Unable to detect database with name "
162: + dbName);
163: }
164: }
165: }
166: } catch (Exception e) {
167: _log.error(e, e);
168: } finally {
169: DataAccess.cleanUp(con);
170: }
171:
172: _sqlPool = CollectionFactory.getHashMap();
173:
174: try {
175: ClassLoader classLoader = getClass().getClassLoader();
176:
177: String[] configs = getConfigs();
178:
179: for (int i = 0; i < configs.length; i++) {
180: read(classLoader, configs[i]);
181: }
182: } catch (Exception e) {
183: _log.error(e, e);
184: }
185: }
186:
187: public String get(String id) {
188: return (String) _sqlPool.get(id);
189: }
190:
191: /**
192: * Returns true if Hibernate is connecting to a DB2 database.
193: *
194: * @return true if Hibernate is connecting to a DB2 database
195: */
196: public boolean isVendorDB2() {
197: return _vendorDB2;
198: }
199:
200: /**
201: * Returns true if Hibernate is connecting to an Informix database.
202: *
203: * @return true if Hibernate is connecting to an Informix database
204: */
205: public boolean isVendorInformix() {
206: return _vendorInformix;
207: }
208:
209: /**
210: * Returns true if Hibernate is connecting to a MySQL database.
211: *
212: * @return true if Hibernate is connecting to a MySQL database
213: */
214: public boolean isVendorMySQL() {
215: return _vendorMySQL;
216: }
217:
218: /**
219: * Returns true if Hibernate is connecting to an Oracle database.
220: *
221: * Oracle has a nasty bug where it treats '' as a NULL value. See
222: * http://thedailywtf.com/forums/thread/26879.aspx for more information
223: * on this nasty bug.
224: *
225: * @return true if Hibernate is connecting to an Oracle database
226: */
227: public boolean isVendorOracle() {
228: return _vendorOracle;
229: }
230:
231: /**
232: * Returns true if Hibernate is connecting to a PostgreSQL database.
233: *
234: * @return true if Hibernate is connecting to a PostgreSQL database
235: */
236: public boolean isVendorPostgreSQL() {
237: return _vendorPostgreSQL;
238: }
239:
240: /**
241: * Returns true if Hibernate is connecting to a Sybase database.
242: *
243: * @return true if Hibernate is connecting to a Sybase database
244: */
245: public boolean isVendorSybase() {
246: return _vendorSybase;
247: }
248:
249: public String[] keywords(String keywords) {
250: return keywords(keywords, true);
251: }
252:
253: public String[] keywords(String keywords, boolean lowerCase) {
254: if (lowerCase) {
255: keywords = keywords.toLowerCase();
256: }
257:
258: keywords = keywords.trim();
259:
260: String[] keywordsArray = StringUtil.split(keywords,
261: StringPool.SPACE);
262:
263: for (int i = 0; i < keywordsArray.length; i++) {
264: String keyword = keywordsArray[i];
265:
266: keywordsArray[i] = StringPool.PERCENT + keyword
267: + StringPool.PERCENT;
268: }
269:
270: return keywordsArray;
271: }
272:
273: public String[] keywords(String[] keywordsArray) {
274: return keywords(keywordsArray, true);
275: }
276:
277: public String[] keywords(String[] keywordsArray, boolean lowerCase) {
278: if ((keywordsArray == null) || (keywordsArray.length == 0)) {
279: keywordsArray = new String[] { null };
280: }
281:
282: if (lowerCase) {
283: for (int i = 0; i < keywordsArray.length; i++) {
284: keywordsArray[i] = StringUtil
285: .lowerCase(keywordsArray[i]);
286: }
287: }
288:
289: return keywordsArray;
290: }
291:
292: public String replaceAndOperator(String sql, boolean andOperator) {
293: String andOrConnector = "OR";
294: String andOrNullCheck = "AND ? IS NOT NULL";
295:
296: if (andOperator) {
297: andOrConnector = "AND";
298: andOrNullCheck = "OR ? IS NULL";
299: }
300:
301: sql = StringUtil.replace(sql, new String[] {
302: "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]" },
303: new String[] { andOrConnector, andOrNullCheck });
304:
305: if (_vendorPostgreSQL) {
306: sql = StringUtil
307: .replace(
308: sql,
309: new String[] {
310: "Date >= ? AND ? IS NOT NULL",
311: "Date <= ? AND ? IS NOT NULL",
312: "Date >= ? OR ? IS NULL",
313: "Date <= ? OR ? IS NULL" },
314: new String[] {
315: "Date >= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
316: "Date <= ? AND CAST(? AS TIMESTAMP) IS NOT NULL",
317: "Date >= ? OR CAST(? AS TIMESTAMP) IS NULL",
318: "Date <= ? OR CAST(? AS TIMESTAMP) IS NULL" });
319: }
320:
321: sql = replaceIsNull(sql);
322:
323: return sql;
324: }
325:
326: public String replaceIsNull(String sql) {
327: if (Validator.isNotNull(_functionIsNull)) {
328: sql = StringUtil.replace(sql, new String[] { "? IS NULL",
329: "? IS NOT NULL" }, new String[] { _functionIsNull,
330: _functionIsNotNull });
331: }
332:
333: return sql;
334: }
335:
336: public String replaceKeywords(String sql, String field,
337: String operator, boolean last, String[] values) {
338:
339: if (values.length == 0) {
340: return sql;
341: }
342:
343: StringMaker oldSql = new StringMaker();
344:
345: oldSql.append("(");
346: oldSql.append(field);
347: oldSql.append(" ");
348: oldSql.append(operator);
349: oldSql.append(" ? [$AND_OR_NULL_CHECK$])");
350:
351: if (!last) {
352: oldSql.append(" [$AND_OR_CONNECTOR$]");
353: }
354:
355: StringMaker newSql = new StringMaker();
356:
357: newSql.append("(");
358:
359: for (int i = 0; i < values.length; i++) {
360: if (i > 0) {
361: newSql.append(" OR ");
362: }
363:
364: newSql.append("(");
365: newSql.append(field);
366: newSql.append(" ");
367: newSql.append(operator);
368: newSql.append(" ? [$AND_OR_NULL_CHECK$])");
369: }
370:
371: newSql.append(")");
372:
373: if (!last) {
374: newSql.append(" [$AND_OR_CONNECTOR$]");
375: }
376:
377: return StringUtil.replace(sql, oldSql.toString(), newSql
378: .toString());
379: }
380:
381: public String removeOrderBy(String sql) {
382: int pos = sql.indexOf(" ORDER BY ");
383:
384: if (pos != -1) {
385: sql = sql.substring(0, pos);
386: }
387:
388: return sql;
389: }
390:
391: public String replaceOrderBy(String sql, OrderByComparator obc) {
392: if (obc == null) {
393: return sql;
394: }
395:
396: StringMaker sm = new StringMaker();
397:
398: sm.append(removeOrderBy(sql));
399: sm.append(" ORDER BY ");
400: sm.append(obc.getOrderBy());
401:
402: return sm.toString();
403: }
404:
405: protected abstract String[] getConfigs();
406:
407: protected void read(ClassLoader classLoader, String source)
408: throws Exception {
409:
410: String xml = null;
411:
412: try {
413: xml = StringUtil.read(classLoader, source);
414: } catch (Exception e) {
415: _log.warn("Cannot load " + source);
416: }
417:
418: if (xml == null) {
419: return;
420: }
421:
422: if (_log.isDebugEnabled()) {
423: _log.debug("Loading " + source);
424: }
425:
426: SAXReader reader = new SAXReader();
427:
428: Document doc = reader.read(new StringReader(xml));
429:
430: Element root = doc.getRootElement();
431:
432: Iterator itr = root.elements("sql").iterator();
433:
434: while (itr.hasNext()) {
435: Element sql = (Element) itr.next();
436:
437: String file = sql.attributeValue("file");
438:
439: if (Validator.isNotNull(file)) {
440: read(classLoader, file);
441: } else {
442: String id = sql.attributeValue("id");
443: String content = transform(sql.getText());
444:
445: content = replaceIsNull(content);
446:
447: _sqlPool.put(id, content);
448: }
449: }
450: }
451:
452: protected String transform(String sql) {
453: StringMaker sm = new StringMaker();
454:
455: try {
456: BufferedReader br = new BufferedReader(
457: new StringReader(sql));
458:
459: String line = null;
460:
461: while ((line = br.readLine()) != null) {
462: sm.append(line.trim());
463: sm.append(StringPool.SPACE);
464: }
465:
466: br.close();
467: } catch (IOException ioe) {
468: return sql;
469: }
470:
471: return sm.toString();
472: }
473:
474: private static Log _log = LogFactory.getLog(CustomSQLUtil.class);
475:
476: private boolean _vendorDB2;
477: private boolean _vendorInformix;
478: private boolean _vendorMySQL;
479: private boolean _vendorOracle;
480: private boolean _vendorPostgreSQL;
481: private boolean _vendorSybase;
482: private String _functionIsNull;
483: private String _functionIsNotNull;
484: private Map _sqlPool;
485:
486: }
|