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 可能只是 request> requestdto.status 。

参考：

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