Mybatis-plus实现多表查询

两个表: notice_send保存消息发送数据, notice_content保存具体消息内容
sql建表语句如下:

CREATE TABLE `notice_send` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `content_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '消息内容ID',
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户ID',
  `receive_object` varchar(200) NOT NULL DEFAULT '' COMMENT '接收对象',
  `type` int(11) NOT NULL DEFAULT '0' COMMENT '消息类型 1站内信2邮件3短信5微信6推送',
  `gmt_create` datetime DEFAULT NULL COMMENT '创建时间',
  `gmt_send` datetime DEFAULT NULL COMMENT '发送时间',
  `is_send` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否已发送',
  `gmt_received` datetime DEFAULT NULL COMMENT '接收时间',
  `gmt_read` datetime DEFAULT NULL COMMENT '阅读时间',
  `is_read` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否已读',
  `user_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '用户删除标记',
  `gmt_modified` datetime DEFAULT NULL COMMENT '修改时间',
  `is_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '逻辑删除标记',
  PRIMARY KEY (`id`),
  KEY `notice_send_content_id_index` (`content_id`),
  KEY `notice_send_user_id_index` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12462 DEFAULT CHARSET=utf8mb4 COMMENT='消息发送列表'

CREATE TABLE `notice_content` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '标题',
  `content` VARCHAR(2000) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '消息内容',
  `app_url` VARCHAR(500) NOT NULL DEFAULT '' COMMENT 'APP跳转地址',
  `gmt_create` DATETIME DEFAULT NULL COMMENT '创建时间',
  `is_deleted` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=11733 DEFAULT CHARSET=utf8mb4 COMMENT='消息内容'

要做两表左连接查询, sql语句如下:

SELECT 
ns.id,ns.content_id,nc.title,nc.content, nc.app_url,ns.gmt_create,ns.gmt_send,ns.user_id, ns.receive_object,ns.type,ns.is_send,ns.gmt_received, ns.gmt_read,ns.user_deleted,ns.gmt_modified,ns.is_deleted 
FROM notice_send ns 
LEFT JOIN notice_content nc 
ON ns.content_id = nc.id 
ORDER BY ns.id DESC

项目中使用mybatis-plus实现多表查询

-0 引入依赖

如果是springboot项目需要starter web, 读取数据库需要数据库对应的依赖和设置, 此处省略.

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.70</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>

-1 建实体类

• 基础DO

import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;

/**
 * 基础DO
 */

@Data
public class BaseDO {

    /**
     * 索引页
     */
    @TableField(exist = false)
    private Integer page = 1;

    /**
     * 每页条数
     */
    @TableField(exist = false)
    private Integer limit = 10;

    /**
     * 搜索条件
     */
    @TableField(exist = false)
    private String strSearch = "";

    /**
     * 开始时间
     */
    @TableField(exist = false)
    private String strBeginTime;

    /**
     * 结束时间
     */
    @TableField(exist = false)
    private String strEndTime;

    /**
     * 自定义排序
     */
    @TableField(exist = false)
    private String orderBy = "id desc";
}

• NoticeSendDO

/**
 * 消息发送列表
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("notice_send")
public class NoticeSendDO extends BaseDO implements Serializable {

    private static final long serialVersionUID = 1L;

    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    /** * 消息内容ID */
    private Long contentId;

    /** * 消息标题 */
    @TableField(exist = false)
    private String title;

    /** * 消息内容 */
    @TableField(exist = false)
    private String content;

    /** * APP跳转地址 */
    @TableField(exist = false)
    private String appUrl;

    /** * 用户ID */
    private Long userId;

    /** * 接收对象 */
    private String receiveObject;

    /** * 消息类型 1站内信2邮件3短信5微信6推送 */
    private Integer type;

    /** * 创建时间 */
    @TableField(value = "gmt_create", fill = FieldFill.INSERT)
    private String gmtCreate;

    /** * 发送时间 */
    private String gmtSend;

    /** * 是否已发送 */
    private Integer isSend;

    /** * 接收时间 */
    private String gmtReceived;

    /** * 阅读时间 */
    private String gmtRead;

    /** * 是否已读 */
    private Integer isRead;

    /** * 用户删除标记 */
    private Integer userDeleted;

    /** * 修改时间 */
    @TableField(value = "gmt_modified", fill = FieldFill.INSERT_UPDATE)
    private String gmtModified;

    /** * 逻辑删除标记 */
    @TableLogic
    private Integer isDeleted;

}

-2 Mapper接口

/**
 * 消息发送列表 Mapper 接口
 */
public interface NoticeSendMapper extends BaseMapper<NoticeSendDO> {

    @Select("SELECT ns.id,ns.content_id,nc.title,nc.content," +
            "nc.app_url,ns.gmt_create,ns.gmt_send,ns.user_id," +
            "ns.receive_object,ns.type,ns.is_send,ns.gmt_received," +
            "ns.gmt_read,ns.user_deleted,ns.gmt_modified,ns.is_deleted " +
            "FROM notice_send ns " +
            "LEFT JOIN notice_content nc" +
            " ON ns.content_id = nc.id " +
            "ORDER BY ns.id DESC")
    public List<NoticeSendDO> getNoticesWithContents(Page<NoticeSendDO> page);
}

-3 NoticeService中的方法

    public JSONObject getMgrPageList(NoticeSendDO noticeSendDO) {
        JSONObject jsonObject = new JSONObject();
        try {
            Page<NoticeSendDO> page = new Page<>();
            page.setCurrent(noticeSendDO.getPage());
            page.setSize(noticeSendDO.getLimit());
            List<NoticeSendDO> list = noticeSendMapper.getNoticesWithContents(page);
            jsonObject.put("total", page.getTotal());
            jsonObject.put("page", page.getCurrent());
            jsonObject.put("limit", page.getSize());
            jsonObject.put("list", list);
            return jsonObject;
        } catch (Exception e) {
            log.error("get noticeSend page list error:{}", e.getMessage());
            throw new DBException();
        }
    }

-4 NoticeController中的方法

    @RequestMapping(value = "/getNoticeList", method = RequestMethod.POST)
    public R getAdminRoleList(@RequestBody NoticeSendDO noticeSendDO) {
        try {
            return R.ok().put("data", noticeSendService.getMgrPageList(noticeSendDO));
        } catch (Exception e) {
            return R.error();
        }
    }
}

这样就可以使用了. 谢谢收看!