How to add optional parameter in JPQL?

Question

How to use/add optional parameter in JPQL?

@Query(value = "SELECT stud FROM Student stud where stud.name = :studentName AND stud.age IN :studentAgeList")
List<Student> getStudents(
            @Param("studentName ") String studentName, 
            @Param("studentAgeList") List<Integer> studentAgeList
         )

How to make studentAgeList parameter in above query ?

I tried below :

@Query(value = "SELECT stud FROM Student stud where stud.name = :studentName AND (:studentAgeList IS NULL OR stud.age IN :studentAgeList))
List<Student> getStudents(
            @Param("studentName ") String studentName, 
            @Param("studentAgeList") List<Integer> studentAgeList
         )

But getting error : unexpected AST node:

Tried above but getting error

I've tried it and the only problem I get is when I set an empty list in `studentAgeList` parameter. If I set NULL or a non-empty list, it works correctly. Could you show your stack trace ? — Pierre Demeestere, Nov 16 '22 at 07:59
org.springframework.dao.InvalidDataAccessResourceUsageException: unexpected AST node: {vector} — Ajit, Nov 16 '22 at 09:29
This exception occurs when the program starts during JPA initialization or when you call the method `getStudents()` ? — Pierre Demeestere, Nov 16 '22 at 10:11
check https://stackoverflow.com/questions/69610579/unexpected-ast-node-vector-in-spring-data-jpa. I think you will find a solution using `coalesce(:studentAgeList) is null` — Pierre Demeestere, Nov 16 '22 at 10:46

score 0 · Answer 1 · answered Nov 16 '22 at 13:11

0

JPQL does not support optional parameters, you can use overload methods with different queries or criteria API or JPA specifications.

may be get answer here: Set optional parameters in JPQL Query

answered Nov 16 '22 at 13:11

I.Yuldoshev

108
1
5

Indeed, there could be 2 query methods defined (with or without the student list) but the question was more about the possibility to build a where clause testing the parameter it self. – Pierre Demeestere Nov 16 '22 at 13:58

How to add optional parameter in JPQL?

1 Answers1