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
}
]
}
就这?