r2dbc + postgresql，在@Query里面，如果参数值为null，怎样动态组装sql语句(语言-java)

Question

问题遇到的现象和发生背景

环境：r2dbc + postgresql
这样的一个简单的需求，根据type查询数据，如果type参数值=null或者type= “”，就查出所有的数据
以前用mybatis是这样写的：

 <if test="type != null">
     and a.type = #{type}
 </if>

这样，如果type=null，出来的sql就是 select * from a;否则就是 select * from a where a.type=5；

问题相关代码，请勿粘贴截图

现在看很多文章之后，写成这样：

    @Query("select a.* from sys a where if(?1 !='',a.type=?1,1=1),nativeQuery = true")
    public Mono<SysEntity> getSysByType(String type);

运行结果及报错内容

运行之后，报错如下：
2022-03-14 17:46:33.324 DEBUG 4284 --- [actor-tcp-nio-2] a.w.r.e.AbstractErrorWebExceptionHandler : [176dafae-4, L:/0:0:0:0:0:0:0:1:8700 - R:/0:0:0:0:0:0:0:1:6049] Resolved [UnsupportedOperationException: Binding parameters is not supported for the statement 'select a.* from sys a where if(?1 !='',a.type=?1,1=1),nativeQuery = true'] for HTTP GET /sys/byType
2022-03-14 17:46:33.326 ERROR 4284 --- [actor-tcp-nio-2] a.w.r.e.AbstractErrorWebExceptionHandler : [176dafae-4, L:/0:0:0:0:0:0:0:1:8700 - R:/0:0:0:0:0:0:0:1:6049] 500 Server Error for HTTP GET "/sys/byType"

java.lang.UnsupportedOperationException: Binding parameters is not supported for the statement 'select a.* from sys a where if(?1 !='',a.type=?1,1=1),nativeQuery = true'
at io.r2dbc.postgresql.SimpleQueryPostgresqlStatement.bind(SimpleQueryPostgresqlStatement.java:69) ~[r2dbc-postgresql-0.8.1.RELEASE.jar:0.8.1.RELEASE]
Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException:
Error has been observed at the following site(s):
*__checkpoint ? Handler com.jundax.fluxwk.sys.controller.SysTaskController#getSysTaskByType(String) [DispatcherHandler]
*__checkpoint ? HTTP GET "/sys/byType" [ExceptionHandlingWebHandler]
Stack trace:
at io.r2dbc.postgresql.SimpleQueryPostgresqlStatement.bind(SimpleQueryPostgresqlStatement.java:69) ~[r2dbc-postgresql-0.8.1.RELEASE.jar:0.8.1.RELEASE]
at io.r2dbc.postgresql.SimpleQueryPostgresqlStatement.bind(SimpleQueryPostgresqlStatement.java:39) ~[r2dbc-postgresql-0.8.1.RELEASE.jar:0.8.1.RELEASE]
at org.springframework.r2dbc.core.DefaultDatabaseClient$DefaultGenericExecuteSpec.lambda$bindByIndex$6(DefaultDatabaseClient.java:442) ~[spring-r2dbc-5.3.12.jar:5.3.12]

我的解答思路和尝试过的方法

我尝试改成

@Query(value = "select a.* from sys a where a.type=(:type or :type iss null ", nativeQuery = true)

依然是报错

我想要达到的结果

请教，这里应该怎么写？
谢谢

Answer 1

不同数据库及不同版本的函数可能会存在一点差异，常见的一般就是用nvl 或者coalesce函数来处理，比如

where coalesce(传入的查询参数,表里的字段)=表里的字段

由于null和空字符串在不同数据库里存在不一样的情况，所以下面这种方式更为通用

where (case when ?='' or ? is null then a.type else ? end) =a.type

---

这个查询条件，我直接在数据库里测试是没有问题的,传null或者传非null都可以正常输出结果