springboot-multisource
项目中经常会出现需要同时连接两个数据源的情况，这里基于MyBatis来配置两个数据源，并演示如何切换不同的数据源。
通过自定义注解+AOP的方式，来简化这种数据源的切换操作。
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<druid.version>1.1.2</druid.version>
<mysql-connector.version>8.0.7-dmr</mysql-connector.version>
<mybatis-plus.version>2.1.8</mybatis-plus.version>
<mybatisplus-spring-boot-starter.version>1.0.5</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=0; USE pos; -- 后台管理用户表 DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `id` INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID', `username` VARCHAR(32) NOT NULL COMMENT '账号', `name` VARCHAR(16) DEFAULT '' COMMENT '名字', `password` VARCHAR(128) DEFAULT '' COMMENT '密码', `salt` VARCHAR(64) DEFAULT '' COMMENT 'md5密码盐', `phone` VARCHAR(32) DEFAULT '' COMMENT '联系电话', `tips` VARCHAR(255) COMMENT '备注', `state` TINYINT(1) DEFAULT 1 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=1 DEFAULT CHARSET=utf8 COMMENT='后台管理用户表'; -- 下面是pos数据库中的插入数据 INSERT INTO `t_user` VALUES (1,'admin','系统管理员','123456','www', '17890908889', '系统管理员', 1, '2017-12-12 09:46:12', '2017-12-12 09:46:12'); INSERT INTO `t_user` VALUES (2,'aix','张三','123456','eee', '17859569358', '', 1, '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=0; USE biz; -- 后台管理用户表 DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `id` INT(11) PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID', `username` VARCHAR(32) NOT NULL COMMENT '账号', `name` VARCHAR(16) DEFAULT '' COMMENT '名字', `password` VARCHAR(128) DEFAULT '' COMMENT '密码', `salt` VARCHAR(64) DEFAULT '' COMMENT 'md5密码盐', `phone` VARCHAR(32) DEFAULT '' COMMENT '联系电话', `tips` VARCHAR(255) COMMENT '备注', `state` TINYINT(1) DEFAULT 1 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=1 DEFAULT CHARSET=utf8 COMMENT='后台管理用户表'; -- 下面是biz数据库中的插入数据 INSERT INTO `t_user` VALUES (1,'admin1','系统管理员','123456','www', '17890908889', '系统管理员', 1, '2017-12-12 09:46:12', '2017-12-12 09:46:12'); INSERT INTO `t_user` VALUES (2,'aix1','张三','123456','eee', '17859569358', '', 1, '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的上下文，用来存储当前线程的数据源类型：
1 public class DataSourceContextHolder { 2 3 private static final ThreadLocal<String> contextHolder = new ThreadLocal<>(); 4 5 /** 6 * @param dataSourceType 数据库类型 7 * @Description: 设置数据源类型 8 */ 9 public static void setDataSourceType(String dataSourceType) { 10 contextHolder.set(dataSourceType); 11 } 12 13 /** 14 * @Description: 获取数据源类型 15 */ 16 public static String getDataSourceType() { 17 return contextHolder.get(); 18 } 19 20 public static void clearDataSourceType() { 21 contextHolder.remove(); 22 } 23 }
定义动态数据源，继承AbstractRoutingDataSource ：
1 public class DynamicDataSource extends AbstractRoutingDataSource { 2 3 @Override 4 protected Object determineCurrentLookupKey() { 5 return DataSourceContextHolder.getDataSourceType(); 6 } 7 }
接下来自定义一个注解，用来在Service方法上面注解使用哪个数据源：
1 @Inherited 2 @Retention(RetentionPolicy.RUNTIME) 3 @Target(ElementType.METHOD) 4 public @interface DataSource { 5 6 String name() default ""; 7 }
最后，最核心的AOP类定义：
1 @Aspect 2 @Component 3 @ConditionalOnProperty(prefix = "code", name = "muti-datasource-open", havingValue = "true") 4 public class MultiSourceExAop implements Ordered { 5 6 private static final Logger LOGGER = LoggerFactory.getLogger(MultiSourceExAop.class); 7 8 @Pointcut(value = "@annotation(com.code.springbootmultisource.common.annotation.DataSource)") 9 private void cut() {} 10 11 @Around("cut()") 12 public Object around(ProceedingJoinPoint proceedingJoinPoint) throws Throwable { 13 Signature signature = proceedingJoinPoint.getSignature(); 14 MethodSignature methodSignature = null; 15 if (!(signature instanceof MethodSignature)) { 16 throw new IllegalArgumentException("该注解只能用于方法"); 17 } 18 methodSignature = (MethodSignature) signature; 19 Object target = proceedingJoinPoint.getTarget(); 20 Method currentMethod = target.getClass().getMethod(methodSignature.getName(), methodSignature.getParameterTypes()); 21 22 DataSource dataSource = currentMethod.getAnnotation(DataSource.class); 23 if (dataSource != null) { 24 DataSourceContextHolder.setDataSourceType(dataSource.name()); 25 LOGGER.info("数据源设置为: " + dataSource.name()); 26 } else { 27 DataSourceContextHolder.setDataSourceType(DSEnum.DATA_SOURCE_CORE); 28 LOGGER.info("数据源设置为: " + DSEnum.DATA_SOURCE_CORE); 29 } 30 try { 31 return proceedingJoinPoint.proceed(); 32 } finally { 33 LOGGER.debug("清空数据源信息！"); 34 DataSourceContextHolder.clearDataSourceType(); 35 } 36 } 37 38 /** 39 * aop的顺序要早于spring的事务 40 */ 41 @Override 42 public int getOrder() { 43 return 1; 44 } 45 }
这里使用到了注解@ConditionalOnProperty，只有当我的配置文件中muti-datasource-open=true的时候注解才会生效。
另外还有一个要注意的地方，就是order，aop的顺序一定要早于spring的事务，这里我将它设置成1，后面你会看到我将spring事务顺序设置成2。
配置类
首先有两个属性类：
DruidProperties连接池的属性类MutiDataSourcePropertiesbiz数据源的属性类
1 @Component 2 @ConfigurationProperties(prefix = "spring.datasource") 3 public class DruidProperties { 4 5 private String url; 6 7 private String username; 8 9 private String password; 10 11 private String driverClassName = "com.mysql.cj.jdbc.Driver"; 12 13 private Integer initialSize = 10; 14 15 private Integer minIdle = 3; 16 17 private Integer maxActive = 60; 18 19 private Integer maxWait = 60000; 20 21 private Boolean removeAbandoned = true; 22 23 private Integer removeAbandonedTimeout = 180; 24 25 private Integer timeBetweenEvictionRunsMillis = 60000; 26 27 private Integer minEvictableIdleTimeMillis = 300000; 28 29 private String validationQuery = "SELECT 'x'"; 30 31 private Boolean testWhileIdle = true; 32 33 private Boolean testOnBorrow = false; 34 35 private Boolean testOnReturn = false; 36 37 private Boolean poolPreparedStatements = true; 38 39 private Integer maxPoolPreparedStatementPerConnectionSize = 50; 40 41 private String filters = "stat"; 42 43 public void config(DruidDataSource dataSource) { 44 dataSource.setDbType(JdbcConstants.MYSQL); 45 dataSource.setUrl(url); 46 dataSource.setUsername(username); 47 dataSource.setPassword(password); 48 dataSource.setDriverClassName(driverClassName); 49 dataSource.setInitialSize(initialSize); // 定义初始连接数 50 dataSource.setMinIdle(minIdle); // 最小空闲 51 dataSource.setMaxActive(maxActive); // 定义最大连接数 52 dataSource.setMaxWait(maxWait); // 获取连接等待超时的时间 53 dataSource.setRemoveAbandoned(removeAbandoned); // 超过时间限制是否回收 54 dataSource.setRemoveAbandonedTimeout(removeAbandonedTimeout); // 超过时间限制多长 55 56 // 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 57 dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); 58 // 配置一个连接在池中最小生存的时间，单位是毫秒 59 dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); 60 // 用来检测连接是否有效的sql，要求是一个查询语句 61 dataSource.setValidationQuery(validationQuery); 62 // 申请连接的时候检测 63 dataSource.setTestWhileIdle(testWhileIdle); 64 // 申请连接时执行validationQuery检测连接是否有效，配置为true会降低性能 65 dataSource.setTestOnBorrow(testOnBorrow); 66 // 归还连接时执行validationQuery检测连接是否有效，配置为true会降低性能 67 dataSource.setTestOnReturn(testOnReturn); 68 // 打开PSCache，并且指定每个连接上PSCache的大小 69 dataSource.setPoolPreparedStatements(poolPreparedStatements); 70 dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); 71 // 属性类型是字符串，通过别名的方式配置扩展插件，常用的插件有： 72 // 监控统计用的filter:stat 73 // 日志用的filter:log4j 74 // 防御SQL注入的filter:wall 75 try { 76 dataSource.setFilters(filters); 77 } catch (SQLException e) { 78 e.printStackTrace(); 79 } 80 } 81 82 public String getUrl() { 83 return url; 84 } 85 86 public void setUrl(String url) { 87 this.url = url; 88 } 89 90 public String getUsername() { 91 return username; 92 } 93 94 public void setUsername(String username) { 95 this.username = username; 96 } 97 98 public String getPassword() { 99 return password; 100 } 101 102 public void setPassword(String password) { 103 this.password = password; 104 } 105 106 public String getDriverClassName() { 107 return driverClassName; 108 } 109 110 public void setDriverClassName(String driverClassName) { 111 this.driverClassName = driverClassName; 112 } 113 114 public Integer getInitialSize() { 115 return initialSize; 116 } 117 118 public void setInitialSize(Integer initialSize) { 119 this.initialSize = initialSize; 120 } 121 122 public Integer getMinIdle() { 123 return minIdle; 124 } 125 126 public void setMinIdle(Integer minIdle) { 127 this.minIdle = minIdle; 128 } 129 130 public Integer getMaxActive() { 131 return maxActive; 132 } 133 134 public void setMaxActive(Integer maxActive) { 135 this.maxActive = maxActive; 136 } 137 138 public Integer getMaxWait() { 139 return maxWait; 140 } 141 142 public void setMaxWait(Integer maxWait) { 143 this.maxWait = maxWait; 144 } 145 146 public Integer getTimeBetweenEvictionRunsMillis() { 147 return timeBetweenEvictionRunsMillis; 148 } 149 150 public void setTimeBetweenEvictionRunsMillis(Integer timeBetweenEvictionRunsMillis) { 151 this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis; 152 } 153 154 public Integer getMinEvictableIdleTimeMillis() { 155 return minEvictableIdleTimeMillis; 156 } 157 158 public void setMinEvictableIdleTimeMillis(Integer minEvictableIdleTimeMillis) { 159 this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis; 160 } 161 162 public String getValidationQuery() { 163 return validationQuery; 164 } 165 166 public void setValidationQuery(String validationQuery) { 167 this.validationQuery = validationQuery; 168 } 169 170 public Boolean getTestWhileIdle() { 171 return testWhileIdle; 172 } 173 174 public void setTestWhileIdle(Boolean testWhileIdle) { 175 this.testWhileIdle = testWhileIdle; 176 } 177 178 public Boolean getTestOnBorrow() { 179 return testOnBorrow; 180 } 181 182 public void setTestOnBorrow(Boolean testOnBorrow) { 183 this.testOnBorrow = testOnBorrow; 184 } 185 186 public Boolean getTestOnReturn() { 187 return testOnReturn; 188 } 189 190 public void setTestOnReturn(Boolean testOnReturn) { 191 this.testOnReturn = testOnReturn; 192 } 193 194 public Boolean getPoolPreparedStatements() { 195 return poolPreparedStatements; 196 } 197 198 public void setPoolPreparedStatements(Boolean poolPreparedStatements) { 199 this.poolPreparedStatements = poolPreparedStatements; 200 } 201 202 public Integer getMaxPoolPreparedStatementPerConnectionSize() { 203 return maxPoolPreparedStatementPerConnectionSize; 204 } 205 206 public void setMaxPoolPreparedStatementPerConnectionSize(Integer maxPoolPreparedStatementPerConnectionSize) { 207 this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize; 208 } 209 210 public String getFilters() { 211 return filters; 212 } 213 214 public void setFilters(String filters) { 215 this.filters = filters; 216 } 217 218 public Boolean getRemoveAbandoned() { 219 return removeAbandoned; 220 } 221 222 public void setRemoveAbandoned(Boolean removeAbandoned) { 223 this.removeAbandoned = removeAbandoned; 224 } 225 226 public Integer getRemoveAbandonedTimeout() { 227 return removeAbandonedTimeout; 228 } 229 230 public void setRemoveAbandonedTimeout(Integer removeAbandonedTimeout) { 231 this.removeAbandonedTimeout = removeAbandonedTimeout; 232 } 233 }
1 @Component 2 @ConfigurationProperties(prefix = "biz.datasource") 3 public class MutiDataSourceProperties { 4 5 private String url; 6 7 private String username; 8 9 private String password; 10 11 public void config(DruidDataSource dataSource) { 12 dataSource.setUrl(url); 13 dataSource.setUsername(username); 14 dataSource.setPassword(password); 15 } 16 17 public String getUrl() { 18 return url; 19 } 20 21 public void setUrl(String url) { 22 this.url = url; 23 } 24 25 public String getUsername() { 26 return username; 27 } 28 29 public void setUsername(String username) { 30 this.username = username; 31 } 32 33 public String getPassword() { 34 return password; 35 } 36 37 public void setPassword(String password) { 38 this.password = password; 39 } 40 }
然后定义配置类：
1 @Configuration 2 @EnableTransactionManagement(order = 2) 3 @MapperScan(basePackages = {"com.code.springbootmultisource.common.dao.repository"}) 4 public class MybatisPlusConfig { 5 6 @Autowired 7 DruidProperties druidProperties; 8 9 @Autowired 10 MutiDataSourceProperties mutiDataSourceProperties; 11 12 /** 13 * 核心数据源 14 */ 15 private DruidDataSource coreDataSource() { 16 DruidDataSource dataSource = new DruidDataSource(); 17 druidProperties.config(dataSource); 18 return dataSource; 19 } 20 21 /** 22 * 另一个数据源 23 */ 24 private DruidDataSource bizDataSource() { 25 DruidDataSource dataSource = new DruidDataSource(); 26 druidProperties.config(dataSource); 27 mutiDataSourceProperties.config(dataSource); 28 return dataSource; 29 } 30 31 /** 32 * 单数据源连接池配置 33 */ 34 @Bean 35 @ConditionalOnProperty(prefix = "code", name = "muti-datasource-open", havingValue = "false") 36 public DruidDataSource singleDatasource() { 37 return coreDataSource(); 38 } 39 40 41 /** 42 * 多数据源连接池配置 43 */ 44 @Bean 45 @ConditionalOnProperty(prefix = "code", name = "muti-datasource-open", havingValue = "true") 46 public DynamicDataSource mutiDataSource() { 47 48 DruidDataSource coreDataSource = coreDataSource(); 49 DruidDataSource bizDataSource = bizDataSource(); 50 51 try { 52 coreDataSource.init(); 53 bizDataSource.init(); 54 } catch (SQLException sql) { 55 sql.printStackTrace(); 56 } 57 58 DynamicDataSource dynamicDataSource = new DynamicDataSource(); 59 HashMap<Object, Object> hashMap = new HashMap<>(); 60 hashMap.put(DSEnum.DATA_SOURCE_CORE, coreDataSource); 61 hashMap.put(DSEnum.DATA_SOURCE_BIZ, bizDataSource); 62 dynamicDataSource.setTargetDataSources(hashMap); 63 dynamicDataSource.setDefaultTargetDataSource(coreDataSource); 64 return dynamicDataSource; 65 } 66 67 }
实体类
1 @TableName(value = "t_user") 2 public class User extends Model<User> { 3 4 private static final long serialVersionUID = 1L; 5 6 @TableId(value="id", type= IdType.AUTO) 7 private Integer id; 8 9 private String username; 10 11 private String name; 12 13 private String password; 14 15 private String salt; 16 17 private String phone; 18 19 private String tips; 20 21 private Integer state; 22 23 private Date createdTime; 24 25 private Date updatedTime; 26 27 public static long getSerialVersionUID() { 28 return serialVersionUID; 29 } 30 31 public Integer getId() { 32 return id; 33 } 34 35 public void setId(Integer id) { 36 this.id = id; 37 } 38 39 public String getUsername() { 40 return username; 41 } 42 43 public void setUsername(String username) { 44 this.username = username; 45 } 46 47 public String getName() { 48 return name; 49 } 50 51 public void setName(String name) { 52 this.name = name; 53 } 54 55 public String getPassword() { 56 return password; 57 } 58 59 public void setPassword(String password) { 60 this.password = password; 61 } 62 63 public String getSalt() { 64 return salt; 65 } 66 67 public void setSalt(String salt) { 68 this.salt = salt; 69 } 70 71 public String getPhone() { 72 return phone; 73 } 74 75 public void setPhone(String phone) { 76 this.phone = phone; 77 } 78 79 public String getTips() { 80 return tips; 81 } 82 83 public void setTips(String tips) { 84 this.tips = tips; 85 } 86 87 public Integer getState() { 88 return state; 89 } 90 91 public void setState(Integer state) { 92 this.state = state; 93 } 94 95 public Date getCreatedTime() { 96 return createdTime; 97 } 98 99 public void setCreatedTime(Date createdTime) { 100 this.createdTime = createdTime; 101 } 102 103 public Date getUpdatedTime() { 104 return updatedTime; 105 } 106 107 public void setUpdatedTime(Date updatedTime) { 108 this.updatedTime = updatedTime; 109 } 110 111 @Override 112 protected Serializable pkVal() { 113 return this.id; 114 } 115 116 }
定义DAO
1 public interface UserMapper extends BaseMapper<User> { 2 3 }
定义Service
1 @Service 2 public class UserService { 3 4 @Resource 5 private UserMapper userMapper; 6 7 public User findById(Integer id) { 8 return userMapper.selectById(id); 9 } 10 11 @DataSource(name = DSEnum.DATA_SOURCE_BIZ) 12 public User findById1(Integer id) { 13 return userMapper.selectById(id); 14 } 15 }
这里唯一要说明的就是我在方法findById1()上面增加了注解@DataSource(name = DSEnum.DATA_SOURCE_BIZ)，这样这个方法就会访问biz数据库。
注意，不加注解就会访问默认数据库pos。
测试
最后编写一个简单的测试，我只测试findById()方法和findById1()方法，看它们是否访问的是不同的数据源。
1 @RunWith(SpringRunner.class) 2 @SpringBootTest 3 public class SpringbootMultisourceApplicationTests { 4 5 private static final Logger LOGGER = LoggerFactory.getLogger(SpringbootMultisourceApplicationTests.class); 6 7 @Resource 8 private UserService userService; 9 10 @Test 11 public void contextLoads() { 12 13 User user = userService.findById(1); 14 LOGGER.info("核心数据库user = " + user.getUsername()); 15 16 User user1 = userService.findById1(1); 17 LOGGER.info("biz数据库user = " + user1.getUsername()); 18 } 19 20 }
显示结果：
核心数据库user = admin
数据源设置为: dataSourceBiz
biz数据库user = admin
