JPA SPEL可选列表参数

Question

我有一个具有多个连接和可选参数的JPA查询，如果在状态列表参数中没有发送多个值，则查询工作正常，但如果我发送多个值，其给出的异常为

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

下面是查询，你能帮帮忙吗

@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

应该这样做:

@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}) " 

请注意，在原始查询中，您指定的requestdto.getStatus应该只是requestdto.status。

引用:

• 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