jjzjj

MyBatis(三)-动态SQL

xiaoqigui 2023-04-16 原文

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 "cid != null and cid eq '1'.toString()"

test整体用双引号，里面的判断条件单引号并且判断条件加了toString()；

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

6.1.3 测试

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.4 单引号和双引号总结

总结：

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

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

}

12、排序无效问题

参考博客：mybatis动态sql排序无效问题

在使用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

13.1 修改get实体的方法

因为MyBatis映射的格式就有问题，所以可以在get方法中将时间格式化；

public String getBirthday() {
    return new SimpleDateFormat("yyyy-MM-dd").format(this.birthday);
}

13.2 jstl格式化标签库

通过格式化标签库的 <fmt:formatDate value="" pattern = "" > 进行格式化

<fmt:formatDate value='${userInfo.birthday}' pattern='yyyy-MM-dd'/>
MyBatisSQLcodecidltJava

有关MyBatis(三)-动态SQL的更多相关文章

  1. Hive SQL 五大经典面试题 - 2

    目录第1题连续问题分析：解法：第2题分组问题分析：解法：第3题间隔连续问题分析：解法：第4题打折日期交叉问题分析：解法：第5题同时在线问题分析：解法：第1题连续问题如下数据为蚂蚁森林中用户领取的减少碳排放量iddtlowcarbon10012021-12-1212310022021-12-124510012021-12-134310012021-12-134510012021-12-132310022021-12-144510012021-12-1423010022021-12-154510012021-12-1523.......找出连续3天及以上减少碳排放量在100以上的用户分析：遇到这类

  2. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  3. ruby - 在 Ruby 中动态创建数组 - 2

    有没有办法在Ruby中动态创建数组？例如，假设我想遍历用户输入的书籍数组:books=gets.chomp用户输入:"TheGreatGatsby,CrimeandPunishment,Dracula,Fahrenheit451,PrideandPrejudice,SenseandSensibility,Slaughterhouse-Five,TheAdventuresofHuckleberryFinn"我把它变成一个数组:books_array=books.split(",")现在，对于用户输入的每一本书，我想用Ruby创建一个数组。伪代码来做到这一点:x=0books_array.

  4. ruby - 是否可以将 IRB 提示配置为动态更改？ - 2

    我想在IRB中浏览文件系统并让提示更改以反射(reflect)当前工作目录，但我不知道如何在每个命令后进行提示更新。最终，我想在日常工作中更多地使用IRB，让bash溜走。我在我的.irbrc中试过这个:require'fileutils'includeFileUtilsIRB.conf[:PROMPT][:CUSTOM]={:PROMPT_N=>"\e[1m:\e[m",:PROMPT_I=>"\e[1m#{pwd}>\e[m",:PROMPT_S=>"FOO",:PROMPT_C=>"\e[1m#{pwd}>\e[m",:RETURN=>""}IRB.conf[:PROMPT_MO

  5. ruby-on-rails - carrierwave:在序列化动态属性上安装 uploader - 2

    首先，我使用的是rails3.1.3和来自master的carrierwavegithub仓库的分支。我使用after_init钩子(Hook)来确定基于属性的字段页面模型实例并为这些字段定义属性访问器将值存储在序列化哈希中(希望它清楚我是什么谈论)。这是我正在做的事情的精简版:classPage省略mount_uploader命令让我可以访问我想要的属性。但是当我安装uploader时出现错误消息说“nil类的未定义新方法”我在源代码中读到有方法read_uploader和扩展模块中的write_uploader。我如何必须覆盖这些来制作mount_uploader命令使用我的“虚拟

  6. sql - 在 Rails Console for PostgreSQL 的表中显示数据 - 2

    我找到了这样的东西:Rails:Howtolistdatabasetables/objectsusingtheRailsconsole?这一行没问题:ActiveRecord::Base.connection.tables并返回所有表但是ActiveRecord::Base.connection.table_structure("users")产生错误:ActiveRecord::Base.connection.table_structure("projects")我认为table_structure不是Postgres方法。如何列出Postgres数据库的Rails控制台中表中的所有

  7. ruby - 在 Ruby 中动态生成多维数组 - 2

    我正在尝试动态构建一个多维数组。我想要的基本上是这样的(为简单起见写出来):b=0test=[[]]test[b]这给了我错误:NoMethodError:undefinedmethod`test=[[],[],[]]而且它工作正常，但在我的实际使用中，我不会事先知道需要多少个数组。有一个更好的方法吗？谢谢 最佳答案 不需要像您正在使用的索引变量。只需将每个数组附加到您的test数组:irb>test=[]=>[]irb>test[["a","b","c"]]irb>test[["a","b","c"],["d","e","f"]]

  8. ruby-on-rails - 使用 gmaps4rails 动态加载谷歌地图标记 - 2

    如何只加载map边界内的标记gmaps4rails？当然，在平移和/或缩放后加载新的。与此直接相关的是，如何获取map的当前边界和缩放级别？ 最佳答案 我是这样做的，我只在用户完成平移或缩放后替换标记，如果您需要不同的行为，请使用不同的事件监听器:在你看来(index.html.erb):{"zoom"=>15,"auto_adjust"=>false,"detect_location"=>true,"center_on_user"=>true}},false,true)%>在View的底部添加:functiongmaps4rail

  9. ruby - 动态方法链？ - 2

    如何在对象上调用方法名称的嵌套哈希？例如，给定以下哈希:hash={:a=>{:b=>{:c=>:d}}}我想创建一个方法，给定上面的散列，执行以下操作:object.send(:a).send(:b).send(:c).send(:d)我的想法是我需要从一个未知的关联中获取一个特定的属性(这个方法不知道，但程序员知道)。我希望能够指定一个方法链来以嵌套哈希的形式检索该属性。例如:hash={:manufacturer=>{:addresses=>{:first=>:postal_code}}}car.execute_method_hash(hash)=>90210

  10. ruby - 防止SQL注入(inject)/好的Ruby方法 - 2

    Ruby中防止SQL注入(inject)的好方法是什么？ 最佳答案 直接使用ruby？使用准备好的语句:require'mysql'db=Mysql.new('localhost','user','password','database')statement=db.prepare"SELECT*FROMtableWHEREfield=?"statement.execute'value'statement.fetchstatement.close 关于ruby-防止SQL注入(inject

随机推荐