mybatis collection多级嵌套查询

妖狐艹你老母 2022-10-22 12:58 156阅读 0赞

> `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
        }
      ]
    }

> `就这?`