前言
最近项目要适配国产化数据库,之前的基于其他种类创建的字段,在国产数据库中如果是关键字会查询报错。(比如达梦数据库中的 comment = = !)
分享一下在网上查了多种方式,以及最后自己的实现方式。与君共勉,欢迎指正。(菜鸟一枚,大神轻喷。)
1. 直接修改 @TableField
@TableField("\"name\"")
private String name;
2. 修改 mapper.xml
<select id="queryTest" resultType="test">
select "comment" from test
</select>
上两种方法是百度搜到的方法,结合项目的情况觉得两种都不适合,因为有可能还要适配别的数据库,主要也懒得排着改 0.0
3. 最后自己的实现方法
利用Mybatis-plus实现多数据源结合拦截器实现
@Configuration
@EnableTransactionManagement //启用事务管理
public class MyBatisConfig {
@Bean
public DatabaseIdProvider databaseIdProvider() {
// 数据库厂商提供者
DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
Properties p = new Properties();
p.setProperty("Oracle", DbType.ORACLE.getDb());
p.setProperty("Mysql", DbType.MYSQL.getDb());
p.setProperty("PostgreSQL", DbType.POSTGRE_SQL.getDb());
p.setProperty("DM", DbType.DM.getDb());
databaseIdProvider.setProperties(p);
return databaseIdProvider;
}
}
@Slf4j
@Component
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class MybatisKeywordCompatibilityInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
/* 先拦截到RoutingStatementHandler,里面有个StatementHandler类型的delegate变量,
* 其实现类是BaseStatementHandler,然后就到BaseStatementHandler的成员变量mappedStatement。
*/
MetaObject metaObject = MetaObject.forObject(
statementHandler,
SystemMetaObject.DEFAULT_OBJECT_FACTORY,
SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,
new DefaultReflectorFactory()
);
MappedStatement ms = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
BoundSql boundSql = statementHandler.getBoundSql();
// 根据 databaseId 获取当前数据库的种类
if(StringUtils.isNotEmpty(ms.getDatabaseId())){
// 达梦数据库 关键字替换
if (DbType.DM.getDb().equals(ms.getDatabaseId())) {
String sql_new = boundSql.getSql().replaceAll(" comment,", "\"comment\",")
.replaceAll(",comment,", ",\"comment\",")
.replaceAll(",comment ", ",\"comment\"")
.replaceAll(" comment ", "\"comment\"")
//
.replaceAll(" COMMENT,", "\"COMMENT\",")
.replaceAll(",COMMENT,", ",\"COMMENT\",")
.replaceAll(",COMMENT ", ",\"COMMENT\"")
.replaceAll(" COMMENT ", "\"COMMENT\"")
.replaceAll(" object,", "\"object\",")
.replaceAll(",object,", ",\"object\",")
.replaceAll(",object ", ",\"object\"")
.replaceAll(" object ", "\"object\"")
.replaceAll(" OBJECT,", "\"OBJECT\",")
.replaceAll(",OBJECT,", ",\"OBJECT\",")
.replaceAll(",OBJECT ", ",\"OBJECT\"")
.replaceAll(" OBJECT ", "\"OBJECT\"");
// 后续其他关键字在此处继续 replaceAll
// 。。。。。
PluginUtils.mpBoundSql(boundSql).sql(sql_new);
}
// 其他数据库种类的关键字兼容在这里更新
/*else if(DbType.DM.getDb().equals(ms.getDatabaseId())){
}*/
}
return invocation.proceed();
}
}
<select id="queryTest" resultType="test" databaseId="dm">
select "comment" from test
</select>
后续再适配其他数据库也可以解决,这是目前想好的解决方案,后更好的方案再继续更新