Spring-boot JPA, query with LIKE and NULL fields

Question

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.

Turo · Accepted Answer · 2019-01-03 11:18:36Z

3

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

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

etc.

answered Jan 3, 2019 at 11:18

Turo

5,0442 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Raffaeu Over a year ago

There you go, that's exactly what I was looking for. The problem is on SQL Server and not on the JPA query

Kiran Joshi Over a year ago

JPA does not convert into NULL in case of ORACLE. So this will not work if you are using Oracle as DB.

Turo Over a year ago

@KiranJoshi That's one thing I find very annoying from Oracle, luckily the question is tagged with sql-server :-)

Collectives™ on Stack Overflow

Spring-boot JPA, query with LIKE and NULL fields

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related