Mybatis-Plus分页查询（自定义联表SQL）

本文采用SpringBoot + mybatis + mybatis-plus 实现分页查询

pom.xml依赖引入：

<!-- SpringBoot引入MybatisPlus依赖包 -->
<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-boot-starter</artifactId>
	<version>3.4.3</version>
</dependency>

<dependency>
	<groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.20<</version>
    <scope>provided</scope>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-validation</artifactId>
    <version>2.4.10</version>
</dependency>

Entity实体层

@Data
@ApiModel(description = "查询公司自建商品入参")
public class SelfBuiltProductsQueryDTO {

	//页码
    @NotNull(message = "页码不能为空")
    @Min(value = 0, message = "页码最小为0")
    private Integer pageNo;

	//每页行数
    @NotNull(message = "每页行数不能为空")
    @Min(value = 10, message = "每页最少10行")
    @Max(value = 50, message = "每页最多50行")
    private Integer pageSize;
	
	//商品名称
    private String goodsName; 
}

Controller层

@Slf4j
@RestController
@RequestMapping("/order")
public class OrderController {
	@Autowired
	private OrderService orderService;

	@RequestMapping(value = "/list", method = RequestMethod.POST)
	public IPage<自己定义的实体> getOrderAndGoodsPage(@Validated @RequestBody OrderDTO dto) {
		return orderService.getOrderAndGoodsPage(dto);
	}
}

Service接口层

public interface OrderService extends IService<Order> {
	public IPage<自己定义的实体> getOrderAndGoodsPage(@Validated @RequestBody OrderDTO dto);
}

ServiceImpl层

@Slf4j
@Service
public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements OrderService {
	@Autowired
	private OrderMapper orderMapper;

	@Override
	pubic IPage<自己定义的实体> getOrderAndGoodsPage(OrderDTO dto) {
		IPage<自己定义的实体> page = new Page<>(页码, 每页行数);
		page = orderMapper.selectOrderAndGoodsPage(dto.getPageNo, dto查询条件);
		return page;
	}
}

Mapper接口层

@Repository
public interface OrderMapper extends BaseMapper<Order> {
	IPage<自己定义的实体> selectOrderAndGoodsPage(IPage page, @Param("dto") OrderDTO dto);
}

注意 selectOrderAndGoodsPage() 中的参数顺序是 先page 再 queryDTO查询条件，否则Mybatis的xml文件中 resultType=“自己定义的实体” 返回查询列表数据时会报错。

Mapper.xml层

<select id="selectOrderAndGoodsPage" resultType="自己定义的实体">
	SELECT
		a.id, a.code, b.id AS goods_id, b.name AS goods_name
	FROM
		order_oder_info a
        INNER JOIN goods_goods_info b ON b.id = a.goods_id 
    WHERE
        b.goods_name = #{dto.goodsName}
    ORDER BY 
   		a.code
</select>