发布时间:2023-09-14 12:30
1、什么是动态sql:根据不同的条件生成不同的sql语句;
<select id=\"findBlogByMap\" parameterType=\"map\" resultType=\"blog\">
select * from blog where 1=1
<if test=\"title != null and title != \'\'\">
and title = #{title}
</if>
<if test=\"author!= null and author!= \'\'\">
and author = #{author}
</if>
</select>
<select id=\"findBlogByBlog\" parameterType=\"blog\" resultType=\"blog\">
select * from blog where 1=1
<if test=\"title != null and title != \'\'\">
and title like \"%\"#{title}\"%\"
</if>
<if test=\"author!= null and author!= \'\'\">
and author like \"%\"#{author}\"%\"
</if>
</select>
@org.junit.Test
public void findBlogByBlog(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
//blog.setTitle(\"神\");
blog.setAuthor(\"神\");
List<Blog> blogByMap = mapper.findBlogByBlog(blog);
for (Blog blogs : blogByMap) {
System.out.println(blogs);
}
sqlSession.close();
}
3、常用的动态标签:
where标签:
<select id=\"findBlogByBlog\" parameterType=\"blog\" resultType=\"blog\">
select * from blog
<where>
<if test=\"title != null and title != \'\'\">
and title like \"%\"#{title}\"%\"
</if>
<if test=\"author!= null and author!= \'\'\">
and author like \"%\"#{author}\"%\"
</if>
</where>
</select>
set标签:
//更新
int updateBlog(Map map);
<update id=\"updateBlog\" parameterType=\"map\">
update blog
<set>
<if test=\"title != null and title != \'\'\">
title = #{title},
</if>
<if test=\"author != null and author != \'\'\">
author = #{author}
</if>
where id = #{id}
</set>
</update>
trim(where 、set)标签:
如果 where 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 元素的功能;和 where 元素等价的自定义 trim 元素为
...
...
覆盖了后缀值设置,并且自定义了前缀值choose、when、otherise标签:
//优先条件查询 List findBlogByMap2(Map map);
<select id=\"findBlogByMap2\" parameterType=\"map\" resultType=\"blog\">
select * from blog
<where>
<choose>
<when test=\"title != null and title != \'\'\">
title = #{title}
</when>
<when test=\"author != null and author != \'\'\">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
@org.junit.Test
public void findBlogByMap2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
map.put(\"title\", \"神\");
map.put(\"author\", \"狂神说\");
map.put(\"views\",9999);
List<Blog> blogByMap = mapper.findBlogByMap2(map);
for (Blog blog : blogByMap) {
System.out.println(blog);
}
sqlSession.close();
}
SQL片段: 将公共的部分抽取出来,方便公用;
<sql id=\"youwant\">
<if test=\"title != null and title != \'\'\">
title = #{title},
</if>
<if test=\"author != null and author != \'\'\">
and author = #{author}
</if>
</sql>
<select id=\"findBlogByMap\" parameterType=\"map\" resultType=\"blog\">
select * from blog
<where>
<include refid=\"youwant\"/>
</where>
</select>
Foreach标签:对集合遍历;
<select id=\"selectPostIn\" resultType=\"domain.blog.Post\">
SELECT *
FROM POST P
WHERE ID in
<foreach item=\"item\" index=\"index\" collection=\"list\"
open=\"(\" separator=\",\" close=\")\">
#{item}
</foreach>
</select>
案例:
//查询1,2,3号
List<Blog> findBlogByForeach(Map map);
<!--我们现在传递一个万能的map,map中可以存在一个集合
select * from blog where 1=1 and (id=1 or id=2 or id=3)
collection:一个自定义的集合,名为ids
item:遍历出来的每一项,名为id
open = \" 开头字段 (\" close=\")\"
seperator=\" 分隔字段\"
-->
<select id=\"findBlogByForeach\" parameterType=\"map\" resultType=\"blog\">
select * from blog
<where>
<foreach collection=\"ids\" item=\"id\" open=\"and (\" close=\")\" separator=\"or\">
id =#{id}
</foreach>
</where>
</select>
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
ArrayList<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
map.put(\"ids\", ids);
map.put(\"author\", \"狂神说1=1\");
map.put(\"id\",\"27fe9302735f4b21ae1047d45784e578\");
mapper.updateBlog(map);
List<Blog> blogByMap = mapper.findBlogByForeach(map);
for (Blog blog : blogByMap) {
System.out.println(blog);
}
sqlSession.close();