CODEWITHSUNDEEP
javamysqlsqljpa
warmturtle987
warmturtle987

Reputation: 13

Complex queries using JPA

I have a mySQL database that stores information about cities by zip code including dog population and cat population. I would like to query the database for all zip codes that have a higher dog population than cat population, and order the results by the difference in descending order.

I'm using the JpaRepository class and have figured out how to create simple queries such as findByPopulation(int population) but is JPA capable of creating a complex query like the one I need?

Upvotes: 0

Views: 272

Answers (2)

HariKishore
HariKishore

Reputation: 169

I suggest add a native query to the repo.

@Query(value="",nativeQuery = true)
findByPopulation(@Param("population") int population);

Add your complex query in the value=""

Upvotes: 0

SKD
SKD

Reputation: 87

You can use Spring JPA Specification to build complex query.

I am not sure about your relationship but as per my understanding. Below code should be helpful.

 public static Specification<City> isDogsGreaterThanCats() {

return (root, query, cb) ->{ 

        return cb.greaterThan(root.get(dog_count), root.get(cat_count));

};

Repo method call will be like

cityRepository.findAll(isDogsGreaterThanCats());

Upvotes: 1

Related Questions