JPA 多条件(包括时间)统计查询

最新推荐文章于 2022-10-29 12:52:02 发布

坚持做笔记

最新推荐文章于 2022-10-29 12:52:02 发布

阅读量1.8k

点赞数

分类专栏： java 文章标签： java

本文链接：https://blog.youkuaiyun.com/u011562187/article/details/116461070

版权

java 专栏收录该内容

11 篇文章

订阅专栏

这篇博客探讨了如何在Java中利用JPA的Criteria API进行复杂的数据统计查询，包括日期格式化、分组及过滤条件。示例代码展示了如何创建一个统计每个操作员每天每种操作数量的查询，同时提供了对查询条件的灵活处理，如按项目ID、时间范围和操作员筛选。

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

几种写法关键代码:

这个与使用@Query 类似:

        Query query = entityManager.createQuery("SELECT new com.test.OperationStatisticItem( count(pl),function('date_format',pl.createdTime,'%Y-%m-%d') ,pl.action,pl.operator) from ProjectLogInfoEntity  pl " +
                "where pl.action <> '' " +
                " group by function('date_format',pl.createdTime,'%Y-%m-%d') ,pl.operator,pl.action");
        List<OperationStatisticItem> resultList = query.getResultList();

使用native query的就不写了.

推荐下面的写法:
统计每日(每人)(每种操作)的数量

@Data
@NoArgsConstructor
@AllArgsConstructor
public class OperationStatisticItem {
    private Long count;
    private String date;
    private ProjectLogAction action;
    private String operator;
}

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<OperationStatisticItem> cq = builder.createQuery(OperationStatisticItem.class);
        Root<ProjectLogInfoEntity> r = cq.from(ProjectLogInfoEntity.class);
        Expression<String> createdTime = builder.function("date_format", String.class, r.get("createdTime"), builder.literal("%Y-%m-%d"));
        cq.select(builder.construct(OperationStatisticItem.class, builder.count(r), createdTime, r.get("action"), r.get("operator")));
        List<Predicate> predicates = new ArrayList<>();
        if (projectId != null) {
            predicates.add(builder.equal(r.get("projectId"), projectId));
        }
        if (startTime != null) {
            predicates.add(builder.greaterThanOrEqualTo(r.get("createdTime"), startTime));
        }
        if (endTime != null) {
            predicates.add(builder.lessThan(r.get("createdTime"), endTime));
        }
        if (operator != null) {
            predicates.add(builder.lessThan(r.get("operator"), operator));
        }
        if (!CollectionUtils.isEmpty(actions)) {
            predicates.add(r.get("action").in(actions));
        }
        cq.where(predicates.toArray(new Predicate[0]));
        cq.groupBy(createdTime, r.get("operator"), r.get("action"));
        List<OperationStatisticItem> resultList = entityManager.createQuery(cq).getResultList();