Mybatis实现@Select@Update等注解动态查询或更新SQL语句

标签：script matcher org sql Update apache SQL Mybatis import

通过自己实现LanguageDriver，在服务器启动的时候，就会将我们自定义的标签解析为动态SQL语句。
例如，写个构造update in 的动态sql更新，代码如下：

package com.ljw.web.common.mybatis;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.scripting.xmltags.XMLLanguageDriver;
import org.apache.ibatis.session.Configuration;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

/** 构造update in 动态sql更新 */
@Slf4j
/** @author seqwait */
public class LanguageDriverUpdateIn extends XMLLanguageDriver implements LanguageDriver {

  private final Pattern pattern = Pattern.compile("\\(#\\{(\\w+)\\}\\)");

  @Override
  public SqlSource createSqlSource(
      Configuration configuration, String script, Class<?> parameterType) {
    Matcher matcher = pattern.matcher(script);
    if (matcher.find()) {
      script =
          matcher.replaceAll(
              "(<foreach collection=\"$1\" item=\"__item\" separator=\",\" >#{__item}</foreach>)");
    }
    script = "<script>" + script + "</script>";
    SqlSource source = super.createSqlSource(configuration, script, parameterType);
    log.info("sql=>{}", script);
    return source;
  }
}

DAO接口层的动态更新sql上面，加上自定义动态update注解，代码如下：

  @Lang(LanguageDriverUpdateIn.class)
  @Update(
      "update user_level set level=1,updateAt=now() where uid in(#{uids})")
  void updateLevel(@Param("uids") Collection<Long> uids);

原理：通过实现LanguageDriver，剥离了冗长的动态拼接SQL语句，简化了Update In的注解代码。

需要注意的是在使用Update In的时候，必在传入的参数前加@Param注解，否则会导致Mybatis找不到参数而抛出异常。

这样的sql语句是不是显得非常简洁，又可实现动态更新多条记录

再来对比一下常规写法，代码如下：

@Update({
          "<script> "
                  + "update user_level set level=1,updateAt=now() where uid in ("
                  + "<foreach collection='uids'  item='uid' separator=','> "
                  + "#{uid} "
                  + "</foreach> ) ",
          "</script>"
})
  void updateLevel(@Param("uids") Collection<Long> uids);

这种写法是不是代码量多了很多，麻烦不说，还容易写错，额外增加了工作难度。

 

类似的自定义动态sql查询注解，代码如下：

package com.ljw.web.common.mybatis;

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.scripting.xmltags.XMLLanguageDriver;
import org.apache.ibatis.session.Configuration;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

/** 构造select in 动态sql查询 */
@Slf4j
/** @author seqwait */
public class LanguageDriverInsertBatch extends XMLLanguageDriver implements LanguageDriver {

  private final Pattern pattern =
      Pattern.compile(
          "(^\\s*?(?:replace|insert)\\s+into\\s+\\w+\\s+)\\((.*?)\\)\\s+(values)\\s+\\(#\\{(\\w+)}\\)");

  @Override
  public SqlSource createSqlSource(
      Configuration configuration, String script, Class<?> parameterType) {
    Matcher matcher = pattern.matcher(script);
    if (matcher.find()) {
      String[] columns = matcher.group(2).split(",");
      String sql =
          "%s (%s) values" + "<foreach collection='%s' item='item' separator=',' >(%s)</foreach>";
      StringBuilder sqls = new StringBuilder();
      for (int i = 0; i < columns.length; i++) {
        sqls.append("#{item.").append(columns[i]).append("}");
        if (i != columns.length - 1) {
          sqls.append(",");
        }
      }
      script =
          String.format(sql, matcher.group(1), matcher.group(2), matcher.group(4), sqls.toString());
    }
    script = "<script>" + script + "</script>";
    return super.createSqlSource(configuration, script, parameterType);
  }
}

 

标签：script,matcher,org,sql,Update,apache,SQL,Mybatis,import
From： https://www.cnblogs.com/Daniel-Leung/p/20221011174130MybatisSelectIn.html