7

I have a JPQL named query that takes a List as a parameter. I'm using the parameter in an IN phrase:

...WHERE x.id IN :list

I'd like to do something like the following but the syntax apparently won't let me:

...WHERE :list IS EMPTY OR x.id IN :list

Neither will:

...WHERE SIZE(:list) = 0 OR x.id IN :list

Is what I'm trying to do impossible in a JPA 2.0 named query? I know how to do it via the criteria API or using plain old JPQL strings.

Laird Nelson
  • 15,321
  • 19
  • 73
  • 127

1 Answers1

-4

What you are trying to do in the query seems a non sense. You are trying to restrict the query results using the state of a parameter. But you don't compare this parameter with anything in your select.

Think about this query (it make no sense) :

SELECT * FROM SOMETABLE WHERE :list IS EMPTY

So I think you need to check if the :list is empty before choosing between two different queries:

if :list is not empty: ...WHERE x.id IN :list

if :list is empty: (NO WHERE CLAUSE AT ALL)

ben75
  • 29,217
  • 10
  • 88
  • 134
  • You're quite right of course. I was hoping to move that conditional in code into the query itself, but this is not supported by the JPQL BNF. – Laird Nelson Feb 27 '13 at 17:18
  • 4
    I will offer in my feeble defense that the following kind of SQL (not JPQL) is often seen in generated scenarios: `SELECT x FROM y WHERE 1 = 1;` (i.e. whatever is assembling the `WHERE` clause sometimes makes sure that `1 = 1` is in the `WHERE` clause so dynamic assembly does not have to do the sort of if/then logic you're talking about). Having said that, I don't think I can get away with this in JPQL. Thanks. – Laird Nelson Feb 27 '13 at 17:19