Mybatis自定义关联多表并构造条件的分页查询

概述

分页查询我们用的非常多，一般来说，mybatisplus自带的哪个分页查询就能满足我们的需求，但是有些情况我们需要关联一些表来进行分页查询，这就需要我们自定义分页查询了，我来给大家分享一下，关联多表的自定义分页查询和关联多表并带有搜索条件的的自定义分页查询

实体类

帖子实体类

@Data
public class Card {
    @TableId
    private Integer cardId;//主键
    private String title;//标题
    private String content;//内容
    private String date;//日期
    private String appendix;//附件
    private String connect;//联系方式
    private String username;//用户名
    private String headImg;//头像
    private Integer cid; // 类型id
    private boolean status;//是否为热门
    @TableField(exist = false)
    private Category category; //类型
}

帖子类型

@Data
public class Category {
    @TableId
    private Integer cid; //分类id
    private String cname; //分类名称
}

关联多表的自定义分页查询

mapper接口

public interface CardMapper extends BaseMapper<Card> {
 Page<Card> findPage(Page<Card> page);
}

mapper.xml中的cardMapper

 <resultMap id="cardMapper" type="fz.pojo.Card">
        <id property="cardId" column="cardId"></id>
        <result property="title" column="title"></result>
        <result property="content" column="content"></result>
        <result property="date" column="date"></result>
        <result property="appendix" column="appendix"></result>
        <result property="connect" column="connect"></result>
        <result property="username" column="username"></result>
        <result property="headImg" column="headImg"></result>
        <result property="status" column="status"></result>
        <result property="cid" column="cid"></result>
        <association property="category" column="cid" javaType="fz.pojo.Category">
            <id property="cid" column="cid"></id>
            <result property="cname" column="cname"></result>
        </association>
    </resultMap>

查询语句

  <select id="findPage" resultMap="cardMapper">
        SELECT *
        from card
                 LEFT JOIN category on card.cid=category.cid
        ORDER BY card.cardId DESC
    </select>

服务层

@Service
public class CardService {
    @Autowired
    private CardMapper cardMapper;
    @Autowired
    private AdminService adminService;
    //分页查询（自定义）
    public Page<Card> findPage(int page, int size) {
        Page<Card> cardPage =cardMapper.findPage(new Page<>(page,size));
        return cardPage;
    }
}

关联多表并带有搜索条件的的自定义分页查询

mapper接口

 //自定义分页查询（包括搜索条件和类型关联）
    Page<Card> findSearchPage(Page<Card> page,@Param(Constants.WRAPPER) Wrapper<Card> wrapper);

mapper.xml中的cardMapper

 <resultMap id="cardMapper" type="fz.pojo.Card">
        <id property="cardId" column="cardId"></id>
        <result property="title" column="title"></result>
        <result property="content" column="content"></result>
        <result property="date" column="date"></result>
        <result property="appendix" column="appendix"></result>
        <result property="connect" column="connect"></result>
        <result property="username" column="username"></result>
        <result property="headImg" column="headImg"></result>
        <result property="status" column="status"></result>
        <result property="cid" column="cid"></result>
        <association property="category" column="cid" javaType="fz.pojo.Category">
            <id property="cid" column="cid"></id>
            <result property="cname" column="cname"></result>
        </association>
    </resultMap>

查询语句(这里的${ew.customSqlSegment}很关键)

  <select id="findSearchPage" resultMap="cardMapper">
        SELECT *
        from card
                 LEFT JOIN category ON card.cid = category.cid
            ${ew.customSqlSegment}
        ORDER BY card.cardId DESC
    </select>

服务层

//根据搜索条件查询帖子
    public Page<Card> search(String search,int page, int size){
        QueryWrapper<Card> queryWrapper = new QueryWrapper<>();
        if (search != null) {
            queryWrapper.like("title", search).
                    or().like("content", search).
                    or().like("username", search);
        }
        return cardMapper.findSearchPage(new Page<>(page, size), queryWrapper);
    }