mybatis-plus自定义sql语句的关联分页查询并使用VO显示查询结果

场景：查询组织机构。展示组织机构基本信息时，显示该组织机构的上级机构名称

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;//非数据库表字段，是关联查询上级机构名称
 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

    /**
     * 查询组织机构
     *
     * @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;
    }

查询结果

 "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是否自动开启了驼峰映射，既数据库字段未 org_add_name。VO类属性为orgAddName
2、mapper注解自定义sql语句时，使用AS进行VO属性映射，如B.org_abb_name AS parentName
3、使用${ew.customSqlSegment} 进行多条件查询，并在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