备案控制台
开发者社区 开发与运维 文章 正文

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

2022-05-22 122
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 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>


文章标签：
Java
关键词：
MyBatis crud
MyBatis增删改查
Java mybatis
MyBatis java
MyBatis批量操作
白大锅
目录
相关文章
骚戴
|
4天前
|
Java 数据库 Android开发
不同主题增删改查系统【纯控制台】（Java课设）
不同主题增删改查系统【纯控制台】（Java课设）
骚戴
16 1
宁波阿成.
|
4天前
|
SQL druid Java
java mysql druid mybatis-plus里使用多表删除出错的一种处理方式
java mysql druid mybatis-plus里使用多表删除出错的一种处理方式
宁波阿成.
11 0
咕噜咕噜wy
|
2天前
|
SQL 存储 算法
Mybatis-Plus- CRUD接口-主键策略-自动填充和乐观锁-分页-逻辑删除-条件构造器和常用接口
Mybatis-Plus- CRUD接口-主键策略-自动填充和乐观锁-分页-逻辑删除-条件构造器和常用接口
咕噜咕噜wy
7 0
s:103
|
3天前
|
SQL Java 数据库连接
【JavaEE】懒人的福音-MyBatis框架—[单表]增删改查等常规操作（下）
【JavaEE】懒人的福音-MyBatis框架—[单表]增删改查等常规操作
s:103
7 0
s:103
|
3天前
|
SQL 前端开发 Java
【JavaEE】懒人的福音-MyBatis框架—[单表]增删改查等常规操作（上）
【JavaEE】懒人的福音-MyBatis框架—[单表]增删改查等常规操作
s:103
8 0
长梦
|
4天前
|
XML Java 数据库连接
Java一分钟之MyBatis：持久层框架基础
【5月更文挑战第15天】MyBatis是Java的轻量级持久层框架，它分离SQL和Java代码，提供灵活的数据库操作。常见问题包括：XML配置文件未加载、忘记关闭SqlSession、接口方法与XML映射不一致、占位符使用错误、未配置ResultMap和事务管理不当。解决这些问题的关键在于正确配置映射文件、管理SqlSession、避免SQL注入、定义ResultMap以及确保事务边界。遵循最佳实践可优化MyBatis使用体验。
长梦
12 2
Java一分钟之MyBatis：持久层框架基础
java开发-郭老师
|
4天前
|
SQL Java
【JAVA进阶篇教学】第九篇：MyBatis-Plus用法介绍
【JAVA进阶篇教学】第九篇：MyBatis-Plus用法介绍
java开发-郭老师
18 3
渐暖
|
4天前
|
SQL Java 数据库连接
【mybatis】动态sql之批量增删改查
【mybatis】动态sql之批量增删改查
渐暖
13 0
Yeats_Liao
|
4天前
|
SQL Java 数据库连接
15：MyBatis对象关系与映射结构-Java Spring
15：MyBatis对象关系与映射结构-Java Spring
Yeats_Liao
31 4
yma16
|
4天前
|
Java 数据库连接 数据库
spring+mybatis_编写一个简单的增删改查接口
spring+mybatis_编写一个简单的增删改查接口
yma16
17 2

热门文章

最新文章

  • 1
    使用Redis进行Java缓存策略设计
  • 2
    Java语言开发的AI智慧导诊系统源码springboot+redis 3D互联网智导诊系统源码
  • 3
    【JAVA】Java 中什么叫单例设计模式？请用 Java 写出线程安全的单例模式
  • 4
    基于Java的公司员工工作日志办公系统的设计与实现(源码+lw+部署文档+讲解等)
  • 5
    【JAVA】线程的run()和start()有什么区别？
  • 6
    高德地图爬虫实践：Java多线程并发处理策略
  • 7
    [AIGC] 21世纪Java与Go的相爱相杀
  • 8
    Java多线程实战-CompletableFuture异步编程优化查询接口响应速度
  • 9
    打造个人的Minecraft服务器：Java+cpolar实现我的世界联机游戏
  • 10
    Java 开发者必备：JDK 版本详解与选择策略(含安装与验证)
  • 1
    SpringBoot整合Mybatis连接Oracle数据库
    81
  • 2
    Mybatis-Plus学习小项目及详细教程
    38
  • 3
    Mybatis查询的时候BigDecimal类型的值查询失效的解决办法
    85
  • 4
    【SpringBoot系列】SpringBoot集成Fast Mybatis
    181
  • 5
    Mybatis拦截器实现带参数SQL语句打印
    47
  • 6
    Mybatis中Mapper.xml 文件使用注释遇到的坑
    21
  • 7
    MyBatis Plus 解决大数据量查询慢问题
    68
  • 8
    Mybatis-Flex框架初体验
    329
  • 9
    【Mybatis】XML映射文件
    30
  • 10
    【MyBatis】主配置文件
    30

    • 相关课程

    更多
  • MyBatis持久层框架入门
  • Java面试疑难点解析 - 面试技巧及语言基础
  • Java面试疑难点解析 - Java Web开发
  • Java编程入门
  • Java面向对象编程
  • Java高级编程

    • 相关电子书

    更多
  • 基于Java容器的多应用部署技术实践
  • 从《阿里巴巴Java开发手册》编写推广谈技术成长
  • Java Spring Boot开发实战系列课程【第6讲】：Spring Boot 2.0实战MyBatis与优化(Java面试题)

    • 相关实验场景

    更多
  • 阿里云平台上进行Java程序的编译与运行
  • 使用Java面向对象编写网络通信程序应用
  • Elasticsearch Java API Client 开发
  • 手动部署Java Web环境（Alibaba Cloud Linux 2）
  • 搭建Java Web开发环境（Anolis OS）
  • RocketMQ中使用Java客户端发送消息和消费的应用
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考