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

野性酷女 2022-04-23 01:02 221阅读 0赞

#### 错误及解决方法 ####

因为担心`@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。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1bzQxMDU_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x1bzQxMDU_size_16_color_FFFFFF_t_70]: /images/20220323/3bdf9d13b1c8401993823161141d7c55.png