此实体模型的Hibernate条件实现（子查询，自联接） [英] Hibernate criteria implementation for this entity model (subquery, self-join)

问题描述

给出以下实体一对多模型：

Given the following entity one-to-many model:

一个存储库可以链接到许多AuditRecords。

One Repository can be linked to many AuditRecords.

许多AuditRecords都可以链接到相同的存储库

Many AuditRecords can all link to the same Repository

@Entity
class AuditRecordEntity {
  private AuditRepositoryEntity auditRepository;  

  @ManyToOne(cascade = CascadeType.ALL)
  @JoinColumn(name = AUDIT_REPOSITORY_DB_COLUMN_NAME, nullable = false, updatable = false)
  public AuditRepositoryEntity getAuditRepository() {
    return auditRepository;
  }
  ...
}

@Entity
class AuditRepositoryEntity {
  private List<AuditRecordEntity> auditRecords = new ArrayList<AuditRecordEntity>();

  @OneToMany(mappedBy = "auditRepository")
  public List<AuditRecordEntity> getAuditRecords() {
    return auditRecords;
  }
  ...
}

较小校正下面的ERD图，对于'repositoryId'，请阅读'auditRepository'

Minor correction, in ERD diagram below, for 'repositoryId', read 'auditRepository'

我正在尝试实现Criteria API实现：

I am trying to get the Criteria API implementation to:

每个不同存储库的最新（按访问时间）AuditRecord？即审计记录列表，每个存储库一个，其中AuditRecord是该存储库的最后一个AuditRecord（如果存储库有许多AuditRecords）。

Get the latest (by accessTime) AuditRecord for each distinct Repository? I.e. a list of AuditRecords, one for each Repository, where the AuditRecord is the last AuditRecord for that Repository (in the case where a Repository has many AuditRecords).

要执行此操作的HQL查询：

I have the HQL query to do this:

select auditRecord from AuditRecordEntity auditRecord where auditRecord.accessTime = 
(select max(auditRecord2.accessTime) from AuditRecordEntity auditRecord2 where  
auditRecord2.auditRepository = auditRecord.auditRepository)

而是使用Criteria APi：

But need to use the Criteria APi instead:

CriteriaBuilder builder = getEntityManager().getCriteriaBuilder();
CriteriaQuery<Object> query = builder.createQuery();
Root<AuditRecordEntity> root = query.from(AuditRecordEntity.class);
// what next?

推荐答案

我已经使用来解决这个问题HQL查询的输出作为对标准API的输入：

I have got this to work(around) by using the output from the HQL query as input to the criteria API:

final List<UUID> auditRecordIds = execute("select auditRecord from AuditRecordEntity auditRecord where auditRecord.accessTime = 
(select max(auditRecord2.accessTime) from AuditRecordEntity auditRecord2 where  
auditRecord2.auditRepository = auditRecord.auditRepository)")

Root<AuditRecordEntity> root = criteriaQuery.from(AuditRecordEntity.class);
criteriaQuery.select(root);

List<Predicate> predicates = new ArrayList<Predicate>();
predicates.add(root.get("id").in(auditRecordIds.toArray()));
entitySearchCriteria.addPredicates(predicates);
...

这篇关于此实体模型的Hibernate条件实现（子查询，自联接）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！