How to have filtering of multiple parameters passed as RequestParams in Spring Data Jpa?

Question

Suppose I have an Employee class with multiple fields like name, age, salary, designation, joiningDate, gender and many more. Now, how could I apply filtering with many such parameters? There could be many combinations possible in such cases. (For Eg. if I want to have 6 filters, then there can be total of 6! = 720 combinations possible!!!)

For only 2, 3 parameters, like age, salary, name; then I could write multiple if cases like:

if(age!=null && name==null && salary==null)
{
    findByAge
}
if(age==null && name!=null && salary==null)
{
    findByName
}
if(age!=null && name!=null && salary==null)
{
    findByAgeAndName
}

etc. like these with help of Spring Data JPA. But how to handle more parameters since combinations would increase with each RequestParams?

Could you please elaborate or give an example with the fields I have listed here. — Umang Kamdar, Oct 23 '21 at 11:52
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#specifications — Saeed Hassanvand, Oct 23 '21 at 11:58
Does this answer your question? [Filtering database rows with spring-data-jpa and spring-mvc](https://stackoverflow.com/questions/20280708/filtering-database-rows-with-spring-data-jpa-and-spring-mvc) — Saeed Hassanvand, Oct 23 '21 at 12:11
Sorry, but a newbie here! Can't get what exactly needs to do. — Umang Kamdar, Oct 23 '21 at 12:12

Harry Coder · Answer 1 · 2023-03-14T15:21:32.283

What you are looking for is a multi criteria query. You can use the very simple Example API

You will create an entity with the builder pattern. Lombok can help you in this step :

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "employee")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "name")
    private String name;
    @Column(name = "age")
    private int age;
    // your other fields

}

Then you can call your method :

Employee employee = Employee
    .builder()
    .name(nameValue)
    .age(ageValue)
    .build();

return repository.findAll(Example.of(employee));

You will find a full example here.

Yann39 · Answer 2 · 2021-10-23T13:33:01.150

A solution is to have a single method, and make parameters "optional" by managing null values in the query :

@Query("""
       select a from YourEntity a where
       (?1 is null or a.name = ?1)
       and (?2 is null or a.age= ?2)
       and (?3 is null or a.salary = ?3)
       and (?4 is null or a.description = ?4)
       and (?5 is null or a.joiningDate = ?5)
       and (?6 is null or a.gender = ?6)
       """)
List<YourEntity> findFiltered(String name, Integer age, Float salary, String designation, LocalDateTime joiningDate, String gender)

Huy Nguyen · Answer 3 · 2021-10-23T13:19:27.283

You should not write many condition and/or to call the function. It's hard to combine many condition.

In this case i often write the query which will append the condition if parameter is not null or if specify.

Here is my way:

String query = "select * from table where 1=1";
Map<String,Object> params = new HashMap<>();
if(filter1!=null){
   query += " and field1 = :filter1";
   params.put("filter1",filter1);
}
if(filter2!=null){
   query += " and field2 = :filter2";
   params.put("filter2",filter2);
}
dao.execute(query,params);

The condition null is not required, you can adapt it. In case you don't want to have many arguments in your function. You can create object/context/map to hold all filters.

PS: I use String for readability, you should use StringBuilder.

For spring-jpa: ExampleMatcher can do the same.

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example.matchers

How to have filtering of multiple parameters passed as RequestParams in Spring Data Jpa?

3 Answers3

Linked