Can I create a generic @Query that detects which parameters from URL are not null and make a search only by them with Spring?

Question

Am I able to create a generic @Query that detects which parameters from URL are not null and make a search only by them instead of creating a method for each parameters combination with Spring Framework?

For example: I have two parameters in my URL; I wanna make only one query that detects the not-null parameters from my URL and pass them to my query, ignoring the nulls (instead of creating a lot of if/else and different queries for different parameters combination).

I created this code which returns a list of countries based on the NOT-NULL parameters the user passed in the URL. Since I have 4 parameters, all the possibilities result in 16 different queries (as I also have a findAll method, that brings all the results when no parameter is defined), and it's boring to create them all the time.

My controller:

@RequestMapping(value = "/countries", method = RequestMethod.GET, produces = "application/json")
public ResponseEntity listCountries(
        @RequestParam(value = "name", required = false) String name,
        @RequestParam(value = "twoLetterCode", required = false) String twoLetterCode,
        @RequestParam(value = "threeLetterCode", required = false) String threeLetterCode,
        @RequestParam(value = "nameInPortuguese", required = false) String nameInPortuguese) {
    try {

        List<Country> countryList = null;

        @Autowired
        private CountryRepository countryRepository;

        if (name != null && !name.isEmpty() && !name.equals("")) {
            if (twoLetterCode != null && !twoLetterCode.isEmpty() && !twoLetterCode.equals("")) {
                if (threeLetterCode != null && !threeLetterCode.isEmpty() && !threeLetterCode.equals("")) {
                    if (nameInPortuguese != null && !nameInPortuguese.isEmpty() && !nameInPortuguese.equals("")) {
                        countryList = countryRepository.findByNameTwoLetterCodeThreeLetterCodeNameInPortuguese(name, twoLetterCode, threeLetterCode, nameInPortuguese);
                    } else {
                        countryList = countryRepository.findByNameTwoLetterCodeThreeLetterCode(name, twoLetterCode, threeLetterCode);
                    }
                } else {
                    if (nameInPortuguese != null && !nameInPortuguese.isEmpty() && !nameInPortuguese.equals("")) {
                        countryList = countryRepository.findByNameTwoLetterCodeNameInPortuguese(name, twoLetterCode, nameInPortuguese);
                    } else {
                        countryList = countryRepository.findByNameTwoLetterCode(name, twoLetterCode);
                    }
                }
            } else {
                if (threeLetterCode != null && !threeLetterCode.isEmpty() && !threeLetterCode.equals("")) {
                    if (nameInPortuguese != null && !nameInPortuguese.isEmpty() && !nameInPortuguese.equals("")) {
                        countryList = countryRepository.findByNameThreeLetterCodeNameInPortuguese(name, threeLetterCode, nameInPortuguese);
                    } else {
                        countryList = countryRepository.findByNameThreeLetterCode(name, threeLetterCode);
                    }
                } else {
                    if (nameInPortuguese != null && !nameInPortuguese.isEmpty() && !nameInPortuguese.equals("")) {
                        countryList = countryRepository.findByNameNameInPortuguese(name, nameInPortuguese);
                    } else {
                        countryList = countryRepository.findByName(name);
                    }
                }
            }
        } else {
            if (twoLetterCode != null && !twoLetterCode.isEmpty() && !twoLetterCode.equals("")) {
                if (threeLetterCode != null && !threeLetterCode.isEmpty() && !threeLetterCode.equals("")) {
                    if (nameInPortuguese != null && !nameInPortuguese.isEmpty() && !nameInPortuguese.equals("")) {
                        countryList = countryRepository.findByTwoLetterCodeThreeLetterCodeNameInPortuguese(twoLetterCode, threeLetterCode, nameInPortuguese);
                    } else {
                        countryList = countryRepository.findByTwoLetterCodeThreeLetterCode(twoLetterCode, threeLetterCode);
                    }
                } else {
                    if (nameInPortuguese != null && !nameInPortuguese.isEmpty() && !nameInPortuguese.equals("")) {
                        countryList = countryRepository.findByTwoLetterCodeNameInPortuguese(twoLetterCode, nameInPortuguese);
                    } else {
                        countryList = countryRepository.findByTwoLetterCode(twoLetterCode);
                    }
                }
            } else {
                if (threeLetterCode != null && !threeLetterCode.isEmpty() && !threeLetterCode.equals("")) {
                    if (nameInPortuguese != null && !nameInPortuguese.isEmpty() && !nameInPortuguese.equals("")) {
                        countryList = countryRepository.findByThreeLetterCodeNameInPortuguese(threeLetterCode, nameInPortuguese);
                    } else {
                        countryList = countryRepository.findByThreeLetterCode(threeLetterCode);
                    }
                } else {
                    if (nameInPortuguese != null && !nameInPortuguese.isEmpty() && !nameInPortuguese.equals("")) {
                        countryList = countryRepository.findByNameInPortuguese(nameInPortuguese);
                    } else {
                        countryList = countryRepository.findAll();
                    }
                }
            }
        }


        if (countryList != null && !countryList.isEmpty()) {
            List<CountryDTO> dtosList = modelMapper.map(countryList, new TypeToken<CountryDTO>() {}.getType());
            return ResponseEntity(dtosList, HttpStatus.OK);
        } else {
            return ResponseEntity.status(HttpStatus.OK).body(new EmptySerializableClass());
        }

    } catch (Exception e) {
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
    }

}

My repository:

@Query("SELECT c FROM Country c WHERE c.name LIKE :name")
public List<Country> findByName(@Param("name") String name);

@Query("SELECT c FROM Country c WHERE c.twoLetterCode LIKE :twoLetterCode")
public List<Country> findByTwoLetterCode(@Param("twoLetterCode") String twoLetterCode);

@Query("SELECT c FROM Country c WHERE c.threeLetterCode LIKE :threeLetterCode")
public List<Country> findByThreeLetterCode(@Param("threeLetterCode") String threeLetterCode);

@Query("SELECT c FROM Country c WHERE c.nameInPortuguese LIKE :nameInPortuguese")
public List<Country> findByNameInPortuguese(@Param("nameInPortuguese") String nameInPortuguese);

@Query("SELECT c FROM Country c WHERE c.name LIKE :name AND c.twoLetterCode LIKE :twoLetterCode")
public List<Country> findByNameTwoLetterCode(@Param("name") String name, @Param("twoLetterCode") String twoLetterCode);

@Query("SELECT c FROM Country c WHERE c.name LIKE :name AND c.threeLetterCode LIKE :threeLetterCode")
public List<Country> findByNameThreeLetterCode(@Param("name") String name, @Param("threeLetterCode") String threeLetterCode);

@Query("SELECT c FROM Country c WHERE c.name LIKE :name AND c.nameInPortuguese LIKE :nameInPortuguese")
public List<Country> findBynameInPortuguese(@Param("name") String name, @Param("nameInPortuguese") String nameInPortuguese);

@Query("SELECT c FROM Country c WHERE c.twoLetterCode LIKE :twoLetterCode AND c.threeLetterCode LIKE :threeLetterCode)")
public List<Country> findByTwoLetterCodeThreeLetterCode(@Param("twoLetterCode") String twoLetterCode, @Param("threeLetterCode") String threeLetterCode);

@Query("SELECT c FROM Country c WHERE c.twoLetterCode LIKE :twoLetterCode AND c.nameInPortuguese LIKE :nameInPortuguese")
public List<Country> findByTwoLetterCodeNameInPortuguese(@Param("twoLetterCode") String twoLetterCode, @Param("nameInPortuguese") String nameInPortuguese);

@Query("SELECT c FROM Country c WHERE c.threeLetterCode LIKE :threeLetterCode AND c.nameInPortuguese LIKE :nameInPortuguese")
public List<Country> findByThreeLetterCodeNameInPortuguese(@Param("threeLetterCode") String threeLetterCode, @Param("nameInPortuguese") String nameInPortuguese);

@Query("SELECT c FROM Country c WHERE c.name LIKE :name AND c.twoLetterCode LIKE :twoLetterCode AND c.threeLetterCode LIKE :threeLetterCode)")
public List<Country> findByNameTwoLetterCodeThreeLetterCode(@Param("name") String name, @Param("twoLetterCode") String twoLetterCode, @Param("threeLetterCode") String threeLetterCode);

@Query("SELECT c FROM Country c WHERE c.name LIKE :name AND c.twoLetterCode LIKE :twoLetterCode AND c.nameInPortuguese LIKE :nameInPortuguese")
public List<Country> findByNameTwoLetterCodeNameInPortuguese(@Param("name") String name, @Param("twoLetterCode") String twoLetterCode, @Param("nameInPortuguese") String nameInPortuguese);

@Query("SELECT c FROM Country c WHERE c.name LIKE :name AND c.threeLetterCode LIKE :threeLetterCode AND c.nameInPortuguese LIKE :nameInPortuguese")
public List<Country> findByNameThreeLetterCodeNameInPortuguese(@Param("name") String name, @Param("threeLetterCode") String threeLetterCode, @Param("nameInPortuguese") String nameInPortuguese);

@Query("SELECT c FROM Country c WHERE c.twoLetterCode LIKE :twoLetterCode AND c.threeLetterCode LIKE :threeLetterCode AND c.nameInPortuguese LIKE :nameInPortuguese")
public List<Country> findByTwoLetterCodeThreeLetterCodeNameInPortuguese(@Param("twoLetterCode") String twoLetterCode, @Param("threeLetterCode") String threeLetterCode, @Param("nameInPortuguese") String nameInPortuguese);

@Query("SELECT c FROM Country c WHERE c.name LIKE :name AND c.twoLetterCode LIKE :twoLetterCode AND c.threeLetterCode LIKE :threeLetterCode AND c.nameInPortuguese LIKE :nameInPortuguese")
public List<Country> findByNameTwoLetterCodeThreeLetterCodeNameInPortuguese(@Param("name") String name, @Param("twoLetterCode") String twoLetterCode, @Param("threeLetterCode") String threeLetterCode, @Param("nameInPortuguese") String nameInPortuguese);

@Query("SELECT c FROM Country c")
public List<Country> findAll();

So, I guess you guys got it what my question is about. Instead of creating those millions if/else and thousands of queries for handling not-null parameters defined on the URL call, is there a way on Spring Framework - or even on JPA/Hibernate - to write only one query that detects those not-null parameters and search by them (and if all parameters are null, then just return all results)?

Possible duplicate of [Dynamic spring data jpa repository query with arbitrary AND clauses](https://stackoverflow.com/questions/28874135/dynamic-spring-data-jpa-repository-query-with-arbitrary-and-clauses) — Jens Schauder, May 17 '19 at 05:16

score 0 · Answer 1 · answered May 17 '19 at 05:14

You can't do it with @Query annotations. But you can use either Query by Example or Specifications.

With Query by Example you create an instance of your target entity Country that contains the filter values as attributes and null for those that should not get filtered. Also provide an ExampleMatcher that controls what kind of comparison should be used (equality, contains, case insensitive and so on).

See the documentation for details.

Even more flexible are Specifications where you construct the where condition programmatically using the JPA Criteria API or alternatively using Querydsl.

Again the documentation describes the details.

score 0 · Accepted Answer · answered May 28 '19 at 14:52

thanks to everyone, but I could do it! It was simple like that:

@Query("SELECT c FROM Country c WHERE (:name is null or c.name LIKE :name)"
+ " AND (:twoLetterCode is null or c.twoLetterCode LIKE :twoLetterCode)"
+ " AND (:threeLetterCode is null or c.threeLetterCode LIKE :threeLetterCode)"
+ " AND (:nameInPortuguese is null or c.nameInPortuguese LIKE :nameInPortuguese)")

public List<Country> findCountries(
@Param("name") String name, 
@Param("twoLetterCode") String twoLetterCode,
@Param("threeLetterCode") String threeLetterCode,
@Param("nameInPortuguese") String nameInPortuguese);

Can I create a generic @Query that detects which parameters from URL are not null and make a search only by them with Spring?

2 Answers2