选择实体中包含的列表的子集 [英] Select subset of the list contained in an entity

问题描述

说我想获取 MyEntity 的所有ID小于10的行。该实体包含另一个实体。我希望仅由 listAnother 的子集来获取此列表。此子集仅包含另一个，其中包含的用户是特定子集。

Say I want to get all rows of the MyEntity that have an id lower than 10. This entity contains a list of Another entity. I would like this list to be fetched only by a subset of the listAnother. This subset containing only Another where the user contained in it is a specific one.

基本上在SQL中是这样的：

Basically in SQL it would be like this :

SELECT * FROM myentity m
LEFT JOIN another a
ON m.idTable=a.my_entity
AND a.user = "test1"
WHERE m.idTable < 10;

但是我没有设法将查询转换为jpql。

I didn't manage however to translate this query to jpql.

我的实体如下：

@Entity
public class MyEntity implements Serializable {    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int idTable;

    @OneToMany(mappedBy = "myEntity")
    private List<Another> listAnother;

}

@Entity
public class Another implements Serializable {    
    @Id
    private int idAnother;

    // bi-directional many-to-one association to Thethread
    @ManyToOne(fetch = FetchType.LAZY)
    private MyEntity myEntity;

    @ManyToOne(fetch = FetchType.LAZY)
    private User user;
}

@Entity
public class User implements Serializable {

    @Id
    private String username;
}

---

在jpa中我可以做

---

In jpa I could do this :

SELECT m FROM MyEntity where m.idTable < 10;

然后对于我从此列表中获得的每个实体，将其称为：

And then for each entity I get from this list call this:

SELECT a FROM Another Where a.user.username=:'test' AND a.myEntity=:entity;

但是我想在一个查询中一次完成所有操作。我可以使用标准吗？我没有花时间学习它，但是如果有可能的话，我会的。

However I would like to do it all at once in one query. Can I do this with criteria ? I didn't take the time to learn it but if it's possible then I will.

推荐答案

JPQL和Critaria API在您可以用它们表达什么的条款。

JPQL and Critaria API are equal in terms of what you can express with them. What is possible with JPQL is possible with Criteria and vice versa.

使用JPQL，您可以通过以下方式将两个查询合并为一个：

With JPQL, you can simply combine your 2 queries into one in this way:

SELECT a FROM Another a Where a.user.username=:test AND a.myEntity.idTable < 10

您可以使用点表示法（。）联接查询中的多个实体，前提是该关系是一对一的。如果您具有X对多关系，则需要使用JPQL JOIN，这不是很复杂。 （LEFT）JOIN的示例：

You can use dot notation (.) to join multiple entities in the query, provided that the relationship is X-to-one. If you have X-to-many relationship, you need to use JPQL JOIN, which is not very complicated. Example with (LEFT) JOIN:

SELECT m FROM MyEntity m LEFT JOIN m.listAnother a Where a.user.username=:test AND m.idTable < 10

结果当然不相等-在第一种情况下，您将获得另一个实体的列表，您可以通过a.myEntity获取MyEntity，在第二种情况下，您将获得MyEntity列表，这些列表都至少具有一个具有给定用户的另一个实体

The result is of course not equal - in first case you will get list of Another entities and you can get MyEntity by a.myEntity, in the second case you will get list of MyEntity, which all have at least one Another entity with given user

这篇关于选择实体中包含的列表的子集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！