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>
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>
2.2.通过ID物理删除
<delete id="deleteById"> delete from SysUser where UserID = #{userid} </delete>
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>
2.4.通过ID逻辑删除
<update id="logicDeleteById"> update SysUser <trim prefix="set" suffixOverrides=","> is_del = 2 </trim> where UserID = #{userid} </update>
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>
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>
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>
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>
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>
批量逻辑删除
<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>
批量保存数据
<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>