sql的动态条件拼装

1. import java.util.ArrayList; 
2. import java.util.Collection; 
3. import java.util.HashMap; 
4. import java.util.LinkedList; 
5. import java.util.List; 
6. import java.util.Map; 
7. /**
8. * SQL动态封装工具
9. *
10. * simple：：
11. *
12. *           BaseSelector select = new BaseSelector();
13. *           select.setCommand("select * from  stat_user ");
14. *           select.createCriteria().andEqualTo("org_code", "YZ1201")
15. *           .andGreaterThanOrEqualTo("day_code", "2009-01-01")
16. *           .andLessThanOrEqualTo("day_code", "2009-03-31");
17. *           List
18. *
19. *           output:select * from  stat_user where org_code = 'YZ1201' and day_code >= '2009-01-01' and day_code <= '2009-03-31'
20. *
21. */
22. public class BaseSelector { 
25. /**
26.      * sql语句
27.      */
28. private String command; 
29. /**
30.      * 排序字段
31.      */
32. protected String orderByClause; 
35. /**
36.      * sql条件
37.      */
38. protected List oredCriteria; 
42. public String getCommand() 
43.     { 
44. return command; 
45.     } 
48. public void setCommand(String command) 
49.     { 
50. this.command = command; 
51.     } 
54. public BaseSelector() { 
55.         oredCriteria = new ArrayList (); 
56.     } 
59. protected BaseSelector(BaseSelector example) { 
60. this.orderByClause = example.orderByClause; 
61. this.oredCriteria = example.oredCriteria; 
62.     } 
64. /**
65.      * 生成最终sql语句
66.      */
67. public String toSql() 
68.     { 
69. if(oredCriteria==null||oredCriteria.size()<=0) 
70. return command; 
72.         StringBuffer sqlAll = new StringBuffer(); 
73.         sqlAll.append(command); 
74. if (command != null && command.toUpperCase().indexOf(" WHERE ") == -1) 
75.             sqlAll.append(" WHERE "); 
76. else
77.             sqlAll.append(" AND "); 
78. for (Criteria cri : oredCriteria) 
79.         { 
80. if(!cri.isValid()) 
81. continue; 
82.             sqlAll.append("("); 
83.             StringBuffer sql = new StringBuffer(); 
84.             criteriaWithoutValueSql(sql,cri.criteriaWithoutValue); 
85.             criteriaWithSingleValueSql(sql,cri.criteriaWithSingleValue); 
86.             criteriaWithListValueSql(sql,cri.criteriaWithListValue); 
87.             criteriaWithBetweenValueSql(sql,cri.criteriaWithBetweenValue); 
88.             sqlAll.append(sql.toString()); 
89.             sqlAll.append(")"); 
90.             sqlAll.append(" or "); 
91.         } 
92. return sqlAll.substring(0, sqlAll.length()-4); 
94.     } 
96. @SuppressWarnings("unchecked") 
97. private String criteriaWithoutValueSql(StringBuffer sql,List list) 
98.     { 
99. if(list==null) 
100. return ""; 
102. int n = list.size(); 
103. for (int i = 0;i
104.         { 
105.             sql.append(list.get(i)); 
106. if(i
107.                 sql.append(" and "); 
108.         } 
110. return sql.toString(); 
111.     } 
113. @SuppressWarnings("unchecked") 
114. private String criteriaWithSingleValueSql(StringBuffer sql,List list) 
115.     { 
116. if(list==null) 
117. return ""; 
118. if (sql.length() > 0&&list.size()>0) 
119.             sql.append(" and "); 
120. int n = list.size(); 
121. for (int i = 0;i
122.         { 
123.             Map map = (Map) list.get(i); 
124.             sql.append(map.get("condition")).append(map.get("value")); 
125. if(i
126.                 sql.append(" and "); 
127.         } 
128. return sql.toString(); 
129.     } 
131. @SuppressWarnings("unchecked") 
132. private String criteriaWithListValueSql(StringBuffer sql,List list) 
133.     { 
134. if(list==null) 
135. return ""; 
136. if (sql.length() > 0&&list.size()>0) 
137.             sql.append(" and "); 
138. int n = list.size(); 
139. for (int i = 0;i
140.         { 
141.             Map map = (Map) list.get(i); 
142.             sql.append(map.get("condition")).append("("+join((Collection) map.get("values"),",")+")"); 
143. if(i
144.                 sql.append(" and "); 
145.         } 
146. return sql.toString(); 
147.     } 
149. @SuppressWarnings("unchecked") 
150. private String criteriaWithBetweenValueSql(StringBuffer sql,List list) 
151.     { 
152. if(list==null) 
153. return ""; 
154. if (sql.length() > 0&&list.size()>0) 
155.             sql.append(" and "); 
156. int n = list.size(); 
157. for (int i = 0;i
158.         { 
159.             Map map = (Map) list.get(i); 
160.             sql.append(map.get("condition")).append(join((Collection) map.get("values")," and ")); 
161. if(i
162.                 sql.append(" and "); 
163.         } 
164. return sql.toString(); 
165.     } 
167. @SuppressWarnings("unchecked") 
168. private String  join(Collection list,String spe) 
169.     { 
170. if(list==null) 
171. return ""; 
172.         Object array[] = list.toArray(); 
173.         StringBuffer buff = new StringBuffer(); 
174. for (int i = 0; i < array.length; i++) 
175.         { 
176.             buff.append(array[i]); 
177. if(i
178.                 buff.append(spe); 
179.         } 
180. return buff.toString(); 
181.     } 
183. /**
184.      * 顺序排序
185.      * @param field
186.      */
187. public void setOrderByClauseAsc(String field) { 
188. this.orderByClause = getFieldName(field) + " ASC"; 
189.     } 
191. /**
192.      * 倒序排序
193.      * @param field
194.      */
195. public void setOrderByClauseDesc(String field) { 
196. this.orderByClause = getFieldName(field) + " DESC"; 
197.     } 
201. public String getOrderByClause() { 
202. return orderByClause; 
203.     } 
206. public List getOredCriteria() { 
207. return oredCriteria; 
208.     } 
210. /**
211.      * or 条件
212.      */
213. public void or(Criteria criteria) { 
214.         oredCriteria.add(criteria); 
215.     } 
217. /**
218.      * 创建条件对象
219.      */
220. public Criteria createCriteria() { 
221.         Criteria criteria = createCriteriaInternal(); 
222. if (oredCriteria.size() == 0) { 
223.             oredCriteria.add(criteria); 
224.         } 
225. return criteria; 
226.     } 
228. protected Criteria createCriteriaInternal() { 
229.         Criteria criteria = new Criteria(); 
230. return criteria; 
231.     } 
233. /**
234.      * 清除条件
235.      */
236. public void clear() { 
237.         oredCriteria.clear(); 
238.     } 
240. static String getFieldName(String field) 
241.     { 
243. if (field == null ) { 
244. throw new RuntimeException( field + " cannot be null"); 
245.         } 
246. return field.toUpperCase(); 
247. //
248. //
249. //        Pattern pattern = Pattern.compile("[A-Z]{1}");
250. //        Matcher m =  pattern.matcher(field);
251. //        StringBuffer sbr = new StringBuffer();
252. //        while(m.find())
253. //            m.appendReplacement(sbr, "_"+m.group());
254. //        m.appendTail(sbr);
255. //        return sbr.toString().toUpperCase();
256.     } 
258. /** 
259.      * 查询条件 
260.      */
261. public static class Criteria { 
262. protected List criteriaWithoutValue; 
264. protected List
266. protected List
268. protected List
270. protected Criteria() { 
271. super(); 
272.             criteriaWithoutValue = new ArrayList (); 
273.             criteriaWithSingleValue = new ArrayList
274.             criteriaWithListValue = new ArrayList
275.             criteriaWithBetweenValue = new ArrayList
276.         } 
278. public boolean isValid() { 
279. return criteriaWithoutValue.size() > 0
280.                 || criteriaWithSingleValue.size() > 0
281.                 || criteriaWithListValue.size() > 0
282.                 || criteriaWithBetweenValue.size() > 0; 
283.         } 
285. public List getCriteriaWithoutValue() { 
286. return criteriaWithoutValue; 
287.         } 
289. public List
290. return criteriaWithSingleValue; 
291.         } 
293. public List
294. return criteriaWithListValue; 
295.         } 
297. public List
298. return criteriaWithBetweenValue; 
299.         } 
301. protected void addCriterion(String condition) { 
302. if (condition == null||"".equals(condition)) { 
303. return; 
304.             } 
305.             criteriaWithoutValue.add(condition); 
306.         } 
308. protected void addCriterion(String condition, Object value, String property) { 
309. if (value == null||"".equals(value)) { 
310. return; 
311.             } 
312.             Map map = new HashMap (); 
313.             map.put("condition", condition); 
314.             map.put("value", value); 
315.             criteriaWithSingleValue.add(map); 
316.         } 
318. protected void addCriterion(String condition, List values, String property) { 
319. if (values == null || values.size() == 0) { 
320. return; 
321.             } 
322.             Map map = new HashMap (); 
323.             map.put("condition", condition); 
324.             map.put("values", values); 
325.             criteriaWithListValue.add(map); 
326.         } 
328. protected void addCriterion(String condition, Object value1, Object value2, String property) { 
329. if (value1 == null || value2 == null) { 
330. return; 
331.             } 
332.             List