一、前言
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 } ]
结果是可以的，希望可以帮到任何一个人，那这篇文章就有意义了。
