mybatis注解动态sql foreach工具
daodfs1 人气:0实现目标
由于在注解版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;
}
}
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
加载全部内容