Could not set parameters for mapping错误与mybatis源码追踪
Could not set parameters for mapping错误与mybatis源码追踪
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/luo4105/article/details/86599896
错误及解决方法
因为担心@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。
