- if
- choose (when, otherwise)
- trim (where, set)
- foreach
trim 简介
使用when和set的时候,它会自动给sql追加where和set关键字、分隔符、去除多余的and多余的逗号等等;使用 trim 就可以自定义 追加的关键字 分隔符等等;
where 元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
来看看与 set 元素等价的自定义 trim 元素吧:
<trim prefix="SET" suffixOverrides=",">
...
</trim>
其他的动态操作在案例里详细说明
以博客表为案例
目录
案例环境
数据库
博客实体类
//实体类
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
}
博客接口
//接口
public interface BlogMapper {
//插入数据
int addBlog(Blog blog);
//查询博客
List<Blog> queryBlogIF(Map map);
List<Blog> queryBlogChoose(Map map);
//修改博客
int updateBlog(Map map);
//动态Foreach查询
List<Blog> queryBolgForeach(Map map);
}
if动态sql
BlogMapper.xml 映射配置
<!--if动态sql where标签会在有一个条件以上的时候自动添加where条件语句,也会自动去掉第一个条件前的and 、or -->
<select id="queryBlogIF" resultType="Blog" parameterType="map">
select * from blog where 1=1
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
测试方法
//if动态查询
@Test
public void queryBlogIf(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","java如此简单");
map.put("author","幼诗");
List<Blog> blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
choose动态sql
Mapper 配置
<!--choose动态sql;choose相当于java的while判断,如果满足第一个条件就不会继续往下判断,
when 条件
otherwise 当条件都不满足就执行,
-->
<select id="queryBlogChoose" resultType="Blog" parameterType="map">
select * from blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
测试方法
//Choose动态sql查询
@Test
public void queryBlogChoose(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","java如此简单");
map.put("author","幼诗");
map.put("views",9999);
List<Blog> blogs = mapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
set 动态修改字段
Mapper 配置
<!-- set修改数据时候使用,set标签会动态添加前置的SET关键字,同时也会去掉无关的逗号 -->
<update id="updateBlog" parameterType="map">
update blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author},
</if>
</set>
where id = #{id}
</update>
测试方法
//set 动态修改
@Test
public void updateBlog(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","学习如此简单");
map.put("author","maoshi");
map.put("id","1");
int i = mapper.updateBlog(map);
System.out.println();
sqlSession.commit();
sqlSession.close();
}
Foreach 动态sql
Mapper 配置
<!--Foreach 循环动态sql,例如:查询的字段参数值有多个,就可以使用。
collection="idList" 遍历的集合
item="id" 遍历出来的值
open="(" 开始
close=")" 结束
separator="," 分隔,用于分隔每个值
-->
<select id="queryBolgForeach" parameterType="map" resultType="Blog">
select * from blog where id in
<foreach collection="idList" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
测试方法
//Foreach 查询
@Test
public void queryBolgForeach(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
List idList = new ArrayList();
idList.add(1);
idList.add(2);
map.put("idList",idList);
List<Blog> blogs = mapper.queryBolgForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
sql 代码块
<!--sql代码块-->
<sql id="if_title_author">
<if test="title != null">
title = #{title}
</if>
/*引用sql代码块*/
<include refid="if_title_author"></include>
</sql>