与QueryDSL和JPASQLQuery建立多对多关系 [英] Join a many to many relation with QueryDSL and JPASQLQuery

问题描述

我有以下实体:

@AllArgsConstructor
@EqualsAndHashCode(of = {"name"})
@Data
@NoArgsConstructor
@Entity
@Table(schema = "eat")
public class Pizza {

   @Id
   @GeneratedValue(strategy = GenerationType.SEQUENCE, generator="pizza_id_seq")
   private Integer id;

   @NotNull       
   private String name;

   @NotNull
   @Positive
   private Double cost;

   @ManyToMany
   @JoinTable(schema = "eat",
              name = "pizza_ingredient",
              inverseJoinColumns = { @JoinColumn(name = "ingredient_id") })
   private Set<Ingredient> ingredients;

}


@AllArgsConstructor
@EqualsAndHashCode(of = {"name"})
@Data
@NoArgsConstructor
@Entity
@Table(schema = "eat")
public class Ingredient {

   @Id
   @GeneratedValue(strategy = GenerationType.SEQUENCE, generator="ingredient_id_seq")
   private Integer id;

   @NotNull
   @Size(min=1, max=64)
   private String name;

}

我使用<代码> JPASQLQuery 通过提供的对象的 QueryDSL (4.2.2)中的PostgreSQL创建一些原生查询:

I'm using JPASQLQuery object provided by QueryDSL (4.2.2) to create some native queries in PostgreSQL:

public JPASQLQuery<T> getJPASQLQuery() {
   return new JPASQLQuery<>(
      entityManager,
      PostgreSQLTemplates.builder().printSchema().build()
   );
}

尝试使用 join 函数出现问题，例如:

The problem comes trying to use join functions, for example:

QIngredient ingredient = QIngredient.ingredient;
QPizza pizza = QPizza.pizza;

StringPath ingredientPath = Expressions.stringPath("ingredient");
StringPath pizzaPath = Expressions.stringPath("pizza");
NumberPath<Double> costPath = Expressions.numberPath(Double.class, "cost");
Expression rowNumber = SQLExpressions.rowNumber().over().partitionBy(ingredientPath).orderBy(costPath.desc()).as("rnk");

JPASQLQuery subQuery = getJPASQLQuery()
   .select(ingredient.name.as(ingredientPath), pizza.name.as(pizzaPath), pizza.cost.as(costPath), rowNumber)
   .from(pizza)
   // The error is in next innerJoin
   .innerJoin((SubQueryExpression<?>) pizza.ingredients, ingredient)
   .where(ingredient.name.in(ingredientNames));

如果我保持当前的<代码> innerJoin((SubQueryExpression<>)pizza.ingredients，成分?)我接收:

If I keep the current innerJoin((SubQueryExpression<?>) pizza.ingredients, ingredient) I receive:

类com.querydsl.core.types.dsl.SetPath不能转换到类com.querydsl.core.types.SubQueryExpression

我不能删除当前的(SubQueryExpression<?>)，因为 innerJoin 不接受 SetPath 作为参数.

I cannot remove current (SubQueryExpression<?>) because innerJoin doesn't accept SetPathas parameter.

另一方面，以下内容:

.from(pizza)               
.innerJoin(ingredient)

由于 pizza_ingredient 未包含在生成的查询中而无法使用.

Doesn't work due to pizza_ingredient is not included in the generated query.

如何使用<代码> innerJoin 在<代码> JPASQLQuery 有许多以像上面许多关系?

How can I use innerJoin in JPASQLQuery with a many to many relationship like above?

推荐答案

基本上，有试图解决它的两种主要方法:

Basically, there are two main approaches trying to solve it:

建议一位QueryDSL开发人员此处，替换为 JPASQLQuery >通过JPA替代方案.

As suggest one QueryDSL developer here, replacing JPASQLQuery by JPA alternatives.

首先重要的一点是将 name 属性添加到每个 @Table 批注中，因为内部是QueryDSL NativeSQLSerializer 类用于生成 from 和 join 子句.

First is important to add name property into every @Table annotation because internally is the one used by QueryDSL NativeSQLSerializer class to generate from and join clauses.

例如，

@Table(schema = "eat")
public class Pizza ...

应替换为:

@Table(name = "pizza", schema = "eat")
public class Pizza ...

接下来，为多对多表创建自定义的 Path :

Next, create for custom Path for the many to many table:

RelationalPathBase<Object> pizzaIngredient = new RelationalPathBase<>(Object.class, "pi", "eat", "pizza_ingredient");
NumberPath<Integer> pizzaIngredient_PizzaId = Expressions.numberPath(Integer.class, pizzaIngredient, "pizza_id");
NumberPath<Integer> pizzaIngredient_IngredientId = Expressions.numberPath(Integer.class, pizzaIngredient, "ingredient_id");

因此完整的代码应为:

QIngredient ingredient = QIngredient.ingredient;
QPizza pizza = QPizza.pizza;

RelationalPathBase<Object> pizzaIngredient = new RelationalPathBase<>(Object.class, "pi", "eat", "pizza_ingredient");
NumberPath<Integer> pizzaIngredient_PizzaId = Expressions.numberPath(Integer.class, pizzaIngredient, "pizza_id");
NumberPath<Integer> pizzaIngredient_IngredientId = Expressions.numberPath(Integer.class, pizzaIngredient, "ingredient_id");

StringPath ingredientPath = Expressions.stringPath("ingredient");
StringPath pizzaPath = Expressions.stringPath( "pizza");
NumberPath<Double> costPath = Expressions.numberPath(Double.class, "cost");

Expression rowNumber = SQLExpressions.rowNumber().over().partitionBy(ingredientPath).orderBy(costPath.desc()).as("rnk");
NumberPath<Long> rnk = Expressions.numberPath(Long.class, "rnk");

SubQueryExpression subQuery = getJPASQLQuery()
   .select(ingredient.name.as(ingredientPath), pizza.name.as(pizzaPath), pizza.cost.as(costPath), rowNumber)
   .from(pizza)
   .innerJoin(pizzaIngredient).on(pizzaIngredient_PizzaId.eq(pizza.id))
   .innerJoin(ingredient).on(ingredient.id.eq(pizzaIngredient_IngredientId))
   .where(ingredient.name.in(ingredientNames));

return getJPASQLQuery()
          .select(ingredientPath, pizzaPath, costPath)
          .from(
              subQuery,
              Expressions.stringPath("temp")
          )
          .where(rnk.eq(1l))
          .fetch();

这篇关于与QueryDSL和JPASQLQuery建立多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！