动态sql
根据不同的条件生成不同的sql语句
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
搭建环境
sql
create table blog(id varchar(30) not null comment '博客id',
title varchar(100) not null comment '博客标题',
author varchar(30) not null comment '博客作者',
create_time datetime not null comment '创建时间',
views int not null comment '浏览量'
)engine=innodb default charset=utf8;
其余(MybatisUtils,mybatis-config.xml)步骤可参考前面的博客进行搭建
实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
}
补充:
-
在utils包中
public class IDUtil { //自动生成id public static String getId(){ return UUID.randomUUID().toString().replace("-", ""); } }
-
mybatis-config.xml
<settings> <!-- 支持改为驼峰 数据库一般用_连接,但在实体类中要用驼峰命名--> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings>
if
传入了 “title” 就按 “title” 查找,传入了 “author” 就按 “author” 查找的情形,当都传入时,都匹配
BlogMapper
public interface BlogMapper {
//查询
List<Blog> queryByIf(Map map);
}
BlogMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wjq.dao.BlogMapper">
<select id="queryByIf" parameterType="map" resultType="blog">
<!--此处的where下面会解决-->
select * from blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
</mapper>
test
public class BlogMapperTest {
@Test
public void queryByIf(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title", "美食");
map.put("author", "aaa");
List<Blog> blogs = mapper.queryByIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
}
choose、when、otherwise
传入了 “title” 就按 “title” 查找,传入了 “author” 就按 “author” 查找的情形,当都传入时,根据choose中的匹配,只能选择一个
先补充: where元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
<select id="queryByChoose" parameterType="map" resultType="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>
test
@Test
public void queryByChoose(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
//只会匹配一个,即title
map.put("views", 2000);
map.put("title", "美食");
map.put("author", "aaa");
List<Blog> blogs = mapper.queryByChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
trim、where、set
set
set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。
<update id="updateBlogBySet" parameterType="map">
update blog
<set>
<if test="author != null">
author = #{author},
</if>
<if test="title != null">
title = #{title}
</if>
</set>
where id = #{id}
</update>
test
@Test
public void updateBlogBySet(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("author", "李四");
map.put("id", "196f87d37afa4f82a253f06ec0ea3d42");
mapper.updateBlogBySet(map);
sqlSession.close();
}
trim
来看看与 set 元素等价的自定义 trim 元素吧:
<!-- 自定义了前缀值 覆盖了后缀值设置,即去掉","-->
<trim prefix="SET" suffixOverrides=",">
...
</trim>
和 where 元素等价的自定义 trim 元素为:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
prefixOverrides 属性会忽略通过管道符分隔的文本序列(注意此例中的空格是必要的)。上述例子会移除所有 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。
sql片段
将一些重复的部分提取出来实现复用
<!--sql片段-->
<sql id="if-title-author">
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
<select id="queryByIf" parameterType="map" resultType="blog">
select * from blog where 1=1
<!--对sql片段的引用-->
<include refid="if-title-author"></include>
</select>
注意事项:
- 最好基于单表来定义sql片段
- 最好不要存在where标签
foreach
BlogMapper
List<Blog> queryByForeach(Map map);
BlogMapper.xml
<!-- select * from blog where id in (1,2,3);-->
<select id="queryByForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<foreach collection="ids" item="id" open="id in (" separator="," close=")">
#{id}
</foreach>
</where>
</select>
test
@Test
public void queryByForeach(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
ArrayList<String> list = new ArrayList();
list.add("1");
list.add("2");
list.add("3");
map.put("ids", list);
List<Blog> blogs = mapper.queryByForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
建议:先写好完整的sql语句,在写动态的sql!
上一节—>复杂查询环境搭建 ( 一对多处理)
如有不对的地方欢迎指出,共同进步!