百木园-与人分享,
就是让自己快乐。

MyBatis(三)-动态SQL

1、if

<if>动态标签:判断参数时满足test指定的条件,如果满足,就执行if(增加if标签中的SQL语句);

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

1.1 SQL

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

<!-- List<Anime> selectAnimesByConditionUserIf(@Param(\"cid\") Integer cid,@Param(\"author\") String author);  -->
<select id=\"selectAnimesByConditionUserIf\" resultType=\"com.kgc.mybatis.bean.Anime\">
    select  `id`,
    `cid`,
    `name`,
    `author`,
    `actor`,
    `produce`,
    `create_date`
    from `animes`
    where `create_date` &lt; now()
    <if test=\"cid != null and cid != 0 \">
        cid = #{cid}
    </if>
    <if test=\"author != null\">
        and author like concat(\'%\',#{author},\'%\')
    </if>
</select>

执行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<Anime> animeList = animeMapper.selectAnimesByConditionUserIf(0, \"土豆\");

    animeList.forEach(System.out::println);
}

2、where + if

  • <where><if> 动态标签组合:当where标签中,有if条件成立时自动增加where关键字,如果所有的if都不成立,也不会多增加where关键字

  • 当where标签中,if成立,增加的SQL语句,前面多出现一个and或者 or关键字,会被自动过滤(剔除),但是末尾出现的,不会被剔除

  • where标签中,也可以增加固定条件,再实际开发过程中,建议where标签中,必须写固定条件,不能全部写if判断;

2.1 SQL

<!--   List<Anime> selectAnimesByConditionUserIfWhere(@Param(\"cid\") Integer cid,@Param(\"author\") String author); -->
<select id=\"selectAnimesByConditionUserIfWhere\" resultType=\"com.kgc.mybatis.bean.Anime\">
    select  `id`,
    `cid`,
    `name`,
    `author`,
    `actor`,
    `produce`,
    `create_date`
    from `animes`
    <where>
        <if test=\"cid != null and cid != 0 \">
            and cid = #{cid}
        </if>
        <if test=\"author != null\">
            and author like concat(\'%\',#{author},\'%\')
        </if>
    </where>
</select>

执行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<Anime> animeList = animeMapper.selectAnimesByConditionUserIfWhere(0, \"土豆\");

    animeList.forEach(System.out::println);
    
}

3、trim + if

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

3.1 SQL

  • \"and |or\" 中间一般都会添加一个空格;
<!--  List<Anime> selectAnimesByConditionUserIfTrim(@Param(\"cid\") Integer cid,@Param(\"author\") String author); -->
<select id=\"selectAnimesByConditionUserIfTrim\" resultType=\"com.kgc.mybatis.bean.Anime\">
    select  `id`,
    `cid`,
    `name`,
    `author`,
    `actor`,
    `produce`,
    `create_date`
    from `animes`
    <trim prefix=\" where \"  prefixOverrides=\"and |or\" suffixOverrides=\"and |or\" suffix=\";\">
        <if test=\"cid != null and cid != 0 \">
            cid = #{cid} and
        </if>
        <if test=\"author != null\">
            author like concat(\'%\',#{author},\'%\') and
        </if>
    </trim>
</select>

执行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<Anime> animeList = animeMapper.selectAnimesByConditionUserIfTrim(0, \"土豆\");

    animeList.forEach(System.out::println);

}

4、set + if update

4.1SQL

<!--  int updateAnimeByConditionUserIfSet(Anime animeFOrm); -->
<update id=\"updateAnimeByConditionUserIfSet\">
    update `animes`
    <set>
        <if test=\"cid != null\"> `cid` = #{cid},</if>
        <if test=\"name != null\"> `name` = #{name},</if>
        <if test=\"author != null\"> `author` = #{author},</if>
        <if test=\"actor != null\"> `actor` = #{actor},</if>
        <if test=\"produce != null\"> `produce` = #{produce},</if>
        <if test=\"createDate != null\"> `create_date` = #{createDate},</if>
    </set>
    where `id` = #{id}
</update>

执行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

<!--   int updateAnimeByConditionUserIfTrim(Anime animeFOrm); -->
<update id=\"updateAnimeByConditionUserIfTrim\">
    <trim prefix=\"update `animes` set \" prefixOverrides=\",\" suffixOverrides=\",\">
        <if test=\"cid != null\"> `cid` = #{cid},</if>
        <if test=\"name != null\"> `name` = #{name},</if>
        <if test=\"author != null\"> `author` = #{author},</if>
        <if test=\"actor != null\"> `actor` = #{actor},</if>
        <if test=\"produce != null\"> `produce` = #{produce},</if>
        <if test=\"createDate != null\"> `create_date` = #{createDate},</if>
    </trim>
    where `id` = #{id}
</update>

执行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整体用单引号,里面的判断条件双引号

<!--List<Anime> selectAnimesByConditionUserChooseWhenOtherwise(@Param(\"cid\") String cid); -->
<select id=\"selectAnimesByConditionUserChooseWhenOtherwise\" resultType=\"com.kgc.mybatis.bean.Anime\">
    select  `id`,
    `cid`,
    `name`,
    `author`,
    `actor`,
    `produce`,
    `create_date`
    from `animes`
    <where>
        <choose>
            <!-- test整体使用单引号,判断条件使用双引号  -->
            <when test=\'cid != null  and cid == \"1\"\'>
                and cid = 1
            </when>
            <when test=\'cid != null  and cid == \"2\"\'>
                and cid = 2
            </when>
            <otherwise>
                and cid = 3
            </otherwise>
        </choose>
    </where>
</select>

6.1.1 测试

List<Anime> 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整体用双引号,里面的判断条件单引号

...
<choose>
    <!-- test整体使用双引号,判断条件使用单引号  -->
    <when test=\"cid != null  and cid == \'1\'\">
        and cid = 1
    </when>
    <when test=\"cid != null  and cid == \'2\'\">
        and cid = 2
    </when>
    <otherwise>
        and cid = 3
    </otherwise>
</choose>
...

6.1.2 测试

List<Anime> 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 ==

...
<choose>
    <when test=\'cid != null  and cid == \"1\"\'>
        and cid = 1
    </when>
    <when test=\'cid != null  and cid == \"2\"\'>
        and cid = 2
    </when>
    <otherwise>
        and cid = 3
    </otherwise>
</choose>
...

6.2.1 测试

List<Anime> 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

...
<choose>
    <when test=\'cid != null  and cid eq \"1\"\'>
        and cid = 1
    </when>
    <when test=\'cid != null  and cid eq \"2\"\'>
        and cid = 2
    </when>
    <otherwise>
        and cid = 3
    </otherwise>
</choose>
...

6.2.2 测试

List<Anime> 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\"
...
<choose>
    <when test=\'cid != null  and cid eq \"1\"\'>
        and cid = 1
    </when>
    <when test=\'cid != null  and cid eq \"2\"\'>
        and cid = 2
    </when>
    <otherwise>
        and cid = 3
    </otherwise>
</choose>
...
测试
List<Anime> 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()
...
<choose>
     <when test=\'cid != null  and cid eq \"1\".toString()\'>
         and cid = 1
     </when>
     <when test=\'cid != null  and cid eq \"2\".toString()\'>
         and cid = 2
     </when>
     <otherwise>
         and cid = 3
     </otherwise>
</choose>
...
测试
List<Anime> 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\"
...
<choose>
    <when test=\'cid != null  and cid eq \"A\"\'>
        and cid = 1
    </when>
    <when test=\'cid != null  and cid eq \"B\"\'>
        and cid = 2
    </when>
    <otherwise>
        and cid = 3
    </otherwise>
</choose>
...
测试
List<Anime> 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()
...
<choose>
    <when test=\'cid != null  and cid eq \"A\".toString()\'>
        and cid = 1
    </when>
    <when test=\'cid != null  and cid eq \"B\".toString()\'>
        and cid = 2
    </when>
    <otherwise>
        and cid = 3
    </otherwise>
</choose>
...
测试
List<Anime> 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\"
<choose>
    <when test=\'cid != null  and cid eq \"11\"\'>
        and cid = 1
    </when>
    <when test=\'cid != null  and cid eq \"22\"\'>
        and cid = 2
    </when>
    <otherwise>
        and cid = 3
    </otherwise>
</choose>
测试
List<Anime> 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()
...
<choose>
    <when test=\'cid != null  and cid eq \"11\"toString()\'>
        and cid = 1
    </when>
    <when test=\'cid != null  and cid eq \"22\"toString()\'>
        and cid = 2
    </when>
    <otherwise>
        and cid = 3
    </otherwise>
</choose>
...
测试
List<Anime> 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\"
...
<choose>
    <when test=\'cid != null  and cid eq \"AA\"\'>
        and cid = 1
    </when>
    <when test=\'cid != null  and cid eq \"BB\"\'>
        and cid = 2
    </when>
    <otherwise>
        and cid = 3
    </otherwise>
</choose>
...
测试
List<Anime> 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()
...
<choose>
    <when test=\'cid != null  and cid eq \"AA\".toString()\'>
        and cid = 1
    </when>
    <when test=\'cid != null  and cid eq \"BB\".toString()\'>
        and cid = 2
    </when>
    <otherwise>
        and cid = 3
    </otherwise>
</choose>
...
测试
List<Anime> 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\"
...
<choose>
    <when test=\'cid != null  and cid eq \"任意字符1\"\'>
        and cid = 1
    </when>
    <when test=\'cid != null  and cid eq \"任意字符2\"\'>
        and cid = 2
    </when>
    <otherwise>
        and cid = 3
    </otherwise>
</choose>
...
测试
List<Anime> 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()
...
<choose>
    <when test=\'cid != null  and cid eq \"任意字符1\".toString()\'>
        and cid = 1
    </when>
    <when test=\'cid != null  and cid eq \"任意字符2\".toString()\'>
        and cid = 2
    </when>
    <otherwise>
        and cid = 3
    </otherwise>
</choose>
...
测试
List<Anime> 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;

<!--List<Anime> selectAnimesByConditionUserForeach(@Param(\"ids\") List<Integer> ids);-->
<select id=\"selectAnimesByConditionUserForeach\" resultType=\"com.kgc.mybatis.bean.Anime\">
    select  `id`,
            `cid`,
            `name`,
            `author`,
            `actor`,
            `produce`,
            `create_date`
    from `animes`
	<where>
        <foreach collection=\"ids\" item=\"id\" open=\"id in(\" close=\" )\" separator=\", \">
            #{id}
        </foreach>
	</where>
</select>

执行SQL:

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

7.1.2 不起别名 where + foreach (in)

使用 in;

<!--   List<Anime> selectAnimesByConditionUserForeach( List<Integer> ids);  -->
<select id=\"selectAnimesByConditionUserForeach\" resultType=\"com.kgc.mybatis.bean.Anime\">
    select  `id`,
            `cid`,
            `name`,
            `author`,
            `actor`,
            `produce`,
            `create_date`
    from `animes`
    <where>
        <foreach collection=\"list\" item=\"id\" open=\"id in(\" close=\" )\" separator=\", \">
            #{id}
        </foreach>
    </where>   
</select>

执行SQL:

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

7.1.3 起别名 foreach (in)

不用where标签;

使用 in;

<!--List<Anime> selectAnimesByConditionUserForeach(@Param(\"ids\") List<Integer> ids);-->
<select id=\"selectAnimesByConditionUserForeach\" resultType=\"com.kgc.mybatis.bean.Anime\">
    select  `id`,
            `cid`,
            `name`,
            `author`,
            `actor`,
            `produce`,
            `create_date`
    from `animes`
    <foreach collection=\"ids\" item=\"id\" open=\" where id in(\" close=\" )\" separator=\", \">
        #{id}
    </foreach>
</select>

执行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<Anime> selectAnimesByConditionUserForeach(@Param(\"ids\") List<Integer> ids);-->
<select id=\"selectAnimesByConditionUserForeach\" resultType=\"com.kgc.mybatis.bean.Anime\">
    select  `id`,
            `cid`,
            `name`,
            `author`,
            `actor`,
            `produce`,
            `create_date`
    from `animes`
	<trim prefix=\" where id in \">
        <foreach collection=\"ids\" item=\"id\" open=\" (\" close=\" )\" separator=\", \">
            #{id}
        </foreach>
     </trim>
</select>

执行SQL:

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

7.1.5 起别名 foreach (or)

不用where标签;

使用 or;

<!--List<Anime> selectAnimesByConditionUserForeach(@Param(\"ids\") List<Integer> ids);-->
<select id=\"selectAnimesByConditionUserForeach\" resultType=\"com.kgc.mybatis.bean.Anime\">
    select  `id`,
            `cid`,
            `name`,
            `author`,
            `actor`,
            `produce`,
            `create_date`
    from `animes`
    <foreach collection=\"ids\" item=\"id\" open=\" where \" close=\" \" separator=\" or \">
        id = #{id}
    </foreach>
</select>

执行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<Anime> animeList = animeMapper.selectAnimesByConditionUserForeach(Arrays.asList(101,102,103));

    animeList.forEach(System.out::println);

}

8、trim + if insert

8.1 SQL

<!-- int insertAnimeByConditionIfTrim(Anime animeForm); -->
<insert id=\"insertAnimeByConditionIfTrim\">
    <trim prefix=\"insert into `animes` ( \" suffix=\")\" suffixOverrides=\",\">
        <if test=\"cid != null\"> `cid`,</if>
        <if test=\"name != null\"> `name`,</if>
        <if test=\"author != null\"> `author`,</if>
        <if test=\"actor != null\"> `actor`,</if>
        <if test=\"produce != null\"> `produce`,</if>
        <if test=\"createDate != null\"> `create_date`,</if>
    </trim>
    <trim prefix=\"values ( \" suffix=\")\" suffixOverrides=\",\">
        <if test=\"cid != null\"> #{cid},</if>
        <if test=\"name != null\"> #{name},</if>
        <if test=\"author != null\"> #{author},</if>
        <if test=\"actor != null\"> #{actor},</if>
        <if test=\"produce != null\"> #{produce},</if>
        <if test=\"createDate != null\"> #{createDate},</if>
    </trim>
</insert>

执行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({\"<script>\",
            \"delete from `animes`\",
            \"<foreach collection=\'ids\' item=\'id\' open=\' where id in ( \' close= \' ) \' separator= \' , \'> \" ,
            \"      #{id} \",
            \"</foreach>\" ,
            \"</script>\"})
int deleteAnimesByConditionUserAnnotationDelete(@Param(\"ids\") List<Integer> 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

<!-- int insertCategoryBatchUserFoeEach(List<Category> categoryList) -->
<!-- int insertCategoryBatchUserFoeEach(@Param(\"categoryList\") List<Category> categoryList); -->
<insert id=\"insertCategoryBatchUserFoeEach\" >
    insert into `category` (`name`) values
    <!-- 
    默认参数:collection(不自定义参数名的时候可以使用Available parameters are [collection, list])
    自定义参数:categoryList 
    -->
    <foreach collection=\"collection\" item=\"category\" separator=\", \">
        (#{category.name})
    </foreach>
</insert>

执行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<Category> categoryList = new ArrayList<>();
    categoryList.add(category1);
    categoryList.add(category2);
    categoryList.add(category3);

    int row = animeMapper.insertCategoryBatchUserFoeEach(categoryList);

    System.out.println(row);

}

来源:https://www.cnblogs.com/xiaoqigui/p/16619854.html
本站部分图文来源于网络,如有侵权请联系删除。

未经允许不得转载:百木园 » MyBatis(三)-动态SQL

相关推荐

  • 暂无文章