公司的项目,有很多个版本,然后某些版本的同名表的表结构不同,但是我们数据库环境只有一套。于是就想有没有办法,不改代码就实现某些物理表名自动加后缀的功能。这样当我们给生成环境打包时就关闭加后缀功能,本地开发时就打开。
由于项目框架中同时用了hibernate5和mybatis,所以两个工具都要分别处理表名替换(其实如果druid能做,直接在druid里面做表名替换是最简单的,但是没搞通):
先看hibernate5怎么做:
一,先配置自定义的物理表名获取策略
<!-- 配置hibernate5的SessionFactory -->
<bean name="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${dialect}</prop>
<prop key="hibernate.show_sql">false</prop>
<prop key="hibernate.format_sql">true</prop>
</props>
</property>
<!-- 配置物理表名获取策略 -->
<property name="physicalNamingStrategy" ref="customPhysicalNamingStrategy"/>
<property name="packagesToScan" value="com.xxx.**.model"/>
</bean>
<!-- 自定义物理表名获取策略 -->
<bean id="customPhysicalNamingStrategy" class="com.xxx.config.TableSuffixPhysicalNamingStrategy"/>
二,自定义策略类
package com.xxx.config;
import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;
import org.springframework.beans.factory.annotation.Value;
import java.util.Objects;
/**
* 给某些表一个指定的后缀
*/
public class TableSuffixPhysicalNamingStrategy extends PhysicalNamingStrategyStandardImpl {
/**
* 表名
*/
@Value("${physicalNamingStrategy.tableName:}")
private String tableName;
/**
* 后缀
*/
@Value("${physicalNamingStrategy.suffix:}")
private String suffix;
@Value("${hibernate.addTableSuffix:false}")
private Boolean addTableSuffix;
@Override
public Identifier toPhysicalTableName(final Identifier identifier, final JdbcEnvironment jdbcEnv) {
if (identifier == null) {
return null;
}
String name = identifier.getText();
if (Objects.nonNull(addTableSuffix) && addTableSuffix.booleanValue()) {
String[] tableNameArr = tableName.split(",");
for (String desc : tableNameArr) {
if (name.equals(desc)) {
name = name + suffix;
break;
}
}
}
return Identifier.toIdentifier(name);
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getSuffix() {
return suffix;
}
public void setSuffix(String suffix) {
this.suffix = suffix;
}
}
三,增加配置
add_table_suffix=true
physicalNamingStrategy.tableName=T_TEST
physicalNamingStrategy.suffix=_XX
再看mybatis怎么做:
原理:利用mysql的interceptor拦截sql,然后用druid的解析表名工具类,把表名解析出来,然后给表名加后缀,再重新生成新的sql,设置到框架中去。
注意: druid的解析sql工具,碰到with这种语法就会报错;于是我听同事推荐又试了一下jsqlparser,这个工具又有个问题是如果省略了as,它解析不了。
最后还是用的druid,同时把报错catch了,解析不了就算了,反正是开发阶段用的功能。如果一定要用,我觉得可以把两个工具串起来,一个不行,就换一个解析。
一,先定义一个druid解析sql中表名的工具类:
package com.xxx.framework.base.util;
import com.alibaba.druid.sql.ast.expr.SQLPropertyExpr;
import com.alibaba.druid.sql.ast.statement.SQLExprTableSource;
import com.alibaba.druid.sql.visitor.SQLASTVisitorAdapter;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
/**
* @Author: 遛猫达人
* @Description: TODO
* @DateTime: 2022/1/25 17:21
**/
@Component
public class AutoAddTableSuffixASTVisitor extends SQLASTVisitorAdapter {
/**
* 表名
*/
@Value("${physicalNamingStrategy.tableName:}")
private String tableName;
/**
* 后缀
*/
@Value("${physicalNamingStrategy.suffix:}")
private String suffix;
@Override
public boolean visit(SQLExprTableSource x) {
SQLPropertyExpr tableExpr = (SQLPropertyExpr) (x.getExpr());
String name = tableExpr.getName();
String[] tableNameArr = tableName.split(",");
for (String desc : tableNameArr) {
if (name.equalsIgnoreCase(desc)) {
tableExpr.setName(name + suffix);
break;
}
}
return true;
}
}
二,再定义一个mybatis的interceptor用于拦截修改sql:
package com.xxx.framework.base.interceptor;
import cn.hutool.core.util.ReflectUtil;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.util.JdbcUtils;
import com.xxx.framework.base.util.AutoAddTableSuffixASTVisitor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.*;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import java.sql.Connection;
import java.util.List;
import java.util.Objects;
import java.util.Properties;
/**
* 自动给表名加后缀
*
* @author:遛猫达人
* @date:2022-01-26 23:16:22
*/
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
public class AutoAddTableSuffixInterceptor implements Interceptor {
private final Logger logger = Logger.getLogger(AutoAddTableSuffixInterceptor.class);
@Autowired
private AutoAddTableSuffixASTVisitor autoAddTableSuffixASTVisitor;
@Value("${hibernate.addTableSuffix:false}")
private Boolean addTableSuffix;
/**
* jdbcurl,用于判定数据库的类型
*/
@Value("${jdbc_url:}")
private String jdbcUrl;
@Override
public Object intercept(Invocation invocation) throws Throwable {
if (Objects.nonNull(addTableSuffix) && addTableSuffix.booleanValue()) {
//获取StatementHandler,默认是RoutingStatementHandler
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
BoundSql boundSql = statementHandler.getBoundSql();
String sql = boundSql.getSql();
//把sql中的表名替换了
String dbType = JdbcUtils.getDbType(jdbcUrl, null);
try {
List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
for (SQLStatement stmt : stmtList) {
stmt.accept(autoAddTableSuffixASTVisitor);
}
sql = SQLUtils.toSQLString(stmtList, dbType);
ReflectUtil.setFieldValue(boundSql, "sql", sql);
} catch (Throwable e) {
logger.warn(e.getMessage());
}
}
Object returnValue = invocation.proceed();
return returnValue;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
三,配置mysql的sqlsessionFactory时把interceptor加入进去:
<bean id="autoAddTableSuffixInterceptor"
class="com.xxx.framework.base.interceptor.AutoAddTableSuffixInterceptor"/>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 实例化sqlSessionFactory时需要使用上述配置好的数据源以及SQL映射文件 -->
<property name="dataSource" ref="dataSource"/>
<property name="plugins">
<list>
<ref bean="autoAddTableSuffixInterceptor"/>
</list>
</property>
<!-- 扫描所有的myBatis XML配置 -->
<property name="mapperLocations">
<list>
<value>classpath*:/com/xxx/**/mapping/**/*-sqlmap.xml</value>
</list>
</property>
<!-- 加载myBatis 配置信息 -->
<property name="configLocation" value="classpath:/mybatis/mybatis-config.xml"/>
<property name="databaseIdProvider" ref="databaseIdProvider"/>
</bean>
4,增加系统变量配置,用于控制是否开启加表后缀,以及给哪些表加后缀:
add_table_suffix=true
physicalNamingStrategy.tableName=T_TEST
physicalNamingStrategy.suffix=_XX