5

UPD

    @Query(value = "select distinct d.documentId " +
        "from TrainingDocument d " +
        "where (:docTypes is null or d.documentTypeName in :docTypes)")
List<String> findDocByDocTypes(
        @Param("docTypes") List<String> docTypes);

I have a query like above, where I check List<String> docTypes whether it is null or not. With docTypes == null or with only one element in the list it works. As soon as I have more than one element I get:

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: {vector} 
[select distinct d.documentId from com.example.model.TrainingDocument d 
where (:docTypes_0_, :docTypes_1_ is null or d.documentTypeName in
(:docTypes_0_, :docTypes_1_))]; nested exception is
java.lang.IllegalArgumentException:
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: {vector} 
[select distinct d.documentId from com.example.model.TrainingDocument d
where (:docTypes_0_, :docTypes_1_ is null or d.documentTypeName in
(:docTypes_0_, :docTypes_1_))]

I already found this solution How to skip @Param in @Query if is null or empty in Spring Data JPA It describes my case but doesn't work for me. I'm using spring-boot 1.5.9.RELEASE

Elina Zarisheva
  • 51
  • 3
  • The JPQL looks well formed IMHO. So the problem is either Spring not passing that JPQL across to your JPA provider correctly, OR your JPA provider not converting the JPQL into SQL correctly (the `:docTypes_0_, :docTypes_1_ is null` is invalid). Use the JPA providers log to decide which of those, and report a bug on someone –  Jan 26 '18 at 13:56
  • Sorry, I find the various variations you mention extremely confusing. Could you: 1. Simplify the code to remove everything that is not related to the question. Probably a method with one parameter with a select with one in clause + null check. 2. For each variant show the code, including how you call it and what the result is (the executed query + stack trace). – Jens Schauder Jan 26 '18 at 15:33
  • I reduce the example, hopefully it'll help to understand better. It seems if I add this null check and if the list Is bigger than one element it stops to work. – Elina Zarisheva Jan 29 '18 at 10:09

1 Answers1

1

You can yield null if your vector is empty, or yield the first value if is not

where (coalesce(:docTypes, null) is null or d.documentTypeName in :docTypes)
spezzino
  • 664
  • 12
  • 21