竖表如何基于动态的搜索条件分页查询

首先对于一个竖表的结构的实体，很多时候 该实体的字段是动态的。

而我这次遇到的麻烦就是搜索条件也是动态的，这导致我在写sql的时候无法确认那些字段需要作为搜索条件，就算知道了也无法通过foreach标签循环2个List。

解决方案：

在service层就使用StringBuffer将where字句搜索条件拼接好，到mapper层直接作为入参放进sql中就好了

首先Controller层接收map作为搜索条件，因为键值对的特性每个搜索条件对应的动态字段id也会带到后端：

import cn.dev.common.core.domain.AjaxResult;
import cn.dev.system.domain.dto.unp.ProductAddDTO;
import cn.dev.system.domain.dto.unp.ProductModifyDTO;
import cn.dev.system.service.IYcUnpProductService;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import lombok.RequiredArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.Map;

//@Api(tags = "通用配件管理")
@RestController
@RequestMapping("/unp/product")
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class YcUnpProductController {

    private final IYcUnpProductService productService;

    //    @ApiOperation("获取通用配件列表")
    @GetMapping("/list")
    public AjaxResult getProductList(
            @RequestParam("pageNum") Integer pageNum,
            @RequestParam("pageSize") Integer pageSize,
            @RequestParam Map<Integer, String> queryParamMap,
            @RequestParam Integer typeId) {
        return productService.getProductList(pageNum, pageSize, queryParamMap, typeId);
    }
}

Service层：

import cn.dev.common.core.domain.AjaxResult;
import cn.dev.system.domain.dto.unp.ProductAddDTO;
import cn.dev.system.domain.dto.unp.ProductModifyDTO;
import cn.dev.system.domain.entity.YcUnpProduct;
import com.baomidou.mybatisplus.extension.service.IService;

import java.util.Map;

public interface IYcUnpProductService extends IService<YcUnpProduct> {
    /**
     * 获取通用配件列表
     * @param pageNum 页码
     * @param pageSize 每页数量
     * @param queryParamMap 查询参数
     * @param typeId 类型id
     * @return 通用配件列表
     */
    AjaxResult getProductList(Integer pageNum, Integer pageSize, Map<Integer,String>             
        queryParamMap , Integer typeId);
}

 

ServiceImpl层：将接收过来map的value组装成where字句搜索条件

import cn.dev.common.core.domain.AjaxResult;
import cn.dev.system.domain.dto.unp.ProductAddDTO;
import cn.dev.system.domain.dto.unp.ProductModifyDTO;
import cn.dev.system.domain.entity.YcUnpField;
import cn.dev.system.domain.entity.YcUnpProduct;
import cn.dev.system.domain.entity.YcUnpProductValue;
import cn.dev.system.mapper.YcUnpFieldMapper;
import cn.dev.system.mapper.YcUnpProductMapper;
import cn.dev.system.mapper.YcUnpProductValueMapper;
import cn.dev.system.service.IYcUnpProductService;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
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 lombok.RequiredArgsConstructor;
import org.apache.commons.math3.stat.descriptive.summary.Product;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
@RequiredArgsConstructor(onConstructor_ = @Autowired(required = false))
public class YcUnpProductServiceImpl extends ServiceImpl<YcUnpProductMapper, YcUnpProduct>
        implements IYcUnpProductService {
    //实体字段mapper
    private final YcUnpFieldMapper ycUnpFieldMapper;
    @Override
    public AjaxResult getProductList(Integer pageNum, Integer pageSize,         
            Map<Integer,String> queryParamMap ,Integer typeId){
        IPage<YcUnpProduct> productIPage = new Page<>(pageNum, pageSize);
        StringBuffer queryParams = new StringBuffer("");
        // 拼接查询条件(如果在字段表中设置了该字段为可搜索，那么就可以在查询条件中使用该字段)
        ycUnpFieldMapper.selectList(new QueryWrapper<YcUnpField>().eq("type_id",typeId)
                        .eq("searchable",1)).stream().forEach(field ->{
            if (queryParamMap.containsKey(field.getFieldId())){
                queryParams.append(" or field_id = " + field.getFieldId());
                queryParams.append(" and field_value " + " like '%" +                 
                        queryParamMap.get(field.getFieldId()) + "%'");
            }
        });
        baseMapper.selectProductList(productIPage, typeId, queryParams.toString());
        System.out.println(productIPage.getRecords());
        return AjaxResult.success("查询成功", productIPage);
    }

}

Mapper：

import cn.dev.system.domain.entity.YcUnpProduct;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

@Mapper
public interface YcUnpProductMapper extends BaseMapper<YcUnpProduct> {
    /**
     * 查询产品列表
     * @param page
     * @param typeId
     * @param queryParams
     * @return
     */
    IPage<YcUnpProduct> selectProductList(IPage<YcUnpProduct> page, @Param("typeId") Integer typeId, @Param("queryParams") String queryParams);
}

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="你的mapper路径">

    <resultMap id="BaseResultMap" type="你的路径">
        <id property="productId" column="product_id" jdbcType="INTEGER"/>
        <result property="typeId" column="type_id" jdbcType="INTEGER"/>
        <result property="sequence" column="sequence" jdbcType="INTEGER"/>
        <result property="createBy" column="create_by" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
        <result property="updateBy" column="update_by" jdbcType="VARCHAR"/>
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <sql id="Base_Column_List">
        product_id, type_id, `sequence`, create_by, create_time, update_by, update_time
    </sql>

    <select id="selectProductList" resultMap="BaseResultMap">
        SELECT distinct yup.product_id
        FROM yc_unp_product yup join yc_unp_product_value yupv on yup.product_id = 
             yupv.product_id and yup.type_id = #{typeId}
        <where>
        <![CDATA[
            ${queryParams}
        ]]>
        </where>
        order by yup.sequence
    </select>
</mapper>

如此一来就大功告成了