mybatis-plus由mysql改成達夢數據庫

前置條件: 達夢數據庫設置了大小寫敏感,我比較菜,改不動!先這么湊合著用吧;
因為設置了大小寫敏感,所以所有的sql語句都要加引號;

這樣是會報錯的:
SELECT  remark,createDept,createBy,createTime,updateBy,updateTime  FROM sys_oss_config這樣才可以
SELECT  "create_dept", "create_by", "create_time","update_by", "update_time" FROM "sys_oss_config"

所以只需要將表名和字段名都加上引號就行了

第一步添加依賴

     <dependency><groupId>com.dameng</groupId><artifactId>Dm8JdbcDriver18</artifactId><version>8.1.1.49</version></dependency><!-- Druid 數據源 --><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.16</version></dependency>

配置文件修改

type: com.alibaba.druid.pool.DruidDataSource
driverClassName: dm.jdbc.driver.DmDriver
url: jdbc:dm://10.10.10.10:5236?schema=RY_VUE5&useUnicode=true&characterEncoding=utf8
username: SYSDBA
password: DmAdmin123

尤其要注意的是庫名里面不能有橫杠類似這樣"RY-VUE5",這就容易出問題;
指定庫要用 schema=RY_VUE5

剩下的就在mybatis-plus配置類中添加兩個攔截器
一個是針對所有sql ,添加引號 ,一個是對mybatisplus分頁sql 改寫的

import cn.hutool.core.net.NetUtil;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.DdlApplicationRunner;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusAutoConfiguration;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator;
import com.baomidou.mybatisplus.core.incrementer.IdentifierGenerator;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.ParameterUtils;
import com.baomidou.mybatisplus.extension.ddl.IDdl;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfiguration;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.PropertySource;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import java.util.*;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;import java.sql.SQLException;
import java.util.regex.Matcher;
import java.util.regex.Pattern;public class MybatisPlusConfig {@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();// 這倆攔截器要放前面interceptor.addInnerInterceptor(new QuoteAllSqlInterceptor());interceptor.addInnerInterceptor(new DmMultiLevelPaginationInterceptor());return interceptor;}public static class QuoteAllSqlInterceptor implements InnerInterceptor {// 表名匹配正則（不變）private static final Pattern TABLE_PATTERN = Pattern.compile("(FROM|JOIN)\\s+([a-zA-Z0-9_]+(\\.[a-zA-Z0-9_]+)?)",Pattern.CASE_INSENSITIVE);// 優化字段匹配正則：解決末尾字段未被匹配的問題// 新增對SQL末尾字段的支持（允許字段后是空格、逗號或換行）private static final Pattern COLUMN_PATTERN = Pattern.compile("(?<!\\\")\\b([a-zA-Z0-9_]+)\\b(?!\\\")(?!\\s+[A-Za-z0-9_]*\\.)(?=\\s*[,)\\s]|\\s+AS|\\s+IN|\\s+LIKE)",Pattern.CASE_INSENSITIVE);private static final Pattern COLUMN_ALIAS = Pattern.compile("(FROM|JOIN)\\s+[\"`\\w\\.]+\\s+([a-zA-Z0-9_]+)",Pattern.CASE_INSENSITIVE);@Overridepublic void beforeQuery(Executor executor, MappedStatement ms, Object parameter,RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {processSql(boundSql);}@Overridepublic void beforeUpdate(Executor executor, MappedStatement ms, Object parameter) throws SQLException {BoundSql boundSql = ms.getBoundSql(parameter);processSql(boundSql);}@Overridepublic void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {BoundSql boundSql = sh.getBoundSql();processSql(boundSql);}private void processSql(BoundSql boundSql) {PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);String originalSql = mpBs.sql();if (originalSql == null || originalSql.isEmpty()) {return;}// 先處理表名，再處理字段名String sqlWithTables = addQuotesToTables(originalSql);String sqlWithColumns = addQuotesToColumns(sqlWithTables);//  mp 強制更新SQL，確保所有字段被處理mpBs.sql(sqlWithColumns);}// 表名添加引號（不變）private String addQuotesToTables(String sql) {Matcher matcher = TABLE_PATTERN.matcher(sql);StringBuffer sb = new StringBuffer();while (matcher.find()) {String keyword = matcher.group(1);String tableName = matcher.group(2);if (tableName.contains(".")) {String[] parts = tableName.split("\\.");matcher.appendReplacement(sb, keyword + " \"" + parts[0] + "\".\"" + parts[1] + "\"");} else {matcher.appendReplacement(sb, keyword + " \"" + tableName + "\"");}}return matcher.appendTail(sb).toString();}// 字段名添加引號（重點優化）private String addQuotesToColumns(String sql) {Matcher matcher1 = COLUMN_ALIAS.matcher(sql);List<String> aliases = new ArrayList<>();while (matcher1.find()) {aliases.add(matcher1.group(2)); // 捕獲組2為別名}Matcher matcher = COLUMN_PATTERN.matcher(sql);StringBuffer sb = new StringBuffer();while (matcher.find()) {String column = matcher.group(1);// 單獨打印匹配到的字段，方便調試System.out.println("匹配到字段：" + column);if (isKeyword(column)) {matcher.appendReplacement(sb, column);} else {if (aliases.contains(column)) {System.out.println("別名不加引號：" + column);} else {matcher.appendReplacement(sb, "\"" + column + "\"");}}}String result = matcher.appendTail(sb).toString();// 打印處理后的SQL，確認是否添加引號System.out.println("處理后的SQL：" + result);return result;}// 完善關鍵字列表（確保不包含實際字段名）private boolean isKeyword(String word) {Set<String> keywords = new HashSet<>(Arrays.asList("SELECT", "FROM", "WHERE", "AND", "OR", "GROUP", "BY", "ORDER", "LIMIT","INSERT", "UPDATE", "DELETE", "SET", "VALUES", "AS", "JOIN", "ON", "NULL","DESC", "ASC", "IN", "LIKE", "BETWEEN", "IS", "NOT", "LEFT","INTO"));return keywords.contains(word.toUpperCase());}@Overridepublic boolean willDoQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {return true;}@Overridepublic boolean willDoUpdate(Executor executor, MappedStatement ms, Object parameter) {return true;}}/*** 達夢數據庫分頁攔截器（不依賴setPriority方法）*/public static class DmMultiLevelPaginationInterceptor extends PaginationInnerInterceptor {public DmMultiLevelPaginationInterceptor() {super(DbType.DM);}@Overridepublic void beforeQuery(Executor executor, MappedStatement ms, Object parameter,RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {// 1. 獲取分頁參數IPage<?> page = ParameterUtils.findPage(parameter).orElse(null);if (page == null || page.getSize() <= 0) {// 無分頁參數時執行默認邏輯super.beforeQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql);return;}// 2. 手動構建無歧義的分頁SQLString originalSql = boundSql.getSql();String paginationSql = buildMultiLevelPaginationSql(originalSql, page);// 3. 替換原始SQL（關鍵步驟）PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);mpBs.sql(paginationSql);// 4. 執行父類邏輯，但禁用其分頁處理（使用RowBounds.DEFAULT）super.beforeQuery(executor, ms, parameter, RowBounds.DEFAULT, resultHandler, boundSql);}/*** 構建僅兩層嵌套的分頁SQL，徹底避免ROW_ID重復*/private String buildMultiLevelPaginationSql(String originalSql, IPage<?> page) {long current = page.getCurrent();long size = page.getSize();long offset = (current - 1) * size;// 清除原始SQL中的分號和多余空格originalSql = originalSql.replaceAll(";|\\s+", " ").trim();// 達夢分頁核心：僅用兩層嵌套，別名絕對唯一return String.format("SELECT * FROM (" +"  SELECT TMP.*, ROWNUM AS RN2 FROM (" +"    SELECT TMP.*, ROWNUM AS RN1 FROM (" +"      %s" +  // 原始查詢"    ) TMP WHERE ROWNUM <= %d" +  // 總條數限制：當前頁末尾"  ) TMP WHERE RN1 > %d" +       // 偏移量：跳過前幾頁"  ORDER BY RN1" +               // 保證排序一致性") TMP WHERE ROWNUM <= %d",      // 每頁條數限制originalSql, current * size, offset, size);}}
}