mybatis-plus 自定义sql拼接 的方式进行Wrapper条件查询 实现了分页 多表查询

法一：用单表的思想（可以跳过，直接看法二）

方法：先收集公共字段到集合中，再批量查询，然后封装起来

缺点：相较于法二，代码量大，多访问了一次数据库

service层：

@Service
@RequiredArgsConstructor
public class ProductServiceImpl extends ServiceImpl<ProductMapper, Product> implements IProductService {
    private final ICategoryService categoryService;
    @Override
    public List<ProductVO> searchList(ProductPageQueryDTO queryDTO) {
        // 根据分页查询
        Integer categoryId = queryDTO.getCategoryId();
        String title = queryDTO.getTitle();
        Page<Product> page = lambdaQuery()
                .eq(categoryId != null, Product::getCategoryId, categoryId)
                .like(!StrUtil.isBlank(title), Product::getTitle, title)
                .page(Page.of(queryDTO.getPageNum(), queryDTO.getPageSize()));
        List<Product> records = page.getRecords();

        // 获取商品分类名称
        List<Integer> categoryIds = new ArrayList<>(records.size());
        for (Product record : records) {
            categoryIds.add(record.getCategoryId());
        }
        Map<Integer, String> namesMap = categoryService.getNamesByIds(categoryIds);
        System.out.println(namesMap.get(1));

        // 封装结果
        List<ProductVO> productVOS = BeanUtil.copyToList(records, ProductVO.class);
        for (ProductVO productVO : productVOS) {
            String categoryName = namesMap.get(productVO.getCategoryId());
            productVO.setCategoryName(categoryName);
        }
        return productVOS;
    }
}

@Service
@RequiredArgsConstructor
public class CategoryServiceImpl extends ServiceImpl<CategoryMapper, Category> implements ICategoryService {
    private final CategoryMapper categoryMapper;
    

    @Override
    public Map<Integer, String> getNamesByIds(List<Integer> categoryIds) {
        return categoryMapper.getNamesByIds(categoryIds);
    }



}

Mapper层：

public interface CategoryMapper extends BaseMapper<Category> {
    @MapKey("id")
    Map<Integer, String> getNamesByIds(List<Integer> categoryIds);
}

<?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.ptu.mall.mapper.CategoryMapper">

    <select id="getNamesByIds" resultType="map">
        select name
        from category
        where id in
        <foreach collection="categoryIds" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>

</mapper>

法二：

方法：自定义sql拼接mp查询条件

解释：mybatis-plus只要配置了分页插件，当Page进入mapper层时，就会进行分页的sql拼接

Service

@Service
@RequiredArgsConstructor
public class ProductServiceImpl extends ServiceImpl<ProductMapper, Product> implements IProductService {
    private final ProductMapper productMapper;
    @Override
    public List<ProductVO> searchListGood(ProductPageQueryDTO queryDTO) {
        LambdaQueryWrapper<Product> wrapper = new LambdaQueryWrapper<>();
        Integer categoryId = queryDTO.getCategoryId();
        String title = queryDTO.getTitle();
        wrapper.eq(categoryId != null, Product::getCategoryId, categoryId).
                like(!StrUtil.isBlank(title), Product::getTitle, title);
        Page<ProductVO> page = new Page<>(queryDTO.getPageNum(), queryDTO.getPageSize());
        return productMapper.pageQuery(page, wrapper);
    }
}

mapper

public interface ProductMapper extends BaseMapper<Product> {
    List<ProductVO> pageQuery(Page<ProductVO> page, @Param("ew") LambdaQueryWrapper<Product> wrapper);
}

xml

<?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.ptu.mall.mapper.ProductMapper">

    <select id="pageQuery" resultType="com.ptu.mall.domain.vo.ProductVO">
        select p.id as id, p.code as code, p.title as title, p.category_id as categoryId, c.name as categoryName, p.img as img, p.price as price, p.stocks as stocks, p.description as description
        from product p
        left join category c on p.category_id = c.id
        ${ew.customSqlSegment}

    </select>
</mapper>