前段时间做过 mysql 的项目适配达梦数据库，记录一下遇到的主要问题
一、配置修改
1.达梦数据库驱动
<dependency>
<groupId>com.dameng</groupId>
<artifactId>DmJdbcDriver18</artifactId>
<version>8.1.1.193</version>
</dependency>
同样的，修改数据源中的驱动类名
spring.datasource.dynamic.datasource.master.driver-class-name=dm.jdbc.driver.DmDriver
spring.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.DruidDataSource
spring.datasource.dynamic.datasource.slave.driver-class-name=dm.jdbc.driver.DmDriver
spring.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 的值。
/**
* mybatisPlus配置类
*
* @author HetFrame
*/
@Slf4j
@Configuration
public class MybatisPlusConfig {
@Bean
public DmFieldCustomizer getDmFieldCustomizer() {
return new DmFieldCustomizer();
}
/**
* 在mybatisPlus加载进spring容器之前自定义某些配置
*
* @author HetFrame
*/
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<Class<?>> classList = new ArrayList<>();
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
//找到所有实体类的class
Resource[] resources = resolver.getResources("classpath*:com/**/entity/**/*.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<Field> 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数据库");
}
}
}
}
达梦数据库某些关键字例如 audit，在 mysql 中不是关键字的，需要手动设置
@TableField(`audit`)。
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 语法是正确的。
<dependency>
<groupId>org.jeasy</groupId>
<artifactId>easy-random-core</artifactId>
<version>4.3.0</version>
</dependency>
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;
/**
* 简单mapper测试
* <p>根据mapper中定义的方法，mock接口入参，通过事务执行sql并回滚，达到测试sql的目的</p>
*
* @author HetFrame
*/
@Slf4j
@Component
public 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<Map<String, Object>> THREAD_LOCAL = new TransmittableThreadLocal<Map<String, Object>>() {
@Override
protected Map<String, Object> initialValue() {
return new HashMap();
}
@Override
public Map<String, Object> childValue(Map<String, Object> parentValue) {
return new HashMap(parentValue);
}
@Override
public Map<String, Object> copy(Map<String, Object> parentValue) {
return new HashMap(parentValue);
}
};
@Autowired
private List<BaseMapper<?>> 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<Integer>() {
@Override
public Integer getRandomValue() {
Random random = new Random();
return random.nextInt(1000);
}
});
parameters.randomize(BigDecimal.class, new AbstractRandomizer<BigDecimal>() {
@Override
public BigDecimal getRandomValue() {
Random random = new Random();
return BigDecimal.valueOf(random.nextInt(1000));
}
});
//由于部分sql使用String传入数字，所以用数字生成字符串
parameters.randomize(String.class, new AbstractRandomizer<String>() {
@Override
public String getRandomValue() {
Random random = new Random();
return String.valueOf(random.nextInt(1000));
}
});
EasyRandom generator = new EasyRandom(parameters);
//记录执行结果
List<SqlResult> 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();
}
}
/**
* mybatis日志插件
*
* @author HetFrame
*/
@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<SimpleDateFormat> 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();
}
/**
* 获取sql
*/
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) {
}
/**
* 获取完整的sql实体的信息
*
* @see org.apache.ibatis.scripting.defaults.DefaultParameterHandler 参考Mybatis 参数处理
*/
private String formatSql(BoundSql boundSql, org.apache.ibatis.session.Configuration configuration) {
String sql = boundSql.getSql();
List<ParameterMapping> 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;
}
/**
* 美化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。
497
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
