Criteria API/HQL for this entity class

Question

I have the following entity class:

@Entity
@Table(name = "auditrecord", uniqueConstraints = { @UniqueConstraint(columnNames = {
    "accountid", "repositoryid" }) })
public class AuditRecordEntity {
  private UUID accountId;
  private UUID repositoryId;
  private Date accessTime;

  @Column(name = "accountid", nullable = false, updatable = false)
  public UUID getAccountId() {
    return accountId;
  }

  @Column(name = "repositoryid", nullable = false, updatable = false)
  public UUID getRepositoryId() {
    return repositoryId;
  }

  @Temporal(TemporalType.TIMESTAMP)
  @Column(name = "accesstime", nullable = false, updatable = true)
  public Date getAccessTime() {
    return accessTime;
  }

  // setters for above fields
}

Note the unique constraint on accountId+repositoryId, one account can only have one audit record for a specific repo, so there can be multiple audit records for the same repository, with each having a different accountid.

I want to get a list of the latest/most recent by access time AuditRecordEntitys for each specific repo, preferably using the criteria API.

It needs to slot in the space below:

CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();

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

// here


List<Predicate> predicates = new ArrayList<Predicate>();
// add predicates here.
entitySearchCriteria.addPredicates(predicates);

addEntityCriteria(criteriaBuilder, criteriaQuery, root, entitySearchCriteria, null, null);
return getPagedByQuery(criteriaQuery, pageSize, pageNumber);

score 1 · Answer 1 · edited May 23 '17 at 12:29

1

Try this.

DetachedCriteria maxDateQuery = DetachedCriteria.forClass(AuditRecordEntity.class);
ProjectionList proj = Projections.projectionList();
proj.add(Projections.max("accessTime"));
proj.add(Projections.groupProperty("repositoryId"));
maxDateQuery.setProjection(proj);

I am not sure if this will work, this should give you some idea on how to do this.

CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
Root<AuditRecordEntity> root = criteriaQuery.from(AuditRecordEntity.class);
Expression<Date> accessTime = root.get("accessTime");
criteriaQuery.select(criteriaBuilder.max(accessTime));
criteriaQuery.groupBy(root.get("userId"));
//other code

Reference : Answer

edited May 23 '17 at 12:29

Community

1
1

answered Jul 30 '14 at 09:56

bitkot

4,466
2
28
39

Thanks @AmitChotaliya, would you know how to fit this into my method above (I've updated my post)? Thanks – user2586917 Jul 31 '14 at 10:10
Thanks @HiberKnight, sorry for asking again :-( but now getting this compile error on the call to criteriaQuery.max(...) - Bound mismatch: The generic method max(Expression) of type CriteriaBuilder is not applicable for the arguments (Path – user2586917 Aug 04 '14 at 10:44
try the updated answer, also check the reference answer provided. – bitkot Aug 04 '14 at 15:15

Criteria API/HQL for this entity class

1 Answers1