使用Criteria Builder获取每个人的最新记录

Question

我想知道如何使用Criteria Query生成此查询，我可以使用JPQL来实现，但需要使用JPA Criteria Query。查询为：

    select user_id, user_name,  borrowing_date
    from books_record b 
    where (user_id, borrowing_time) in (
    select br.user_id, max(br.borrowing_time)
       from books_record br
       group by br.user_id
    )

Answer 1

下面的标准应该是有效的，我还没有测试它

//sub query
DetachedCriteria subquery = DetachedCriteria.forClass(BooksRecord.class, "bk1");
ProjectionList proj1 = Projections.projectionList();
proj1.add((Projections.property("user_id").as("user_id"));
proj1.add(Projections.max("borrowing_time").as("borrowing_time"));
proj1.add(Projections.groupProperty("user_id"));
subquery.setProjection(proj1);

//main query
Criteria mainQueryCriteria = getSession().createCriteria(BooksRecord.class, "bk2");
mainQueryCriteria.add(Restrictions.eqProperty("bk2.user_id", "bk1.user_id"));
mainQueryCriteria.add(Restrictions.eqProperty("bk2.borrowing_time", "bk1.borrowing_time"));
ProjectionList proj2 = Projections.projectionList();
proj2.add((Projections.property("user_id").as("user_id"));
proj2.add(Projections.property("user_name").as("user_name"));
proj2.add(Projections.property("borrowing_date").as("borrowing_date"));
mainQueryCriteria.setProjection(proj2);
mainQueryCriteria.setResultTransformer(Transformers.aliasToBean(BooksRecord.class));

List<BooksRecord> records = mainQueryCriteria.list();