mybatis 实现动态参数批量插入

在实际业务开发中，为了减少代码量，实现万变不离其宗的思想，像批量插入、查询、删除等数据库操作以动态参数的方式实现，本文将阐述动态参数实现mybatis批量插入核心代码

1.mapper文件写法

    <sql id="values">
        <foreach item="item" collection="list" separator=",">
            <foreach collection="item" item="detailItem" index="index" open="("
                     close=")" separator=",">
                #{detailItem}
            </foreach>
        </foreach>
    </sql>
    <!-- 批量新增单位信息表信息 -->
    <insert id="addSalAgencys" parameterType="list">
        INSERT INTO ${tableName} (
        <foreach collection="columns" item="columnItem" separator=",">
            ${columnItem}
        </foreach>)
         VALUES
        <include refid="values"></include>
    </insert>

2.持久化层函数

    /**
     * basic sql
     * @param tableName 表名
     * @param columns 列字段集合
     * @param list 值集合
     */
    void addSalAgencys(@Param("tableName") String tableName,
                       @Param("columns") List<String> columns,
                       @Param("list") List<List<Object>> list);

3.参数tableName是表名，这个没什么特别注意的，通过配置表的方式或者别的方式传入都可以，和数据库表名对应即可

columns列字段集合，直接用工具类ReflectionUtil获取就可以，工具类代码

import com.boss.bis.common.exception.BusinessException;
import com.google.common.collect.Lists;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ObjectUtils;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

public class ReflectionUtil {
    public static final char UNDERLINE = '_';
    private static final Logger log = LoggerFactory.getLogger(ReflectionUtil.class);

    public static List<Field> getFields(Class<?> clazz) {
        List<Field> fieldList = new ArrayList<>();
        fieldList.addAll(Lists.newArrayList(clazz.getDeclaredFields()));
        if (!(clazz.getSuperclass().isAssignableFrom(Object.class))) {
            fieldList.addAll(getFields(clazz.getSuperclass()));
        }
        return fieldList;
    }

    public static Field getField(String fieldName, Class<?> cls) {
        try {
            return cls.getDeclaredField(underlineToCamel(fieldName));
        } catch (NoSuchFieldException e) {
            Class<?> parentCls = cls.getSuperclass();
            if (ObjectUtils.isEmpty(parentCls)) {
                log.error(e.getMessage(), e);
                throw new BusinessException(e.getMessage());
            }
            return getField(fieldName, parentCls);
        }
    }

    public static void setValueByField(String fieldName, Object object, Object value) {
        Field field;
        try{
            field = getField(fieldName, object.getClass());
            field.setAccessible(true);
        }catch (Exception e){
            log.error("字段不存在", e);
            return;
        }

        try {
            if (BigDecimal.class.equals(field.getType())) {
                field.set(object, new BigDecimal(String.valueOf(value)));
            } else if (String.class.equals(field.getType())) {
                field.set(object, value);
            }
        } catch (IllegalAccessException e) {
            log.error(e.getMessage(), e);
            throw new BusinessException(e.getMessage());
        }
    }

    /**
     * 下划线格式字符串转换为驼峰格式字符串
     *
     * @param param
     * @return
     */
    public static String underlineToCamel(String param) {
        if (param == null || "".equals(param.trim())) {
            return "";
        }
        if (param.indexOf(UNDERLINE) < 0) {
            return param;
        }
        String paramTmp = param.toLowerCase();
        int len = paramTmp.length();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c = paramTmp.charAt(i);
            if (c == UNDERLINE) {
                if (++i < len) {
                    sb.append(Character.toUpperCase(paramTmp.charAt(i)));
                }
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }

    public static Object getValueByField(String fieldName, Object object) {
        Field field;
        try{
            field = getField(fieldName, object.getClass());
            field.setAccessible(true);
        }catch (Exception e){
            log.error("字段不存在", e);
            throw new BusinessException(e.getMessage());
        }
        try {
            if (BigDecimal.class.equals(field.getType())) {
                return new BigDecimal(String.valueOf(field.get(object)));
            }
            return field.get(object);
        } catch (IllegalAccessException e) {
            log.error(e.getMessage(), e);
            throw new BusinessException(e.getMessage());
        }
    }
}

通过ReflectionUtil.getFields获取实体类的映射字段

// 数据库字段生成
        List<Field> fields = ReflectionUtil.getFields(cls);
        List<String> underList = new ArrayList<>();
        List<String> camelList = new ArrayList<>();
        StringBuilder colsql = new StringBuilder(200);
        for (Field field : fields) {
            if(Modifier.isStatic(field.getModifiers())){
                continue;
            }
            String name = field.getName();
            String underName = BisBeanUtils.camelToUnderline(name).toUpperCase();
            if(camelList.indexOf(name) > -1){
                continue;
            }
            underList.add(underName);
            camelList.add(name);
            colsql.append(underName + " as " + name).append(", ");
        }
        colsql.delete(colsql.length() - 2, colsql.length() - 1);

这样就获取到了带下划线的字段集合underList，即columns
camelList驼峰法的字段集合，用于第4点组装值集合
colsql查询字段并起驼峰法别名，类似于stu_name as stuName, stu_age as stuAge

这样查询出来的数据用map接收，得出来的map就会是以下这样，maps

4.组装插入的值集合list

private List<List<Object>> buildSaveData(List<Map> maps, List<String> camelList) {
        List<List<Object>> dataList = maps.stream().map(t -> {
            List<Object> list = new ArrayList<>();
            camelList.stream().forEach(field -> {
                list.add(t.get(field));
            });
            return list;
        }).collect(Collectors.toList());
        return dataList;
    }

以上就是mybatis实现动态参数批量插入的核心代码，描述上可能比较欠缺，毕竟是一只不太会表达的程序猿，有不明白的铁子可以给我留言。