mybatis批量操作

mybatis批量操作

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

-主要使用<foreach>

<foreach collection="list" item="item" open="(" close=")" separator="," index="index">
   #{item.xx}, #{item.xx}
</foreach>

详细说明：

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

• 批量新增

<!-- 批量保存用户,并返回每个用户插入的ID -->
<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>

• daoUserMapper

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