ToB企服应用市场:ToB评测及商务社交产业平台

标题: MyBatis(三)-动态SQL [打印本页]

---

作者: 杀鸡焉用牛刀    时间: 2022-9-16 17:20
标题: MyBatis(三)-动态SQL
1、if

动态标签：判断参数时满足test指定的条件，如果满足，就执行if（增加if标签中的SQL语句）;
注意：test里面使用的参数，可以是mybatis的默认参数，也可以是实体属性名，但是不能是没有指定别名的参数名(尤其是单个参数，也必须起别名，否则异常);
1.1 SQL

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

1. <select id="selectAnimesByConditionUserIf" resultType="com.kgc.mybatis.bean.Anime">
2.     select  `id`,
3.     `cid`,
4.     `name`,
5.     `author`,
6.     `actor`,
7.     `produce`,
8.     `create_date`
9.     from `animes`
10.     where `create_date` < now()
11.     <if test="cid != null and cid != 0 ">
12.         cid = #{cid}
13.     </if>
14.     <if test="author != null">
15.         and author like concat('%',#{author},'%')
16.     </if>
17. </select>

复制代码

执行SQL:

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

复制代码

1.2 测试

1. @Test
2. public void testMybatisMapperDynamicSQlUserIf() throws IOException {
3.     SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
5.     //获取mapper接口的代理实现类对象
6.     AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
8.     //执行动态SQL，查询动漫列表
9.     List<Anime> animeList = animeMapper.selectAnimesByConditionUserIf(0, "土豆");
11.     animeList.forEach(System.out::println);
12. }

复制代码

2、where + if

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

2.1 SQL

1. <select id="selectAnimesByConditionUserIfWhere" resultType="com.kgc.mybatis.bean.Anime">
2.     select  `id`,
3.     `cid`,
4.     `name`,
5.     `author`,
6.     `actor`,
7.     `produce`,
8.     `create_date`
9.     from `animes`
10.     <where>
11.         <if test="cid != null and cid != 0 ">
12.             and cid = #{cid}
13.         </if>
14.         <if test="author != null">
15.             and author like concat('%',#{author},'%')
16.         </if>
17.     </where>
18. </select>

复制代码

执行SQL:

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

复制代码

2.2 测试

1. @Test
2. public void testMybatisMapperDynamicSQlUserIfWhere() throws IOException {
3.     SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
5.     //获取mapper接口的代理实现类对象
6.     AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
8.     //执行动态SQL，查询动漫列表
9.     List<Anime> animeList = animeMapper.selectAnimesByConditionUserIfWhere(0, "土豆");
11.     animeList.forEach(System.out::println);
12.    
13. }

复制代码

3、trim + if

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

3.1 SQL

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

1. <select id="selectAnimesByConditionUserIfTrim" resultType="com.kgc.mybatis.bean.Anime">
2.     select  `id`,
3.     `cid`,
4.     `name`,
5.     `author`,
6.     `actor`,
7.     `produce`,
8.     `create_date`
9.     from `animes`
10.     <trim prefix=" where "  prefixOverrides="and |or" suffixOverrides="and |or" suffix=";">
11.         <if test="cid != null and cid != 0 ">
12.             cid = #{cid} and
13.         </if>
14.         <if test="author != null">
15.             author like concat('%',#{author},'%') and
16.         </if>
17.     </trim>
18. </select>

复制代码

执行SQL:

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

复制代码

3.2 测试

1. @Test
2. public void testMybatisMapperDynamicSQlUserIfTerm() throws IOException {
3.     SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
5.     //获取mapper接口的代理实现类对象
6.     AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
8.     //执行动态SQL，查询动漫列表
9.     List<Anime> animeList = animeMapper.selectAnimesByConditionUserIfTrim(0, "土豆");
11.     animeList.forEach(System.out::println);
13. }

复制代码

4、set + if update

4.1SQL

1. <update id="updateAnimeByConditionUserIfSet">
2.     update `animes`
3.     <set>
4.         <if test="cid != null"> `cid` = #{cid},</if>
5.         <if test="name != null"> `name` = #{name},</if>
6.         <if test="author != null"> `author` = #{author},</if>
7.         <if test="actor != null"> `actor` = #{actor},</if>
8.         <if test="produce != null"> `produce` = #{produce},</if>
9.         <if test="createDate != null"> `create_date` = #{createDate},</if>
10.     </set>
11.     where `id` = #{id}
12. </update>

复制代码

执行SQL:

1. Preparing: update `animes` SET `name` = ?, `author` = ? where `id` = ?

复制代码

4.2 测试

1. @Test
2. public  void testMybatisMapperDynamicSQlIfSetUpd() throws IOException {
3.     SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
5.     //获取mapper接口的代理实现类对象
6.     AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
8.     //模拟前端提供更新参数，实现动态更新，给说明值，就更新什么指端
9.     Anime animeForm = new Anime();
10.     animeForm.setId(637);
11.     animeForm.setName("武动乾坤KGC");
12.     animeForm.setAuthor("土豆KGC");
14.     int row = animeMapper.updateAnimeByConditionUserIfSet(animeForm);
16.     System.out.println(row);
18. }

复制代码

5、trim + if update

5.1 SQL

1. <update id="updateAnimeByConditionUserIfTrim">
2.     <trim prefix="update `animes` set " prefixOverrides="," suffixOverrides=",">
3.         <if test="cid != null"> `cid` = #{cid},</if>
4.         <if test="name != null"> `name` = #{name},</if>
5.         <if test="author != null"> `author` = #{author},</if>
6.         <if test="actor != null"> `actor` = #{actor},</if>
7.         <if test="produce != null"> `produce` = #{produce},</if>
8.         <if test="createDate != null"> `create_date` = #{createDate},</if>
9.     </trim>
10.     where `id` = #{id}
11. </update>

复制代码

执行SQL:

1. Preparing: update `animes` set `name` = ?, `author` = ? where `id` = ?

复制代码

5.2 测试

1. @Test
2. public  void testMybatisMapperDynamicSQlIfTrimUpd() throws IOException {
3.     SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
5.     //获取mapper接口的代理实现类对象
6.     AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
8.     //模拟前端提供更新参数，实现动态更新，给说明值，就更新什么指端
9.     Anime animeForm = new Anime();
10.     animeForm.setId(637);
11.     animeForm.setName("武动乾坤22KGC");
12.     animeForm.setAuthor("土豆22KGC");
14.     int row = animeMapper.updateAnimeByConditionUserIfTrim(animeForm);
16.     System.out.println(row);
18. }

复制代码

6、where + choose + when (判断条件测试)

这个场景主要在传过来的参数，与放进SQL中的参数不一致的时候使用；
比如，前端传过来男/女,但是数据库中查询的时候需要使用1/2;(当然参数也可以在前端或者业务层处理好再放进SQL)
6.1 单引号与双引号的区别

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

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

1. <select id="selectAnimesByConditionUserChooseWhenOtherwise" resultType="com.kgc.mybatis.bean.Anime">
2.     select  `id`,
3.     `cid`,
4.     `name`,
5.     `author`,
6.     `actor`,
7.     `produce`,
8.     `create_date`
9.     from `animes`
10.     <where>
11.         <choose>
12.             
13.             <when test='cid != null  and cid == "1"'>
14.                 and cid = 1
15.             </when>
16.             <when test='cid != null  and cid == "2"'>
17.                 and cid = 2
18.             </when>
19.             <otherwise>
20.                 and cid = 3
21.             </otherwise>
22.         </choose>
23.     </where>
24. </select>

复制代码

6.1.1 测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. 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整体用双引号，里面的判断条件单引号；

1. ...
2. <choose>
3.    
4.     <when test="cid != null  and cid == '1'">
5.         and cid = 1
6.     </when>
7.     <when test="cid != null  and cid == '2'">
8.         and cid = 2
9.     </when>
10.     <otherwise>
11.         and cid = 3
12.     </otherwise>
13. </choose>
14. ...

复制代码

6.1.2 测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");

复制代码

执行SQL：

1. -- SQL没有报错，但是 cid == 2 的条件没有成立，而是走了默认参数 cid = 3
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 3
4. -- 可以查到数据，但是数据不正确，是cid=3的数据
5. 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 ==

1. ...
2. <choose>
3.     <when test='cid != null  and cid == "1"'>
4.         and cid = 1
5.     </when>
6.     <when test='cid != null  and cid == "2"'>
7.         and cid = 2
8.     </when>
9.     <otherwise>
10.         and cid = 3
11.     </otherwise>
12. </choose>
13. ...

复制代码

6.2.1 测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)

复制代码

6.2.2 eq

1. ...
2. <choose>
3.     <when test='cid != null  and cid == "1"'>
4.         and cid = 1
5.     </when>
6.     <when test='cid != null  and cid == "2"'>
7.         and cid = 2
8.     </when>
9.     <otherwise>
10.         and cid = 3
11.     </otherwise>
12. </choose>
13. ...

复制代码

6.2.2 测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. 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"

1. ...
2. <choose>
3.     <when test='cid != null  and cid == "1"'>
4.         and cid = 1
5.     </when>
6.     <when test='cid != null  and cid == "2"'>
7.         and cid = 2
8.     </when>
9.     <otherwise>
10.         and cid = 3
11.     </otherwise>
12. </choose>
13. ...

复制代码

测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)

复制代码

"2".toString()

1. ...
2. <choose>
3.      <when test='cid != null  and cid eq "1".toString()'>
4.          and cid = 1
5.      </when>
6.      <when test='cid != null  and cid eq "2".toString()'>
7.          and cid = 2
8.      </when>
9.      <otherwise>
10.          and cid = 3
11.      </otherwise>
12. </choose>
13. ...

复制代码

测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("2");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. 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"

1. ...
2. <choose>
3.     <when test='cid != null  and cid == "1"'>
4.         and cid = 1
5.     </when>
6.     <when test='cid != null  and cid == "2"'>
7.         and cid = 2
8.     </when>
9.     <otherwise>
10.         and cid = 3
11.     </otherwise>
12. </choose>
13. ...

复制代码

测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("B");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)

复制代码

"B".toString()

1. ...
2. <choose>
3.     <when test='cid != null  and cid == "1"'>
4.         and cid = 1
5.     </when>
6.     <when test='cid != null  and cid == "2"'>
7.         and cid = 2
8.     </when>
9.     <otherwise>
10.         and cid = 3
11.     </otherwise>
12. </choose>
13. ...

复制代码

测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("B");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. 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"

1. <choose>
2.     <when test='cid != null  and cid eq "11"'>
3.         and cid = 1
4.     </when>
5.     <when test='cid != null  and cid eq "22"'>
6.         and cid = 2
7.     </when>
8.     <otherwise>
9.         and cid = 3
10.     </otherwise>
11. </choose>

复制代码

测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("22");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)

复制代码

"22".toString()

1. ...
2. <choose>
3.     <when test='cid != null  and cid == "1"'>
4.         and cid = 1
5.     </when>
6.     <when test='cid != null  and cid == "2"'>
7.         and cid = 2
8.     </when>
9.     <otherwise>
10.         and cid = 3
11.     </otherwise>
12. </choose>
13. ...

复制代码

测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("22");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. 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"

1. ...
2. <choose>
3.     <when test='cid != null  and cid == "1"'>
4.         and cid = 1
5.     </when>
6.     <when test='cid != null  and cid == "2"'>
7.         and cid = 2
8.     </when>
9.     <otherwise>
10.         and cid = 3
11.     </otherwise>
12. </choose>
13. ...

复制代码

测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("BB");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)

复制代码

"BB".toString()

1. ...
2. <choose>
3.     <when test='cid != null  and cid == "1"'>
4.         and cid = 1
5.     </when>
6.     <when test='cid != null  and cid == "2"'>
7.         and cid = 2
8.     </when>
9.     <otherwise>
10.         and cid = 3
11.     </otherwise>
12. </choose>
13. ...

复制代码

测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("BB");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. 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"

1. ...
2. <choose>
3.     <when test='cid != null  and cid == "1"'>
4.         and cid = 1
5.     </when>
6.     <when test='cid != null  and cid == "2"'>
7.         and cid = 2
8.     </when>
9.     <otherwise>
10.         and cid = 3
11.     </otherwise>
12. </choose>
13. ...

复制代码

测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("任意字符2");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. Anime(id=627, cid=2, name=魁拔3, author=青春树, actor=蛮吉, produce=青春树, createDate=Tue Jan 07 00:00:00 CST 2003)

复制代码

"任意字符2".toString()

1. ...
2. <choose>
3.     <when test='cid != null  and cid == "1"'>
4.         and cid = 1
5.     </when>
6.     <when test='cid != null  and cid == "2"'>
7.         and cid = 2
8.     </when>
9.     <otherwise>
10.         and cid = 3
11.     </otherwise>
12. </choose>
13. ...

复制代码

测试

1. List<Anime> animeList = animeMapper.selectAnimesByConditionUserChooseWhenOtherwise("任意字符2");

复制代码

执行SQL:

1. -- SQL正常
2. Preparing: select `id`, `cid`, `name`, `author`, `actor`, `produce`, `create_date` from `animes` WHERE cid = 2
4. -- 可以查到正确数据
5. Anime(id=201, cid=2, name=海贼王, author=尾田, actor=路飞, produce=腾讯动漫, createDate=Tue Apr 05 00:00:00 CST 2022)
6. 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;

1. <select id="selectAnimesByConditionUserForeach" resultType="com.kgc.mybatis.bean.Anime">
2.     select  `id`,
3.             `cid`,
4.             `name`,
5.             `author`,
6.             `actor`,
7.             `produce`,
8.             `create_date`
9.     from `animes`
10.         <where>
11.         <foreach collection="ids" item="id" open="id in(" close=" )" separator=", ">
12.             #{id}
13.         </foreach>
14.         </where>
15. </select>

复制代码

执行SQL:

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

复制代码

7.1.2 不起别名 where + foreach  (in)

使用 in;

1. <select id="selectAnimesByConditionUserForeach" resultType="com.kgc.mybatis.bean.Anime">
2.     select  `id`,
3.             `cid`,
4.             `name`,
5.             `author`,
6.             `actor`,
7.             `produce`,
8.             `create_date`
9.     from `animes`
10.     <where>
11.         <foreach collection="list" item="id" open="id in(" close=" )" separator=", ">
12.             #{id}
13.         </foreach>
14.     </where>   
15. </select>

复制代码

执行SQL:

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

复制代码

7.1.3  起别名 foreach  (in)

不用where标签;
使用 in;

1. <select id="selectAnimesByConditionUserForeach" resultType="com.kgc.mybatis.bean.Anime">
2.     select  `id`,
3.             `cid`,
4.             `name`,
5.             `author`,
6.             `actor`,
7.             `produce`,
8.             `create_date`
9.     from `animes`
10.     <foreach collection="ids" item="id" open=" where id in(" close=" )" separator=", ">
11.         #{id}
12.     </foreach>
13. </select>

复制代码

执行SQL:

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

1. <select id="selectAnimesByConditionUserForeach" resultType="com.kgc.mybatis.bean.Anime">
2.     select  `id`,
3.             `cid`,
4.             `name`,
5.             `author`,
6.             `actor`,
7.             `produce`,
8.             `create_date`
9.     from `animes`
10.         <trim prefix=" where id in ">
11.         <foreach collection="ids" item="id" open=" (" close=" )" separator=", ">
12.             #{id}
13.         </foreach>
14.      </trim>
15. </select>

复制代码

执行SQL:

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

复制代码

7.1.5 起别名 foreach  （or）

不用where标签;
使用 or;

1. <select id="selectAnimesByConditionUserForeach" resultType="com.kgc.mybatis.bean.Anime">
2.     select  `id`,
3.             `cid`,
4.             `name`,
5.             `author`,
6.             `actor`,
7.             `produce`,
8.             `create_date`
9.     from `animes`
10.     <foreach collection="ids" item="id" open=" where " close=" " separator=" or ">
11.         id = #{id}
12.     </foreach>
13. </select>

复制代码

执行SQL:

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

复制代码

7.2 测试

1. @Test
2. public void testMybatisMapperDynamicSQlUserForeach() throws IOException {
3.     SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
5.     //获取mapper接口的代理实现类对象
6.     AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
8.     //执行动态SQL，查询动漫列表
9.     List<Anime> animeList = animeMapper.selectAnimesByConditionUserForeach(Arrays.asList(101,102,103));
11.     animeList.forEach(System.out::println);
13. }

复制代码

8、trim + if  insert

8.1 SQL

1. <insert id="insertAnimeByConditionIfTrim">
2.     <trim prefix="insert into `animes` ( " suffix=")" suffixOverrides=",">
3.         <if test="cid != null"> `cid`,</if>
4.         <if test="name != null"> `name`,</if>
5.         <if test="author != null"> `author`,</if>
6.         <if test="actor != null"> `actor`,</if>
7.         <if test="produce != null"> `produce`,</if>
8.         <if test="createDate != null"> `create_date`,</if>
9.     </trim>
10.     <trim prefix="values ( " suffix=")" suffixOverrides=",">
11.         <if test="cid != null"> #{cid},</if>
12.         <if test="name != null"> #{name},</if>
13.         <if test="author != null"> #{author},</if>
14.         <if test="actor != null"> #{actor},</if>
15.         <if test="produce != null"> #{produce},</if>
16.         <if test="createDate != null"> #{createDate},</if>
17.     </trim>
18. </insert>

复制代码

执行SQL:

1. insert into `animes` ( `cid`, `name`, `author`, `actor`, `produce` ) values ( ?, ?, ?, ?, ? )

复制代码

8.2 测试

1. @Test
2. public  void testMybatisMapperDynamicSQlIfTrimInsert() throws IOException {
3.     SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
5.     //获取mapper接口的代理实现类对象
6.     AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
8.     Anime animeForm = new Anime();
9.     animeForm.setCid(1);
10.     animeForm.setName("知否知否");
11.     animeForm.setAuthor("关心则乱");
12.     animeForm.setActor("明兰");
13.     animeForm.setProduce("优酷");
15.     //指定if+ trim 冬天SQL,新增动漫
16.     int row = animeMapper.insertAnimeByConditionIfTrim(animeForm);
18.     System.out.println(row);
19. }

复制代码

9、@ Select

使用注释添加动漫；
9.1 SQL

1. @Select("select `id`,`cid`,`name`,`author`,`actor`,`produce`,`create_date` from `animes` where id = #{id} ")
2. Anime selectAnimesByConditionUserAnnotationSelect(Integer id);

复制代码

执行SQL:

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

复制代码

9.2 测试

1. @Test
2. public  void  testAnimesByConditionUserAnnotationSelect() throws IOException {
3.     SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
5.     //获取mapper接口的代理实现类对象
6.     AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
8.     Anime anime = animeMapper.selectAnimesByConditionUserAnnotationSelect(653);
10.     System.out.println(anime);
12. }

复制代码

10、@Delete  批量删除

10.1 SQL

1. @Delete({""})
2. int deleteAnimesByConditionUserAnnotationDelete(@Param("ids") List<Integer> ids);

复制代码

执行SQL:

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

复制代码

10.2 测试

1. @Test
2. public void testDeleteAnimesByConditionUserAnnotationDelete() throws IOException {
3.     SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
5.     //获取mapper接口的代理实现类对象
6.     AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
8.     int row = animeMapper.deleteAnimesByConditionUserAnnotationDelete(Arrays.asList(649, 651, 652));
10.     System.out.println(row);
12. }

复制代码

11、批量加入动漫分类

11.1 SQL

1. <insert id="insertCategoryBatchUserFoeEach" >
2.     insert into `category` (`name`) values
3.    
4.     <foreach collection="collection" item="category" separator=", ">
5.         (#{category.name})
6.     </foreach>
7. </insert>

复制代码

执行SQL:

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

复制代码

11.2 测试

1. @Test
2. public void testInsertCategoryBatchUserFoeEach() throws IOException {
3.     SqlSession defaultSqlSession = getSqlSessionFactory().openSession(true);
5.     //获取mapper接口的代理实现类对象
6.     AnimeMapper animeMapper = defaultSqlSession.getMapper(AnimeMapper.class);
8.     Category category1 = new Category();
9.     Category category2 = new Category();
10.     Category category3 = new Category();
11.     category1.setName("aaa");
12.     category2.setName("bbb");
13.     category3.setName("ccc");
15.     List<Category> categoryList = new ArrayList<>();
16.     categoryList.add(category1);
17.     categoryList.add(category2);
18.     categoryList.add(category3);
20.     int row = animeMapper.insertCategoryBatchUserFoeEach(categoryList);
22.     System.out.println(row);
24. }

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4