从源码角度浅析 Mybatis 动态SQL及$和#的区别

参考文档： https://mybatis.org/mybatis-3/zh/dynamic-sql.html
Mybatis目前支持的动态标签有：

if
choose (when, otherwise)
trim (where, set)
foreach

用法示例：

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

@Update({"<script>",
   "update Author",
   "  <set>",
   "    <if test='username != null'>username=#{username},</if>",
   "    <if test='password != null'>password=#{password},</if>",
   "    <if test='email != null'>email=#{email},</if>",
   "    <if test='bio != null'>bio=#{bio}</if>",
   "  </set>",
   "where id=#{id}",
   "</script>"})
void updateAuthorValues(Author author);

标签对应的的SqlNode及关系如下：

与SQL语句相关的两个组件如下：

public interface SqlSource {
  BoundSql getBoundSql(Object parameterObject);
}

public class BoundSql {
  private final String sql;
  private final List<ParameterMapping> parameterMappings;
  private final Object parameterObject;
  private final Map<String, Object> additionalParameters;
  private final MetaObject metaParameters;

  public BoundSql(Configuration configuration, String sql, List<ParameterMapping> parameterMappings, Object parameterObject) {
    this.sql = sql;
    this.parameterMappings = parameterMappings;
    this.parameterObject = parameterObject;
    this.additionalParameters = new HashMap<>();
    this.metaParameters = configuration.newMetaObject(additionalParameters);
  }

  public String getSql() {
    return sql;
  }

  public List<ParameterMapping> getParameterMappings() {
    return parameterMappings;
  }

  public Object getParameterObject() {
    return parameterObject;
  }

  public boolean hasAdditionalParameter(String name) {
    String paramName = new PropertyTokenizer(name).getName();
    return additionalParameters.containsKey(paramName);
  }

  public void setAdditionalParameter(String name, Object value) {
    metaParameters.setValue(name, value);
  }

  public Object getAdditionalParameter(String name) {
    return metaParameters.getValue(name);
  }
}

其中，SqlSource 有多个实现类，如下：

RawSqlSource：描述静态SqlSource，即不包含动态SQL相关配置的SQL资源信息。
DynamicSqlSource：描述动态SqlSource，包含动态SQL标签或${}占位符或#{}的SQL资源信息。
ProviderSqlSource：描述通过@Select、@SelectProvider等注解配置的SQL资源信息。
StaticSqlSource：描述RawSqlSource、DynamicSqlSource、ProviderSqlSource解析后得到的静态SQL资源信息。
VelocitySqlSource：截至当前版本（3.5.3），mybatis给出的注释为：Just a test case. Not a real Velocity implementation.

用于处理XML和注解方式配置的SQL的一个类：

public class XMLLanguageDriver implements LanguageDriver {

  @Override
  public ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql) {
    return new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
  }

  @Override
  public SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType) {
    XMLScriptBuilder builder = new XMLScriptBuilder(configuration, script, parameterType);
    return builder.parseScriptNode();
  }

  @Override
  public SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType) {
    // issue #3
    if (script.startsWith("<script>")) {
      XPathParser parser = new XPathParser(script, false, configuration.getVariables(), new XMLMapperEntityResolver());
      return createSqlSource(configuration, parser.evalNode("/script"), parameterType);
    } else {
      // issue #127
      script = PropertyParser.parse(script, configuration.getVariables());
      TextSqlNode textSqlNode = new TextSqlNode(script);
      if (textSqlNode.isDynamic()) {
        return new DynamicSqlSource(configuration, textSqlNode);
      } else {
        return new RawSqlSource(configuration, script, parameterType);
      }
    }
  }

}

对于动态SQL部分，只给出主要类，根据提供的类断点调试可以获知动态SQL的解析过程。

源码中处理$和#的流程：

1.注意下面美元符号用了单引号包裹，代码示例：

public interface StudentMapper {
  @Select("select * from student where no=#{no} and name='${name}'")
  Student getStudent(String no, String name);

  @Test
  void tests() {
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
      StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
      Student student = mapper.getStudent("1001", "francis");
      assertNotNull(student);
    }
  }

2.org.apache.ibatis.scripting.xmltags.DynamicSqlSource#getBoundSql中断点：

3.此时，sql还是原来的sql，进入rootSqlNode.apply(context)，如下：

4.进入parser.parse(text)，会调用org.apache.ibatis.parsing.GenericTokenParser#parse，然后再调用org.apache.ibatis.scripting.xmltags.TextSqlNode.BindingTokenParser#handleToken，然后再回到org.apache.ibatis.parsing.GenericTokenParser#parse时，已解析完${}，如下：

5.重新回到org.apache.ibatis.scripting.xmltags.DynamicSqlSource#getBoundSql，如下：

6.进入sqlSourceParser.parse(context.getSql(), parameterType, context.getBindings())，会调用到org.apache.ibatis.builder.SqlSourceBuilder#parse，如下：

7.进入parser.parse(originalSql)，会调用到org.apache.ibatis.parsing.GenericTokenParser#parse，如下：

8.接着第7步执行到builder.append(handler.handleToken(expression.toString()))时，进入handler.handleToken(expression.toString())，会调用到org.apache.ibatis.builder.SqlSourceBuilder.ParameterMappingTokenHandler#handleToken，如下：

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

    private ParameterMapping buildParameterMapping(String content) {
      Map<String, String> propertiesMap = parseParameterMapping(content);
      String property = propertiesMap.get("property");
      Class<?> propertyType;
      if (metaParameters.hasGetter(property)) { // issue #448 get type from additional params
        propertyType = metaParameters.getGetterType(property);
      } else if (typeHandlerRegistry.hasTypeHandler(parameterType)) {
        propertyType = parameterType;
      } else if (JdbcType.CURSOR.name().equals(propertiesMap.get("jdbcType"))) {
        propertyType = java.sql.ResultSet.class;
      } else if (property == null || Map.class.isAssignableFrom(parameterType)) {
        propertyType = Object.class;
      } else {
        MetaClass metaClass = MetaClass.forClass(parameterType, configuration.getReflectorFactory());
        if (metaClass.hasGetter(property)) {
          propertyType = metaClass.getGetterType(property);
        } else {
          propertyType = Object.class;
        }
      }
      ParameterMapping.Builder builder = new ParameterMapping.Builder(configuration, property, propertyType);
      Class<?> javaType = propertyType;
      String typeHandlerAlias = null;
      for (Map.Entry<String, String> entry : propertiesMap.entrySet()) {
        String name = entry.getKey();
        String value = entry.getValue();
        if ("javaType".equals(name)) {
          javaType = resolveClass(value);
          builder.javaType(javaType);
        } else if ("jdbcType".equals(name)) {
          builder.jdbcType(resolveJdbcType(value));
        } else if ("mode".equals(name)) {
          builder.mode(resolveParameterMode(value));
        } else if ("numericScale".equals(name)) {
          builder.numericScale(Integer.valueOf(value));
        } else if ("resultMap".equals(name)) {
          builder.resultMapId(value);
        } else if ("typeHandler".equals(name)) {
          typeHandlerAlias = value;
        } else if ("jdbcTypeName".equals(name)) {
          builder.jdbcTypeName(value);
        } else if ("property".equals(name)) {
          // Do Nothing
        } else if ("expression".equals(name)) {
          throw new BuilderException("Expression based parameters are not supported yet");
        } else {
          throw new BuilderException("An invalid property '" + name + "' was found in mapping #{" + content + "}.  Valid properties are " + PARAMETER_PROPERTIES);
        }
      }
      if (typeHandlerAlias != null) {
        builder.typeHandler(resolveTypeHandler(javaType, typeHandlerAlias));
      }
      return builder.build();
    }

9.执行完上述操作后回到org.apache.ibatis.builder.SqlSourceBuilder#parse，如下：

10.执行完中间流程，直到执行到org.apache.ibatis.scripting.defaults.DefaultParameterHandler#setParameters 的 typeHandler.setParameter(ps, i + 1, value, jdbcType)，可看到如下效果：

到这里，SQL语句已经完成参数值的替换。

有一点需要注意的是：${}取到的值是什么就是什么，不会进行类型转换，而#{}的值替换时会调用StringTypeHandler（只针对本文示例），上面我的StudentMapper 中是加了单引号的，如果不加，效果如下：

public interface StudentMapper {
  @Select("select * from student where no=#{no} and name=${name}")
  Student getStudent(String no, String name);
}

不一定每次都会报错，如果调用getStudent()时传值为数字不会报错，但是会导致数据库索引失效。