JPQL not returning null value if combined with LIKE

Question

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

Add the additional condition (or the name is `null`). It returns exactly what you told it to return. — M. Deinum, Jan 04 '22 at 07:30
@M.Deinum hi sir, what I was trying to say was I need the result to be the same as the first result (which was shown as a JSON data), it worked at SSMS SQL Server it also returns the null value despite adding the new condition — M. Kel, Jan 04 '22 at 07:39
As stated it returns **exactly what you told it to return**. You added a restriction on the fullname to be **like** something. `null` isn't **like something** it is `null`, doesn't match so isn't included. Your initial query doesn't have this restsriction. If you want `null` to be returned as well you will have to write that in your query, which you currently haven't. — M. Deinum, Jan 04 '22 at 07:43
@M.Deinum Ah I see! Thank you very much for your further explanation, I understand now on what I have to do :) — M. Kel, Jan 04 '22 at 07:53
Does this answer your question? [Why is my t-sql left join not working?](https://stackoverflow.com/questions/40093809/why-is-my-t-sql-left-join-not-working) You need to put the `sup` conditions in the `ON` clause, not the `WHERE` — Charlieface, Jan 04 '22 at 09:36
@Charlieface hi charlie, it worked I can now see both the null and the non-null ones! Tho when I try to insert a parameter I it couldn't work.. for example `?superiorName=a`, it'll also show the null ones too — M. Kel, Jan 04 '22 at 09:57
Depends what logic you are trying to achieve. If you don't want to show those null rows when a parameter *is* provided then you probably need your own solution below — Charlieface, Jan 04 '22 at 10:28

score 0 · Answer 1 · answered Jan 04 '22 at 08:24

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

Don't, just include an `OR` clause in your query. Or better use the JPA criteria API (Or Sprnig Data JPA `JpaSpecificationExecutor` to dynamically build a query instead of creating massive amounts of finder methods for all possible (and impossible) combinations). — M. Deinum, Jan 04 '22 at 08:35
@M.Deinum I did try to add`AND (CONCAT(sup.firstName, ' ', sup.lastName) LIKE %?4% OR CONCAT(sup.firstName, ' ', sup.lastName) IS NULL)`. it worked, the only problem is when I set a parameter it also shows the null ones.. is there something missing in my code? — M. Kel, Jan 04 '22 at 09:15
Now that is something you didn't explain in your post. However if you want to dynamically include parameters I strongly suggest a read on how to use the [`JpaSpecificationExecutor`](https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/JpaSpecificationExecutor.html), this makes it much easier to write dynamic queries. — M. Deinum, Jan 04 '22 at 09:18
@M.Deinum Ah, sorry for the miss communication caused by me. I am new to stackoverflow and kinda nervous that Im giving redundant information. Okay looks exciting! I'll give it a try.. Once again thank you for your continuous consults I really appreciate it! :) — M. Kel, Jan 04 '22 at 09:26

score -1 · Answer 2 · answered Jan 04 '22 at 08:19

-1

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

answered Jan 04 '22 at 08:19

Xianghua

72
3

Hi! cmiiw but queries cant use 'use cases' since it is from T-SQL, JPQL only understands SQL – M. Kel Jan 04 '22 at 08:27

JPQL not returning null value if combined with LIKE

2 Answers2