Could not set parameters for mapping错误与mybatis源码追踪

错误及解决方法

因为担心@Builder的注解的类不支持mybatis做查询，刚好也有了一个错误，跟了一圈发现不是mybatis的问题，是自己mapper的like写错导致。记录一下跟踪过程，做个总结。

这个错误的原因是mybatis的sql解析参数数量和匹配的参数数量不一致。我这里的原因是把参数写在''里了，导致mapper没有解析到这个参数。

错误的写法'#{userNamePinyin}%'，正确的写法#{userNamePinyin}'%'(错误的写法)。

正确的写法可能是#{userNamePinyin} '%'，#{userNamePinyin}"%"。原因还是mybatis的解析。

错误原因追踪

错误信息是

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='userNamePinyin', mode=IN, javaType=class java.lang.String, jdbcType=VARCHAR, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy41.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
	...
	
Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='userNamePinyin', mode=IN, javaType=class java.lang.String, jdbcType=VARCHAR, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
	at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:89)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:93)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:64)
	...

看英文说的是参数set值失败，参数的index大于sql中匹配的参数的数量。从下面的错误的setParameters开始看起，点击错误栈中第一行org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:89)

try {
  typeHandler.setParameter(ps, i + 1, value, jdbcType);
} catch (TypeException e) {
  throw new TypeException("Could not set parameters for mapping: " + parameterMapping + ". Cause: " + e, e);
} catch (SQLException e) {
  throw new TypeException("Could not set parameters for mapping: " + parameterMapping + ". Cause: " + e, e);
}

异常抛出是在typeHandler.setParameter(ps, i + 1, value, jdbcType);这行，打断点进去，发现是toString方法出错，继续进去，找到了报错源头。我这里paramIndex是1，parameterCount是0

else if (paramIndex > this.parameterCount) {
    throw SQLError.createSQLException(
        Messages.getString("PreparedStatement.51") + paramIndex + Messages.getString("PreparedStatement.52")+ (this.parameterValues.length) + Messages.getString("PreparedStatement.53"),SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor());
}

parameterMappings parse

首先看paramIndex的值的来源，paramIndex值是在遍历SqlSource.parameterMappings过程中的计数器+1，实际就是第几个参数。parameterMappings的值在boundSql属性中，那么就是sql解析的问题了。

  @Override
  public void setParameters(PreparedStatement ps) {
    ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
    List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
    if (parameterMappings != null) {
      for (int i = 0; i < parameterMappings.size(); i++) {
        ParameterMapping parameterMapping = parameterMappings.get(i);
          ...
          try {
            typeHandler.setParameter(ps, i + 1, value, jdbcType);
          } catch (TypeException e) {
            throw new TypeException("Could not set parameters for mapping: " + parameterMapping + ". Cause: " + e, e);
          } catch (SQLException e) {
            throw new TypeException("Could not set parameters for mapping: " + parameterMapping + ". Cause: " + e, e);
          }
        
      }
    }
  }

parameterMappings的赋值是在SqlSourceBuilder.parse方法，因为创建StaticSqlSource对象，猜测是加载mapper.xml文件时执行。在parse，方法中将匹配符"#{", "}"写死。

  public SqlSource parse(String originalSql, Class<?> parameterType, Map<String, Object> additionalParameters) {
    ParameterMappingTokenHandler handler = new ParameterMappingTokenHandler(configuration, parameterType, additionalParameters);
    GenericTokenParser parser = new GenericTokenParser("#{", "}", handler);
    String sql = parser.parse(originalSql);
    return new StaticSqlSource(configuration, sql, handler.getParameterMappings());
  }

parser.parse(originalSql)是赋值parameterMappings的方法。它会解析出sql中存在的#{}，替换成$，并将值放入parameterMappings中。

  public String parse(String text) {
      ...
    //openToken 是 '#{'
    int start = text.indexOf(openToken, 0);
      ...
    char[] src = text.toCharArray();
    int offset = 0;
    final StringBuilder builder = new StringBuilder();
    StringBuilder expression = null;
    while (start > -1) {
      if (start > 0 && src[start - 1] == '\\') {
        builder.append(src, offset, start - offset - 1).append(openToken);
        offset = start + openToken.length();
      } else {
        if (expression == null) {
          expression = new StringBuilder();
        } else {
          expression.setLength(0);
        }
        builder.append(src, offset, start - offset);
        offset = start + openToken.length();
        int end = text.indexOf(closeToken, offset);
        while (end > -1) {
          if (end > offset && src[end - 1] == '\\') {
            // closeToken 是 '}'，这里拿到'#{'，'}‘之间字符
            expression.append(src, offset, end - offset - 1).append(closeToken);
            offset = end + closeToken.length();
            end = text.indexOf(closeToken, offset);
          } else {
            expression.append(src, offset, end - offset);
            offset = end + closeToken.length();
            break;
          }
        }
        if (end == -1) {
          builder.append(src, start, src.length - start);
          offset = src.length;
        } else {
          //处理参数，并转成'?'
          builder.append(handler.handleToken(expression.toString()));
          offset = end + closeToken.length();
        }
      }
      start = text.indexOf(openToken, offset);
    }
    if (offset < src.length) {
      builder.append(src, offset, src.length - offset);
    }
    return builder.toString();
  }

    public String handleToken(String content) {
      parameterMappings.add(buildParameterMapping(content));
      return "?";
    }

set parameterCount value

parameterCount的初始化，赋值在com.mysql.cj.jdbc.PreparedStatement中

    private void initializeFromParseInfo() throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            this.staticSqlStrings = this.parseInfo.staticSql;
            this.isLoadDataQuery = this.parseInfo.foundLoadData;
            this.firstCharOfStmt = this.parseInfo.firstStmtChar;

            this.parameterCount = this.staticSqlStrings.length - 1;

            this.parameterValues = new byte[this.parameterCount][];
            this.parameterStreams = new InputStream[this.parameterCount];
            this.isStream = new boolean[this.parameterCount];
            this.streamLengths = new int[this.parameterCount];
            this.isNull = new boolean[this.parameterCount];
            this.parameterTypes = new MysqlType[this.parameterCount];

            clearParameters();

            for (int j = 0; j < this.parameterCount; j++) {
                this.isStream[j] = false;
            }
        }
    }

parameterCount是parseInfo.staticSql.length-1。继续寻找parseInfo.staticSql的赋值，发现parseInfo是在构造器中赋值的，parseInfo.staticSql的赋值是这句this.staticSql = new byte[endpointList.size()][];。接着看endpointList的赋值，它是在循环整个sql语句中找到?就记录一下开始与现在下标并放入list

for (i = this.statementStartPos; i < this.statementLength; ++i) {
    char c = sql.charAt(i);
    //...
    if ((c == '?') && !inQuotes && !inQuotedId) {
        endpointList.add(new int[] { lastParmEnd, i });
        lastParmEnd = i + 1;

        if (this.isOnDuplicateKeyUpdate && i > this.locationOfOnDuplicateKeyUpdate) {
            this.parametersInDuplicateKeyClause = true;
        }
    }
    //...
}

而我的sql是这样的and USER_NAME_PINYIN like '#{userNamePinyin,jdbcType=VARCHAR}%'，断点跟踪的sql是

select ID, CUSTOMER_CODE,COURT_CODE, USER_NO, USER_NAME, USER_NAME_PINYIN, DEPT_CODE, DEPT_NAME, ROLE_CODE, ROLE_NAME, MOBILE, ID_PHOTO,
WECHAT_OPEN_ID, STATUS, CREATE_TM, CREATE_USER, UPDATE_TM, UPDATE_USER
FROM user
WHERE USER_NAME_PINYIN like ‘?%’

那就只能看看(c == '?') && !inQuotes && !inQuotedId中的inQuotes和inQuotedId是怎么赋值的，就是它们是true导致没有成功解析参数。而它是这么赋值的

char quotedIdentifierChar = 0;
if (!inQuotes && (quotedIdentifierChar != 0) && (c == quotedIdentifierChar)) {
    inQuotedId = !inQuotedId;
} else if (!inQuotedId) {
    // only respect quotes when not in a quoted identifier
    if (inQuotes) {
        if (((c == '\'') || (c == '"')) && c == quoteChar) {
            if (i < (this.statementLength - 1) && sql.charAt(i + 1) == quoteChar) {
                i++;
                continue; // inline quote escape
            }

            inQuotes = !inQuotes; //当前面有'，且目前也是'，inQuotes会反转
            quoteChar = 0;
        //两个判断条件一样，应该是bug
        } else if (((c == '\'') || (c == '"')) && c == quoteChar) {
            inQuotes = !inQuotes;
            quoteChar = 0;
        }
    } else {
        if (c == '#' || (c == '-' && (i + 1) < this.statementLength && sql.charAt(i + 1) == '-')) {
            continue;
        } else if (c == '/' && (i + 1) < this.statementLength) {
            
            }
        } else if ((c == '\'') || (c == '"')) {
            inQuotes = true;
            //quoteChar反转字符发现变化
            quoteChar = c;
        }
    }
}

即如果字符是'或"，就inQuotes = true;quoteChar = c;表示字符在不解析字符串内；当字符是字符是'或"且等于quoteChar，inQuotes = !inQuotes;quoteChar = 0;表示字符串以结束。

实际到这里问题已经表现的很清晰了，在parameterMappings解析的时候，凡是遇到#{}就转成?并把#{}内的值放到parameterMappings中；而paramCount赋值的时候会判断#{}是否在''中，如果在，就不计数。当存在${}在''中时，会导致parameterMappings的数量大于paramCount，在遍历parameterMappings时，这行代码就会报错了。

if (paramIndex > this.parameterCount) {
  throw SQLError.createSQLException(
        Messages.getString("PreparedStatement.51") + paramIndex + Messages.getString("PreparedStatement.52")+ (this.parameterValues.length) + Messages.getString("PreparedStatement.53"),SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor());
}

我代码的问题就是${}在''内，导致解析的paramCount为0。