How to NOT select empty string

Question

We have the following JPQL:

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

And this generates the following mysql statement.

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

This obviously doesn't work and returns empty string instead of omitting it. However, I am looking for something like this to be generated:

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

However, I can't figure out why our jpa query doesn't generate something simliar like that. Any ideas?

Pascal Thivent · Accepted Answer · 2010-08-05 17:50:46Z

18

I think that you are misusing IS [NOT] EMPTY that is used to check whether a collection association path resolves to an empty collection or has at least one value. From the JPA specification:

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.

In my opinion, you should just use the <> comparison operator:

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

answered Aug 5, 2010 at 17:50

Pascal Thivent

572k140 gold badges1.1k silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alkanshel Over a year ago

"IS NOT EMPTY" doesn't seem to be working to detect blank strings. I'm using hibernate and JPA. Is the <> operator ok for jpql?

Dario Seidl Over a year ago

@Amalgovinus Yes, <> (and !=) work to check whether a string is empty in JPQL. See here docs.jboss.org/hibernate/orm/current/userguide/html_single/…

Collectives™ on Stack Overflow

How to NOT select empty string

1 Answer 1

4.6.11 Empty Collection Comparison Expressions

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4.6.11 Empty Collection Comparison Expressions

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related