• 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);
  • 相关阅读:
    Vue实例
    Vue介绍
    Vue相关知识点记录
    JS面向对象设计-创建对象
    JS面向对象设计-理解对象
    软件工程基础 完结撒花
    深度学习 基于CNN的纹理合成实践【附python实现】
    图像处理 傅里叶正逆变换与余弦正逆变换 【附C++实现】
    Webviz
    Webviz
  • 原文地址:https://www.cnblogs.com/yangyongjie/p/10790347.html
Copyright © 2020-2023  润新知