Mybatis动态SQL

1、是什么

动态 SQL 是 MyBatis 的强大特性之一。在 JDBC 或其它类似的框架中，开发人员通常需要手动拼接 SQL 语句。根据不同的条件拼接 SQL 语句是一件极其痛苦的工作。例如，拼接时要确保添加了必要的空格，还要注意去掉列表最后一个列名的逗号。而动态 SQL 恰好解决了这一问题，可以根据场景动态的构建查询。

动态SQL（code that is executed dynamically），它一般是根据用户输入或外部条件动态组合的SQL语句块。动态SQL能灵活的发挥SQL强大的功能、方便的解决一些其它方法难以解决的问题。相信使用过动态SQL的人都能体会到它带来的便利，然而动态SQL有时候在执行性能 (效率)上面不如静态SQL，而且使用不恰当，往往会在安全方面存在隐患 (SQL 注入式攻击)。

2、Mybatis动态sql标签有哪些？

(1) if 标签

xml:

<select id="listByUsername" resultType="com.ly.entity.Account">
select
<include refid="ALL_COLUMNS"></include>
from tb_account
where 1 = 1
<if test="username != null and username != ''">
		AND user_name = #{username}
</if>
<if test="username != null and username != ''">
		OR name like CONCAT('%',#{name},'%')
</if>
</select>

select id, user_name, age, birthday, email from tb_account where 1 = 1 AND user_name = ?

当且仅当username属性不为null且不为空字符串时if满足条件凭借username=#

(2) where+if 标签

xml:

<select id="listAgeGt" resultType="com.ly.entity.Account">
	select
	<include refid="ALL_COLUMNS"></include>
	from tb_account
	<where>
		<if test="age != null">
			AND age &gt;= #{age}
		</if>
	</where>
</select>

sql:

select id, user_name, age, birthday, email from tb_account WHERE age >= ?
(3) set 标签

顾名思义set用来设置值，用来修改的
xml:

update tb_account
<set>
	<if test="username != null and username != ''">
		user_name = #{username},
	</if>
</set>
where id = #{id}

sql:

update tb_account SET user_name = ? where id = ?
(4) choose(when,otherwise) 标签

有时候，我们不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 的 switch 语句

xml:

<select id="listAccountsByChoose" resultType="com.ly.entity.Account">
        select
        <include refid="ALL_COLUMNS"></include>
        from tb_account
        <where>
            <choose>
                <when test="id != null">
                    AND id = #{id}
                </when>
                <when test="username != null and username != ''">
                    AND user_name = #{username}
                </when>
                <otherwise>
                    AND age = #{age}
                </otherwise>
            </choose>
        </where>
    </select>

sql:

select id, user_name, age, birthday, email from tb_account WHERE age = ?

这里有三个条件，id、username、age，只能选择一个作为查询条件

  • 如果 id 不为空，那么查询语句为：select * from user where id=?
  • 如果 id 为空，那么看username 是否为空，如果不为空，那么语句为 select * from user where user_name=?;
  • 如果 id 和 username 为空，那么查询语句为 select * from user where age=?
(5) trim 标签

trim标记是一个格式化的标记，可以完成set或者是where标记的功能

①、用trim完成 if+where 语句

xml:

<select id="listAccountsByTrim" resultType="com.ly.entity.Account">
        SELECT
        <include refid="ALL_COLUMNS"></include>
        FROM tb_account
        <trim prefix="where" prefixOverrides="and | or">
            <if test="username != null and username != ''">
                AND user_name = #{username}
            </if>
            <if test="age != null">
                AND age = #{age}
            </if>
        </trim>
    </select>

sql:

select id, user_name, age, birthday, email from tb_account where user_name = ? AND age = ?
  • prefix：前缀
  • prefixoverride：去掉第一个and或者是or
②、用trim完成 if+set 语句

xml:

<update id="updateAccountByTrim">
        update tb_account
        <trim prefix="set" suffixOverrides=",">
            <if test="username != null and username != ''">
                user_name = #{username},
            </if>
            <if test="age != null">
                age = #{age}
            </if>
        </trim>
        where id = #{id}
    </update>

sql:

update tb_account set user_name = ?, age = ? where id = ?
  • suffix：后缀
  • suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）
③、用trim完成 insert 语句

xml:

<insert id="insertAccountByTrim" parameterType="com.ly.entity.Account">
        insert into tb_account
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="username != null and username != ''">user_name,</if>
            <if test="age != null">age,</if>
            <if test="birthday != null and birthday != ''">birthday,</if>
            <if test="email != null and email != ''">email,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="username != null and username != ''">#{username},</if>
            <if test="age != null">#{age},</if>
            <if test="birthday != null and birthday != ''">#{birthday},</if>
            <if test="email != null and email != ''">#{email},</if>
        </trim>
    </insert>

sql:

insert into tb_account ( user_name, age, birthday, email ) values ( ?, ?, ?, ? )

3、Mybatis关联查询

(1) 一对一查询

xml:

<resultMap id="UserResultMap" type="com.ly.entity.User" autoMapping="true">
	<id column="id" property="id"/>
	<result column="user_name" property="userName"/>
	<result column="card_id" property="cardId"/>
	<association property="card" javaType="com.ly.entity.Card">
		<id column="card_id" property="id"/>
		<result column="idNumber" property="idNumber"/>
		<result column="location" property="location"/>
		<result column="createTime" property="createTime"/>
		<result column="updateTime" property="updateTime"/>
	</association>
</resultMap>

<select id="getUser" resultMap="UserResultMap">
	select u.*, c.*
	from tb_user u,
	tb_card c
	where u.card_id = c.id
	and u.id = #{id}
</select>

sql:

select u.*, c.* from tb_user u, tb_card c where u.card_id = c.id and u.id = ?
(2) 一对多查询

查询一个班的多个学生
xml:

<resultMap id="ClassesResultMap" type="com.ly.entity.Classes" autoMapping="true">
        <id column="cId" property="id"/>
        <result column="cName" property="name"/>
        <!--配置Classes对象中students集合的映射-->
        <!--property集合名，ofType 集合中的类型-->
        <collection property="students" ofType="com.ly.entity.Student">
            <id column="sId" property="id"/>
            <result column="sName" property="name"/>
            <result column="cId" property="cId"/>
        </collection>
    </resultMap>

    <select id="oneToMany" resultMap="ClassesResultMap">
        select c.id as cId, c.name as cName, s.id as sId, s.name as sName
        from tb_classes c
                 left join tb_student s on c.id = s.c_id
    </select>

sql:

select c.id as cId, c.name as cName, s.id as sId, s.name as sName from tb_classes c left join tb_student s on c.id = s.c_id
(3) 多对一查询

查询某个学生信息和所在的班级
xml:

 <resultMap id="StudentResultMap" type="com.ly.entity.Student">
        <id column="sId" property="id"/>
        <result column="sName" property="name"/>
        <result column="cId" property="cId"/>
        <association property="classes" javaType="com.ly.entity.Classes">
            <id column="cId" property="id"/>
            <result column="cName" property="name"/>
        </association>
    </resultMap>

    <select id="manyToOne" resultType="com.ly.entity.Student" resultMap="StudentResultMap">
        SELECT s.id     AS sId,
               s.`name` AS sName,
               c.id     AS cId,
               c.NAME   AS cName
        FROM tb_student s
                 LEFT JOIN tb_classes c ON s.c_id = c.id
    </select>

sql:

SELECT s.id AS sId, s.`name` AS sName, c.id AS cId, c.NAME AS cName FROM tb_student s LEFT JOIN tb_classes c ON s.c_id = c.id
(4) 多对多查询

查询用户和角色
xml:

<resultMap id="UserRoleResultMap" type="com.ly.entity.User">
        <id column="uId" property="id"/>
        <result column="uName" property="userName"/>
        <collection property="roles" ofType="com.ly.entity.Role">
            <id column="rId" property="id"/>
            <result column="rName" property="name"/>
        </collection>
    </resultMap>

    <select id="userManyToRoleMany" resultType="com.ly.entity.User" resultMap="UserRoleResultMap">
        SELECT u.id          AS uId,
               u.`user_name` AS uName,
               r.id          AS rId,
               r.NAME        AS rName
        FROM tb_user u
                 LEFT JOIN tb_user_role ur ON u.id = ur.user_id
                 LEFT JOIN tb_role r ON ur.role_id = r.id;
    </select>

    <resultMap id="RoleUserResultMap" type="com.ly.entity.Role">
        <id column="rId" property="id"/>
        <result column="rName" property="name"/>
        <collection property="users" ofType="com.ly.entity.User">
            <id column="uId" property="id"/>
            <result column="uName" property="userName"/>
        </collection>
    </resultMap>

    <select id="roleManyToUserMany" resultMap="RoleUserResultMap">
        SELECT
            u.id AS uId,
            u.`user_name` AS uName,
            r.id AS rId,
            r.NAME AS rName
        FROM
            tb_user u
                RIGHT JOIN tb_user_role ur ON u.id = ur.user_id
                RIGHT JOIN tb_role r ON ur.role_id = r.id
    </select>

sql:

SELECT u.id AS uId, u.`user_name` AS uName, r.id AS rId, r.NAME AS rName FROM tb_user u RIGHT JOIN tb_user_role ur ON u.id = ur.user_id RIGHT JOIN tb_role r ON ur.role_id = r.id


SELECT u.id AS uId, u.`user_name` AS uName, r.id AS rId, r.NAME AS rName FROM tb_user u LEFT JOIN tb_user_role ur ON u.id = ur.user_id LEFT JOIN tb_role r ON ur.role_id = r.id;
posted @   我也有梦想呀  阅读(9)  评论(0)  编辑  收藏  举报
相关博文：
· Mybatis-Flex之QueryWrapper
· MybatisPlus实现高效的多对多查询
· Mybatis中的动态 SQL
· Mybatis之动态SQL使用讲解
· MyBatis 动态 SQL 最全教程，这样写 SQL 太爽了！
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
历史上的今天：
2022-09-20 DataGrip给DateTime类型字段赋值当前系统默认时间
点击右上角即可分享
微信分享提示