读代码有感
程序员文章站
2022-07-12 17:51:00
...
写在前面的话，最近熟悉新项目代码时，发现了很多好玩的东西，在此记录，以备不时之需。
1. 不对SQL搞点事情吗！？
1.1 业务场景
由于业务数据表在初期创建时，是以租户Id为单位划分的，即在查询时，表名中的租户Id是动态生成的，所以在执行sql语句之前要动态将租户Id拼接到sql语句中：
<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
select id, apply_code, preordain_count, preordain_flow_code, expire_time, create_time,
update_time, tenant_id,`status`
from coupon_preordain_{_table_tenant_id}
where id = #{id,jdbcType=BIGINT}
</select>
其中的{_table_tenant_id}的形式并不是mybatis中规定的有效关键字，所以就有了下面的拦截器：
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class MybatisSqlInterceptor implements Interceptor {
private static final Logger logger = LoggerFactory.getLogger(MybatisSqlInterceptor.class);
private DruidProperties druidProperties;
public MybatisSqlInterceptor(DruidProperties druidProperties){
this.druidProperties = druidProperties;
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
//通过MetaObject优雅访问对象的属性，这里是访问statementHandler的属性;：MetaObject是Mybatis提供的一个用于方便、
//优雅访问对象属性的对象，通过它可以简化代码、不需要try/catch各种reflect异常，同时它支持对JavaBean、Collection、Map三种类型对象的操作。
MetaObject metaObject = MetaObject
.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY,
new DefaultReflectorFactory());
//先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，其实现类是BaseStatementHandler，
//然后就到BaseStatementHandler的成员变量mappedStatement
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
//id为执行的mapper方法的全路径名，如com.uv.dao.UserMapper.insertUser
String id = mappedStatement.getId();
//sql语句类型 select、delete、insert、update
String sqlCommandType = mappedStatement.getSqlCommandType().toString();
BoundSql boundSql = statementHandler.getBoundSql();
//获取到原始sql语句
String sql = boundSql.getSql();
if (BlankUtil.isBlank(sql)) {
return invocation.proceed();
}
String sql2Reset = replaceAllSpecialCharacters(sql,null);
if(druidProperties.getShowsql())
logger.info("id:" + id + " sql: " + sql2Reset);
//通过反射修改sql语句
Field field = boundSql.getClass().getDeclaredField("sql");
field.setAccessible(true);
field.set(boundSql, sql2Reset);
// 返回，继续执行
return invocation.proceed();
}
@Override
public Object plugin(Object obj) {
return Plugin.wrap(obj, this);
}
@Override
public void setProperties(Properties arg0) {
// doSomething
}
/**
* 获取sql语句
*
* @param invocation
* @return
*/
private String getSqlByInvocation(Invocation invocation) {
final Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement)args[0];
Object parameterObject = args[1];
BoundSql boundSql = ms.getBoundSql(parameterObject);
return boundSql.getSql();
}
/**
* 包装sql后，重置到invocation中
*
* @param invocation
* @param sql
* @throws SQLException
*/
private void resetSql2Invocation(Invocation invocation, String sql) throws SQLException {
final Object[] args = invocation.getArgs();
MappedStatement statement = (MappedStatement)args[0];
Object parameterObject = args[1];
BoundSql boundSql = statement.getBoundSql(parameterObject);
MappedStatement newStatement = newMappedStatement(statement, new BoundSqlSqlSource(boundSql));
MetaObject msObject = MetaObject.forObject(newStatement, new DefaultObjectFactory(),
new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
msObject.setValue("sqlSource.boundSql.sql", sql);
args[0] = newStatement;
}
private MappedStatement newMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
MappedStatement.Builder builder =
new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
builder.resource(ms.getResource());
builder.fetchSize(ms.getFetchSize());
builder.statementType(ms.getStatementType());
builder.keyGenerator(ms.getKeyGenerator());
if (ms.getKeyProperties() != null && ms.getKeyProperties().length != 0) {
StringBuilder keyProperties = new StringBuilder();
for (String keyProperty : ms.getKeyProperties()) {
keyProperties.append(keyProperty).append(",");
}
keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
builder.keyProperty(keyProperties.toString());
}
builder.timeout(ms.getTimeout());
builder.parameterMap(ms.getParameterMap());
builder.resultMaps(ms.getResultMaps());
builder.resultSetType(ms.getResultSetType());
builder.cache(ms.getCache());
builder.flushCacheRequired(ms.isFlushCacheRequired());
builder.useCache(ms.isUseCache());
return builder.build();
}
private String getOperateType(Invocation invocation) {
final Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement)args[0];
SqlCommandType commondType = ms.getSqlCommandType();
if (commondType.compareTo(SqlCommandType.SELECT) == 0) {
return "select";
}
if (commondType.compareTo(SqlCommandType.INSERT) == 0) {
return "insert";
}
if (commondType.compareTo(SqlCommandType.UPDATE) == 0) {
return "update";
}
if (commondType.compareTo(SqlCommandType.DELETE) == 0) {
return "delete";
}
return null;
}
// 定义一个内部辅助类，作用是包装sq
class BoundSqlSqlSource implements SqlSource {
private BoundSql boundSql;
public BoundSqlSqlSource(BoundSql boundSql) {
this.boundSql = boundSql;
}
@Override
public BoundSql getBoundSql(Object parameterObject) {
return boundSql;
}
}
/**
* 替换特殊字符，包含租户ID、影院ID
* @param sql
* @return
*/
public String replaceAllSpecialCharacters(String sql,String tenantId) {
String newSql = "";
if (sql != null) {
Pattern p = Pattern.compile("\\s{2,}|\t|\r|\n");
Matcher m = p.matcher(sql);
newSql = m.replaceAll(" ");
}
if(BlankUtil.isBlank(tenantId)){
tenantId = ThreadLocalUserInfoUtil.getTenantId();
}
if (!BlankUtil.isBlank(tenantId)) {
newSql = newSql.replaceAll("\\{_table_tenant_id\\}", tenantId);
return newSql;
}else{
return sql;
}
}
}
我认为这个拦截器中最重要的就是如何获取sql，如何修改sql，并将修改后的sql重新设置到数据库连接中，通过这个拦截器可以应用的以后的开发过程中，可以在读取到sql语句之后，执行之前对sql语句进行操作。
下一篇： Effective C++ 10