java - 如何不选择空字符串

Question

我们有以下 JPQL:

Select distinct sys.ipAddress from SystemLog sys where sys.ipAddress is not null and sys.ipAddress is not empty

这会生成以下 mysql 语句。

select
    distinct systemlog0_.ipAddress as col_0_0_ 
from
    SystemLog systemlog0_ 
where
    (
        systemlog0_.ipAddress is not null
    ) 
    and (
        exists (
            select
                systemlog0_.id 
            from
                SystemLog systemlog0_
        )
    )

这显然不起作用并返回空字符串而不是省略它。但是，我正在寻找生成这样的东西:

select distinct ipAddress from SystemLog where ipAddress is not null and ipAddress <> '';

但是，我不明白为什么我们的 jpa 查询没有生成类似的东西。有任何想法吗？

Answer 1

我认为您在滥用 IS [NOT] EMPTY用于检查集合关联路径 是否解析为空集合或至少具有一个值。来自 JPA 规范:

4.6.11 Empty Collection Comparison Expressions

The syntax for the use of the comparison operator IS EMPTY in an empty_collection_comparison_expression is as follows:

collection_valued_path_expression IS [NOT] EMPTY

This expression tests whether or not the collection designated by the collection-valued path expression is empty (i.e, has no elements).

Example:

SELECT o
FROM Order o
WHERE o.lineItems IS EMPTY

If the value of the collection-valued path expression in an empty collection comparison expression is unknown, the value of the empty comparison expression is unknown.

在我看来，你应该只使用 <>比较运算符:

select distinct sys.ipAddress 
  from SystemLog sys 
 where sys.ipAddress is not null 
   and sys.ipAddress <> ''