springboot配置多數據源後mybatis攔截器失效的解決
配置文件是通過springcloudconfig遠程分佈式配置。采用阿裡Druid數據源。並支持一主多從的讀寫分離。分頁組件通過攔截器攔截帶有page後綴的方法名,動態的設置total總數。
1. 解析配置文件初始化數據源
@Configuration public class DataSourceConfiguration { /** * 數據源類型 */ @Value("${spring.datasource.type}") private Class<? extends DataSource> dataSourceType; /** * 主數據源配置 * * @return */ @Bean(name = "masterDataSource", destroyMethod = "close") @Primary @ConfigurationProperties(prefix = "spring.datasource") public DataSource masterDataSource() { DataSource source = DataSourceBuilder.create().type(dataSourceType).build(); return source; } /** * 從數據源配置 * * @return */ @Bean(name = "slaveDataSource0") @ConfigurationProperties(prefix = "spring.slave0") public DataSource slaveDataSource0() { DataSource source = DataSourceBuilder.create().type(dataSourceType).build(); return source; } /** * 從數據源集合 * * @return */ @Bean(name = "slaveDataSources") public List<DataSource> slaveDataSources() { List<DataSource> slaveDataSources = new ArrayList(); slaveDataSources.add(slaveDataSource0()); return slaveDataSources; } }
2. 定義數據源枚舉類型
public enum DataSourceType { master("master", "master"), slave("slave", "slave"); private String type; private String name; DataSourceType(String type, String name) { this.type = type; this.name = name; } public String getType() { return type; } public void setType(String type) { this.type = type; } public String getName() { return name; } public void setName(String name) { this.name = name; } }
3. TheadLocal保存數據源類型
public class DataSourceContextHolder { private static final ThreadLocal<String> local = new ThreadLocal<String>(); public static ThreadLocal<String> getLocal() { return local; } public static void slave() { local.set(DataSourceType.slave.getType()); } public static void master() { local.set(DataSourceType.master.getType()); } public static String getJdbcType() { return local.get(); } public static void clearDataSource(){ local.remove(); } }
4. 自定義sqlSessionProxy
並將數據源填充到DataSourceRoute
@Configuration @ConditionalOnClass({EnableTransactionManagement.class}) @Import({DataSourceConfiguration.class}) public class DataSourceSqlSessionFactory { private Logger logger = Logger.getLogger(DataSourceSqlSessionFactory.class); @Value("${spring.datasource.type}") private Class<? extends DataSource> dataSourceType; @Value("${mybatis.mapper-locations}") private String mapperLocations; @Value("${mybatis.type-aliases-package}") private String aliasesPackage; @Value("${slave.datasource.number}") private int dataSourceNumber; @Resource(name = "masterDataSource") private DataSource masterDataSource; @Resource(name = "slaveDataSources") private List<DataSource> slaveDataSources; @Bean @ConditionalOnMissingBean public SqlSessionFactory sqlSessionFactory() throws Exception { logger.info("======================= init sqlSessionFactory"); SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(roundRobinDataSourceProxy()); PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(); sqlSessionFactoryBean.setMapperLocations(resolver.getResources(mapperLocations)); sqlSessionFactoryBean.setTypeAliasesPackage(aliasesPackage); sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true); return sqlSessionFactoryBean.getObject(); } @Bean(name = "roundRobinDataSourceProxy") public AbstractRoutingDataSource roundRobinDataSourceProxy() { logger.info("======================= init robinDataSourceProxy"); DataSourceRoute proxy = new DataSourceRoute(dataSourceNumber); Map<Object, Object> targetDataSources = new HashMap(); targetDataSources.put(DataSourceType.master.getType(), masterDataSource); if(null != slaveDataSources) { for(int i=0; i<slaveDataSources.size(); i++){ targetDataSources.put(i, slaveDataSources.get(i)); } } proxy.setDefaultTargetDataSource(masterDataSource); proxy.setTargetDataSources(targetDataSources); return proxy; } }
5. 自定義路由
public class DataSourceRoute extends AbstractRoutingDataSource { private Logger logger = Logger.getLogger(DataSourceRoute.class); private final int dataSourceNumber; public DataSourceRoute(int dataSourceNumber) { this.dataSourceNumber = dataSourceNumber; } @Override protected Object determineCurrentLookupKey() { String typeKey = DataSourceContextHolder.getJdbcType(); logger.info("==================== swtich dataSource:" + typeKey); if (typeKey.equals(DataSourceType.master.getType())) { return DataSourceType.master.getType(); }else{ //從數據源隨機分配 Random random = new Random(); int slaveDsIndex = random.nextInt(dataSourceNumber); return slaveDsIndex; } } }
6. 定義切面,dao層定義切面
@Aspect @Component public class DataSourceAop { private Logger logger = Logger.getLogger(DataSourceAop.class); @Before("execution(* com.dbq.iot.mapper..*.get*(..)) || execution(* com.dbq.iot.mapper..*.isExist*(..)) " + "|| execution(* com.dbq.iot.mapper..*.select*(..)) || execution(* com.dbq.iot.mapper..*.count*(..)) " + "|| execution(* com.dbq.iot.mapper..*.list*(..)) || execution(* com.dbq.iot.mapper..*.query*(..))" + "|| execution(* com.dbq.iot.mapper..*.find*(..))|| execution(* com.dbq.iot.mapper..*.search*(..))") public void setSlaveDataSourceType(JoinPoint joinPoint) { DataSourceContextHolder.slave(); logger.info("=========slave, method:" + joinPoint.getSignature().getName()); } @Before("execution(* com.dbq.iot.mapper..*.add*(..)) || execution(* com.dbq.iot.mapper..*.del*(..))" + "||execution(* com.dbq.iot.mapper..*.upDate*(..)) || execution(* com.dbq.iot.mapper..*.insert*(..))" + "||execution(* com.dbq.iot.mapper..*.create*(..)) || execution(* com.dbq.iot.mapper..*.update*(..))" + "||execution(* com.dbq.iot.mapper..*.delete*(..)) || execution(* com.dbq.iot.mapper..*.remove*(..))" + "||execution(* com.dbq.iot.mapper..*.save*(..)) || execution(* com.dbq.iot.mapper..*.relieve*(..))" + "|| execution(* com.dbq.iot.mapper..*.edit*(..))") public void setMasterDataSourceType(JoinPoint joinPoint) { DataSourceContextHolder.master(); logger.info("=========master, method:" + joinPoint.getSignature().getName()); } }
7. 最後在寫庫增加事務管理
@Configuration @Import({DataSourceConfiguration.class}) public class DataSouceTranscation extends DataSourceTransactionManagerAutoConfiguration { private Logger logger = Logger.getLogger(DataSouceTranscation.class); @Resource(name = "masterDataSource") private DataSource masterDataSource; /** * 配置事務管理器 * * @return */ @Bean(name = "transactionManager") public DataSourceTransactionManager transactionManagers() { logger.info("===================== init transactionManager"); return new DataSourceTransactionManager(masterDataSource); } }
8. 在配置文件中增加數據源配置
spring.datasource.name=writedb spring.datasource.url=jdbc:mysql://192.168.0.1/master?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false spring.datasource.username=root spring.datasource.password=1234 spring.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.filters=stat spring.datasource.initialSize=20 spring.datasource.minIdle=20 spring.datasource.maxActive=200 spring.datasource.maxWait=60000 #從庫的數量 slave.datasource.number=1 spring.slave0.name=readdb spring.slave0.url=jdbc:mysql://192.168.0.2/slave?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false spring.slave0.username=root spring.slave0.password=1234 spring.slave0.type=com.alibaba.druid.pool.DruidDataSource spring.slave0.driver-class-name=com.mysql.jdbc.Driver spring.slave0.filters=stat spring.slave0.initialSize=20 spring.slave0.minIdle=20 spring.slave0.maxActive=200 spring.slave0.maxWait=60000
這樣就實現瞭在springcloud框架下的讀寫分離,並且支持多個從庫的負載均衡(簡單的通過隨機分配,也有網友通過算法實現平均分配,具體做法是通過一個線程安全的自增長Integer類型,取餘實現。個人覺得沒大必要。如果有大神有更好的方法可以一起探討。)
Mabatis分頁配置可通過dao層的攔截器對特定方法進行攔截,攔截後添加自己的邏輯代碼,比如計算total等,具體代碼如下(參考瞭網友的代碼,主要是通過@Intercepts註解):
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})}) public class PageInterceptor implements Interceptor { private static final Log logger = LogFactory.getLog(PageInterceptor.class); private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory(); private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory(); private static final ReflectorFactory DEFAULT_REFLECTOR_FACTORY = new DefaultReflectorFactory(); private static String defaultDialect = "mysql"; // 數據庫類型(默認為mysql) private static String defaultPageSqlId = ".*Page$"; // 需要攔截的ID(正則匹配) private String dialect = ""; // 數據庫類型(默認為mysql) private String pageSqlId = ""; // 需要攔截的ID(正則匹配) @Override public Object intercept(Invocation invocation) throws Throwable { StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY,DEFAULT_REFLECTOR_FACTORY); // 分離代理對象鏈(由於目標類可能被多個攔截器攔截,從而形成多次代理,通過下面的兩次循環可以分離出最原始的的目標類) while (metaStatementHandler.hasGetter("h")) { Object object = metaStatementHandler.getValue("h"); metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY,DEFAULT_REFLECTOR_FACTORY); } // 分離最後一個代理對象的目標類 while (metaStatementHandler.hasGetter("target")) { Object object = metaStatementHandler.getValue("target"); metaStatementHandler = MetaObject.forObject(object, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY,DEFAULT_REFLECTOR_FACTORY); } Configuration configuration = (Configuration) metaStatementHandler.getValue("delegate.configuration"); if (null == dialect || "".equals(dialect)) { logger.warn("Property dialect is not setted,use default 'mysql' "); dialect = defaultDialect; } if (null == pageSqlId || "".equals(pageSqlId)) { logger.warn("Property pageSqlId is not setted,use default '.*Page$' "); pageSqlId = defaultPageSqlId; } MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement"); // 隻重寫需要分頁的sql語句。通過MappedStatement的ID匹配,默認重寫以Page結尾的MappedStatement的sql if (mappedStatement.getId().matches(pageSqlId)) { BoundSql boundSql = (BoundSql) metaStatementHandler.getValue("delegate.boundSql"); Object parameterObject = boundSql.getParameterObject(); if (parameterObject == null) { throw new NullPointerException("parameterObject is null!"); } else { PageParameter page = (PageParameter) metaStatementHandler .getValue("delegate.boundSql.parameterObject.page"); String sql = boundSql.getSql(); // 重寫sql String pageSql = buildPageSql(sql, page); metaStatementHandler.setValue("delegate.boundSql.sql", pageSql); metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET); metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT); Connection connection = (Connection) invocation.getArgs()[0]; // 重設分頁參數裡的總頁數等 setPageParameter(sql, connection, mappedStatement, boundSql, page); } } // 將執行權交給下一個攔截器 return invocation.proceed(); } /** * @param sql * @param connection * @param mappedStatement * @param boundSql * @param page */ private void setPageParameter(String sql, Connection connection, MappedStatement mappedStatement, BoundSql boundSql, PageParameter page) { // 記錄總記錄數 String countSql = "select count(0) from (" + sql + ") as total"; PreparedStatement countStmt = null; ResultSet rs = null; try { countStmt = connection.prepareStatement(countSql); BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), boundSql.getParameterObject()); Field metaParamsField = ReflectUtil.getFieldByFieldName(boundSql, "metaParameters"); if (metaParamsField != null) { try { MetaObject mo = (MetaObject) ReflectUtil.getValueByFieldName(boundSql, "metaParameters"); ReflectUtil.setValueByFieldName(countBS, "metaParameters", mo); } catch (SecurityException | NoSuchFieldException | IllegalArgumentException | IllegalAccessException e) { // TODO Auto-generated catch block logger.error("Ignore this exception", e); } } Field additionalField = ReflectUtil.getFieldByFieldName(boundSql, "additionalParameters"); if (additionalField != null) { try { Map<String, Object> map = (Map<String, Object>) ReflectUtil.getValueByFieldName(boundSql, "additionalParameters"); ReflectUtil.setValueByFieldName(countBS, "additionalParameters", map); } catch (SecurityException | NoSuchFieldException | IllegalArgumentException | IllegalAccessException e) { // TODO Auto-generated catch block logger.error("Ignore this exception", e); } } setParameters(countStmt, mappedStatement, countBS, boundSql.getParameterObject()); rs = countStmt.executeQuery(); int totalCount = 0; if (rs.next()) { totalCount = rs.getInt(1); } page.setTotalCount(totalCount); int totalPage = totalCount / page.getPageSize() + ((totalCount % page.getPageSize() == 0) ? 0 : 1); page.setTotalPage(totalPage); } catch (SQLException e) { logger.error("Ignore this exception", e); } finally { try { if (rs != null){ rs.close(); } } catch (SQLException e) { logger.error("Ignore this exception", e); } try { if (countStmt != null){ countStmt.close(); } } catch (SQLException e) { logger.error("Ignore this exception", e); } } } /** * 對SQL參數(?)設值 * * @param ps * @param mappedStatement * @param boundSql * @param parameterObject * @throws SQLException */ private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException { ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql); parameterHandler.setParameters(ps); } /** * 根據數據庫類型,生成特定的分頁sql * * @param sql * @param page * @return */ private String buildPageSql(String sql, PageParameter page) { if (page != null) { StringBuilder pageSql = new StringBuilder(); pageSql = buildPageSqlForMysql(sql,page); return pageSql.toString(); } else { return sql; } } /** * mysql的分頁語句 * * @param sql * @param page * @return String */ public StringBuilder buildPageSqlForMysql(String sql, PageParameter page) { StringBuilder pageSql = new StringBuilder(100); String beginrow = String.valueOf((page.getCurrentPage() - 1) * page.getPageSize()); pageSql.append(sql); pageSql.append(" limit " + beginrow + "," + page.getPageSize()); return pageSql; } @Override public Object plugin(Object target) { if (target instanceof StatementHandler) { return Plugin.wrap(target, this); } else { return target; } } @Override public void setProperties(Properties properties) { } }
這裡碰到一個比較有趣的問題,就是sql如果是foreach參數,在攔截後無法註入。需要加入以下代碼才可以(有得資料上隻提到重置metaParameters)。
Field metaParamsField = ReflectUtil.getFieldByFieldName(boundSql, "metaParameters"); if (metaParamsField != null) { try { MetaObject mo = (MetaObject) ReflectUtil.getValueByFieldName(boundSql, "metaParameters"); ReflectUtil.setValueByFieldName(countBS, "metaParameters", mo); } catch (SecurityException | NoSuchFieldException | IllegalArgumentException | IllegalAccessException e) { // TODO Auto-generated catch block logger.error("Ignore this exception", e); } } Field additionalField = ReflectUtil.getFieldByFieldName(boundSql, "additionalParameters"); if (additionalField != null) { try { Map<String, Object> map = (Map<String, Object>) ReflectUtil.getValueByFieldName(boundSql, "additionalParameters"); ReflectUtil.setValueByFieldName(countBS, "additionalParameters", map); } catch (SecurityException | NoSuchFieldException | IllegalArgumentException | IllegalAccessException e) { // TODO Auto-generated catch block logger.error("Ignore this exception", e); } }
讀寫分離倒是寫好瞭,但是發現增加瞭mysql一主多從的讀寫分離後,此分頁攔截器直接失效。
最後分析原因是因為,我們在做主從分離時,自定義瞭SqlSessionFactory,導致此攔截器沒有註入。
在上面第4步中,DataSourceSqlSessionFactory中註入攔截器即可,具體代碼如下
通過註解引入攔截器類:
@Import({DataSourceConfiguration.class,PageInterceptor.class})
註入攔截器
@Autowired private PageInterceptor pageInterceptor;
SqlSessionFactoryBean中設置攔截器
sqlSessionFactoryBean.setPlugins(newInterceptor[]{pageInterceptor});
這裡碰到一個坑,就是設置plugins時必須在sqlSessionFactoryBean.getObject()之前。
SqlSessionFactory在生成的時候就會獲取plugins,並設置到Configuration中,如果在之後設置則不會註入。
可跟蹤源碼看到:
sqlSessionFactoryBean.getObject()
public SqlSessionFactory getObject() throws Exception { if (this.sqlSessionFactory == null) { afterPropertiesSet(); } return this.sqlSessionFactory; }
public void afterPropertiesSet() throws Exception { notNull(dataSource, "Property 'dataSource' is required"); notNull(sqlSessionFactoryBuilder, "Property 'sqlSessionFactoryBuilder' is required"); state((configuration == null && configLocation == null) || !(configuration != null && configLocation != null), "Property 'configuration' and 'configLocation' can not specified with together"); this.sqlSessionFactory = buildSqlSessionFactory(); }
buildSqlSessionFactory()
if (!isEmpty(this.plugins)) { for (Interceptor plugin : this.plugins) { configuration.addInterceptor(plugin); if (LOGGER.isDebugEnabled()) { LOGGER.debug("Registered plugin: '" + plugin + "'"); } } }
最後貼上正確的配置代碼(DataSourceSqlSessionFactory代碼片段)
@Bean @ConditionalOnMissingBean public SqlSessionFactory sqlSessionFactory() throws Exception { logger.info("======================= init sqlSessionFactory"); SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setPlugins(new Interceptor[]{pageInterceptor}); sqlSessionFactoryBean.setDataSource(roundRobinDataSourceProxy()); PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver(); sqlSessionFactoryBean.setMapperLocations(resolver.getResources(mapperLocations)); sqlSessionFactoryBean.setTypeAliasesPackage(aliasesPackage); SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBean.getObject(); sqlSessionFactory.getConfiguration().setMapUnderscoreToCamelCase(true); return sqlSessionFactory; }
以上為個人經驗,希望能給大傢一個參考,也希望大傢多多支持WalkonNet。
推薦閱讀:
- SpringBoot+Mybatis-Plus實現mysql讀寫分離方案的示例代碼
- MyBatis自定義SQL攔截器示例詳解
- springboot mybatis調用多個數據源引發的錯誤問題
- 關於Mybatis實體別名支持通配符掃描問題小結
- sql查詢返回值使用map封裝多個key和value實例