业务场景
- 公司APP需要将主模块拆分成多个APP给代理商运营
- 不同代理商代理的APP产生的数据需根据对应公司进行区分
- 公司总数据库需同步管理所有代理商运营的数据
设计思想
- 设计在最小修改原则保证产品业务无大变动只对表进行新增COMPAN_ID字段进行区分
- 综合以上需求场景产生的思路决定采用低耦合、高复用进行架构设计
- 通过MyBatis拦截器Sql进行处理，采用类自定义注解+反射的形式
代码实现
创建自定义注解 HmwCompanyAnnotation
@Target({ElementType.METHOD,ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
public @interface HmwCompanyAnnotation {
/**
* 公司ID(暂未启用)
* @return
*/
String CompanyId() default "";
/**
* 别名（针对复杂查询备用处理属性）
* @return
*/
String Alias() default "";
}
MyBatis的Mapper的实现示例
接口方法上添加 自定义注解@HmwCompanyAnnotation()
@Mapper
public interface SmsCodeDao {
/**
* 新增
*
* @param smsCode
* @return
*/
@Options(useGeneratedKeys = true, keyProperty = "smsCodeId" , keyColumn = "SMS_CODE_ID")
@Insert("insert into hmw_sms_code(SMS_TYPE, ... 省略字段..., COMPANY_ID) " +
" values(#{smsType},...省略字段... ,#{companyId})")
int save(SmsCode smsCode);
/**
* 查询用户每天发送的验证码数量
*
* @param sendTel 手机号
* @return
*/
@HmwCompanyAnnotation()
@Select(" select " +
" count(0) as count_num " +
" from hmw_sms_code" +
" where " +
" SEND_TEL = #{sendTel} " +
" and TO_DAYS(NOW()) = TO_DAYS(SEND_TIME) ")
Long findTelTodayCount(String sendTel );
/**
* 根据短信类型查询是否已使用获取已过期
*
* @param sendTel 手机号
* @param sendCode 验证码
* @param smsType 短信类型
* @param nowTime 当前服务器时间(请用服务器New出来的时间 不要用NOW())
* @return
*/
@HmwCompanyAnnotation()
@Select(" select " +
" count(0) as count_num " +
" from " +
" hmw_sms_code " +
" where " +
" SEND_TEL = #{sendTel,jdbcType=VARCHAR} " +
" and SEND_CODE = #{sendCode,jdbcType=VARCHAR} " +
" and SMS_STATE = 'N' " +
" and SMS_TYPE = #{smsType,jdbcType=VARCHAR} " +
" and SEND_TIME >= DATE_SUB(#{sendTime},INTERVAL 5 MINUTE) ")
Long findSmsCodeByTel(String sendTel , String sendCode , String smsType , Date nowTime);
}
MyBatis 拦截器具体实现
import com.hmw.annotation.HmwCompanyAnnotation;
import com.hmw.base.Const;
import com.hmw.utils.common.ReflectHelper;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.Properties;
/**
* 功能：Mybatis 拦截器
* 说明：拦截SQL执行前的语句 根据自定义注解对方法进行公司查询
*
* 开发：Bruce.Liu Create by 2018-03-12 20:15
*/
@Component
@Intercepts(
{
@Signature(
type = StatementHandler.class,
method = "prepare",
args = {
Connection.class ,
Integer.class
}
)
}
)
public class CompanySqlInterceptor implements Interceptor {
@Value("${base.config.companyId}")
private String COMPANY_ID;
@Override
public Object intercept(Invocation invocation) throws Throwable {
try{
if(invocation.getTarget() instanceof RoutingStatementHandler){
RoutingStatementHandler statementHandler = (RoutingStatementHandler)invocation.getTarget();
StatementHandler delegate = (StatementHandler) ReflectHelper.getFieldValue(statementHandler, "delegate");
BoundSql boundSql = delegate.getBoundSql();
MappedStatement mappedStatement = (MappedStatement) ReflectHelper.getFieldValue(delegate, "mappedStatement");
Class<?> classType = Class.forName(mappedStatement.getId().substring(0,mappedStatement.getId().lastIndexOf(".")));
String mName = mappedStatement.getId().substring(mappedStatement.getId().lastIndexOf(".") + 1 ,mappedStatement.getId().length());
for(Method method : classType.getDeclaredMethods()){
if(method.isAnnotationPresent(HmwCompanyAnnotation.class) && mName.equals(method.getName()) )
{
HmwCompanyAnnotation companyAnnotation = method.getAnnotation(HmwCompanyAnnotation.class);
String sql = boundSql.getSql();
if( mappedStatement.getSqlCommandType().toString().equals(Const.SELECT) ||
mappedStatement.getSqlCommandType().toString().equals(Const.UPDATE) ||
mappedStatement.getSqlCommandType().toString().equals(Const.DELETE )){
if(companyAnnotation.Alias().equals("")){
sql = sql + " and COMPANY_ID = '"+ COMPANY_ID +"' ";
} else {
sql = sql + " and "+ companyAnnotation.Alias()+".COMPANY_ID = '" + COMPANY_ID +"' ";
}
} else if( mappedStatement.getSqlCommandType().toString().equals(Const.INSERT)){
// System.err.println("Insert 实现已移到Service层处理了");
}
//System.err.println("执行后SQL:"+sql);
ReflectHelper.setFieldValue(boundSql, "sql", sql);
break;
}
}
}
} catch (Exception e){
e.printStackTrace();
}
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
if (target instanceof StatementHandler) {
return Plugin.wrap(target,this);
} else {
return target;
}
}
@Override
public void setProperties(Properties properties) {
}
}
- 利用MyBatis拦截器@Signature对底层StatementHandler.class的 prepare方法进行拦截处理
- 在执行JDBC的SQL脚本之前对sql进行统一拼接处理
- 自定义注解标示出需要进行拦截处理的方法，通过反射机制获取方法以及SqlCommonType 然后作出相应的逻辑处理
