java - 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.

标签： javaspringspring-bootspring-data-jpa

Answer 1

尽管您可以使用注释中建议的规范，但您可以尝试使用注释的自定义查询@Query。

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)

}

使用类似条件?1 is null允许您传递null不想在查询中使用的参数。所以你不再需要"NA"价值了。

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接受任何有效的 JPQL 查询。这意味着您可以使用任何 JPQL 函数。例如，如果您需要忽略大小写，您可以使用upper函数：

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