CODEWITHSUNDEEP
springhibernatejpahqlspring-data-jpa
Alessandro C
Alessandro C

Reputation: 3560

How to use Spring @Query with a Collection of elements to make the correct join between two tables?

I have the following structure for a Java persistence Entity-Table:

@Entity
@Table
public class Product {

    @Id
    private String id;

    @Column
    private String name;

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    private List<Price> prices;

    // getters and setters
}


@Entity
@Table
public class Price {

    @Id
    private String id;

    @Column
    private Float value;

    @Column
    private Strung type;

    // getters and setters
}

The Database is HQL. Tables have been created automatically, so I have this structure with 3 tables:

CREATE MEMORY TABLE PRODUCT (ID VARCHAR(255) NOT NULL PRIMARY KEY, NAME VARCHAR(255))   
CREATE MEMORY TABLE PRICE (ID VARCHAR(255) NOT NULL PRIMARY KEY, VALUE DOUBLE, TYPE VARCHAR(255))
CREATE MEMORY TABLE PRODUCT_PRICE (PRODUCT_ID VARCHAR(255) NOT NULL, PRICES_ID VARCHAR(255) NOT NULL, CONSTRAINT FK_SJ093JT9KRVI6PN4LQBDO30FU FOREIGN KEY(PRICES_ID) REFERENCES PRICE(ID),CONSTRAINT FK_H70YJF4SF4ABLK7MSXXV648Q9 FOREIGN KEY(PRODUCT_ID) REFERENCES PRODUCT(ID),CONSTRAINT UK_QJ093JT9KRVI4PN2LQBDO30FU UNIQUE(PRICES_ID))

The "intermediate" table PRODUCT_PRICE only contains 2 fields (the IDs to make the join between PRODUCT and PRICE).

Here there are some example datas:

INSERT INTO PRODUCT ('PRD001','Product 1');
INSERT INTO PRICE ('PRC001', 1000, 'euro');
INSERT INTO PRICE ('PRC002', 50, 'tax');
INSERT INTO PRODUCT_PRICE ('PRD001','PRC001');
INSERT INTO PRODUCT_PRICE ('PRD001','PRC002');

I have to do a query on the JPA Repository to search all products and their price type = 'euro'.

I want to use Spring @Query, with something like:

@Query("select p from Product p where p.prices.type = 'euro'")

How can I make the correct join? My example query generates an Hibernate Exception.

Thanks.

Upvotes: 0

Views: 1456

Answers (2)

Muhammed Riyas T
Muhammed Riyas T

Reputation: 71

Try this one, without @Query annotation

My Code

 List<User> findAllByAuthorities(Authority authority);

For u, you pass Price object where type is 'euro'

List<Product> findProductsByPrices(Price price);

Upvotes: 0

Vaelyr
Vaelyr

Reputation: 3176

You can simply add a join clause to the query.

@Query("SELECT p FROM Product p JOIN p.prices pr WHERE pr.type = 'euro'")

But as you are already using Spring Data, I would just advice using query methods instead of writing custom statements for such simple case.

Upvotes: 1

Related Questions