How to filter an entity by multiple fields using a Spring JPA Repository?

user_13801131 0
Viewed 15773

I am writing back-end for renting flat web app and I have no idea how to filter flats from database (ex. rooms available, beds,floor or city). So I have 10 fields , so user can choose all of them in one search to filter flats and I just to wanted put in JPA repository all fields in one method but when I used only 5 of them it started freezing I couldn't add more fields.When I start to type for example NumberOfRooms it's just froze ,then by typing each letter also freezing.

1.Is there any limit for searching(filtering) fields ?

2.What is another way to do that?( I wanted to put everything in one method and in controller check if user used all filters or not, checking each field for null)

List<Flat> findAllByPriceBetweenAndCityAndRentORbuyAndUtilitiesBetweenAndNumberOfBedsBetweenAndNum(FastMoney less,FastMoney greater,String city,Boolean rentORbuy,int util1,int util2,int num1,int num2);
spring-data-jpa spring java
4 Answers

88-character query method? Please, don't!

Why would you write an 88-character method that's impossible to parse when you can write a nicely formatted multiline JPQL query, using Text Blocks for extra points?

Custom Implementations for Spring Data Repositories to the rescue!

If you want to build the query dynamically, use Criteria API. You can even benefit from the type-safe Metamodel.

So, here's what you need to do:

  1. Go to the Custom Implementations for Spring Data Repositories section in the Spring Data manual and see how to create a custom repository where you will add the findFlats method.
  2. Inside the new findFlats method use Criteria API to build the query dynamically.

That's it!

user_10251180
user_29943430
user_122672090

You may also simply use a library like this one, which lets you build dynamic filters (supports logical operators, comparators, enums, dates, booleans, joins, functions, and much more): https://github.com/turkraft/spring-filter

Example query:

/search?filter= average(ratings) > 4.5 and brand.name in ('audi', 'land rover') and (year > 2018 or km < 50000) and color : 'white' and accidents is empty

Usage:

@GetMapping(value = "/search")
public List<Entity> search(@EntityFilter Specification<Entity> spec, Pageable page) {
  return repo.findAll(spec, page);
}

Don't forget the dependency:

<dependency>
    <groupId>com.turkraft</groupId>
    <artifactId>spring-filter</artifactId>
    <version>0.9.5</version>
</dependency>

You may also check rsql, although it's a bit outdated now https://github.com/jirutka/rsql-parser

user_132736720
Related