MyBatisPlus实现自定义分页查询数据结合QueryWrapper实现自定义的多表连接查询

在MyBatisPlus中，虽然框架自带的BaseMapper具有各种CRUD操作，但有时需要进行自定义的查询，如多表的连接查询：

首先准备实体类：

@Data
public class Dish implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long id;


    //菜品名称
    private String name;


    //菜品分类id
    private Long categoryId;


    //菜品价格
    private BigDecimal price;


    //商品码
    private String code;


    //图片
    private String image;


    //描述信息
    private String description;


    //0 停售 1 起售
    private Integer status;


    //顺序
    private Integer sort;


    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;


    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;


    @TableField(fill = FieldFill.INSERT)
    private Long createUser;


    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Long updateUser;


    //是否删除
    private Integer isDeleted;

}

@Data
public class DishFlavor implements Serializable {

    private static final long serialVersionUID = 1L;

    private Long id;


    //菜品id
    private Long dishId;


    //口味名称
    private String name;


    //口味数据list
    private String value;


    @TableField(fill = FieldFill.INSERT)
    private LocalDateTime createTime;


    @TableField(fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;


    @TableField(fill = FieldFill.INSERT)
    private Long createUser;


    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Long updateUser;


    //是否删除
    private Integer isDeleted;

}

@Data
public class DishDto extends Dish {

    private List<DishFlavor> flavors = new ArrayList<>();

    private String categoryName;

    private Integer copies;
}

编写Mapper接口及其对应的xml文件：

@Mapper
public interface DishMapper extends BaseMapper<Dish> {
    Page<DishDto> DishDtoPage(@Param("page") Page<DishDto> page,
                              @Param("name") String name,
                              // @Param("ew") 表示取mapper.xml中的${ew.customSqlSegment}
                              @Param("ew") QueryWrapper<DishDto> queryWrapper);
}

其中，@Param("ew") QueryWrapper<DishDto> queryWrapper就是结合QueryWrapper，除了可以自定义sql语句之外，还可用QueryWrapper来快捷添加限制条件！

<?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.itheima.reggie_takeout.mapper.DishMapper">
    <select id="DishDtoPage" resultType="com.itheima.reggie_takeout.dto.DishDto">
        select d.*,c.name as categoryName
        from dish d left outer join category c
        on d.category_id = c.id
        <where>
            <if test="name != null and name != ''">
                d.name like #{name}
            </if>
        </where>
        /*
            ${ew.customSqlSegment} 将此行代码放置于Mapper文件的语句中，
            MybatisPlus将会为我们自动将 @Param(Constants.WRAPPER) 注解后的查询参数自动组装到Sql中
        */
        ${ew.customSqlSegment}
    </select>
</mapper>

Service层：

public interface DishService extends IService<Dish> {
    public Page<DishDto> pageDishDto(Page<DishDto> pageInfo, String name, QueryWrapper<DishDto> queryWrapper);
}

ServiceImpl：

/**
     * 调用DishMapper中的自定义方法需要将DishMapper注册到容器中
     * 使用@Resource注解注册
     */
    @Resource
    private DishMapper dishMapper;

    @Override
    public Page<DishDto> pageDishDto(Page<DishDto> pageInfo, String name, QueryWrapper<DishDto> queryWrapper) {
        Page<DishDto> dishDtoPage = dishMapper.DishDtoPage(pageInfo, name,queryWrapper);
        return dishDtoPage;
    }

注意由于需要使用mapper接口中自定义的方法，所以ServiceImpl中需要注入对应的Mapper，使用@Resource注解注入！

Controller层：

@RestController
@RequestMapping("/test")
public class testController {
    @Autowired
    DishService dishService;

    @GetMapping("/page")
    public Page<DishDto> pageDishDto() {
        QueryWrapper<DishDto> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("update_time");
        Page<DishDto> page = new Page<>();
        Page<DishDto> dishDtoPage = dishService.pageDishDto(page, null, queryWrapper);
        return dishDtoPage;
    }
}

测试效果：

查询成功！