Best way to use query params in Spring Boot

Question

I have my JPA repository in place in code which is having multiple findByColumn method for fast lookup.

and few findBy clause with multiple columns like findByColumn1AndColumn2AndColumn3 and many more like or combination of multiple columns like mentioned.

My question is currently I expect my client to use just one endpoint for all the select/fetch operation which expect the RequestParam for each column and if he does not enter any expected column value on the request endpoint I set default as "NA" and I use if clause to identified all the incoming fields params and then call the respected repository endpoint.

I problem is now due to adding multiple new columns in the same endpoint request my if clause if become craze and I am confident enough code will not be user friendly for other team member to read.

Can anyone suggestion how to achieve this dynamic query part on the spring boot app.

My Controller Code snippet look like this:

@GetMapping("/getdata")
public ResponseEntity<Page<PojoTbl>> getDataFromDB(
        @PageableDefault(page = 0, size = 20) @SortDefault.SortDefaults({
                @SortDefault(sort = "id", direction = Sort.Direction.DESC) }) Pageable pageable,
        @RequestParam(value = "col1", required = false, defaultValue = "NA") String col1,
        @RequestParam(value = "col2", required = false, defaultValue = "NA") String col2,
        @RequestParam(value = "col3", required = false, defaultValue = "NA") String col3,
        @RequestParam(value = "col4", required = false, defaultValue = "NA") String col4,
        @RequestParam(value = "col5", required = false, defaultValue = "NA") String col5,
        @RequestParam(value = "col6", required = false, defaultValue = "NA") String col6,
        @RequestParam(value = "col7", required = false, defaultValue = "NA") String col7,
        @RequestParam(value = "col8", required = false, defaultValue = "NA") String col8,
        @RequestParam(value = "id", required = false) Long id) {

    Page<PojoTbl> selectedRecords = myService.findDataFromDB(col1,
            col2, col3, col4, col5, col6, col7, id,
            col8, pageable);

    return ResponseEntity.ok(selectedRecords);

}

Back-end service layer for the same look like this:

public Page<PojoTbl> findDataFromDB(String col1, String col2,
        String col3, String col4, String col5, String col6,
        String col7, Long id, String col8, Pageable pageable) {

    Page<PojoTbl> selectedRecords = null;

    if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findAll(pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && !col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol8IgnoreCase(col8, pageable);

    } else if (!col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol1IgnoreCase(col1, pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && !col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol5IgnoreCase(col5, pageable);

    } else if (col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol2IgnoreCase(col2, pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && !col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol6IgnoreCase(col6, pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && !col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol7IgnoreCase(col7, pageable);

    } else if (col1.equals("NA") && col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && !Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findById(id, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol1Andcol2IgnoreCase(col1, col2,
                pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && !col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol1Andcol2Andcol5IgnoreCase(col1,
                col2, col5, pageable);


    } else if (!col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && !col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository.findBycol1Andcol2Andcol6IgnoreCase(col1,
                col2, col6, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && col3.equals("NA")
            && col4.equals("NA") && !col6.equals("NA") && !col7.equals("NA")
            && Objects.isNull(id) && col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository
                .findBycol1Andcol2Andcol6Andcol7IgnoreCase(col1, col2,
                        col6, col7, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && !col3.equals("NA")
            && !col4.equals("NA") && !col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && !col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository
                .findBycol1Andcol2Andcol3Andcol4Andcol6IgnoreCase(
                        col1, col2, col3, col4, col6, pageable);

    } else if (!col1.equals("NA") && !col2.equals("NA") && !col3.equals("NA")
            && !col4.equals("NA") && col6.equals("NA") && col7.equals("NA")
            && Objects.isNull(id) && !col5.equals("NA") && col8.equals("NA")) {

        selectedRecords = tableJpaRepository
                .findBycol1Andcol2Andcol3Andcol4Andcol5IgnoreCase(
                        col1, col2, col3, col4, col5, pageable);

    }
    return selectedRecords;

}

See the complexity i have created in the service layer, this will going to create problem in future release as code looks messy due to many check in single if statement.

Hope someone can guide me on the best practices which can reduce the code complexity here.

Specification pattern is what you are after: See https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/ and https://stackoverflow.com/questions/46970689/multi-column-search-with-spring-jpa-specifications/46971053#46971053 and https://stackoverflow.com/questions/28678797/what-is-the-best-approach-to-reuse-multiple-repository-criterias/28679795#28679795 — Alan Hay, Feb 16 '20 at 11:04
also have a look at https://www.baeldung.com/rest-api-search-language-spring-data-specifications — guleryuz, Feb 16 '20 at 11:18
I have answered similar question here https://stackoverflow.com/questions/60243712/spring-data-jpa-named-query-ignoring-null-parameters/60246119#60246119 — Oleksii Valuiskyi, Feb 16 '20 at 11:46

Ken Bekov · Answer 1 · 2020-02-16T12:30:20.640

Though you can use specification as you were suggested in comments, you can try a custom query with @Query annotation.

public interface PersonRepository extends JpaRepository<PojoTbl, Integer> {

    @Query("select p from PojoTbj p where " + 
        "(p.col1 like ?1 or ?1 is null) and " +
        "(p.col2 like ?2 or ?2 is null) and " +
        "(p.col3 like ?3 or ?3 is null) and " +
        "(p.id = ?4 or id is null)"
    )
    Page<PojoTbl> findByParams(String param1, String param2, String param3, Long id, Pageable pageable)

}

Using conditions like ?1 is null allows you to pass null for params you don't want to use in the query. So you don't need "NA" value anymore.

public ResponseEntity<Page<PojoTbl>> getDataFromDB(
        @PageableDefault(page = 0, size = 20) @SortDefault.SortDefaults({
                @SortDefault(sort = "id", direction = Sort.Direction.DESC) }) Pageable pageable,
        @RequestParam(required = false) String col1,
        @RequestParam(required = false) String col2,
        @RequestParam(required = false) String col3,
        @RequestParam(value = "id", required = false, defaultValue=null) Long id) {

    Page<PojoTbl> selectedRecords = myService.findDataFromDB(col1,
            col2, col3, id, pageable);

    return ResponseEntity.ok(selectedRecords);

}

@Query accepts any valid JPQL query. That means you can use any JPQL functions with it. For example if you need to ignore case you can use upper function:

@Query("select p from PojoTbj p where " + 
    "(upper(p.col1) like upper(?1) or ?1 is null))

score 0 · Answer 2 · answered Feb 16 '20 at 12:12

By your repository methods I understand you are doing basic findBy for the fields that are available.

Take a look at Query by Example (QBE): documentation

Essentially you are filling all the present fields to the entity and ask for an Example type back.

Although this approach helps the dynamic nature of your case it comes with some limitations as are described in the documentation link above.

Best way to use query params in Spring Boot

2 Answers2