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