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

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

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>


目录
相关文章
|
14天前
|
SQL Java 数据库连接
MyBatis-Plus:简化 CRUD 操作的艺术
MyBatis-Plus 是一个基于 MyBatis 的增强工具,它旨在简化 MyBatis 的使用,提高开发效率。
42 1
MyBatis-Plus:简化 CRUD 操作的艺术
|
28天前
|
Java 数据库连接 Maven
mybatis使用一:springboot整合mybatis、mybatis generator,使用逆向工程生成java代码。
这篇文章介绍了如何在Spring Boot项目中整合MyBatis和MyBatis Generator,使用逆向工程来自动生成Java代码,包括实体类、Mapper文件和Example文件,以提高开发效率。
87 2
mybatis使用一:springboot整合mybatis、mybatis generator,使用逆向工程生成java代码。
|
15天前
|
搜索推荐 Java 数据库连接
Java|在 IDEA 里自动生成 MyBatis 模板代码
基于 MyBatis 开发的项目,新增数据库表以后,总是需要编写对应的 Entity、Mapper 和 Service 等等 Class 的代码,这些都是重复的工作,我们可以想一些办法来自动生成这些代码。
25 6
|
2月前
|
SQL XML Java
mybatis复习02,简单的增删改查,@Param注解多个参数,resultType与resultMap的区别,#{}预编译参数
文章介绍了MyBatis的简单增删改查操作,包括创建数据表、实体类、配置文件、Mapper接口及其XML文件,并解释了`#{}`预编译参数和`@Param`注解的使用。同时,还涵盖了resultType与resultMap的区别,并提供了完整的代码实例和测试用例。
mybatis复习02,简单的增删改查,@Param注解多个参数,resultType与resultMap的区别,#{}预编译参数
|
1月前
|
分布式计算 Java 大数据
大数据-147 Apache Kudu 常用 Java API 增删改查
大数据-147 Apache Kudu 常用 Java API 增删改查
25 1
|
2月前
|
缓存 前端开发 Java
【Java面试题汇总】Spring,SpringBoot,SpringMVC,Mybatis,JavaWeb篇(2023版)
Soring Boot的起步依赖、启动流程、自动装配、常用的注解、Spring MVC的执行流程、对MVC的理解、RestFull风格、为什么service层要写接口、MyBatis的缓存机制、$和#有什么区别、resultType和resultMap区别、cookie和session的区别是什么?session的工作原理
【Java面试题汇总】Spring,SpringBoot,SpringMVC,Mybatis,JavaWeb篇(2023版)
|
30天前
|
SQL Java 数据库连接
Mybatis方式完成CRUD操作
Mybatis方式完成CRUD操作
37 0
|
2月前
|
Java 数据库连接 数据格式
【Java笔记+踩坑】Spring基础2——IOC,DI注解开发、整合Mybatis,Junit
IOC/DI配置管理DruidDataSource和properties、核心容器的创建、获取bean的方式、spring注解开发、注解开发管理第三方bean、Spring整合Mybatis和Junit
【Java笔记+踩坑】Spring基础2——IOC,DI注解开发、整合Mybatis,Junit
|
2月前
|
SQL XML Java
mybatis :sqlmapconfig.xml配置 ++++Mapper XML 文件(sql/insert/delete/update/select)(增删改查)用法
当然,这些仅是MyBatis功能的初步介绍。MyBatis还提供了高级特性,如动态SQL、类型处理器、插件等,可以进一步提供对数据库交互的强大支持和灵活性。希望上述内容对您理解MyBatis的基本操作有所帮助。在实际使用中,您可能还需要根据具体的业务要求调整和优化SQL语句和配置。
44 1
|
3月前
|
前端开发 Java 数据库连接
一天十道Java面试题----第五天(spring的事务传播机制------>mybatis的优缺点)
这篇文章总结了Java面试中的十个问题,包括Spring事务传播机制、Spring事务失效条件、Bean自动装配方式、Spring、Spring MVC和Spring Boot的区别、Spring MVC的工作流程和主要组件、Spring Boot的自动配置原理和Starter概念、嵌入式服务器的使用原因,以及MyBatis的优缺点。