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注入漏洞,所以不建议使用!