解决springboot 多表查询分页查询问题

问题：

springboot页面显示的数据来自三张不同的表，同时还要分页，不知道怎么查。

解决过程：

1、pom.xml文件中注入分页依赖

2、application.yml添加分页配置

3、自定义分页的配置类

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * 自定义一个配置类，mapper 扫描也可在此写上
 */
@Configuration
@MapperScan("com.xunfang.mapper")
public class PageConfig {
    /**
     * 分页插件
     * @return 分页插件的实例
     */
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return mybatisPlusInterceptor;
    }
}

4、自定义查询所有的数据返回的实体类

已有实体不能满足查询，想要多表查询，要将所有查询的属性汇总到一个新的实体类，作为page查询的泛型

这里是我的SaleInformation类，其中productName来自product表，realName来自users表，其余属性都来自sale表

@Data
public class SaleInformation {
    private Integer id;
    private String productName;
    private Float price;
    private Integer quantity;
    private Float totalPrice;
    private Date saleDate;
    private String realName;
}

5、DAO层

mapper

写查询的方法，返回IPage<T> ，参数列表(Page<T> page, @Param(Constants.WRAPPER) Wrapper<T> wrapper)

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xunfang.entity.Sale;
import com.xunfang.entity.SaleInformation;
import org.apache.ibatis.annotations.Param;


public interface SaleMapper extends BaseMapper<Sale> {

    //@Param(Constants.WRAPPER)  固定写法
    IPage<SaleInformation> selectAllPage(Page<SaleInformation> page, @Param(Constants.WRAPPER) Wrapper<Sale> wrapper);

}

mapper.xml

sql语句：多表查询，${ew.customSqlSegment} 是wrapper里的方法

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xunfang.dao.SaleMapper">

    <select id="selectAllPage" resultType="com.xunfang.entity.SaleInformation">
        select s.id,p.product_name as productName,s.price,s.quantity,s.total_price as totalPrice,s.sale_date as saleDate,u.real_name as realName
        from (sale as s left join product as p on s.product_id=p.id) left join users as u on s.user_id=u.id ${ew.customSqlSegment}
    </select>
</mapper>

7、业务层

service

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import com.xunfang.entity.PageParams;
import com.xunfang.entity.Sale;
import com.xunfang.entity.SaleInformation;

public interface SaleService extends IService<Sale> {

    IPage<SaleInformation> findPage(PageParams pageParams);
}

serviceImpl

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.xunfang.dao.SaleMapper;
import com.xunfang.entity.PageParams;
import com.xunfang.entity.Sale;
import com.xunfang.entity.SaleInformation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
@Transactional
public class SaleServiceImpl extends ServiceImpl<SaleMapper, Sale> implements SaleService {

    @Autowired(required = false)
    SaleMapper saleMapper;

    @Override
    public IPage<SaleInformation> findPage(PageParams pageParams) {
        QueryWrapper<SaleInformation> wrapper = new QueryWrapper<>();
        wrapper.orderByDesc(pageParams.getOrdered());
        Page<SaleInformation> page = new Page<>(pageParams.getCurrentPage(), pageParams.getPageSize());
        IPage<SaleInformation> iPage = saleMapper.selectAllPage(page, wrapper);
        return iPage;
    }
}

8、控制层 controller

//分页
@PostMapping("/findPage")
    public IPage<SaleInformation> findPage(@RequestBody PageParams pageParams){
        return saleService.findPage(pageParams);
    }

(这里的 PageParams 是我自定义的分页参数类)