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

就这?