如何在多表关联时使用Java CriteriaBuilder的notLike方法

实体类定义 #

Recipe类 #

@jakarta.persistence.Entity 
public class Recipe implements Serializable {

private static final long serialVersionUID = 1L;

@jakarta.persistence.Id @jakarta.persistence.GeneratedValue
private Integer id;

private String name;

private String instructions;

private Boolean isVegetarian;

private String numberOfServings;

@jakarta.persistence.OneToMany(cascade = CascadeType.ALL)
@Valid
private List<@Valid Ingredient> includedIngredients = new ArrayList<>();

@jakarta.persistence.OneToMany(cascade = CascadeType.ALL)
@Valid
private List<@Valid Ingredient> excludedIngredients;

public Recipe() {
  super();
}

Ingredient类 #

@jakarta.persistence.Entity
public class Ingredient implements Serializable {

private static final long serialVersionUID = 1L;

@jakarta.persistence.Id @jakarta.persistence.GeneratedValue
private Integer id;

private String name;

private Boolean required;

public Ingredient() {
  super();
}

现有实现与问题 #

已成功用CriteriaBuilder实现包含指定食材的食谱查询，代码如下：

List<Predicate> ingredientsPredicates = new ArrayList<>();

for (Ingredient ingredient : fields.getIncludedIngredients()) {
    ingredientsPredicates.add(builder.like(builder.lower(recipe.get("includedIngredients").<String> get("name")), "%" + ingredient.getName().toLowerCase() + "%"));
}

allPredicates.add(builder.or(ingredientsPredicates.toArray(new Predicate[]{})));

但尝试实现不包含指定食材的查询时，直接使用notLike方法失败（该方法对Recipe的name字段有效，但对关联的includedIngredients列表无效），尝试的代码：

List<Predicate> ingredientsPredicates = new ArrayList<>();

for (Ingredient ingredient : fields.getExcludedIngredients()) {
    ingredientsPredicates.add(builder.notLike(builder.lower(recipe.get("includedIngredients").<String> get("name")), "%" + ingredient.getName().toLowerCase() + "%"));
}

allPredicates.add(builder.or(ingredientsPredicates.toArray(new Predicate[]{})));

解决方案 #

问题核心是：直接对集合字段用notLike会导致逻辑错误——只要集合中有一个元素不匹配条件，整个食谱就会被选中，而我们需要的是食谱不存在任何匹配指定名称的includedIngredients。

方法一：子查询+ID排除法 #

List<Predicate> excludePredicates = new ArrayList<>();

for (Ingredient excludeIngredient : fields.getExcludedIngredients()) {
    // 创建子查询：找出所有包含当前排除食材的Recipe ID
    Subquery<Integer> subquery = query.subquery(Integer.class);
    Root<Recipe> subRecipe = subquery.from(Recipe.class);
    Join<Recipe, Ingredient> subIngredient = subRecipe.join("includedIngredients");
    
    subquery.select(subRecipe.get("id"))
            .where(
                builder.like(
                    builder.lower(subIngredient.get("name")),
                    "%" + excludeIngredient.getName().toLowerCase() + "%"
                )
            );
    
    // 主查询条件：当前Recipe的ID不在子查询结果中
    excludePredicates.add(builder.not(builder.in(recipe.get("id")).value(subquery)));
}

// 所有排除条件必须同时满足（食谱不能包含任何指定的排除食材）
allPredicates.add(builder.and(excludePredicates.toArray(new Predicate[]{})));

方法二：notExists简洁实现（JPA 2.1+支持） #

List<Predicate> excludePredicates = new ArrayList<>();

for (Ingredient excludeIngredient : fields.getExcludedIngredients()) {
    Subquery<Ingredient> subquery = query.subquery(Ingredient.class);
    Root<Recipe> subRecipe = subquery.from(Recipe.class);
    Join<Recipe, Ingredient> subIngredient = subRecipe.join("includedIngredients");
    
    subquery.select(subIngredient)
            .where(
                builder.equal(subRecipe.get("id"), recipe.get("id")),
                builder.like(
                    builder.lower(subIngredient.get("name")),
                    "%" + excludeIngredient.getName().toLowerCase() + "%"
                )
            );
    
    excludePredicates.add(builder.notExists(subquery));
}

allPredicates.add(builder.and(excludePredicates.toArray(new Predicate[]{})));

逻辑说明 #

• 针对每个需排除的食材，先查询出所有包含该食材的食谱ID
• 主查询筛选出ID不在上述结果中的食谱，确保食谱完全不包含该食材
• 用and组合所有排除条件，保证食谱不包含任何指定的排除食材

内容的提问来源于stack exchange，提问作者humbleCoder