1. Johngo学长首页
  2. 技术点详细复盘专题
  3. 其他编程语言
  4. Java

MyBatis(三)-动态SQL

• Java • 阅读 74

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},'%')

&#x6267;&#x884C;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},'%')

&#x6267;&#x884C;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

&#x6267;&#x884C;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}

&#x6267;&#x884C;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}

&#x6267;&#x884C;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");

&#x6267;&#x884C;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");

&#x6267;&#x884C;SQL&#xFF1A;

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

&#x6267;&#x884C;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)

&#x603B;&#x7ED3;&#xFF1A;

  • test 整体用单引号，里面的 判断条件双引号；
  • 如果要使用test 整体用双引号，里面的 判断条件单引号，一定要加toString()；
...

        and cid = 1

        and cid = 2

        and cid = 3

...


List animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");

&#x6267;&#x884C;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");

&#x6267;&#x884C;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");

&#x6267;&#x884C;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");

&#x6267;&#x884C;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");

&#x6267;&#x884C;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");

&#x6267;&#x884C;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");

&#x6267;&#x884C;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");

&#x6267;&#x884C;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");

&#x6267;&#x884C;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");

&#x6267;&#x884C;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");

&#x6267;&#x884C;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");

&#x6267;&#x884C;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}

&#x6267;&#x884C;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}

&#x6267;&#x884C;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}

&#x6267;&#x884C;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}

&#x6267;&#x884C;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}

&#x6267;&#x884C;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},

&#x6267;&#x884C;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);

&#x6267;&#x884C;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);

&#x6267;&#x884C;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})

&#x6267;&#x884C;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/616154/

转载文章受原作者版权保护。转载请注明原作者出处！

赞 (0)
0

【自取】最近整理的，有需要可以领取学习：



Linux核心资料大放送~

全栈面试题汇总（持续更新&可下载）

一个提高学习100%效率的工具！

【超详细】深度学习面试题目！

LeetCode Python刷题答案下载！

LeetCode Java版刷题答案下载！

LeetCode C++ 版本，抓紧保存！

LeetCode GO语言 刷题答案下载！

大家都在看

亲爱的 Coder【最近整理，可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球