基于mybatis注解动态sql中foreach工具的方法
目录
- 实现目标
- 工具类
- 测试
- 集成进spring后的使用方法
- Mapper层
- Provider层
- Ognl问题
- 解决方案
- 创建一个DefaultMemberAccess.java文件
- 改造ForeachMybatisUtils.java类
实现目标
由于在注解版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
工具类
package 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; }
测试
public 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层
package 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层
package 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文件
package 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方法的第二个参数
private static final OgnlContext context = new OgnlContext(null,null,new DefaultMemberAccess(true)); Ognl.getValue(group, context, obj); Ognl.setValue(group, context, obj, value);
完整的ForeachMybatisUtils.java类
package 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; } }
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。
赞 (0)