JPAQuery日期分组查询

最新推荐文章于 2024-01-27 21:38:30 发布

工程师小A

最新推荐文章于 2024-01-27 21:38:30 发布

阅读量1k

点赞数

分类专栏： JAVA 文章标签： jpaQueryFactory sql

本文链接：https://blog.csdn.net/qq_34287953/article/details/126969926

版权

JAVA 专栏收录该内容

85 篇文章 0 订阅

订阅专栏

按照天分组查询

@Query(value = "select DATE_FORMAT(order_time,'%Y-%m-%d') as time,count(*) as count from order  where order_time >=:startTime and order_time<=:endTime group by orderTime", nativeQuery = true)
List<Map<String, Object>> countTrainingByDay(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

JPAQuery查询

QDevice qDevice = QDevice.device;
StringTemplate groupByDate =  Expressions.stringTemplate("DATE_FORMAT({0},'%Y-%m-%d')", qDevice.registerToNetworkDate);

StringTemplate returnDate =  Expressions.stringTemplate("DATE_FORMAT({0},'%m.%d')", qDevice.registerToNetworkDate);


List<Statistics> dayStatistics = jpaQueryFactory.select(Projections.constructor(Statistics.class, qDevice.id.count(), returnDate))
    .from(qDevice)
    .where(predicate)
    .where(qDevice.registerToNetworkDate.goe(startTime.atStartOfDay(zone).toInstant())
        .and(qDevice.registerToNetworkDate.loe(Instant.now())))
    .groupBy(groupByDate)
    .fetch();