Mybatis中Mapper.xml详解

Mybatis中Mapper.xml详解

SQL 映射文件只有很少的的几个（按照被定义的顺序列出）

• cache - 对给定命名空间的缓存配置
• cache-ref - 对给定的命名空间缓存配置的引用
• resultMap - 是最复杂也是最强大的元素，用来描述如何从数据库结果集中来加载对象
• sql - 可被其他语句引用的可重用语句块
• insert - 映射插入语句
• update - 映射更新语句
• delete - 映射删除语句
• select - 映射查询语句

官网地址：https://mybatis.net.cn/sqlmap-xml.html

1.select

• 查询语句是最长用的元素之一，是用来查询数据。它复杂的点在于查询结果集的映射，但是经过MyBaits 的处理一切都变得非常的简单，比如：

<select id="findById"  parametreType="int" resultType="blog">
    select * from blog where `id` = #{id}
</select>

2.parameterType

可选标签。参数类的完全限定名或别名，如果是传参是对象，这是对象的绝对路径，例如：parameterType=“com.hjt.userInfo.entity.SysMenu”。如果是基本数据类型，就直接写。例如：parameterType=“Long”,parameterType=“java.util.List”

3.resultType

非必选标签。注意这里的非选是因为resultType和resultMap不能并存，两者能且只能选择一个。主要是用来定义一个返回结果集对象的全限定名或者别名。

4.resultMap

非必选标签。注意这里的非选是因为resultType和resultMap不能并存，两者能且只能选择一个。

resultMap类型的结果集映射，也就是返回值类型（比如接收类型是一个对象：resultMap=“SysMenuResult”）

<resultMap type="com.hjt.userInfo.entity.SysDept" id="SysDeptResult">
      <id     property="deptId"     column="dept_id"     />
      <result property="parentId"   column="parent_id"   />
      <result property="ancestors"  column="ancestors"   />
      <result property="deptName"   column="dept_name"   />
      <result property="orderNum"   column="order_num"   />
      <result property="leader"     column="leader"      />
      <result property="phone"      column="phone"       />
      <result property="email"      column="email"       />
      <result property="status"     column="status"      />
      <result property="delFlag"    column="del_flag"    />
      <result property="createBy"   column="create_by"   />
      <result property="createTime" column="create_time" />
      <result property="updateBy"   column="update_by"   />
      <result property="updateTime" column="update_time" />
   </resultMap>

也可以返回值是基本数据类型 eg: resultType=“Integer”

5.insert

<!-- 新增 -->
<insert id="insert" parameterType="blog">
    insert into blog (`id`, `name`, `title`, `content`)
    values (#{id}, #{name}, #{title}, #{content})
</insert>

6.update

<!-- 更新 -->
<update id="update" parameterType="blog">
    update blog
    set `name` = #{name}, `title` = #{title}, `content` = #{title}
    where id = #{id}
</update>

7.delete

<!-- 删除 -->
<delete id="delete" parameterType="int">
    delete from blog where id = #{id}
</delete>

8.forearch

collection	表示迭代集合的名称
item	表示本次迭代获取的元素，若collection为List、Set或者数组，则表示其中的元素；若collection为map，则代表key-value的value，该参数为必选
open	表示该语句以什么开始，最常用的是左括弧’(’，注意:mybatis会将该字符拼接到整体的sql语句之前，并且只拼接一次，该参数为可选项
close	表示该语句以什么结束，最常用的是右括弧’)’，注意:mybatis会将该字符拼接到整体的sql语句之后，该参数为可选项
separator	mybatis会在每次迭代后给sql语句append上separator属性指定的字符，该参数为可选项
index	在list、Set和数组中,index表示当前迭代的位置，在map中，index代指是元素的key，该参数是可选项。

8.1 forearch批量查询

	<select id="testSelectAllSysDeptByDeptId"  resultType="com.hjt.userInfo.entity.SysDept">
		<include refid="selectDeptVo"></include>
		where d.dept_id in
		<foreach collection="deptIds" item="item" 
				 separator="," open="(" close=")">
			#{item}
		</foreach>

	</select>

​

/***
 * 根据deptId查询全部
 * @param deptIds split(',')
 * @return
 */
List<SysDept> testSelectAllSysDeptByDeptId(@Param("deptIds") List<Long> deptIds);

实际执行的样子：

==>  Preparing: select d.dept_id, d.parent_id, d.ancestors, d.dept_name, d.order_num, d.leader, d.phone, d.email, d.status, d.del_flag, d.create_by, d.create_time from sys_dept d where d.dept_id in ( ? , ? , ? , ? )

先看第一个foreach：
1、是在括号里有多个参数，所以要open="(“开始，close=”)“结束，separator=”,"作为中间的分隔

2.@Param(“deptIds”) List deptIds中的 deptIds要和 <foreach collection=“deptIds” 中的deptIds 对应

/**

批量插入用户信息
*

@param userList

@return
*/
int insertList(List<SysUser> userList);

8.2 forearch批量插入

<insert id="insertList" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO sys_user(user_name, user_password, user_email, user_info, head_img, create_time)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.userName},#{user.userPassword},#{user.userEmail},#{user.userInfo},#{user.headImg,jdbcType=BLOB},#{user.createTime,jdbcType=TIMESTAMP})
    </foreach>
</insert>

DEBUG [main] - ==> Preparing: INSERT INTO sys_user(user_name, user_password, user_email, user_info, head_img, create_time) VALUES (?,?,?,?,?,?) , (?,?,?,?,?,?)

8.3 forearch批量修改

<update id="updateDeptNameById" parameterType="java.util.List">
   update sys_dept set dept_name =
    <foreach collection="sysDeptDtos" item="item" open="case dept_id" close="end"
           separator=" ">
    when #{item.deptId} then #{item.deptName}
    </foreach>
    where dept_id in
    <foreach collection="sysDeptDtos" item="item" index="index"
           separator="," open="(" close=")">
       #{item.deptId}
    </foreach>

</update>

/***
 * 批量修改
 * @param sysDeptDtos
 */
void updateDeptNameById(@Param("sysDeptDtos")List<SysDeptDto> sysDeptDtos);

实际执行语句

==> Preparing: update sys_dept set dept_name = case dept_id when ? then ? when ? then ? end where dept_id in ( ? , ? )
> Parameters: 366(Long), 测试3(String), 367(Long), 测试4(String), 366(Long), 367(Long)
< Updates: 2

9.include

事先写好sql语句

<sql id="selectMenuVo">
       select menu_id, menu_name, parent_id, order_num, path, name, component, iframe_url, is_frame, is_cache, menu_type, visible, status, ifnull(perms,'') as perms, icon, create_time
   from sys_menu
   </sql>

eg:

<select id="checkMenuNameUnique" parameterType="com.hjt.userInfo.entity.SysMenu" resultMap="SysMenuResult">
   <include refid="selectMenuVo"/>
   where menu_name=#{menuName} and parent_id = #{parentId} limit 1
</select>

10.if

<update id="updateDept" parameterType="com.hjt.userInfo.entity.SysDept">
      update sys_dept
      <set>
         <if test="parentId != null and parentId != 0">parent_id = #{parentId},</if>
         <if test="deptName != null and deptName != ''">dept_name = #{deptName},</if>
         <if test="ancestors != null and ancestors != ''">ancestors = #{ancestors},</if>
         <if test="orderNum != null and orderNum != ''">order_num = #{orderNum},</if>
         <if test="leader != null">leader = #{leader},</if>
         <if test="phone != null">phone = #{phone},</if>
         <if test="email != null">email = #{email},</if>
         <if test="status != null and status != ''">status = #{status},</if>
         <if test="updateBy != null and updateBy != ''">update_by = #{updateBy},</if>
         update_time = sysdate()
      </set>
      where dept_id = #{deptId}
</update>

11.trim

mybatis的trim标签一般用于去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。

属性 描述
prefix 给sql语句拼接的前缀
suffix 给sql语句拼接的后缀
prefixOverrides 去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"
suffixOverrides 去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定

首先来看一个问题

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG 
  WHERE 
  <if test="state != null">
    state = #{state}
  </if> 
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

如果这些条件没有一个能匹配上会发生什么？最终这条 SQL 会变成这样：

SELECT * FROM BLOG WHERE

可以用where标签或者trim标签来解决这个问题

<trim prefix="WHERE" prefixOverrides="AND">
	<if test="state != null">
	  state = #{state}
	</if> 
	<if test="title != null">
	  AND title like #{title}
	</if>
	<if test="author != null and author.name != null">
	  AND author_name like #{author.name}
	</if>
</trim>

也可以用下面的where来解决

12.where

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG 
  <where> 
    <if test="state != null">
         state = #{state}
    </if> 
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

13 复杂查询

如果遇到需要自定义写的vo,并且还要分页，可以这种写法。

IPage<SysEmpListVo> queryKpy(Page<SysEmpListVo> page, @Param("orgId") Long orgId, @Param("kpyRoleCode") String kpyRoleCode, @Param("phone") String phone, @Param("userName") String userName);

SysEmpListVo这个就是自定义的vo类

对应的sql语句如下：

sys_emp是员工表，top_org_id是企业id，sr.code是对于的角色

<select id="queryKpy" resultType="vip.xiaonuo.sys.modular.emp.dto.SysEmpListVo">
    select
        se.id,
        se.user_id as userId ,
        su.phone ,
        su.name ,
        su.status ,
        se.create_time
        #vo主要接收字段写在前面
    from
        sys_emp se
            join sys_user su on
            se.user_id = su.id
            
    where
        top_org_id = #{orgId}
      and 
      #--------------用户必须拥有该角色 ------------
      exists (
            select
                *
            from
                sys_role sr
                    join sys_user_role sur on
                    sr.id = sur.role_id
            where
                sr.code = #{kpyRoleCode}
              and sr.org_id = #{orgId}
              and sur.user_id = se.user_id
      )
       #---------用户必须拥有该角色-------------
       #查询的结果再筛选
      <if test="phone != null and phone !=''">
          and su.account = #{phone}
      </if>
        <if test="userName != null and userName !=''">
            and su.name like concat('%',#{userName},'%')
        </if>
    order by se.create_time desc
</select>

14.标签

跟if-else一样。如果qo.ywlx不为空就拼接and t1.id in。如果为空则拼接 and hg.id in

<choose>
    <when test="qo.ywlx !=null and qo.ywlx !=''">
        and t1.id in
    </when>
    <otherwise>
        and hg.id in
    </otherwise>
</choose>

14 Mysql中find_in_set()函数用法详解以及使用场景

Find_IN_SET 是精确匹配，字段值会以英文”,”分隔，
eg:

SELECT * FROM `smkj_data`.`bs_zero_code_model_management`
WHERE FIND_IN_SET('1776862913617752066', REPLACE(`model_type_id`, ' ', '')) > 0;

就算是字段只存一个也可以查出来

如果传的是数组数组，这时候可以这样查询 该sql表示数组中任意一个匹配到数据就输出

@Param("modelTypeIdsArrays") List<String> modelTypeIdsArrays

<if test="modelTypeIdsArrays!=null">
    and
    <foreach collection="modelTypeIdsArrays" item="item"
             separator="or">
        FIND_IN_SET(#{item}, model_type_id) > 0
    </foreach>

</if>

15 数据库字段显示 xxxxx -------> xxxxxx

eg:

可以用这种形式展示

16.遇到某个指定的指可能为null的时候，这时候如果你要给他赋值是0的话。

COALESCE(co.refund_amount, 0)  as refundAmount,