Query in Spring Boot JPA - @OneToMany List relation-CodePudding

I've got entity like this:

@Entity
@Table(name = "formula")
 public class Formula {

@Id
@GeneratedValue(strategy = GenerationType.TABLE)
@Column(name = "formula_id")
private Long formulaId;

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

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

@Column(name = "time")
private int time;

@OneToMany(mappedBy = "formula",cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<Product> productList = new ArrayList<>();

And another Entity:

@Entity
@Table(name = "products")
public class Product {

@Id
@GeneratedValue(strategy = GenerationType.TABLE)
private Long productId;

@Column(name = "product_name")
private String productName;

@Column(name = "amount")
private Double amount;

@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "formula_id")
private Formula formula;

I want to ask Query to DB which help me get every type of data (by key word). I've got all except List of <Product>. It look like this:

public interface FormulaRepository extends JpaRepository<Formula, Long> {

@Query("SELECT f FROM Formula f WHERE "   "CONCAT(f.name, f.description, 
f.time)"   "LIKE %?1%")
List<Formula> findFormulaBy(String word);

How can add productList to Query and acomplished my searching? Is there any possibility to do this in findFormulaBy(String word); method?

CodePudding user response：

Change query to include LEFT JOIN FETCH to eagerly fetch productList. Also include DISTINCT to prevent duplicate Formula objects in List

    @Query("SELECT DISTINCT f FROM Formula f "  
            "LEFT JOIN FETCH f.productList "  
            "WHERE "   "CONCAT(f.name, f.description,f.time)"   "LIKE %?1%")
    List<Formula> findFormulaBy(String word);

SQL generated by Hibernate

2022-09-10 10:16:38.287 DEBUG   --- [           main] org.hibernate.SQL                        : 
    select
        distinct formula0_.formula_id as formula_1_9_0_,
        productlis1_.product_id as product_1_12_1_,
        formula0_.description as descript2_9_0_,
        formula0_.name as name3_9_0_,
        formula0_.time as time4_9_0_,
        productlis1_.amount as amount2_12_1_,
        productlis1_.formula_id as formula_4_12_1_,
        productlis1_.product_name as product_3_12_1_,
        productlis1_.formula_id as formula_4_12_0__,
        productlis1_.product_id as product_1_12_0__ 
    from
        formula formula0_ 
    left outer join
        products productlis1_ 
            on formula0_.formula_id=productlis1_.formula_id 
    where
        (
            formula0_.name||formula0_.description||formula0_.time
        ) like ?

I see in your comment you have added f.productList list to the CONCAT function which is why you are getting a SQL error. If you want to search product fields in CONCAT function you will need to give p.productList an alias and reference the fields in this way

    @Query("SELECT DISTINCT f FROM Formula f "  
            "LEFT JOIN FETCH f.productList p "  
            "WHERE "   "CONCAT(f.name, f.description,f.time,p.productName)"   "LIKE %?1%")
    List<Formula> findFormulaBy(String word);

This seems a strange way to search formulae and products and you will be better off adding a second parameter to your SQL

    @Query("SELECT DISTINCT f FROM Formula f "  
            "LEFT JOIN FETCH f.productList p "  
            "WHERE "   "CONCAT(f.name, f.description,f.time)"   "LIKE %?1% "  
            "AND p.productName = ?2 ")
    List<Formula> findFormulaBy(String word, String productName);