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

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

错误及解决方法

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

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

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

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

错误原因追踪

错误信息是

  1. 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).
  3.     at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
  4.     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
  5.     at com.sun.proxy.$Proxy41.selectList(Unknown Source)
  6.     at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:230)
  7.     ...
  9. 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).
  10.     at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:89)
  11.     at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:93)
  12.     at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:64)
  13.     ...

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

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

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

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

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

  1. @Override
  2.   public void setParameters(PreparedStatement ps) {
  3.     ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
  4.     List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
  5.     if (parameterMappings != null) {
  6.       for (int i = 0; i < parameterMappings.size(); i++) {
  7.         ParameterMapping parameterMapping = parameterMappings.get(i);
  8.           ...
  9.           try {
  10.             typeHandler.setParameter(ps, i + 1, value, jdbcType);
  11.           } catch (TypeException e) {
  12.             throw new TypeException("Could not set parameters for mapping: " + parameterMapping + ". Cause: " + e, e);
  13.           } catch (SQLException e) {
  14.             throw new TypeException("Could not set parameters for mapping: " + parameterMapping + ". Cause: " + e, e);
  15.           }
  17.       }
  18.     }
  19.   }

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

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

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

  1. public String parse(String text) {
  2.       ...
  3.     //openToken 是 '#{'
  4.     int start = text.indexOf(openToken, 0);
  5.       ...
  6.     char[] src = text.toCharArray();
  7.     int offset = 0;
  8.     final StringBuilder builder = new StringBuilder();
  9.     StringBuilder expression = null;
  10.     while (start > -1) {
  11.       if (start > 0 && src[start - 1] == '\\') {
  12.         builder.append(src, offset, start - offset - 1).append(openToken);
  13.         offset = start + openToken.length();
  14.       } else {
  15.         if (expression == null) {
  16.           expression = new StringBuilder();
  17.         } else {
  18.           expression.setLength(0);
  19.         }
  20.         builder.append(src, offset, start - offset);
  21.         offset = start + openToken.length();
  22.         int end = text.indexOf(closeToken, offset);
  23.         while (end > -1) {
  24.           if (end > offset && src[end - 1] == '\\') {
  25.             // closeToken 是 '}'，这里拿到'#{'，'}‘之间字符
  26.             expression.append(src, offset, end - offset - 1).append(closeToken);
  27.             offset = end + closeToken.length();
  28.             end = text.indexOf(closeToken, offset);
  29.           } else {
  30.             expression.append(src, offset, end - offset);
  31.             offset = end + closeToken.length();
  32.             break;
  33.           }
  34.         }
  35.         if (end == -1) {
  36.           builder.append(src, start, src.length - start);
  37.           offset = src.length;
  38.         } else {
  39.           //处理参数，并转成'?'
  40.           builder.append(handler.handleToken(expression.toString()));
  41.           offset = end + closeToken.length();
  42.         }
  43.       }
  44.       start = text.indexOf(openToken, offset);
  45.     }
  46.     if (offset < src.length) {
  47.       builder.append(src, offset, src.length - offset);
  48.     }
  49.     return builder.toString();
  50.   }
  52. public String handleToken(String content) {
  53.       parameterMappings.add(buildParameterMapping(content));
  54.       return "?";
  55.     }
set parameterCount value

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

  1. private void initializeFromParseInfo() throws SQLException {
  2.         synchronized (checkClosed().getConnectionMutex()) {
  3.             this.staticSqlStrings = this.parseInfo.staticSql;
  4.             this.isLoadDataQuery = this.parseInfo.foundLoadData;
  5.             this.firstCharOfStmt = this.parseInfo.firstStmtChar;
  7.             this.parameterCount = this.staticSqlStrings.length - 1;
  9.             this.parameterValues = new byte[this.parameterCount][];
  10.             this.parameterStreams = new InputStream[this.parameterCount];
  11.             this.isStream = new boolean[this.parameterCount];
  12.             this.streamLengths = new int[this.parameterCount];
  13.             this.isNull = new boolean[this.parameterCount];
  14.             this.parameterTypes = new MysqlType[this.parameterCount];
  16.             clearParameters();
  18.             for (int j = 0; j < this.parameterCount; j++) {
  19.                 this.isStream[j] = false;
  20.             }
  21.         }
  22.     }

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

  1. for (i = this.statementStartPos; i < this.statementLength; ++i) {
  2.     char c = sql.charAt(i);
  3.     //...
  4.     if ((c == '?') && !inQuotes && !inQuotedId) {
  5.         endpointList.add(new int[] { lastParmEnd, i });
  6.         lastParmEnd = i + 1;
  8.         if (this.isOnDuplicateKeyUpdate && i > this.locationOfOnDuplicateKeyUpdate) {
  9.             this.parametersInDuplicateKeyClause = true;
  10.         }
  11.     }
  12.     //...
  13. }

而我的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导致没有成功解析参数。而它是这么赋值的

  1. char quotedIdentifierChar = 0;
  2. if (!inQuotes && (quotedIdentifierChar != 0) && (c == quotedIdentifierChar)) {
  3.     inQuotedId = !inQuotedId;
  4. } else if (!inQuotedId) {
  5.     // only respect quotes when not in a quoted identifier
  6.     if (inQuotes) {
  7.         if (((c == '\'') || (c == '"')) && c == quoteChar) {
  8.             if (i < (this.statementLength - 1) && sql.charAt(i + 1) == quoteChar) {
  9.                 i++;
  10.                 continue; // inline quote escape
  11.             }
  13.             inQuotes = !inQuotes; //当前面有'，且目前也是'，inQuotes会反转
  14.             quoteChar = 0;
  15.         //两个判断条件一样，应该是bug
  16.         } else if (((c == '\'') || (c == '"')) && c == quoteChar) {
  17.             inQuotes = !inQuotes;
  18.             quoteChar = 0;
  19.         }
  20.     } else {
  21.         if (c == '#' || (c == '-' && (i + 1) < this.statementLength && sql.charAt(i + 1) == '-')) {
  22.             continue;
  23.         } else if (c == '/' && (i + 1) < this.statementLength) {
  25.             }
  26.         } else if ((c == '\'') || (c == '"')) {
  27.             inQuotes = true;
  28.             //quoteChar反转字符发现变化
  29.             quoteChar = c;
  30.         }
  31.     }
  32. }

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

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

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

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

在这里插入图片描述

发表评论

表情：
评论列表 （有 0 条评论，345人围观）

还没有评论，来说两句吧...

相关阅读