<>使用原因:

        批量新增和批量修改在业务中是很常见的,一条sql访问数据库和通过代码循环体中循环访问数据库做单个数据新增修改相比较下:一条sql访问数据库性能上明显提升,代码且简洁明了

<>批量修改:

1、Mapper.java中

说明:参数是list
@Repository public interface ProcessUnitDurationMapper { int
updateMultiple(List<ProcessUnitDuration> list); }
 2、Mapper.xml中

说明:通过trim标签拼接前后缀和去除多余的逗号,parameterType即是参数list中的元素—实体类(必须写
),item是list中元素的实体类的实例对象,WHEN是条件,THEN是赋值
<update id="updateMultiple"
parameterType="com.luntek.metaversevr.entity.ProcessUnitDuration"> UPDATE
st_process_unit_duration <trim prefix="set" suffixOverrides=","> <trim
prefix="process_id=case" suffix="end,"> <foreach collection="list"
item="processUnitDuration"> WHEN id =#{processUnitDuration.id} THEN
#{processUnitDuration.processId} </foreach> </trim> <trim
prefix="unit_duration=case" suffix="end,"> <foreach collection="list"
item="processUnitDuration"> WHEN id =#{processUnitDuration.id} THEN
#{processUnitDuration.unitDuration} </foreach> </trim> <trim
prefix="init_duration=case" suffix="end,"> <foreach collection="list"
item="processUnitDuration"> WHEN id =#{processUnitDuration.id} THEN
#{processUnitDuration.initDuration} </foreach> </trim> <trim
prefix="update_by=case" suffix="end,"> <foreach collection="list"
item="processUnitDuration"> WHEN id =#{processUnitDuration.id} THEN
#{processUnitDuration.updateBy} </foreach> </trim> <trim
prefix="update_time=case" suffix="end,"> <foreach collection="list"
item="processUnitDuration"> WHEN id =#{processUnitDuration.id} THEN
#{processUnitDuration.updateTime} </foreach> </trim> version = version+1
</trim> <where> <foreach collection="list" separator="or"
item="processUnitDuration"> id =#{processUnitDuration.id} </foreach> </where>
</update>
<>批量新增:

1、Mapper.java中

说明:参数是list
@Repository public interface ProcessUnitDurationMapper { int
insertMultiple(List<ProcessUnitDuration> list); }
 2、Mapper.xml中

分两种情况: (一)、主键自增情况字段较少,插入字段灵活性高  (二)、主键不自增,字段较多

情况一说明:通过trim标签拼接前后缀和去除多余的逗号,parameterType即是参数list中的元素—实体类(必须写
),useGeneratedKeys=true表明主键自增,keyProperty指定主键自增字段
<insert id="insertMultiple"
parameterType="com.luntek.metaversevr.entity.ProcessUnitDuration"
useGeneratedKeys="true" keyProperty="id"> insert into st_process_unit_duration
<trim prefix="(" suffix=")" suffixOverrides=","> <if test="process_id != null">
process_id, </if> <if test="unit_duration != null"> unit_duration, </if> <if
test="init_duration != null"> init_duration </if> </trim> <foreach
collection="list" item="list" index="index" separator=","> <trim
prefix="values(" suffix=")" suffixOverrides=","> <if test="processId != null">
#{list.processId,jdbcType=VARCHAR}, </if> <if test="unitDuration != null">
#{list.unitDuration,jdbcType=INTEGER}, </if> <if test="initDuration != null">
#{list.initDuration,jdbcType=INTEGER} </if> </trim> </foreach> </insert>
情况二说明:如果使用trim标签会显得很臃肿,所以手动拼接即可,parameterType即是参数list中的元素—实体类(必须写
),与情况一对比,字段插入灵活性略低
<sql id="filed"> id, create_by, create_time, update_by, update_time, version,
process_id, unit_duration, init_duration </sql> <insert id="insertMultiple"
parameterType="com.luntek.metaversevr.entity.ProcessUnitDuration"> insert into
st_process_unit_duration( <include refid="filed"/> ) values <foreach
collection="list" item="list" index="index" separator=",">
(#{list.id,jdbcType=VARCHAR}, #{list.createBy,jdbcType=VARCHAR},
#{list.createTime,jdbcType=TIMESTAMP}, #{list.updateBy,jdbcType=VARCHAR},
#{list.updateTime,jdbcType=TIMESTAMP}, #{list.version,jdbcType=INTEGER},
#{list.processId,jdbcType=VARCHAR}, #{list.unitDuration,jdbcType=INTEGER},
#{list.initDuration,jdbcType=INTEGER}) </foreach> </insert>
 注意情况:

        <foreach>标签中:collection属性中的list与传入参数类型相对应,item属性就是list中的元素—实体类的实例对象

 结束语:珍视每一次机会,输给自己的感觉,比输给别人更惨

技术
今日推荐
PPT
阅读数 84
下载桌面版
GitHub
百度网盘(提取码:draw)
Gitee
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:ixiaoyang8@qq.com
QQ群:766591547
关注微信