摘要:Mybatis笔记_08-2021-06-26
1.认识动态SQL
动态SQL:
-
动态 SQL 是 MyBatis 的强大特性之一。
-
主要标签:if、(choose、when、otherwise)、(trim、where、set)、foreach
-
简而言之,我们编写SQL语句时,可以添加一些条件,当条件成立时那么我们的SQL语句就会自动拼接;
-
例如:
-- 查询blog的所有信息,并且当id != null时,查询id = 1的所有信息 select * from blog; -- 动态SQL select * from blog; <where> <if test="id != null"> id = 1</if> </where> -- 条件成立,SQL语句就变成了 select * from blog where id = 1; -- 条件不成立,SQL语句仍保持不变 select * from blog;
2.if
BlogMapperDao.java
/**
* @Description 学习使用if构建动态SQL语句
* @Param [map]
* @return java.util.List<com.riove.pojo.Blog>
*/
List<Blog> getBlogListByIf(Map<String,Object> map);
BlogMapper.xml
<!--知识检缀:#{}和${}的区别和联系,在mybatis中使用#{}-->
<!--动态SQL语句,if的使用-->
<select id="getBlogListByIf" parameterType="map" resultType="blog">
select * from blog where views like "%"#{views}"%"
<if test="title != null">
and title like "%"#{title}"%"
</if>
<if test="author != null">
and author like "%"#{author}"%"
</if>
</select>
BlogMapperDaoTest
/**
* @Description 测试方法:getBlogListByIf
*/
@Test
public void getBlogListByIfTest() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapperDao mapper = sqlSession.getMapper(BlogMapperDao.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("views","5");
map.put("title","新新");
map.put("author","吴");
List<Blog> blogList = mapper.getBlogListByIf(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}
3.choose、when、otherwise
BlogMapperDao.java
/**
* @Description 通过choose、when、otherwise,可以过滤我们不需要执行的语句,只要条件成立,SQL会自动拼接语句
* @Param []
* @return java.util.List<com.riove.pojo.Blog>
*/
List<Blog> getBlogListByChoose(Map<String,Object> map);
BlogMapper.xml
<!--通过choose、when、otherwise,可以过滤我们不需要执行的语句,只要该条件成立,只执行该语句
*区别:if动态SQL,只要条件成立就会执行;
choose、when、therwise动态SQL只要有一个条件成立,那么SQL就只会执行该语句,不会再继续执行后面的语句,相当于Switch case条件;
-->
<select id="getBlogListByChoose" resultType="blog">
select * from blog
<where>
<choose>
<when test="title != null">
title like "%"#{title}"%"
</when>
<when test="title != null">
and author like "%"#{author}"%"
</when>
<otherwise>
and 1 = 1;
</otherwise>
</choose>
</where>
</select>
BlogMapperDaoTest
/**
* @Description 测试方法:getBlogListByChoose
*/
@Test
public void getBlogListByChooseTest() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapperDao mapper = sqlSession.getMapper(BlogMapperDao.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("title","大");
map.put("author","西");
List<Blog> blogList = mapper.getBlogListByChoose(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}
4.trim、where、set
BlogMapperDao.java
/**
* @Description 学习使用trim/where/set/if构建动态SQL语句
* @Param [map]
* @return java.util.List<com.riove.pojo.Blog>
*/
List<Blog> getBlogListByWhere(Map<String,Object> map);
/**
* @Description 学习使用set构建动态SQL语句,并且通过id更新数据
* @Param [map]
* @return void
*/
void updateBlogBySet(Map<String,Object> map);
/**
* @Description 通过trim可定制标签构建动态SQL,实现更新
* @Param [map]
* @return void
*/
void updateBlogByTrim(Map<String,Object> map);
BlogMapper.xml
<!--/*
*if标签:test为判定条件,前面的if中test条件不成立,那么就会继续执行下一个if标签
*注意语句:view为静态SQL;title、author为动态SQL
*假如将view也变成动态SQL时,test条件均不成立,此时where关键词扔保留在SQL语句中,会造成SQL异常;
*因此,接下来就需要引入新的动态SQL方法:where标签
*where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
*/-->
<select id="getBlogListByWhere" resultType="blog">
select * from blog
<where>
<if test="views != null">
views like "%"#{views}"%"
</if>
<if test="title != null">
and title like "%"#{title}"%"
</if>
<if test="author != null">
and author like "%"#{author}"%"
</if>
</where>
</select>
<!--学习使用set构建动态SQL语句
注意:set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。
-->
<update id="updateBlogBySet">
update blog
<set>
<if test="title != null">title = #{title},</if>
<if test="title != null">author = #{author}</if>
</set>
where id = #{id};
</update>
<!--学习使用trim构建动态SQL语句
注意:trim,定制动态SQL语句
prefixOverrides,填写需要忽略分隔符,属会移除所有 prefixOverrides 属性中指定的内容,比如:多条件where中分隔符"and""or",更新多条语句","
prefix 属性中指定的内容:set/where,根据情况而定,比如:更新是用set,多条件查询是用where
-->
<update id="updateBlogByTrim">
update blog
<trim prefix="set" prefixOverrides=",">
<if test="title != null">title = #{title},</if>
<if test="author != null">author = #{author}</if>
</trim>
where id = #{id};
</update>
BlogMapperDaoTest
/**
* @Description 测试方法:getBlogListByWhere
*/
@Test
public void getBlogListByWhereTest() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapperDao mapper = sqlSession.getMapper(BlogMapperDao.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("views","5");
map.put("title","新新");
map.put("author","吴");
List<Blog> blogList = mapper.getBlogListByWhere(map);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}
/**
* @Description 测试方法:updateBlogById
*/
@Test
public void updateBlogBySetTest() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapperDao mapper = sqlSession.getMapper(BlogMapperDao.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("id","5");
map.put("title","大话西游");
map.put("author","齐天大圣");
mapper.updateBlogBySet(map);
sqlSession.commit();
sqlSession.close();
}
/**
* @Description 测试方法:updateBlogByTrim
*/
@Test
public void updateBlogByTrimTest() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapperDao mapper = sqlSession.getMapper(BlogMapperDao.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("id","1");
map.put("title","大话西游");
map.put("author","齐天大圣");
mapper.updateBlogByTrim(map);
sqlSession.commit();
sqlSession.close();
}
5.foreach
BlogMapperDao.java
/**
* @Description 通过foreach标签构建动态SQL
* @Param [map]
* @return java.util.List<com.riove.pojo.Blog>
*/
List<Blog> getBlogListByForeach(List<Integer> list);
BlogMapper.xml
<!--学习使用foreach构建动态SQL
1)需求:根据ids(多个id)查询用户信息。比如查询id为1/3/5/7/9这五个id的用户信息。
2)sql:select * from blog where id in(1,3,5,7,9);
3)传参方式:定义接口时,参数类型为什么,就通过该类性进行传值,这里以list为例
①Array(常用):Integer[] ids = new Integer[]{1,3,5,7,9};
②List(常用):List<Integer> list = new ArrayList<Integer>();
list.add(1);
...
③Map:通过数组Array或者集合List实现
④Bean:通过对象类型进行传入
4)foreach属性:
collection:一般为list|array|map
index:
Array中为:数组下标;
list中为:集合序号;
map中为:键Key;
item:为传入的参数值,#{}必须与item属性值保持一致:item="ids" #{ids},ids为自定义名;
-->
<select id="getBlogListByForeach" parameterType="map" resultType="blog">
select * from blog
<where>
id in
<foreach collection="list" index="index" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</where>
</select>
BlogMapperDaoTest
/**
* @Description 测试方法:getBlogListByForeach
*/
public void getBlogListByForeachTest2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapperDao mapper = sqlSession.getMapper(BlogMapperDao.class);
List<Integer> list = new ArrayList<Integer>();
list.add(1);
list.add(3);
list.add(5);
list.add(7);
list.add(9);
List<Blog> blogList = mapper.getBlogListByForeach(list);
for (Blog blog : blogList) {
System.out.println(blog);
}
sqlSession.close();
}