mybatis通过插件方式实现读写分离
原理:通过自定义mybatis插件,拦截Executor的update和query方法,检查sql中有select就用读的库,其它的用写的库(如果有调用存储过程就另当别论了)
@Intercepts({ @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }), @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class }) })
@Intercepts({ @Signature(type = Executor.class, method = "update", args = { MappedStatement.class, Object.class }), @Signature(type = Executor.class, method = "query", args = { MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class }) }) public class RWDataSourceMybatisPlugin implements Interceptor { private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*"; private static final Map<String, RWDataSourceType> cacheMap = new ConcurrentHashMap<>(); @Override public Object intercept(Invocation invocation) throws Throwable { Object[] objects = invocation.getArgs(); MappedStatement ms = (MappedStatement) objects[0]; RWDataSourceType dataSourceType = null; if ((dataSourceType = cacheMap.get(ms.getId())) == null) { // 读方法 if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) { // !selectKey 为自增id查询主键(SELECT LAST_INSERT_ID() )方法,使用主库 if (ms.getId().contains(SelectKeyGenerator.SELECT_KEY_SUFFIX)) { dataSourceType = RWDataSourceType.WRITE; } else { BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]); String sql = boundSql.getSql().toLowerCase().replaceAll("[\\t\\n\\r]", " "); if (sql.matches(REGEX)) { dataSourceType = RWDataSourceType.WRITE; } else { dataSourceType = RWDataSourceType.READ; } } } else { dataSourceType = RWDataSourceType.WRITE; } cacheMap.put(ms.getId(), dataSourceType); } // 修改当前线程要选择的数据源的key RWDataSourceHolder.putDataSource(dataSourceType); return invocation.proceed(); } @Override public Object plugin(Object target) { if (target instanceof Executor) { return Plugin.wrap(target, this); } else { return target; } } }
附:
MyBatis 允许你在已映射语句执行过程中的某一点进行拦截调用。默认情况下,MyBatis 允许使用插件来拦截的方法调用包括:
- Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
- ParameterHandler (getParameterObject, setParameters)
- ResultSetHandler (handleResultSets, handleOutputParameters)
- StatementHandler (prepare, parameterize, batch, update, query)
spring boot加载插件方式
@Bean public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception { SqlSessionFactoryBean fb = new SqlSessionFactoryBean(); fb.setDataSource(dataSource); fb.setMapperLocations( new PathMatchingResourcePatternResolver().getResources(env.getProperty("mybatis.mapper-locations"))); PageHelper pageHelper = new PageHelper(); Properties props = new Properties(); props.setProperty("reasonable", "true"); props.setProperty("supportMethodsArguments", "true"); props.setProperty("returnPageInfo", "check"); props.setProperty("params", "count=countSql"); pageHelper.setProperties(props); fb.setPlugins(new Interceptor[] { pageHelper, new MybatisSqlInterceptor() }); return fb.getObject(); }
随便开发的一个记录sql的插件
package com.qmtt.config; import java.sql.Connection; import java.util.Properties; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Plugin; import org.apache.ibatis.plugin.Signature; @Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class, Integer.class }) }) public class MybatisSqlInterceptor implements Interceptor { @Override public Object intercept(Invocation invocation) throws Throwable { StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); BoundSql boundSql = statementHandler.getBoundSql(); String sql = boundSql.getSql(); System.out.println(sql); return invocation.proceed(); } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { // String dialect = properties.getProperty("dialect"); } }