Page<Record> queryAll(String memberId, String memberName);

<select id="queryAll" resultMap="recordResultMap">
    SELECT *
    FROM record
    WHERE member_id LIKE CONCAT('%', #{memberId}, '%')
        AND member_name LIKE CONCAT('%', #{memberName}, '%')
    ORDER BY ID DESC
</select>

• 在 Spring Boot 项目中，使用 MyBatis PostgreSQL，执行上述代码，传入的参数为 null 时，出现如下错误信息

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: org.postgresql.util.PSQLException: 错误: 无法确定参数 $1 的数据类型
### The error may exist in file ...
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT count(0) FROM record WHERE member_id LIKE CONCAT('%', ?, '%') AND member_name LIKE CONCAT('%', ?, '%')
### Cause: org.postgresql.util.PSQLException: 错误: 无法确定参数 $1 的数据类型
; bad SQL grammar []
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)

	...

Caused by: org.postgresql.util.PSQLException: 错误: 无法确定参数 $1 的数据类型
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)

    ...

问题原因

• 在 PostgreSQL 中，传入的参数为 null，CONCAT 函数无法推断参数的数据类型

处理策略

• 使用条件判断动态 SQL

<select id="queryAll" resultMap="recordResultMap">
    SELECT *
    FROM record
    WHERE 1=1
    <if test="memberId != null and memberId != ''">
        AND member_id LIKE CONCAT('%', #{memberId}, '%')
    </if>
    <if test="memberName != null and memberName != ''">
        AND member_name LIKE CONCAT('%', #{memberName}, '%')
    </if>
    ORDER BY ID DESC
</select>