解决JPA查询以查找连接列表中的最后一个条目 [英] Solving JPA query finding the last entry in connected list

问题描述

给出以下类结构:

class Job 
{
    String description;
    Collection<JobHistory> history;
}

class JobHistory
{
    Date assignDate;
    User jobOwner;
}

class JobOwner 
{
    String name;
    String id;
}

可以通过JPA在数据库上访问此类结构.在DAO层中，我可以用JPA语法编写查询.

This class-structure is accessible on the db via JPA. In the DAO-Layer I can write queries in JPA syntax.

问题:我想要一个具有Job和JobHistory条目的列表，该列表包含具有给定ID的给定所有者以及谁是作业Jobhistory中的最后一个(由assignDate排序).听起来很复杂，也许更简单:给我所有工作，然后JobHistory其中指定的所有者是该工作的实际所有者.

The Problem: I want a list with Job and JobHistory entries for a given owner with given id and who is the last one in the Jobhistory of the job (ordered by assignDate). Sounds quite complicated, perhaps simpler: give me all jobs and JobHistory where specified owner is the actual owner of the job.

更新:为清楚起见，我将稍微更改类的名称.

Update: for clarity I will slightly change the names of the classes.

class Job 
{
    String description;
    Collection<JobOwnerHistory> history;
}

class JobOwnerHistory
{
    Date assignDate;
    User jobOwner;
}

class JobOwner 
{
    String name;
    String id;
}

每个Job都有其所有者的历史记录，并按assignDate进行了排序.实际的所有者获得了最后分配的工作(即MAX(assignDate)).我想为每个作业找到特定用户User的JobOwnerHistory条目和MAX(assignDate).

Every Job has a history of his owners sorted by assignDate. The actual owner got the job last assigned (i.e. MAX(assignDate)). I want find for every job the JobOwnerHistory entry with MAX(assignDate) for a specific user User.

推荐答案

尝试:

SELECT j, j.history FROM Job j JOIN User u WHERE u.name = :name

如果要在EclipseLink中进行此操作，则需要对其稍作更改:

If I were to do this in EclipseLink, I would change it slightly:

public List<Job> getAllJobsForUser(String username) {
  List<Job> jobs = entityManager
    .createQuery("SELECT j FROM Job j JOIN User u WHERE u.name = :name")
    .setParameter("name", username)
    .setHint(QueryHints.BATCH, "j.history")
    .queryForList();
}

有什么区别吗?在第一个版本中，您将返回两个对象，因此您必须从List或Object数组中检索它们，而在第二个版本中，查询提示只是从(假定的)懒惰的一对多关系中加载所有作业历史记录

The difference? In the first version, you're returning two objects, so you have to retrieve them from a List or Object arrays whereas in the second, the query hint just loads all the job histories from an (assumedly) lazyy one-to-many relationship.

我不知道Hibernate是否具有与此等效的功能. Toplink Essentials没有.但这是我最喜欢的EclipseLink功能之一.

I don't know if Hibernate has an equivalent to this. Toplink Essentials doesn't. But it's one of my favourite features of EclipseLink.

哦，很明显，您可以(并且应该应该)使用命名查询而不是像我一样使用即席查询(因为可以在构建过程中对其进行验证).

Oh and obviously you can (and probably should) use a named query instead of an adhoc query like I've done (since those can be verified during the build).

这篇关于解决JPA查询以查找连接列表中的最后一个条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！