【Mybatis】功能强大的动态SQL之foreach批量查询、批量插入

前言

在【Mybatis】功能强大的动态SQL之if与choose（03）中介绍了Mybatis动态SQL的if用法，这一节将重点介绍foreach的用法。

在实际的业务场景中，业务层通常会将批量数据放入集合或者数组传给Dao层，并做相应的增删改查操作，而Mybatis可以利用foreach元素来处理集合。

foreach实现批量查询

在学习foreach之前，先给大家回顾一下SQL语句中的or和in的用法。

下面给出查询语句需要用到的表数据，一共七条数据。

 

假设有需求要查询id为1或2或3的用户信息,大家很有可能会想到用or来查询,SQL语句如下。

select * from user where id = 1 or id = 2 or id =3;

但是上述语句看起来比较冗余，不够雅观，于是将上述SQL改用in来查询。

select * from user where id in (1,2,3);

上述语句中将要查询的id编号放在了一对括号中，表示id在这对括号中的用户都会被查出来。

但是在实际业务中，括号中出现的数据通常是业务层动态传入的，这个时候Mybatis框架就提供foreach来解决这一问题。

Dao层接口UserMapper增加selectByIds方法。

public List<User> selectByIds(List<Integer> ids);

映射文件UserMapper.xml中增加

<select id="selectByIds" resultType="com.zssj.domain.User">
        select * from user
        where id in
        <foreach collection="list" open="(" close=")" item="id" separator="," >
            #{id}
        </foreach>
 </select>

测试方法如下

    @Test
    public void test10() throws IOException {
        //1. 读取核心配置文件SqlMapConfig.xml
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. 创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //3. 使用工厂生产一个SqlSession对象
        SqlSession session = factory.openSession();
        //4. 使用SqlSession创建Dao接口的代理对象
        UserMapper userMapper = session.getMapper(UserMapper.class);
        ArrayList<Integer> idList = new ArrayList<Integer>();
        idList.add(1);
        idList.add(2);
        idList.add(3);
        List<User> userList = userMapper.selectByIds(idList);
        System.out.println(userList);
        //5. 释放资源
        session.close();
        in.close();
    }

在测试方法中创建List集合并写入需要查询数据库的id值分别是1，2，3。

调用Dao层接口的selectByIds方法并传入list集合。

UserMapper.xml中对应的select标签利用foreach元素遍历list中的元素，实现批量查询。

实际上foreach元素是对(1,2,3)这个整体的一种翻译。

collection的值表示Dao层传入的是个List集合；open的值表示翻译了左括号；close表示翻译了右括号；item表示集合中的值赋值给id这个变量；separator表示括号中的值是用逗号分隔的;最后用#{id}循环读取id这个变量中的值。

foreach实现批量插入

MySql批量插入

Dao层接口UserMapper增加saveUsers方法

public int saveUsers(List<User> users);

映射文件UserMapper.xml中增加

<insert id="saveUsers">
        insert into user (id, username, password)
        values
        <foreach collection="list" item="user" separator=",">
            (#{user.id}, #{user.username}, #{user.password})
        </foreach>
</insert>

测试方法如下

   @Test
    public void test11() throws IOException {

        //1. 读取核心配置文件SqlMapConfig.xml
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. 创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //3. 使用工厂生产一个SqlSession对象
        SqlSession session = factory.openSession();
        //4. 使用SqlSession创建Dao接口的代理对象
        UserMapper userMapper = session.getMapper(UserMapper.class);
        ArrayList<User> users = new ArrayList<User>();
        User user1 = new User("zhujun","hello123");
        User user2 = new User("xunyu","hello123");
        User user3 = new User("jiayi","hello123");
        users.add(user1);
        users.add(user2);
        users.add(user3);
        userMapper.saveUsers(users);
        //新增数据需要提交事务
        session.commit();
        //6. 释放资源
        session.close();
        in.close();
    }

在测试方法中，我们生成了三个User类对象并放入集合中，调用Dao层saveUsers方法，将集合作为参数传入。

在UserMapper.xml对应的insert标签中利用foreach遍历集合的数据并完成插入。

值得注意的是通过item指定了循环变量后，在引用值的时候采用变量名.属性值的方式取出对应的属性值。

Oracle批量插入

上述Mysql实现批量插入的方式不适用于Oracle，下面给出Oracle的两种批量插入方式。

方式一：利用begin...end...完成插入

Dao层接口UserMapper增加insertUses方法

public int insertUses(List<User> users);

映射文件UserMapper.xml中增加

<insert id="insertUses" >

            <foreach collection="list" item="user" open="begin" close="end;" >
                insert into "user" ("id", "username", "password") values(#{user.id}, #{user.username}, #{user.password});
            </foreach>
</insert>

测试方法如下

@Test
    public void test12() throws IOException {

        //1. 读取核心配置文件SqlMapConfig.xml
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. 创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //3. 使用工厂生产一个SqlSession对象
        SqlSession session = factory.openSession();
        //4. 使用SqlSession创建Dao接口的代理对象
        UserMapper userMapper = session.getMapper(UserMapper.class);
        ArrayList<User> users = new ArrayList<User>();
        User user1 = new User(2,"zhujun","hello123");
        User user2 = new User(3,"xunyu","hello123");
        User user3 = new User(4,"jiayi","hello123");
        users.add(user1);
        users.add(user2);
        users.add(user3);
        int i = userMapper.insertUses(users);
        //新增数据需要提交事务
        session.commit();
        //6. 释放资源
        session.close();
        in.close();
    }

在UserMapper.xml中对应的insert标签中，foreach元素结合begin...end...语法完成了对list集合中数据的批量插入。

方式二：利用insert into table select ... from ...

Dao层接口UserMapper增加insertUses2方法

public int insertUses2(List<User> users);

映射文件UserMapper.xml中增加

<insert id="insertUses2" >
        insert into "user" ("id","username", "password")
        select id, username, password from(
            <foreach collection="list" item="user" separator="union">
                select #{user.id} id, #{user.username} username, #{user.password} password from dual
            </foreach>
)
 </insert>

测试方法如下

 @Test
    public void test13() throws IOException {

        //1. 读取核心配置文件SqlMapConfig.xml
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. 创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //3. 使用工厂生产一个SqlSession对象
        SqlSession session = factory.openSession();
        //4. 使用SqlSession创建Dao接口的代理对象
        UserMapper userMapper = session.getMapper(UserMapper.class);
        ArrayList<User> users = new ArrayList<User>();
        User user1 = new User(5,"zhujun","hello123");
        User user2 = new User(6,"xunyu","hello123");
        User user3 = new User(8,"jiayi","hello123");
        users.add(user1);
        users.add(user2);
        users.add(user3);
        int i = userMapper.insertUses2(users);
        //新增数据需要提交事务
        session.commit();
        //6. 释放资源
        session.close();
        in.close();
    }

foreach实现动态update

Dao层接口UserMapper增加updateByMap方法

public int updateByMap(Map<String, Object> userMap);

映射文件UserMapper.xml中增加

<update id="updateByMap">
        update user
        set
        <foreach collection="_parameter" item="val" index="key" separator=",">
            ${key} = #{val}
        </foreach>
        where id = #{id}
</update>

测试方法如下

@Test
    public void test14() throws IOException {

        //1. 读取核心配置文件SqlMapConfig.xml
        InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2. 创建SqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(in);
        //3. 使用工厂生产一个SqlSession对象
        SqlSession session = factory.openSession();
        //4. 使用SqlSession创建Dao接口的代理对象
        UserMapper userMapper = session.getMapper(UserMapper.class);
        Map<String, Object> map = new HashMap<>();
        map.put("id",1);
        map.put("username", "liuxiang");
        map.put("password","hello789");
        int update = userMapper.updateByMap(map);
        session.commit();
        System.out.println(update);
        //5. 释放资源
        session.close();
        in.close();
    }

在测试方法中创建map集合，并将要修改的值根据key-value对应的形式放入。

key对应着数据库的字段名，value是要将其修改的值。

这样在UserMapper.xml的update标签中采用foreach遍历时，index表示取到的map中key的值，item表示取到了对应的value的值。