spring jpa nativeQuery

本文介绍了一种基于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();
    }
}
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值