基于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)

相关推荐

  • mybatis防止SQL注入的方法实例详解

    SQL注入是一种很简单的攻击手段,但直到今天仍然十分常见.究其原因不外乎:No patch for stupid.为什么这么说,下面就以JAVA为例进行说明: 假设数据库中存在这样的表: table user( id varchar(20) PRIMARY KEY , name varchar(20) , age varchar(20) ); 然后使用JDBC操作表: private String getNameByUserId(String userId) { Connection conn

  • MyBatis下SQL注入攻击的3种方式

    目录 前言 Mybatis框架下易产生SQL注入漏洞的情况主要分为以下三种: 1.模糊查询 2.in 之后的多个参数 3.order by 之后 二.实战思路 三.总结 前言 SQL注入漏洞作为WEB安全的最常见的漏洞之一,在java中随着预编译与各种ORM框架的使用,注入问题也越来越少.新手代码审计者往往对Java Web应用的多个框架组合而心生畏惧,不知如何下手,希望通过Mybatis框架使用不当导致的SQL注入问题为例,能够抛砖引玉给新手一些思路. Mybatis的SQL语句可以基于注解的

  • Mybatis防止sql注入的实例

    sql注入大家都不陌生,是一种常见的攻击方式,攻击者在界面的表单信息或url上输入一些奇怪的sql片段,例如"or '1'='1'"这样的语句,有可能入侵参数校验不足的应用程序.所以在我们的应用中需要做一些工作,来防备这样的攻击方式.在一些安全性很高的应用中,比如银行软件,经常使用将sql语句全部替换为存储过程这样的方式,来防止sql注入,这当然是一种很安全的方式,但我们平时开发中,可能不需要这种死板的方式. mybatis框架作为一款半自动化的持久层框架,其sql语句都要我们自己来手

  • 基于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

  • mybatis注解动态sql注入map和list方式(防sql注入攻击)

    目录 网上的教程 我的教程(防sql注入攻击) 注入Map 注入List 封装foreach mybatis防止sql注入的循环map写法 网上的教程 配置xml 注解中写xml脚本@Select() 使用Java类中的Java方法拼写sql语句(不防sql注入攻击的纯字符串拼接) 我的教程(防sql注入攻击) 注入Map Mapper层代码 @Repository public interface ManageMapper { @SelectProvider(type = ManageProv

  • Mybatis下动态sql中##和$$的区别讲解

    一.介绍 mybatis 中使用 Mapper.xml里面的配置进行 sql 查询,经常需要动态传递参数,例如我们需要根据用户的姓名来筛选用户时,sql 如下: select * from user where name = "Jack"; 上述 sql 中,我们希望 name 后的参数 "Jack" 是动态可变的,即不同的时刻根据不同的姓名来查询用户.在 Mapper.xml文件中使用如下的 sql 可以实现动态传递参数 name: select * from u

  • 关于Mybatis动态sql中test的坑点总结

    目录 总结Mybatis动态sql中test的坑 判断相等的注意点 判断字符是否相等 动态sql标签的小陷阱 下面先举个正常的例子 总结Mybatis动态sql中test的坑 在mybatis中要实现动态sql,重要方式就是使用test,通过其中表达式返回的true.false来达到动态sql的拼接.随着业务的复杂,test中的判断将会越来越复杂,所以熟悉test中细节处理对动态sql来说尤为重要. 判断相等的注意点 == 少打一个= 现在我们有一个Integer类型的参数typeId,需要当t

  • MyBatis动态SQL中的trim标签的使用方法

    trim标记是一个格式化的标记,可以完成set或者是where标记的功能,如下代码: 1. select * from user <trim prefix="WHERE" prefixoverride="AND |OR"> <if test="name != null and name.length()>0"> AND name=#{name}</if> <if test="gender

  • mybatis的动态sql详解(精)

    MyBatis 的一个强大的特性之一通常是它的动态 SQL 能力.如果你有使用 JDBC 或其他 相似框架的经验,你就明白条件地串联 SQL 字符串在一起是多么的痛苦,确保不能忘了空 格或在列表的最后省略逗号.动态 SQL 可以彻底处理这种痛苦. 通常使用动态SQL不可能是独立的一部分,MyBatis当然使用一种强大的动态SQL语言来改进这种情形,这种语言可以被用在任意映射的SQL语句中. 动态SQL元素和使用 JSTL或其他相似的基于XML的文本处理器相似.在MyBatis之前的版本中,有很多

  • MyBatis使用动态SQL标签的小陷阱

    MyBatis是一个支持普通SQL查询,存储过程和高级映射的优秀持久层框架.MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装.MyBatis可以使用简单的XML或注解用于配置和原始映射,将接口和Java的POJO(Plain Old Java Objects,普通的Java对象)映射成数据库中的记录. 现在MyBatis越来越受大家的喜爱了,它的优势大家都知道,我就不多说了,直接说重点. MyBatis中提供动态SQL功能,我们可以使用<if><when&

  • Mybatis之动态sql标签的使用

    1.Mybatis动态sql MyBatis 的强大特性之一便是它的动态 SQL.如果你有使用 JDBC 或其它类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句的痛苦.例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号.利用动态 SQL 这一特性可以彻底摆脱这种痛苦. 虽然在以前使用动态 SQL 并非一件易事,但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形. 动态 SQL 元素和 JSTL 或基于类似

  • MyBatis常用动态sql大总结

    简介 相信大家没用Mybatis之前,都碰到过各种条件判断拼接SQL.需要去掉多余的逗号等痛苦,Mybatis中的动态SQL能很好的解决上面说的情况,可以很灵活的组装SQL语句,从而提高开发效率. 1.SQL的动态拼接有哪些 if标签 where标签 choose when otherwise标签 set标签 trim标签 bind标签 sql和include标签 foreach标签 2.if标签: test中写判断条件 参数直接paramN或者别名 特点: 只要成立就拼接在Sql语句中,都成立

  • Mybatis的动态Sql组合模式详情

    目录 前言 Component角色 Composite角色 Left类角色 TextSqlNode TrimSqlNode IfSqlNode StaticTextSqlNode 总结 前言 当同一类型的很多对象组成一个树结构的时候,可以考虑使用组合模式,组合模式涉及三个类: Component接口:定义树的各个节点的一些操作 Left类:这个是树的叶子结点,实现Component接口,对于节点的管理它不去实现,只实现业务逻辑 Composite类:这个是树的非叶子节点,实现Component接

随机推荐