Spring data repository query to retrieve values containing null

Question

I have following User table and repository.

User:

id;name;job;age
1;steve;nurse;33
2;steve;programmer;null
3;steve;programmer;null

Repository method:

@Query("SELECT u FROM User u WHERE ("
        + "LOWER(u.name) = LOWER(:name) AND "
        + "LOWER(u.beruf) = LOWER(:job) AND "
        + "LOWER(u.alter) = LOWER(:age))")
public List<User> findUsers(@Param("name") String name,
        @Param("job") String job,
        @Param("age") String age);

If I call the repository method with following parameters

String name = "steve";
String job = "programmer";
List<User> result = repository.findUsers(name, job, null); // empy list ..why ?

I get an empty list as result, although I expect to get the entities with id=2 and id=3 as result.

What am I doing wrong ? How should I change the query to get the two entities as result.

Thanks

First of all I don't understand why age is a String and not an int; in any case.. did you try your query by using a MySQL client? — Angelo Immediata, Jun 25 '18 at 16:24

score 0 · Answer 1 · answered Jun 25 '18 at 16:24

According to the documentation this behaviour is normal there is no way to ignore null fields. using @Query method. instead you can use the query method specifications. more information [here][jpa documentaiton]

if you want to keep your existing method you can also go like this:

@Query("SELECT u FROM User u WHERE ("
            + "LOWER(u.name) = LOWER(:name) AND "
            + "LOWER(u.beruf) = LOWER(:job) AND "
            + "( " +
            "   :age is null or LOWER(u.alter) = LOWER(:age) " +
                ")" 
    )
    public List<User> findUsers(@Param("name") String name,
                                @Param("job") String job,
                                @Param("age") String age);

Spring data repository query to retrieve values containing null

1 Answers1