Dynamic query with @Query in Spring Data JPA?

Dynamic query with @Query in Spring Data JPA?

Problem Description:

I am using Specifications in my Spring Boot app and can filter result by different filter options. However, I need to use special filter with @Query in my repository method and as far as I see, I cannot build a dynamic WHERE clause in this query.

There are also QueryDSL and CriteriaAPI options, but I cannot find an example for using them in @Query.

So, is it possible to dynamically build WHERE clause or create filter for the query in @Query? Here is my method:

// there are more filters that omitted for brevity 

@Query("SELECT r FROM Recipe r WHERE r.title LIKE %:text%")
Page<Recipe> findByFields(@Param("text") String text);

I tried to use my specification in this method, but it is not possible to use them with @Query :((

Update:

@Entity
public class Recipe {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String title;

    @Enumerated(value = EnumType.STRING)
    private HealthLabel healthLabel;

    // code omitted for brevity

    @OneToMany(mappedBy = "recipe", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<RecipeIngredient> recipeIngredients = new ArrayList<>();
}

@Entity
public class RecipeIngredient {

    @EmbeddedId
    private RecipeIngredientId recipeIngredientId = new RecipeIngredientId();

    @Column(nullable = false)
    private BigDecimal amount;

    @ManyToOne(optional = true, fetch = FetchType.LAZY)
    @MapsId("recipeId")
    @JoinColumn(name = "recipe_id", referencedColumnName = "id")
    private Recipe recipe;

    @ManyToOne(optional = true, fetch = FetchType.LAZY)
    @MapsId("ingredientId")
    @JoinColumn(name = "ingredient_id", referencedColumnName = "id")
    private Ingredient ingredient;
}

@Entity
public class Ingredient {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(unique = true, nullable = false, length = 50)
    private String name;

    @OneToMany(mappedBy = "ingredient", cascade = CascadeType.ALL)
    private Set<RecipeIngredient> recipeIngredients = new HashSet<>();
}

Here is also my enum that I cannot filter by:

@Getter
@AllArgsConstructor
public enum HealthLabel {

    DEFAULT("Default"),
    EGG_FREE("Egg-free"),
    VEGETARIAN("Vegetarian"),
    WHEAT_FREE("Wheat-free");

    private String label;
}

Solution – 1

@Query can only do static queries.

If you want something more dynamic you have to use another feature, for example Specifications or even fall back to custom method implementations.

Solution – 2

You can try like this:

@Query("SELECT r FROM Recipe r WHERE r.title LIKE %?1%")
Page<Recipe> findByFields(String text);

Solution – 3

There is nothing preventing you from using a Specification (which is basically the Criteria API but easier to use). You basically want the dynamic version of the following JPQL.

SELECT r FROM Recipe r 
JOIN r.recipeIngredients ri 
JOIN ri.ingredient i
WHERE i.name LIKE :name

Now if you can write it in JPQL you can also use the Criteria API to write it (generally speaking).

public static Specification<Recipe> findByIngredientName(String name) {
  return (root, query, criteriaBuilder) -> {
     Join<Recipe, RecipeIngredient> ingredients = root.join("ingredients");
     Join< RecipeIngredient, Ingredient> ingredient = ingredients.join("ingredient");      
     return criteriaBuilder.like(ingredient.get("name"), "%" + name + "%"); 
  };
}

That is the specification to retrieve a Recipe containing an Ingredient with a name like something. You need 2 joins no query just a join.

You can now even combine multiple predicates by using Predicate.and. So if you create another Predicate for the Recipe.name you can just chain them together, JPA will handle the rest.

public static Specification<Recipe> findName(String name) {
  return (root, query, criteriaBuilder) -> { 
     return criteriaBuilder.like(root.get("title"), "%" + name + "%"); 
  };
}

Specification<Recipe> specs = findByName("recipe").and(findByIngredientName("ingredient"));
recipeRepository.findAll(specs);

That is all you need. What and how you receive those parameters that is up to you how you build the request/response objects and is highly subjective. If you want a sort order use the findAll which takes a Specification and a Sort

Specification<Recipe> specs = findByName("recipe").and(findByIngredientName("ingredient"));
recipeRepository.findAll(specs, Sort.by("title"));

The above will generate a SQL in the form of

SELECT recipesapp0_.id as id1_1_, recipesapp0_.title as title2_1_ FROM recipes_application$recipe recipesapp0_ 
INNER JOIN recipes_application$recipe_ingredient recipeingr1_ ON recipesapp0_.id=recipeingr1_.recipe_id 
INNER JOIN recipes_application$ingredient recipesapp2_ ON recipeingr1_.ingredient_id=recipesapp2_.id 
WHERE (recipesapp0_.title like ?) AND (recipesapp2_.name like ?) ORDER BYrecipesapp0_.title ASC

NOTE: Next time when someone asks for clarification of your use-case please give it instead of being unfriendly to them!