Hibernate JPA: 'IS EMPTY' condition doesn't work for collections passed as parameters

Question

I'm trying to write a query in which I need to check whether the collection passed as a parameter empty or not.

I'm writing something like this:

@Query("SELECT u FROM User u" +
        "WHERE" +
        "   :ids IS EMPTY" +
        "   OR u.id IN :ids")
Collection<User> findUsers(@Param("ids") Collection<String> ids);

This code fails on startup with QuerySyntaxException: ??? is not mapped. The stack-trace doesn't give any valuable information, but I found out that if I remove the next part:

:ids IS EMPTY

it starts working.

Q: How to use IS EMPTY for query parameters? Or how to check that collection is empty in any other way?

Note:

I'm using Spring Boot 1.5.8.RELEASE.
I also looked on "Passing empty list as parameter to JPA query throws error" which is different and didn't help.
The code I posted is primitive, it was added just for illustration purpose, my real query is quite complex and I really need to check collection emptiness.

You can use `find..In` like this `Collection findByIdIn(@Param("ids") Collection ids);` it works fine for an empty parameter — Youcef LAIDANI, Jan 16 '18 at 14:27
If that not help you can check the answer of @davidxxx here https://stackoverflow.com/a/48073370/5558072 — Youcef LAIDANI, Jan 16 '18 at 14:29
If I remember well `IS EMPTY` is meant to be used with fields references in your HQL query and not with parameters e.g. `u.someList IS EMPTY` — Szymon Stepniak, Jan 16 '18 at 14:32
@YCF_L see this is just an example and I pretty complex query where I really need to check for emptiness. — Sasha Shpota, Jan 16 '18 at 14:33
@OleksandrShpota You can use Spring Data DSL method that wont throw any exception if empty collection was passed as a parameter. Please check my answer below. — Szymon Stepniak, Jan 16 '18 at 14:38
@SzymonStepniak no, I can't. My real query is much more complex. — Sasha Shpota, Jan 16 '18 at 14:41
If the list is empty - do you expect ALL or NOTHING as a result? — dognose, Jan 16 '18 at 14:43
So its basically a filter query, where "unset" selection means "show ANY". — dognose, Jan 16 '18 at 14:44
Correction : you mean "does not work with Hibernate JPA", since it works with the JPA provider I've tried (DataNucleus JPA). You should report a bug on your provider — , Jan 16 '18 at 15:22

score 2 · Accepted Answer · answered Jan 16 '18 at 15:12

The JPA documentation clearly states, that you should avoid empty collections as parameters.

4.6.8 In Expressions from the JPA 1.0 specification

There must be at least one element in the comma separated list that defines the set of values for the IN expression.

If you think about it, JPA can't do much about it. if the query contains WHERE id in ?0 - how to handle an empty collection? WHERE id in () is invalid syntax, as well as WHERE id in... So it would neet to remove the whole where-constraint, which is not what is desired in most situations.

Guess you need to handle this in your Application rather than on a query level.

Side-Node: If you are working with filter values with only little options, you could get the result by adding ALL ids, if the user does not select something, i.e.

where color_id in (1,2,..,10) and type_id in (1,2,3) AND tile_id in (1,2,..,5)
instead of the invalid
where color_id in () and type_id in () AND tile_id in ()

Just not working, if you have thousands of options, or you'll exceed the query length.

score 1 · Answer 2 · answered Jan 16 '18 at 14:34

1

In your query, you ask the database to check on the value of :ids, which is not a column in the database, but a Collection in your Java program.

So naturally the persistence framework has no idea what you are talking about and tells you "... is not mapped", which is true.

I think you probably wanted the following query:

@Query("SELECT u FROM User u" +
    "WHERE" +
    "   u.id IS NULL" +
    "   OR u.id IN :ids")

If that is not the case, please describe what you want to achieve with the query.

answered Jan 16 '18 at 14:34

Kirinya

245
3
12

No,I need different. The condition `u.id IN :ids` should only apply when `ids` is not empty (if `ids` is empty is treated as true - just like I posted in the question) – Sasha Shpota Jan 16 '18 at 14:37
If `:ids` is true ? What do you mean by that? Your comment does not compile :P – Repoker Jan 16 '18 at 14:50
@Repoker If `ids` is empty I need to return all rows ignoring `u.id IN :ids`. – Sasha Shpota Jan 16 '18 at 15:00
that does not make sense from a persistence perspective, look for @dognose answer for an explanation – Repoker Jan 16 '18 at 15:23
1

@OleksandrShpota You can not reference anything in an SQL query that is not part of the database layer. You could write another method findUsers() with no parameter that does return all users and call that one from your outer context when the collection is empty. – Kirinya Jan 16 '18 at 15:59

Hibernate JPA: 'IS EMPTY' condition doesn't work for collections passed as parameters

2 Answers2