12 动态SQL
什么是动态SQL:动态SQL就是根据不同的条件生成不同的SQL语句
所谓的动态SQL,实质上还是SQL语句,只是我们可以在SQL层面,去执行一个逻辑代码
动态SQL是MyBatis的强大特性之一。若果你是用过JDBC或者其他框架,你应该能够理解根据不同的条件拼接SQL语句很是复杂,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态SQL。可以彻底摆脱这种痛苦。
搭建环境
-- 建表
CREATE TABLE `mybatis1`.`blog` (
`id` VARCHAR(50) NOT NULL COMMENT '博客id',
`title` VARCHAR(30) NOT NULL COMMENT '博客标题',
`author` VARCHAR(30) NOT NULL COMMENT '博客作者',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`views` INT(30) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8
创建一个基础工程:
相关代码:
//生成随机数工具类
public class IDUtils {
public static String getId(){
return UUID.randomUUID().toString().replaceAll("-", "");
}
@Test
public void test(){
System.out.println(IDUtils.getId());
}
}
@Data
public class Blog {
private String id;
private String title;
private String author;
//属性名与字段名字不一样,有一个setting属性配置可转换驼峰
private Date createTime;
private int views;
}
public interface BlogMapper {
//插入数据
int addBlogs(Blog blog);
}
@Test
public void addBlog(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IDUtils.getId());
blog.setTitle("Mybatis");
blog.setAuthor("狂神说");
blog.setCreateTime(new Date());
blog.setViews(9999);
mapper.addBlogs(blog);
blog.setId(IDUtils.getId());
blog.setTitle("Java");
mapper.addBlogs(blog);
blog.setId(IDUtils.getId());
blog.setTitle("Spring");
mapper.addBlogs(blog);
blog.setId(IDUtils.getId());
blog.setTitle("微服务");
mapper.addBlogs(blog);
sqlSession.close();
}
<!-- mybatis-config.xml-->
<!-- 开启自动驼峰命名规则映射,当字段名与属性名不一致时-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<mappers>
<mapper class="com.serene.dao.BlogMapper" />
</mappers>
<insert id="addBlogs" parameterType="blog">
insert into mybatis1.blog (id, title, author, create_time, views)
values (#{id}, #{title}, #{author}, #{createTime}, #{views});
</insert>
测试环境搭建完成,数据库数据如下:
12.1 IF where标签
输入参数条件,将满条件的if标签下的sql语句追加到初始sql语句末尾
where标签,若语句开头有or或and,会将其自动去除
<select id="queryBlogsIF" parameterType="map" resultType="blog">
select * from mybatis1.blog
<!-- where标签,若语句开头有or或and,会将其自动去除-->
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
@Test
public void queryBlogsIF(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title", "Java");
map.put("title", "狂神说");
List<Blog> blogList = mapper.queryBlogsIF(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}
12.2 choose-when-otherwise标签
在choose里面选择一个执行,优先级与顺序有关系,类似于case语句
<select id="queryBlogsIF" parameterType="map" resultType="blog">
select * from mybatis1.blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and 1=1
</otherwise>
</choose>
</where>
</select>
@Test
//select * from mybatis1.blog WHERE title = Java
public void queryBlogsIF(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title", "Java");
// map.put("author", "狂神说");
// map.put("views", "9999");
List<Blog> blogList = mapper.queryBlogsIF(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}
12.3 update-set-if
更新数据库数据
<update id="updateBlogs" parameterType="map">
update mybatis1.blog
<set>
<if test="title != null">
title = #{title}, <!-- 注意这里的逗号-->
</if>
<if test="author != null">
author = #{author},
</if>
</set>
where views = #{views}
</update>
@Test
//update mybatis1.blog SET title = Java1 author = 晴朗 where views = 1111
public void updateBlogs(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title", "Java1");
map.put("author", "晴朗");
map.put("views", "1111");
mapper.updateBlogs(map);
sqlSession.close();
}
12.4 SQL片段
有的时候,我们可能会将一些功能的部分抽取出来,方便使用!如上面12.1和12.3个节中xml代码中都有代码
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
我们可以使用下面来实现
<sql id="ti-and-au">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
<update id="updateBlogs" parameterType="map">
update mybatis1.blog
<set>
<include refid="ti-and-au"></include>
</set>
where views = #{views}
</update>
最好基于单表来定于SQL片段,不要存在where标签
12.5 foreach标签
<!--
select * from mybatis1.blog where 1=1 and (views=1 or views=2 or views=3)
万能map传入一个集合
-->
<select id="queryBlogsForeach" parameterType="map" resultType="blog">
select * from mybatis1.blog
<where>
<foreach collection="viewss" item="views" open="and (" close=")" separator="or">
views = #{views}
</foreach>
</where>
</select>
@Test
//select * from mybatis1.blog where 1=1 and (views=1 or views=3)
public void queryBlogsForeach(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
ArrayList<Integer> viewss = new ArrayList<Integer>();
viewss.add(1);
viewss.add(3);
map.put("viewss",viewss);
List<Blog> blogList = mapper.queryBlogsForeach(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}
结果:
动态SQL就是在拼接SQL语句,我们只需要保证SQL的真确性,按照SQL的格式,去排列组合就可以了。
先在Mysql中写出完整的SQL,在对应的去修改成我们的动态SQL实现通用即可