CODEWITHSUNDEEP
mysqlsqlhibernatehql
SRG
SRG

Reputation: 75

How to get null records after passing 'null' string in HQl

I Am trying to write one query.

Below is the query ritten till now.

@Query(value = "select cn from CapNumber cn " +
"WHERE (:#{#request.number} = '' OR cn.number LIKE CONCAT('%',:#{#request.number},'%')) " +
"AND (:#{#request.name} IS '' OR cn.name LIKE CONCAT('%',:#{#request.name},'%') )" +
"AND (:#{#request.smsStatus} IS '' OR cn.smsStatus = :#{#request.smsStatus} )" +
"AND (:#{#request.internalId} IS '' OR cn.internalId LIKE CONCAT('%',:#{#request.internalId},'%') )")
Page<CapNumber> search(NumbersRequest request, Pageable pageable);

No as per current query if smsStatus is '' I am getting all the records and data if I am passing correct keyword.

Now I want all the records in which smsStatus is null, Not able to figure out How can i do that. I know I have to use IS NULL with CASE but somehow what I tried is not working.

Can someone please help

Upvotes: 0

Views: 35

Answers (2)

nbk
nbk

Reputation: 49409

You could add COALESCE(cn.smsStatus,'NULL') = :#{#request.smsStatus}

if request.smsstatus is 'NULL' (String) the condition would become true if cn.smsstatus is NULL, because COALESCE will take the first NOT NULL value.

@Query(value = "select cn from CapNumber cn " +
"WHERE (:#{#request.number} = '' OR cn.number LIKE CONCAT('%',:#{#request.number},'%')) " +
"AND (:#{#request.name} IS '' OR cn.name LIKE CONCAT('%',:#{#request.name},'%') )" +
"AND (:#{#request.smsStatus} IS '' OR COALESCE(cn.smsStatus,'NULL') = :#{#request.smsStatus}  OR cn.smsStatus = :#{#request.smsStatus} )" +
"AND (:#{#request.internalId} IS '' OR cn.internalId LIKE CONCAT('%',:#{#request.internalId},'%') )")
Page<CapNumber> search(NumbersRequest request, Pageable pageable);

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

You may use IS NULL checks to no-op the logical branches having a parameter with no value set.

@Query(value = "select cn from CapNumber cn " +
    "WHERE (:#{#request.number} IS NULL OR cn.number LIKE CONCAT('%',:#{#request.number},'%')) " +
    "AND (:#{#request.name} IS NULL OR cn.name LIKE CONCAT('%',:#{#request.name},'%') )" +
    "AND (:#{#request.smsStatus} IS NULL OR cn.smsStatus = :#{#request.smsStatus} )" +
    "AND (:#{#request.internalId} IS NULL OR cn.internalId LIKE CONCAT('%',:#{#request.internalId},'%') )")
Page<CapNumber> search(NumbersRequest request, Pageable pageable);

Upvotes: 0

Related Questions