springboot+druid+mybatis多数据源动态切换

最近空闲，做了一个springboot+mybatis多数据源的动态切换！！！

鉴于网上使用注解区分数据库的方式一个区别，通过mybatis的拦截器拦截SQL语句类型调用不同数据库实现读写分离！！！

同时引入了PageHelper进行分页处理

 

例子介绍

为了简化书写SQL，这里用到了tk.mybatis

maven配置

<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>2.1.5</version>
</dependency>

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.2.0</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.22</version>
</dependency>

配置文件

spring:
  application:
    name: demo
  datasource:
    master:
      # 多数据源，读写分离，写库
      url: jdbc:mysql://IP:PORT/DATABASE?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
      username: test
      password: test123
      driver-class-name: com.mysql.cj.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
    slave:
      # 多数据源，读写分离， 读库
      url: jdbc:mysql://IP:PORT/DATABASE?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
      username: test 
      password: test123
      driver-class-name: com.mysql.cj.jdbc.Driver
      type: com.alibaba.druid.pool.DruidDataSource
logging:
  level:
    com.spring.druid.tk.demo.mapper: info
mybatis:
  mapper-locations: classpath:mapper/*.xml

配置信息ip、端口、数据库、用户名和密码换上自己相应的信息即可

数据库信息实例

CREATE TABLE `blog_auth` (
  `id` varchar(32) NOT NULL COMMENT 'id',
  `user_name` varchar(100) NOT NULL COMMENT '用户名',
  `password` varchar(200) NOT NULL COMMENT '密码',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

这样数据库主库的信息就配置好了，我在这里本机上安装了3306（主库）和3307（从库）两个mysql数据库，在主库新建了表和数据后从库也自动有了数据：

主库用于写和更新删除等操作，从库通过该主库中my.ini配置文件的log-bin开启二进制同步并设定要同步的数据库，这里可以自己去百度配置一下，很简单

 

下面开始代码模块废话不多说！！！

文件目录结构

 

多数据源配置

config目录

config目录，配置了druid数据源信息和SqlSessionFactoryBean以及线程池，这里只对前两者进行描述

@Configuration
public class DataSourceConfig {

    @Value("${mybatis.mapper-locations}")
    private String mybatisPath;

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DruidDataSource writeDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DruidDataSource readDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean
    public DynamicDataSource dynamicDataSource(DruidDataSource writeDataSource, DruidDataSource readDataSource) {
        DynamicDataSource source = new DynamicDataSource();
        source.setWriteDataSource(writeDataSource);
        source.setReadDataSource(readDataSource);
        source.setDefaultTargetDataSource(writeDataSource);
        return source;
    }

    @Bean
    public SqlSessionFactoryBean sqlSessionFactory(DynamicDataSource dynamicDataSource,PageInterceptor pageInterceptor, MybatisPlugin dynamicPlugin){
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setLogImpl(StdOutImpl.class);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCallSettersOnNulls(true);
        sqlSessionFactoryBean.setConfiguration(configuration);
        sqlSessionFactoryBean.setDataSource(dynamicDataSource);
        sqlSessionFactoryBean.setTypeAliasesPackage("com.test.test.entity");
        PathMatchingResourcePatternResolver resourceLoader = new PathMatchingResourcePatternResolver();
        Resource[] resources = new Resource[0];
        try {
            resources = resourceLoader.getResources(mybatisPath);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactoryBean.setMapperLocations(resources);
        Interceptor[] plugins = new Interceptor[]{pageInterceptor, dynamicPlugin};
        sqlSessionFactoryBean.setPlugins(plugins);
        return sqlSessionFactoryBean;
    }

    @Bean
    public PageInterceptor pageInterceptor() {
        PageInterceptor interceptor = new PageInterceptor();
        Properties properties = new Properties();
        properties.setProperty("helperDialect", "mysql");
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("params", "true");
        properties.setProperty("autoRuntimeDialect", "true");
        interceptor.setProperties(properties);
        return interceptor;
    }

    @Bean
    public MybatisPlugin dynamicPlugin() {
        MybatisPlugin plugin = new MybatisPlugin();
        return plugin;
    }

    @Bean
    public DynamicDataSourceTransactionManager transactionManager(DynamicDataSource dynamicDataSource) {
        DynamicDataSourceTransactionManager manager = new DynamicDataSourceTransactionManager();
        manager.setDataSource(dynamicDataSource);
        return manager;
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

记住PageHelper包要用5.x以后的，因为之前的PageHelper包进行sql拦截时的intercept方法中return null;拦截器未向下传递导致我的自定义拦截器未生效，爬了很久的坑终于发现！！！

同时重写了sqlSessionFactory方法把拦截配置和数据源装载进去；transactionManager方法实现了事务操作

 

重点来了，mysql拦截器

@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}
)})
@Slf4j
@NoArgsConstructor
public class MybatisPlugin implements Interceptor {

    private static final String REGEX = ".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*";
    private static final Map<String, DynamicDataSourceGlobal> cacheMap = new ConcurrentHashMap();

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        boolean synchronizationActive = TransactionSynchronizationManager.isSynchronizationActive();
        if (!synchronizationActive) {
            Object[] objects = invocation.getArgs();
            MappedStatement ms = (MappedStatement)objects[0];
            DynamicDataSourceGlobal dynamicDataSourceGlobal = null;
            if ((dynamicDataSourceGlobal = (DynamicDataSourceGlobal)cacheMap.get(ms.getId())) == null) {
                if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
                    if (ms.getId().contains("!selectKey")) {
                        dynamicDataSourceGlobal = DynamicDataSourceGlobal.WRITE;
                    } else {
                        BoundSql boundSql = ms.getSqlSource().getBoundSql(objects[1]);
                        String sql = boundSql.getSql().toLowerCase(Locale.CHINA).replaceAll("[\\t\\n\\r]", " ");
                        if (sql.matches(".*insert\\u0020.*|.*delete\\u0020.*|.*update\\u0020.*")) {
                            dynamicDataSourceGlobal = DynamicDataSourceGlobal.WRITE;
                        } else {
                            dynamicDataSourceGlobal = DynamicDataSourceGlobal.READ;
                        }
                    }
                } else {
                    dynamicDataSourceGlobal = DynamicDataSourceGlobal.WRITE;
                }
                log.warn("设置方法[{}] use [{}] Strategy, SqlCommandType [{}]..", new Object[]{ms.getId(), dynamicDataSourceGlobal.name(), ms.getSqlCommandType().name()});
                cacheMap.put(ms.getId(), dynamicDataSourceGlobal);
            }

            DynamicDataSourceHolder.putDataSource(dynamicDataSourceGlobal);
            log.info("-----------------------------------当前数据库为：{}", DynamicDataSourceHolder.getDataSource());
        }

        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        return target instanceof Executor ? Plugin.wrap(target, this) : target;
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

mybatis定义了Interceptor接口，实现对应的方法完成拦截，类名上的注解标注了要拦截的SQL类型

• Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
• ParameterHandler (getParameterObject, setParameters)
• ResultSetHandler (handleResultSets, handleOutputParameters)
• StatementHandler (prepare, parameterize, batch, update, query)

这里的intercept方法进来进行了一个事务判断，如果标注了事务操作，那么不进行拦截走默认数据库即主库，因为从库只用于读，加了事务也没用

如果非事务操作时根据sql的类型去动态决定数据走读库或写库

 

现在我们开始配置多数据源的动态切换

datasource目录

通过Spring了解到要实现多数据源的切换，我们需要继承AbstractRoutingDataSource类并重写determineCurrentLookupKey方法，上干货

@NoArgsConstructor
@Data
@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {
    /**
     * 写数据库
     */
    private Object writeDataSource;
    /**
     * 读数据库
     */
    private Object readDataSource;

    @Override
    public void afterPropertiesSet() {
        if (writeDataSource == null) {
            throw new IllegalArgumentException("Property 'writeDataSource' is required");
        } else {
            this.setDefaultTargetDataSource(this.writeDataSource);
            Map<Object, Object> targetDataSources = new HashMap();
            targetDataSources.put(DynamicDataSourceGlobal.WRITE.name(), this.writeDataSource);
            if (this.readDataSource != null) {
                targetDataSources.put(DynamicDataSourceGlobal.READ.name(), this.readDataSource);
            }

            this.setTargetDataSources(targetDataSources);
            super.afterPropertiesSet();
        }
    }

    @Override
    protected Object determineCurrentLookupKey() {
        DynamicDataSourceGlobal dynamicDataSourceGlobal = DynamicDataSourceHolder.getDataSource();
        log.info("-------------------------当前使用的数据库为：{}------------------------", DynamicDataSourceHolder.getDataSource());
        return dynamicDataSourceGlobal != null && dynamicDataSourceGlobal != DynamicDataSourceGlobal.WRITE ? DynamicDataSourceGlobal.READ.name() : DynamicDataSourceGlobal.WRITE.name();
    }
}

afterPropertiesSet方法，初始化bean的时候执行，可以针对某个具体的bean进行配置。

public enum DynamicDataSourceGlobal {

    READ,
    WRITE;

    private DynamicDataSourceGlobal() {
    }

}

public final class DynamicDataSourceHolder {

    private static final ThreadLocal<DynamicDataSourceGlobal> holder = new ThreadLocal();

    private DynamicDataSourceHolder() {
    }

    public static void putDataSource(DynamicDataSourceGlobal dataSource) {
        holder.set(dataSource);
    }

    public static DynamicDataSourceGlobal getDataSource() {
        return (DynamicDataSourceGlobal)holder.get();
    }

    public static void clearDataSource() {
        holder.remove();
    }

}

@NoArgsConstructor
public class DynamicDataSourceTransactionManager extends DataSourceTransactionManager implements Serializable {

    private static final long serialVersionUID = -3092901060855087254L;

    @Override
    protected void doBegin(Object transaction, TransactionDefinition definition) {
        boolean readOnly = definition.isReadOnly();
        if (readOnly) {
            DynamicDataSourceHolder.putDataSource(DynamicDataSourceGlobal.READ);
        } else {
            DynamicDataSourceHolder.putDataSource(DynamicDataSourceGlobal.WRITE);
        }

        super.doBegin(transaction, definition);
    }

    @Override
    protected void doCleanupAfterCompletion(Object transaction) {
        super.doCleanupAfterCompletion(transaction);
        DynamicDataSourceHolder.clearDataSource();
    }
}

 

业务操作

在进行事务操作时，判断使用哪个数据库。现在主从库就配置完成了，接下来就是entity，mapper，service和controller的配置，代码如下

entity

@Data
@NoArgsConstructor
@AllArgsConstructor
public class BlogAuth {

    private String id;

    private String userName;

    private String password;

    private Date createTime;

    private Date updateTime;

    private String remark;

}

mapper

@Mapper
@Repository
public interface BlogAuthMapper extends tk.mybatis.mapper.common.Mapper<BlogAuth> {
    BlogAuth qryBlogAuth(@Param("id")String id);
}

service

public interface Test {

    boolean test() throws Exception;
}

@Service
public class TestImpl implements Test {

    @Autowired
    private BlogAuthMapper blogAuthMapper;

    @Transactional(propagation = Propagation.REQUIRED)
    @Override
    public boolean test() throws Exception{
        BlogAuth blogAuth = blogAuthMapper.qryBlogAuth("1");
        BlogAuth blogAuth1 = new BlogAuth();
        blogAuth1.setId(UUIDUtil.getUUID());
        blogAuth1.setPassword("1231234");
        blogAuth1.setUserName("11李四11");
        blogAuth1.setCreateTime(new Date());
        int i = blogAuthMapper.insert(blogAuth1);
  、    throw new RuntimeException("故意抛的异常");
        return true;
    }
}

这里故意抛了异常去检查主从事务是否生效

@RestController
@RequestMapping("/test")
public class web {

    @Autowired
    private Test test;

    @RequestMapping(value = "/test",method = RequestMethod.POST)
    public boolean test(@RequestBody BlogAuth blogAuth) throws Exception{
        test.test();
        return true;
    }
}

至此一个简单的多数据源动态切换的项目就完成了，跑起来试试效果吧小伙伴们！！！