springboot配置多数据源后mybatis拦截器失效怎么办
发表于：2025-02-07 作者：千家信息网编辑
千家信息网最后更新 2025年02月07日，这篇文章给大家分享的是有关springboot配置多数据源后mybatis拦截器失效怎么办的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。配置文件是通过springcloud
千家信息网最后更新 2025年02月07日springboot配置多数据源后mybatis拦截器失效怎么办
这篇文章给大家分享的是有关springboot配置多数据源后mybatis拦截器失效怎么办的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。
配置文件是通过springcloudconfig远程分布式配置。采用阿里Druid数据源。并支持一主多从的读写分离。分页组件通过拦截器拦截带有page后缀的方法名，动态的设置total总数。
1. 解析配置文件初始化数据源
@Configurationpublic class DataSourceConfiguration { /** * 数据源类型 */ @Value("${spring.datasource.type}") private Class 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 slaveDataSources() { List 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 local = new ThreadLocal(); public static ThreadLocal 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 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 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 targetDataSources = new HashMap(); targetDataSources.put(DataSourceType.master.getType(), masterDataSource); if(null != slaveDataSources) { for(int i=0; i5. 自定义路由
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@Componentpublic 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=writedbspring.datasource.url=jdbc:mysql://192.168.0.1/master?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=falsespring.datasource.username=rootspring.datasource.password=1234spring.datasource.type=com.alibaba.druid.pool.DruidDataSourcespring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.filters=statspring.datasource.initialSize=20spring.datasource.minIdle=20spring.datasource.maxActive=200spring.datasource.maxWait=60000#从库的数量slave.datasource.number=1spring.slave0.name=readdbspring.slave0.url=jdbc:mysql://192.168.0.2/slave?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=falsespring.slave0.username=rootspring.slave0.password=1234spring.slave0.type=com.alibaba.druid.pool.DruidDataSourcespring.slave0.driver-class-name=com.mysql.jdbc.Driverspring.slave0.filters=statspring.slave0.initialSize=20spring.slave0.minIdle=20spring.slave0.maxActive=200spring.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 map = (Map) 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 map = (Map) 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@ConditionalOnMissingBeanpublic 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;}感谢各位的阅读！关于"springboot配置多数据源后mybatis拦截器失效怎么办"这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
数据
数据源
拦截器
配置
代码
类型
参数
数据库
文件
方法
目标
代理
分配
怎么办
事务
事务管理
内容
切面
多个
对象
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
数据库三层安全机制
呼吁重视网络安全的话
怎么更换服务器名称
云南省软件开发人员工资标准
量化交易软件开发盒装
三星神经网络技术
数据库信息查询
和网络安全工程师相关的专业
Vision软件开发环境
PB数据库sql
辽宁专升本网络技术专业
软件开发合同模式
云南省网络安全竞赛
软件开发皆往翰诺科技
南通个人软件开发价钱
文明重启有多少服务器
虚拟化服务器厂家
甘肃中小学生网络安全教育视频
奉贤区制造网络技术要多少钱
计算机三级网络技术时间6
excel 服务器组件
物联网服务器代码
达梦数据库 dm7
数据库ps和vb哪个好学
天津软件开发岗待遇
面试网络安全自我介绍
海诚信息影像软件开发
大型网游服务器多少钱
幼儿园网络安全周图片
网络安全工作由谁负责
