mapper文件模板

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fan.mapper.ModuleMapper">

</mapper>

关于mapper和xml

首先会定义一个mapper接口，这里操作简单也可以使用增删改查的注解完成操作，但是通常是不推荐的，在有些情况下（如表字段和实体字段不对应的情况），就不好处理

正常情况下都会对应一个mapper文件（不好对应的可以安装MybatisX插件，就能直接跳转），然后定义好表和实体类字段的对应关系

注意：这步最好不要省略，在以后的迭代中不保证出现字段不一致的情况，在这里修改就很方便

提醒：

desc、key、read是关键字，不在出现在表中
MybatisX插件可以生成该文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fan.mapper.ReportRecordMapper">
    <resultMap id="BaseResultMap" type="com.lhxn.model.course.ReportRecord">
        <id property="id" column="id" jdbcType="BIGINT"/>
        <result property="uid" column="uid" jdbcType="BIGINT"/>
        <result property="reportId" column="report_id" jdbcType="BIGINT"/>
        <result property="reportTypeName" column="report_type_name" jdbcType="VARCHAR"/>
        <result property="operationId" column="operation_id" jdbcType="BIGINT"/>
        <result property="type" column="type" jdbcType="INTEGER"/>
        <result property="desc" column="content" jdbcType="VARCHAR"/>
        <result property="status" column="status" jdbcType="INTEGER"/>
        <result property="handleDesc" column="handle_desc" jdbcType="VARCHAR"/>
        <result property="handleResult" column="handle_result" jdbcType="INTEGER"/>
        <result property="picUrl" column="pic_url" jdbcType="VARCHAR"/>
        <result property="createDate" column="create_date" jdbcType="TIMESTAMP"/>
        <result property="updateDate" column="update_date" jdbcType="TIMESTAMP"/>
        <result property="processorReportResult" column="processor_report_result" jdbcType="VARCHAR"/>
    </resultMap>


</mapper>

对象返回（一对一）

有些场景需要返回对象中还有对象的情况，这样的逻辑确实比全部字段放在一个对象中清晰，能更方便的知道哪个字段属于哪个对象

<!--这里的id与查询的resultMap对应，type与接收的实体类对象-->
<resultMap id="getMessageDynamicListMap" type="com.model.MessageDynamicInfo">
    <id column="id" property="id"/>
    <result column="user_id" property="userId"/>
    <result column="text" property="text"/>
    <result column="photos" property="photos"/>
    <!--这是映射对象中的对象，property为对象中的对象的字段，javaType为实体类-->
    <association property="user" javaType="com.model.User">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="real_name" property="realName"/>
        <result column="avatar" property="avatar"/>
    </association>
</resultMap>

<select id="selectMessageList" resultMap="getMessageDynamicListMap">
    <!--SQL语句-->
    SELECT xxx
</select>

对象返回（一对多）

推荐子查询实现

<!--对象映射-->
<resultMap id="selectHostelInfoDetailsResultMap" type="com.springmvc.model.HostelInfoDetailsVo">
    <id column="id" property="id"/>
    <result column="user_id" property="userId"/>
    <result column="hostel_name" property="hostelName"/>
    <result column="username" property="linkUser"/>
    <result column="province" property="province"/>
    <result column="city" property="city"/>
    <result column="county" property="county"/>
    <result column="city_code" property="cityCode"/>
    <result column="address" property="address"/>
    <result column="authenticate" property="authenticate"/>
    <result column="create_time" property="createTime"/>
    <result column="update_time" property="updateTime"/>
    <!--将主表的字段传给子查询-->
    <collection property="imgList" ofType="com.springmvc.model.HostelInfoImgVo" column="{hostelId=id}" select="selectHostelInfoDetailsImg">
        <id column="id" property="id"/>
        <result column="url" property="url"/>
    </collection>
</resultMap>
<!--主表查询-->
<select id="selectHostelInfoDetails" resultMap="selectHostelInfoDetailsResultMap">
    select *
    from sw_hostel_info
    where sw_hostel_info.id = #{hostelId}
</select>
<!--子查询-->
<select id="selectHostelInfoDetailsImg" resultType="com.springmvc.model.HostelInfoImgVo">
    select *
    from sw_hostel_info_img
    where hostel_id = #{hostelId} and status = 1
</select>

关于查询别名

刚开始的时候不觉得，后来发现关联表查询时，使用别名能更好的区分获取了哪张表的哪个字段

SELECT t1.id,
       t1.user_id,
       t1.text,
       t1.photos,
       t1.from_oid,
       t1.share_num,
       t1.viewing_count,
       t1.file_type,
       t2.id as uid,
       t2.username,
       t2.real_name,
       t2.avatar,
       t2.type
FROM message_dynamic t1,
     `user` t2
WHERE t1.user_id = t2.id
  AND t1.`status` = 0
  AND t1.from_oid = #{circleId}
ORDER BY t1.id DESC LIMIT #{skip}, #{limit}

关于点赞和总数

SELECT t1.id,
       t1.user_id,
       t1.text,
       t2.id as uid,
       t2.username,
       <!--查询点赞数-->
       (SELECT count(1) FROM dynamic_like WHERE dynamic_like.m_id = t1.id)                         as likeNum,
       <!--查询自己是否点赞-->
       (SELECT count(1) FROM dynamic_like WHERE dynamic_like.m_id = t1.id AND user_id = #{userId} AND dynamic_like.m_type = 1) as `like`
FROM message_dynamic t1,
     `user` t2
WHERE t1.user_id = t2.id
  AND t1.`status` = 0
  AND t1.from_oid = #{circleId}
ORDER BY t1.id DESC LIMIT #{skip}, #{limit}

查询存在优化：SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1

xml中使用对象参数

mapper

/**
 * 添加管理员信息
 */
Integer insertAdminInfo(@Param("admin") Admin admin);

这里注解不可少，同时注意该注解在import org.apache.ibatis.annotations.Param;包下

xml

<!--添加管理员信息-->
<insert id="insertAdminInfo">
    insert into admin(username, password, nick)
    values (#{admin.username}, #{admin.password}, #{admin.nick})
</insert>

if标签

<select id="selectList" resultType="com.springmvc.model.HostelGuestRegisterVo">
    select *
    from sw_hostel_guest_user t1
    where t1.hostel_id = #{hostelId}
    <if test="userId != null and userId != ''">
        and user_id = #{userId}
    </if>
</select>

like拼接

<if test="keyword != null and keyword != ''">
    and keyword like CONCAT('%',#{keyword},'%')
</if>

日期大小

不要加!=''，会报错。

<if test="hostelListDto.registerTimeStart != null">
    and t1.last_operate_time &gt; #{hostelListDto.registerTimeStart}
</if>

列表查询示例（直接计算分页）

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lky.entity.manage.SysRole;
import com.lky.vo.system.SysRoleListReqVo;
import com.lky.vo.system.SysRoleListRespVo;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * 系统角色
 */
public interface SysRoleMapper extends BaseMapper<SysRole> {

    /**
     * 列表总数
     */
    Long listCount(@Param("params") SysRoleListReqVo params);

    /**
     * 列表
     */
    List<SysRoleListRespVo> listSelect(@Param("params") SysRoleListReqVo params);
    
}

// 注意@Param的包不要引错



<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lky.mapper.SysRoleMapper">

    <select id="listCount" resultType="java.lang.Long">
        select count(id) from sys_role
        where status = 1
        <if test="params.keyword != null and params.keyword != ''">
            and name like CONCAT('%',#{params.keyword},'%')
        </if>
    </select>

    <select id="listSelect" resultType="com.lky.vo.system.SysRoleListRespVo">
        select * from sys_role
        where status = 1
        <if test="params.keyword != null and params.keyword != ''">
            and name like CONCAT('%',#{params.keyword},'%')
        </if>
        limit ${(params.pageIndex-1)*params.pageSize}, #{params.pageSize}
    </select>

</mapper>

when标签

<select id="selectOrderList" parameterType="Order" resultMap="OrderResult">
    <include refid="selectOrderVo"/>
    <where>
        <if test="id != null  and id != ''">and id = #{id}</if>
        <if test="unitName != null  and unitName != ''">and unit_name like concat('%', #{unitName}, '%')</if>
        <if test="unitUser != null  and unitUser != ''">and unit_user = #{unitUser}</if>
        <if test="unitPhone != null  and unitPhone != ''">and unit_phone = #{unitPhone}</if>
        <choose>
            <when test="status == -1">
                and status != 1
            </when>
            <when test="status == null">

            </when>
            <otherwise>
                and status = #{status}
            </otherwise>
        </choose>
        <if test="payStatus != null ">and pay_status = #{payStatus}</if>
    </where>
</select>