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>