java - JPA仅选择每个项目的最新日期

因此，我有一个有效的jpa数据库查询，并从该表返回了所有SiteBmdfBusinessDay，并通过加入其ID列将它们与相应的SiteBmdf相关联。我想要做的是，而不是获取所有的SiteBmdfBusinessDay，而是仅获取具有最近businessDay的信息。

这是SiteBmdfBusinessDay类...

@Entity
public class SiteBmdfBusinessDay extends BaseEntity {

private static final long serialVersionUID = 1L;

private Long id;
private SiteBmdf siteBmdf;
private DateTime businessDay;
private Long failedCount;
private DateTime lastSuccessfulBusinessDay;
private DateTime lastSuccessfulFullBusinessDay;
private Ticket ticket;

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "site_bmdf_business_day_site_bmdf_business_day_id_seq")
@SequenceGenerator(name = "site_bmdf_business_day_site_bmdf_business_day_id_seq", sequenceName = "site_bmdf_business_day_site_bmdf_business_day_id_seq", allocationSize = 1)
@Column(name = "site_bmdf_business_day_id")
public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

@ManyToOne
@JoinColumn(name = "site_bmdf_id")
public SiteBmdf getSiteBmdf() {
    return siteBmdf;
}

public void setSiteBmdf(SiteBmdf siteBmdf) {
    this.siteBmdf = siteBmdf;
}

@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime", parameters = {
        @Parameter(name = "databaseZone", value = "UTC"), @Parameter(name = "javaZone", value = "UTC") })
public DateTime getBusinessDay() {
    return businessDay;
}

public void setBusinessDay(DateTime businessDay) {
    this.businessDay = businessDay;
}

public Long getFailedCount() {
    return failedCount;
}

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

@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime", parameters = {
        @Parameter(name = "databaseZone", value = "UTC"), @Parameter(name = "javaZone", value = "UTC") })
public DateTime getLastSuccessfulBusinessDay() {
    return lastSuccessfulBusinessDay;
}

public void setLastSuccessfulBusinessDay(DateTime lastSuccessfulBusinessDay) {
    this.lastSuccessfulBusinessDay = lastSuccessfulBusinessDay;
}

@Type(type = "org.jadira.usertype.dateandtime.joda.PersistentDateTime", parameters = {
        @Parameter(name = "databaseZone", value = "UTC"), @Parameter(name = "javaZone", value = "UTC") })
public DateTime getLastSuccessfulFullBusinessDay() {
    return lastSuccessfulFullBusinessDay;
}

public void setLastSuccessfulFullBusinessDay(DateTime lastSuccessfulFullBusinessDay) {
    this.lastSuccessfulFullBusinessDay = lastSuccessfulFullBusinessDay;
}

@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinColumn(name = "ticket_id")
public Ticket getTicket() {
    return ticket;
}

public void setTicket(Ticket ticket) {
    this.ticket = ticket;
}
}

还有SiteBmdf类...

@Entity
public class SiteBmdf extends BaseEntity {

private static final long serialVersionUID = 1L;

private Long id;
private String site;
private Long failureCount;
private Set<SiteBmdfBusinessDay> businessDays;
private List<SiteBmdfNote> notes;
private Resolution resolution;

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "site_bmdf_site_bmdf_id_seq")
@SequenceGenerator(name = "site_bmdf_site_bmdf_id_seq", sequenceName = "site_bmdf_site_bmdf_id_seq", allocationSize = 1)
@Column(name = "site_bmdf_id")
public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public String getSite() {
    return site;
}

public void setSite(String site) {
    this.site = site;
}

@Transient
public Long getFailureCount() {
    return failureCount;
}

public void setFailureCount(Long failureCount) {
    this.failureCount = failureCount;
}

@JsonIgnore
@OneToMany(mappedBy = "siteBmdf", orphanRemoval = true, cascade = CascadeType.ALL, fetch = FetchType.EAGER)
public Set<SiteBmdfBusinessDay> getBusinessDays() {
    return this.businessDays;
}

public void setBusinessDays(Set<SiteBmdfBusinessDay> businessDays) {
    this.businessDays = businessDays;
}

@OneToMany(mappedBy = "siteBmdf", orphanRemoval = true, cascade = CascadeType.ALL, fetch = FetchType.LAZY)
public List<SiteBmdfNote> getNotes() {
    Collections.sort(notes);
    return notes;
}

public void setNotes(List<SiteBmdfNote> notes) {
    this.notes = notes;
}

@ManyToOne
@JoinColumn(name = "resolutionId")
public Resolution getResolution() {
    return resolution;
}

public void setResolution(Resolution resolution) {
    this.resolution = resolution;
}
}

这是当前的查询方法。

@Override
public PageImpl<SiteBmdfBusinessDay> bmdfSitePaged(UiBuildCriteria criteria) {

    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<SiteBmdfBusinessDay> query = builder.createQuery(SiteBmdfBusinessDay.class);
    Root<SiteBmdfBusinessDay> root = query.from(SiteBmdfBusinessDay.class);

    if (!criteria.getSearch().getPredicateObject().isEmpty()) {
        List<Predicate> predicates = predicateBuilderService.buildPredicates(builder, root, criteria.getSearch());
        query.where(builder.and(predicates.toArray(new Predicate[] {})));
    }

    query.select(root);

    builder.max(root.<Long>get("businessDay"));

    // get the count for PageImpl
    Long total = this.count(criteria);

    // Set the order by
    List<CustomSort> defaultSorts = new ArrayList<CustomSort>(
            Arrays.asList(
                new CustomSort("businessDay", Boolean.TRUE),
                new CustomSort("siteBmdf.site", Boolean.FALSE)
            ));

    List<Order> orders = sortOrderBuilderService.buildSort(builder, root, criteria.getSort(), defaultSorts);

    query.orderBy(orders);

    List<SiteBmdfBusinessDay> content = entityManager.createQuery(query).setFirstResult(criteria.getPagination().getStart())
            .setMaxResults(criteria.getPagination().getNumber()).getResultList();

    return new PageImpl<>(content, criteria.getPageRequest(), total);
}

因此，就像我说的那样，当前结果只是SiteBmdfBusinessDay表中的所有项目及其对应的SiteBmdfs。我只希望每个站点都有最近businessDay的SiteBmdfBusinessDays。

例如，如果我有表site_bmdf_business_day：

site_bmdf_business_day_id | site_bmdf_id | business_day
1                         | 1            |  6/1/2011
2                         | 2            |  6/1/2011
3                         | 1            |  6/6/2011
4                         | 3            |  6/6/2011

我只想显示：

site_bmdf_business_day_id | site_bmdf_id | business_day
2                         | 2            |  6/1/2011
3                         | 1            |  6/6/2011
4                         | 3            |  6/6/2011

我知道我将需要一些类似于builder.max（root.get（“ businessDay”））的东西，但是我不确定如何实现它。

在这里的任何帮助将不胜感激。

请注意，this question是相似的，但并不是我正在寻找的，因为所有答案都在SQL中，并且在这里jpa中需要一些东西。

最佳答案

对于这些类型的查询，我通常将其简化为sql（没有分页和其他限制），然后将其反向构造为querybuilder。它使代码更清洁IMO。您正在寻找的是类似

select a.* from SiteBmdfBusinessDay a
inner join
( select site_bmdf_id, max (business_day) as business_day
  from SiteBmdfBusinessDay group by site_bmdf_id ) b
on (a.site_bmdf_id = b.site_bmdf_id) and (a.business_day = b.business_day)

假设每个site_bmdf_id每天最多有一个条目。

据我所知，它不能作为构建器使用，但是，您可以将其作为创建查询使用，并且效果很好（因为结果只是一个普通实体。）但是，这是一个性能极高的查询，假设您有大量数据。除非您的数据集很小，否则最好将其全部拿走，然后以编程方式过滤出重复项（您应该能够在O（n）时间内完成）。