CODEWITHSUNDEEP
javasql-serverspringspring-boot
Raffaeu
Raffaeu

Reputation: 6983

Spring-boot JPA, query with LIKE and NULL fields

I am using Spring-Boot 1.5.10 with JPA connected to SQL Server 2016. I do have a custom query which is executing a LIKE but when the fields is NULL no records are returned:

@Query("SELECT s FROM Speaker s where " +
        "(" +
        "lower(s.lastName) like lower(concat('%', ?1,'%')) " +
        "OR lower(s.firstName) like lower(concat('%', ?1,'%')) " +
        "OR lower(s.biography) like lower(concat('%', ?1,'%'))" +
        ") " +
        "and lower(s.language) like lower(concat('%', ?2,'%')) " +
        "and lower(s.contactType) like lower(concat('%', ?3,'%')) " +
        "and s.fee <= ?4")
Page<Speaker> findByContains(String criteria, String language, String type, int fee, Pageable pageable);

The Query is connected to a REST controller where 1 parameter is passed called criteria, the problem is that you might call the REST controller without passing any criteria at all. In that scenario the query doesn't return records where one of the search criteria has NULL in the Database.

Upvotes: 1

Views: 1502

Answers (1)

Turo
Turo

Reputation: 4924

You could use COALESCE to transform null into the emtpy string:

lower(COALESCE(s.firstName,''))

etc.

Upvotes: 3

Related Questions