Mybatis-Plus 实现有中间表的多表关联查询带分页

需求，根据文章分类id查询文章列表

一、已有表

文章表

id	name
29	cccccq333
30	dddddc

文章分类表

id	name
4	测试
5	测试1

关系映射表

arctile_id	category_id
29	4
30	4

二、Mybatis-plus分页配置

package com.synda.mobile.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * @description 分页配置
 * @auther changq
 */
@Configuration
public class MyBatisPlusConfig {
    /**
     * 分页插件
     * @return
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        // paginationInterceptor.setOverflow(false);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        // paginationInterceptor.setLimit(500);
        return paginationInterceptor;
    }
}

三、代码实现

Mapper.java

package com.synda.mobile.articleformobile.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.synda.mobile.articleformobile.entity.CmsArticle;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 文章表 Mapper 接口
 * </p>
 *
 * @author changq
 */
@Mapper
public interface CmsArticleMapper extends BaseMapper<CmsArticle> {
	@Select("select a.* from (select * from cms_article_category where pid = #{categoryId} or id=#{categoryId}) c join cms_article_category_mapping ac on c.id=ac.category_id join cms_article a on ac.article_id=a.id")
	Page<CmsArticle> getByCategoryId(Page<CmsArticle> page,@Param("categoryId") String categoryId);
}

service.java

package com.synda.mobile.articleformobile.service;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import com.synda.mobile.articleformobile.entity.CmsArticle;

/**
 * <p>
 * 文章表 服务类
 * </p>
 *
 * @author changq
 */
public interface ICmsArticleService extends IService<CmsArticle> {
	Page<CmsArticle> getByCategoryId(Page<CmsArticle> page,String categoryId);

}

serviceimpl.java

package com.synda.mobile.articleformobile.service.impl;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.synda.mobile.articleformobile.entity.CmsArticle;
import com.synda.mobile.articleformobile.mapper.CmsArticleMapper;
import com.synda.mobile.articleformobile.service.ICmsArticleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 * <p>
 * 文章表 服务实现类
 * </p>
 *
 * @author changq
 */
@Service
public class CmsArticleServiceImpl extends ServiceImpl<CmsArticleMapper, CmsArticle> implements ICmsArticleService {
	@Autowired
	private CmsArticleMapper cmsArticleMapper;
	@Override
	public Page<CmsArticle> getByCategoryId(Page<CmsArticle> page, String categoryId) {
		return cmsArticleMapper.getByCategoryId(page,categoryId);
	}
}

controller.java

package com.synda.mobile.articleformobile.controller;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.synda.mobile.articleformobile.entity.CmsArticle;
import com.synda.mobile.articleformobile.service.ICmsArticleService;
import com.synda.mobile.common.BaseController;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

/**
 * @author changq
 */
@RestController
@Validated
@RequestMapping("/cmsArticle")
public class CmsArticleController extends BaseController {
    @Autowired
    private ICmsArticleService cmsArticleService;


    /**
     *根据分类id查找
     * @param categoryId  分类id
     * @return FinalResult
     */
    @PostMapping("/{current}/{size}/{categoryId}")
    public FinalResult<CmsArticle> getCmsArticleByCategoryId(
            @PathVariable String categoryId,
            @PathVariable Integer current,
            @PathVariable Integer size){
        Page<CmsArticle> page = new Page<>(current, size);
        Page<CmsArticle> pages= cmsArticleService.getByCategoryId(page, categoryId);
        return buildFinalResult(pages);
    }

运行结果

{
    "code": 200,
    "data": {
        "records": [
            {
                "id": 29,
                "pid": 0,
                "slug": "c",
                "title": "cccccq333",
                "content": "string",
                "editMode": "string",
                "summary": "string",
                "linkTo": "string",
                "thumbnail": "string",
                "style": "string",
                "userId": 0,
                "orderNumber": 0,
                "status": "string",
                "commentStatus": true,
                "commentCount": 0,
                "commentTime": "2020-02-27T04:08:57",
                "viewCount": 10000,
                "created": "2020-02-27T04:08:57",
                "modified": "2020-02-27T04:08:57",
                "flag": "string",
                "metaKeywords": "string",
                "metaDescription": "string",
                "remarks": "string"
            },
            {
                "id": 30,
                "pid": 15,
                "slug": "e",
                "title": "dddddc",
                "content": "string",
                "editMode": "string",
                "summary": "string",
                "linkTo": "string",
                "thumbnail": "string",
                "style": "string",
                "userId": 0,
                "orderNumber": 0,
                "status": "string",
                "commentStatus": true,
                "commentCount": 0,
                "commentTime": "2020-02-27T04:00:15",
                "viewCount": 0,
                "created": "2020-02-27T04:00:15",
                "modified": "2020-02-27T04:00:15",
                "flag": "string",
                "metaKeywords": "string",
                "metaDescription": "string",
                "remarks": "string"
            }
        ],
        "total": 4,
        "size": 2,
        "current": 1,
        "orders": [],
        "searchCount": true,
        "pages": 2
    }
}

收工，完美搞定。