MybatisPlus 实现多表联合分页条件查询

方式一：XML

有点繁琐，不太想用

mapper接口

public interface RoomMapper extends BaseMapper<Room> {
 
    List<RoomVO> getRoomPageList(Page page, @Param("roomPageReq")RoomPageReq roomPageReq);
}

xml

和常见的一样

    <select id="selectProductPage" resultType="com.xxx">
        SELECT
        p.id id,
        ppr.product_code productCode,
        p.`name` productName,
        p.`url` supplyUrl,
        p.`img_url` mainPicUrl,
        p.carry_status carryStatus,
        p.carry_status_desc carryStatusMsg,
        p.create_time createTime
        FROM product AS p
        LEFT JOIN product_pull_record AS ppr ON p.id = ppr.product_id and ppr.`primary_sign` = 0
        <where>
            p.`delete_status` = 0
            <if test="code != null">
                and ppr.product_code =#{code}
            </if>
            <if test="name != null">
                and p.`name` like concat("%",#{name},"%")
            </if>
        </where>
        ORDER BY p.create_time DESC LIMIT #{index}, #{limit}
    </select>

服务层

加上mybatis的分页对象，组成查询条件

或

自己手动加上分页参数，我个人比较喜欢这种

    public PageBean<ProductRecordResp> selectPageList(String name, Long code, Integer page, Integer limit) {
        Integer index = (--page) * limit;
        List<ProductRecordResp> respList = productMapper.selectProductList(name, code, index, limit);
        Long count = productMapper.selectProductCount(name, code);// 与分页sql类似，用count()
        return new PageBean<>(page, limit, count, respList);
    }

方式二：mapper接口注解

新项目加上的，感觉不错，仿照jpa写的

mapper接口

@Mapper
public interface MessageMapper extends BaseMapper<Message> {

    @Select("SELECT m.`id`, mt.`project_id`, mt.`content`, UNIX_TIMESTAMP(m.`create_time`) AS create_time, m.`status` " +
            "FROM `message` m LEFT JOIN `message_text` mt ON m.message_text_id = mt.id " +
            "where 1=1 and ${ew.sqlSegment}")
    List<MessageResponse> findMessagePage(Page<MessageResponse> pageParam, @Param(Constants.WRAPPER) QueryWrapper<MessageResponse> queryWrapper);
}

服务层

组装参数

public List<MessageResponse> findMessagePage(Long userId, Integer status, Long startTime, Long endTime, Integer page, Integer limit) {
       Page<MessageResponse> pageParam = new Page<>(page, limit);

　　　　QueryWrapper<MessageResponse> wrapper = new QueryWrapper<>();
　　　　wrapper.eq("receive_id", userId);
　　　　wrapper.eq(ObjectUtil.isNotNull(status), "status", status);
　　　　wrapper.ge(ObjectUtil.isNotNull(startTime), "create_time", ObjectUtil.isNull(startTime) ? null : Times.toLocalDateTime(startTime));
　　　　wrapper.le(ObjectUtil.isNotNull(endTime), "create_time", ObjectUtil.isNull(endTime) ? null : Times.toLocalDateTime(endTime));

 　　　 wrapper.orderByDesc("create_time"); 
　　　　return messageMapper.findMessagePage(pageParam, wrapper); 
}