1、需要的结果数据格式为
{ "responseCode": "0000", "responseMsg": null, "data": [ { "genreId": "6015", "genreName": "财务", "appRankDtos": [ { "ranking": "10", "rankDate": "2019-04-22" }, { "ranking": "8", "rankDate": "2019-04-23" }, { "ranking": "9", "rankDate": "2019-04-24" } ] }, { "genreId": "6007", "genreName": "应用总榜", "appRankDtos": [ { "ranking": "20", "rankDate": "2019-04-22" }, { "ranking": "28", "rankDate": "2019-04-23" } ] } ] }
创建的bean
RankResult:
public class RankResult extends CommonResult { /** * CommentResult中包含responseCode和responseMsg */ private List<RankGenreResult> data; public List<RankGenreResult> getData() { return data; } public void setData(List<RankGenreResult> data) { this.data = data; } }
RankGenreResult:
public class RankGenreResult { /** * 排名分类 */ private String genreId; /** * 分类名称 */ private String genreName; /** * 分类列表数据 */ private List<AppRankDto> appRankDtos; // 省略getter and setter }
AppRankDto:
public class AppRankDto { /** * 排名 */ private String ranking; /** * 排名时间 */ private String rankDate; // 省略getter and setter }
controller:
@ResponseBody @RequestMapping("queryAppRank") public RankResult queryAppRank(@RequestBody Map<String, String> param) { RankResult result = new RankResult(); List<RankGenreResult> rankGenreResultList; try { rankGenreResultList = commentService.queryAppRankGenreResult(param); result.setData(rankGenreResultList); } catch (Exception e) { result.fail(ResponseEnum.SYSTEM_ERROR.getResponseCode(), ResponseEnum.SYSTEM_ERROR.getResponseMsg()); LOGGER.error("查询XXX,e={}", ExceptionUtil.getAllStackTrace(e)); return result; } return result; }
service:
@Override public List<RankGenreResult> queryAppRankGenreResult(Map<String, String> param) { return commentMapper.queryAppRankGenreResult(param); }
mapper:
List<RankGenreResult> queryAppRankGenreResult(Map<String, String> param);
mapper.xml
<!--定义映射resultMap--> <resultMap id="rankGenreResult" type="RankGenreResult"> <result property="genreId" column="genre_id"/> <result property="genreName" column="genre_name"/> <collection property="appRankDtos" ofType="AppRankDto"> <result property="ranking" column="ranking"/> <result property="rankDate" column="rank_date"/> </collection> </resultMap>
<select id="queryAppRankGenreResult" resultMap="rankGenreResult"> select genre_id,genre_name,ranking,rank_date from t_app_rank <where> <if test="popId!=''and popId!=null"> t.POP_ID=#{popId} </if> <if test="marketAppId!=''and marketAppId!=null"> t.MARKET_APP_ID=#{marketAppId} </if> <if test="beginRankDate!=''and beginRankDate!=null"> <![CDATA[t.RANK_DATE>=#{beginRankDate}]]> </if> <if test="endRankDate!=''and endRankDate!=null"> <![CDATA[t.RANK_DATE<=#{endRankDate}]]> </if> </where> group by genre_id,GENRE_NAME,ranking,rank_date ORDER BY rank_date </select>
返回父菜单及其下的子菜单
需要的数据格式:
[ { "name": "一级菜单", "pid": 0, "sub_button": [ { "key": "", "name": "二级菜单1", "pid": 1, "sub_button": [], "type": "view", "url": "http://oa.cnsuning.com/portal/soa/index.htm" }, { "key": "", "name": "二级菜单2", "pid": 1, "sub_button": [], "type": "view", "url": "http://oa.cnsuning.com/portal/soa/index.htm" }, { "key": "", "name": "二级菜单4", "pid": 1, "sub_button": [], "type": "view", "url": "http://oa.cnsuning.com/portal/soa/index.htm" }, { "key": "", "name": "二级菜单5", "pid": 1, "sub_button": [], "type": "view", "url": "http://oa.cnsuning.com/portal/soa/index.htm" } ] }, { "name": "一级菜单2", "pid": 0, "sub_button": [ { "key": "", "name": "二级菜单21", "pid": 4, "sub_button": [], "type": "view", "url": "http://oa.cnsuning.com/portal/soa/index.htm" }, { "key": "", "name": "二级菜单22", "pid": 4, "sub_button": [], "type": "view", "url": "http://oa.cnsuning.com/portal/soa/index.htm" }, { "key": "", "name": "二级菜单23", "pid": 4, "sub_button": [], "type": "view", "url": "http://oa.cnsuning.com/portal/soa/index.htm" }, { "key": "", "name": "二级菜单24", "pid": 4, "sub_button": [], "type": "view", "url": "http://oa.cnsuning.com/portal/soa/index.htm" } ] }, { "name": "一级菜单", "pid": 0, "sub_button": [ { "key": "123456789", "name": "二级菜单1", "pid": 23, "sub_button": [], "type": "click", "url": "" } ] } ]
表结构:
CREATE TABLE `t_wechat_menu` ( `ID` bigint(15) NOT NULL AUTO_INCREMENT COMMENT '主键', `TYPE` varchar(10) DEFAULT NULL COMMENT '菜单的响应动作类型', `NAME` varchar(60) DEFAULT NULL COMMENT '菜单标题', `KEY` varchar(128) DEFAULT NULL COMMENT '菜单KEY值,用于消息接口推送', `URL` varchar(1024) DEFAULT NULL COMMENT '网页链接', `MEDIA_ID` varchar(40) DEFAULT NULL COMMENT '调用新增永久素材接口', `PID` bigint(15) DEFAULT '0', // 父菜单 `WECHAT_ID` bigint(20) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
pojo
public class WechatMenu { private transient Integer id;//JSON序列化时忽略此字段 private String url; private String type; private String name; private String key; private transient Integer pid;//JSON序列化时忽略此字段 private List<WechatMenu> sub_button = new ArrayList<WechatMenu>(); }
dao
@Override public List<WechatMenu> queryWechatMenuAndSub(@Param("wechatId") Integer wechatId) { return sqlSessionTemplate.selectList("weChatMenuDao.queryWechatMenuAndSub", wechatId); }
mapper.xml
<resultMap id="wechatMenuResultMap" type="com.suning.epps.fmmb.dmo.wechat.WechatMenu"> <result property="id" column="id"/> <result property="name" column="name"/> <result property="type" column="type"/> <result property="key" column="key"/> <result property="url" column="url"/> <collection column="id" property="sub_button" ofType="com.suning.epps.fmmb.dmo.wechat.WechatMenu" select="queryWechatMenuByPid"/> </resultMap> <select id="queryWechatMenuAndSub" resultMap="wechatMenuResultMap"> select t.ID,t.TYPE,t.NAME,t.KEY,t.URL from t_wechat_menu t where PID=0 and wechat_id=#{wechatId} </select> <select id="queryWechatMenuByPid" resultMap="wechatMenuResultMap" parameterType="String"> select t.ID,t.TYPE,t.NAME,t.KEY,t.URL from t_wechat_menu t where PID=#{id} </select>
service
// 转成json String json = JSON.toJSONString(menu, SerializerFeature.SkipTransientField);