If @RequestParameter is given, filter by it's value, if not do nothing

Question

I'm trying to use @RequestParam, and if the value is given it should filter from all items found in the database by this parameter, if not it should do nothing. I would also like to ask if functional programming is a good use here.

This is my Car class:

import lombok.AccessLevel;
import lombok.Data;
import lombok.Setter;

import javax.persistence.*;

@Data
@Entity
@Table(name = "Cars")
public class Car {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Setter(AccessLevel.NONE)
    private Long Id;

    private int yearOfProduction;
    private int price;
    private String color;
    private String brand;
    private String model;

    @ManyToOne
    @JoinColumn(name = "customer")
    private Customer customer;

    public Car() {
    }

    public Car(int yearOfProduction, int price, String color, String brand, String model) {
        this.yearOfProduction = yearOfProduction;
        this.price = price;
        this.color = color;
        this.brand = brand;
        this.model = model;
    }
}

This is the Controller where I set the parameters to ask for:

@GetMapping
public List<Car> getCars(@RequestParam(required = false) Integer minPrice,
                         @RequestParam(required = false) Integer maxPrice,
                         @RequestParam(required = false) String model){

    return carService.getCars(minPrice, maxPrice, model);
}

This is Car Service and what I would like to do:

public List<Car> getCars(Integer minPrice, Integer maxPrice, String model) {
    return carRepository
            .findAll()
            .stream()
            .filter(car ->
                      //if minPrice exists
                                car.getPrice() >= minPrice
                                 &&
                       //if maxPrice exists
                                 car.getPrice() <= maxPrice
                                 &&
                       //if model exists
                                 car.getModel().equals(model))
                      .collect(Collectors.toList());
}

I could set @RequestParam (defaultValue = "something") in controller, but it's problematic since I don't know what are the default values for "model" field, because every Car has a different model, and still I have to filter items by default values, and it's not needed since I don't want to do anything with that if it's not given.

I was also trying to pass Optional<> as the parameters and then check every parameter with if statement and ifPresent() method in filter function, but I don't how how to bring it together.

One simple rule to follow, don't do something in your code the database does it better than you. Filtering and sorting are better handled by the database. — b.GHILAS, Nov 06 '21 at 12:46
@b.GHILAS So you suggest to create for example 3 SQL queries in repository, then write 3 if statements in getCars() function, check if parameter exists and inside them execute a proper function ? Cause I don't know how to make all configurations of given parameters work in these queries, because some of them have a common elements for sure. Could you explain it in more details please? — 77jt777, Nov 06 '21 at 12:58
Update your question and provide the entity Car, I assume you want to get all the cars by default, and if there's any request params filter by it. If is this what you want, it's easy to accomplish with JPA. — b.GHILAS, Nov 06 '21 at 13:00
@b.GHILAS I updated it, and yes that is exacly what I want to do. — 77jt777, Nov 06 '21 at 13:12
@b.GHILAS is correct. Don't fetch all the values and filter in your service. Set default values to `*` and pass the values to your `@Repository` for use in a parameterized query (to avoid SQL injection attacks). — Paul, Nov 06 '21 at 13:16
Just do a findAll with example, and put your params in the example if they are null the query will ignore them, if not it will add the where clause. just look up findAll(Example example). — Roque Sosa, Nov 06 '21 at 15:05

b.GHILAS · Accepted Answer · 2021-11-06T16:00:19.397

4

You can create a jpa query like this (in your car repository):

@Query("select c from Car c where (?1 is null or c.price >= ?1) and (?2 is null or c.price <= ?2) and (?3 is null or c.model = ?3)")
List<Car> getCars(Integer minPrice, Integer maxPrice, String model);

and then call it from your CarService:

public List<Car> getCars(Integer minPrice, Integer maxPrice, String model) {
   return carRepository.getCars(minPrice, maxPrice, model);
}

One way to circumvent cast problem in postgresql, is to use default values for the params. Say you set 0 as default value for min price and max price and empty string for model.

@Query("select c from Car c where (?1 = 0 or c.price >= ?1) and (?2 = 0 or c.price <= ?2) and (?3 = '' or c.model = ?3)")
List<Car> getCars(Integer minPrice, Integer maxPrice, String model);

And in your controller:

@RequestParam(defaultValue="") String model
@RequestParam(defaultValue="0") Integer minPrice
@RequestParam(defaultValue="0") Integer maxPrice

edited Nov 06 '21 at 16:00

answered Nov 06 '21 at 13:16

b.GHILAS

2,273
1
8
16

Unfortunetely I'am getting error: ,,ERROR: operator does not exist: integer >= bytea Hint: No operator matches the given name and argument types. You might need to add explicit type casts." – 77jt777 Nov 06 '21 at 14:44
It works fine now, it shows no errors, but when I sent request it returned response code 200 but I got no results (no cars have been displayed) – 77jt777 Nov 06 '21 at 17:03
@77jt777 did you configure the default values well, what's the generated query ? – b.GHILAS Nov 06 '21 at 17:32
This is the only thing I get in console: Hibernate: select car0_.id as id1_0_, car0_.brand as brand2_0_, car0_.color as color3_0_, car0_.customer as customer7_0_, car0_.model as model4_0_, car0_.price as price5_0_, car0_.year_of_production as year_of_6_0_ from cars car0_ where ( ?=0 or car0_.price>=? ) and ( ?=0 or car0_.price<=? ) and ( ?='' or car0_.model=? ) – 77jt777 Nov 06 '21 at 17:52
@77jt777 are you sure that you configured the default values (minPrice and maxPrice to 0) and model to empty string ? – b.GHILAS Nov 06 '21 at 17:54
I missplaced 0 with empty string so nevermind! Everything works great, sorry for bothering you and thank you very much for your help and attention! – 77jt777 Nov 06 '21 at 18:11
@77jt777 no problem, another way to do this is using Jpa Specification (google it, It will help you create queries with multiple filters easily) – b.GHILAS Nov 06 '21 at 18:13
Okay, surely I will, thank you once again! – 77jt777 Nov 06 '21 at 18:25

If @RequestParameter is given, filter by it's value, if not do nothing

1 Answers1