SpringBoot+MyBatis批量增删查改操作

SpringBoot+MyBatis批量增删查改操作

本文转载自链接：https://blog.csdn.net/qq_42881421/article/details/90708532

本博文主要介绍SpringBoot+MyBatis的批量增删查改

 

环境：IDEA2018.3 + MySQL5.7.25 + SpringBoot2.1.4 + MyBatis2.0.1

 

数据表

DROP TABLE IF EXISTS `UserInfo`;
CREATE TABLE IF NOT EXISTS `UserInfo` (
  `userInfoId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `userName`varchar(30) NOT NULL COMMENT '用户名',
  `age` int(10) NOT NULL COMMENT '年龄',
  `sex` enum('男','女','保密') DEFAULT '保密' COMMENT '性别',
	`salary` float(7,2) NOT NULL DEFAULT 0.00 COMMENT '工资',
	`regTime` timestamp DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
	`completed` boolean DEFAULT FALSE COMMENT '是否完成任务',
	`remark` text default NULL COMMENT '备注',
  PRIMARY KEY (`userInfoid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

MyBatis批量查询

Dao类

List<UserInfo> batchSelect(List<Long> list);//批量查询

Service类

List<UserInfo> batchSelect(List<Long> list);//按ID批量查询

Service实现类

@Override
    public List<UserInfo> batchSelect(List<Long> list) {
        return userInfoDao.batchSelect(list);
    }

xxxMapper.xml

    <!--按ID批量查询-->
    <select id="batchSelect" resultType="com.example.mybatisdemo1.domin.UserInfo">
        SELECT * FROM UserInfo where userInfoId in
        <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

测试类

    @Test
    public void testBatchSelect(){
        List<Long> userList =new ArrayList();
        userList.add(1L);
        userList.add(2L);
        userList.add(3L);
        
 
        List<UserInfo> list = userService.batchSelect(userList);
        for (UserInfo user : list) {
            System.out.println(user);
        }
 
    }

 结果

 

MyBatis批量插入

Dao类

int batchInsert(List<UserInfo> list);//批量插入

Service类

int batchInsert(@Param("list") List<UserInfo> list);//批量插入

Service实现类

@Override
    public int batchInsert(List<UserInfo> list) {
        return userInfoDao.batchInsert(list);
    }

xxxMapper.xml

    <!--批量插入-->
    <sql id="userInfoColumnsList">
        <trim suffixOverrides=",">
            <if test="item.userName != null">userName,</if>
            <if test="item.age != null">age,</if>
            <if test="item.sex != null">sex,</if>
            <if test="item.salary != null">salary,</if>
            <if test="item.completed != null">completed,</if>
            <if test="item.remark != null">remark</if>
        </trim>
    </sql>
    <sql id="userInfoValuesList">
        <trim suffixOverrides=",">
            <if test="item.userName != null">#{item.userName},</if>
            <if test="item.age != null">#{item.age},</if>
            <if test="item.sex != null">#{item.sex},</if>
            <if test="item.salary != null">#{item.salary},</if>
            <if test="item.completed != null">#{item.completed},</if>
            <if test="item.remark != null">#{item.remark}</if>
        </trim>
    </sql>
 
 
    <!--测试动态批量插入成功,注意application.yml数据库设置要加上&allowMultiQueries=true,否者提示SQLSyntaxErrorException-->
    <insert id="batchInsert" parameterType="com.example.mybatisdemo1.domin.UserInfo" keyColumn="userInfoId" keyProperty="userInfoId" useGeneratedKeys="true">
 
        <foreach item="item" index="index" collection="list" separator=";">
        insert into UserInfo(<include refid="userInfoColumnsList"/>)
        VALUES(<include refid="userInfoValuesList"/>)
        </foreach>
 
    </insert>

说明：foreach里的语句将拼接成如下：

insert into UserInfo(...) VALUES(...);  insert into UserInfo(...) VALUES(...);  insert into UserInfo(...) VALUES(...)

需要设置allowMultiQueries=true，设置方法如下：

application.yml

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/student?serverTimezone=GMT%2B8&allowMultiQueries=true

 测试类

@Test
    public void testBatchInsert(){
        List<UserInfo> list = new ArrayList();
 
        //添加第一条数据
        UserInfo userInfo1 = new UserInfo();
        userInfo1.setUserName("aa");
        userInfo1.setAge(11);
//        userInfo1.setSex("男");
        userInfo1.setSalary(2570.50);
        userInfo1.setCompleted(Boolean.TRUE);
        userInfo1.setRemark("测试动态批量插入 用默认性别");
        list.add(userInfo1);
 
        //添加第二条数据
        UserInfo userInfo2 = new UserInfo();
        userInfo2.setUserName("bb");
        userInfo2.setAge(22);
        userInfo2.setSex("女");
//        userInfo2.setSalary(3257.50);
        userInfo2.setCompleted(Boolean.FALSE);
        userInfo2.setRemark("测试动态批量插入 用默认工资");
        list.add(userInfo2);
 
        //添加第三条数据
        UserInfo userInfo3 = new UserInfo();
        userInfo3.setUserName("cc");
        userInfo3.setAge(33);
        userInfo3.setSex("男");
        userInfo3.setSalary(4600.00);
//        userInfo3.setCompleted(Boolean.FALSE);
        userInfo3.setRemark("测试动态批量插入 用默认完成情况");
        list.add(userInfo3);
 
        System.out.println(list);
        userService.batchInsert(list);
 
 
    }

 

MyBatis批量更新

Dao类

int batchUpdate(List<UserInfo> list);//批量更新

Service类

int batchUpdate(List<UserInfo> list);//批量更新

Service实现类

@Override
    public int batchUpdate(List<UserInfo> list) {
        return userInfoDao.batchUpdate(list);
    }

xxxMapper.xml

    <!--动态批量更新，<set></set>相当于SET-->
    <update id="batchUpdate" parameterType="com.example.mybatisdemo1.domin.UserInfo">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            update UserInfo
 
            <set>
                <if test="item.userName != null">userName=#{item.userName},</if>
                <if test="item.age != null">age=#{item.age},</if>
                <if test="item.sex != null">sex=#{item.sex},</if>
                <if test="item.salary != null">salary=#{item.salary},</if>
                <if test="item.completed != null">completed=#{item.completed},</if>
                <if test="item.remark != null">remark=#{item.remark}</if>
            </set>
            where userInfoId = #{item.userInfoId}
        </foreach>
    </update>

测试类

@Test
    public void testBatchUpdate(){
        List<UserInfo> list = new ArrayList();
 
        UserInfo userInfo1 = new UserInfo();
        userInfo1.setUserName("Liang4");
        userInfo1.setAge(30);
//        userInfo1.setSex("男");
        userInfo1.setUserInfoId(4L);
        userInfo1.setSalary(2000.00);
        userInfo1.setCompleted(Boolean.TRUE);
        userInfo1.setRemark("批量更新的备注，用原来的性别");
        list.add(userInfo1);
 
        UserInfo userInfo2 = new UserInfo();
        userInfo2.setUserName("Liang5");
//        userInfo2.setAge(30);
        userInfo2.setSex("男");
        userInfo2.setUserInfoId(5L);
        userInfo2.setSalary(2000.00);
        userInfo2.setCompleted(Boolean.TRUE);
        userInfo2.setRemark("批量更新的备注，用原来的年龄");
        list.add(userInfo2);
 
        int a = userService.batchUpdate(list);
        // 查看返回值
        System.out.println("a: "+ a );
    }//批量更新成功，注意application.yml数据库设置要加上&allowMultiQueries=true

MyBatis批量删除

Dao类

int batchDelete(List<Long> userInfoId);//批量删除

Service类

int batchDelete(List<Long> userInfoId);

Service实现类

@Override
    public int batchUpdate(List<UserInfo> list) {
        return userInfoDao.batchUpdate(list);
    }

xxxMapper.xml

    <!--批量删除-->
    <delete id="batchDelete" parameterType="com.example.mybatisdemo1.domin.UserInfo">
        delete from UserInfo where userInfoId
        in
        <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
          #{item}
        </foreach>
    </delete>

测试类

@Test
    public void testBatchDelete(){
        List<Long> list = new ArrayList();
        list.add(4L);
        list.add(5L);
        list.add(6L);
        System.out.println(list);
 
        int a = userService.batchDelete(list);
        System.out.println(a);//打印影响的条数，这里输出3
 
    }

完整项目地址：https://github.com/jipsonliang/Springboot-Mybatis-CRUD.git

完成！enjoy it！