Mybatits-plus实现简单的多表联查和分页操作

清疚 2023-07-16 07:57 3阅读 0赞

# 一、前言 #

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

# 二、JavaBean #

> 1.  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;
    
    }

> 1.  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;
        }
    
    }

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

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

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

> 四、方法调用结果

@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
      }
    ]

结果是可以的，希望可以帮到任何一个人，那这篇文章就有意义了。