- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

Java神鬼莫测之Mybatis--增删改查CRUD以及批量操作(二)

baidaguo 发表于 2022/05/26 23:12:22 2022/05/26

【摘要】 1.与allColumn定义 <resultMap id="sysuserDtoMap" type="com.bsj.wms.pojo.dto.SysuserDto"> &l...

1.与allColumn定义

<resultMap id="sysuserDtoMap" type="com.bsj.wms.pojo.dto.SysuserDto">
        <result column="UserID" property="userid"/>
        <result column="UserCode" property="usercode"/>
        <result column="UserName" property="username"/>
        <result column="UserPwd" property="userpwd"/>
        <result column="UserType" property="usertype"/>
        <result column="DEPID" property="depid"/>
        <result column="UserAddress" property="useraddress"/>
        <result column="UserMobile" property="usermobile"/>
        <result column="UserPhone" property="userphone"/>
        <result column="UserMail" property="usermail"/>
        <result column="EMID" property="emid"/>
        <result column="UserLock" property="userlock"/>
        <result column="UserLockTime" property="userlocktime"/>
        <result column="UserMemo" property="usermemo"/>
        <result column="UserUpdate" property="userupdate"/>
        <result column="TpUploading" property="tpuploading"/>
    </resultMap>

    <!--所有字段-->
    <sql id="allColumn">
        UserID,
        UserCode,
        UserName,
        UserPwd,
        UserType,
        DEPID,
        UserAddress,
        UserMobile,
        UserPhone,
        UserMail,
        EMID,
        UserLock,
        UserLockTime,
        UserMemo,
        UserUpdate,
        TpUploading
    </sql>

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38

2.正常CRUD操作单条数据操作(以正常User表为例)

2.1.插入

<insert id="save" useGeneratedKeys="true" keyProperty="userid">
        insert into SysUser(
            UserCode,
            UserName,
            UserPwd,
            UserType,
            DEPID,
            UserAddress,
            UserMobile,
            UserPhone,
            UserMail,
            EMID,
            UserLock,
            UserLockTime,
            UserMemo,
            UserUpdate,
           TpUploading
        )values (
        <trim suffixOverrides=",">
                    #{usercode},
                    #{username},
                    #{userpwd},
                    #{usertype},
                    #{depid},
                    #{useraddress},
                    #{usermobile},
                    #{userphone},
                    #{usermail},
                    #{emid},
                    #{userlock},
                    #{userlocktime},
                    #{usermemo},
                    #{userupdate},
                    #{tpuploading},
        </trim>
        )
    </insert>

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37

2.2.通过ID物理删除

<delete id="deleteById">
    delete from SysUser
    where UserID = #{userid}
</delete>

  
 
  1
  2
  3
  4

2.3.根据多个条件物理删除

<delete id="delete">
        delete from SysUser where 1=1
            <if test="userid != null">
                and UserID = #{userid}
            </if>
            <if test="usercode != null and ''!= usercode">
                and UserCode = #{usercode}
            </if>
            <if test="username != null and ''!= username">
                and UserName = #{username}
            </if>
            <if test="userpwd != null and ''!= userpwd">
                and UserPwd = #{userpwd}
            </if>
            <if test="usertype != null and ''!= usertype">
                and UserType = #{usertype}
            </if>
            <if test="depid != null">
                and DEPID = #{depid}
            </if>
            <if test="useraddress != null and ''!= useraddress">
                and UserAddress = #{useraddress}
            </if>
            <if test="usermobile != null and ''!= usermobile">
                and UserMobile = #{usermobile}
            </if>
            <if test="userphone != null and ''!= userphone">
                and UserPhone = #{userphone}
            </if>
            <if test="usermail != null and ''!= usermail">
                and UserMail = #{usermail}
            </if>
            <if test="emid != null">
                and EMID = #{emid}
            </if>
            <if test="userlock != null">
                and UserLock = #{userlock}
            </if>
            <if test="usermemo != null and ''!= usermemo">
                and UserMemo = #{usermemo}
            </if>
            <if test="tpuploading != null">
                and TpUploading = #{tpuploading}
            </if>
    </delete>

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45

2.4.通过ID逻辑删除

<update id="logicDeleteById">
        update SysUser
        <trim prefix="set" suffixOverrides=",">
            is_del = 2
        </trim>
        where UserID = #{userid}
</update>

  
 
  1
  2
  3
  4
  5
  6
  7

2.5.根据多个条件逻辑删除

<update id="logicDelete">
        update SysUser
        <trim prefix="set" suffixOverrides=",">
            is_del = 2
        </trim>
        where 1=1
            <if test="usercode != null and ''!= usercode">
                and UserCode = #{usercode}
            </if>
            <if test="username != null and ''!= username">
                and UserName = #{username}
            </if>
            <if test="userpwd != null and ''!= userpwd">
                and UserPwd = #{userpwd}
            </if>
            <if test="usertype != null and ''!= usertype">
                and UserType = #{usertype}
            </if>
            <if test="depid != null">

                and DEPID = #{depid}
             </if>
            <if test="useraddress != null and ''!= useraddress">
                and UserAddress = #{useraddress}
            </if>
            <if test="usermobile != null and ''!= usermobile">
                and UserMobile = #{usermobile}
            </if>
            <if test="userphone != null and ''!= userphone">
                and UserPhone = #{userphone}
            </if>
            <if test="usermail != null and ''!= usermail">
                and UserMail = #{usermail}
            </if>
            <if test="emid != null">

                and EMID = #{emid}
             </if>
            <if test="userlock != null">

                and UserLock = #{userlock}
             </if>
            <if test="usermemo != null and ''!= usermemo">
                and UserMemo = #{usermemo}
            </if>
            <if test="tpuploading != null">

                and TpUploading = #{tpuploading}
             </if>
    </update>

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48
  49
  50

2.6.编辑

<update id="update">
        update SysUser
        <trim prefix="set" suffixOverrides=",">
            <if test="usercode != null and ''!= usercode">
                UserCode = #{usercode},
            </if>
            <if test="username != null and ''!= username">
                UserName = #{username},
            </if>
            <if test="userpwd != null and ''!= userpwd">
                UserPwd = #{userpwd},
            </if>
            <if test="usertype != null and ''!= usertype">
                UserType = #{usertype},
            </if>
            <if test="depid != null">
               DEPID = #{depid},
            </if>
            <if test="useraddress != null and ''!= useraddress">
                UserAddress = #{useraddress},
            </if>
            <if test="usermobile != null and ''!= usermobile">
                UserMobile = #{usermobile},
            </if>
            <if test="userphone != null and ''!= userphone">
                UserPhone = #{userphone},
            </if>
            <if test="usermail != null and ''!= usermail">
                UserMail = #{usermail},
            </if>
            <if test="emid != null">
               EMID = #{emid},
            </if>
            <if test="userlock != null">
               UserLock = #{userlock},
            </if>
             <if test="userlocktime != null">
                UserLockTime = #{userlocktime},
              </if>
            <if test="usermemo != null and ''!= usermemo">
                UserMemo = #{usermemo},
            </if>
             <if test="userupdate != null">
                UserUpdate = #{userupdate},
              </if>
            <if test="tpuploading != null">
               TpUploading = #{tpuploading},
            </if>
        </trim>
        where UserID = #{userid}
    </update>

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48
  49
  50
  51

2.7.单个查询

<select id="findOne" resultMap="sysuserDtoMap">
        select top 1
        <include refid="allColumn"/>
        from SysUser
        where 1=1
            <if test="sysuser.userid != null">
                and UserID = #{sysuser.userid}
            </if>
            <if test="sysuser.usercode != null and ''!= sysuser.usercode">
                and UserCode = #{sysuser.usercode}
            </if>
            <if test="sysuser.username != null and ''!= sysuser.username">
                and UserName = #{sysuser.username}
            </if>
            <if test="sysuser.userpwd != null and ''!= sysuser.userpwd">
                and UserPwd = #{sysuser.userpwd}
            </if>
            <if test="sysuser.usertype != null and ''!= sysuser.usertype">
                and UserType = #{sysuser.usertype}
            </if>
            <if test="sysuser.depid != null">
                and DEPID = #{sysuser.depid}
            </if>
            <if test="sysuser.useraddress != null and ''!= sysuser.useraddress">
                and UserAddress = #{sysuser.useraddress}
            </if>
            <if test="sysuser.usermobile != null and ''!= sysuser.usermobile">
                and UserMobile = #{sysuser.usermobile}
            </if>
            <if test="sysuser.userphone != null and ''!= sysuser.userphone">
                and UserPhone = #{sysuser.userphone}
            </if>
            <if test="sysuser.usermail != null and ''!= sysuser.usermail">
                and UserMail = #{sysuser.usermail}
            </if>
            <if test="sysuser.emid != null">
                and EMID = #{sysuser.emid}
            </if>
            <if test="sysuser.userlock != null">
                and UserLock = #{sysuser.userlock}
            </if>
            <if test="sysuser.usermemo != null and ''!= sysuser.usermemo">
                and UserMemo = #{sysuser.usermemo}
            </if>
            <if test="sysuser.tpuploading != null">
                and TpUploading = #{sysuser.tpuploading}
            </if>
    </select>

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48

2.8.查询全部

<select id="findAll" resultMap="sysuserDtoMap">
        select
        <include refid="allColumn"/>
        from SysUser
        where 1=1
            <if test="sysuser.userid != null">
                and UserID = #{sysuser.userid}
            </if>
            <if test="sysuser.usercode != null and ''!= sysuser.usercode">
                and UserCode = #{sysuser.usercode}
            </if>
            <if test="sysuser.username != null and ''!= sysuser.username">
                and UserName = #{sysuser.username}
            </if>
            <if test="sysuser.userpwd != null and ''!= sysuser.userpwd">
                and UserPwd = #{sysuser.userpwd}
            </if>
            <if test="sysuser.usertype != null and ''!= sysuser.usertype">
                and UserType = #{sysuser.usertype}
            </if>
            <if test="sysuser.depid != null">
                and DEPID = #{sysuser.depid}
            </if>
            <if test="sysuser.useraddress != null and ''!= sysuser.useraddress">
                and UserAddress = #{sysuser.useraddress}
            </if>
            <if test="sysuser.usermobile != null and ''!= sysuser.usermobile">
                and UserMobile = #{sysuser.usermobile}
            </if>
            <if test="sysuser.userphone != null and ''!= sysuser.userphone">
                and UserPhone = #{sysuser.userphone}
            </if>
            <if test="sysuser.usermail != null and ''!= sysuser.usermail">
                and UserMail = #{sysuser.usermail}
            </if>
            <if test="sysuser.emid != null">
                and EMID = #{sysuser.emid}
            </if>
            <if test="sysuser.userlock != null">
                and UserLock = #{sysuser.userlock}
            </if>
            <if test="sysuser.usermemo != null and ''!= sysuser.usermemo">
                and UserMemo = #{sysuser.usermemo}
            </if>
            <if test="sysuser.tpuploading != null">
                and TpUploading = #{sysuser.tpuploading}
            </if>
    </select>

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48

3.批量操作

3.1.批量物理删除

<delete id="deleteListByIds">
        delete from SysUser
         where UserID in
        <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>

  
 
  1
  2
  3
  4
  5
  6
  7

批量逻辑删除

 <update id="logicDeleteListByIds">
        update SysUser
        <trim prefix="set" suffixOverrides=",">
            is_del = 2
        </trim>
           where UserID in
        <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </update>

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

批量保存数据

<insert id="saveList" useGeneratedKeys="true" keyProperty="userid">
        insert into SysUser(
            UserCode,
            UserName,
            UserPwd,
            UserType,
            DEPID,
            UserAddress,
            UserMobile,
            UserPhone,
            UserMail,
            EMID,
            UserLock,
            UserLockTime,
            UserMemo,
            UserUpdate,
            TpUploading
        )values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            <trim suffixOverrides=",">
               #{item.usercode},
               #{item.username},
               #{item.userpwd},
               #{item.usertype},
               #{item.depid},
               #{item.useraddress},
               #{item.usermobile},
               #{item.userphone},
               #{item.usermail},
               #{item.emid},
               #{item.userlock},
               #{item.userlocktime},
               #{item.usermemo},
               #{item.userupdate},
               #{item.tpuploading},
            </trim>
            )
        </foreach>

    </insert>

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41

文章来源: baidaguo.blog.csdn.net，作者：白大锅，版权归原作者所有，如需转载，请联系作者。

原文链接：baidaguo.blog.csdn.net/article/details/118418041

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

Java神鬼莫测之Mybatis--增删改查CRUD以及批量操作(二)

1.与allColumn定义

2.正常CRUD操作单条数据操作(以正常User表为例)

2.1.插入

2.2.通过ID物理删除

2.3.根据多个条件物理删除

2.4.通过ID逻辑删除

2.5.根据多个条件逻辑删除

2.6.编辑

2.7.单个查询

2.8.查询全部

3.批量操作

3.1.批量物理删除

批量逻辑删除

批量保存数据

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

Java神鬼莫测之Mybatis--增删改查CRUD以及批量操作(二)

1.与allColumn定义

2.正常CRUD操作 单条数据操作(以正常User表为例)

2.1.插入

2.2.通过ID物理删除

2.3.根据多个条件物理删除

2.4.通过ID逻辑删除

2.5.根据多个条件逻辑删除

2.6.编辑

2.7.单个查询

2.8.查询全部

3.批量操作

3.1.批量物理删除

批量逻辑删除

批量保存数据

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

推荐阅读

相关产品

2.正常CRUD操作单条数据操作(以正常User表为例)