Does JPQL support the use of a boolean result in another expression?-CodePudding

I am trying to write a query to back a search API. Specifically for a flag searchDrafts - if the flag is true I have to get rows with status DRAFT and else I have to get all rows that have any status other than DRAFT.

In regular SQL, the below query works fine:

SELECT id, status
FROM records
where ((status = 'DRAFT') = :searchDrafts);

However, a similar thing in JPQL doesn't work:

SELECT r 
FROM Records r 
WHERE ((r.status = 'DRAFT') = :searchDrafts);

It gives the error:

unexpected AST node: = near line 1, column nn

Is there any way in JPQL to use the value of a boolean result in another expression?

The alternative would be doing it the longer way, but its a bit verbose. This works fine in JPQL:

SELECT id, status
FROM records
where (:searchDrafts=true AND (status = 'DRAFT')) 
or (:searchDrafts=false AND (status != 'DRAFT'));

CodePudding user response：

JPQL grammar from Jakarta Persistence 3.0 specification doesn't allow that. (I had 3.0 by hand, but I don't expect that earlier ones differ.) Boolean comparison corresponds to this production rule:

comparison_expression ::=
    ...
    boolean_expression {= | <>} {boolean_expression | all_or_any_expression} |
    ...

Then comparison_expression is only ever encountered as a part of conditional_expression production rules:

conditional_expression ::= conditional_term | conditional_expression OR conditional_term
conditional_term ::= conditional_factor | conditional_term AND conditional_factor
conditional_factor ::= [NOT] conditional_primary
conditional_primary ::= simple_cond_expression | (conditional_expression)
simple_cond_expression ::=
    comparison_expression |
    ...

And conditional_expression is what goes directly in WHEN, WHERE, HAVING and ON clauses. This means that comparison_expression cannot be a boolean_expression.

Apart from your solution with AND, this one seems to work too:

SELECT r 
FROM Records r 
WHERE CASE WHEN r.status = 'DRAFT' THEN TRUE ELSE FALSE END = :searchDrafts;

It's quite ugly, but it does convert a conditional_expression into a boolean_expression.

In practice you'd better have two different queries: one for "=" and the other for "<>".

Overall, it feels like an omission in the JPA standard.