0

I have the following projection class and I want to retrieve data by joining Recipe and Ingredient tables from db using @Query in Spring data JPA:

public interface RecipeProjection {

        Long getId();
        String getTitle();
        
        List<Ingredient> getIngredients();
}

However, I cannot map the ingredients to the projection. Here is my query in the repository:

@Query(value = "SELECT r.id AS id, r.title, i.name AS ingredientName " +
        "FROM Recipe r " +
        "LEFT JOIN RecipeIngredient ri ON r.id = ri.recipeId " +
        "LEFT JOIN Ingredient i ON ri.ingredientId = i.id "
)
List<RecipeSearchProjection> getData();

I am not sure if using a proper alias for ingredient table can solve the problem, but even I tried, I cannot retrieve its data. So, is it possible to get nested data via Java Projection?

Jack
  • 1
  • 21
  • 118
  • 236

1 Answers1

1

I suggest using query methods where queries are derived from the method name directly without writing them manually. When interface-based projections are used, the names of their methods have to be identical to the getter methods defined in the entity class.

Try to define your method as:

List<RecipeSearchProjection> findAllBy();

However, projections can also be used with @Query annotation. For more details on the different ways to use JPA query projections, check out the blog post.

Toni
  • 3,296
  • 2
  • 13
  • 34
  • Thanks a lot for your answer but I could not understand what is the most proper way for my situation. Let's say I use query methods, then can I join multiple tables? Could you pls post an example for this scenario? – Jack Dec 04 '22 at 20:18
  • 1
    All entities associated with the entity are joined automatically if you define those associations in the entity class (`@ManyToMany` in this case). Try to add the method from the answer to your repository and check the data it fetches from the database, it should contain all data you are looking for. – Toni Dec 04 '22 at 21:21
  • **1.** In this scene, I think it is also possible to use a DTO and populate data to it instead of Projection, right? – Jack Dec 05 '22 at 00:04
  • **2.** Can I mix named query and `@Query` in the same Repository method? For example I am filtering records using named query, but I also need to use Text Search of PostgreSQL and I need to add a text parameter besides the others. Then, can I use something like this? I also created custom search filter, but now I also need to add a text search filter parameter to this custom filter, but I could not. Any idea? You may see details on https://stackoverflow.com/questions/74680426/how-to-create-custom-query-using-spring-data-jpa-specifications – Jack Dec 05 '22 at 00:09
  • 1. Yes. 2. No, you can't mix them, if you have @Query annotation, that query would be used instead of the query method. For dynamic queries you should use Specifications, QueryDSL, or CriteriaAPI. – Toni Dec 05 '22 at 07:00
  • Thanks a lot, marked as answer. Actually I integrated Criteria API and use Specifications. But in order to use a specific feature e.g. text search of PostgreSQL, I need to use `@Query`. On the other hand, in order to use dynamic filtering, I need to use `@Query Method` (because I need to make text search on database). Am I wrong? – Jack Dec 05 '22 at 07:11
  • You can't mix @Query and Specifications. [https://stackoverflow.com/a/26381869/10231374](https://stackoverflow.com/a/26381869/10231374) – Toni Dec 05 '22 at 07:29
  • Then, I have to use `@Query by passing search parameters. In this situation, can you clarify me these points pls? >>> – Jack Dec 05 '22 at 08:08
  • **1.** Can I make these parameters Optional in the @Query clause? I am not sure maybe using `?` . – Jack Dec 05 '22 at 08:08
  • **2.** As I need to pass these filter parameters (4 parameter) to my @Query, then how can I build my WHERE clause dynamically? I cannot use spec, but I am not sure if there is an aşternative for creating WHERE clause in my Query. Any idea? – Jack Dec 05 '22 at 08:10
  • 1. You can't use Optional parameters. 2. There is no way to dynamically write a query inside @Query annotation. – Toni Dec 05 '22 at 08:28
  • In this case I should use something e.g. LIKE for String parameters and true/false for boolean for passing the parameters all the time. Right? – Jack Dec 05 '22 at 08:33
  • Exactly, that can be used with specifications. – Toni Dec 05 '22 at 08:36
  • But of course I cannot use specifications with @Query and need to find a better way :(( – Jack Dec 05 '22 at 08:39