Mybatis异常解决之：java.sql.SQLException: Parameter index out of range

mybatis使用过程中遇到下面一个异常：

org.springframework.dao.TransientDataAccessResourceException:
### Error querying database.  Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
### The error may exist in conf/entitymap/diseaseMapper.xml
### The error may involve diseaseMapper.queryCountByName-Inline
### The error occurred while setting parameters
### SQL: select         count(1)         from tb_disease where 1=1                       AND name like CONCAT('%','?','%')
### Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
; SQL []; Parameter index out of range (1 > number of parameters, which is 0).; nested exception is java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
  at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:106) ~[spring-jdbc-3.2.9.RELEASE.jar:3.2.9.RELEASE]
  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-3.2.9.RELEASE.jar:3.2.9.RELEASE]
  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-jdbc-3.2.9.RELEASE.jar:3.2.9.RELEASE]
  at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) ~[spring-jdbc-3.2.9.RELEASE.jar:3.2.9.RELEASE]
  at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73) ~[mybatis-spring-1.2.2.jar:1.2.2]
  at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371) ~[mybatis-spring-1.2.2.jar:1.2.2]
  at com.sun.proxy.$Proxy70.selectOne(Unknown Source) ~[na:na]
  at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:163) ~[mybatis-spring-1.2.2.jar:1.2.2]

根据错误信息 

select count(1) from tb_disease where 1=1 AND name like CONCAT('%','?','%')

找到相应配置如下，发现原来是#{diseaseName}用法有误：CONCAT('%','#{diseaseName}','%')

mapper文件配置如下：

select id="queryCountByName" resultType="java.lang.Long" parameterType="java.util.HashMap">
        select
        count(1)
        from tb_disease where 1=1
        <if test="diseaseName!=null and diseaseName!=''">
            AND name like CONCAT('%','#{diseaseName}','%')
        </if>
    </select>

在mapper文件中使用#占位符传参时，一定不要在两侧添加引号，否则会导致参数解析异常。

正确的配置方式应该是以下这样：

<select id="queryCountByName" resultType="java.lang.Long" parameterType="java.util.HashMap">
        select
        count(1)
        from tb_disease where 1=1
        <if test="diseaseName!=null and diseaseName!=''">
            AND name like CONCAT('%',#{diseaseName},'%')
        </if>
    </select>

另外，或者可以把#改成$，但是mapper文件中使用$会有sql注入漏洞，所以不建议使用！

附：Mybatis模糊查询MySQL中记录的的常用三种方法