mybatis中Foreach 使用

Foreach

将数据库中前三个数据的id修改为1,2,3；

需求：我们需要查询 blog 表中 id 分别为1,2,3的博客信息

1、编写接口

List<Blog> queryBlogForeach(Map map);

2、编写SQL语句

<select id="queryBlogForeach" parameterType="map" resultType="blog">
  select * from blog
   <where>
       <!--
       collection:指定输入对象中的集合属性
       item:每次遍历生成的对象
       open:开始遍历时的拼接字符串
       close:结束时拼接的字符串
       separator:遍历对象之间需要拼接的字符串
       select * from blog where 1=1 and (id=1 or id=2 or id=3)
     -->
       <foreach collection="ids"  item="id" open="and (" close=")" separator="or">
          id=#{id}
       </foreach>
   </where>
</select>

3、测试

@Test
public void testQueryBlogForeach(){
   SqlSession session = MybatisUtils.getSession();
   BlogMapper mapper = session.getMapper(BlogMapper.class);

   HashMap map = new HashMap();
   List<Integer> ids = new ArrayList<Integer>();
   ids.add(1);
   ids.add(2);
   ids.add(3);
   map.put("ids",ids);

   List<Blog> blogs = mapper.queryBlogForeach(map);

   System.out.println(blogs);

   session.close();
}

小结：其实动态 sql 语句的编写往往就是一个拼接的问题，为了保证拼接准确，我们最好首先要写原生的 sql 语句出来，然后在通过 mybatis 动态sql 对照着改，防止出错。多在实践中使用才是熟练掌握它的技巧。

Foreach 使用 list 传入

重点是写出sql

List<Blog> queryBlog(List<Blog> blogs);

public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime; // 属性名和字段名不一致
    private int views;

select * from blog where (id = 1 or id = 2 or id =3)

<!--select * from blog where (id = 1 or id = 2  or id =3)-->
    <select id="queryBlog" parameterType="list" resultType="blog">
        select * from blog
       <where>
           <foreach collection="list" item="blog" open="(" close=")" separator="or">
               id = #{blog.id}
           </foreach>
       </where>
    </select>

select * from blog where ((id = 1 and author= ‘czc’)or (id = 2 ))

 <!--select * from blog where ((id = 1 and author= 'czc')or (id = 2  ))-->
    <select id="queryBlog" parameterType="list" resultType="blog">
        select * from blog
        <where>
            <foreach collection="list" item="blog" open="((" close="))" separator=")or(">
                    id = #{blog.id}
                    <if test="blog.author != null">
                        and author= #{blog.author}
                    </if>
            </foreach>
        </where>
    </select>