從源碼角度淺析 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);
}

從源碼角度淺析 Mybatis 動态SQL及$和#的差別

繼續閱讀

windows下配置Apache的vhost初次接觸，強烈歡迎拍磚，指出錯誤

Apache與PHP環境下配置本地虛拟主機

SQL語言基礎：常用的資料查詢語句

Linux 7 中配置Apache服務，及禁止ip通路，删除apache廣告頁面。

Apache配置檔案中的deny和allow的使用

Apache 配置預設編碼

伺服器配置——Apache

Apache靜态檔案通路配置（書封伺服器）

apache httpd 配置

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Apache配置SSLApache配置SSL

Windows下配置Apache的SSL服務

Apache2.4.x 配置檔案詳解Apache配置需要了解如下：開始講解：

配置apache支援PHP（win7）

neo4j之cypher使用文檔

sqlServer根據經緯查距離