如果與LIKE結合使用，JPQL不會回傳空值

嗨，我是 JPA 的新手，在添加到 JPA @Query 注釋時難以回傳空值，下面我將嘗試解釋我遇到的問題：

@Query("SELECT new dto.salesman.SalesmanGridDto("  
            "sal.id, CONCAT(sal.firstName, ' ', sal.lastName), "  
            "sal.level, CONCAT(sup.firstName, ' ', sup.lastName))"  
            "FROM Salesman sal "  
            "LEFT JOIN Salesman sup ON  sal.superior = sup.id "  
            "WHERE sal.id LIKE %?1% "  
            "AND CONCAT(sal.firstName, ' ', sal.lastName) LIKE %?2% "  
            "AND sal.level LIKE %?3% ")
    public List<SalesmanGridDto> getAllSalesmen(String employeeNumber, String employeeFullName,
                                                String employeeLevel, String superiorFullName,
                                                Pageable page);

它按預期作業，并給了我優越的全名以下是一些資料：

[
{
"salesmanEmployeeNumber": "J101",
"salesmanFullName": "Galuh Rajata",
"salesmanLevel": "Regional_Sales_Director",
"superiorFullName": null
},
{
"salesmanEmployeeNumber": "J109",
"salesmanFullName": "Wiwied Sunanto",
"salesmanLevel": "Retail_Sales",
"superiorFullName": "Banni Ayudhani"
},
{
"salesmanEmployeeNumber": "J200",
"salesmanFullName": "Angel Widyatmo",
"salesmanLevel": "Retail_Sales",
"superiorFullName": "Banni Ayudhani"
},
{
"salesmanEmployeeNumber": "J227",
"salesmanFullName": "Ulya Michele",
"salesmanLevel": "Inside_Sales_Representative",
"superiorFullName": null
}
]

但是在我為上級全名添加了一個新條件之后：

    @Query("SELECT new dto.salesman.SalesmanGridDto("  
            "sal.id, CONCAT(sal.firstName, ' ', sal.lastName), "  
            "sal.level, CONCAT(sup.firstName, ' ', sup.lastName))"  
            "FROM Salesman sal "  
            "LEFT JOIN Salesman sup ON  sal.superior = sup.id "  
            "WHERE sal.id LIKE %?1% "  
            "AND CONCAT(sal.firstName, ' ', sal.lastName) LIKE %?2% "  
            "AND sal.level LIKE %?3% "  
            "AND CONCAT(sup.firstName, ' ', sup.lastName) LIKE %?4%")
    public List<SalesmanGridDto> getAllSalesmen(String employeeNumber, String employeeFullName,
                                                String employeeLevel, String superiorFullName,
                                                Pageable page);

它只給了我那些擁有優越全名的人。（我想要的是他們也回傳null）

[
{
"salesmanEmployeeNumber": "J109",
"salesmanFullName": "Wiwied Sunanto",
"salesmanLevel": "Retail_Sales",
"superiorFullName": "Banni Ayudhani"
},
{
"salesmanEmployeeNumber": "J200",
"salesmanFullName": "Angel Widyatmo",
"salesmanLevel": "Retail_Sales",
"superiorFullName": "Banni Ayudhani"
},
{
"salesmanEmployeeNumber": "J567",
"salesmanFullName": "Jill Vianto",
"salesmanLevel": "Sales_Engineer",
"superiorFullName": "Banni Ayudhani"
},
{
"salesmanEmployeeNumber": "J889",
"salesmanFullName": "Olivia Puspasari",
"salesmanLevel": "Sales_Assistant",
"superiorFullName": "Banni Ayudhani"
}
]

我使用的 DTO 如下（我使用 Lombok）：

@Data
public class SalesmanGridDto implements Serializable {
    private final String salesmanEmployeeNumber;
    private final String salesmanFullName;
    private final String salesmanLevel;
    private final String superiorFullName;
}

控制器的默認值如下：

    @GetMapping
    @ResponseBody
    public List<SalesmanGridDto> getAll(
            @RequestParam(defaultValue = "1") Integer page,
            @RequestParam(defaultValue = "") String employeeNumber,
            @RequestParam(defaultValue = "") String name,
            @RequestParam(defaultValue = "") String employeeLevel,
            @RequestParam(defaultValue = "") String superiorName
    ){
        return service.getAllSalesmen(page, employeeNumber, name, employeeLevel,superiorName);
    }

我很困惑為什么在添加新條件之前記錄了空值如果引數為“”，在添加新條件后如何使空值也存在？或者，還有更好的方法？請賜教

uj5u.com熱心網友回復：

我在@M.Deinum 的幫助下有了一個解決方案，我現在明白它必須回傳一個值（不能為空）。因此我的解決方案是根據用戶的輸入創建 2 個查詢。

這里的區別是引數。

    @Query("SELECT new com.indocyber.basilisk.dto.salesman.SalesmanGridDto("  
            "sal.id, CONCAT(sal.firstName, ' ', sal.lastName), "  
            "sal.level, CONCAT(sup.firstName, ' ', sup.lastName))"  
            "FROM Salesman sal "  
            "LEFT JOIN Salesman sup ON  sal.superior = sup.id "  
            "WHERE sal.id LIKE %?1% "  
            "AND CONCAT(sal.firstName, ' ', sal.lastName) LIKE %?2% "  
            "AND sal.level LIKE %?3% ")
    public List<SalesmanGridDto> getAllSalesmen(String employeeNumber, String employeeFullName,
                                                String employeeLevel, Pageable page);

    @Query("SELECT new com.indocyber.basilisk.dto.salesman.SalesmanGridDto("  
            "sal.id, CONCAT(sal.firstName, ' ', sal.lastName), "  
            "sal.level, CONCAT(sup.firstName, ' ', sup.lastName))"  
            "FROM Salesman sal "  
            "LEFT JOIN Salesman sup ON  sal.superior = sup.id "  
            "WHERE sal.id LIKE %?1% "  
            "AND CONCAT(sal.firstName, ' ', sal.lastName) LIKE %?2% "  
            "AND sal.level LIKE %?3% "  
            "AND CONCAT(sup.firstName, ' ', sup.lastName) LIKE %?4%")
    public List<SalesmanGridDto> getAllSalesmen(String employeeNumber, String employeeFullName,
                                                String employeeLevel, String superiorFullName,
                                                Pageable page);

然后在@Service 部分，我添加了以下代碼：

    @Override
    public List<SalesmanGridDto> getAllSalesmen(Integer page, String employeeNumber, String name, String employeeLevel, String superiorName) {
        Pageable chosenPage = PageRequest.of((page - 1), 10);
        if (superiorName.trim().equals("")) {
            return salesmanRepository.getAllSalesmen(employeeNumber, name, employeeLevel, chosenPage);
        } else {
            return salesmanRepository.getAllSalesmen(employeeNumber, name, employeeLevel, superiorName, chosenPage);
        }
    }

我希望這對處理同樣問題的人有所幫助

uj5u.com熱心網友回復：

而不是添加 where 條件，您應該在 select 中使用 case

標籤：

上一篇：如何修復無法延遲初始化集合

下一篇：正則運算式小數大于0小于1，最多3個小數位