什么是动态SQL:
动态SQL就是指根据不同的条件生成不同的SQL语句
环境搭建
CREATE TABLE `blog`(
`id` VARCHAR(50) NOT NULL COMMENT '博客id',
`title` VARCHAR(100) 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
准备工作 :创建一个基础工程
1,导包
2,编写配置文件
3,编写实体类
4,编写mapper接口以及xml文件
一,
if
接口方法
mapper.xml
select * from blog where 1=1
and title = #{title}
and author =#{author}
</select>
if 即为判断条件
在if标签内可添加追加条件
测试方法
@Test
public void selectBlogIfTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","Java如此简单");
List<Blog> blogs = mapper.selectBlogIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
}
查询结果
二,
choose(when,otherwise)
<select id="selectBlogChoose" parameterType="map" resultType="com.briup.pojo.Blog">
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标签类似于java中的swich case语句
当满足第一个条件时,后面的都不执行
三,
trim(where,set)
select * from blog
<where>
<if test="title != null" >
title = #{title}
</if>
<if test="author != null">
and author =#{author}
</if>
</where>
<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>
Foreach遍历
普通SQL语句
select * from user where 1 =1
and (id = 1 or id = 2 or id = 3)
使用Foreach标签
<foreach collection="ids" item="id"
open="(" separator="or" close=")">
#{id}
</foreach>
举例:
查询1,2,3号博客
<select id="selectBlogForeach" parameterType="map" resultType="com.briup.pojo.Blog">
select * from blog
<where>
<foreach collection="ids" item="id"
open="and (" close=")" separator="or"
>
id =#{id}
</foreach>
</where>
</select>
传入一个map,在map中传入一个list集合,这个list集合叫ids,在这个list集合中传入你想要查询的id(1,2,3)
小结:
所谓的动态SQL,本质还是SQL语句,只是我们可以在SQL层面,执行一个逻辑代码if,where,set,choose,when