概述
分页查询我们用的非常多,一般来说,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);
}