HOS基础开发平台

# 复杂查询

# 关联查询

这里使用两张表分别为staff</code>员工信息表和organization组织结构表来演示关联查询，查出员工信息和组织结构信息。

1.创建`organization组织结构表

CREATE TABLE `organization` (
  `id` varchar(32) NOT NULL COMMENT '主键',
  `name` varchar(60) DEFAULT NULL COMMENT '部门名称',
  `code` varchar(60) DEFAULT NULL COMMENT '部门编码',
  `description` varchar(255) DEFAULT NULL COMMENT '描述',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='组织结构表';

relative_select_1

staff表在第一个CRUD模块已经创建，此处略过

向表中插入测试数据

INSERT INTO `demo`.`organization`(`id`, `name`, `code`, `description`, `create_time`, `update_time`) VALUES ('1', '开发部', '0001', NULL, '2022-01-14 15:23:55', '2022-01-14 15:23:55');
INSERT INTO `demo`.`organization`(`id`, `name`, `code`, `description`, `create_time`, `update_time`) VALUES ('2', '测试部', '0002', NULL, '2022-01-14 15:24:04', '2022-01-14 15:24:04');
INSERT INTO `demo`.`organization`(`id`, `name`, `code`, `description`, `create_time`, `update_time`) VALUES ('3', '财务部', '0003', NULL, '2022-01-14 15:24:11', '2022-01-14 15:24:27');
INSERT INTO `demo`.`organization`(`id`, `name`, `code`, `description`, `create_time`, `update_time`) VALUES ('4', '销售部', '0004', NULL, '2022-01-14 15:24:21', '2022-01-14 15:24:29');

此时组织机构表的数据如下

relative_select_2

当前员工信息表的数据如下

relative_select_3

2.在模块oa-user-model的com.mediway.oa.user.model.vo包下创建staffVo， 该类既包含员工信息又包含组织机构信息。

package com.mediway.oa.user.model.vo;

import lombok.Data;

@Data
public class StaffVo {
    /**
     * 员工id
     */
    private String id;

    /**
     * 员工姓名
     */
    private String name;

    /**
     * 员工手机号
     */
    private String phone;

    /**
     * 员工邮箱
     */
    private String email;

    /**
     * 员工年龄
     */
    private Integer age;

    /**
     * 员工部门id
     */
    private String orgId;

    /**
     * 员工的部门名称
     */
    private String orgName;

    /**
     * 员工部门编码
     */
    private String orgCode;

}

3.在模块oa-user-service的StaffMapper中创建selectListStaffVo方法。

package com.mediway.oa.user.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mediway.oa.user.model.entity.Staff;
import com.mediway.oa.user.model.vo.StaffVo;

import java.util.List;

public interface StaffMapper extends BaseMapper<Staff> {

    List<StaffVo> selectListStaffVo();
}

4.在模块oa-user-service的staffMapper.xml中创建 selectListStaffVo方法对应的SQL。

<select id="selectListStaffVo" resultType="com.mediway.oa.user.model.vo.StaffVo">
    select s.id,s.name, s.age,s.email,s.phone,o.id as orgId,o.name as orgName,o.code
    from staff s
    inner join organization o
    on s.org_id = o.id
</select>

5.在模块oa-user-service的StaffService中创建selectListStaffVo方法。

package com.mediway.oa.user.service;


import com.mediway.hos.database.service.BaseService;
import com.mediway.oa.user.model.entity.Staff;
import com.mediway.oa.user.model.vo.StaffVo;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public interface StaffService extends BaseService<Staff> {

    /**
     * 功能描述: 关联查询用户部门
     *
     **/
    List<StaffVo> selectListStaffVo();
}

6.在模块oa-user-service的StaffServiceImpl 中完成selectListStaffVo的方法实现。

package com.mediway.oa.user.service.impl;


import com.mediway.hos.database.service.impl.BaseServiceImpl;
import com.mediway.oa.user.mapper.StaffMapper;
import com.mediway.oa.user.model.entity.Staff;
import com.mediway.oa.user.model.vo.StaffVo;
import com.mediway.oa.user.service.StaffService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class StaffServiceImpl extends BaseServiceImpl<StaffMapper, Staff> implements StaffService {

    @Autowired
    private StaffMapper staffMapper;

    @Override
    public List<StaffVo> selectListStaffVo() {
        return staffMapper.selectListStaffVo();
    }

}

7.在模块oa-user-controller的StaffController中添加selectListStaffVo方法。

package com.mediway.oa.user.controller;

import com.mediway.hos.database.controller.BaseController;
import com.mediway.hos.base.model.BaseResponse;
import com.mediway.oa.user.model.entity.Staff;
import com.mediway.oa.user.model.vo.StaffVo;
import com.mediway.oa.user.service.StaffService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/staff")
public class StaffController extends BaseController<Staff> implements StaffApi {

    @Autowired
    private StaffService staffService;

    @GetMapping("/selectListStaffVo")
    public BaseResponse<List<StaffVo>> selectListStaffVo() {
        return BaseResponse.success(staffService.selectListStaffVo());
    }
}

8.我们请求StaffController的selectListStaffVo方法，该方法的作用是查询出所有用户信息和组织机构信息能够相匹配的数据，接口的请求方式和响应结果如下

请求方式

url：http://localhost:8367/api/staff/selectListStaffVo
method：GET

响应结果

{
    "code": "200",
    "msg": "success",
    "data": [
        {
            "id": "4f0c41cba0c044358c307fb43d5d3ce5",
            "name": "王五",
            "phone": "13612345678",
            "email": "wangwu@qq.com",
            "age": 18,
            "orgId": "2",
            "orgName": "测试部",
            "orgCode": null
        },
        {
            "id": "9e9c735844b841c0a970decdb2b5e182",
            "name": "张三",
            "phone": "13812345678",
            "email": "zhangsan@qq.com",
            "age": 28,
            "orgId": "1",
            "orgName": "开发部",
            "orgCode": null
        },
        {
            "id": "d5e1137514be473c873bf30e14325e0e",
            "name": "李四",
            "phone": "13712345678",
            "email": "lisi@qq.com",
            "age": 22,
            "orgId": "1",
            "orgName": "开发部",
            "orgCode": null
        }
    ],
    "success": true
}

# 手写分页

之前有介绍过使用框架封装的BaseController中selectPage方法进行分页查询，但是该方法具有一定的局限性，即只能对单表进行分页查询。如果有复杂一些比如需要关联多表分页查询，则可以利用MybatisPlus的分页插件+手写业务查询SQL的方式进行，下边介绍下使用过程。

1.在模块oa-user-service的StaffMapper中创建selectPageStaff方法。调用该方法需要两个参数，第一个是Page对象，构建Page对象需要当前页和每页数量两个参数，需要开发人员构建Page对象时手动传入，需要注意的是Page对象是必输的；第二个是业务实体对象，主要负责在SQL中构建查询条件，当然使用输入业务实体对象构建查询条件只是本示例的要求，你也可以定义其它的查询条件参数，这块没有固定限制。

package com.mediway.oa.user.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mediway.oa.user.model.entity.Staff;
import com.mediway.oa.user.model.vo.StaffVo;
import org.apache.ibatis.annotations.Param;

public interface StaffMapper extends BaseMapper<Staff> {

    IPage<StaffVo> selectPageStaff(Page<Staff> objectPage, @Param("staff") Staff staff);

}

2.在模块oa-user-service的staffMapper.xml中创建selectPageStaff方法对应的SQL。需要注意的是selectPageStaff方法第一个参数Page分页对象无需在xml中书写SQL时体现，MybatisPlus会自动解析该对象然后配置在分页语句上。

<select id="selectPageStaff"  resultType="com.mediway.oa.user.model.vo.StaffVo">
    select s.id,s.name, s.age,s.email,s.phone,o.id as orgId,o.name as orgName,o.code
    from staff s
    inner join organization o
    on s.org_id = o.id
    <where>
        <if test="staff.gender != null">
            s.gender = #{staff.gender}
        </if>
    </where>
</select>

3.在模块oa-user-service的StaffService中创建selectPageStaff方法。

package com.mediway.oa.user.service;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.mediway.hos.database.service.BaseService;
import com.mediway.oa.user.model.entity.Staff;
import com.mediway.oa.user.model.vo.StaffVo;
import org.springframework.stereotype.Service;


@Service
public interface StaffService extends BaseService<Staff> {

     IPage<StaffVo> selectPageStaff(Staff staff);

}

4.在模块oa-user-service的StaffServiceImpl中完成selectPageStaff的方法实现。

package com.mediway.oa.user.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.mediway.hos.database.service.impl.BaseServiceImpl;
import com.mediway.oa.user.mapper.StaffMapper;
import com.mediway.oa.user.model.entity.Staff;
import com.mediway.oa.user.model.vo.StaffVo;
import com.mediway.oa.user.service.StaffService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class StaffServiceImpl extends BaseServiceImpl<StaffMapper, Staff> implements StaffService {

    @Autowired
    private StaffMapper staffMapper;

    @Override
    public IPage<StaffVo> selectPageStaff(Staff staff) {
        return staffMapper.selectPageStaff(new Page<>(staff.getCurrent(), staff.getSize()), staff);
    }
}

5.在模块oa-user-controller的StaffController中添加selectPageStaff方法。

package com.mediway.oa.user.controller;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.mediway.hos.database.controller.BaseController;
import com.mediway.hos.base.model.BaseResponse;
import com.mediway.oa.user.model.entity.Staff;
import com.mediway.oa.user.model.vo.StaffVo;
import com.mediway.oa.user.service.StaffService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/staff")
public class StaffController extends BaseController<Staff> {

    @Autowired
    private StaffService staffService;

    /**
     * 关联+分页查询用户部门
     *
     * @param staff
     * @return
     */
    @GetMapping("/selectPageStaff")
    public BaseResponse<IPage<StaffVo>> selectPageStaff(@RequestBody Staff staff) {
        return BaseResponse.success(staffService.selectPageStaff(staff));
    }

}

6.我们请求StaffController的selectPageStaff方法，该方法的作用是使用分页查询出所有用户信息和组织机构信息能够相匹配且符合查询条件的数据，接口的请求方式和响应结果如下

请求方式

url：http://localhost:8367/api/staff/selectPageStaff
method：GET
Content-Type: application/json;charset=UTF-8

request body入参

{
    "current":1,
    "size":2,
    "gender":"男"
}

响应结果

{
    "code": "200",
    "msg": "success",
    "data": {
        "records": [
            {
                "id": "9e9c735844b841c0a970decdb2b5e182",
                "createTime": null,
                "updateTime": null,
                "current": null,
                "size": null,
                "name": "张三",
                "gender": null,
                "age": 28,
                "orgId": "1",
                "email": "zhangsan@qq.com",
                "phone": "13812345678",
                "description": null
            },
            {
                "id": "d5e1137514be473c873bf30e14325e0e",
                "createTime": null,
                "updateTime": null,
                "current": null,
                "size": null,
                "name": "李四",
                "gender": null,
                "age": 22,
                "orgId": "1",
                "email": "lisi@qq.com",
                "phone": "13712345678",
                "description": null
            }
        ],
        "total": 2,
        "size": 2,
        "current": 1,
        "searchCount": true,
        "pages": 1
    },
    "success": true
}
注意区别在请求url时要访问网关，从网关再路由至具体服务。

此处查询仅针对单服务，若有多服务间的关联查询可关注后续微服务远程调用章节

← 事务处理 →