1
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
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 }