实现目标
由于在注解版mybatis中手动循环拼接动态sql容易出错
请看mybatis注解动态sql注入map和list（防sql注入攻击），所以封装了这个类似于foreach标签的工具方法。
由于mybatis(3.5.6、3.5.7 or earlier)的bug（mybatis can not parse #{list[0][0]} correctly），不支持数组/List直接嵌套数组/List，但是可以与Map嵌套。不过 mybatis can parse ${list[0][0]} correctly
工具类
复制代码
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
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389package com.xxx.common.util; import java.util.Arrays; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.ibatis.binding.BindingException; import org.apache.ibatis.ognl.Ognl; import org.apache.ibatis.ognl.OgnlException; import java.util.Set; public class ForeachMybatisUtils { private ForeachMybatisUtils() {} public static <C> ParamObject<C> createParamObject(C obj) { ParamObject<C> paramObject = new ParamObject<C>(); paramObject.setObj(obj); return paramObject; } public static <C> StringBuilder foreach(ParamObject<C> paramObject) { return foreach(paramObject, null); } @SuppressWarnings("rawtypes") public static <C> StringBuilder foreach(ParamObject<C> paramObject, Interceptor interceptor) { return foreach(paramObject.getObj(), paramObject.getCollection(), paramObject.getItem(), paramObject.getIndex(), paramObject.getItemFormatter(), paramObject.getSeparator(), paramObject.getOpen(), paramObject.getClose(), interceptor); } /** * itemFormatter部分用法：#{item,jdbcType=VARCHAR},#{item.3345,jdbcType=VARCHAR}其中3345为map的key, ${item}, ${item['3345']}其中3345为map的key * @param <C> List.class、Map.class、Array * @param obj list、map、数组对象 * @param collection 对应xml foreach标签的collection属性 * @param item 对应xml foreach标签的item属性 * @param index 对应xml foreach标签的index属性，但是在itemFormatter中只匹配 ${ } 格式 * @param itemFormatter 对应xml foreach标签内 #{item} * @param separator 对应xml foreach标签的separator属性 * @param open 对应xml foreach标签的open属性 * @param close 对应xml foreach标签的close属性 * @return 拼接后的动态sql */ public static <C> StringBuilder foreach(C obj, String collection, String item, String itemFormatter, String separator, String open, String close) { return foreach(obj, collection, item, null, itemFormatter, separator, open, close, null); } public static <C> StringBuilder foreach(C obj, String collection, String item, String index, String itemFormatter, String separator, String open, String close) { return foreach(obj, collection, item, index, itemFormatter, separator, open, close, null); } @SuppressWarnings({ "rawtypes", "unchecked" }) public static <C> StringBuilder foreach(C obj, String collection, String item, String index, String itemFormatter, String separator, String open, String close, ForeachMybatisUtils.Interceptor interceptor) { if (obj == null) { throw new NullPointerException("object cannot be null"); } if (collection == null || collection.trim().equals("")) { throw new NullPointerException("collection cannot be blank"); } if (item == null || item.trim().isEmpty()) { throw new NullPointerException("item cannot be blank"); } if (itemFormatter == null) { throw new NullPointerException("itemFormatter cannot be null, and you can fill #{item},please"); } collection = collection.trim(); item = item.trim(); if (index != null && item.equals(index.trim())) { throw new IllegalArgumentException("index cannot be equal to item"); } Pattern itemDynamicPattern = Pattern.compile("(?<=#\{)" + item + "(?=[\s\S]*\})"); Pattern itemBindingPattern = Pattern.compile("\$\{" + item + "(?:(?:\.|\[)\S+)?\s*\}"); Pattern indexStaticPattern = null; if (index != null && !index.trim().isEmpty() && itemFormatter.contains("${")) { indexStaticPattern = Pattern.compile("\$\{" + index.trim() + "\s*\}"); } if (separator == null) { separator = ""; } StringBuilder sqlBuilder = new StringBuilder(); if (open != null) { sqlBuilder.append(open); } String prod = ""; int n = 0; try { if (obj instanceof Map) { Set<Entry> set = ((Map) obj).entrySet(); for (Entry entry : set) { String key = (String) entry.getKey(); if (interceptor != null && interceptor.preBreakIntercept(obj, key, entry.getValue())) { break; } if (interceptor != null && interceptor.continueIntercept(obj, key, entry.getValue())) { continue; } if (key.contains(".") || key.contains(" ")) { throw new IllegalStateException("the Key of map can not contains '.' or ' '"); } if (n > 0) { sqlBuilder.append(separator); } prod = replaceAll(itemDynamicPattern, itemFormatter, collection + "." + key); if (indexStaticPattern != null) { prod = replaceAll(indexStaticPattern, prod, key); } prod = replaceBindingMap(itemBindingPattern, item, prod, key, obj); sqlBuilder.append(prod); n++; if (interceptor != null && interceptor.postBreakIntercept(obj, key, entry.getValue())) { break; } } } else if (obj instanceof List) { List list = (List) obj; for (int i = 0, size = list.size(); i < size; i++) { if (interceptor != null && interceptor.preBreakIntercept(obj, i, list.get(i))) { break; } if (interceptor != null && interceptor.continueIntercept(obj, i, list.get(i))) { continue; } if (n > 0) { sqlBuilder.append(separator); } prod = replaceAll(itemDynamicPattern, itemFormatter, collection + "[" + i + "]"); if (indexStaticPattern != null) { prod = replaceAll(indexStaticPattern, prod, "" + i); } prod = replaceBindingList(itemBindingPattern, item, prod, "" + i, obj); sqlBuilder.append(prod); n++; if (interceptor != null && interceptor.postBreakIntercept(obj, i, list.get(i))) { break; } } } else if (obj.getClass().isArray()) { List list = Arrays.asList((Object[]) obj); for (int i = 0, size = list.size(); i < size; i++) { if (interceptor != null && interceptor.preBreakIntercept(obj, i, list.get(i))) { break; } if (interceptor != null && interceptor.continueIntercept(obj, i, list.get(i))) { continue; } if (n > 0) { sqlBuilder.append(separator); } prod = replaceAll(itemDynamicPattern, itemFormatter, collection + "[" + i + "]"); if (indexStaticPattern != null) { prod = replaceAll(indexStaticPattern, prod, "" + i); } prod = replaceBindingList(itemBindingPattern, item, prod, "" + i, obj); sqlBuilder.append(prod); n++; if (interceptor != null && interceptor.postBreakIntercept(obj, i, list.get(i))) { break; } } } else { throw new IllegalArgumentException("the Type of collection support only Array,List,Map"); } } catch (OgnlException e) { throw new BindingException("ognl exception", e); } if (n < 1) { sqlBuilder.delete(0, sqlBuilder.length()); } else { if (close != null) { sqlBuilder.append(close); } } return sqlBuilder; } public static interface Interceptor<C, K, T> { /** * for循环内是否执行break语句, break语句在循环内第一行 * @param collection 集合 * @param item 集合元素 * @param key 集合key或下标 * @return 返回true,则执行break语句 */ boolean preBreakIntercept(C collection, K key, T item); /** * for循环内是否执行break语句, break语句在循环内最后一行 * @param collection 集合 * @param item 集合元素 * @param key 集合key或下标 * @return 返回true,则执行break语句 */ boolean postBreakIntercept(C collection, K key, T item); /** * for循环内是否执行continue语句 * @param collection 集合 * @param item 集合元素 * @param key 集合key或下标 * @return 返回true,则执行continue语句 */ boolean continueIntercept(C collection, K key, T item); } private static String replaceAll(Pattern pattern, String itemFormatter, String collection) { itemFormatter = pattern.matcher(itemFormatter).replaceAll(collection); return itemFormatter; } private static <C> String replaceBindingMap(Pattern pattern, String item, String itemFormatter, String index, C obj) throws OgnlException { Matcher matcher = pattern.matcher(itemFormatter); StringBuffer buffer = new StringBuffer(); matcher.reset(); String group = ""; while (matcher.find()) { group = matcher.group(); group = group.replaceFirst("\$\{" + item, "#root['" + index + "']"); group = group.substring(0, group.length() - 1).trim(); group = String.valueOf(Ognl.getValue(group, obj)); matcher.appendReplacement(buffer, group); } matcher.appendTail(buffer); return buffer.toString(); } private static <C> String replaceBindingList(Pattern pattern, String item, String itemFormatter, String index, C obj) throws OgnlException { Matcher matcher = pattern.matcher(itemFormatter); StringBuffer buffer = new StringBuffer(); matcher.reset(); String group = ""; while (matcher.find()) { group = matcher.group(); group = group.replaceFirst("\$\{" + item, "#root[" + index + "]"); group = group.substring(0, group.length() - 1).trim(); group = String.valueOf(Ognl.getValue(group, obj)); matcher.appendReplacement(buffer, group); } matcher.appendTail(buffer); return buffer.toString(); } public static class ParamObject<C> { private C obj; private String collection; private String item = "item"; private String index; private String itemFormatter; private String separator; private String open; private String close; public C getObj() { return obj; } public ParamObject<C> setObj(C obj) { this.obj = obj; return this; } public String getCollection() { return collection; } public ParamObject<C> setCollection(String collection) { this.collection = collection; return this; } public String getItem() { return item; } public ParamObject<C> setItem(String item) { this.item = item; return this; } public String getIndex() { return index; } public ParamObject<C> setIndex(String index) { this.index = index; return this; } public String getItemFormatter() { return itemFormatter; } public ParamObject<C> setItemFormatter(String itemFormatter) { this.itemFormatter = itemFormatter; return this; } public String getSeparator() { return separator; } public ParamObject<C> setSeparator(String separator) { this.separator = separator; return this; } public String getOpen() { return open; } public ParamObject<C> setOpen(String open) { this.open = open; return this; } public String getClose() { return close; } public ParamObject<C> setClose(String close) { this.close = close; return this; } public StringBuilder foreach() { return this.foreach(null); } @SuppressWarnings("rawtypes") public StringBuilder foreach(Interceptor interceptor) { return ForeachMybatisUtils.foreach(this, interceptor); } } public interface InnerForeach<C, K> { CharSequence foreach(C innerObj, K index); } @SuppressWarnings({ "rawtypes", "unchecked" }) public static <C> StringBuilder nestedForeach(C obj, String separator, String open, String close, InnerForeach innerForeach) { if (obj == null) { throw new NullPointerException("object can not is null"); } if (separator == null) { separator = ""; } StringBuilder sqlBuilder = new StringBuilder(); if (open != null) { sqlBuilder.append(open); } int n = 0; int i = 0; CharSequence sqlItem = null; if (obj instanceof Map) { Set<Entry> set = ((Map) obj).entrySet(); for (Entry entry : set) { sqlItem = innerForeach.foreach(entry.getValue(), entry.getKey()); if (sqlItem != null && !sqlItem.toString().trim().isEmpty()) { if (n > 0) { sqlBuilder.append(separator); } sqlBuilder.append(sqlItem); n++; } i++; } } else if (obj instanceof List) { List list = (List) obj; for (Object element : list) { sqlItem = innerForeach.foreach(element, i); if (sqlItem != null && !sqlItem.toString().trim().isEmpty()) { if (n > 0) { sqlBuilder.append(separator); } sqlBuilder.append(sqlItem); n++; } i++; } } else if (obj.getClass().isArray()) { List list = Arrays.asList((Object[]) obj); for (Object element : list) { sqlItem = innerForeach.foreach(element, i); if (sqlItem != null && !sqlItem.toString().trim().isEmpty()) { if (n > 0) { sqlBuilder.append(separator); } sqlBuilder.append(sqlItem); n++; } i++; } } else { throw new IllegalArgumentException("the Type of collection support only Array,List,Map"); } if (n < 1) { sqlBuilder.delete(0, sqlBuilder.length()); } else { if (close != null) { sqlBuilder.append(close); } } return sqlBuilder; }
测试
复制代码
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
139public static void main(String[] args) { String[][] strs = {{"ddd","jfhd","uyijn"}, {"ddd","jgwhd","uyijn"}, {"ddd","kyugkfd","uyijn"}}; // List<String[]> list = Arrays.asList(strs); Map<String, Object> map = new HashMap<>(); map.put("fwgsss", new String[] {"ddd","jfhd","uyijn"}); map.put("uytr", new String[] {"ddd","jgwhd","uyijn"}); map.put("2", new String[] {"ddd","kyugkfd","uyijn"}); StringBuilder foreach = foreach(map, "wea.arr", "item", "index", "SELECT #{item[1], jdbcType=VARCHAR, javaType=java.lang.String} AS SFF, #{item[2], jdbcType=VARCHAR} AS AT, '${index }' AS FV FROM dual", " UNION ALL ", " (", ")", new Interceptor<Map<String, Object>, String, String[]>() { @Override public boolean preBreakIntercept(Map<String, Object> collection, String key, String[] item) { // TODO Auto-generated method stub return false; } @Override public boolean postBreakIntercept(Map<String, Object> collection, String key, String[] item) { // TODO Auto-generated method stub return false; } @Override public boolean continueIntercept(Map<String, Object> collection, String key, String[] item) { // TODO Auto-generated method stub return false; } }); System.out.println(foreach); StringBuilder foreach1 = foreach(strs, "wea.arr", "item", "index", "SELECT #{item[1], jdbcType=VARCHAR, javaType=java.lang.String} AS SFF, #{item[2], jdbcType=VARCHAR} AS AT FROM dual", " UNION ALL ", " (", ")", new Interceptor<String[][], Integer, String[]>() { @Override public boolean preBreakIntercept(String[][] collection, Integer key, String[] item) { return false; } @Override public boolean postBreakIntercept(String[][] collection, Integer key, String[] item) { return false; } @Override public boolean continueIntercept(String[][] collection, Integer key, String[] item) { return false; } }); System.out.println(foreach1); StringBuilder foreach2 = ForeachMybatisUtils.createParamObject(strs) .setCollection("wea.arr") .setItem("item") .setIndex("index") .setItemFormatter("SELECT #{item[1], jdbcType=VARCHAR, javaType=java.lang.String} AS SFF, #{item[2], jdbcType=VARCHAR} AS AT FROM dual") .setSeparator(" UNION ALL ") .setOpen(" (") .setClose(")") .foreach(new ForeachMybatisUtils.Interceptor<String[][], Integer, String[]>() { @Override public boolean preBreakIntercept(String[][] collection, Integer key, String[] item) { return false; } @Override public boolean postBreakIntercept(String[][] collection, Integer key, String[] item) { return false; } @Override public boolean continueIntercept(String[][] collection, Integer key, String[] item) { return false; } }); System.out.println(foreach2); StringBuilder foreach3 = ForeachMybatisUtils.foreach(ForeachMybatisUtils.createParamObject(strs) .setCollection("wea.arr") .setItem("item") .setIndex("index") .setItemFormatter("SELECT #{item[1], jdbcType=VARCHAR, javaType=java.lang.String} AS SFF, #{item[2], jdbcType=VARCHAR} AS AT FROM dual") .setSeparator(" UNION ALL ") .setOpen(" (") .setClose(")"), new ForeachMybatisUtils.Interceptor<String[][], Integer, String[]>() { @Override public boolean preBreakIntercept(String[][] collection, Integer key, String[] item) { return false; } @Override public boolean postBreakIntercept(String[][] collection, Integer key, String[] item) { return false; } @Override public boolean continueIntercept(String[][] collection, Integer key, String[] item) { return false; } } ); System.out.println(foreach3); } public static void main(String[] args) { // @Param("list") List<List<Integer>> lists = new ArrayList<List<Integer>>(); List<Integer> list1 = new ArrayList<>(); list1.add(1); list1.add(2); list1.add(3); lists.add(list1); List<Integer> list2 = new ArrayList<>(); list2.add(11); list2.add(12); list2.add(13); list2.add(14); list2.add(19); lists.add(list2); List<Integer> list3 = new ArrayList<>(); list3.add(31); list3.add(32); list3.add(35); list3.add(38); lists.add(list3); StringBuilder sql = ForeachMybatisUtils.nestedForeach(lists, " union all ", "select b.id, b.name from (", ") b", new ForeachMybatisUtils.InnerForeach<List<Integer>, Integer>() { @Override public CharSequence foreach(List<Integer> innerObj, Integer index) { return ForeachMybatisUtils.createParamObject(innerObj) .setCollection("list[" + index + "]") .setItem("item") .setItemFormatter("#{item}") .setSeparator(",") .setOpen("select id, name from table_demo where id in (") .setClose(")") .foreach(); } }); System.out.println(sql); StringBuilder sql2 = ForeachMybatisUtils.createParamObject(lists) .setCollection("list") .setItem("item") .setItemFormatter("'${item[1]}'") .setSeparator(",") .setOpen("select id, name from table_demo where id in (") .setClose(")") .foreach(); System.out.println(sql2.toString());
集成进spring后的使用方法
Mapper层
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18package com.xxx.manage.mapper; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.Param; import org.apache.ibatis.annotations.SelectProvider; import org.springframework.stereotype.Repository; import com.xxx.manage.bo.DeviceBO; import com.xxx.manage.provider.ManageProvider; @Repository public interface ManageMapper { @SelectProvider(type = ManageProvider.class, method = "queryDevices") List<Map<String, Object>> queryDevices(@Param("devicetypeno") String devicetypeno, @Param("list") List<Object> list); @SelectProvider(type = ManageProvider.class, method = "queryMap") List<Map<String, Object>> queryMap(@Param("map") Map<String, List<Integer>> map); @SelectProvider(type = ManageProvider.class, method = "queryList") List<Map<String, Object>> queryList(@Param("list") List<Map<String, Object>> lists); }
Provider层
复制代码
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
63package com.xxx.manage.provider; import java.util.List; import java.util.Map; import org.apache.ibatis.jdbc.SQL; import com.xxx.common.util.ForeachMybatisUtils; public class ManageProvider { public String queryDevices(Map<String, Object> params) { @SuppressWarnings("unchecked") List<Object> list = (List<Object>) params.get("list"); SQL sql = new SQL() .SELECT("TERMINALNUM, ORGCODE, DEVICETYPENO, DEVICENAME") .FROM("S_DEVICE_INFO") .WHERE("DEVICETYPENO = #{devicetypeno}"); StringBuilder inBuilder = ForeachMybatisUtils.foreach(list, "list", "item", "#{item}", ", ", "ORGCODE IN (", ")"); if (inBuilder.length() > 0) { sql.WHERE(inBuilder.toString()); } return sql.toString(); } public String queryMap(Map<String, Object> params) { @SuppressWarnings("unchecked") Map<String, List<Integer>> map = (Map<String, List<Integer>>) params.get("map"); StringBuilder sqlBuilder = ForeachMybatisUtils.nestedForeach(map, " union all ", "select b.id, b.name from (", ") b", new ForeachMybatisUtils.InnerForeach<List<Integer>, String>() { @Override public CharSequence foreach(List<Integer> innerObj, String index) { return ForeachMybatisUtils.createParamObject(innerObj) .setCollection("map." + index + "") .setItem("item") .setItemFormatter("#{item, jdbcType=NUMERIC}") .setSeparator(",") .setOpen("select id, name from table_demo where id in (") .setClose(")") .foreach(); } }); System.out.println(sqlBuilder.toString()); return sqlBuilder.toString(); } public String queryList(Map<String, Object> params) { @SuppressWarnings("unchecked") List<Map<String, Object>> list = (List<Map<String, Object>>) params.get("list"); StringBuilder sqlBuilder = ForeachMybatisUtils.nestedForeach(list, " union all ", "select b.id, b.name from (", ") b", new ForeachMybatisUtils.InnerForeach<Map<String, Object>, Integer>() { @Override public CharSequence foreach(Map<String, Object> innerObj, Integer index) { return ForeachMybatisUtils.createParamObject(innerObj) .setCollection("list[" + index + "]") .setItem("item") .setItemFormatter("#{item, jdbcType=NUMERIC}") .setSeparator(",") .setOpen("select id, name from table_demo where id in (") .setClose(")") .foreach(); } }); System.out.println(sqlBuilder.toString()); return sqlBuilder.toString(); } }
Ognl问题
高版本的mybatis使用${}注入时，可能会抛出异常
MemberAccess implementation must be provided - null not permitted!
解决方案
创建一个DefaultMemberAccess.java文件
复制代码
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
93package com.xxx.common.util; import java.lang.reflect.*; import java.util.*; import org.apache.ibatis.ognl.MemberAccess; public class DefaultMemberAccess implements MemberAccess { private boolean allowPrivateAccess = false; private boolean allowProtectedAccess = false; private boolean allowPackageProtectedAccess = false; /*=================================================================== Constructors ===================================================================*/ public DefaultMemberAccess(boolean allowAllAccess) { this(allowAllAccess, allowAllAccess, allowAllAccess); } public DefaultMemberAccess(boolean allowPrivateAccess, boolean allowProtectedAccess, boolean allowPackageProtectedAccess) { super(); this.allowPrivateAccess = allowPrivateAccess; this.allowProtectedAccess = allowProtectedAccess; this.allowPackageProtectedAccess = allowPackageProtectedAccess; } /*=================================================================== Public methods ===================================================================*/ public boolean getAllowPrivateAccess() { return allowPrivateAccess; } public void setAllowPrivateAccess(boolean value) { allowPrivateAccess = value; } public boolean getAllowProtectedAccess() { return allowProtectedAccess; } public void setAllowProtectedAccess(boolean value) { allowProtectedAccess = value; } public boolean getAllowPackageProtectedAccess() { return allowPackageProtectedAccess; } public void setAllowPackageProtectedAccess(boolean value) { allowPackageProtectedAccess = value; } /*=================================================================== MemberAccess interface ===================================================================*/ @Override public Object setup(Map context, Object target, Member member, String propertyName) { Object result = null; if (isAccessible(context, target, member, propertyName)) { AccessibleObject accessible = (AccessibleObject)member; if (!accessible.isAccessible()) { result = Boolean.FALSE; accessible.setAccessible(true); } } return result; } @Override public void restore(Map context, Object target, Member member, String propertyName, Object state) { if (state != null) { ((AccessibleObject)member).setAccessible(((Boolean)state).booleanValue()); } } /** Returns true if the given member is accessible or can be made accessible by this object. */ @Override public boolean isAccessible(Map context, Object target, Member member, String propertyName) { int modifiers = member.getModifiers(); boolean result = Modifier.isPublic(modifiers); if (!result) { if (Modifier.isPrivate(modifiers)) { result = getAllowPrivateAccess(); } else { if (Modifier.isProtected(modifiers)) { result = getAllowProtectedAccess(); } else { result = getAllowPackageProtectedAccess(); } } } return result; } }
改造ForeachMybatisUtils.java类
添加一个静态字段context，
将context插入，Ognl.getValue和Ognl.setValue方法的第二个参数
复制代码
1
2
3private static final OgnlContext context = new OgnlContext(null,null,new DefaultMemberAccess(true)); Ognl.getValue(group, context, obj); Ognl.setValue(group, context, obj, value);
完整的ForeachMybatisUtils.java类
复制代码
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
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385package com.xxx.common.util; import java.util.Arrays; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.ibatis.binding.BindingException; import org.apache.ibatis.ognl.Ognl; import org.apache.ibatis.ognl.OgnlException; import java.util.Set; public class ForeachMybatisUtils { private static final OgnlContext context = new OgnlContext(null,null,new DefaultMemberAccess(true)); private ForeachMybatisUtils() {} public static <C> ParamObject<C> createParamObject(C obj) { ParamObject<C> paramObject = new ParamObject<C>(); paramObject.setObj(obj); return paramObject; } public static <C> StringBuilder foreach(ParamObject<C> paramObject) { return foreach(paramObject, null); } @SuppressWarnings("rawtypes") public static <C> StringBuilder foreach(ParamObject<C> paramObject, Interceptor interceptor) { return foreach(paramObject.getObj(), paramObject.getCollection(), paramObject.getItem(), paramObject.getIndex(), paramObject.getItemFormatter(), paramObject.getSeparator(), paramObject.getOpen(), paramObject.getClose(), interceptor); } /** * itemFormatter部分用法：#{item,jdbcType=VARCHAR},#{item.3345,jdbcType=VARCHAR}其中3345为map的key, ${item}, ${item['3345']}其中3345为map的key * @param <C> List.class、Map.class、Array * @param obj list、map、数组对象 * @param collection 对应xml foreach标签的collection属性 * @param item 对应xml foreach标签的item属性 * @param index 对应xml foreach标签的index属性，但是在itemFormatter中只匹配 ${ } 格式 * @param itemFormatter 对应xml foreach标签内 #{item} * @param separator 对应xml foreach标签的separator属性 * @param open 对应xml foreach标签的open属性 * @param close 对应xml foreach标签的close属性 * @return 拼接后的动态sql */ public static <C> StringBuilder foreach(C obj, String collection, String item, String itemFormatter, String separator, String open, String close) { return foreach(obj, collection, item, null, itemFormatter, separator, open, close, null); } public static <C> StringBuilder foreach(C obj, String collection, String item, String index, String itemFormatter, String separator, String open, String close) { return foreach(obj, collection, item, index, itemFormatter, separator, open, close, null); } @SuppressWarnings({ "rawtypes", "unchecked" }) public static <C> StringBuilder foreach(C obj, String collection, String item, String index, String itemFormatter, String separator, String open, String close, ForeachMybatisUtils.Interceptor interceptor) { if (obj == null) { throw new NullPointerException("object cannot be null"); } if (collection == null || collection.trim().equals("")) { throw new NullPointerException("collection cannot be blank"); } if (item == null || item.trim().isEmpty()) { throw new NullPointerException("item cannot be blank"); } if (itemFormatter == null) { throw new NullPointerException("itemFormatter cannot be null, and you can fill #{item},please"); } collection = collection.trim(); item = item.trim(); if (index != null && item.equals(index.trim())) { throw new IllegalArgumentException("index cannot be equal to item"); } Pattern itemDynamicPattern = Pattern.compile("(?<=#\{)" + item + "(?=[\s\S]*\})"); Pattern itemBindingPattern = Pattern.compile("\$\{" + item + "(?:(?:\.|\[)\S+)?\s*\}"); Pattern indexStaticPattern = null; if (index != null && !index.trim().isEmpty() && itemFormatter.contains("${")) { indexStaticPattern = Pattern.compile("\$\{" + index.trim() + "\s*\}"); } if (separator == null) { separator = ""; } StringBuilder sqlBuilder = new StringBuilder(); if (open != null) { sqlBuilder.append(open); } String prod = ""; int n = 0; try { if (obj instanceof Map) { Set<Entry> set = ((Map) obj).entrySet(); for (Entry entry : set) { String key = (String) entry.getKey(); if (interceptor != null && interceptor.preBreakIntercept(obj, key, entry.getValue())) { break; } if (interceptor != null && interceptor.continueIntercept(obj, key, entry.getValue())) { continue; } if (key.contains(".") || key.contains(" ")) { throw new IllegalStateException("the Key of map can not contains '.' or ' '"); } if (n > 0) { sqlBuilder.append(separator); } prod = replaceAll(itemDynamicPattern, itemFormatter, collection + "." + key); if (indexStaticPattern != null) { prod = replaceAll(indexStaticPattern, prod, key); } prod = replaceBindingMap(itemBindingPattern, item, prod, key, obj); sqlBuilder.append(prod); n++; if (interceptor != null && interceptor.postBreakIntercept(obj, key, entry.getValue())) { break; } } } else if (obj instanceof List) { List list = (List) obj; for (int i = 0, size = list.size(); i < size; i++) { if (interceptor != null && interceptor.preBreakIntercept(obj, i, list.get(i))) { break; } if (interceptor != null && interceptor.continueIntercept(obj, i, list.get(i))) { continue; } if (n > 0) { sqlBuilder.append(separator); } prod = replaceAll(itemDynamicPattern, itemFormatter, collection + "[" + i + "]"); if (indexStaticPattern != null) { prod = replaceAll(indexStaticPattern, prod, "" + i); } prod = replaceBindingList(itemBindingPattern, item, prod, "" + i, obj); sqlBuilder.append(prod); n++; if (interceptor != null && interceptor.postBreakIntercept(obj, i, list.get(i))) { break; } } } else if (obj.getClass().isArray()) { List list = Arrays.asList((Object[]) obj); for (int i = 0, size = list.size(); i < size; i++) { if (interceptor != null && interceptor.preBreakIntercept(obj, i, list.get(i))) { break; } if (interceptor != null && interceptor.continueIntercept(obj, i, list.get(i))) { continue; } if (n > 0) { sqlBuilder.append(separator); } prod = replaceAll(itemDynamicPattern, itemFormatter, collection + "[" + i + "]"); if (indexStaticPattern != null) { prod = replaceAll(indexStaticPattern, prod, "" + i); } prod = replaceBindingList(itemBindingPattern, item, prod, "" + i, obj); sqlBuilder.append(prod); n++; if (interceptor != null && interceptor.postBreakIntercept(obj, i, list.get(i))) { break; } } } else { throw new IllegalArgumentException("the Type of collection support only Array,List,Map"); } } catch (OgnlException e) { throw new BindingException("ognl exception", e); } if (n < 1) { sqlBuilder.delete(0, sqlBuilder.length()); } else { if (close != null) { sqlBuilder.append(close); } } return sqlBuilder; } public static interface Interceptor<C, K, T> { /** * for循环内是否执行break语句, break语句在循环内第一行 * @param collection 集合 * @param item 集合元素 * @param key 集合key或下标 * @return 返回true,则执行break语句 */ boolean preBreakIntercept(C collection, K key, T item); /** * for循环内是否执行break语句, break语句在循环内最后一行 * @param collection 集合 * @param item 集合元素 * @param key 集合key或下标 * @return 返回true,则执行break语句 */ boolean postBreakIntercept(C collection, K key, T item); /** * for循环内是否执行continue语句 * @param collection 集合 * @param item 集合元素 * @param key 集合key或下标 * @return 返回true,则执行continue语句 */ boolean continueIntercept(C collection, K key, T item); } private static String replaceAll(Pattern pattern, String itemFormatter, String collection) { itemFormatter = pattern.matcher(itemFormatter).replaceAll(collection); return itemFormatter; } private static <C> String replaceBindingMap(Pattern pattern, String item, String itemFormatter, String index, C obj) throws OgnlException { Matcher matcher = pattern.matcher(itemFormatter); StringBuffer buffer = new StringBuffer(); matcher.reset(); String group = ""; while (matcher.find()) { group = matcher.group(); group = group.replaceFirst("\$\{" + item, "#root['" + index + "']"); group = group.substring(0, group.length() - 1).trim(); group = String.valueOf(Ognl.getValue(group, context, obj)); matcher.appendReplacement(buffer, group); } matcher.appendTail(buffer); return buffer.toString(); } private static <C> String replaceBindingList(Pattern pattern, String item, String itemFormatter, String index, C obj) throws OgnlException { Matcher matcher = pattern.matcher(itemFormatter); StringBuffer buffer = new StringBuffer(); matcher.reset(); String group = ""; while (matcher.find()) { group = matcher.group(); group = group.replaceFirst("\$\{" + item, "#root[" + index + "]"); group = group.substring(0, group.length() - 1).trim(); group = String.valueOf(Ognl.getValue(group, context, obj)); matcher.appendReplacement(buffer, group); } matcher.appendTail(buffer); return buffer.toString(); } public static class ParamObject<C> { private C obj; private String collection; private String item = "item"; private String index; private String itemFormatter; private String separator; private String open; private String close; public C getObj() { return obj; } public ParamObject<C> setObj(C obj) { this.obj = obj; return this; } public String getCollection() { return collection; } public ParamObject<C> setCollection(String collection) { this.collection = collection; return this; } public String getItem() { return item; } public ParamObject<C> setItem(String item) { this.item = item; return this; } public String getIndex() { return index; } public ParamObject<C> setIndex(String index) { this.index = index; return this; } public String getItemFormatter() { return itemFormatter; } public ParamObject<C> setItemFormatter(String itemFormatter) { this.itemFormatter = itemFormatter; return this; } public String getSeparator() { return separator; } public ParamObject<C> setSeparator(String separator) { this.separator = separator; return this; } public String getOpen() { return open; } public ParamObject<C> setOpen(String open) { this.open = open; return this; } public String getClose() { return close; } public ParamObject<C> setClose(String close) { this.close = close; return this; } public StringBuilder foreach() { return this.foreach(null); } @SuppressWarnings("rawtypes") public StringBuilder foreach(Interceptor interceptor) { return ForeachMybatisUtils.foreach(this, interceptor); } } public interface InnerForeach<C, K> { CharSequence foreach(C innerObj, K index); } @SuppressWarnings({ "rawtypes", "unchecked" }) public static <C> StringBuilder nestedForeach(C obj, String separator, String open, String close, InnerForeach innerForeach) { if (obj == null) { throw new NullPointerException("object can not is null"); } if (separator == null) { separator = ""; } StringBuilder sqlBuilder = new StringBuilder(); if (open != null) { sqlBuilder.append(open); } int n = 0; int i = 0; CharSequence sqlItem = null; if (obj instanceof Map) { Set<Entry> set = ((Map) obj).entrySet(); for (Entry entry : set) { sqlItem = innerForeach.foreach(entry.getValue(), entry.getKey()); if (sqlItem != null && !sqlItem.toString().trim().isEmpty()) { if (n > 0) { sqlBuilder.append(separator); } sqlBuilder.append(sqlItem); n++; } i++; } } else if (obj instanceof List) { List list = (List) obj; for (Object element : list) { sqlItem = innerForeach.foreach(element, i); if (sqlItem != null && !sqlItem.toString().trim().isEmpty()) { if (n > 0) { sqlBuilder.append(separator); } sqlBuilder.append(sqlItem); n++; } i++; } } else if (obj.getClass().isArray()) { List list = Arrays.asList((Object[]) obj); for (Object element : list) { sqlItem = innerForeach.foreach(element, i); if (sqlItem != null && !sqlItem.toString().trim().isEmpty()) { if (n > 0) { sqlBuilder.append(separator); } sqlBuilder.append(sqlItem); n++; } i++; } } else { throw new IllegalArgumentException("the Type of collection support only Array,List,Map"); } if (n < 1) { sqlBuilder.delete(0, sqlBuilder.length()); } else { if (close != null) { sqlBuilder.append(close); } } return sqlBuilder; } }
以上为个人经验，希望能给大家一个参考，也希望大家多多支持靠谱客。
最后
以上就是大力耳机最近收集整理的关于基于mybatis注解动态sql中foreach工具的方法的全部内容，更多相关基于mybatis注解动态sql中foreach工具内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复