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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 【MySQL用法】MySQL动态SQL语句标签的详细使用方法说明

一、动态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关键字,和剔除追加到条件末尾的任何不相关的逗号。

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

使用 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的值都不为空的话,会忽略最后一个“,” 。

六、配置关联关系

6.1 association 标签(一对一)

由于文章篇幅限制,请查看我的另一篇文章【Mybatis用法】Mybatis框架中一对一,一对多association和collection的使用举例方法

6.2 collection 标签(一对多)

由于文章篇幅限制,请查看我的另一篇文章【Mybatis用法】Mybatis框架中一对一,一对多association和collection的使用举例方法


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
25天前
|
关系型数据库 MySQL PostgreSQL
postgresql和mysql中的limit使用方法
postgresql和mysql中的limit使用方法
37 1
|
1月前
|
SQL 运维 关系型数据库
MySQL 运维 SQL 备忘
MySQL 运维 SQL 备忘录
47 1
|
1月前
|
SQL 存储 关系型数据库
SQL文件导入MySQL数据库的详细指南
数据库中的数据转移是一项常规任务,无论是在数据迁移过程中,还是在数据备份、还原场景中,导入导出SQL文件显得尤为重要。特别是在使用MySQL数据库时,如何将SQL文件导入数据库是一项基本技能。本文将详细介绍如何将SQL文件导入MySQL数据库,并提供一个清晰、完整的步骤指南。这篇文章的内容字数大约在
187 1
|
25天前
|
SQL 关系型数据库 MySQL
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
36 0
|
25天前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
31 0
|
1月前
|
SQL 存储 关系型数据库
mysql 数据库空间统计sql
mysql 数据库空间统计sql
47 0
|
1月前
|
SQL 存储 关系型数据库
mysql SQL必知语法
本文详细介绍了MySQLSQL的基本语法,包括SELECT、FROM、WHERE、GROUPBY、HAVING、ORDERBY等关键字的使用,以及数据库操作如创建、删除表,数据类型,插入、查询、过滤、排序、连接和汇总数据的方法。通过学习这些内容,读者将能更好地管理和操
18 0
|
1月前
|
SQL 分布式计算 关系型数据库
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
Hadoop-24 Sqoop迁移 MySQL到Hive 与 Hive到MySQL SQL生成数据 HDFS集群 Sqoop import jdbc ETL MapReduce
87 0
|
11月前
|
SQL 存储 关系型数据库
MySQL下使用SQL命令进行表结构与数据复制实践
MySQL下使用SQL命令进行表结构与数据复制实践
156 0
|
SQL 关系型数据库 MySQL
MySQl数据库第八课-------SQL命令查询-------主要命脉2
MySQl数据库第八课-------SQL命令查询-------主要命脉
下一篇
无影云桌面