MyBatis操作数据库 -- 动态SQL

---

T04BF
👋专栏: 算法|JAVA|MySQL|Spring
🫵 与天斗其乐无穷

---

---

1. 动态SQL

动态sql能够实现不同条件下的sql拼接

标签

我们在新增数据的时候,有时候要指定列赋值

INSERT INTO userinfo (username,`password`) VALUES (?,?)

INSERT INTO userinfo (username,`password`) VALUES (?,?,?)

大多数情况需要我们根据参数的个数完善sql语句
这时候就要使用动态标签来判断了:

<insert id="insertUser">
  insert into userinfo (
  username,`password`,age,
  <if test="gender != null">
    gender,
  </if>
  <if test="phone != null">
    phone
  </if>)
  values (
  #{username},#{password},#{age},
  <if test="gender != null">
    #{gender},
  </if>
  <if test="phone != null">
    #{phone}
  </if>)
</insert>

测试用例:
测试当gender为空:

@Test
void insertUser() {
    UserInfo userInfo = new UserInfo();
    userInfo.setUsername("wangba");
    userInfo.setPassword("123456");
    userInfo.setAge(18);
    userInfo.setPhone("123456789");
    System.out.println(userInfoMapper.insertUser(userInfo));
}

但是实际上,单纯这样写会出现问题:
当我们的phone为空时:

发现会多一个逗号,导致sql语句出错
此时就要使用标签

标签

<trim>标签主要有4个属性:

• prefix:表示被<trim>括起来的整个语句,以prefix值作为前缀
• suffix:表示被<trim>括起来的整个语句,以suffix值作为后缀
• prefixOverrides:表示被<trim>括起来的整个语句,去除prefixOverrides前缀
• suffixOverrides:表示被<trim>括起来的整个语句,去除suffixOverrides前缀

此时就能就解决上面的问题:

<insert id="insertUser">
  insert into userinfo
  <trim prefix="(" suffix=")" suffixOverrides=",">
    username,`password`,age,
    <if test="gender != null">
      gender,
    </if>
    <if test="phone != null">
      phone
    </if>
  </trim>
  values
  <trim prefix="(" suffix=")" suffixOverrides=",">
    #{username},#{password},#{age},
    <if test="gender != null">
      #{gender},
    </if>
    <if test="phone != null">
      #{phone}
    </if>
  </trim>
</insert>

标签

<where>只会在子元素有内容的情况下才会插入where字句,并且会自动去除字句开头的and或or

    <select id="selectUserList" resultMap="XMLBaseMap">
        select * from userinfo
        <where>
            <if test="age != null">
                and age = #{age}
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="phone != null">
                and phone = #{phone}
            </if>
        </where>
    </sel

标签

<set> 标签会动态的在sql语句里面插入set关键字,并且会删除额外的逗号

<update id="updateUser" keyProperty="id" useGeneratedKeys="true">
  update userinfo
  <set>
    <if test="username != null">
      username = #{username},
    </if>
    <if test="password != null">
      password = #{password},
    </if>
    <if test="age != null">
      age = #{age},
    </if>
    <if test="phone != null">
      phone = #{phone},
    </if>
    <if test="gender != null">
      gender = #{gender},
    </if>
  </set>
  where id = #{id}
</update>

标签

对集合进行遍历时可以使用foreach 标签.有以下属性:

• collection:绑定方法参数中的集合
• item:遍历的每一个对象
• open:语句块开头的字符串
• close:语句块结束的字符串
• separator:每次遍历之间间隔的字符串

应用:根据多个userid,删除用户数据

<delete id="deleteUserByIds">
  delete from userinfo where id in
  <foreach collection="ids" item="id" separator="," open="(" close=")">
    #{id}
  </foreach>
</delete>

标签

在xml映射文件中配置的sql,可能会存在很多重复的片段,此时就会存在很多冗余的代码

可以对重复的代码片段进行抽取,将其通过<sql> 标签封装到一个sql片段.再通过<include>标签进行引用

• sql 定义可重复的sql片段
• <include> 通过属性refid,指定包含的sql片段

<sql id="allColumn">
    select * from userinfo
</sql>


<select id="queryAllUser" resultMap="XMLBaseMap">
  <include refid="allColumn"></include>
</select>

注解方式

注解方式动态sql比较麻烦,可读性不太好,不推荐使用

@Insert("<script>" +
"INSERT INTO userinfo " +
"<trim prefix='(' suffix=')' suffixOverrides=','>" +
"<if test='username!=null'>username,</if>" +
"<if test='password!=null'>password,</if>" +
"<if test='age!=null'>age,</if>" +
"<if test='gender!=null'>gender,</if>" +
"<if test='phone!=null'>phone,</if>" +
"</trim>" +
"VALUES " +
"<trim prefix='(' suffix=')' suffixOverrides=','>" +
"<if test='username!=null'>#{username},</if>" +
"<if test='password!=null'>#{password},</if>" +
"<if test='age!=null'>#{age},</if>" +
"<if test='gender!=null'>#{gender},</if>" +
"<if test='phone!=null'>#{phone}</if>" +
"</trim>"+
"</script>")
Integer insertUserByCondition(UserInfo userInfo);

---