【MySQL用法】MySQL动态SQL语句标签的详细使用方法说明

目录动态SQL标签一、动态SQL片段1.1 sql 标签1.2 include 标签二、动态SQL标签2.1 select 标签2.2 insert 标签2.3 delete 标签2.4 update 标签三、配置 JAVA 对象属性与查询结果集中列名对应关系四、动态 sql 拼接4.1 if 标签4.2 foreach 标签4.3 choo...

No8g攻城狮

10715人浏览 · 2019-11-26 16:03:57

No8g攻城狮 · 2019-11-26 16:03:57 发布

本文目录

三、配置 JAVA 对象属性与查询结果集中列名对应关系

6.1 association 标签（一对一）

6.2 collection 标签（一对多）

一、动态SQL片段

1.1 sql 标签

当多种类型的查询语句的查询字段或者查询条件相同时，可以将其定义为常量，方便调用，这样就可以通过SQL片段达到代码复用。为求 <select> 结构清晰也可将 sql 语句分解。

<!-- 动态条件分页查询 -->
<sql id="sql_count">
        select count(*)
</sql>

<sql id="sql_select">
        select *
</sql>

1.2 include 标签

用于引用定义的常量

通过<include refid = "sql_count"/>引用

<select id="selectCount" resultType="com.iot.site.module.quote.entity.Quote">
    <include refid = "sql_count"/>
    from site_quote
</select>

二、动态SQL标签

2.1 select 标签

属性介绍:

id :唯一的标识符.

parameterType:传给此语句的参数的全路径名或别名例:com.test.poso.User 或 user

resultType :语句返回值类型或别名。注意，如果是集合，那么这里填写的是集合的泛型，而不是集合本身（resultType 与 resultMap 不能并用）

<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="Object">
    select * from student where id=#{id}
</select>

2.2 insert 标签

属性介绍:

id :唯一的标识符

parameterType:传给此语句的参数的全路径名或别名例:com.test.poso.User

<insert id="insertMoreSilder" parameterType="com.iot.site.mybatis.model.TCmsAdSilder">
        INSERT INTO t_cms_ad_silder 
            (silde_name, img, silde_url, sort_id, 
                status, `type`, site_id, user_id, push_id) 
        VALUES
        <foreach collection="list" item="item" separator=",">
          (#{item.sildeName}, #{item.img}, #{item.sildeUrl}, #{item.sortId},
          #{item.status}, #{item.type}, #{item.siteId}, #{item.userId}, #{item.pushId})
        </foreach>
    </insert>

<insert id="insert" parameterType="Object">
    insert into student
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="name != null"> NAME, </if>
    </trim>
    <trim prefix="values(" suffix=")" suffixOverrides=",">
        <if test="name != null"> #{name}, </if>
     </trim>
</insert>

2.3 delete 标签

属性同 insert

<delete id="deleteByPrimaryKey" parameterType="Object">
    delete from student where id=#{id}
</delete>

2.4 update 标签

属性同 insert

<!--修改发布状态-->
<update id="updateAudit">
    UPDATE site_model_content SET
    status = 1
    WHERE content_id in
    <foreach collection="ids" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</update>

三、配置 JAVA 对象属性与查询结果集中列名对应关系

resultMap 标签的使用

基本作用：

建立 SQL 查询结果字段与实体属性的映射关系信息查询的结果集转换为 java 对象，方便进一步操作。

将结果集中的列与 java 对象中的属性对应起来并将值填充进去

注意：与 java 对象对应的列不是数据库中表的列名，而是查询后结果集的列名

<resultMap id="BaseResultMap" type="com.iot.site.module.student.model.Student">
    <id property="id" column="id" />
    <result column="NAME" property="name" />
    <result column="HOBBY" property="hobby" />
    <result column="MAJOR" property="major" />
    <result column="BIRTHDAY" property="birthday" />
    <result column="AGE" property="age" />
</resultMap>

<!--查询时resultMap引用该resultMap -->
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="Object">
    select id,name,hobby,major,birthday,age from student where id=#{id}
</select>

标签说明：

主标签：

id:该 resultMap 的标志

type：返回值的类名，此例中返回 Studnet 类

子标签：

id:用于设置主键字段与领域模型属性的映射关系，此处主键为 ID，对应 id。

result：用于设置普通字段与领域模型属性的映射关系

四、动态 sql 拼接

4.1 if 标签

if 标签通常用于 WHERE 语句、UPDATE 语句、INSERT 语句中，通过判断参数值来决定是否使用某个查询条件、判断是否更新某一个字段、判断是否插入某个字段的值。

<if test="name != null and name != ''">
    and NAME = #{name}
</if>

4.2 foreach 标签

foreach 标签主要用于构建 in 条件，可在 sql 中对集合进行迭代。也常用到批量删除、添加等操作中。

<!-- in查询所有，不分页 -->
<select id="selectIn" resultMap="BaseResultMap">
    select name,hobby from student where id in
    <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

属性介绍：

foreach标签主要有以下参数：

collection：collection 属性的值有三个分别是 list、array、map 三种，分别对应的参数类型为：List、数组、map 集合。

item ：循环体中的具体对象，表示在迭代过程中每一个元素的别名，支持属性的点路径访问，如item.age,item.info.details，在list和数组中是其中的对象，在map中是value。

index ：表示在迭代过程中每次迭代到的位置（下标），在list和数组中，index是元素的序号，在map中，index是元素的key，该参数可选。

open ：前缀，表示该语句以什么开始

close ：后缀，表示该语句以什么结束

separator ：分隔符，表示迭代时每个元素之间以什么分隔，例如在in()的时候，separator=","会自动在元素中间用“,“隔开，避免手动输入逗号导致sql错误，如in(1,2,)这样。该参数可选。

4.3 choose 标签

有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。MyBatis 提供了 choose 元素，按顺序判断 when 中的条件是否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when

的条件都不满则时，则执行 otherwise 中的 sql。类似于 Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

if 是与(and)的关系，而 choose 是或（or）的关系。

注意：

4.3.1 test中的key不能使用特殊字符 - 或者 +

4.3.2 test中的判断仅对!= null有效,不能使用==对字符串判断。

<select id="selectRecommendContentList" resultType="java.util.Map">
        SELECT
        smc.*,
        u.avatar avatar
        FROM
        site_model_content smc
        LEFT JOIN
        t_sys_user u ON u.user_id = smc.user_id
        <trim prefix="WHERE ("  suffix=")" prefixOverrides="AND|OR">
            smc.status=1
            <if test="siteId != null">
                AND  smc.site_id = #{siteId}
            </if>
            <if test="categoryId != null">
                AND  smc.category_id = #{categoryId}
            </if>
            <if test="categoryIds!=null and isRecommend==1">
                AND
                smc.category_id in
                <foreach item="item" index="index" collection="categoryIds" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>
            <if test='isRecommend != null and isRecommend==1'>
                AND
                smc.recommend = #{isRecommend}
            </if>
            <if test='siteType!=null'>
                AND
                smc.site_type like CONCAT('%', #{siteType}, '%')
            </if>
        </trim>
        GROUP BY smc.content_id
        <choose>
            <when test="orderBy==1">
                order by smc.content_id
            </when>
            <when test="orderBy==2">
                order by smc.inputdate DESC
            </when>
            <when test="orderBy==3">
                order by smc.inputdate
            </when>
            <when test="orderBy==4">
                order by smc.updatedate DESC
            </when>
            <when test="orderBy==5">
                order by smc.updatedate
            </when>
            <otherwise>
                order by smc.content_id DESC
            </otherwise>
        </choose>
    </select>

<select id="getStudentListChoose" parameterType="Student" resultMap="BaseResultMap">
    SELECT * from STUDENT WHERE 1=1
        <choose>
            <when test="Name!=null and student!='' ">
                AND name LIKE CONCAT(CONCAT('%', #{student}),'%')
            </when>
            <when test="hobby!= null and hobby!= '' ">
                AND hobby = #{hobby}
            </when>
            <otherwise>
                AND AGE = 15
            </otherwise>
        </choose>
</select>

五、格式化输出

5.1 where 标签

当 if 标签较多时，这样的组合可能会导致错误。如下：

<select id="getStudentListWhere" parameterType="Object" resultMap="BaseResultMap">
    SELECT * from STUDENT WHERE
    <if test="name!=null and name!='' ">
        NAME LIKE CONCAT(CONCAT('%', #{name}),'%')
    </if>
    <if test="hobby!= null and hobby!= '' ">
        AND hobby = #{hobby}
    </if>
</select>

当 name 值为 null 时，查询语句会出现 “WHERE AND” 的情况，解决该情况除了将"WHERE"改为“WHERE 1=1”之外，还可以利用 where 标签。

这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以 AND 或 OR 开头的，则它会剔除掉。

<select id="getStudentListWhere" parameterType="Object" resultMap="BaseResultMap">
    SELECT * from STUDENT
    <where>
        <if test="startDate != null and startDate != ''">
            and endDate &gt;= #{startDate}
        </if>
        <if test="endDate != null and endDate != ''">
            and endDate &lt;= #{endDate}
        </if>
        <if test="name!=null and name!='' ">
            NAME LIKE CONCAT(CONCAT('%', #{name}),'%')
        </if>
        <if test="hobby!= null and hobby!= '' ">
            AND hobby = #{hobby}
        </if>
    </where>
</select>

标签可以自动的将第一个条件前面的逻辑运算符 (or ,and) 去掉，正如代码中写的，id 查询条件前面是有“and”关键字的，但是在打印出来的 SQL 中却没有，这就是where标签的作用。

5.2 set 标签

没有使用 if 标签时，如果有一个参数为 null，都会导致错误。当在 update 语句中使用 if 标签时，如果最后的 if 没有执行，则或导致逗号多余错误。使用 set 标签可以将动态的配置 set关键字，和剔除追加到条件末尾的任何不相关的逗号。

<!--以下sql语句可能会出错，不建议这样写-->

<update id="updateStudent" parameterType="Object">
    UPDATE STUDENT
    SET NAME = #{name},
    MAJOR = #{major},
    HOBBY = #{hobby}
    WHERE ID = #{id};
</update>

<update id="updateStudent" parameterType="Object">
    UPDATE STUDENT SET
    <if test="name!=null and name!='' ">
        NAME = #{name},
    </if>
    <if test="hobby!=null and hobby!='' ">
        MAJOR = #{major},
    </if>
    <if test="hobby!=null and hobby!='' ">
        HOBBY = #{hobby}
    </if>
    WHERE ID = #{id};
</update>

使用 set+if 标签修改后，如果某项为 null 则不进行更新，而是保持数据库原值。

<update id="updateStudent" parameterType="Object">
    UPDATE STUDENT
    <set>
        <if test="name!=null and name!='' ">
            NAME = #{name},
        </if>
        <if test="hobby!=null and hobby!='' ">
            MAJOR = #{major},
        </if>
        <if test="hobby!=null and hobby!='' ">
            HOBBY = #{hobby}
        </if>
    </set>
    WHERE ID = #{id};
</update>

5.3 trim 标签

trim标记是一个格式化的标记，主要用于拼接sql的条件语句（前缀或后缀的添加或忽略），可以完成set或者是where标记的功能。

trim属性主要有以下四个

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

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

prefixOverrides：指定去除多余的前缀内容，如：prefixOverrides=“AND | OR”，去除trim标签内sql语句多余的前缀"and"或者"or"。

suffixOverrides：指定去除多余的后缀内容。

<select id="selectByCondition" resultType="com.iot.site.module.quote.entity.Quote">
    select
      quote_id as quoteId,
      site_id as siteId,
      site_name as siteName,
      city,
      area,
      customer_name as customerName,
      customer_phone as customerPhone,
      create_time as createTime,
      status,
      search_key as searchKey
    from site_quote
    <trim prefix="WHERE (" suffix=")" prefixOverrides="AND|OR" suffixOverrides=",">
        <if test="customerName != null and customerName != ''">
            customer_name like CONCAT('%', #{customerName}, '%')
        </if>
        <if test="customerPhone != null and customerPhone != ''">
            and customer_phone = #{customerPhone}
        </if>
        <if test="createTime != null">
            and create_time = #{createTime}
        </if>
        <if test="city != null and city != ''">
            and city = #{city}
        </if>
        <if test="area != null and area != ''">
            and area = #{area}
        </if>
        <if test="siteId != null and siteId != '' and siteId != 1">
            and site_id = #{siteId}
        </if>
        <if test="siteName != null and siteName != ''">
            and site_name = #{siteName}
        </if>
        <if test="status != null and status != ''">
            and status = #{status}
        </if>
        <if test="searchKey != null and searchKey != ''">
            and search_key = #{searchKey}
        </if>
    </trim>
    ORDER BY create_time DESC
</select>

如果status和searchKey的值都不为空的话，会忽略最后一个“，” 。