1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
package com.ibiz.util.helper;
import com.ibiz.util.SearchContext;
import com.ibiz.util.SearchFieldFilter;
import com.ibiz.util.SearchFilter;
import com.ibiz.util.SearchGroupFilter;
import com.ibiz.util.enums.SearchFieldType;
import com.ibiz.util.enums.SearchGroupType;
import org.apache.commons.lang3.EnumUtils;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import java.util.List;
/**
* 关系型数据库SQL转换工具类
*/
@Component
public class QueryBuilderHelper {
/**
* 解析SearchContext中的field与group，返回sql
* @param context
* @return
*/
public String buildSQL(SearchContext context){
String resultCond="";
StringBuffer sbfSQL=new StringBuffer();
List<SearchFilter> conditions=context.getCondition();
String customCond=context.getCustomCond();//自定义条件查询
String defaultGroupType="AND";////组内条件的组合关系(AND/OR)
for(SearchFilter cond: conditions){//条件查询
String tempCond = "";
if(cond instanceof SearchFieldFilter){ //单条件查询
tempCond=parseFieldCond((SearchFieldFilter) cond);
}
else if (cond instanceof SearchGroupFilter){//组条件查询
tempCond=parseGroupCond((SearchGroupFilter) cond);
}
if(!StringUtils.isEmpty(tempCond))
sbfSQL.append(String.format("%s %s ",defaultGroupType,tempCond));
}
for (String key : context.getParams().keySet()) {
String[] keys = key.split("_") ;
if(keys.length ==3 && keys[0].equals("n") && EnumUtils.isValidEnumIgnoreCase(SearchFieldType.class, keys[2]) ){
SearchFieldFilter field = new SearchFieldFilter() ;
field.setCondition(EnumUtils.getEnumIgnoreCase(SearchFieldType.class, keys[2]));
field.setParam(keys[1]);
field.setValue(context.getParams().get(key));
sbfSQL.append(String.format("%s %s ",StringUtils.isEmpty(sbfSQL.toString())?"":defaultGroupType,parseFieldCond(field)));
}
}
if(!StringUtils.isEmpty(customCond))//自定义条件查询
sbfSQL.append(String.format("%s %s ",defaultGroupType,customCond));
resultCond=parseResult(sbfSQL,defaultGroupType);
return resultCond;
}
/**
* 字段条件解析
* @param field
* @return
*/
private String parseFieldCond(SearchFieldFilter field){
String strSQL="";
String param=field.getParam();
SearchFieldType cond=field.getCondition();
Object value=field.getValue();
if(StringUtils.isEmpty(param)|| StringUtils.isEmpty(cond))
return strSQL;
switch (cond){
case GT:
strSQL=String.format(" %s > '%s'",param ,value); break;
case GTANDEQ:
strSQL=String.format(" %s >= '%s'",param ,value); break;
case EQ:
strSQL=String.format(" %s = '%s'",param ,value); break;
case NOTEQ:
strSQL=String.format(" %s <> '%s'",param ,value); break;
case LT:
strSQL=String.format(" %s < '%s'",param ,value); break;
case LTANDEQ:
strSQL=String.format(" %s <= '%s'",param ,value); break;
case LIKE:
strSQL=String.format(" %s like '%%%s%%'",param ,value); break;
case LEFTLIKE:
strSQL=String.format(" %s like '%s%%'",param ,value); break;
case RIGHTLIKE:
strSQL=String.format(" %s like '%%%s'",param ,value); break;
case ISNULL:
strSQL=String.format(" %s is null",param); break;
case ISNOTNULL:
strSQL=String.format(" %s is not null ",param); break;
case IN:
if(value instanceof List){
String tempValue=formatStringArr((List<String>) value);
if(!StringUtils.isEmpty(tempValue))
strSQL=String.format(" %s in (%s)",param,tempValue); break;
}
break;
case NOTIN:
if(value instanceof List){
String tempValue=formatStringArr((List<String>) value);
if(!StringUtils.isEmpty(tempValue))
strSQL=String.format(" %s not in (%s)",param,tempValue); break;
}
break;
}
return strSQL;
}
/**
* 组条件解析
* @param group
* @return
*/
private String parseGroupCond(SearchGroupFilter group){
String sql="";
List<SearchFilter> groupCond=group.getCondition();//组内条件
SearchGroupType groupType=group.getSearchGroupType();//组内条件的组合关系(AND/OR)
if(groupCond.size()==0 || StringUtils.isEmpty(groupType))
return sql;
String resultCond="";
String strGroupType=getGroupType(groupType);
StringBuffer sbfSQL=new StringBuffer();
for(SearchFilter cond : groupCond){
String tempCond = "";
if(cond instanceof SearchFieldFilter){ //单条件查询
tempCond=parseFieldCond((SearchFieldFilter) cond);
}
else if (cond instanceof SearchGroupFilter){//组条件查询
tempCond=parseGroupCond((SearchGroupFilter) cond);
}
if(!StringUtils.isEmpty(tempCond))
sbfSQL.append(String.format("%s %s ",strGroupType,tempCond));
}
resultCond=parseResult(sbfSQL,strGroupType);
resultCond=String.format("(%s)",resultCond);
return resultCond;
}
/**
* 组内条件的组合关系(AND/OR)
* @param groupType
* @return
*/
private String getGroupType(SearchGroupType groupType){
String strGroupType="";
switch(groupType){
case AND: strGroupType="AND";break;
case OR: strGroupType="OR"; break;
}
return strGroupType;
}
/**
* 格式转换
* @param cond
* @param operator
* @return
*/
private String parseResult(StringBuffer cond,String operator){
String resultCond = cond.toString();
if (resultCond.startsWith(operator))
resultCond = resultCond.replaceFirst(operator, "");
if (resultCond.endsWith(operator))
resultCond = resultCond.substring(0, resultCond.lastIndexOf(operator));
return resultCond;
}
/**
* 转换[a,b]格式字符串到 'a','b'格式
*
* @return
*/
private String formatStringArr(List<String> array) {
String[] arr = array.toArray(new String[array.size()]);
return "'" + String.join("','",arr) + "'";
}
}