MyBatis 动态SQL

最新推荐文章于 2023-09-22 12:15:06 发布

汪程序猿

最新推荐文章于 2023-09-22 12:15:06 发布

阅读量294

点赞数

分类专栏： SpringBoot 文章标签： sql java 数据库

本文链接：https://blog.csdn.net/m0_61504367/article/details/124680501

版权

SpringBoot 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

简介

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。

动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。

if
choose (when, otherwise)
trim (where, set)
foreach

if

动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分。比如:
在这里插入图片描述

choose (when, otherwise)

有些时候，我们不想用到所有的条件语句，而只想从中择其一二。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

还是上面的例子，但是这次变为提供了"title"就按"title"查找，提供了"author"就按"author"查找，若两者都没有提供，就返回所有符合条件的BLOG（实际情况可能是由管理员按一定策略选出BLOG列表，而不是返回大量无意义的随机结果）。

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

trim (where, set)

where 元素知道只有在一个以上的if条件有值的情况下才去插入"WHERE"子句。而且，若最后的内容是"AND"或"OR"开头的，where 元素也知道如何将他们去除。

如果 where 元素没有按正常套路出牌，我们还是可以通过自定义 trim 元素来定制我们想要的功能。比如，和 where 元素等价的自定义 trim 元素为：

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

prefixOverrides 属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的）。它带来的结果就是所有在 prefixOverrides 属性中指定的内容将被移除，并且插入 prefix 属性中指定的内容。

类似的用于动态更新语句的解决方案叫做 set。set 元素可以被用于动态包含需要更新的列，而舍去其他的。


<trim prefix="" suffix="" suffixOverrides="" prefixOverrides=""></trim>

prefix:在trim标签内sql语句加上前缀。

suffix:在trim标签内sql语句加上后缀。

suffixOverrides:指定去除多余的后缀内容，如：suffixOverrides=“,”，去除trim标签内sql语句多余的后缀","。

prefixOverrides:指定去除多余的前缀内容

在这里插入图片描述

foreach

动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

resultMap

resultMap可以实现将查询结果映射为复杂类型的pojo，比如在查询结果映射对象中包括pojo和list实现一对一查询和一对多查询。

<!-- 查询所有的订单数据 -->
    <!-- resultMap:填入配置的resultMap标签的id值 -->
    <select id="queryOrderAll" resultMap="orderResultMap">
        SELECT id, user_id,
        number,
        createtime, note FROM `order`
    </select>

roperty：主键在pojo中的属性名
column：主键在数据库中的列名

<!-- resultMap最终还是要将结果映射到pojo上，type就是指定映射到哪一个pojo -->
    <!-- id：设置ResultMap的id -->
    <resultMap type="order" id="orderResultMap">
        <!-- 定义主键 ,非常重要。如果是多个字段,则定义多个id -->
        <!-- property：主键在pojo中的属性名 -->
        <!-- column：主键在数据库中的列名 -->
        <id property="id" column="id" />

        <!-- 定义普通属性 -->
        <result property="userId" column="user_id" />
        <result property="number" column="number" />
        <result property="createtime" column="createtime" />
        <result property="note" column="note" />
    </resultMap>

在这里插入图片描述

<resultMap type="user" id="userOrderResultMap">
    <id property="id" column="id" />
    <result property="username" column="username" />
    <result property="birthday" column="birthday" />
    <result property="sex" column="sex" />
    <result property="address" column="address" />

    <!-- 配置一对多的关系
        property：填写pojo类中集合类类属性的名称
        javaType：填写集合类型的名称 
    -->
    <collection property="orders" javaType="list" ofType="order">
        <!-- 配置主键，是关联Order的唯一标识 -->
        <id property="id" column="oid" />
        <result property="number" column="number" />
        <result property="createtime" column="createtime" />
        <result property="note" column="note" />
    </collection>
</resultMap>

实际开发

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.system.mapper.SysUserMapper">

    <resultMap type="SysUser" id="SysUserResult">
        <result property="userId"    column="user_id"    />
        <result property="deptId"    column="dept_id"    />
        <result property="userName"    column="user_name"    />
        <result property="nickName"    column="nick_name"    />
        <result property="userType"    column="user_type"    />
        <result property="email"    column="email"    />
        <result property="phonenumber"    column="phonenumber"    />
        <result property="sex"    column="sex"    />
        <result property="avatar"    column="avatar"    />
        <result property="password"    column="password"    />
        <result property="status"    column="status"    />
        <result property="delFlag"    column="del_flag"    />
        <result property="loginIp"    column="login_ip"    />
        <result property="loginDate"    column="login_date"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="remark"    column="remark"    />
    </resultMap>

    <sql id="selectSysUserVo">
        select user_id, dept_id, user_name, nick_name, user_type, email, phonenumber, sex, avatar, password, status, del_flag, login_ip, login_date, create_by, create_time, update_by, update_time, remark from sys_user
    </sql>

    <select id="selectSysUserList" parameterType="SysUser" resultMap="SysUserResult">
        <include refid="selectSysUserVo"/>
        <where>
            <if test="deptId != null "> and dept_id = #{deptId}</if>
            <if test="userName != null  and userName != ''"> and user_name like concat('%', #{userName}, '%')</if>
            <if test="nickName != null  and nickName != ''"> and nick_name like concat('%', #{nickName}, '%')</if>
            <if test="userType != null  and userType != ''"> and user_type = #{userType}</if>
            <if test="email != null  and email != ''"> and email = #{email}</if>
            <if test="phonenumber != null  and phonenumber != ''"> and phonenumber = #{phonenumber}</if>
            <if test="sex != null  and sex != ''"> and sex = #{sex}</if>
            <if test="avatar != null  and avatar != ''"> and avatar = #{avatar}</if>
            <if test="password != null  and password != ''"> and password = #{password}</if>
            <if test="status != null  and status != ''"> and status = #{status}</if>
            <if test="loginIp != null  and loginIp != ''"> and login_ip = #{loginIp}</if>
            <if test="loginDate != null "> and login_date = #{loginDate}</if>
        </where>
    </select>

    <select id="selectSysUserByUserId" parameterType="Long" resultMap="SysUserResult">
        <include refid="selectSysUserVo"/>
        where user_id = #{userId}
    </select>

    <insert id="insertSysUser" parameterType="SysUser" useGeneratedKeys="true" keyProperty="userId">
        insert into sys_user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="deptId != null">dept_id,</if>
            <if test="userName != null and userName != ''">user_name,</if>
            <if test="nickName != null and nickName != ''">nick_name,</if>
            <if test="userType != null">user_type,</if>
            <if test="email != null">email,</if>
            <if test="phonenumber != null">phonenumber,</if>
            <if test="sex != null">sex,</if>
            <if test="avatar != null">avatar,</if>
            <if test="password != null">password,</if>
            <if test="status != null">status,</if>
            <if test="delFlag != null">del_flag,</if>
            <if test="loginIp != null">login_ip,</if>
            <if test="loginDate != null">login_date,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateBy != null">update_by,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="remark != null">remark,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="deptId != null">#{deptId},</if>
            <if test="userName != null and userName != ''">#{userName},</if>
            <if test="nickName != null and nickName != ''">#{nickName},</if>
            <if test="userType != null">#{userType},</if>
            <if test="email != null">#{email},</if>
            <if test="phonenumber != null">#{phonenumber},</if>
            <if test="sex != null">#{sex},</if>
            <if test="avatar != null">#{avatar},</if>
            <if test="password != null">#{password},</if>
            <if test="status != null">#{status},</if>
            <if test="delFlag != null">#{delFlag},</if>
            <if test="loginIp != null">#{loginIp},</if>
            <if test="loginDate != null">#{loginDate},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateBy != null">#{updateBy},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="remark != null">#{remark},</if>
         </trim>
    </insert>

    <update id="updateSysUser" parameterType="SysUser">
        update sys_user
        <trim prefix="SET" suffixOverrides=",">
            <if test="deptId != null">dept_id = #{deptId},</if>
            <if test="userName != null and userName != ''">user_name = #{userName},</if>
            <if test="nickName != null and nickName != ''">nick_name = #{nickName},</if>
            <if test="userType != null">user_type = #{userType},</if>
            <if test="email != null">email = #{email},</if>
            <if test="phonenumber != null">phonenumber = #{phonenumber},</if>
            <if test="sex != null">sex = #{sex},</if>
            <if test="avatar != null">avatar = #{avatar},</if>
            <if test="password != null">password = #{password},</if>
            <if test="status != null">status = #{status},</if>
            <if test="delFlag != null">del_flag = #{delFlag},</if>
            <if test="loginIp != null">login_ip = #{loginIp},</if>
            <if test="loginDate != null">login_date = #{loginDate},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="remark != null">remark = #{remark},</if>
        </trim>
        where user_id = #{userId}
    </update>

    <delete id="deleteSysUserByUserId" parameterType="Long">
        delete from sys_user where user_id = #{userId}
    </delete>

    <delete id="deleteSysUserByUserIds" parameterType="String">
        delete from sys_user where user_id in
        <foreach item="userId" collection="array" open="(" separator="," close=")">
            #{userId}
        </foreach>
    </delete>
</mapper>