在项目中需要将list
<insert id="insertListbywill" parameterType="string" >
<foreach collection="list" item="paramsMap" open="BEGIN" close=";END;" separator=";" index="index">
insert into A01
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="paramsMap.A00 != null and paramsMap.A00 != ''" >
A00 ,
</if>
<if test="paramsMap.A01 != null and paramsMap.A01 != ''" >
A01,
</if>
<if test="paramsMap.A02 != null and paramsMap.A02 != ''" >
A02 ,
</if>
...
</trim>
<trim prefix=" values (" suffix=")" suffixOverrides="," >
<if test="paramsMap.A00 != null and paramsMap.A00 != ''" >
#{paramsMap.A00},
</if>
<if test="paramsMap.A01 != null and paramsMap.A01 != ''" >
#{paramsMap.A01},
</if>
<if test="paramsMap.A02 != null and paramsMap.A02 != ''" >
#{paramsMap.A02},
</if>
...
</trim>
</foreach>
</insert>
当遇到表较多和字段较多的情况时,其代码行数是直线增长的,为了便捷开发,就在foreach标签中采用了第二层的foreach循环。通过内层循环调用外层循环中的map,通过获取map中的key值获取字段名称及通过key值获取字段值,实现数据的批量操作;
<insert id="insertListbywill" parameterType="string" >
<foreach collection="list" item="paramsMap" open="BEGIN" close=";END;" separator=";" index="index">
insert into ${tableName}
<foreach collection="paramsMap.keys" item="key" open="(" close=")" separator=",">
<if test="key !=null and key != ''">
${key}
</if>
</foreach>
<foreach collection="paramsMap.keys" item="key" open="VALUES (" close=")" separator=",">
<if test="key !=null and key != ''">
#{paramsMap[${key}]}
</if>
</foreach>
</foreach>
</insert>
优点: 解除了mybatis配置与数据表的绑定,减少了开发的代码量;
缺点:有sql注入可能,需在前台处理好数据格式;
示例:
后台代码:
mybatis配置:如上;
打印的参数及sql:
(处理了下换行)
结果:
好了,这篇博客就到这里,如果您有更好的想法欢迎给我留言~