spring data jpa查询自定义查询字段

最新推荐文章于 2024-04-28 15:56:46 发布

原创最新推荐文章于 2024-04-28 15:56:46 发布 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

Spring相关专栏收录该内容

34 篇文章

订阅专栏

本文介绍了一种使用SQL和Java实现的热门城市查询算法，通过分析旅游景点的页面浏览量，得出特定省份下最热门的城市列表。算法首先从数据库中获取满足条件的数据，然后将结果转换为易于处理的对象形式。

@Query(value = "SELECT a.city city,sum(m.page_view) view from t_ques m " +
            "LEFT JOIN attractions a on m.spot_name=a.name where m.ques_time<= ?1 and a.province_code=?2 GROUP BY a.city ORDER BY view desc", nativeQuery = true)
    List<Map<String, Object>> getHotCity(Date date, String pcode);

List<Map<String, Object>> cityHotBOList = quesRepository.getHotCity(dataDate, pcode);
        if(null == cityHotBOList || cityHotBOList.size() <= 0){
            return null;
        }
        for(int i = 0; i< top && i<cityHotBOList.size();i++){
            Map<String, Object> cityHotBO = cityHotBOList.get(i);
            HotCityResVO hotCityResVO = new HotCityResVO();
            hotCityResVO.setName((String)cityHotBO.get("city"));
            List<Attractions> attractionsList = attractionsRepository.findByCity((String)cityHotBO.get("city"));
            hotCityResVO.setCode(attractionsList.get(0).getCityCode());
            hotCityResVO.setNum((BigDecimal)cityHotBO.get("view"));
            hotCityResVOList.add(hotCityResVO);
        }