• 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);
  • 相关阅读:
    Promise小结
    Jquery 一次处理多个ajax请求的代码
    for of 与 for in的区别
    三级联动效果
    最好的拖拽js
    Unicode转义(uXXXX)的编码和解码
    禁止遮罩层以下屏幕滑动
    director.js:客户端的路由---简明中文教程
    通过CSS的border绘制三角形
    概率图模型(PGM)学习笔记(四)-贝叶斯网络-伯努利贝叶斯-多项式贝叶斯
  • 原文地址:https://www.cnblogs.com/yangyongjie/p/10790347.html
Copyright © 2020-2023  润新知