如何在HQL中传入'null'字符串后获取smsStatus为空值的记录？

直接修改smsStatus对应的WHERE条件即可实现需求，以下是调整后的完整查询代码：

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

逻辑说明 #

• 当request.smsStatus为空字符串时，条件会匹配cn.smsStatus IS NULL的记录
• 当request.smsStatus不为空时，依然保持原逻辑，匹配cn.smsStatus等于该参数的记录

如果担心逻辑优先级问题，可以给条件加上括号明确分组，避免运算符优先级导致的查询错误：

AND ( (:#{#request.smsStatus} = '' AND cn.smsStatus IS NULL) OR (:#{#request.smsStatus} != '' AND cn.smsStatus = :#{#request.smsStatus}) )

内容的提问来源于stack exchange，提问作者SRG