JPA SPEL optional List parameter

Question

I have a JPA query with multiple joins and optional parameters , the query works fine if there is no multiple values sent in the status list parameter but if I send multiple values its giving exception as

"Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: An expression of non-boolean type specified in a context where a condition is expected, near ','.

Below is the query, could you please help

@Query(value = select distinct a.invn, a.accid, ac.accountCode, b.Fbusinesscode" +
            ", b.scode, i.invtype, d.dtid,  d.did " +
            " from INV.BINv a " +
            "join READONLY.Fbusiness b on a.FbusinessId =b.FbusinessId " +
            "join READONLY.Acc ac on a.accid=ac.accid " +
            "join INV.Doc c on a.invid = c.invid " +
            "join INV.Dstreq d on c.docid = d.docid " +
            "join INV.dassoc e " +
            "on d.dassocId = e.dassocId " +
            "join BLNG.InvMType AS i on e.invtypeId = i.invtypeId "+
            "where (?#{#requestdto.invoiceNumber } is null OR a.InvoiceEntityNumber = ?#{#requestdto.invoiceNumber}) and " +
            " (?#{#requestdto.mod} is null OR i.InvTypeName = ?#{#requestdto.mod}) and " +
            " (?#{#requestdto.getStatus} is null OR e.distrstttypecode in ?#{#requestdto.getStatus}) " 

Answer 1

Something like this should do it:

@Query("select distinct a.invn, a.accid, ac.accountCode, b.Fbusinesscode" +
            ", b.scode, i.invtype, d.dtid,  d.did " +
            " from INV.BINv a " +
            "join READONLY.Fbusiness b on a.FbusinessId =b.FbusinessId " +
            "join READONLY.Acc ac on a.accid=ac.accid " +
            "join INV.Doc c on a.invid = c.invid " +
            "join INV.Dstreq d on c.docid = d.docid " +
            "join INV.dassoc e " +
            "on d.dassocId = e.dassocId " +
            "join BLNG.InvMType AS i on e.invtypeId = i.invtypeId "+
            "where (?#{#requestdto.invoiceNumber } is null OR a.InvoiceEntityNumber = ?#{#requestdto.invoiceNumber}) and " +
            " (?#{#requestdto.mod} is null OR i.InvTypeName = ?#{#requestdto.mod}) and " +
            " (?#{#requestdto.status?.size() ?: 0} = 0 OR e.distrstttypecode in ?#{#requestdto.status}) " 

Note that in original query you specified requestdto.getStatus which should likely be just requestdto.status.

References:

• https://docs.spring.io/spring-framework/docs/current/reference/html/core.html#expressions-operator-elvis
• https://docs.spring.io/spring-framework/docs/current/reference/html/core.html#expressions-operator-safe-navigation