已有項目已經(jīng)和【MYSQL】語法綁定死（比如：字段使用``敢订，分頁使用 limit楚午，group by 字段包含不在組的字段等），在entity中寫了很多以下類似代碼：

    /**
     * 數(shù)據(jù)來源
     */
    @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>

此時如果想把底層數(shù)據(jù)庫換成【達夢8】的話，底層語言為mybatis里覆、mybatisplus缆瓣，并且要求對現(xiàn)有代碼毫無侵入性弓坞，代碼無須改動的情況下才建議參考此文渡冻。
此文的目的是做到盡可能通用化方案族吻，把達夢8或者mysql換成其他數(shù)據(jù)庫，參考本文方案做些微調(diào)照樣可以實現(xiàn)。

數(shù)據(jù)庫準(zhǔn)備（以達夢8為例）

官網(wǎng)下載 https://eco.dameng.com/download/

image.png

此處以windows系統(tǒng)舉例(linux部署方式自行參考官方文檔)
一路下一步點過來切平，需要注意的是在初始化參數(shù)的時候需要修改默認參數(shù)（此處坑太多握础，仔細檢查）

1、頁大小改為32k（linux對應(yīng)的配置自行搜索）
  mysql中varchar類型的字段內(nèi)容如果設(shè)置很大話悴品，會超過默認的頁大小禀综，此時當(dāng)你遷移數(shù)據(jù)的時候達夢會報錯
2简烘、字符集改為UTF-8（linux配置為CHARSET=1）
3、字符串大小寫敏感去掉 （linux配置為CASE_SENSITIVE=0）
4定枷、VARCHAR類型以字符為單位（linux配置為LENGTH_IN_CHAR=1）
5孤澎、數(shù)據(jù)庫模式設(shè)置為mysql （linux配置為compatible_mode=4）
      這個需要等達夢數(shù)據(jù)庫安裝成功后在對應(yīng)的安裝目錄（此處展示的是相對目錄欠窒，設(shè)置完后達夢實例服務(wù)需要重啟）
      ....\data\DAMENG\dm.ini 文件中修改compatible_mode=4

image.png

代碼方面

代碼方面會大量采用java源代碼覆旭，只會大致解釋該代碼主要作用。具體細節(jié)自行測試體會岖妄。

• DbConfig.java

• 此處只是做了一個簡易配置DB_KEYWORD_MAPPING 放的是 每個數(shù)據(jù)庫類型 對應(yīng)的字段以什么方式進行包裝型将。比如mysql是`` 達夢則是 ""
• DB_KEYWORDS 中放的則是 數(shù)據(jù)庫中的特殊關(guān)鍵字需要進行包裝的配置。后續(xù)sql語句中凡是碰到對應(yīng)關(guān)鍵字的都進行包裝 比如 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, "\"");
    }};
    /**
     * 數(shù)據(jù)庫關(guān)鍵字（只處理在字段中有可能出現(xiàn)的關(guān)鍵字）
     */
    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值替換成對應(yīng)數(shù)據(jù)的字段格式 七兜。比如： @TableField(value = "`source`") 對應(yīng)值最終會動態(tài)替換成@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;
    /**
     * 緩存驗證結(jié)果腕铸，提高性能
     */
    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是否合規(guī),SQL:" + originalSql);
            String md5Base64 = EncryptUtils.md5Base64(originalSql);
            if (cacheValidResult.contains(md5Base64)) {
                log.debug("該SQL已驗證,無需再次驗證.SQL:" + originalSql);
                return;
            }
            LogVisitor logVisitor = new LogVisitor(getReplaceStr());
            // 先將原始sql語句中的特殊關(guān)鍵字替換一遍，這樣后續(xù)解析sql才不會報錯
            originalSql = logVisitor.rebuildSql(originalSql);
            // 調(diào)用開源組件解析sql語句铛碑，具體解析邏輯在logVisitor中
            CCJSqlParser parser = CCJSqlParserUtil.newParser(originalSql);
            Statement stmt = parser.Statement();
            parser.getASTRoot().jjtAccept(logVisitor, null);
            String newSql = stmt.toString();

            //修改sql 獲取到最終修改的sql狠裹，然后重新設(shè)置到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);
            }
            //緩存驗證結(jié)果
            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());
//    }

    /**
     * 重新構(gòu)建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("#####重新構(gòu)建后的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);
    }

以上核心代碼已經(jīng)完全實現(xiàn)了目的酪耳，剩下的只需要將其當(dāng)成bean配置到spring中即可。

    @Autowired
    private DataSource dataSource;

    /**
     * 新的分頁插件,一緩和二緩遵循mybatis的規(guī)則,需要設(shè)置 MybatisConfiguration#useDeprecatedExecutor = false 避免緩存出現(xiàn)問題(該屬性會在舊插件移除后一同移除)
     */
    @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()));
    }

結(jié)束碗暗，有其他更好的方案歡迎討論。