Spring+Mybatis 复杂的分组查询

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);