springboot-multisource

项目中经常会出现需要同时连接两个数据源的情况，这里基于MyBatis来配置两个数据源，并演示如何切换不同的数据源。

通过自定义注解+AOP的方式，来简化这种数据源的切换操作。

<properties>

    <project.build.sourceEncoding>UTF-</project.build.sourceEncoding>

    <project.reporting.outputEncoding>UTF-</project.reporting.outputEncoding>

    <java.version>1.8</java.version>

    <druid.version>1.1.</druid.version>

    <mysql-connector.version>8.0.-dmr</mysql-connector.version>

    <mybatis-plus.version>2.1.</mybatis-plus.version>

    <mybatisplus-spring-boot-starter.version>1.0.</mybatisplus-spring-boot-starter.version>

</properties>

<dependencies>

    <dependency>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-aop</artifactId>

    </dependency>

    <dependency>

        <groupId>mysql</groupId>

        <artifactId>mysql-connector-java</artifactId>

        <version>${mysql-connector.version}</version>

        <scope>runtime</scope>

    </dependency>

    <dependency>

        <groupId>com.alibaba</groupId>

        <artifactId>druid</artifactId>

        <version>${druid.version}</version>

    </dependency>

    <!-- MyBatis plus增强和springboot的集成-->

    <dependency>

        <groupId>com.baomidou</groupId>

        <artifactId>mybatis-plus</artifactId>

        <version>${mybatis-plus.version}</version>

    </dependency>

    <dependency>

        <groupId>com.baomidou</groupId>

        <artifactId>mybatisplus-spring-boot-starter</artifactId>

        <version>${mybatisplus-spring-boot-starter.version}</version>

    </dependency>

    <dependency>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-test</artifactId>

        <scope>test</scope>

    </dependency>

    <dependency>

        <groupId>org.hamcrest</groupId>

        <artifactId>hamcrest-all</artifactId>

        <version>1.3</version>

        <scope>test</scope>

    </dependency>

</dependencies>

初始化数据库

这里我们需要创建两个数据库，初始化脚本如下：

-- -------------------------------------以下是pos业务库开始-------------------------------------------

CREATE DATABASE IF NOT EXISTS pos default charset utf8 COLLATE utf8_general_ci;

SET FOREIGN_KEY_CHECKS=;

USE pos;

-- 后台管理用户表

DROP TABLE IF EXISTS `t_user`;

CREATE TABLE `t_user` (

  `id`                        INT() PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',

  `username`                  VARCHAR() NOT NULL COMMENT '账号',

  `name`                      VARCHAR() DEFAULT '' COMMENT '名字',

  `password`                  VARCHAR() DEFAULT '' COMMENT '密码',

  `salt`                      VARCHAR() DEFAULT '' COMMENT 'md5密码盐',

  `phone`                     VARCHAR() DEFAULT '' COMMENT '联系电话',

  `tips`                      VARCHAR() COMMENT '备注',

  `state`                     TINYINT() DEFAULT  COMMENT '状态 1:正常 2:禁用',

  `created_time`              DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

  `updated_time`              DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'

) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8 COMMENT='后台管理用户表';

-- 下面是pos数据库中的插入数据

INSERT INTO `t_user` VALUES (,'admin','系统管理员','','www', '', '系统管理员', , '2017-12-12 09:46:12', '2017-12-12 09:46:12');

INSERT INTO `t_user` VALUES (,'aix','张三','','eee', '', '', , '2017-12-12 09:46:12', '2017-12-12 09:46:12');

-- -------------------------------------以下biz业务库开始-------------------------------------------

CREATE DATABASE IF NOT EXISTS biz default charset utf8 COLLATE utf8_general_ci;

SET FOREIGN_KEY_CHECKS=;

USE biz;

-- 后台管理用户表

DROP TABLE IF EXISTS `t_user`;

CREATE TABLE `t_user` (

  `id`                        INT() PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',

  `username`                  VARCHAR() NOT NULL COMMENT '账号',

  `name`                      VARCHAR() DEFAULT '' COMMENT '名字',

  `password`                  VARCHAR() DEFAULT '' COMMENT '密码',

  `salt`                      VARCHAR() DEFAULT '' COMMENT 'md5密码盐',

  `phone`                     VARCHAR() DEFAULT '' COMMENT '联系电话',

  `tips`                      VARCHAR() COMMENT '备注',

  `state`                     TINYINT() DEFAULT  COMMENT '状态 1:正常 2:禁用',

  `created_time`              DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

  `updated_time`              DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'

) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8 COMMENT='后台管理用户表';

-- 下面是biz数据库中的插入数据

INSERT INTO `t_user` VALUES (,'admin1','系统管理员','','www', '', '系统管理员', , '2017-12-12 09:46:12', '2017-12-12 09:46:12');

INSERT INTO `t_user` VALUES (,'aix1','张三','','eee', '', '', , '2017-12-12 09:46:12', '2017-12-12 09:46:12');

可以看到我创建了两个数据库pos和biz，同时还初始化了用户表，并分别插入两条初始数据。注意用户名数据不相同。

配置文件

接下来修改application.yml配置文件，如下：

###################  自定义配置  ###################

xncoding:

  muti-datasource-open: true #是否开启多数据源(true/false)

###################  mybatis-plus配置  ###################

mybatis-plus:

  mapper-locations: classpath*:com/xncoding/pos/common/dao/repository/mapping/*.xml

  typeAliasesPackage: >

    com.xncoding.pos.common.dao.entity

  global-config:

    id-type: 0  # 0:数据库ID自增   1:用户输入id  2:全局唯一id(IdWorker)  3:全局唯一ID(uuid)

    db-column-underline: false

    refresh-mapper: true

  configuration:

    map-underscore-to-camel-case: true

    cache-enabled: true #配置的缓存的全局开关

    lazyLoadingEnabled: true #延时加载的开关

    multipleResultSetsEnabled: true #开启的话，延时加载一个属性时会加载该对象全部属性，否则按需加载属性

#默认数据源

spring:

  datasource:

    url: jdbc:mysql://127.0.0.1:3306/pos?useSSL=false&autoReconnect=true&tinyInt1isBit=false&useUnicode=true&characterEncoding=utf8

    username: root

    password: 123456

#多数据源

biz:

  datasource:

    url: jdbc:mysql://127.0.0.1:3306/biz?useSSL=false&autoReconnect=true&tinyInt1isBit=false&useUnicode=true&characterEncoding=utf8

    username: root

    password: 123456

添加了一个自定义配置项muti-datasource-open，用来控制是否开启多数据源支持。这个配置项后面会用到。接下来定义MyBatis的配置，最后定义了两个MySQL数据库的连接信息，一个是pos库，一个是biz库。

动态切换数据源

这里通过Spring的AOP技术实现数据源的动态切换。

多数据源的常量类：

public interface DSEnum {

    String DATA_SOURCE_CORE = "dataSourceCore";         //核心数据源

    String DATA_SOURCE_BIZ = "dataSourceBiz";            //其他业务的数据源

}

datasource的上下文，用来存储当前线程的数据源类型：

 public class DataSourceContextHolder {

     private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

     /**

      * @param dataSourceType 数据库类型

      * @Description: 设置数据源类型

      */

     public static void setDataSourceType(String dataSourceType) {

         contextHolder.set(dataSourceType);

     }

     /**

      * @Description: 获取数据源类型

      */

     public static String getDataSourceType() {

         return contextHolder.get();

     }

     public static void clearDataSourceType() {

         contextHolder.remove();

     }

 }

定义动态数据源，继承AbstractRoutingDataSource ：

 public class DynamicDataSource extends AbstractRoutingDataSource {

     @Override

     protected Object determineCurrentLookupKey() {

         return DataSourceContextHolder.getDataSourceType();

     }

 }

接下来自定义一个注解，用来在Service方法上面注解使用哪个数据源：

 @Inherited

 @Retention(RetentionPolicy.RUNTIME)

 @Target(ElementType.METHOD)

 public @interface DataSource {

     String name() default "";

 }

最后，最核心的AOP类定义：

 @Aspect

 @Component

 @ConditionalOnProperty(prefix = "code", name = "muti-datasource-open", havingValue = "true")

 public class MultiSourceExAop implements Ordered {

     private static final Logger LOGGER = LoggerFactory.getLogger(MultiSourceExAop.class);

     @Pointcut(value = "@annotation(com.code.springbootmultisource.common.annotation.DataSource)")

     private void cut() {}

     @Around("cut()")

     public Object around(ProceedingJoinPoint proceedingJoinPoint) throws Throwable {

         Signature signature = proceedingJoinPoint.getSignature();

         MethodSignature methodSignature = null;

         if (!(signature instanceof MethodSignature)) {

             throw new IllegalArgumentException("该注解只能用于方法");

         }

         methodSignature = (MethodSignature) signature;

         Object target = proceedingJoinPoint.getTarget();

         Method currentMethod = target.getClass().getMethod(methodSignature.getName(), methodSignature.getParameterTypes());

         DataSource dataSource = currentMethod.getAnnotation(DataSource.class);

         if (dataSource != null) {

             DataSourceContextHolder.setDataSourceType(dataSource.name());

             LOGGER.info("数据源设置为: " + dataSource.name());

         } else {

             DataSourceContextHolder.setDataSourceType(DSEnum.DATA_SOURCE_CORE);

             LOGGER.info("数据源设置为: " + DSEnum.DATA_SOURCE_CORE);

         }

         try {

             return proceedingJoinPoint.proceed();

         } finally {

             LOGGER.debug("清空数据源信息！");

             DataSourceContextHolder.clearDataSourceType();

         }

     }

     /**

      * aop的顺序要早于spring的事务

      */

     @Override

     public int getOrder() {

         return ;

     }

 }

这里使用到了注解@ConditionalOnProperty，只有当我的配置文件中muti-datasource-open=true的时候注解才会生效。

另外还有一个要注意的地方，就是order，aop的顺序一定要早于spring的事务，这里我将它设置成1，后面你会看到我将spring事务顺序设置成2。

配置类

首先有两个属性类：

DruidProperties 连接池的属性类
MutiDataSourceProperties biz数据源的属性类

 @Component

 @ConfigurationProperties(prefix = "spring.datasource")

 public class DruidProperties {

     private String url;

     private String username;

     private String password;

     private String driverClassName = "com.mysql.cj.jdbc.Driver";

     private Integer initialSize = ;

     private Integer minIdle = ;

     private Integer maxActive = ;

     private Integer maxWait = ;

     private Boolean removeAbandoned = true;

     private Integer removeAbandonedTimeout = ;

     private Integer timeBetweenEvictionRunsMillis = ;

     private Integer minEvictableIdleTimeMillis = ;

     private String validationQuery = "SELECT 'x'";

     private Boolean testWhileIdle = true;

     private Boolean testOnBorrow = false;

     private Boolean testOnReturn = false;

     private Boolean poolPreparedStatements = true;

     private Integer maxPoolPreparedStatementPerConnectionSize = ;

     private String filters = "stat";

     public void config(DruidDataSource dataSource) {

         dataSource.setDbType(JdbcConstants.MYSQL);

         dataSource.setUrl(url);

         dataSource.setUsername(username);

         dataSource.setPassword(password);

         dataSource.setDriverClassName(driverClassName);

         dataSource.setInitialSize(initialSize);     // 定义初始连接数

         dataSource.setMinIdle(minIdle);             // 最小空闲

         dataSource.setMaxActive(maxActive);         // 定义最大连接数

         dataSource.setMaxWait(maxWait);             // 获取连接等待超时的时间

         dataSource.setRemoveAbandoned(removeAbandoned); // 超过时间限制是否回收

         dataSource.setRemoveAbandonedTimeout(removeAbandonedTimeout); // 超过时间限制多长

         // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒

         dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);

         // 配置一个连接在池中最小生存的时间，单位是毫秒

         dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);

         // 用来检测连接是否有效的sql，要求是一个查询语句

         dataSource.setValidationQuery(validationQuery);

         // 申请连接的时候检测

         dataSource.setTestWhileIdle(testWhileIdle);

         // 申请连接时执行validationQuery检测连接是否有效，配置为true会降低性能

         dataSource.setTestOnBorrow(testOnBorrow);

         // 归还连接时执行validationQuery检测连接是否有效，配置为true会降低性能

         dataSource.setTestOnReturn(testOnReturn);

         // 打开PSCache，并且指定每个连接上PSCache的大小

         dataSource.setPoolPreparedStatements(poolPreparedStatements);

         dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);

         // 属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有：

         // 监控统计用的filter:stat

         // 日志用的filter:log4j

         // 防御SQL注入的filter:wall

         try {

             dataSource.setFilters(filters);

         } catch (SQLException e) {

             e.printStackTrace();

         }

     }

     public String getUrl() {

         return url;

     }

     public void setUrl(String url) {

         this.url = url;

     }

     public String getUsername() {

         return username;

     }

     public void setUsername(String username) {

         this.username = username;

     }

     public String getPassword() {

         return password;

     }

     public void setPassword(String password) {

         this.password = password;

     }

     public String getDriverClassName() {

         return driverClassName;

     }

     public void setDriverClassName(String driverClassName) {

         this.driverClassName = driverClassName;

     }

     public Integer getInitialSize() {

         return initialSize;

     }

     public void setInitialSize(Integer initialSize) {

         this.initialSize = initialSize;

     }

     public Integer getMinIdle() {

         return minIdle;

     }

     public void setMinIdle(Integer minIdle) {

         this.minIdle = minIdle;

     }

     public Integer getMaxActive() {

         return maxActive;

     }

     public void setMaxActive(Integer maxActive) {

         this.maxActive = maxActive;

     }

     public Integer getMaxWait() {

         return maxWait;

     }

     public void setMaxWait(Integer maxWait) {

         this.maxWait = maxWait;

     }

     public Integer getTimeBetweenEvictionRunsMillis() {

         return timeBetweenEvictionRunsMillis;

     }

     public void setTimeBetweenEvictionRunsMillis(Integer timeBetweenEvictionRunsMillis) {

         this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;

     }

     public Integer getMinEvictableIdleTimeMillis() {

         return minEvictableIdleTimeMillis;

     }

     public void setMinEvictableIdleTimeMillis(Integer minEvictableIdleTimeMillis) {

         this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;

     }

     public String getValidationQuery() {

         return validationQuery;

     }

     public void setValidationQuery(String validationQuery) {

         this.validationQuery = validationQuery;

     }

     public Boolean getTestWhileIdle() {

         return testWhileIdle;

     }

     public void setTestWhileIdle(Boolean testWhileIdle) {

         this.testWhileIdle = testWhileIdle;

     }

     public Boolean getTestOnBorrow() {

         return testOnBorrow;

     }

     public void setTestOnBorrow(Boolean testOnBorrow) {

         this.testOnBorrow = testOnBorrow;

     }

     public Boolean getTestOnReturn() {

         return testOnReturn;

     }

     public void setTestOnReturn(Boolean testOnReturn) {

         this.testOnReturn = testOnReturn;

     }

     public Boolean getPoolPreparedStatements() {

         return poolPreparedStatements;

     }

     public void setPoolPreparedStatements(Boolean poolPreparedStatements) {

         this.poolPreparedStatements = poolPreparedStatements;

     }

     public Integer getMaxPoolPreparedStatementPerConnectionSize() {

         return maxPoolPreparedStatementPerConnectionSize;

     }

     public void setMaxPoolPreparedStatementPerConnectionSize(Integer maxPoolPreparedStatementPerConnectionSize) {

         this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;

     }

     public String getFilters() {

         return filters;

     }

     public void setFilters(String filters) {

         this.filters = filters;

     }

     public Boolean getRemoveAbandoned() {

         return removeAbandoned;

     }

     public void setRemoveAbandoned(Boolean removeAbandoned) {

         this.removeAbandoned = removeAbandoned;

     }

     public Integer getRemoveAbandonedTimeout() {

         return removeAbandonedTimeout;

     }

     public void setRemoveAbandonedTimeout(Integer removeAbandonedTimeout) {

         this.removeAbandonedTimeout = removeAbandonedTimeout;

     }

 }

 @Component

 @ConfigurationProperties(prefix = "biz.datasource")

 public class MutiDataSourceProperties {

     private String url;

     private String username;

     private String password;

     public void config(DruidDataSource dataSource) {

         dataSource.setUrl(url);

         dataSource.setUsername(username);

         dataSource.setPassword(password);

     }

     public String getUrl() {

         return url;

     }

     public void setUrl(String url) {

         this.url = url;

     }

     public String getUsername() {

         return username;

     }

     public void setUsername(String username) {

         this.username = username;

     }

     public String getPassword() {

         return password;

     }

     public void setPassword(String password) {

         this.password = password;

     }

 }

然后定义配置类：

 @Configuration

 @EnableTransactionManagement(order = )

 @MapperScan(basePackages = {"com.code.springbootmultisource.common.dao.repository"})

 public class MybatisPlusConfig {

     @Autowired

     DruidProperties druidProperties;

     @Autowired

     MutiDataSourceProperties mutiDataSourceProperties;

     /**

      * 核心数据源

      */

     private DruidDataSource coreDataSource() {

         DruidDataSource dataSource = new DruidDataSource();

         druidProperties.config(dataSource);

         return dataSource;

     }

     /**

      * 另一个数据源

      */

     private DruidDataSource bizDataSource() {

         DruidDataSource dataSource = new DruidDataSource();

         druidProperties.config(dataSource);

         mutiDataSourceProperties.config(dataSource);

         return dataSource;

     }

     /**

      * 单数据源连接池配置

      */

     @Bean

     @ConditionalOnProperty(prefix = "code", name = "muti-datasource-open", havingValue = "false")

     public DruidDataSource singleDatasource() {

         return coreDataSource();

     }

     /**

      * 多数据源连接池配置

      */

     @Bean

     @ConditionalOnProperty(prefix = "code", name = "muti-datasource-open", havingValue = "true")

     public DynamicDataSource mutiDataSource() {

         DruidDataSource coreDataSource = coreDataSource();

         DruidDataSource bizDataSource = bizDataSource();

         try {

             coreDataSource.init();

             bizDataSource.init();

         } catch (SQLException sql) {

             sql.printStackTrace();

         }

         DynamicDataSource dynamicDataSource = new DynamicDataSource();

         HashMap<Object, Object> hashMap = new HashMap<>();

         hashMap.put(DSEnum.DATA_SOURCE_CORE, coreDataSource);

         hashMap.put(DSEnum.DATA_SOURCE_BIZ, bizDataSource);

         dynamicDataSource.setTargetDataSources(hashMap);

         dynamicDataSource.setDefaultTargetDataSource(coreDataSource);

         return dynamicDataSource;

     }

 }

实体类

 @TableName(value = "t_user")

 public class User extends Model<User> {

     private static final long serialVersionUID = 1L;

     @TableId(value="id", type= IdType.AUTO)

     private Integer id;

     private String username;

     private String name;

     private String password;

     private String salt;

     private String phone;

     private String tips;

     private Integer state;

     private Date createdTime;

     private Date updatedTime;

     public static long getSerialVersionUID() {

         return serialVersionUID;

     }

     public Integer getId() {

         return id;

     }

     public void setId(Integer id) {

         this.id = id;

     }

     public String getUsername() {

         return username;

     }

     public void setUsername(String username) {

         this.username = username;

     }

     public String getName() {

         return name;

     }

     public void setName(String name) {

         this.name = name;

     }

     public String getPassword() {

         return password;

     }

     public void setPassword(String password) {

         this.password = password;

     }

     public String getSalt() {

         return salt;

     }

     public void setSalt(String salt) {

         this.salt = salt;

     }

     public String getPhone() {

         return phone;

     }

     public void setPhone(String phone) {

         this.phone = phone;

     }

     public String getTips() {

         return tips;

     }

     public void setTips(String tips) {

         this.tips = tips;

     }

     public Integer getState() {

         return state;

     }

     public void setState(Integer state) {

         this.state = state;

     }

     public Date getCreatedTime() {

         return createdTime;

     }

     public void setCreatedTime(Date createdTime) {

         this.createdTime = createdTime;

     }

     public Date getUpdatedTime() {

         return updatedTime;

     }

     public void setUpdatedTime(Date updatedTime) {

         this.updatedTime = updatedTime;

     }

     @Override

     protected Serializable pkVal() {

         return this.id;

     }

 }

定义DAO

 public interface UserMapper extends BaseMapper<User> {

 }

定义Service

 @Service

 public class UserService {

     @Resource

     private UserMapper userMapper;

     public User findById(Integer id) {

         return userMapper.selectById(id);

     }

     @DataSource(name = DSEnum.DATA_SOURCE_BIZ)

     public User findById1(Integer id) {

         return userMapper.selectById(id);

     }

 }

这里唯一要说明的就是我在方法findById1()上面增加了注解@DataSource(name = DSEnum.DATA_SOURCE_BIZ)，这样这个方法就会访问biz数据库。

注意，不加注解就会访问默认数据库pos。

测试

最后编写一个简单的测试，我只测试findById()方法和findById1()方法，看它们是否访问的是不同的数据源。

 @RunWith(SpringRunner.class)

 @SpringBootTest

 public class SpringbootMultisourceApplicationTests {

     private static final Logger LOGGER = LoggerFactory.getLogger(SpringbootMultisourceApplicationTests.class);

     @Resource

     private UserService userService;

     @Test

     public void contextLoads() {

         User user = userService.findById();

         LOGGER.info("核心数据库user = " + user.getUsername());

         User user1 = userService.findById1();

         LOGGER.info("biz数据库user = " + user1.getUsername());

     }

 }

显示结果：

核心数据库user = admin

数据源设置为: dataSourceBiz

biz数据库user = admin