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

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

CodePudding user response：

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);

CodePudding user response：

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);