已有项目已经和【MYSQL】语法绑定死（比如：字段使用``，分页使用 limit，group by 字段包含不在组的字段等），在entity中写了很多以下类似代码：

    /**
     * 数据来源
     */
    @TableField(value = "`source`")
    private String source;

或者在xml中写死了很多mysql语法的sql语句:

 <sql id="Base_Column_List">
    <!--@mbg.generated-->
    id, code, attach_code, `name`, `level`, pcode, disease_type, create_time, update_time
  </sql>
  <insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
    <!--@mbg.generated-->
    insert into basic_dict_disease
    (code, attach_code, `name`, `level`, pcode, disease_type, create_time, update_time
      )
    values
    <foreach collection="list" item="item" separator=",">
      (#{item.code,jdbcType=VARCHAR}, #{item.attachCode,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR}, 
        #{item.level,jdbcType=INTEGER}, #{item.pcode,jdbcType=VARCHAR}, #{item.diseaseType,jdbcType=INTEGER}, 
        #{item.createTime,jdbcType=TIMESTAMP}, #{item.updateTime,jdbcType=TIMESTAMP})
    </foreach>
  </insert>

此时如果想把底层数据库换成【达梦8】的话，底层语言为mybatis、mybatisplus，并且要求对现有代码毫无侵入性，代码无须改动的情况下才建议参考此文。
此文的目的是做到尽可能通用化方案，把达梦8或者mysql换成其他数据库，参考本文方案做些微调照样可以实现。

数据库准备（以达梦8为例）

官网下载 https://eco.dameng.com/download/

image.png

此处以windows系统举例(linux部署方式自行参考官方文档)
一路下一步点过来，需要注意的是在初始化参数的时候需要修改默认参数（此处坑太多，仔细检查）

1、页大小改为32k（linux对应的配置自行搜索）
  mysql中varchar类型的字段内容如果设置很大话，会超过默认的页大小，此时当你迁移数据的时候达梦会报错
2、字符集改为UTF-8（linux配置为CHARSET=1）
3、字符串大小写敏感去掉 （linux配置为CASE_SENSITIVE=0）
4、VARCHAR类型以字符为单位（linux配置为LENGTH_IN_CHAR=1）
5、数据库模式设置为mysql （linux配置为compatible_mode=4）
      这个需要等达梦数据库安装成功后在对应的安装目录（此处展示的是相对目录，设置完后达梦实例服务需要重启）
      ....\data\DAMENG\dm.ini 文件中修改compatible_mode=4

image.png

代码方面

代码方面会大量采用java源代码，只会大致解释该代码主要作用。具体细节自行测试体会。

• DbConfig.java

• 此处只是做了一个简易配置DB_KEYWORD_MAPPING 放的是 每个数据库类型 对应的字段以什么方式进行包装。比如mysql是`` 达梦则是 ""
• DB_KEYWORDS 中放的则是 数据库中的特殊关键字需要进行包装的配置。后续sql语句中凡是碰到对应关键字的都进行包装 比如 SYNONYM 在 达梦中会包装成 "SYNONYM"

import com.baomidou.mybatisplus.annotation.DbType;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author mysuperl

 * @date 2023-08-22
 */
public class DbConfig {
    private final static Map<DbType, String> DB_KEYWORD_MAPPING = new HashMap<DbType, String>(16) {{
        put(DbType.DM, "\"");
    }};
    /**
     * 数据库关键字（只处理在字段中有可能出现的关键字）
     */
    public final static List<String> DB_KEYWORDS = new ArrayList<String>(64) {{
        add("STATUS");
        add("SYNONYM");
    }};

    public static String getReplaceStr(DbType dbType) {
        return DB_KEYWORD_MAPPING.getOrDefault(dbType, "");
    }

    public static String replaceAll(String str, String replaceStr) {
        return str.replaceAll("`", replaceStr);
    }

    public static String appendAll(String columnName, String replaceStr) {
        if (columnName.startsWith(replaceStr) && columnName.endsWith(replaceStr)) {
            return columnName;
        }
        if (columnName.startsWith("`") && columnName.endsWith("`")) {
            return replaceAll(columnName, replaceStr);
        }
        return replaceStr + columnName + replaceStr;
    }
}

• PropertiesCustomizer.java

此java文件的大致意思是去扫描 ENTITY_CLASSPATH、DOMAIN_CLASSPATH 两处位置下的所有实体类,将实体类中包含有TableField注解的字段value值替换成对应数据的字段格式 。比如： @TableField(value = "`source`") 对应值最终会动态替换成@TableField(value = ""source"")

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusPropertiesCustomizer;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.type.classreading.SimpleMetadataReaderFactory;

import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Proxy;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * @author mysuperl

 * @date 2023-08-22
 */
@Slf4j
public class PropertiesCustomizer implements MybatisPlusPropertiesCustomizer {

    @Getter
    private final String replaceStr;

    private final static String ENTITY_CLASSPATH = "classpath*:com/**/entity/**/*.class";
    private final static String DOMAIN_CLASSPATH = "classpath*:com/**/domain/**/*.class";

    public PropertiesCustomizer(String replaceStr) {
        this.replaceStr = replaceStr;
    }

    @Override
    public void customize(MybatisPlusProperties properties) {
//            log.warn(">>>dataSource: {} \n properties: {}", dataSource, properties);
        if (StringUtils.isBlank(getReplaceStr())) {
            return;
        }
        log.info("##### 加载{}...", this.getClass().getSimpleName());
        try {
            List<Class<?>> classes = findClasses(ENTITY_CLASSPATH);
            classes.addAll(findClasses(DOMAIN_CLASSPATH));
            log.debug(">>>classes: {}", classes);
            this.tableFieldMappings(classes);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 表字段映射
     *
     * @param classes
     */
    private void tableFieldMappings(List<Class<?>> classes) {
        classes.forEach(clazz -> {
            List<Field> list = TableInfoHelper.getAllFields(clazz);
            list.forEach(field -> {
                TableField tableField = field.getAnnotation(TableField.class);
                String metaColName;
                if (Objects.nonNull(tableField) && StringUtils.isNotBlank(metaColName = tableField.value()) && metaColName.contains("`")) {

                    String newColName = DbConfig.replaceAll(metaColName, getReplaceStr());

                    InvocationHandler invocationHandler = Proxy.getInvocationHandler(tableField);
                    try {
                        Field memberValues = invocationHandler.getClass().getDeclaredField("memberValues");
                        memberValues.setAccessible(true);
                        Map<String, Object> memberValuesMap = (Map<String, Object>) memberValues.get(invocationHandler);
                        memberValuesMap.put("value", newColName);
                        log.info("将实体类映射字段{}修改为{}", metaColName, newColName);
                    } catch (NoSuchFieldException | IllegalAccessException exception) {
                        throw new RuntimeException(exception);
                    }
                }
            });
        });
    }


    private List<Class<?>> findClasses(String classpath) throws IOException {
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        //找到所有实体类的class
        Resource[] resources = resolver.getResources(classpath);
        return Arrays.stream(resources).map(res -> {
                    try {
                        // 先获取resource的元信息，然后获取class元信息，最后得到 class 全路径
                        String clsName = new SimpleMetadataReaderFactory().getMetadataReader(res).getClassMetadata().getClassName();
                        return Class.forName(clsName);
                    } catch (IOException | ClassNotFoundException e) {
                        log.debug(">>>>>找不到class文件:", e);
                    }
                    return null;
                }).filter(Objects::nonNull)
                .collect(Collectors.toList());
    }
}

• ParseSQLInnerInterceptor.java

import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.core.toolkit.EncryptUtils;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.parser.CCJSqlParser;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.HashSet;
import java.util.Set;

/**
 * @author mysuperl
 * @date 2023-08-22
 */
@Slf4j
public class ParseSQLInnerInterceptor implements InnerInterceptor {


    @Getter
    private final String replaceStr;
    /**
     * 缓存验证结果，提高性能
     */
    private static final Set<String> cacheValidResult = new HashSet<>();

    public ParseSQLInnerInterceptor(String replaceStr) {
        this.replaceStr = replaceStr;
    }

    @Override
    public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
        try {
            PluginUtils.MPStatementHandler mpStatementHandler = PluginUtils.mpStatementHandler(sh);
            MappedStatement ms = mpStatementHandler.mappedStatement();
            if (InterceptorIgnoreHelper.willIgnoreIllegalSql(ms.getId())) {
                return;
            }
            BoundSql boundSql = mpStatementHandler.boundSql();
            String originalSql = boundSql.getSql();
            log.debug("检查SQL是否合规,SQL:" + originalSql);
            String md5Base64 = EncryptUtils.md5Base64(originalSql);
            if (cacheValidResult.contains(md5Base64)) {
                log.debug("该SQL已验证,无需再次验证.SQL:" + originalSql);
                return;
            }
            LogVisitor logVisitor = new LogVisitor(getReplaceStr());
            // 先将原始sql语句中的特殊关键字替换一遍，这样后续解析sql才不会报错
            originalSql = logVisitor.rebuildSql(originalSql);
            // 调用开源组件解析sql语句，具体解析逻辑在logVisitor中
            CCJSqlParser parser = CCJSqlParserUtil.newParser(originalSql);
            Statement stmt = parser.Statement();
            parser.getASTRoot().jjtAccept(logVisitor, null);
            String newSql = stmt.toString();

            //修改sql 获取到最终修改的sql，然后重新设置到boundSql对象中
            try {
                newSql = DbConfig.replaceAll(newSql, getReplaceStr());
                Field field = boundSql.getClass().getDeclaredField("sql");
                field.setAccessible(true);
                field.set(boundSql, newSql);
            } catch (NoSuchFieldException | IllegalAccessException e) {
                throw new RuntimeException(e);
            }
            //缓存验证结果
            cacheValidResult.add(md5Base64);
        } catch (Exception e) {
            log.error("### beforePrepare error.", e);
        }

    }


}

• LogVisitor.java

import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.parser.CCJSqlParserDefaultVisitor;
import net.sf.jsqlparser.parser.CCJSqlParserTreeConstants;
import net.sf.jsqlparser.parser.SimpleNode;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import org.apache.commons.lang3.StringUtils;

import java.text.BreakIterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Objects;

/**
 * @author huss
 * @date 2023-08-22
 */
@Slf4j
public class LogVisitor extends CCJSqlParserDefaultVisitor {
    @Getter
    private final String replaceStr;

    public LogVisitor(String replaceStr) {
        this.replaceStr = replaceStr;
    }

//    public static void main(String[] args) throws ParseException {
//        String sql = "SELECT person.id,person.name,group.name,synonym as \"症状\", synonym_name as \"症状名称\"  FROM person JOIN group ON person.group_id = group.id WHERE person.birthdat > '1980-01-01'";
//        sql = "INSERT INTO ebm_emr_nsyy.emr_resolve_switch_cfg (res_sw_id, classify_name, data_type,synonym, switch_flag, type_desc, create_time, update_time, frequency) VALUES (96, '病案首页', 10015, 1, '（提醒）诊断合并', '2021-05-31 17:07:08', '2021-05-31 17:07:08', 101);";
////         sql = "测试，咳嗽，咳嗽伴哮喘";
//        System.out.println("原sql：" + sql);
//        String replaceStr = DbConfig.getReplaceStr(DbType.DM);
//
//        LogVisitor logVisitor = new LogVisitor(replaceStr);
//
//        BreakIterator boundary = BreakIterator.getWordInstance();
//        boundary.setText(sql);
//        sql = logVisitor.rebuildSql(sql);
//        System.out.println("新sql：" + sql);
//
//        CCJSqlParser parser = CCJSqlParserUtil.newParser(sql);
//        Statement stmt = parser.Statement();
//        parser.getASTRoot().jjtAccept(logVisitor, null);
//        System.out.printf("sql--> %s", stmt.toString());
//    }

    /**
     * 重新构建sql
     *
     * @param sql
     * @return
     */
    public String rebuildSql(String sql) {
        BreakIterator boundary = BreakIterator.getWordInstance();
        boundary.setText(sql);
        List<String> words = new LinkedList<>();
        int end = boundary.first();
        while (end != BreakIterator.DONE) {
            int start = end;
            end = boundary.next();
            if (end == BreakIterator.DONE) {
                break;
            }
            String word = sql.substring(start, end);
            String finalWord = word;
            if (DbConfig.DB_KEYWORDS.stream().anyMatch(e -> e.equalsIgnoreCase(finalWord))) {
                word = DbConfig.appendAll(word, replaceStr);
            }
            words.add(word);
        }
        String newSql = String.join("", words);

        log.info("#####重新构建后的sql--> {}", newSql);
        return newSql;
    }


    @Override
    public Object visit(SimpleNode node, Object data) {
        Object value = node.jjtGetValue();
        log.debug("id:{} class:{} value:{}", node.getId(), Objects.nonNull(value) ? value.getClass().getSimpleName() : null, value);
        if (node.getId() == CCJSqlParserTreeConstants.JJTCOLUMN) {
            Column column = (Column) value;
            column.setColumnName(DbConfig.appendAll(column.getColumnName(), getReplaceStr()));
            Table table = column.getTable();
            if (Objects.nonNull(table)) {
                table.setName(DbConfig.appendAll(table.getName(), getReplaceStr()));
            }
        } else if (node.getId() == CCJSqlParserTreeConstants.JJTTABLE) {
            Table table = (Table) value;
            if (StringUtils.isNotBlank(table.getSchemaName())) {
                table.setSchemaName(DbConfig.appendAll(table.getSchemaName(), getReplaceStr()));
            }
            if (StringUtils.isNotBlank(table.getName())) {
                table.setName(DbConfig.appendAll(table.getName(), getReplaceStr()));
            }
        }
        return super.visit(node, data);
    }

以上核心代码已经完全实现了目的，剩下的只需要将其当成bean配置到spring中即可。

    @Autowired
    private DataSource dataSource;

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        interceptor.addInnerInterceptor(new ParseSQLInnerInterceptor(parseReplaceStr()));
        return interceptor;
    }

    @Bean
    public MybatisPlusPropertiesCustomizer mybatisPlusPropertiesCustomizer() {
        return new PropertiesCustomizer(parseReplaceStr());
    }

    private String parseReplaceStr() {
        // 此处的datasource使用的阿里的druid连接池，用的其他连接池的话此处则需要自己去修改
        DruidDataSource druidDataSource = (DruidDataSource) dataSource;
        return DbConfig.getReplaceStr(DbType.getDbType(druidDataSource.getDbType()));
    }

结束，有其他更好的方案欢迎讨论。