1、if
注意:test里面使用的参数,可以是mybatis的默认参数,也可以是实体属性名,但是不能是没有指定别名的参数名(尤其是单个参数,也必须起别名,否则异常);
单独使用if,如果不满足条件会SQL拼接出问题,一般我门都跟where一起使用;
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('%',?,'%')
@Test
public 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成立,增加的SQL语句,前面多出现一个and或者 or关键字,会被 自动过滤(剔除),但是 末尾出现的,不会被剔除*;
* where标签中,也可以增加固定条件,再实际开发过程中,建议where标签中,必须写固定条件,不能全部写if判断;
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('%',?,'%')
@Test
public 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
*
prefix: 增加前缀固定字符串;
* prefixOverrides: 前缀覆盖(自动剔除指定的关键字);
* suffix: 增加后缀固定字符串;
* suffixOverrides: 后缀覆盖*(自动剔除指定的关键字);
- “and |or” 中间一般都会添加一个空格;
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('%',?,'%') ;
@Test
public 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 update
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
= ?
@Test
public 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 update
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
= ?
@Test
public 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)
test 整体用单引号,里面的 判断条件双引号;
select id
,
cid
,
name
,
author
,
actor
,
produce
,
create_date
from animes
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)
test 整体用双引号,里面的 判断条件单引号;
...
and cid = 1
and cid = 2
and cid = 3
...
List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");
执行SQL:
-- SQL没有报错,但是 cid == 2 的条件没有成立,而是走了默认参数 cid = 3
Preparing: 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)
test 整体用双引号,里面的 判断条件单引号并且判断条件加了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)
总结:
- test 整体用单引号,里面的 判断条件双引号;
- 如果要使用test 整体用双引号,里面的 判断条件单引号,一定要加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)
...
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)
...
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)
...
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)
...
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)
...
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)
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)
...
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)
...
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)
...
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)
...
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)
...
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)
只需要将test 整体用单引号,里面的 判断条件双引号,就可以,加不加.toString(),并不影响;
7、foreach
根据id集合查询动漫集合;
使用 in;
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( ? , ? , ? )
使用 in;
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( ? , ? , ? )
不用where标签;
使用 in;
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( ? , ? , ? )
不用where标签;
使用 in;
通过7.1.3和7.1.4 可以总结,trim 和 foreach 都有前缀,后缀和分隔符,可以根据情况进项选择使用;
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 ( ? , ? , ? )
不用where标签;
使用 or;
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 = ?
@Test
public 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 insert
cid
,
name
,
author
,
actor
,
produce
,
create_date
,
#{cid},
#{name},
#{author},
#{actor},
#{produce},
#{createDate},
执行SQL:
insert into animes
( cid
, name
, author
, actor
, produce
) values ( ?, ?, ?, ?, ? )
@Test
public 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
使用注 释添加动漫;
@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 = ?
@Test
public 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 批量删除
@Delete({"",
"delete from animes
",
"<foreach collection='ids' item='id' open=' where id in ( ' close= ' ) ' separator= ' , '> " ,
" #{id} ",
"</foreach>" ,
""})
int deleteAnimesByConditionUserAnnotationDelete(@Param("ids") List ids);
执行SQL:
Preparing: delete from animes
where id in ( ? , ? , ? )
@Test
public 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、批量加入动漫分类
insert into category
(name
) values
(#{category.name})
执行SQL:
Preparing: insert into category
(name
) values (?) , (?) , (?)
@Test
public 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);
}
12、排序无效问题
在使用MyBatis解析xml进行排序的时候,遇见 排序无效的问题!
-
将传入的 数据都 当成一个字符串,会对自动传入的数据 加一个双引号。
如:order by #{user_id},如果传入的值是111,那么解析成sql时的值为order by “111”, 如果传入的值是id,则解析成的sql为order by “id”。 多加了双引号;
- $将传入的 数据直接显示生成在sql中。如:order by ${user_id},
如果传入的值是111,那么解析成sql时的值为order by 111, 如果传入的值是id,则解析成的sql为order by id。 参数正常;
- 扩展:关于 表名,字段等等的参数, 必须使用$, 不能使用#,要不然会 预编译后,参数 多加了 双引号;
13、前端时间格式不正确问题
通过Mybatis,自动映射出来的时间,获取出来后时间格式不正确:
显示的格式为: Sat Dec 10 00:00:00 CST 1983
想要显示的格式: 1983-12-10
因为MyBatis映射的格式就有问题,所以可以 在get方法中将时间格式化;
public String getBirthday() {
return new SimpleDateFormat("yyyy-MM-dd").format(this.birthday);
}
通过格式化标签库的 进行格式化
Original: https://www.cnblogs.com/xiaoqigui/p/16619854.html
Author: 化羽羽
Title: MyBatis(三)-动态SQL
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/620894/
转载文章受原作者版权保护。转载请注明原作者出处!