面试必问❤️工作中最常用的MyBatis标签，知道最后几种直接录用！

❤️作者主页：温文尔雅的清欢渡
❤️欢迎 点赞 👍 收藏 ⭐ 留言 📝 关注 ✌ 私聊我

前言

面试中，不仅要知道存储引擎、索引、SQL执行流程、底层原理、SQL语句、数据库优化，我们还要熟悉工作中最常用的Mybatis标签。
一些面试官会问除了增删改查还会用什么标签，能不能说一下具体怎么写？一定要答全，具体怎么写的，这个是工作中常用的东西，一定要记得很熟。

一、定义sql语句

select

<select id="queryUser">
   select * from user where id=#{id}
</select>

insert

<insert id = "saveUser">
	insert into user (id,name,age) values (#{id},#{name},#{age})
<insert>

delete

<delete id="deleteUser">
  delete from user where id = #{id}
</delete>

update

<update id="updateUser">
  update user set
    name = #{name},
    sex= #{age}
  where id = #{id}
</update>

二、resultMap

建立SQL查询结果字段与实体属性的映射关系

<resultMap id="BaseResultMap" type="com.bigdata.entity.User">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="userName" column="user_name" jdbcType="VARCHAR"/>
        <result property="address" column="address" jdbcType="VARCHAR"/>
        <result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
        <result property="updateTime" column="update_time" jdbcType="TIMESTAMP"/>
    </resultMap>
  <!--查询时resultMap引用该resultMap -->  
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="Object">
        select id,userName,address,createTime,updateTime from user where id=#{id}
</select> 

三、动态sql拼接

if

<select id="findUserByName" resultType="BaseResultMap">
  SELECT * FROM user 
  <if test="name != null and name != ''">
    WHERE name = #{name}
  </if>
</select>

foreach

<select id="queryUser" resultType="BaseResultMap">
  SELECT * FROM user 
  WHERE ID in
  <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

choose

//choose 相当于Java 中的 switch 语句。
 <select id="findUser" resultType="BaseResultMap">
        SELECT * FROM user WHERE age = 26
        <choose>
            <when test="name != null and name != ''">
                AND name like #{name}
            </when>
            <when test="sex!= null and name != '' ">
                AND sex =#{sex}
            </when>
            <otherwise>
                AND valid = 1
            </otherwise>
        </choose>
    </select>

四、格式化输出

where

<select id="getUserList" resultType="BaseResultMap">
    SELECT * from user 
    <where>
        <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

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

trim

trim相当于where和set

//prefixOverrides 属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的）。
//它的作用是移除所有指定在 prefixOverrides 属性中的内容，并且插入 prefix 属性中指定的内容。
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>
 
//set 元素会动态前置 SET 关键字，同时也会删掉无关的逗号
<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

举个两个栗子

//等效where
<select id="getUserList" parameterType="User" resultMap="BaseResultMap">     
    SELECT * from user 
    <trim prefix="WHERE" prefixOverrides="AND|OR">     
        <if test="userName!=null and userName!='' ">     
            USER_NAME LIKE CONCAT('%', #{userName},'%')      
        </if>     
    </trim>     
</select>   

//等效set
<update id="updateUser" parameterType="User">     
    UPDATE user      
    <trim prefix="SET" suffixOverrides=",">     
      <if test="name !=null and name !='' ">
            NAME = #{name},
        </if>
        <if test="hobby !=null and hobby !='' ">
            HOBBY = #{hobby}
        </if>   
    </trim>     
    WHERE ID = #{id}    
</update>     

bind

//可以创建一个变量并将其绑定到上下文
<select id="selectUser" resultType="User">
  <bind name="userName" value="'%' + user.getUserName() + '%'" />
  SELECT * FROM user
  WHERE name LIKE #{userName}
</select>

五、定义常量和引用

sql

<!-- 查询字段 -->
<sql id="Base_Column_List">
    ID,MAJOR,BIRTHDAY,AGE,NAME,HOBBY
</sql>

<!-- 查询条件 -->
<sql id="Example_Where_Clause">
    where 1=1
    <trim suffixOverrides=",">
        <if test="id != null and id !=''">
            and id = #{id}
        </if>
        <if test="major != null and major != ''">
            and MAJOR = #{major}
        </if>
        <if test="birthday != null ">
            and BIRTHDAY = #{birthday}
        </if>
        <if test="age != null ">
            and AGE = #{age}
        </if>
        <if test="name != null and name != ''">
            and NAME = #{name}
        </if>
        <if test="hobby != null and hobby != ''">
            and HOBBY = #{hobby}
        </if>
        <if test="sorting != null">
            order by #{sorting}
        </if>
        <if test="sort!= null and sort != ''">
            order by ${sort} ${order}
        </if>
    </trim>
</sql>

include

<select id="selectAll" resultMap="BaseResultMap">
    SELECT
    <include refid="Base_Column_List" />
    FROM student
    <include refid="Example_Where_Clause" />
</select>

六、比较写法

原符号       <        <=      >       >=       &        '        "
替换符号    &lt;    &lt;=   &gt;    &gt;=   &amp;   &apos;  &quot;


大于等于
<![CDATA[ >= ]]>
小于等于
<![CDATA[ <= ]]>

比较timestamp

<select id="getUser" resultType="java.lang.String" >
		select name from user 
		where birthday &lt; TO_TIMESTAMP(#{start}, 'yyyy-mm-dd hh24:mi:ss') 
		and birthday &gt;= TO_TIMESTAMP(#{end}, 'yyyy-mm-dd hh24:mi:ss') order by start desc LIMIT 1;
	</select>