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>

 

转载于:https://www.cnblogs.com/yangyongjie/p/10790347.html