day06-动态SQL语句

动态SQL语句

1.基本介绍

官方文档 mybatis – MyBatis 3 | 动态 SQL

  • 为什么需要动态SQL?

    1. 动态SQL是MyBatis的强大特性之一
    2. 使用 JDBC 或其他类似的框架,根据不同条件拼接SQL语句非常麻烦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号等
    3. SQL映射语句中的强大动态SQL语言可以很好地解决这个问题
  • 基本介绍

    1. 在一个实际的项目中,sql语句往往是比较复杂的
    2. 为了满足更加复杂的业务需求,MyBatis的设计者,提供了动态生成SQL的功能
  • 动态SQL的必要性

    1. 这里有一些需求:查询Monster时,如果程序员输入的age不大于0,sql语句就不要带age条件;更新记录时,如果传入的Monster对象没有设置新的属性值,数据库表字段就保持原来的值,设置了新的值,就更新
    2. 解决方案:从上面的需求中可以看出,有时我们在生成sql语句时,在同一个方法中,还要根据不同的情况生成不同的sql语句,解决的办法就是使用MyBatis提供的动态SQL机制
  • 动态SQL常用标签 — 动态SQL提供了如下几种常用的标签,类似Java的控制语句:

    1. if [判断]
    2. where [拼接where子句]
    3. choose /when /otherwise [类似java的switch,注意是单分支]
    4. foreach [类似in]
    5. trim [替换关键字/定制元素的功能]
    6. set [在update的set中,可以保证进入set标签的属性被修改,没有进入set的就保持原来的值]

2.动态SQL-案例演示

2.1if标签

使用动态 SQL 最常见情景是,根据条件,用if标签包含 where 子句的一部分。

例子:要求查询age大于10的所有记录,如果age不大于0,则输出所有记录。

<!--配置方法findMonsterByAge
    请查询age大于10的所有记录,如果程序员输入的age不大于0,则输出所有的记录
    1.如果在test表达式中使用#{age},是取不出值的
    2.要在接口声明的入参使用@param注解为"age"-->
<select id="findMonsterByAge" resultType="Monster" parameterType="Integer">
    SELECT * FROM `monster`
    <if test="age >= 0">
        WHERE `age`> #{age}
    </if>
</select>

当方法入参>=0时,底层的sql语句为:

image-20230227180806236

当入参<0时,sql为:

image-20230227180914051

2.2where标签

where 标签只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。

例子:查询id>5,并且名字为“牛魔王”的所有记录。如果名字为空,则不拼接带名字的条件,如果id<0,则不拼接带id的条件

<select id="findMonsterByIdAndName" parameterType="Monster" resultType="Monster">
    SELECT * FROM `monster`
    <where>
        <if test="id>=0">
            id > #{id}
        </if>
        <if test="name !=null and name !=""">
            AND `name` = #{name}
        </if>
    </where>
</select>

如下,当id的值小于0时,会自动去除id子句和name子句前的AND

image-20230227183522393

2.3choose/when/otherwise标签

有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。

<choose>
    <when></when>
    <when></when>
    <otherwise></otherwise>
</choose>

例子:

(1)如果给的name不为空,就按名字查询妖怪

(2)如果指定的id>0,就按id来查询妖怪

(3)如果前面两个添加都不满足,就默认查询salary>100的

<select id="findMonsterByIdOrName" parameterType="map" resultType="Monster">
    SELECT * FROM `monster`
    <choose>
        <when test="name!=null and name != """>
            WHERE `name` = #{name}
        </when>
        <when test="id >= 0 and id !=null">
            WHERE  `id` >= #{id}
        </when>
        <otherwise>
            WHERE  `salary` > 100
        </otherwise>
    </choose>
</select>

当入参name不符合条件时,选择id的子句

image-20230227193313279

当入参name和id都不符合条件时,默认选择otherwise的子句

image-20230227193139283

2.4foreach标签

动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。

foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头(open)与结尾(close)的字符串以及集合项迭代之间的分隔符(separator),这个元素也不会错误地添加多余的分隔符。

提示 你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。

例子:查询id为 10,12,14 的记录

<!--1.先判断集合是否为空,如果非空,则进行遍历
    2.collection对应入参 map,item是遍历的当前值value,open是sql的 ( ,
        separator是多个值之间的分隔符,close是 sql的 )
    3. #{id}对应 item="id"-->
<select id="findMonsterById_forEach" parameterType="map" resultType="Monster">
    SELECT * FROM `monster`
    <if test="ids !=null and ids != """>
        <where>
            id IN
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </if>
</select>

测试:

@Test
public void findMonsterById_forEach() {
    Map<String, Object> map = new HashMap<>();
    map.put("ids", Arrays.asList(10, 12, 14));//ids就是要遍历的集合,这里为List
    List<Monster> monsters =
            monsterMapper.findMonsterById_forEach(map);
    for (Monster monster : monsters) {
        System.out.println("monster--" + monster);
    }
    if (sqlSession != null) {
        sqlSession.close();
    }
}

image-20230227200903171

2.5trim标签(使用较少)

你可以通过自定义 trim 元素来定制元素的功能。比如,和 where 元素等价的自定义 trim 元素为:

<trim prefix="WHERE" prefixOverrides="AND |OR">
  ...
</trim>

prefixOverrides 属性会忽略通过管道符(|)分隔的文本序列(注意此例中的空格是必要的)。上述例子会移除所有 prefixOverrides 属性中指定的内容,并且插入 prefix 属性中指定的内容。我们也可以在 prefixOverride 属性中添加其他文本序列,达到拓展where元素的功能。

此外,trim 元素还有 suffix、suffixOverrides 属性,功能和 prefix、prefixOverride 类似。

例子:按照名字或年龄查询记录,如果sql语句开头有 and | or 就替换成 where

<!--trim 的意义在于它可以替换你指定的任何字符-->
<select id="findMonsterByName_Trim" parameterType="map" resultType="Monster">
    SELECT * FROM `monster`
    <trim prefix="WHERE" prefixOverrides="and |or |HAHA">
        <if test="name != null and name !=""">
            HAHA `name` =#{name}
        </if>
        <if test="age != null and age !=""">
            OR `age` =#{age}
        </if>
    </trim>
</select>

image-20230227204220373

2.6set标签(重点)

set 元素可以用于动态包含需要更新的列,忽略其它不更新的列。比如:

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

这个例子中,set 元素会动态地在行首插入 SET 关键字,并会删掉多余的逗号(这些逗号是在使用条件语句给列赋值时引入的)。

你也可以使用trim标签来达到set标签的效果

例子:对指定id的记录进行修改,如果没有设置新的属性,则保持原来的值

<update id="updateMonster_set" parameterType="map">
    UPDATE `monster`
    <set>
        <if test="age != null and age != """>age`=#{age},</if>
        <if test="name != null and name != """>`name`=#{name},</if>
        <if test="email != null and email != """>`email`=#{email},</if>
        <if test="birthday != null and birthday != """>`birthday`=#{birthday},</if>
        <if test="gender != null and gender != """>`gender`=#{gender},</if>
        <if test="salary != null and salary != """>`salary`=#{salary},</if>
    </set>
    WHERE `id`=#{id}
</update>

当入参只有id和name时,SQL语句如下,只有name字段被修改了,其他的字段仍保持原来的值。

image-20230227211048101

3.练习

有一个hero表,字段为id,nickname,skill,rank,salary,birthday

需求如下:

  1. 添加hero记录
  2. 查询rank大于30的所有记录,如果输入的rank不大于0,则输出所有的hero-if
  3. 查询rank为 3,6,10(rank可变)的hero记录-foreach
  4. 修改hero信息,如果没有设置新的属性值,则保持原来的值-set
  5. 根据id查询hero,如果没有传入id,就返回所有的hero-where|choose

(1)创建表hero

-- 创建hero表
CREATE TABLE `hero`(
`id` INT NOT NULL AUTO_INCREMENT,
`nickname` VARCHAR(255) NOT NULL DEFAULT "",
`skill` VARCHAR(255) NOT NULL DEFAULT "",
`rank` INT NOT NULL,
`salary` DOUBLE NOT NULL,
`birthday` DATE NOT NULL,
PRIMARY KEY (`id`)
)CHARSET=utf8

image-20230227214507329

(2)实体类Hero

package com.li.entity;

import lombok.*;

import java.util.Date;

/**
 * @author 李
 * @version 1.0
 */
@NoArgsConstructor
@AllArgsConstructor
@Setter
@Getter
@ToString
public class Hero {
    private Integer id;
    private String nickname;
    private String skill;
    private Integer rank;
    private double salary;
    private Date birthday;
}

(3)接口声明

package com.li.mapper;

import com.li.entity.Hero;

import java.util.List;

/**
 * @author 李
 * @version 1.0
 */
public interface HeroMapper {
    //1. 添加hero记录,返回增长的id
    public void addHero(Hero hero);

    //2. 查询rank大于30的所有记录,如果输入的rank不大于0,则返回所有的hero
    public List<Hero> findHeroByRank(Integer rank);

    //3. 查询rank为 3,6,10(rank可变)的所有hero记录
    public List<Hero> findHeroesByRanks(List<Integer> list);

    //4. 修改hero信息,如果没有设置新的属性值,则保持原来的值
    public void updateHero(Hero hero);

    //5. 根据id查询hero,如果没有传入id,就返回所有的hero
    public List<Hero> findHeroById(Integer id);
}

(4)映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.li.mapper.HeroMapper">
    <!--1. 添加 hero记录,返回增长的id-->
    <!--public void addHero(Hero hero);-->
    <insert id="addHero" parameterType="Hero" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO `hero` (nickname,skill,rank,salary,birthday)
        VALUE(#{nickname},#{skill},#{rank},#{salary},#{birthday})
    </insert>

    <!--2. 查询rank大于30的所有记录,如果输入的rank不大于0,则返回所有的hero-->
    <!--public List<Hero> findHeroByRank(Integer rank);-->
    <select id="findHeroByRank" parameterType="Integer" resultType="Hero">
        SELECT * FROM `hero`
        <if test="rank >=0 ">
            WHERE rank > #{rank}
        </if>
    </select>

    <!--3. 查询rank为 3,6,10(rank可变)的所有hero记录-->
    <!--public List<Hero> findHeroesByRanks(List<Integer> list);-->
    <select id="findHeroesByRanks" parameterType="list" resultType="Hero">
        SELECT * FROM `hero`
        <if test="list !=null">
            <where>
                rank IN
                <foreach collection="list" item="rank" open="(" separator="," close=")">
                    #{rank}
                </foreach>
            </where>
        </if>
    </select>

    <!--4. 修改hero信息,如果没有设置新的属性值,则保持原来的值-->
    <!--public void updateHero(Hero hero);-->
    <update id="updateHero" parameterType="Hero">
        UPDATE `hero`
        <set>
            <if test="nickname !=null and nickname !=""">`nickname`=#{nickname},</if>
            <if test="skill !=null and skill !=""">`skill`=#{skill},</if>
            <if test="rank !=null and rank !=""">`rank`=#{rank},</if>
            <if test="salary !=null and salary !=""">`salary`=#{salary},</if>
            <if test="birthday !=null and birthday !=""">`birthday`=#{birthday},</if>
        </set>
        WHERE id = #{id}
    </update>

    <!--5. 根据id查询hero,如果没有传入id,就返回所有的hero-->
    <!--public List<Hero> findHeroById(Integer id);-->
    <select id="findHeroById" parameterType="Integer" resultType="Hero">
        SELECT * FROM `hero`
        <!--也可以直接使用if-->
        <choose>
            <when test="id !=null and id !=""">
                WHERE id = #{id}
            </when>
        </choose>
    </select>
</mapper>

(5)测试略

hmoban主题是根据ripro二开的主题,极致后台体验,无插件,集成会员系统
自学咖网 » day06-动态SQL语句