CODEWITHSUNDEEP
javaspringhibernatejpa
Saintka
Saintka

Reputation: 33

How to create a query with a parameter comparison condition with JPA?

I want create query which will select banners by category ReqName field and if there are more than one banners in the category, then display a banner with a large Price field.

I use JPA query. I managed create query, which select banners by category ReqName and displays text. Can I use JPA without resorting to Specification to make such a request described above?

Repository:

List<Banner> findByCategoryReqName(String req_name);

Test:

Iterable<Banner> test = bannerRepository.findByCategoryReqName("First");
test.forEach(t-> System.out.println(t.getText()));

Output:

SecondSecondSecondSecondSecondSecondSecondSecondSecondSecondSecondSecondSecondSecondSecondSecond
FirstFirstFirstFirstFirstFirstFirstFirstFirstFirstFirstFirstFirstFirstFirstFirstFirstFirstFirst

Upvotes: 1

Views: 717

Answers (1)

SternK
SternK

Reputation: 13111

You can try to use the following jpa query:

@Query("select b from Banner b where b.category.reqName = :reqName and b.price >= all (select ban.price from Banner ban where ban.category.reqName = :reqName) ")
List<Banner> findByCategoryReqName(@Param("reqName") String req_name);

See this section of hibernate documentation.

The query does not look very efficient, but it looks like to be more efficient you should write a native sql query.

As it's stated in jpa 2.2 specification:

Subqueries may be used in the WHERE or HAVING clause. Subqueries are restricted to the WHERE and HAVING clauses in this release. Support for subqueries in the FROM clause will be considered in a later release of this specification.

Upvotes: 1

Related Questions