1   /**
2    * Copyright (c) 2000-2006 Liferay, LLC. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
22  
23  package com.liferay.portal.spring.hibernate;
24  
25  import com.liferay.portal.kernel.util.StackTraceUtil;
26  import com.liferay.portal.util.PropsUtil;
27  import com.liferay.util.CollectionFactory;
28  import com.liferay.util.StringPool;
29  import com.liferay.util.StringUtil;
30  import com.liferay.util.Validator;
31  import com.liferay.util.dao.hibernate.OrderByComparator;
32  
33  import java.io.BufferedReader;
34  import java.io.IOException;
35  import java.io.StringReader;
36  
37  import java.util.Iterator;
38  import java.util.Map;
39  
40  import org.apache.commons.logging.Log;
41  import org.apache.commons.logging.LogFactory;
42  
43  import org.dom4j.Document;
44  import org.dom4j.Element;
45  import org.dom4j.io.SAXReader;
46  
47  /**
48   * <a href="CustomSQLUtil.java.html"><b><i>View Source</i></b></a>
49   *
50   * @author  Brian Wing Shun Chan
51   *
52   */
53  public class CustomSQLUtil {
54  
55      public static final String CUSTOM_SQL_FUNCTION_ISNULL =
56          PropsUtil.get(PropsUtil.CUSTOM_SQL_FUNCTION_ISNULL);
57  
58      public static String replaceAndOperator(String sql, boolean andOperator) {
59          String andOrConnector = "OR";
60          String andOrNullCheck = "AND ? IS NOT NULL";
61  
62          if (andOperator) {
63              andOrConnector = "AND";
64              andOrNullCheck = "OR ? IS NULL";
65          }
66  
67          sql = StringUtil.replace(
68              sql,
69              new String[] {
70                  "[$AND_OR_CONNECTOR$]", "[$AND_OR_NULL_CHECK$]"
71              },
72              new String[] {
73                  andOrConnector, andOrNullCheck
74              });
75  
76          sql = replaceIsNull(sql);
77  
78          return sql;
79      }
80  
81      public static String replaceIsNull(String sql) {
82          if (Validator.isNotNull(CUSTOM_SQL_FUNCTION_ISNULL)) {
83              sql = StringUtil.replace(
84                  sql,
85                  new String[] {
86                      "? IS NULL", "? IS NOT NULL"
87                  },
88                  new String[] {
89                      CUSTOM_SQL_FUNCTION_ISNULL + "(?, '1') = '1'",
90                      CUSTOM_SQL_FUNCTION_ISNULL + "(?, '1') = '0'"
91                  });
92          }
93  
94          return sql;
95      }
96  
97      public static String removeOrderBy(String sql) {
98          int pos = sql.indexOf(" ORDER BY ");
99  
100         if (pos != -1) {
101             sql = sql.substring(0, pos);
102         }
103 
104         return sql;
105     }
106 
107     public static String replaceOrderBy(String sql, OrderByComparator obc) {
108         if (obc == null) {
109             return sql;
110         }
111 
112         sql = removeOrderBy(sql);
113         sql += " ORDER BY " + obc.getOrderBy();
114 
115         return sql;
116     }
117 
118     public static String get(String id) {
119         return _instance._get(id);
120     }
121 
122     private CustomSQLUtil() {
123         _sqlPool = CollectionFactory.getHashMap();
124 
125         try {
126             ClassLoader classLoader = getClass().getClassLoader();
127 
128             String[] configs = StringUtil.split(
129                 PropsUtil.get(PropsUtil.CUSTOM_SQL_CONFIGS));
130 
131             for (int i = 0; i < configs.length; i++) {
132                 _read(classLoader, configs[i]);
133             }
134         }
135         catch (Exception e) {
136             _log.error(StackTraceUtil.getStackTrace(e));
137         }
138     }
139 
140     private String _get(String id) {
141         return (String)_sqlPool.get(id);
142     }
143 
144     private void _read(ClassLoader classLoader, String source)
145         throws Exception {
146 
147         String xml = null;
148 
149         try {
150             xml = StringUtil.read(classLoader, source);
151         }
152         catch (Exception e) {
153             _log.warn("Cannot load " + source);
154         }
155 
156         if (xml == null) {
157             return;
158         }
159 
160         if (_log.isDebugEnabled()) {
161             _log.debug("Loading " + source);
162         }
163 
164         SAXReader reader = new SAXReader();
165 
166         Document doc = reader.read(new StringReader(xml));
167 
168         Element root = doc.getRootElement();
169 
170         Iterator itr = root.elements("sql").iterator();
171 
172         while (itr.hasNext()) {
173             Element sql = (Element)itr.next();
174 
175             String file = sql.attributeValue("file");
176 
177             if (Validator.isNotNull(file)) {
178                 _read(classLoader, file);
179             }
180             else {
181                 String id = sql.attributeValue("id");
182                 String content = _trim(sql.getText());
183 
184                 content = replaceIsNull(content);
185 
186                 _sqlPool.put(id, content);
187             }
188         }
189     }
190 
191     private String _trim(String sql) {
192         StringBuffer sb = new StringBuffer();
193 
194         try {
195             BufferedReader br = new BufferedReader(new StringReader(sql));
196 
197             String line = null;
198 
199             while ((line = br.readLine()) != null) {
200                 sb.append(line.trim());
201                 sb.append(StringPool.SPACE);
202             }
203 
204             br.close();
205         }
206         catch (IOException ioe) {
207             return sql;
208         }
209 
210         return sb.toString();
211     }
212 
213     private static Log _log = LogFactory.getLog(CustomSQLUtil.class);
214 
215     private static CustomSQLUtil _instance = new CustomSQLUtil();
216 
217     private Map _sqlPool;
218 
219 }