MybatisPlus自定义sql多表关联分页条件查询

@MybatisPlus自定义sql多表关联分页条件查询

mp封装了crud但是对应复杂sql还是需要自己定义

网上对于多表分页查询的mp描述不是很清楚，我在这里重新写一篇文章。

1.数据库准备

需要实现的sql是两表关联条件查询

select um.`nickname` ,aa.`comment_counts`,aa.`gmt_create`,
aa.`id`,aa.`summary`,aa.`title`,aa.`view_counts`,aa.`weight`
from atai_article aa join ucenter_member um 
on aa.`author_id`=um.`id` 
where aa.`title` like '%v%' limit 0 ,1;

查询效果

涉及数据库 atai_article和ucenter_member，ucenter_member不展示只需要关联查询nickname即可。

2.代码编写

2.1controller

关键代码

Page<ArticleFrontVo> pageArticle = new Page<>(current,limit);
IPage<ArticleFrontVo> result = ataiArticleService.findAllArticleCondition(pageArticle,articleQuery);

    @ApiOperation(value = "分页条件查询文章列表")
    @PostMapping("pageArticleCondition/{current}/{limit}")
    public R findAllArticleCondition(@PathVariable long current, @PathVariable long limit,
                                     @RequestBody(required = false)  ArticleQuery articleQuery) {
        Page<ArticleFrontVo> pageArticle = new Page<>(current,limit);
        IPage<ArticleFrontVo> result = ataiArticleService.findAllArticleCondition(pageArticle,articleQuery);
        List<ArticleFrontVo> list = result.getRecords();
        //调用service的方法实现查询所有的操作
        return R.success().data("total",result.getTotal()).data("records",list);
    }

2.2service

public interface AtaiArticleService extends IService<AtaiArticle> {
    IPage<ArticleFrontVo> findAllArticleCondition(Page<ArticleFrontVo> pageArticle, ArticleQuery wrapper);
}

2.3serviceimpl

@Service
public class AtaiArticleServiceImpl extends ServiceImpl<AtaiArticleMapper, AtaiArticle> implements AtaiArticleService {
    @Override
    public IPage<ArticleFrontVo> findAllArticleCondition(Page<ArticleFrontVo> pageArticle, ArticleQuery wrapper) {
        IPage<ArticleFrontVo>  articleFrontVos = baseMapper.findAllArticleCondition(pageArticle,wrapper);
        return articleFrontVos;
    }
}

2.4mapper

关键代码，起别名articleQuery在xml中取数据

@Param("articleQuery") ArticleQuery articleQuery

public interface AtaiArticleMapper extends BaseMapper<AtaiArticle> {
    IPage<ArticleFrontVo> findAllArticleCondition(@Param("page")Page<ArticleFrontVo> page, @Param("articleQuery") ArticleQuery articleQuery);

}

2.5xml

关键代码，使用articleQuery取数据

 <if test="articleQuery.title!=null and !(articleQuery.title).equals('')">
                and t1.title like "%"#{articleQuery.title,jdbcType=VARCHAR}"%"
 </if>

 <!--sql语句：条件查询所有文章-->
    <select id="findAllArticleCondition"  resultType="com.atai.eduservice.entity.frontvo.ArticleFrontVo">
        SELECT um.nickname ,t1.comment_counts,t1.gmt_create,t1.id,
        t1.summary,t1.title,t1.tag,t1.view_counts,t1.weight
        FROM atai_article t1, ucenter_member um
        <where>
            t1.author_id=um.id and t1.is_deleted=0
            <if test="articleQuery.title!=null and !(articleQuery.title).equals('')">
                and t1.title like "%"#{articleQuery.title,jdbcType=VARCHAR}"%"
            </if>
            <if test="articleQuery.begin!=null and !(articleQuery.begin).equals('')">
                and t1.gmt_create &gt; #{articleQuery.begin}
            </if>
            <if test="articleQuery.end!=null and !(articleQuery.end).equals('')">
                and t1.gmt_create &lt; #{articleQuery.end}
            </if>
        </where>
            order by t1.gmt_modified desc
    </select>

2.6涉及的entity

ArticleFrontVo

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value="ArticleFrontVo对象", description="文章")
public class ArticleFrontVo implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "文章ID")
    @TableId(value = "id", type = IdType.ID_WORKER_STR)
    private String id;

    @ApiModelProperty(value = "摘要")
    private String summary;

    @ApiModelProperty(value = "标题")
    private String title;

    @ApiModelProperty(value = "评论数")
    private Integer commentCounts;

    @ApiModelProperty(value = "浏览数")
    private Integer viewCounts;

    @ApiModelProperty(value = "权重")
    private Integer weight;

    @ApiModelProperty(value = "作者名称")
    private String nickname;

    @ApiModelProperty(value = "标签列表")
    private String tag;

    @ApiModelProperty(value = "创建时间")
    private Date gmtCreate;

}

ArticleQuery

 @Data
public class ArticleQuery {
    @ApiModelProperty(value = "文章标题,模糊查询")
    private String title;

    @ApiModelProperty(value = "查询开始时间", example = "2019-01-01 10:10:10")
    private String begin;//注意，这里使用的是String类型，前端传过来的数据无需进行类型转换

    @ApiModelProperty(value = "查询结束时间", example = "2019-12-01 10:10:10")
    private String end;
}

3.进行接口测试

使用的是swagger进行接口测试

正确检索出数据