spring jpa nativeQuery

冬风孤立

于 2022-12-07 10:59:56 发布

阅读量611

点赞数

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

本文链接：https://blog.csdn.net/qq_41921994/article/details/128216576

版权

springboot 同时被 2 个专栏收录

16 篇文章 1 订阅

订阅专栏

数据库

3 篇文章 0 订阅

订阅专栏

    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();
    }
}