在同一日期字段列上找到最小值和最大值，并使用jpa实体管理器条件 [英] Find min and max on the same date field column and count using jpa entity manager criteriabuilder

问题描述

我正在替换下面的sql查询并使用entitymanager条件构建器。我检查了其他博客和文档，但尚未成功。

I am working on replacing below sql query and use entitymanager criteriabuilder. I checked other blogs and documentation but haven't been successful yet.

SELECT MIN（creation_date），MAX（creation_date），COUNT（*），来源FROM creation_tbl，其中creation_date > =？ GROUP BY来源；

SELECT MIN(creation_date),MAX(creation_date),COUNT(*), source FROM creation_tbl where creation_date>=? GROUP BY source;

我当前的方法

                CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Feed> criteriaQuery = criteriaBuilder.createQuery(Feed.class);
    Root<Entity> root = criteriaQuery.from(Entity.class);

    Expression es = root.<Date>get("creation_date");

    criteriaQuery.where(criteriaBuilder.greaterThanOrEqualTo(es, dateSql));

    criteriaQuery.multiselect(criteriaBuilder.greatest(root.<Date>get("creation_date")),
            criteriaBuilder.least(root.<Date>get("creation_date")),
            root.get("source"),
            criteriaBuilder.count(root)).groupBy(root.get("source"));

    Feed resultlist = entityManager.createQuery(criteriaQuery).getSingleResult();

    System.out.println(resultlist);

ETL类

@Entity
@Table(name = "creation_tbl", schema = "test")
public class ETL implements Serializable {
    private static final long serialVersionUID = 3940341617988134707L;


    @Id
    @GeneratedValue
    private long id;

    @Temporal(TemporalType.DATE)
    @Column(name = "creation_date")
    private Date creation_date;


    @Column(name = "source")
    private String source;

    public ETL() {}


    public Date getCreation_date() {
        return creation_date;
    }

    public void setCreation_date(Date creation_date) {
        this.creation_date = creation_date;
    }

    public String getSource() {
        return source;
    }

    public void setSource(String source) {
        this.source = source;
    }
}

进餐等级

@Entity
public class Feed implements Serializable {
    private static final long serialVersionUID = 3940341617988134707L;

    @Id
    @GeneratedValue
    private long id;


    @Temporal(TemporalType.DATE)
    @Column(name = "max")
    private Date creationDateMax;

    @Temporal(TemporalType.DATE)
    @Column(name = "min")
    private Date creationDateMin;

    @Column(name = "source")
    private String source;

    @Column(name = "count")
    private Long count;

    public Feed(long id, Date creationDateMax, Date creationDateMin, String source, Long count) {
        this.id = id;
        this.creationDateMax = creationDateMax;
        this.creationDateMin = creationDateMin;
        this.source = source;
        this.count = count;
    }

    public Feed() {}


    public String getSource() {
        return source;
    }

    public void setSource(String source) {
        this.source = source;
    }

    public Date getCreationDateMax() {
        return creationDateMax;
    }

    public void setCreationDateMax(Date creationDateMax) {
        this.creationDateMax = creationDateMax;
    }

    public Date getCreationDateMin() {
        return creationDateMin;
    }

    public void setCreationDateMin(Date creationDateMin) {
        this.creationDateMin = creationDateMin;
    }

    public Long getCount() {
        return count;
    }

    public void setCount(Long count) {
        this.count = count;
    }
}

错误消息-

org.hibernate.PropertyNotFoundException：类中没有适当的构造函数：Feed

org.hibernate.PropertyNotFoundException: no appropriate constructor in class: Feed

推荐答案

您的查询仅选择4字段，但是您的 Feed 构造函数需要5个字段： long id 丢失。

Your query only selects 4 fields but your Feed constructor requires 5 fields: long id is missing.

因此，可以从构造函数中删除该参数，或者在分组时选择其他聚合的 id 。

So either remove that parameter from constructor or select an additional aggregated id when grouping.

P / s： Feed 看起来像DTO而不是实体，您无需注释这些字段

P/s: Feed looks like a DTO instead of entity, you do not need to annotate these fields

这篇关于在同一日期字段列上找到最小值和最大值，并使用jpa实体管理器条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！