在实际开发中,即使在微服务盛行的今天,在一个系统中可能会有用到多个数据源(最简单的读写分离),但mybatis默认只有一个数据源,我们想用多个数据源的话,就只能自己去控制数据源
解决方案:
其实有好几种解决方案,我采用的是拦截器拦截自定义注解,在执行sql之前切换到具体的数据源,并在执行完之后销毁数据源
上代码:
1、配置启动类,去除默认的DataSource
@SpringBootApplication(exclude={
DataSourceAutoConfiguration.class,
DataSourceTransactionManagerAutoConfiguration.class
})
@MapperScan("com.springbootx.mybatis.mapper")
@EnableConfigurationProperties
public class MybatisApplication {
public static void main(String[] args) {
SpringApplication.run(MybatisApplication.class, args);
}
}
2、配置枚举,每个枚举对应一个数据源,方便我们取用
public enum DataSourceType {
/**
* 注册
*/
MASTER,SLAVE
}
3、配置我们的拦截注解
@Target({ElementType.METHOD, ElementType.TYPE,ElementType.ANNOTATION_TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
DataSourceType value() default DataSourceType.MASTER;
}
4、继承AbstractRoutingDataSource类,把第5步读取的afterPropertiesSet()方法调用时用来将targetDataSources的属性写入resolvedDataSources中的
public class DataSourceRouting extends AbstractRoutingDataSource {
public DataSourceRouting(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
super.setTargetDataSources(targetDataSources);
// afterPropertiesSet()方法调用时用来将targetDataSources的属性写入resolvedDataSources中的
super.afterPropertiesSet();
}
/**
* 根据Key获取数据源的信息
*
* @return key
*/
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceContextHolder.getDataSourceType();
}
}
5、从yml文件读取多个数据源进行解析,生成多数据源进行存储
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties("datasource.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties("datasource.slave")
public DataSource slaveDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@Primary
public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
@Qualifier("slaveDataSource") DataSource slaveDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>(DataSourceType.values().length);
targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
targetDataSources.put(DataSourceType.SLAVE.name(), slaveDataSource);
return new DataSourceRouting(masterDataSource, targetDataSources);
}
}
6,维护一个线程变量来存储拦截器中所要使用的数据源,并提供增删改查操作
public class DynamicDataSourceContextHolder {
public static final Logger log = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);
/**
* 使用ThreadLocal维护变量,ThreadLocal为每个使用该变量的线程提供独立的变量副本,
* 所以每一个线程都可以独立地改变自己的副本,而不会影响其它线程所对应的副本。
*/
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
/**
* 设置数据源变量
* @param dataSourceType 读取数据源type
*/
public static void setDataSourceType(String dataSourceType){
CONTEXT_HOLDER.set(dataSourceType);
}
/**
* 获取数据源变量
* @return type
*/
public static String getDataSourceType(){
return CONTEXT_HOLDER.get();
}
/**
* 清空数据源变量
*/
public static void clearDataSourceType(){
CONTEXT_HOLDER.remove();
}
}
7、设置拦截器,在mapper的sql被调用时拦截并绑定数据源
@Aspect
@Order(1)
@Component
public class DataSourceAop {
private final Logger log = LoggerFactory.getLogger(getClass());
@Pointcut("@annotation(com.springbootx.mybatis.config.annotations.DataSource)")
public void dsPointCut() {
}
@Around("dsPointCut()")
public Object around(ProceedingJoinPoint point) throws Throwable {
MethodSignature signature = (MethodSignature) point.getSignature();
Method method = signature.getMethod();
DataSource dataSource = method.getAnnotation(DataSource.class);
if (dataSource != null) {
DynamicDataSourceContextHolder.setDataSourceType(dataSource.value().name());
}
try {
return point.proceed();
} finally {
// 销毁数据源 在执行方法之后
DynamicDataSourceContextHolder.clearDataSourceType();
}
}
}
8、设置测试的po
@Getter
@Setter
public class Users {
private Integer id;
private String userName;
private String passWord;
private String phone;
private String email;
private String createDay;
}
9、写两个不同数据源的mapper
(1)、数据源一,master数据源
@Mapper
public interface MasterUsersMapper {
/**
* 写入数据
* @param users 实体
*/
@DataSource(value = DataSourceType.MASTER)
@Insert("<script> INSERT INTO users\n" +
" (id,user_name,pass_word,phone,email)\n" +
" VALUES\n" +
" (#{id,jdbcType=INTEGER},#{userName,jdbcType=VARCHAR},#{passWord,jdbcType=VARCHAR},\n" +
" #{phone,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR})</script> ")
void insert (Users users);
}
(1)、数据源二,salve数据源
@Mapper
public interface SalveUsersMapper {
/**
* 写入数据
* @param users 实体
*/
@DataSource(value = DataSourceType.SLAVE)
@Insert("<script> INSERT INTO users\n" +
" (id,user_name,pass_word,phone,email)\n" +
" VALUES\n" +
" (#{id,jdbcType=INTEGER},#{userName,jdbcType=VARCHAR},#{passWord,jdbcType=VARCHAR},\n" +
" #{phone,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR})</script> ")
void saveData (Users users);
}
10、设置service,在逻辑层调用写入方法
public interface UserService {
/**
* 插入测试
*/
void insert();
}
@Service
public class UserServiceImpl implements UserService {
@Resource
MasterUsersMapper masterUsersMapper;
@Resource
SalveUsersMapper salveUsersMapper;
@Override
public void insert() {
Users masterUsers=new Users();
masterUsers.setId(0);
masterUsers.setUserName("master测试数据");
masterUsers.setPassWord("master测试数据");
masterUsers.setPhone("master测试数据");
masterUsers.setEmail("master测试数据");
masterUsersMapper.insert(masterUsers);
Users salveUsers=new Users();
salveUsers.setId(0);
salveUsers.setUserName("master测试数据");
salveUsers.setPassWord("master测试数据");
salveUsers.setPhone("master测试数据");
salveUsers.setEmail("master测试数据");
salveUsersMapper.saveData(salveUsers);
}
}
11、编写测试类执行
@SpringBootTest
class MybatisApplicationTests {
@Resource
UserService userService;
@Test
void contextLoads() {
userService.insert();
}
}
配置文件
mybatis:
configuration:
mapUnderscoreToCamelCase: true
datasource:
master:
username: root
password: 123456
jdbc-url: jdbc:mysql://10.0.1.101:3306/zw_user?useUnicode=true&characterEncoding=utf-8&characterEncoding=utf8&serverTimezone=UTC
driver-class-name: com.mysql.jdbc.Driver
slave:
username: root
password: 123456
jdbc-url: jdbc:mysql://10.0.1.51:3306/zw_user?useUnicode=true&characterEncoding=utf-8&characterEncoding=utf8&serverTimezone=UTC
driver-class-name: com.mysql.jdbc.Driver
sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` bigint(20) NOT NULL,
`user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`pass_word` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`phone` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`email` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`create_day` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;
DELETE from users where id != -1
SELECT * FROM users;
执行完毕后查看数据库:
可以看到,两个不同的机器上的不同库插入成功,多数据源生效,源码: