SpringBoot +Mybatis 一对多查询

 

这个是机构对应的Dto
public class TPublicOrganizationDto {
    private long tPublicOrganization;
    private String tOrganizationName;
    private String tOrganizationKey;
    private long tOrganizationKeyState;

    //机构和部门一对多查询
    private  List<TPublicDepartmentDto> tPublicDepartmentDto;      省略get和set方法

}

这个是部门对应的Dto
public class TPublicDepartmentDto {

  private long tPublicDepartment;
  private String tDepartmentName;
  private String tDepartmentKey;
  private int tDepartmentKeyState;
  private String tOrganizationKey;

省略get和set方法

}

我们查询的一个机构下有多个部门，进行查询，我们需要进行xml配置如下

<mapper namespace="com.example.springboot.mapper.tpublic.tborganization.TPublicOrganizationMapper">
    <resultMap type="com.example.springboot.dto.tpublick.tborganization.TPublicOrganizationDto" id="TPublicOrganizationDtolist">
      <result property="tPublicOrganization" column="t_public_organization"/>
      <result property="tOrganizationName" column="t_organization_name"/>
      <result property="tOrganizationKey" column="t_organization_key"/>
      <result property="tOrganizationKeyState" column="t_organization_key_state"/>

      <!--一对多组织和部门一对多查询-->
      <collection property="tPublicDepartmentDto" ofType="com.example.springboot.dto.tpublick.tbdepartment.TPublicDepartmentDto">
         <id column="T_PUBLIC_DEPARTMENT" property="tPublicDepartment"/>
         <result column="T_DEPARTMENT_NAME" property="tDepartmentName"/>
         <result column="T_DEPARTMENT_KEY" property="tDepartmentKey"/>
         <result column="T_DEPARTMENT_KEY_STATE" property="tDepartmentKeyState"/>
         <result column="T_ORGANIZATION_KEY" property="tOrganizationKey"/>
      </collection>

   </resultMap>

 其中property 对应为TPublicOrganizationDto类里面tPublicDepartmentDto这个属性

ofType对应为TPublicDepartmentDto这个类的路劲

result 对应数据库的字段

property 对应dto里面的字段

 

下来写出我们的sql

<!-- 一对多查询 查询一个机构下有多个部门-->
<select id="selectDepartmentType" resultMap="TPublicOrganizationDtolist" parameterType="com.example.springboot.dto.tpublick.tbdepartment.TPublicDepartmentDto">
   SELECT
      tpo.t_organization_name,
      tpd.T_DEPARTMENT_NAME
   FROM
      t_public_organization tpo
   LEFT JOIN t_public_department tpd ON tpo.t_organization_key = tpd.T_ORGANIZATION_KEY
</select>

 

 

Controller进行测试

@GetMapping(value = "selectDepartmentType")
public Map<String, Object> selectDepartmentType(TPublicOrganizationDto organizationDto) {
    Map<String, Object> resultMap = new HashMap<String, Object>();
    try {
        List<TPublicOrganizationDto> selectOrganization = tPublicOrganizationService.selectDepartmentType(organizationDto);
        if (selectOrganization.size() > 0) {
            resultMap.put("selectOrganization", selectOrganization);
            resultMap.put("code", 0);

        } else {

            resultMap.put("code", 1);
        }

    } catch (Exception e) {
        e.printStackTrace();
        resultMap.put("code", -1);
    }
    return resultMap;
}

测试成功