前段时间做过Mysql的项目适配达梦数据库，记录一下遇到的主要问题

一、配置修改

1.达梦数据库驱动

                    com.dameng            DmJdbcDriver18            8.1.1.193        

同样的，修改数据源中的驱动类名

spring.datasource.dynamic.datasource.master.driver-class-name=dm.jdbc.driver.DmDriverspring.datasource.dynamic.datasource.master.url=spring.datasource.dynamic.datasource.master.username=spring.datasource.dynamic.datasource.master.passWord=spring.datasource.dynamic.datasource.slave.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.dynamic.datasource.slave.driver-class-name=dm.jdbc.driver.DmDriverspring.datasource.dynamic.datasource.slave.url=spring.datasource.dynamic.datasource.slave.username=spring.datasource.dynamic.datasource.slave.password=

2.分页插件

用的mybatis分页插件com.GitHub.pagehelper.PageHelper，所以修改为oracle

pagehelper.helper-dialect=oracle

3.xml文件

我们项目需要同时兼容mysql和达梦，所以建了另一个application-dm.properties，用于使用达梦数据库时的配置。
对于存放sql的xml文件，复制一份用于达梦数据库使用，并修改配置

mybatis-plus.configuration.mapper-localtions=classpath:/mapper/dm**.xml

二、SQL适配

1.关键字列名

mysql中我们使用反引号来区分列名和关键字；但在达梦数据库中，使用双引号来区分。而如果创建实体类在注解@TableField中使用了反引号例如@TableField(`range`)，这种我们改了的话，mysql就用不了，不改达梦就用不了。所以从代码入手，在启动时用反射修改值。

刚好mybatisPlus有个接口MybatisPlusPropertiesCustomizer用于在读取properties之后进行一些自定义操作，我们可以利用这一点修改所有实体类中的@TableField的值。

@Slf4j@Configurationpublic class MybatisPlusConfig {    @Bean    public DmFieldCustomizer getDmFieldCustomizer() {        return new DmFieldCustomizer();    }        public static class DmFieldCustomizer implements MybatisPlusPropertiesCustomizer {        public DmFieldCustomizer() {            log.info("加载DmFieldCustomizer...");        }        @SneakyThrows        @Override        public void customize(MybatisPlusProperties properties) {            //使用达梦数据库            if (Arrays.toString(properties.getMapperLocations()).contains("dm")) {                log.info("使用达梦数据库");                //实体类的class                List> classList = new ArrayList<>();                PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();                //找到所有实体类的class                Resource[] resources = resolver.getResources("classpath*:comentity*.class");                for (Resource res : resources) {                    // 先获取resource的元信息，然后获取class元信息，最后得到 class 全路径                    String clsName = new SimpleMetadataReaderFactory().getMetadataReader(res).getClaSSMetadata().getClassName();                    // 通过名称加载                    Class clazz = Class.forName(clsName);                    classList.add(clazz);                }                classList.forEach(e -> {                    List list = TableInfoHelper.getAllFields(e);                    list.forEach(field -> {                        TableField tableField = field.getAnnotation(TableField.class);                        String metaColName;                        if (tableField != null && StringUtils.isNotBlank(metaColName = tableField.value()) && metaColName.contains("`")) {String newColName = metaColName.replace("`", "\"");InvocationHandler invocationHandler = Proxy.getInvocationHandler(tableField);try {    Field memberValues = invocationHandler.getClass().getDeclaredField("memberValues");    memberValues.setAccessible(true);    Map memberValuesMap = (Map) memberValues.get(invocationHandler);    memberValuesMap.put("value", newColName);    log.info("将实体类映射字段{}修改为{}", metaColName, newColName);} catch (NoSuchFieldException | IllegalAccessException exception) {    throw new RuntimeException(exception);}                        }                    });                });            } else {                log.info("使用mysql数据库");            }        }    }}

2.group by不能查询含多个值的列

例如 select * from user group by age;在mysql中可以通过select @@global.sql_mode;去掉sql_mode中ONLY_FULL_GROUP_BY来实现查询。

在达梦数据库中，需要修改 dm.ini 的 compatible_mode 参数为 4。

三、验证mapper sql合法性

因为sql很多，不好测试，直接上去跑项目，可能跑几步就掉，得改了重新部署，所以利用easy-random随机生成入参去跑sql，保证sql语法是正确的。

                    org.jeasy            easy-random-core            4.3.0        

import cn.hutool.core.exceptions.ExceptionUtil;import cn.hutool.core.io.FileUtil;import cn.hutool.core.lang.func.VoidFunc0;import cn.hutool.poi.excel.BigExcelWriter;import cn.hutool.poi.excel.ExcelUtil;import com.baomidou.mybatisplus.core.MybatisParameterHandler;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import lombok.extern.slf4j.Slf4j;import org.apache.ibatis.executor.statement.StatementHandler;import org.apache.ibatis.mapping.BoundSql;import org.apache.ibatis.mapping.ParameterMapping;import org.apache.ibatis.mapping.ParameterMode;import org.apache.ibatis.plugin.*;import org.apache.ibatis.reflection.MetaObject;import org.apache.ibatis.session.ResultHandler;import org.apache.ibatis.type.TypeHandlerReGIStry;import org.apache.poi.ss.SpreadsheetVersion;import org.jeasy.random.EasyRandom;import org.jeasy.random.EasyRandomParameters;import org.jeasy.random.randomizers.AbstractRandomizer;import org.springframework.aop.framework.AopProxyUtils;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.core.Ordered;import org.springframework.core.ResolvableType;import org.springframework.stereotype.Component;import org.springframework.transaction.TransactionStatus;import org.springframework.transaction.support.TransactionCallbackWithoutResult;import org.springframework.transaction.support.TransactionTemplate;import org.springframework.util.ReflectionUtils;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.math.BigDecimal;import java.sql.Statement;import java.text.SimpleDateFORMat;import java.util.*;import java.util.regex.Matcher;import java.util.stream.Collectors;import java.util.stream.IntStream;@Slf4j@Componentpublic class SimpleMapperTest {    private static final String OUT_PATH = "D:/SQL执行结果.xlsx";    private static final String PACKAGE_KEYWORD = "my_package";    private static final String SQL_KEY = "mybatis_sql";    public static final ThreadLocal> THREAD_LOCAL = new TransmittableThreadLocal>() {        @Override        protected Map initialValue() {            return new HashMap();        }        @Override        public Map childValue(Map parentValue) {            return new HashMap(parentValue);        }        @Override        public Map copy(Map parentValue) {            return new HashMap(parentValue);        }    };    @Autowired    private List> baseMapperList;    @Autowired    private TransactionTemplate transactionTemplate;    public void testMapperSql() {        if (FileUtil.exist(OUT_PATH)) {            throw new BusinessException("已存在文件:" + OUT_PATH);        }        doWithRollback(                () -> {                    //初始化随机参数生成器                    EasyRandomParameters parameters = new EasyRandomParameters();                    parameters.stringLengthRange(3, 10);                    parameters.collectionSizeRange(5, 5);                    parameters.setRandomizationDepth(3);                    parameters.randomize(Integer.class, new AbstractRandomizer() {                        @Override                        public Integer getRandomValue() {Random random = new Random();return random.nextInt(1000);                        }                    });                    parameters.randomize(BigDecimal.class, new AbstractRandomizer() {                        @Override                        public BigDecimal getRandomValue() {Random random = new Random();return BigDecimal.valueOf(random.nextInt(1000));                        }                    });                    //由于部分sql使用String传入数字，所以用数字生成字符串                    parameters.randomize(String.class, new AbstractRandomizer() {                        @Override                        public String getRandomValue() {Random random = new Random();return String.valueOf(random.nextInt(1000));                        }                    });                    EasyRandom generator = new EasyRandom(parameters);                    //记录执行结果                    List result = new ArrayList<>();                    try {                        //遍历所有mapper，填充随机参数后执行方法                        baseMapperList.forEach(e -> {Class realClass = null;Class[] classes = AopProxyUtils.proxiedUserInterfaces(e);if (classes.length > 0) {    realClass = classes[0];}Package p = realClass.getPackage();//只执行my_package包下的mapper方法if (p != null && p.getName().contains(PACKAGE_KEYWORD)) {    //mapper所有方法    for (Method method : realClass.getDeclaredMethods()) {        Class[] paramClass = method.getParameterTypes();        Object[] params = IntStream.range(0, paramClass.length).mapToObj(i -> {            //方法第i个参数            Class param = paramClass[i];            //集合类参数使用泛型类型创建对象            if (Arrays.asList(param.getInterfaces()).contains(List.class)) {                return generator.objects(ResolvableType.forMethodParameter(method, i).getGeneric(0).toClass(), 2).collect(Collectors.toList());            }            if (Arrays.asList(param.getInterfaces()).contains(Set.class)) {                return generator.objects(ResolvableType.forMethodParameter(method, i).getGeneric(0).toClass(), 2).collect(Collectors.toSet());            }            return generator.nextObject(param);        }).collect(Collectors.toList()).toArray(new Object[]{});        SimpleMapperTest.SqlResult sqlResult = new SimpleMapperTest.SqlResult();        sqlResult.setInterfaceName(realClass.getName());        sqlResult.setMethod(method.getName());        try {            method.invoke(e, params);            sqlResult.setSuccess(true);        } catch (InvocationTargetException exception) {            log.error("执行接口{}方法{}发生异常", realClass.getName(), method.getName());            log.error(exception.getTargetException().getMessage(), exception.getTargetException());            sqlResult.setSuccess(false);            sqlResult.setException(ExceptionUtil.stacktraceToString(exception.getTargetException()));        } catch (Exception exception) {            log.error("执行接口{}方法{}发生异常", realClass.getName(), method.getName());            log.error(exception.getMessage(), exception);            sqlResult.setSuccess(false);            sqlResult.setException(ExceptionUtil.stacktraceToString(exception));        }        //SQL拦截器设置sql        sqlResult.setSql((String) THREAD_LOCAL.get().get(SQL_KEY));        THREAD_LOCAL.get().removeValue(SQL_KEY);        if (sqlResult.getException() != null && sqlResult.getException().length() > SpreadsheetVersion.EXCEL2007.getMaxTextLength()) {            sqlResult.setException(sqlResult.getException().substring(0, SpreadsheetVersion.EXCEL2007.getMaxTextLength()));        }        result.add(sqlResult);    }}                        });                    } catch (Exception e) {                        log.error(e.getMessage(), e);                        SimpleMapperTest.SqlResult sqlResult = new SimpleMapperTest.SqlResult();                        sqlResult.setInterfaceName("发生严重错误执行结束");                        sqlResult.setException(ExceptionUtil.stacktraceToString(e).substring(0, SpreadsheetVersion.EXCEL2007.getMaxTextLength()));                        result.add(sqlResult);                    }                    BigExcelWriter excelWriter = ExcelUtil.getBigWriter(OUT_PATH);                    excelWriter.write(result);                    excelWriter.setColumnWidth(-1, 35);                    excelWriter.close();                }        );    }        public void doWithRollback(VoidFunc0 function) {        transactionTemplate.execute(new TransactionCallbackWithoutResult() {            @Override            protected void doInTransactionWithoutResult(TransactionStatus status) {                //执行操作                function.callWithRuntimeException();                //回滚                status.setRollbackOnly();            }        });    }    @Data    @AllArgsConstructor    @NoArgsConstructor    private static class SqlResult {        private String interfaceName;        private String method;        private String sql;        private boolean success;        private String exception;    }    @Configuration    public static class MybatisConfig {        @Bean        public MybatisSqlInterceptor mybatisSqlInterceptor() {            return new MybatisSqlInterceptor();        }    }        @Slf4j    @Intercepts({            @Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}),            @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),            @Signature(type = StatementHandler.class, method = "batch", args = {Statement.class})})    public static class MybatisSqlInterceptor implements Interceptor, Ordered {        private static final ThreadLocal DATE_FORMAT_THREAD_LOCAL = ThreadLocal.withInitial(() -> new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"));        private org.apache.ibatis.session.Configuration configuration = null;        @Override        public Object intercept(Invocation invocation) throws Throwable {            Object target = invocation.getTarget();            String sql = this.getSql(target);            THREAD_LOCAL.get().set(SQL_KEY, sql);            return invocation.proceed();        }                private String getSql(Object target) {            try {                StatementHandler statementHandler = (StatementHandler) target;                BoundSql boundSql = statementHandler.getBoundSql();                if (configuration == null) {                    final MybatisParameterHandler parameterHandler = (MybatisParameterHandler) statementHandler.getParameterHandler();                    Field configurationField = ReflectionUtils.findField(parameterHandler.getClass(), "configuration");                    ReflectionUtils.makeAccessible(configurationField);                    this.configuration = (org.apache.ibatis.session.Configuration) configurationField.get(parameterHandler);                }                //替换参数格式化Sql语句，去除换行符                return formatSql(boundSql, configuration);            } catch (Exception e) {                log.error("{}", target);                log.error(e.getMessage(), e);            }            return "";        }        @Override        public Object plugin(Object target) {            return Plugin.wrap(target, this);        }        @Override        public void setProperties(Properties properties) {        }                private String formatSql(BoundSql boundSql, org.apache.ibatis.session.Configuration configuration) {            String sql = boundSql.getSql();            List parameterMappings = boundSql.getParameterMappings();            Object parameterObject = boundSql.getParameterObject();            // 输入sql字符串空判断            if (sql == null || sql.length() == 0) {                return "";            }            if (configuration == null) {                return "";            }            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();            // 美化sql            sql = beautifySql(sql);            if (parameterMappings != null) {                for (ParameterMapping parameterMapping : parameterMappings) {                    if (parameterMapping.getMode() != ParameterMode.OUT) {                        Object value;                        String propertyName = parameterMapping.getProperty();                        if (boundSql.hasAdditionalParameter(propertyName)) {value = boundSql.getAdditionalParameter(propertyName);                        } else if (parameterObject == null) {value = null;                        } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {value = parameterObject;                        } else {MetaObject metaObject = configuration.newMetaObject(parameterObject);value = metaObject.getValue(propertyName);                        }                        String paramValueStr = "";                        if (value instanceof String) {paramValueStr = "'" + value + "'";                        } else if (value instanceof Date) {paramValueStr = "'" + DATE_FORMAT_THREAD_LOCAL.get().format(value) + "'";                        } else {paramValueStr = value + "";                        }                        sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(paramValueStr));                    }                }            }            return sql;        }                private String beautifySql(String sql) {            sql = sql.replaceAll("[\\s\n ]+", " ");            return sql;        }        @Override        public int getOrder() {            return Ordered.HIGHEST_PRECEDENCE;        }    }}

执行完后会生成excel表格，能看到sql是否执行成功，能拿到报错的mapper方法，但是拿不到执行报错的sql。

来源地址：https://blog.csdn.net/qq_40800602/article/details/129297871