JPQL not returning null value if combined with LIKE-CodePudding

Hi I am new to JPA and having a difficulty in returning null values when adding to the JPA @Query annotation, bellow I will try to explain the problem that I have:

@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);

It worked as expected and gave me the superior full name Here are some of the data:

[
{
"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
}
]

But after I added a new condition for the superior full name :

    @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);

it only gave me the ones who have a superior full name. (what I want is them to also return 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"
}
]

The DTO that I use is as below (Im using Lombok):

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

The default values from the Controller is as bellow:

    @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);
    }

I am confused to why the null values was recorded before I add the new condition How can I make the null values also present after I add the new condition if the param is ""? Or is there a better way? Please enlighten me

CodePudding user response：

I have a solution with the help of @M.Deinum, I now understand that it has to return a value (can't be null). therefore my solution is to create 2 Queries dependending on the input of the user.

Difference here is the parameter.

    @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);

then at the @Service section, I added the following code:

    @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);
        }
    }

I Hope this helps someone who dealt with the same problem

CodePudding user response：

Instead of adding the where condition, you should use case when in select