spring jpa nativeQuery

最新推荐文章于 2024-06-28 18:35:06 发布

冬风孤立

最新推荐文章于 2024-06-28 18:35:06 发布

阅读量734

点赞数

CC 4.0 BY-SA版权

分类专栏： springboot 数据库文章标签： spring java

本文链接：https://blog.youkuaiyun.com/qq_41921994/article/details/128216576

springboot 同时被 2 个专栏收录

16 篇文章

订阅专栏

数据库

3 篇文章

订阅专栏

本文介绍了一种基于Java的视图记录统计查询方法，包括按时间范围和频道ID进行查询的功能。通过使用Spring Data JPA和原生SQL查询，实现了对视图记录的数据统计，并返回了定制化的分页数据模型。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

    public LayuiPageModel<ViewRecordVO> statistics(int page, int limit, String channelId, String date) {
        String[] split = date.split(" - ");
        Page<Object[]> pages;
        List<ViewRecordVO> records = new ArrayList<>();
        if (StringUtils.isBlank(channelId)) {
            pages = viewRecordRepo.getViewRecordByCreateTime(split[0], split[1], PageRequest.of(page - 1, limit));
            objectToViewRecordVO1(pages, records);
        } else {
            pages = viewRecordRepo.getViewRecordByCreateTimeAndChannelId(split[0], split[1], channelId, PageRequest.of(page - 1, limit));
            objectToViewRecordVO2(pages, records);
        }
        LayuiPageModel<ViewRecordVO> layuiPageModel = new LayuiPageModel<>();
        layuiPageModel.setCode("0");
        layuiPageModel.setMsg("");
        layuiPageModel.setCount(pages.getTotalElements());
        layuiPageModel.setData(records);
        return layuiPageModel;
    }


    private void objectToViewRecordVO1(Page<Object[]> pages, List<ViewRecordVO> records) {
        for (Object[] objects : pages.getContent()) {
            ViewRecordVO viewRecordVO = new ViewRecordVO(
                    (Date) objects[0],
                    (BigInteger) objects[1],
                    (BigInteger) objects[2]
            );
            records.add(viewRecordVO);
        }
    }

    private void objectToViewRecordVO2(Page<Object[]> pages, List<ViewRecordVO> records) {
        for (Object[] objects : pages.getContent()) {
            ViewRecordVO viewRecordVO = new ViewRecordVO(
                    (Date) objects[0],
                    (BigInteger) objects[1],
                    (BigInteger) objects[2],
                    (String) objects[3]
            );
            records.add(viewRecordVO);
        }
    }

public interface ViewRecordRepo extends JpaRepository<ViewRecord, Long> {

    @Query(value = "SELECT DATE(createTime) atime,count(DISTINCT ip) uv,count(ip) pv from view_record WHERE createTime>=?1 AND createTime<?2 GROUP BY DATE(createTime) ORDER BY createTime DESC"
            , countQuery = "SELECT count(*) FROM (SELECT count(ip) pv from view_record WHERE createTime>=?1 AND createTime<?2 GROUP BY DATE(createTime)) as haha"
            , nativeQuery = true)
    Page<Object[]> getViewRecordByCreateTime(String start, String end, Pageable pageable);

    @Query(value = "SELECT DATE(createTime) atime,count(DISTINCT ip) uv,count(ip) pv,channelId from view_record WHERE createTime>=?1 AND createTime<?2 AND channelId=?3 GROUP BY DATE(createTime) ORDER BY createTime DESC"
            , nativeQuery = true
            , countQuery = "SELECT count(*) FROM (SELECT count(ip) pv from view_record WHERE createTime>=?1 AND createTime<?2 AND channelId=?3 GROUP BY DATE(createTime)) as haha")
    Page<Object[]> getViewRecordByCreateTimeAndChannelId(String start, String end, String channelId, Pageable pageable);

}

@Data
public class ViewRecordVO {
    private Date atime;
    private Long uv;
    private Long pv;
    private String channelId;


    public ViewRecordVO() {
    }

    public ViewRecordVO(Date atime, BigInteger uv, BigInteger pv, String channelId) {
        this.atime = atime;
        this.uv = uv ==null ? null: uv.longValue();
        this.pv = pv ==null ? null : pv.longValue();
        this.channelId = channelId;
    }

    public ViewRecordVO(Date atime, BigInteger uv, BigInteger pv) {
        this.atime = atime;
        this.uv = uv ==null ? null: uv.longValue();
        this.pv = pv ==null ? null : pv.longValue();
    }
}