1、if
动态标签：判断参数时满足test指定的条件，如果满足，就执行if（增加if标签中的SQL语句）;
注意：test里面使用的参数，可以是mybatis的默认参数，也可以是实体属性名，但是不能是没有指定别名的参数名(尤其是单个参数，也必须起别名，否则异常);
1.1 SQL
单独使用if，如果不满足条件会SQL拼接出问题，一般我门都跟where一起使用；
<!-- List selectAnimesByConditionUserIf(@Param("cid") Integer cid,@Param("author") String author); --> select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where `create_date` < now() cid = #{cid} and author like concat('%',#{author},'%') 执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where `create_date` < now() and author like concat('%',?,'%')1.2 测试
@Testpublic void testMybatisMapperDynamicSQlUserIf() throws IOException { SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true); //获取mapper接口的代理实现类对象 AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class); //执行动态SQL，查询动漫列表 List animeList = animeMapper.selectAnimesByConditionUserIf(0, "土豆"); animeList.forEach(System.out::println);}2、where + if
动态标签组合：当where标签中，有if条件成立时，自动增加where关键字，如果所有的if都不成立，也不会多增加where关键字；
当where标签中，if成立，增加的SQL语句，前面多出现一个and或者 or关键字，会被自动过滤(剔除),但是末尾出现的，不会被剔除；
where标签中，也可以增加固定条件，再实际开发过程中，建议where标签中，必须写固定条件，不能全部写if判断；
2.1 SQL
<!-- List selectAnimesByConditionUserIfWhere(@Param("cid") Integer cid,@Param("author") String author); --> select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` and cid = #{cid} and author like concat('%',#{author},'%') 执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE author like concat('%',?,'%')2.2 测试
@Testpublic void testMybatisMapperDynamicSQlUserIfWhere() throws IOException { SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true); //获取mapper接口的代理实现类对象 AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class); //执行动态SQL，查询动漫列表 List animeList = animeMapper.selectAnimesByConditionUserIfWhere(0, "土豆"); animeList.forEach(System.out::println); }3、trim + if
- + :可以实现动态SQL的定制操作，比如：where标签无法屏蔽末尾多出来的and或者or关键字，前缀 和后缀增加的内容，只有标签中的if标签成立，(需要增加条件，才拼接where)；
- prefix:增加前缀固定字符串；
- prefixOverrides：前缀覆盖(自动剔除指定的关键字)；
- suffix：增加后缀固定字符串；
- suffixOverrides：后缀覆盖(自动剔除指定的关键字)；
3.1 SQL
- “and |or” 中间一般都会添加一个空格；
<!-- List selectAnimesByConditionUserIfTrim(@Param("cid") Integer cid,@Param("author") String author); --> select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` cid = #{cid} and author like concat('%',#{author},'%') and 执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where author like concat('%',?,'%') ;3.2 测试
@Testpublic void testMybatisMapperDynamicSQlUserIfTerm() throws IOException { SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true); //获取mapper接口的代理实现类对象 AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class); //执行动态SQL，查询动漫列表 List animeList = animeMapper.selectAnimesByConditionUserIfTrim(0, "土豆"); animeList.forEach(System.out::println);}4、set + if update4.1SQL
update `animes` `cid` = #{cid}, `name` = #{name}, `author` = #{author}, `actor` = #{actor}, `produce` = #{produce}, `create_date` = #{createDate}, where `id` = #{id}执行SQL:
Preparing: update `animes` SET `name` = ?, `author` = ? where `id` = ?4.2 测试
@Testpublic void testMybatisMapperDynamicSQlIfSetUpd() throws IOException { SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true); //获取mapper接口的代理实现类对象 AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class); //模拟前端提供更新参数，实现动态更新，给说明值，就更新什么指端 Anime animeForm = new Anime(); animeForm.setId(637); animeForm.setName("武动乾坤KGC"); animeForm.setAuthor("土豆KGC"); int row = animeMapper.updateAnimeByConditionUserIfSet(animeForm); System.out.println(row);}5、trim + if update5.1 SQL
`cid` = #{cid}, `name` = #{name}, `author` = #{author}, `actor` = #{actor}, `produce` = #{produce}, `create_date` = #{createDate}, where `id` = #{id}执行SQL:
Preparing: update `animes` set `name` = ?, `author` = ? where `id` = ?5.2 测试
@Testpublic void testMybatisMapperDynamicSQlIfTrimUpd() throws IOException { SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true); //获取mapper接口的代理实现类对象 AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class); //模拟前端提供更新参数，实现动态更新，给说明值，就更新什么指端 Anime animeForm = new Anime(); animeForm.setId(637); animeForm.setName("武动乾坤22KGC"); animeForm.setAuthor("土豆22KGC"); int row = animeMapper.updateAnimeByConditionUserIfTrim(animeForm); System.out.println(row);}6、where + choose + when (判断条件测试)
这个场景主要在传过来的参数，与放进SQL中的参数不一致的时候使用；
比如，前端传过来男/女,但是数据库中查询的时候需要使用1/2;(当然参数也可以在前端或者业务层处理好再放进SQL)
6.1 单引号与双引号的区别6.1.1 test=’cid != null and cid == “1”‘
test整体用单引号，里面的判断条件双引号；
<!--List selectAnimesByConditionUserChooseWhenOtherwise(@Param("cid") String cid); --> select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` and cid = 1 and cid = 2 and cid = 3 6.1.1 测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)6.1.2 test=”cid != null and cid == ‘1’”
test整体用双引号，里面的判断条件单引号；
... and cid = 1 and cid = 2 and cid = 3 ...6.1.2 测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");执行SQL：
-- SQL没有报错，但是 cid == 2 的条件没有成立，而是走了默认参数 cid = 3Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 3-- 可以查到数据，但是数据不正确，是cid=3的数据Anime(id=301, cid=3, name=完美世界, author=辰东, actor=石昊, produce=玄机科技, createDate=Tue Apr 05 00:00:00 CST 2022)6.1.3 单引号和双引号总结
总结：test整体用单引号，里面的判断条件双引号；
6.2 == 和 eq 的区别6.2.1 ==
... and cid = 1 and cid = 2 and cid = 3 ...6.2.1 测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)6.2.2 eq
... and cid = 1 and cid = 2 and cid = 3 ...6.2.2 测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)6.3 “str” 和 “str”.toString() 的区别6.3.1 “2” 和 “2”toString()”2″
... and cid = 1 and cid = 2 and cid = 3 ...测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)“2”.toString()
... and cid = 1 and cid = 2 and cid = 3 ...测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)6.3.2 “B” 和 “B”.toString() 的区别”B”
... and cid = 1 and cid = 2 and cid = 3 ...测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("B");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)“B”.toString()
... and cid = 1 and cid = 2 and cid = 3 ...测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("B");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)6.3.3 “22” 和 “22”.toString()”22″
and cid = 1 and cid = 2 and cid = 3 测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("22");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)“22”.toString()
... and cid = 1 and cid = 2 and cid = 3 ...测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("22");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)6.3.4 “BB” 和 “BB”.toString()”BB”
... and cid = 1 and cid = 2 and cid = 3 ...测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("BB");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)“BB”.toString()
... and cid = 1 and cid = 2 and cid = 3 ...测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("BB");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)6.3.5 “任意字符2” 和 “任意字符2”.toString()”任意字符2″
... and cid = 1 and cid = 2 and cid = 3 ...测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("任意字符2");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)“任意字符2”.toString()
... and cid = 1 and cid = 2 and cid = 3 ...测试
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("任意字符2");执行SQL:
-- SQL正常Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2-- 可以查到正确数据Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)6.4 总结
只需要将test整体用单引号，里面的判断条件双引号，就可以，加不加.toString(),并不影响；
7、foreach
根据id集合查询动漫集合；
7.1 SQL7.1.1 起别名 where + foreach (in)
使用 in;
<!--List selectAnimesByConditionUserForeach(@Param("ids") List ids);--> select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` #{id} 执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE id in( ? , ? , ? )7.1.2 不起别名 where + foreach (in)
使用 in;
<!-- List selectAnimesByConditionUserForeach( List ids); --> select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` #{id} 执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE id in( ? , ? , ? )7.1.3 起别名 foreach (in)
不用where标签;
使用 in;
<!--List selectAnimesByConditionUserForeach(@Param("ids") List ids);--> select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` #{id} 执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where id in( ? , ? , ? )7.1.4 起别名 trim + foreach （in）
不用where标签;
使用 in;
通过7.1.3和7.1.4 可以总结，trim 和 foreach 都有前缀，后缀和分隔符，可以根据情况进项选择使用；
<!--List selectAnimesByConditionUserForeach(@Param("ids") List ids);--> select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` #{id} 执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where id in ( ? , ? , ? )7.1.5 起别名 foreach （or）
不用where标签;
使用 or;
<!--List selectAnimesByConditionUserForeach(@Param("ids") List ids);--> select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` id = #{id} 执行SQL:
Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where id = ? or id = ? or id = ?7.2 测试
@Testpublic void testMybatisMapperDynamicSQlUserForeach() throws IOException { SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true); //获取mapper接口的代理实现类对象 AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class); //执行动态SQL，查询动漫列表 List animeList = animeMapper.selectAnimesByConditionUserForeach(Arrays.asList(101,102,103)); animeList.forEach(System.out::println);}8、trim + if insert8.1 SQL
`cid`, `name`, `author`, `actor`, `produce`, `create_date`, #{cid}, #{name}, #{author}, #{actor}, #{produce}, #{createDate}, 执行SQL:
insert into `animes` ( `cid`, `name`, `author`, `actor`, `produce` ) values ( ?, ?, ?, ?, ? )8.2 测试
@Testpublic void testMybatisMapperDynamicSQlIfTrimInsert() throws IOException { SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true); //获取mapper接口的代理实现类对象 AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class); Anime animeForm = new Anime(); animeForm.setCid(1); animeForm.setName("知否知否"); animeForm.setAuthor("关心则乱"); animeForm.setActor("明兰"); animeForm.setProduce("优酷"); //指定if+ trim 冬天SQL,新增动漫 int row = animeMapper.insertAnimeByConditionIfTrim(animeForm); System.out.println(row);}9、@ Select
使用注释添加动漫；
9.1 SQL
@Select("select `id`,`cid`,`name`,`author`,`actor`,`produce`,`create_date` from `animes` where id = #{id} ")Anime selectAnimesByConditionUserAnnotationSelect(Integer id);执行SQL:
Preparing: select `id`,`cid`,`name`,`author`,`actor`,`produce`,`create_date` from `animes` where id = ?9.2 测试
@Testpublic void testAnimesByConditionUserAnnotationSelect() throws IOException { SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true); //获取mapper接口的代理实现类对象 AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class); Anime anime = animeMapper.selectAnimesByConditionUserAnnotationSelect(653); System.out.println(anime);}10、@Delete 批量删除10.1 SQL
@Delete({"", "delete from `animes`", " " , " #{id} ", "" , ""})int deleteAnimesByConditionUserAnnotationDelete(@Param("ids") List ids);执行SQL:
Preparing: delete from `animes` where id in ( ? , ? , ? )10.2 测试
@Testpublic void testDeleteAnimesByConditionUserAnnotationDelete() throws IOException { SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true); //获取mapper接口的代理实现类对象 AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class); int row = animeMapper.deleteAnimesByConditionUserAnnotationDelete(Arrays.asList(649, 651, 652)); System.out.println(row);}11、批量加入动漫分类11.1 SQL
<!-- int insertCategoryBatchUserFoeEach(List categoryList) --><!-- int insertCategoryBatchUserFoeEach(@Param("categoryList") List categoryList); --> insert into `category` (`name`) values (#{category.name}) 执行SQL:
Preparing: insert into `category` (`name`) values (?) , (?) , (?)11.2 测试
@Testpublic void testInsertCategoryBatchUserFoeEach() throws IOException { SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true); //获取mapper接口的代理实现类对象 AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class); Category category1 = new Category(); Category category2 = new Category(); Category category3 = new Category(); category1.setName("aaa"); category2.setName("bbb"); category3.setName("ccc"); List categoryList = new ArrayList(); categoryList.add(category1); categoryList.add(category2); categoryList.add(category3); int row = animeMapper.insertCategoryBatchUserFoeEach(categoryList); System.out.println(row);}