一、前言

mybatits-plus提供了简单的curd单表操作API,可以使用多样的方式实现。但是多表联查的时候，我遇到了一些困难，不可以使用通过 Lambda 表达式，方便的编写各类查询条件，无需再担心字段写错。于是就回归到了Mapper.xml文件，这种方法实现起来简单，也可以实现动态sql，非常方便。

二、JavaBean

CertInfo

package com.infosec.ra.common.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableField;
import java.io.Serializable;
import lombok.*;
import lombok.experimental.Accessors;
/**
 * <p>
 * 
 * </p>
 *
 * @author javacfox
 * @since 2019-12-02
 */
@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = false)
@TableName("CERTINFO")
public class Certinfo implements Serializable {
    private static final long serialVersionUID=1L;
    @TableId(value = "Id", type= IdType.AUTO)
    private Integer id;
    @TableField("SUBJECT")
    private String subject;
    @TableField("CERTTYPE")
    private String certtype;
    @TableField("NOTBEFORE")
    private Long notbefore;
    @TableField("NOTAFTER")
    private Long notafter;
    @TableField("STATUS")
    private Integer status;
    @TableField("TEMPNAME")
    private String tempname;
    @TableField("CANAME")
    private String caname;
    @TableField("UUID")
    private String uuid;
    @TableField("REQHASH")
    private String reqhash;
    @TableField("ISAUDIT")
    private Integer isaudit;
    @TableField("ADDTIME")
    private Long addtime;
}

Audit

package com.infosec.ra.common.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableField;
import java.io.Serializable;
import com.infosec.ra.common.RAConstant;
import com.infosec.ra.common.dto.receive.CertAuditDto;
import lombok.*;
import lombok.experimental.Accessors;
/**
 * <p>
 * 
 * </p>
 *
 * @author javacfox
 * @since 2019-12-02
 */
@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = false)
@TableName("AUDIT")
public class Audit implements Serializable {
    private static final long serialVersionUID=1L;
    @TableId(value = "Id", type= IdType.AUTO)
    private Integer id;
    @TableField("CERTID")
    private Integer certid;
    @TableField("OPRTYPE")
    private Integer oprtype;
    @TableField("RESULT")
    private Integer result;
    @TableField("AUDITOR")
    private String auditor;
    public Audit(CertAuditDto certBaseDto) {
        this.certid = certBaseDto.getId();
        this.result = certBaseDto.getResult();
        this.auditor = certBaseDto.getAuditor();
    }
    public static Audit getInstant(int certId,int oprType){
        Audit audit = new Audit();
        audit.certid = certId;
        //证书作废
        audit.oprtype = oprType;
        return audit;
    }
}

CertInfoVO

package com.infosec.ra.common.dto.response;
import com.baomidou.mybatisplus.annotation.TableField;
import com.infosec.ra.common.entity.Certinfo;
import io.swagger.annotations.ApiModel;
import lombok.*;
/**
 * @author Javacfox
 */
@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = false)
@ApiModel("证书信息表类")
public class CertInfoVO extends Certinfo {
    private Integer oprtype;
    private Integer result;
    private String auditor;
}

三、CetnInfoMapper

直奔主题吧，service和controller都比较简单，直接跳到mapper.

mapper类

package com.infosec.ra.mapper;
import com.infosec.ra.common.dto.receive.QueryTerminalCertDto;
import com.infosec.ra.common.dto.response.CertInfoVO;
import com.infosec.ra.common.entity.Certinfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import java.util.List;
/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author javacfox
 * @since 2019-12-02
 */
public interface CertinfoMapper extends BaseMapper<Certinfo> {
    /**
     * 查询证书信息
     * @param certBaseDto 查询条件
     * @return 证书列表信息
     */
    List<CertInfoVO> selectCertInfoVO(QueryTerminalCertDto certBaseDto);
    /**
     * 查询总数
     * @param certDto 查询条件
     * @return 信息总条数
     */
    Integer selectCount(QueryTerminalCertDto certDto);
}

2.编写这两个方法的意义在于分页查询，有人就说了，mybatits-plus提供了简便的分页方法，怎么不使用呢？

我也使用过原配的分页插件，但是当数据量达到千万或者一亿条的时候，数据无法查询出来，而且爆出内存溢出的错误。后来我查看了一下源码，其原理是设置了一个拦截器，将数据库返回的结果集再在拦截其中做处理，达到分页的效果，不是真正意义上的数据库分页，所以不得以使用了这两个方法。

3.mapper.xml(本文的核心)

<?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.infosec.ra.mapper.CertinfoMapper">
    <resultMap id="CertInfoVO" type="com.infosec.ra.common.dto.response.CertInfoVO">
        <result column="Id" property="id"/>
        <result column="SUBJECT" property="subject"/>
        <result column="CERTTYPE" property="certtype"/>
        <result column="NOTBEFORE" property="notbefore"/>
        <result column="NOTAFTER" property="notafter"/>
        <result column="STATUS" property="status"/>
        <result column="TEMPNAME" property="tempname"/>
        <result column="CANAME" property="caname"/>
        <result column="UUID" property="uuid"/>
        <result column="REQHASH" property="reqhash"/>
        <result column="ISAUDIT" property="isaudit"/>
        <result column="ADDTIME" property="addtime"/>
        <result column="OPRTYPE" property="oprtype"/>
        <result column="RESULT" property="result"/>
        <result column="AUDITOR" property="auditor"/>
    </resultMap>
    <select id="selectCertInfoVO" parameterType="com.infosec.ra.common.dto.receive.QueryTerminalCertDto" resultMap="CertInfoVO">
      select
          *
        FROM
        CERTINFO c
        LEFT JOIN
          AUDIT a
            ON
                c.id = a.certid
        <where>
            <if test="isAudit != null">
                c.isAudit = #{isAudit}
            </if>
            <if test="subject != null">
                and c.subject like concat('%',#{subject},'%')
            </if>
            <if test="uuid != null">
                and c.uuid like concat('%',#{uuid},'%')
            </if>
            <if test="oprType != null">
                and a.OPRTYPE != 2
            </if>
        </where>
        limit #{pageNum},#{pageSize}
    </select>
    <select id="selectCount" parameterType="com.infosec.ra.common.dto.receive.QueryTerminalCertDto" resultType="Integer">
        select
          count(*)
        FROM
        CERTINFO c
        LEFT JOIN
        AUDIT a
        ON
        c.id = a.certid
        <where>
            <if test="isAudit != null">
                c.isAudit = #{isAudit}
            </if>
            <if test="subject != null">
                and c.subject like concat('%',#{subject},'%')
            </if>
            <if test="uuid != null">
                and c.uuid like concat('%',#{uuid},'%')
            </if>
            <if test="oprType != null">
                and a.OPRTYPE != 0
            </if>
        </where>
    </select>
</mapper>

编写resultMap(结果映射),property对应CerInfoVO中的属性，column对应数据库表中的表字段，VO里面整合了证书信息和审核信息（通过证书id整合起来）。
使用*会影响效率，可以使用标签来标识具体的字段信息，然后再在语句中引用。

四、方法调用结果

@PostMapping("/test")
public List<CertInfoVO> test(@RequestBody QueryTerminalCertDto queryTerminalCertDto){
    List<CertInfoVO> certInfoVO = certinfoMapper.selectCertInfoVO(queryTerminalCertDto);
    return certInfoVO;
}

结果展示：

[
  {
    "id": 13,
    "subject": "c=tsds",
    "certtype": "11",
    "notbefore": null,
    "notafter": null,
    "status": 4,
    "tempname": "EC",
    "caname": "EA1",
    "uuid": "ASRVVHAXKSAHIKSJDO",
    "reqhash": "",
    "isaudit": 1,
    "addtime": 1584015122925,
    "oprtype": 1,
    "result": 0,
    "auditor": "dasghdgash"
  },
  {
    "id": 15,
    "subject": "c=tsdsf",
    "certtype": "00",
    "notbefore": null,
    "notafter": null,
    "status": 0,
    "tempname": "EC",
    "caname": "AA",
    "uuid": "hdsgadgsa",
    "reqhash": "",
    "isaudit": 0,
    "addtime": 1584015175031,
    "oprtype": null,
    "result": null,
    "auditor": null
  }
]