MyBatis基础之动态SQL

科技 08-25 来源： 小柴学Java

1、if

动态标签：判断参数时满足test指定的条件， 如果满足 ，就执行if（ 增加if标签中的SQL语句 ）;

注意：test里面使用的参数，可以是mybatis的默认参数，也可以是实体属性名，但是不能是没有指定别名的参数名(尤其是单个参数，也必须起别名，否则异常);

1.1 SQL

单独使用if，如果不满足条件会SQL拼接出问题，一般我门都跟where一起使用；

执行SQL:

Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where `create_date` < now() and author like concat('%',?,'%')

1.2 测试

@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条件成立时 ， 自动增加where关键字 ，如果 所有的if都不成立 ，也 不会多增加where关键字 ；
  • 当where标签中， if成立，增加的SQL语句 ，前面多出现一个and或者 or关键字，会被 自动过滤 (剔除),但是 末尾出现的，不会被剔除 ；
  • where标签中，也可以增加固定条件，再实际开发过程中，建议where标签中，必须写固定条件，不能全部写if判断；

2.1 SQL

执行SQL:

Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE author like concat('%',?,'%')

2.2 测试

@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

  • + :可以实现动态SQL的定制操作，比如： where标签无法屏蔽末尾 多出来的and或者or关键字，前缀 和后缀增加的内容， 只有标签中的if标签成立 ，( 需要增加条件，才拼接where )；
  • prefix: 增加前缀 固定字符串；
  • prefixOverrides： 前缀覆盖 (自动剔除指定的关键字)；
  • suffix： 增加后缀 固定字符串；
  • suffixOverrides： 后缀覆盖 (自动剔除指定的关键字)；

3.1 SQL

  • "and |or" 中间一般都会添加一个空格；

执行SQL:

Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where author like concat('%',?,'%') ;

3.2 测试

@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

4.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 测试

@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

5.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 测试

@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)

6.1 单引号与双引号的区别

6.1.1 test='cid != null and cid == "1"'

test 整体用单引号 ，里面的 判断条件双引号 ；

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 = 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)

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 SQL

7.1.1 起别名 where + foreach (in)

使用 in;

执行SQL:

Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE id in( ? , ? , ? )

7.1.2 不起别名 where + foreach (in)

使用 in;

执行SQL:

Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE id in( ? , ? , ? )

7.1.3 起别名 foreach (in)

不用where标签;

使用 in;

执行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 都有前缀，后缀和分隔符，可以根据情况进项选择使用；

执行SQL:

Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where id in ( ? , ? , ? )

7.1.5 起别名 foreach （or）

不用where标签;

使用 or;

执行SQL:

Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` where id = ? or id = ? or id = ?

7.2 测试

@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

8.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 测试

@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

使用注释添加动漫；

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 测试

@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 批量删除

10.1 SQL

@Delete({""})
int deleteAnimesByConditionUserAnnotationDelete(@Param("ids") List ids);

执行SQL:

Preparing: delete from `animes` where id in ( ? , ? , ? )

10.2 测试

@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、批量加入动漫分类

11.1 SQL




    insert into `category` (`name`) values
    
    
        (#{category.name})

执行SQL:

Preparing: insert into `category` (`name`) values (?) , (?) , (?)

11.2 测试

@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);

}
  • 支付宝打赏
  • 微信打赏
    • 基础   动态   MyBatis
    发表评论
    留言与评论（共有 0 条评论） “”
       
    验证码：

    相关文章

    央行召开会议，传递出这些信息
    央行召开会议，传递出这些信息
    基础篇-SpringBoot项目创建
    基础篇-SpringBoot项目创建
    注意！ST基础将于9月9日召开股东大会
    注意！ST基础将于9月9日召开股东大会
    理论高地：农村直播电商助力乡村振兴
    理论高地：农村直播电商助力乡村振兴
    优安德米生物科技完成战略投资丨君盛万邦集团投资企业动态
    优安德米生物科技完成战略投资丨君
    一个为虚拟世界构建动态化身的平台，筹集了 5600 万美元
    一个为虚拟世界构建动态化身的平台

    推荐文章

    最热点击文章

    热门标签