序

版本：mybatis-plus-boot-starter 3.3.2、3.4.0

刚开始走了弯路，想着通过 Oracle 解决，先是修改了 NLS_DATE_FORMAT，结果发现不适用 JDBC。当然也可以写触发器，每次执行 SQL 前修改这个参数，但是肯定会影响性能。

解

解决方案是重写通用 insert 方法，用到了 @TableField 注解的 update 属性。

1. 重写通用 insert 方法

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.enums.SqlMethod;
import com.baomidou.mybatisplus.core.injector.methods.Insert;
import com.baomidou.mybatisplus.core.metadata.TableFieldInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.core.toolkit.sql.SqlScriptUtils;
import org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator;
import org.apache.ibatis.executor.keygen.KeyGenerator;
import org.apache.ibatis.executor.keygen.NoKeyGenerator;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.type.JdbcType;

import java.util.List;

/**
 * 注意 MyBatis Plus 升级版本后需要同步其源码
 * <p>
 * 重写通用 insert 方法，主要为了解决数据库中字段类型为 DATE，实体类中属性类型为 String 时需要使用 TO_DATE 函数的问题
 */
public class MyInsert extends Insert {

  // /**
  //  * 3.3.2
  //  *
  //  * @param mapperClass
  //  * @param modelClass
  //  * @param tableInfo
  //  * @return
  //  */
  // @Override
  // public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
  //   KeyGenerator keyGenerator = new NoKeyGenerator();
  //   SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
  //   String columnScript = SqlScriptUtils.convertTrim(tableInfo.getAllInsertSqlColumnMaybeIf(), "(", ")", (String) null, ",");
  //   String valuesScript = SqlScriptUtils.convertTrim(tableInfo.getAllInsertSqlPropertyMaybeIf((String) null), "(", ")", (String) null, ",");
  //   String keyProperty = null;
  //   String keyColumn = null;
  //
  //   // region 自定义内容，其他皆为源码 com.baomidou.mybatisplus.core.injector.methods.Insert.injectMappedStatement
  //   List<TableFieldInfo> tableFieldInfoList = tableInfo.getFieldList();
  //   for (TableFieldInfo tableFieldInfo : tableFieldInfoList) {
  //     JdbcType jdbcType = tableFieldInfo.getJdbcType();
  //     String update = tableFieldInfo.getUpdate();
  //     if (jdbcType != null && jdbcType.equals(JdbcType.DATE) && tableFieldInfo.getPropertyType().isAssignableFrom(String.class) && update.toLowerCase().contains("to_date(")) {
  //       valuesScript = valuesScript.replace("#{" + tableFieldInfo.getProperty() + ",jdbcType=" + jdbcType + "}", "#" + update.replaceAll("#\\{et\\..*}", tableFieldInfo.getProperty() + ",jdbcType=" + jdbcType));
  //     }
  //   }
  //   // endregion
  //
  //   if (StringUtil.isNotBlank(tableInfo.getKeyProperty())) {
  //     if (tableInfo.getIdType() == IdType.AUTO) {
  //       keyGenerator = new Jdbc3KeyGenerator();
  //       keyProperty = tableInfo.getKeyProperty();
  //       keyColumn = tableInfo.getKeyColumn();
  //     } else if (null != tableInfo.getKeySequence()) {
  //       keyGenerator = TableInfoHelper.genKeyGenerator(this.getMethod(sqlMethod), tableInfo, this.builderAssistant);
  //       keyProperty = tableInfo.getKeyProperty();
  //       keyColumn = tableInfo.getKeyColumn();
  //     }
  //   }
  //
  //   String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), columnScript, valuesScript);
  //   SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
  //   return this.addInsertMappedStatement(mapperClass, modelClass, this.getMethod(sqlMethod), sqlSource, keyGenerator, keyProperty, keyColumn);
  // }

  /**
   * 3.4.0
   *
   * @param mapperClass
   * @param modelClass
   * @param tableInfo
   * @return
   */
  @Override
  public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
    KeyGenerator keyGenerator = new NoKeyGenerator();
    SqlMethod sqlMethod = SqlMethod.INSERT_ONE;
    String columnScript = SqlScriptUtils.convertTrim(tableInfo.getAllInsertSqlColumnMaybeIf((String) null), "(", ")", (String) null, ",");
    String valuesScript = SqlScriptUtils.convertTrim(tableInfo.getAllInsertSqlPropertyMaybeIf((String) null), "(", ")", (String) null, ",");
    String keyProperty = null;
    String keyColumn = null;

    // region 自定义内容，其他皆为源码 com.baomidou.mybatisplus.core.injector.methods.Insert.injectMappedStatement
    List<TableFieldInfo> tableFieldInfoList = tableInfo.getFieldList();
    for (TableFieldInfo tableFieldInfo : tableFieldInfoList) {
      JdbcType jdbcType = tableFieldInfo.getJdbcType();
      String update = tableFieldInfo.getUpdate();
      if (jdbcType != null && jdbcType.equals(JdbcType.DATE) && tableFieldInfo.getPropertyType().isAssignableFrom(String.class) && update.toLowerCase().contains("to_date(")) {
        valuesScript = valuesScript.replace("#{" + tableFieldInfo.getProperty() + ",jdbcType=" + jdbcType + "}", update.replaceAll("#\\{et\\..*}", "#{" + tableFieldInfo.getProperty() + "}"));
      }
    }
    // endregion

    if (StringUtils.isNotBlank(tableInfo.getKeyProperty())) {
      if (tableInfo.getIdType() == IdType.AUTO) {
        keyGenerator = new Jdbc3KeyGenerator();
        keyProperty = tableInfo.getKeyProperty();
        keyColumn = tableInfo.getKeyColumn();
      } else if (null != tableInfo.getKeySequence()) {
        keyGenerator = TableInfoHelper.genKeyGenerator(this.getMethod(sqlMethod), tableInfo, this.builderAssistant);
        keyProperty = tableInfo.getKeyProperty();
        keyColumn = tableInfo.getKeyColumn();
      }
    }

    String sql = String.format(sqlMethod.getSql(), tableInfo.getTableName(), columnScript, valuesScript);
    SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
    return this.addInsertMappedStatement(mapperClass, modelClass, this.getMethod(sqlMethod), sqlSource, (KeyGenerator) keyGenerator, keyProperty, keyColumn);
  }
}

2. 自定义 SQL 注入器

/**
 * 自定义 SQL 注入器，用于新增通用方法
 */
@Component
public class MySqlInjector extends DefaultSqlInjector {

  @Override
  public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
    List<AbstractMethod> methodList = super.getMethodList(mapperClass);
    // 替换 insert 通用方法
    for (int i = 0; i < methodList.size(); i++) {
      if (methodList.get(i) instanceof Insert) {
        methodList.set(i, new MyInsert());
        break;
      }
    }
    return methodList;
  }
}

3. 实体类做修改

import com.baomidou.mybatisplus.annotation.KeySequence;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import org.apache.ibatis.type.JdbcType;

import java.io.Serializable;

@KeySequence("SEQ_FLT_SEASON")
@Data
public class FlightSeason implements Serializable {

  private static final long serialVersionUID = 1L;

  // ……
  
  /**
   * 开始时间
   */
  @TableField(jdbcType = JdbcType.DATE, update = "to_date(#{et.startTime},'yyyy-mm-dd hh24:mi:ss')")
  private String startTime;
  /**
   * 结束时间
   */
  @TableField(jdbcType = JdbcType.DATE, update = "to_date(#{et.endTime},'yyyy-mm-dd hh24:mi:ss')")
  private String endTime;
  
  // ……
}

参考

• Sql 注入器 | MyBatis-Plus
• 注解 | MyBatis-Plus