MySQL的动态标签_mysql association

1 动态SQL片段

1.1 sql 标签

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

    <!-- 动态条件分页查询 -->
    <sql id="sql_count">
            select count(*)
    </sql>
     
    <sql id="sql_select">
            select *
    </sql>
1
2
3
4
5
6
7
8

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>
1
2
3
4

2 动态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>
1
2
3

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.3 delete 标签

属性同 insert

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

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>
1
2
3
4
5
6
7
8
9

3 配置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>
1
2
3
4
5
6
7
8
9
10
11
12
13

标签说明：
主标签：
id:该 resultMap 的标志
type：返回值的类名，此例中返回 Studnet 类

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

4 动态 sql 拼接

4.1 if 标签

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

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

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>
1
2
3
4
5
6
7

属性介绍：

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）的关系。

注意：

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

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

5 格式化输出

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>
1
2
3
4
5
6
7
8
9

当 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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

标签可以自动的将第一个条件前面的逻辑运算符 (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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

使用 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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

6 association 和 collection 标签

6.1 association 标签（多对一）

association通常用来映射一对一的关系，例如，有个类User，对应的实体类如下：(getter,setter方法省略)

    @Data
    public class User {
        /**
         * 主键
         */
        private String id;
        /**
         * 用户姓名
         */
        private String userName;
    }
1
2
3
4
5
6
7
8
9
10
11

有个类Article，对应的实体类如下：

  @Data
    public class Article {
        /**
         * 主键
         */
        private String id;
        /**
         * 文章标题
         */
        private String articleTitle;
        /**
         * 文章内容
         */
        private String articleContent;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

如果我想查询一个用户的时候，也查到他写的一篇文章，那么怎样写呢？其实可以在类User中加入一个属性Article类：

 @Data
    public class User {
        /**
         * 主键
         */
        private String id;
        /**
         * 用户姓名
         */
        private String userName;
        /**
         * 类Article
         */
        private Article article;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mapper.xml 我在user类的UserMapper.xml这样配置

   <mapper namespace="com.uiot.practice.mysqltest.dao.UserMapper">
    	<resultMap type="com.uiot.practice.mysqltest.entity.Article" id="articleMap">
    		<id column="id" property="id" jdbcType="VARCHAR" javaType="java.lang.String"/>
    		<result column="userName" property="userName" jdbcType="VARCHAR" javaType="java.lang.String"/>
    		<!--这里把user的id传过去，com.uiot.practice.mysqltest为命名空间-->
    		<association property="article" column="id" select="com.uiot.practice.mysqltest.selectArticleByUserId" />
    	</resultMap>
    </mapper>
1
2
3
4
5
6
7
8

同时，我的article对应的ArticleMapper.xml这样写：

   <mapper namespace="com.uiot.practice.mysqltest.dao.ArticleMapper">
        <resultMap type="com.uiot.practice.mysqltest.entity.Article" id="ArticleResultMap">
            <id column="id" property="id" jdbcType="VARCHAR" javaType="java.lang.String"/>
            <result column="articleTitle" property="articleTitle" jdbcType="VARCHAR" javaType="java.lang.String"/>
            <result column="articleContent" property="articleContent" jdbcType="VARCHAR" javaType="java.lang.String"/>
        </resultMap>
     
        <select id="selectArticleByUserId" parameterType="java.lang.String" resultMap="ArticleResultMap">
            select * from tb_article where userId=#{userId}
        </select>
    </mapper>

1
2
3
4
5
6
7
8
9
10
11
12

6.2、collection 标签（一对多）

一对多，collection，理解了一对一，一对多容易理解

   @Data
    public class User {
        /**
         * 主键
         */
        private String id;
        /**
         * 用户姓名
         */
        private String userName;
        /**
         * 类Article
         */
        private List<Article> articleList;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

UserMapper.xml这样配置

   <mapper namespace="com.uiot.practice.mysqltest.dao.UserMapper">
    	<resultMap type="com.uiot.practice.mysqltest.entity.User" id="articleMap">
    		<id column="id" property="id" jdbcType="VARCHAR" javaType="java.lang.String"/>
    		<result column="userName" property="userName" jdbcType="VARCHAR" javaType="java.lang.String"/>
    		<!--这里把user的id传过去，com.uiot.practice.mysqltest为命名空间-->
    		<collection property="articleList" column="id" select="com.uiot.practice.mysqltest.selectArticleByUserId"/>
    	</resultMap>
    	<!--以下省略，类同，Mybatis会把结果封装成List类型。-->
    </mapper>
1
2
3
4
5
6
7
8
9