Mybatis（三）：批量操作

逃离我推掉我的手 2022-06-02 07:46 250阅读 0赞

> 批量操作分为批量插入和批量更新

主要使用`<foreach>`

collection="list" 其中list是固定的，如果是数组就是array
    item="item" 循环中每一项的别名
    open="" 开始标识，比如删除in (id1,id2)， open="(" close=")"
    close="" 结束标识
    separator="," 分隔符号
    index="index" 下标值

批量新增

<insert id="batchSave" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO `test`.`tb_user`(`username`, age)
        VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.username}, #{item.age})
        </foreach>
    </insert>

批量删除

<delete id="batchDelete" parameterType="java.util.List">
        DELETE FROM `test`.`tb_user` WHERE id IN
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

UserMapper

package cn.saytime.mapper;
    
    import cn.saytime.domain.User;
    import java.util.List;
    
    /** * @author L * @ClassName cn.saytime.mapper.UserMapper * @Description */
    public interface UserMapper { 
    
        /** * 批量保存用户 * @param userList */
        int batchSave(List<User> userList);
    
        /** * 批量删除用户 * @param idList */
        int batchDelete(List<Integer> idList);
    }

测试结果：

import cn.saytime.domain.User;
    import cn.saytime.mapper.UserMapper;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.After;
    import org.junit.Before;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    /** * Created by L on 2018/1/3. */
    public class TestUserMapper { 
    
        SqlSessionFactory sqlSessionFactory = null;
        SqlSession sqlSession = null;
        UserMapper userMapper = null;
    
        @Before
        public void before(){
            // mybatis 配置文件地址
            String resource = "mybatis-config.xml";
            InputStream inputStream = null;
            try {
                inputStream = Resources.getResourceAsStream(resource);
            } catch (IOException e) {
                e.printStackTrace();
            }
            // 加载配置文件，并构建sqlSessionFactory
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            // 获取sqlSession对象
            sqlSession = sqlSessionFactory.openSession();
            userMapper = sqlSession.getMapper(UserMapper.class);
        }
    
        @After
        public void after(){
            if(sqlSession != null){
                // 注意这里的commit，否则提交不成功
                sqlSession.commit();
                sqlSession.close();
            }
        }
    
        /** * 测试批量保存 */
        @org.junit.Test
        public void testBatchSave(){
            List<User> userList = new ArrayList();
            userList.add(new User("张飞", 55));
            userList.add(new User("关羽", 60));
    
            int c = userMapper.batchSave(userList);
            System.out.println("新增数量：" + c);
            for (User user : userList) {
                System.out.println(user);
            }
        }
    
        /** * 测试批量删除 */
        @org.junit.Test
        public void testBatchDelete(){
            List<Integer> idList = new ArrayList();
            idList.add(13);
            idList.add(14);
    
            int c = userMapper.batchDelete(idList);
            System.out.println("删除数量为："+c);
    
        }
    
    }

新增数量：2
    User{id=21, username='张飞', age=55}
    User{id=22, username='关羽', age=60}

删除数量为：2