hibernate 如何在HQl中传递'null'字符串后获取空记录

nfzehxib 于 2022-11-30 发布在其他

关注(0)|答案(2)|浏览(191)

我正在尝试编写一个查询。
下面是到目前为止的查询。

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

如果smsStatus为“”，则根据当前查询为否。如果传递的关键字正确，则将获取所有记录和数据。
现在我想要所有的记录中的smsStatus是空的，不知道我怎么做。我知道我必须使用的情况下是空的，但不知何故，我尝试了什么是不工作。
谁能帮帮忙

Hibernate

来源：https://stackoverflow.com/questions/74600376/how-to-get-null-records-after-passing-null-string-in-hql

2条答案

按热度按时间

k5hmc34c1#

您可以使用IS NULL检查对参数未设置值的逻辑分支执行no-op操作。

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

赞(0）回复(0）举报 2022-11-30

euoag5mw2#

您可以添加COALESCE(cn.smsStatus,'NULL') = :#{#request.smsStatus}
如果request.smsstatus为“NULL”（字符串），则在cn.smsstatus为NULL时条件将变为真，因为COALESCE将采用第一个NOT NULL值。

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

赞(0）回复(0）举报 2022-11-30

我来回答

hibernate 如何在HQl中传递'null'字符串后获取空记录

2条答案

相关问题

热门标签

最新问答