Parameter index out of range (2 > number of parameters, which is 1)

今天在实现一个功能时遇到一个问题，解决了很久。结果是#{}与${}使用错误的原因。但是具体原因还不是很清楚，写此篇总结，知道的可以交流。

具体描述为：通过教师的头衔（1高级讲师2首席讲师）及名称进行模糊查询，报如下错误：

org.springframework.dao.TransientDataAccessResourceException:

### Error querying database.  Cause: java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).

### The error may exist in file [E:\javaEE+android\mooc\workspace\edu-mooc\target\classes\mybatis\mappers\admin\teacher\TeacherMapper.xml]

### The error may involve com.edu.mooc.admin.mapper.TeacherMapper.quearyTeacherListBySolr-Inline

### The error occurred while setting parameters

### SQL: SELECT count(*) FROM EDU_TEACHER WHERE EDU_TEACHER.`STATUS` = 0 AND EDU_TEACHER.IS_STAR = ? AND EDU_TEACHER.`NAME` LIKE '%?%'

### Cause: java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).

; SQL []; Parameter index out of range (2 > number of parameters, which is 1).; nested exception is java.sql.SQLException: Parameter index out of range (2 > number of parameters, which is 1).

 

参看一些网上解释：就是当设置参数时，没有相应的问号与之匹配（或者根本就没有？号）.

分析错误：

Parameter index out of range (2 > number of parameters, which is 1).

翻译为：找到了1个问号，却插入了2个值，导致参数越界（根据得到的信息打印将很容易判断数据是否与数据库字段匹配等小问题）。

 

看看sql代码：

SELECT

      <include refid="edu_teacher_columns"/>

      FROM EDU_TEACHER

      <where>

         EDU_TEACHER.`STATUS` = 0

         <if test="isStar > 0">

            AND EDU_TEACHER.IS_STAR = #{isStar}

         </if>

         <if test="keyWords != null and keyWords != '' ">

            AND EDU_TEACHER.`NAME` LIKE '%#{keyWords}%'

         </if>

         ORDER BY SORT DESC,CREATE_TIME ASC

      </where>

将'%#{keyWords}%'修改为'%${keyWords}%'后结果正确

 

日志信息输出结果为：

==>  Preparing: SELECT count(*) FROM EDU_TEACHER WHERE EDU_TEACHER.`STATUS` = 0 AND EDU_TEACHER.IS_STAR = ? AND EDU_TEACHER.`NAME` LIKE '%李%'

==> Parameters: 1(Integer)

<==    Columns: count(*)

<==        Row: 3

<==      Total: 1

==>  Preparing: SELECT EDU_TEACHER.ID, EDU_TEACHER.NAME, EDU_TEACHER.EDUCATION, EDU_TEACHER.CAREER, EDU_TEACHER.IS_STAR, EDU_TEACHER.PIC_PATH, EDU_TEACHER.STATUS, EDU_TEACHER.CREATE_TIME, EDU_TEACHER.UPDATE_TIME, EDU_TEACHER.SUBJECT_ID, EDU_TEACHER.SORT FROM EDU_TEACHER WHERE EDU_TEACHER.`STATUS` = 0 AND EDU_TEACHER.IS_STAR = ? AND EDU_TEACHER.`NAME` LIKE '%李%' ORDER BY SORT DESC,CREATE_TIME ASC limit ?,?

==> Parameters: 1(Integer), 0(Integer), 10(Integer)

<==    Columns: ID, NAME, EDUCATION, CAREER, IS_STAR, PIC_PATH, STATUS, CREATE_TIME, UPDATE_TIME, SUBJECT_ID, SORT