mybatis collection多级嵌套查询-蒲公英云

mybatis collection多级嵌套查询

需求：一个页面有多个模块，一个模块有多个对应的图片，需要查询出来的json格式需要三层嵌套

实体类

/** * (MesaMenu)实体类 * * @author ran * @since 2021-03-17 11:38:27 */
@Data
@NoArgsConstructor
public class MesaMenu implements Serializable { 
    private static final long serialVersionUID = 759002313430491430L;
    /** * 台面菜单表主键id */
    private Long id;
    /** * 菜单名称 */
    private String menuName;
    /** * 菜单路由 */
    private String menuRoute;
    /** * 是否显示0代表显示，1代表不显示默认0 */
    private Object isShow;
    /** * 菜单名称中文 */
    private String menuNameCn;
    /** * 菜单排序 */
    private Integer sort;
}
/** * (MesaMenuModule)实体类 * * @author ran * @since 2021-03-17 11:38:28 */
@Data
@NoArgsConstructor
public class MesaMenuModule implements Serializable { 
    private static final long serialVersionUID = 520568918307726506L;
    /** * 模块菜单对应表主键id */
    private Long id;
    /** * 模块对应的菜单id */
    private Long mesaMenuId;
    /** * 模块名字 */
    private String moduleName;
    /** * 模块名字中文 */
    private String moduleNameCn;
    private List<ModuleImg> moduleImgs;
}
/** * (ModuleImg)实体类 * * @author ran * @since 2021-03-17 11:38:28 */
@Data
@NoArgsConstructor
public class ModuleImg implements Serializable { 
    private static final long serialVersionUID = 886861501934746787L;
    /** * 模块图片对应表主键id */
    private Long id;
    /** * 模块id */
    private Long moduleId;
    /** * 图片名称 */
    private String imgName;
    /** * 图片路径 */
    private String imgUrl;
}

controller层

/** * (MesaMenu)表控制层 * * @author ran * @since 2021-03-17 11:36:38 */
@RestController
@RequestMapping("mesaMenu")
@ApiDocGroup("菜单")
public class MesaMenuFacade { 
    /** * 服务对象 */
    @Resource
    private MesaMenuService mesaMenuService;
    @GetMapping("mesaMenuList")
    public RestResponse mesaMenuList() { 
        return this.mesaMenuService.mesaMenuList();
    }
}

service层

/** * (MesaMenu)表服务接口 * * @author ran * @since 2021-03-17 11:39:06 */
public interface MesaMenuService { 
    RestResponse mesaMenuList();
}

serviceImpl层

/** * (MesaMenu)表服务实现类 * * @author ran * @since 2021-03-17 11:39:07 */
@Service("mesaMenuService")
public class MesaMenuServiceImpl implements MesaMenuService { 
    @Resource
    private MesaMenuMapper mesaMenuMapper;
    @Override
    public RestResponse mesaMenuList() { 
        List<VoMesaMenu> voMesaMenus = mesaMenuMapper.queryMesaMenuList();
        return RestResponse.success(voMesaMenus);
    }
}

mapper层

/** * (MesaMenu)表数据库访问层 * * @author ran * @since 2021-03-17 11:38:43 */
@Mapper
public interface MesaMenuMapper { 
    List<VoMesaMenu> queryMesaMenuList();
}

sql

<select id="queryMesaMenuList" resultMap="queryMesaMenuList">
                select
                m.ID mID, m.MENU_NAME mMENU_NAME, m.MENU_ROUTE mMENU_ROUTE, m.IS_SHOW mIS_SHOW, m.MENU_NAME_CN mMENU_NAME_CN, m.SORT mSORT,
                mo.MODULE_NAME moMODULE_NAME,mo.ID moID, mo.MODULE_NAME_CN moMODULE_NAME_CN,mo.MESA_MENU_ID moMESA_MENU_ID,
                mi.ID miID,mi.IMG_NAME miIMG_NAME,mi.IMG_URL miIMG_URL
                from mesa_menu m
                left join mesa_menu_module mo on m.ID = mo.MESA_MENU_ID
                left join module_img mi on mo.ID = mi.MODULE_ID
    </select>
    <resultMap id="queryMesaMenuList" type="com.mesa.domain.vo.VoMesaMenu">
        <id property="id" column="mID"/>
        <result property="menuName" column="mMENU_NAME"/>
        <result property="menuRoute" column="mMENU_ROUTE"/>
        <result property="isShow" column="mIS_SHOW"/>
        <result property="menuNameCn" column="mMENU_NAME_CN"/>
        <result property="sort" column="mSORT"/>
        <collection property="mesaMenuModules" ofType="com.mesa.domain.entity.MesaMenuModule">
            <id property="id" column="moID"/>
            <result property="mesaMenuId" column="moMESA_MENU_ID"/>
            <result property="moduleName" column="moMODULE_NAME"/>
            <result property="moduleNameCn" column="moMODULE_NAME_CN"/>
            <collection property="moduleImgs" ofType="com.mesa.domain.entity.ModuleImg">
                <id property="id" column="miID"/>
                <result property="imgName" column="miIMG_NAME"/>
                <result property="imgUrl" column="miIMG_URL"/>
            </collection>
        </collection>
    </resultMap>

返回的json数据格式

{ 
  "status": 200,
  "message": "SUCCESS",
  "result": [
    { 
      "id": 1,
      "menuName": "homePage",
      "menuRoute": "/homePage",
      "isShow": 0,
      "menuNameCn": "首页",
      "sort": 0,
      "mesaMenuModules": [
        { 
          "id": 1,
          "mesaMenuId": 1,
          "moduleName": "publicSector",
          "moduleNameCn": "公共部分",
          "moduleImgs": [
            { 
              "id": 1,
              "moduleId": null,
              "imgName": "公共图片",
              "imgUrl": "https://tpl-c8d9f61-pic-tpl.websiteonline.cn/upload/banner_02_gbon.jpg"
            }
          ]
        },
        { 
          "id": 2,
          "mesaMenuId": 1,
          "moduleName": "aboutUs",
          "moduleNameCn": "关于我们",
          "moduleImgs": [
            { 
              "id": 2,
              "moduleId": null,
              "imgName": "关于我们大厦图片",
              "imgUrl": "https://tpl-c8d9f61-pic-tpl.websiteonline.cn/upload/news_03_dz52.png"
            },
            { 
              "id": 3,
              "moduleId": null,
              "imgName": "关于我们办公图片",
              "imgUrl": "https://tpl-c8d9f61-pic-tpl.websiteonline.cn/upload/news_03_dz52.png"
            },
            { 
              "id": 4,
              "moduleId": null,
              "imgName": "关于我们云图片",
              "imgUrl": "https://tpl-c8d9f61-pic-tpl.websiteonline.cn/upload/news_03_dz52.png"
            },
            { 
              "id": 5,
              "moduleId": null,
              "imgName": "关于我们电脑图片",
              "imgUrl": "https://tpl-c8d9f61-pic-tpl.websiteonline.cn/upload/news_03_dz52.png"
            }
          ]
        },
        { 
          "id": 3,
          "mesaMenuId": 1,
          "moduleName": "schoolNews",
          "moduleNameCn": "学校新闻",
          "moduleImgs": []
        },
        { 
          "id": 4,
          "mesaMenuId": 1,
          "moduleName": "businessCooperation",
          "moduleNameCn": "企业合作",
          "moduleImgs": []
        }
      ],
      "moduleImgs": null
    },
    { 
      "id": 2,
      "menuName": "schoolProfile",
      "menuRoute": "/schoolProfile",
      "isShow": 0,
      "menuNameCn": "学校简介",
      "sort": 1,
      "mesaMenuModules": [],
      "moduleImgs": null
    },
    { 
      "id": 3,
      "menuName": "newsCenter",
      "menuRoute": "/newsCenter",
      "isShow": 0,
      "menuNameCn": "新闻中心",
      "sort": 2,
      "mesaMenuModules": [],
      "moduleImgs": null
    },
    { 
      "id": 4,
      "menuName": "teachingSubjects",
      "menuRoute": "/teachingSubjects",
      "isShow": 0,
      "menuNameCn": "教学科目",
      "sort": 3,
      "mesaMenuModules": [],
      "moduleImgs": null
    },
    { 
      "id": 5,
      "menuName": "contactUs",
      "menuRoute": "/contactUs",
      "isShow": 0,
      "menuNameCn": "联系我们",
      "sort": 4,
      "mesaMenuModules": [],
      "moduleImgs": null
    }
  ]
}