Mybatis中Mapper.xml详解

转载：https://blog.csdn.net/weixin_40483369/article/details/127763075

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

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

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

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

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

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

<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 ( ? , ? , ? , ? )

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

/**

批量插入用户信息
*
@param userList
@return
*/
int insertList(List<SysUser> userList);

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

<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

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

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

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

SELECT * FROM BLOG WHERE

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

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