应用场景

对于存在多表的模糊或者具体搜索查询,能够根据查询到的id,在关联表里面进行筛选

代码

<select id="search" resultType="cn.datax.service.data.standard.pojo.BaseAssetsMappingPojo">
        SELECT sm.* FROM `standard_mapping` sm,`metadata_columns` mc,`standard_codebase` sc
        <where>
            AND sm.metadata_id=mc.id AND sm.standard_id=sc.id
            <if test="map.keywords!= null and map.keywords!=">
            AND (mc.column_ename LIKE CONCAT(%,#{map.keywords},%)
            OR sc.name_ch LIKE CONCAT(%,#{map.keywords},%))
            </if>
            <if test="map.nodeName!= null and map.nodeName!=">
            AND
            sm.standard_id IN (SELECT id FROM `standard_codebase` WHERE tree_id IN (
            SELECT id FROM `standard_codetree` WHERE parent_id IN (
            SELECT id FROM `standard_codetree` WHERE parent_id IN (
            SELECT id FROM `standard_codetree` WHERE parent_id IN (
            SELECT id FROM `standard_codetree` WHERE parent_id IN (
            SELECT id FROM `standard_codetree` WHERE node_name =#{map.nodeName}))))))
            </if>
            <if test="map.dbName!= null and map.dbName!=">
            AND
            sm.metadata_id IN (
            SELECT mc.id FROM `metadata_columns` mc WHERE mc.db_name=#{map.dbName})
            </if>
            <if test="map.mappingDegree!= null and map.mappingDegree!=">
            AND
            mapping_degree = #{map.mappingDegree}
            </if>
        </where>
    </select>

@RequestMapping("/findMappingAll")
    public R findMappingAll(@RequestBody Map<String, Object> map) {
          
   
        List<BaseAssetsMappingPojo> mappingAll2 = null;
        List<BaseAssetsMappingPojo> mappingAll = null;
        Integer pageNum = Integer.parseInt(map.get("pageNum").toString());
        Integer pageSize = Integer.parseInt(map.get("pageSize").toString());
        String keywords = map.get("keywords").toString();
        String dbName = map.get("dbName").toString();
        String nodeName = map.get("nodeName").toString();
        String mappingDegree = map.get("mappingDegree").toString();
        if (keywords == "" && dbName == "" && nodeName == "" && mappingDegree == ""){
          
   
            //查询所有
            mappingAll2 = baseAssetsDao.findMapping();
            PageHelper.startPage(pageNum, pageSize);
            mappingAll = baseAssetsDao.findMapping();
            //表格信息
            List<BaseAssetsMappingPojo> mappingAll1 = forMapping(mappingAll);
            Map<String, Object> params = new HashMap<>();
            params.put("data", mappingAll1);
            params.put("total", mappingAll2.size());
            return R.ok().setData(params);
        }
//        //根据daName查询到metadataId
//        List<Integer> metadataId = baseAssetsDao.findDaName(daName);
//        //根据nodeName查询到standardId
//        List<Integer> standardId = baseAssetsDao.findNodeNameOne(nodeName);
//        //根据mappingDegree直接查询
        //走搜索
        mappingAll2 = baseAssetsDao.search(map);
        PageHelper.startPage(pageNum, pageSize);
        mappingAll = baseAssetsDao.search(map);
        //表格信息
        List<BaseAssetsMappingPojo> mappingAll1 = forMapping(mappingAll);
        Map<String, Object> params = new HashMap<>();
        params.put("data", mappingAll1);
        params.put("total", mappingAll2.size());
        return R.ok().setData(params);
    }