mybatis-plus自定義sql語句的關(guān)聯(lián)分頁查詢并使用VO顯示查詢結(jié)果

場景：查詢組織機(jī)構(gòu)。展示組織機(jī)構(gòu)基本信息時贝次，顯示該組織機(jī)構(gòu)的上級機(jī)構(gòu)名稱

VO模型

@Data@NoArgsConstructorpublic class OrgVo extends BaseVo<Organization> {
private String id;
 private String orgCode;
 private String orgFullName;
 private String orgAbbName; 
private String parentId; 
private String parentName;//非數(shù)據(jù)庫表字段崔兴，是關(guān)聯(lián)查詢上級機(jī)構(gòu)名稱
 private String bussCode;
 private String industryCode;
 private String provinceCode; 
private String cityCode; 
private String deleted;
}

注解方式的mapper

    @Select("select A.id,A.org_code,A.org_full_name, A.org_abb_name, A.parent_id, " +
            "B.org_abb_name AS parentName, " +
            "A.buss_code, A.buss_code, A.province_code, A.city_code, A.deleted,A.updated_time  " +
            "FROM organization A " +
            "LEFT JOIN organization B ON (A.parent_id = B.id) " +
            " ${ew.customSqlSegment} " )
    IPage<OrgVo> queryOrgPageByCondition(Page page, @Param(Constants.WRAPPER) QueryWrapper<OrgVo> orgWrapper);

service

    /**
     * 查詢組織機(jī)構(gòu)
     *
     * @return
     */
    IPage<OrgVo> query(Page<OrgVo> page, OrganizationQueryParam organizationQueryParam);

serviceImpl

    @Override
    public IPage<OrgVo> query(Page<OrgVo> page, OrganizationQueryParam organizationQueryParam) {
        QueryWrapper<OrgVo> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(organizationQueryParam.getOrg_abb_name()), "a.org_abb_name", organizationQueryParam.getOrg_abb_name());

        IPage<OrgVo> selectMyPage = this.baseMapper.queryOrgPageByCondition(page, queryWrapper);

        return selectMyPage;
    }

查詢結(jié)果

 "records": [
            {
                "id": "1291258857443676161",
                "orgCode": "xjjg",
                "orgFullName": "xjjg",
                "orgAbbName": "xjjg",
                "parentId": "1288665456705589249",
                "parentName": "org_abb_name",
                "bussCode": null,
                "industryCode": null,
                "provinceCode": null,
                "cityCode": null,
                "deleted": "N"
            }
        ],
        "total": 1,
        "size": 10,
        "current": 1,
        "searchCount": true,
        "pages": 1

注意事項
1、注意mybatis-plus是否自動開啟了駝峰映射蛔翅，既數(shù)據(jù)庫字段未 org_add_name敲茄。VO類屬性為orgAddName
2、mapper注解自定義sql語句時山析，使用AS進(jìn)行VO屬性映射堰燎，如B.org_abb_name AS parentName
3、使用${ew.customSqlSegment} 進(jìn)行多條件查詢笋轨，并在queryWrapper.like(StringUtils.isNotBlank(organizationQueryParam.getOrg_abb_name()), "a.org_abb_name", organizationQueryParam.getOrg_abb_name());中使用"a.org_abb_name"指定不同表的條件秆剪。

最后編輯于：2020.08.06 14:39:52

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者