Constructing a JPA Query Between Unrelated Entities – 在不相关的实体之间构建一个JPA查询

1. Overview

1.概述

In this tutorial, we’ll see how we can construct a JPA query between unrelated entities.

在本教程中，我们将看到如何在不相关的实体之间构建一个JPA查询。

2. Maven Dependencies

2.Maven的依赖性

Let’s start by adding the necessary dependencies to our pom.xml.

让我们先在我们的pom.xml中添加必要的依赖项。

First of all, we need to add a dependency for the Java Persistence API:

首先，我们需要为Java Persistence API添加一个依赖项。

<dependency>
   <groupId>javax.persistence</groupId>
   <artifactId>javax.persistence-api</artifactId>
   <version>2.2</version>
</dependency>

Then, we add a dependency for the Hibernate ORM which implements the Java Persistence API:

然后，我们为Hibernate ORM添加一个依赖关系，它实现了Java Persistence API。

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.4.14.Final</version>
</dependency>

And finally, we add some QueryDSL dependencies; namely, querydsl-apt and querydsl-jpa:

最后，我们添加一些QueryDSL依赖项；即querydsl-apt和querydsl-jpa。

<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-apt</artifactId>
    <version>4.3.1</version>
    <scope>provided</scope>
</dependency>
<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
    <version>4.3.1</version>
</dependency>

3. The Domain Model

3.领域模型

The domain of our example is a cocktail bar. Here we have two tables in the database:

我们这个例子的领域是一个鸡尾酒会。这里我们在数据库中有两个表。

• The menu table to store the cocktails that our bar sells and their prices, and
• The recipes table to store the instructions of creating a cocktail

These two tables are not strictly related to each other. A cocktail can be in our menu without keeping instructions for its recipe. Additionally, we could have available recipes for cocktails that we don’t sell yet.

这两张表并不是严格意义上的相互关联。一种鸡尾酒可以出现在我们的菜单中，而不保留其配方说明。此外，我们可以有尚未出售的鸡尾酒的可用配方。

In our example, we are going to find all the cocktails on our menu that we have an available recipe.

在我们的例子中，我们要找到菜单上所有有可用配方的鸡尾酒。

4. The JPA Entities

4.联合行动署的实体

We can easily create two JPA entities to represent our tables:

我们可以轻松地创建两个JPA实体来代表我们的表。

@Entity
@Table(name = "menu")
public class Cocktail {
    @Id
    @Column(name = "cocktail_name")
    private String name;

    @Column
    private double price;

    // getters & setters
}

@Entity
@Table(name="recipes")
public class Recipe {
    @Id
    @Column(name = "cocktail")
    private String cocktail;

    @Column
    private String instructions;
    
    // getters & setters
}

Between the menu and recipes tables, there is an underlying one-to-one relationship without an explicit foreign key constraint. For example, if we have a menu record where its cocktail_name column’s value is “Mojito” and a recipes record where its cocktail column’s value is “Mojito”, then the menu record is associated with this recipes record.

在menu和recipes表之间，存在一个基本的一对一的关系，没有一个明确的外键约束。例如，如果我们有一个menu记录，其cocktail_name列的值是 “Mojito”，一个recipes记录，其cocktail列的值是 “Mojito”，那么menu记录就与这个recipes记录相关。

To represent this relationship in our Cocktail entity, we add the recipe field annotated with various annotations:

为了在我们的Cocktail实体中表示这种关系，我们添加了带有各种注释的recipe字段。

@Entity
@Table(name = "menu")
public class Cocktail {
    // ...
 
    @OneToOne
    @NotFound(action = NotFoundAction.IGNORE)
    @JoinColumn(name = "cocktail_name", 
       referencedColumnName = "cocktail", 
       insertable = false, updatable = false, 
       foreignKey = @javax.persistence
         .ForeignKey(value = ConstraintMode.NO_CONSTRAINT))
    private Recipe recipe;
   
    // ...
}

The first annotation is @OneToOne, which declares the underlying one-to-one relationship with the Recipe entity.

第一个注解是@OneToOne,它声明了与Recipe实体的一对一关系。

Next, we annotate the recipe field with the @NotFound(action = NotFoundAction.IGNORE) Hibernate annotation. This tells our ORM to not throw an exception when there is a recipe for a cocktail that doesn’t exist in our menu table.

接下来，我们用@NotFound(action = NotFoundAction.IGNORE) Hibernate注解对recipe字段进行注释。这告诉我们的ORM，当cocktail的recipe在我们的menu表中不存在时，不要抛出一个异常。

The annotation that associates the Cocktail with its associated Recipe is @JoinColumn. By using this annotation, we define a pseudo foreign key relationship between the two entities.

将Cocktail与其关联的Recipe联系起来的注释是@JoinColumn。通过使用这个注解，我们在两个实体之间定义了一个伪外键关系。。

Finally, by setting the foreignKey property to @javax.persistence.ForeignKey(value = ConstraintMode.NO_CONSTRAINT), we instruct the JPA provider to not generate the foreign key constraint.

最后，通过将foreignKey属性设置为@javax.persistence.ForeignKey(value = ConstraintMode.NO_CONSTRAINT)，我们指示JPA提供者不产生外键约束。

5. The JPA and QueryDSL Queries

5.JPA和QueryDSL查询

Since we are interested in retrieving the Cocktail entities that are associated with a Recipe, we can query the Cocktail entity by joining it with its associated Recipe entity.

由于我们对检索与Recipe相关的Cocktail实体感兴趣，我们可以通过将Cocktail实体与其相关的Recipe实体连接起来来查询Cocktail。

One way we can construct the query is by using JPQL:

我们可以通过使用JPQL构建查询的一种方式。

entityManager.createQuery("select c from Cocktail c join c.recipe")

Or by using the QueryDSL framework:

或者通过使用QueryDSL框架。

new JPAQuery<Cocktail>(entityManager)
  .from(QCocktail.cocktail)
  .join(QCocktail.cocktail.recipe)

Another way to get the desired results is to join the Cocktail with the Recipe entity and by using the on clause to define the underlying relationship in the query directly.

获得所需结果的另一种方法是将Cocktail与Recipe实体连接起来，并通过使用on子句在查询中直接定义基础关系。

We can do this using JPQL:

我们可以用JPQL来做这件事。

entityManager.createQuery("select c from Cocktail c join Recipe r on c.name = r.cocktail")

or by using the QueryDSL framework:

或通过使用QueryDSL框架。

new JPAQuery(entityManager)
  .from(QCocktail.cocktail)
  .join(QRecipe.recipe)
  .on(QCocktail.cocktail.name.eq(QRecipe.recipe.cocktail))

6. One-To-One Join Unit Test

6.一对一连接单元测试

Let’s start creating a unit test for testing the above queries. Before our test cases run, we have to insert some data into our database tables.

让我们开始创建一个单元测试来测试上述查询。在我们的测试用例运行之前，我们必须向我们的数据库表插入一些数据。

public class UnrelatedEntitiesUnitTest {
    // ...

    @BeforeAll
    public static void setup() {
        // ...

        mojito = new Cocktail();
        mojito.setName("Mojito");
        mojito.setPrice(12.12);
        ginTonic = new Cocktail();
        ginTonic.setName("Gin tonic");
        ginTonic.setPrice(10.50);
        Recipe mojitoRecipe = new Recipe(); 
        mojitoRecipe.setCocktail(mojito.getName()); 
        mojitoRecipe.setInstructions("Some instructions for making a mojito cocktail!");
        entityManager.persist(mojito);
        entityManager.persist(ginTonic);
        entityManager.persist(mojitoRecipe);
      
        // ...
    }

    // ... 
}

In the setup method, we are saving two Cocktail entities, the mojito and the ginTonic. Then, we add a recipe for how we can make a “Mojito” cocktail.

在setup方法中，我们正在保存两个Cocktail实体，mojito和ginTonic。食谱，说明我们如何制作 “莫吉托”鸡尾酒。

Now, we can test the results of the queries of the previous section. We know that only the mojito cocktail has an associated Recipe entity, so we expect the various queries to return only the mojito cocktail:

现在，我们可以测试上一节的查询结果。我们知道，只有mojito cocktail有一个相关的Recipe 实体，所以我们希望各种查询只返回mojito cocktail。

public class UnrelatedEntitiesUnitTest {
    // ...

    @Test
    public void givenCocktailsWithRecipe_whenQuerying_thenTheExpectedCocktailsReturned() {
        // JPA
        Cocktail cocktail = entityManager.createQuery("select c " +
          "from Cocktail c join c.recipe", Cocktail.class)
          .getSingleResult();
        verifyResult(mojito, cocktail);

        cocktail = entityManager.createQuery("select c " +
          "from Cocktail c join Recipe r " +
          "on c.name = r.cocktail", Cocktail.class).getSingleResult();
        verifyResult(mojito, cocktail);

        // QueryDSL
        cocktail = new JPAQuery<Cocktail>(entityManager).from(QCocktail.cocktail)
          .join(QCocktail.cocktail.recipe)
          .fetchOne();
        verifyResult(mojito, cocktail);

        cocktail = new JPAQuery<Cocktail>(entityManager).from(QCocktail.cocktail)
          .join(QRecipe.recipe)
          .on(QCocktail.cocktail.name.eq(QRecipe.recipe.cocktail))
          .fetchOne();
        verifyResult(mojito, cocktail);
    }

    private void verifyResult(Cocktail expectedCocktail, Cocktail queryResult) {
        assertNotNull(queryResult);
        assertEquals(expectedCocktail, queryResult);
    }

    // ...
}

The verifyResult method helps us to verify that the result returned from the query is equal to the expected result.

verifyResult方法帮助我们验证从查询返回的结果是否等于预期结果。

7. One-To-Many Underlying Relationship

7.一对多的基础关系

Let’s change the domain of our example to show how we can join two entities with a one-to-many underlying relationship.

让我们改变我们的例子的领域，以展示我们如何用一对多的基础关系连接两个实体。

Instead of the recipes table, we have the multiple_recipes table, where we can store as many recipes as we want for the same cocktail.

我们有recipes表，而不是multiple_recipes表，在那里我们可以为同一种cocktail存储任意多的recipes。

@Entity
@Table(name = "multiple_recipes")
public class MultipleRecipe {
    @Id
    @Column(name = "id")
    private Long id;

    @Column(name = "cocktail")
    private String cocktail;

    @Column(name = "instructions")
    private String instructions;

    // getters & setters
}

Now, the Cocktail entity is associated with the MultipleRecipe entity by a one-to-many underlying relationship :

现在，Cocktail实体与MultipleRecipe实体之间是一对多的基础关系。

@Entity
@Table(name = "cocktails")
public class Cocktail {    
    // ...

    @OneToMany
    @NotFound(action = NotFoundAction.IGNORE)
    @JoinColumn(
       name = "cocktail", 
       referencedColumnName = "cocktail_name", 
       insertable = false, 
       updatable = false, 
       foreignKey = @javax.persistence
         .ForeignKey(value = ConstraintMode.NO_CONSTRAINT))
    private List<MultipleRecipe> recipeList;

    // getters & setters
}

To find and get the Cocktail entities for which we have at least one available MultipleRecipe, we can query the Cocktail entity by joining it with its associated MultipleRecipe entities.

为了找到并获得至少有一个可用的MultipleRecipe的Cocktail实体，我们可以通过将Cocktail实体与相关的MultipleRecipe实体连接起来来查询Cocktail实体。

We can do this using JPQL:

我们可以用JPQL来做这件事。

entityManager.createQuery("select c from Cocktail c join c.recipeList");

or by using the QueryDSL framework:

或通过使用QueryDSL框架。

new JPAQuery(entityManager).from(QCocktail.cocktail)
  .join(QCocktail.cocktail.recipeList);

There is also the option to not use the recipeList field which defines the one-to-many relationship between the Cocktail and MultipleRecipe entities. Instead, we can write a join query for the two entities and determine their underlying relationship by using JPQL “on” clause:

还有一个选项是不使用recipeList 字段，它定义了Cocktail和MultipleRecipe 实体之间的一对多关系。相反，我们可以为这两个实体写一个连接查询，并通过使用JPQL的 “on “子句来确定它们的基本关系。

entityManager.createQuery("select c "
  + "from Cocktail c join MultipleRecipe mr "
  + "on mr.cocktail = c.name");

Finally, we can construct the same query by using the QueryDSL framework:

最后，我们可以通过使用QueryDSL框架构建相同的查询。

new JPAQuery(entityManager).from(QCocktail.cocktail)
  .join(QMultipleRecipe.multipleRecipe)
  .on(QCocktail.cocktail.name.eq(QMultipleRecipe.multipleRecipe.cocktail));

8. One-To-Many Join Unit Test

8.一对多连接单元测试

Here, we’ll add a new test case for testing the previous queries. Before doing so, we have to persist some MultipleRecipe instances during our setup method:

在这里，我们将添加一个新的测试案例来测试之前的查询。在这样做之前，我们必须在我们的setup方法中持久化一些MultipleRecipe实例。

public class UnrelatedEntitiesUnitTest {    
    // ...

    @BeforeAll
    public static void setup() {
        // ...
        
        MultipleRecipe firstMojitoRecipe = new MultipleRecipe();
        firstMojitoRecipe.setId(1L);
        firstMojitoRecipe.setCocktail(mojito.getName());
        firstMojitoRecipe.setInstructions("The first recipe of making a mojito!");
        entityManager.persist(firstMojitoRecipe);
        MultipleRecipe secondMojitoRecipe = new MultipleRecipe();
        secondMojitoRecipe.setId(2L);
        secondMojitoRecipe.setCocktail(mojito.getName());
        secondMojitoRecipe.setInstructions("The second recipe of making a mojito!"); 
        entityManager.persist(secondMojitoRecipe);
       
        // ...
    }

    // ... 
}

We can then develop a test case, where we verify that when the queries we showed in the previous section are executed, they return the Cocktail entities that are associated with at least one MultipleRecipe instance:

然后我们可以开发一个测试用例，验证当我们在上一节展示的查询被执行时，它们会返回与至少一个MultipleRecipe实例相关的Cocktail实体。

public class UnrelatedEntitiesUnitTest {
    // ...
    
    @Test
    public void givenCocktailsWithMultipleRecipes_whenQuerying_thenTheExpectedCocktailsReturned() {
        // JPQL
        Cocktail cocktail = entityManager.createQuery("select c "
          + "from Cocktail c join c.recipeList", Cocktail.class)
          .getSingleResult();
        verifyResult(mojito, cocktail);

        cocktail = entityManager.createQuery("select c "
          + "from Cocktail c join MultipleRecipe mr "
          + "on mr.cocktail = c.name", Cocktail.class)
          .getSingleResult();
        verifyResult(mojito, cocktail);

        // QueryDSL
        cocktail = new JPAQuery<Cocktail>(entityManager).from(QCocktail.cocktail)
          .join(QCocktail.cocktail.recipeList)
          .fetchOne();
        verifyResult(mojito, cocktail);

        cocktail = new JPAQuery<Cocktail>(entityManager).from(QCocktail.cocktail)
          .join(QMultipleRecipe.multipleRecipe)
          .on(QCocktail.cocktail.name.eq(QMultipleRecipe.multipleRecipe.cocktail))
          .fetchOne();
        verifyResult(mojito, cocktail);
    }

    // ...

}

9. Many-To-Many Underlying Relationship

9.多对多的基础关系

In this section, we choose to categorize our cocktails in our menu by their base ingredient. For example, the base ingredient of the mojito cocktail is the rum, so the rum is a cocktail category in our menu.

在这一节中，我们选择在菜单中按基本成分对我们的鸡尾酒进行分类。例如，莫吉托鸡尾酒的基本成分是朗姆酒，所以朗姆酒是我们菜单中的一个鸡尾酒类别。

To depict the above in our domain, we add the category field into the Cocktail entity:

为了在我们的领域中描述上述内容，我们将category字段添加到Cocktail实体中。

@Entity
@Table(name = "menu")
public class Cocktail {
    // ...

    @Column(name = "category")
    private String category;
    
     // ...
}

Also, we can add the base_ingredient column to the multiple_recipes table to be able to search for recipes based on a specific drink.

此外，我们还可以在multiple_recipes表中添加base_ingredient列，以便能够根据特定的饮料来搜索食谱。

@Entity
@Table(name = "multiple_recipes")
public class MultipleRecipe {
    // ...
    
    @Column(name = "base_ingredient")
    private String baseIngredient;
    
    // ...
}

After the above, here’s our database schema:

完成上述工作后，这里是我们的数据库模式。

Now, we have a many-to-many underlying relationship between Cocktail and MultipleRecipe entities. Many MultipleRecipe entities can be associated with many Cocktail entities that their category value is equal with the baseIngredient value of the MultipleRecipe entities.

现在，我们在Cocktail和MultipleRecipeentities之间有一个多对多的基础关系。许多MultipleRecipe实体可以与许多Cocktail实体相关联，它们的category值与MultipleRecipe实体的baseIngredient值相等。

To find and get the MultipleRecipe entities that their baseIngredient exists as a category in the Cocktail entities, we can join these two entities by using JPQL:

为了找到并获得他们的baseIngredient作为类别存在于Cocktail实体中的MultipleRecipe实体，我们可以通过使用JPQL连接这两个实体。

entityManager.createQuery("select distinct r " 
  + "from MultipleRecipe r " 
  + "join Cocktail c " 
  + "on r.baseIngredient = c.category", MultipleRecipe.class)

Or by using QueryDSL:

或者通过使用QueryDSL。

QCocktail cocktail = QCocktail.cocktail; 
QMultipleRecipe multipleRecipe = QMultipleRecipe.multipleRecipe; 
new JPAQuery(entityManager).from(multipleRecipe)
  .join(cocktail)
  .on(multipleRecipe.baseIngredient.eq(cocktail.category))
  .fetch();

10. Many-To-Many Join Unit Test

10.多对多连接单元测试

Before proceeding with our test case we have to set the category of our Cocktail entities and the baseIngredient of our MultipleRecipe entities:

在继续我们的测试案例之前，我们必须设置Cocktail实体的category和MultipleRecipe实体的baseIngredient。

public class UnrelatedEntitiesUnitTest {
    // ...

    @BeforeAll
    public static void setup() {
        // ...

        mojito.setCategory("Rum");
        ginTonic.setCategory("Gin");
        firstMojitoRecipe.setBaseIngredient(mojito.getCategory());
        secondMojitoRecipe.setBaseIngredient(mojito.getCategory());

        // ...
    }

    // ... 
}

Then, we can verify that when the queries we showed previously are executed, they return the expected results:

然后，我们可以验证，当我们之前展示的查询被执行时，它们会返回预期的结果。

public class UnrelatedEntitiesUnitTest {
    // ...

    @Test
    public void givenMultipleRecipesWithCocktails_whenQuerying_thenTheExpectedMultipleRecipesReturned() {
        Consumer<List<MultipleRecipe>> verifyResult = recipes -> {
            assertEquals(2, recipes.size());
            recipes.forEach(r -> assertEquals(mojito.getName(), r.getCocktail()));
        };

        // JPQL
        List<MultipleRecipe> recipes = entityManager.createQuery("select distinct r "
          + "from MultipleRecipe r "
          + "join Cocktail c " 
          + "on r.baseIngredient = c.category",
          MultipleRecipe.class).getResultList();
        verifyResult.accept(recipes);

        // QueryDSL
        QCocktail cocktail = QCocktail.cocktail;
        QMultipleRecipe multipleRecipe = QMultipleRecipe.multipleRecipe;
        recipes = new JPAQuery<MultipleRecipe>(entityManager).from(multipleRecipe)
          .join(cocktail)
          .on(multipleRecipe.baseIngredient.eq(cocktail.category))
          .fetch();
        verifyResult.accept(recipes);
    }

    // ...
}

11. Conclusion

11.结语

In this tutorial, we presented various ways of constructing JPA queries between unrelated entities and by using JPQL or the QueryDSL framework.

在本教程中，我们介绍了在不相关的实体之间，通过使用JPQL或QueryDSL框架，构建JPA查询的各种方法。

As always, the code is available over on GitHub.

像往常一样，代码可在GitHub上获得。