springboot基于方法级别注解事务的多数据源切换问题
springBoot多数据源配置
配置读数据源
@Component @ConfigurationProperties(prefix = "jdbc.read") @PropertySource("classpath:application.properties") public class ReadDataSource{ private String userName; private String password; private String driver; private String url; //TODO 此处应有get set方法 }
配置写数据源
@Component @ConfigurationProperties(prefix = "jdbc.read") @PropertySource("classpath:application.properties") public class WriteDataSource{ private String userName; private String password; private String driver; private String url; //TODO 此处应有get set方法 }
//配置数据源适配器 通过此类的set方法可以动态切换数据源,我们只需出入数据源对应key即可
public class DataSourceHolder { private static final ThreadLocal<String> dataSourceTypes = new ThreadLocal<String>() { @Override protected String initialValue() { return "writeDataSource"; } }; public static String get() { if(StringUtils.isEmpty(dataSourceTypes.get())){ return "writeDataSource"; } return dataSourceTypes.get(); } public static void set(String dataSourceType) { dataSourceTypes.set(dataSourceType); } public static void reset() { dataSourceTypes.set("writeDataSource"); } public static void remove() { dataSourceTypes.remove(); } }
配置多数据源 此处多数据源的动态切换主要就是通过determineCurrentLookupKey获取对应数据源的key去决定使用哪个数据源
此处需要注意如果处于同一事务中,则数据源不可切换,在事务中,会直接去获取上一次缓存的数据源,没有则调用该方法获取,但只获取一次,所以有可能会导致数据源切换失败.后续我们会通过切面去清除缓存数据源.但仅仅是拿到开启事务第一次获取的数据源.
public class MultipleDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DataSourceHolder.get(); } }
@ConfigurationProperties(prefix = "jdbc.read")此处映射以jdbc.read开头的配置属性名和实体类属性名一致
@PropertySource("classpath:application.properties") 指定从那个属性配置文件读取数据源,我的是Maven项目,所以放在resources下
注意:必须要能够被spring管理起来,所以需要配置到spring扫描路径.
接下来我们需要一个配置类:配置多数据源
//basePackages 指定读和写mapper包位置
@Configuration @MapperScan(basePackages = {"com.xxx.template.dal.mapper.read","com.xxx.template.dal.mapper.write"},sqlSessionTemplateRef = "sqlSessionTemplate") public Class DataSourceConfig{ @AutoWried private ReadDataSource readDataSourceProperties; @AutoWried private ReadDataSource writeDataSourceProperties;
//配置读数据源属性 @Bean(destroyMethod = "close") public BasicDataSource readDataSource() { BasicDataSource dataSource = new BasicDataSource(); dataSource.setDriverClassName(readDataSourceProperties.getDriver()); dataSource.setUrl(readDataSourceProperties.getUrl()); dataSource.setUsername(readDataSourceProperties.getUserName()); dataSource.setPassword(readDataSourceProperties.getPassword()); dataSource.setInitialSize(readDataSourceProperties.getInitialSize()); dataSource.setMaxTotal(readDataSourceProperties.getMaxTotal()); dataSource.setMaxIdle(readDataSourceProperties.getMaxIdle()); dataSource.setRemoveAbandonedOnBorrow(true); dataSource.setRemoveAbandonedTimeout(10); dataSource.setMaxWaitMillis(30000); dataSource.setTestWhileIdle(true); dataSource.setTestOnBorrow(false); dataSource.setTestOnReturn(false); dataSource.setValidationQuery("SELECT 1"); dataSource.setTimeBetweenEvictionRunsMillis(30000); dataSource.setNumTestsPerEvictionRun(30); dataSource.setMinEvictableIdleTimeMillis(600000); return dataSource; } //配置写数据源属性 @Bean(destroyMethod = "close") public BasicDataSource writeDataSource() { BasicDataSource dataSource = new BasicDataSource(); dataSource.setDriverClassName(writeDataSourceProperties.getDriver()); dataSource.setUrl(writeDataSourceProperties.getUrl()); dataSource.setUsername(writeDataSourceProperties.getUserName()); dataSource.setPassword(writeDataSourceProperties.getPassword()); dataSource.setInitialSize(writeDataSourceProperties.getInitialSize()); dataSource.setMaxTotal(writeDataSourceProperties.getMaxTotal()); dataSource.setMaxIdle(writeDataSourceProperties.getMaxIdle()); dataSource.setRemoveAbandonedOnBorrow(true); dataSource.setRemoveAbandonedTimeout(10); dataSource.setMaxWaitMillis(30000); dataSource.setTestWhileIdle(true); dataSource.setTestOnBorrow(false); dataSource.setTestOnReturn(false); dataSource.setValidationQuery("SELECT 1"); dataSource.setTimeBetweenEvictionRunsMillis(30000); dataSource.setNumTestsPerEvictionRun(30); dataSource.setMinEvictableIdleTimeMillis(600000); return dataSource; }
//配置动态数据源属性 动态数据源包含读写数据源
@Bean
public MultipleDataSource dataSource() {
MultipleDataSource multipleDataSource = new MultipleDataSource();
Map<Object, Object> map = new HashMap<>();
map.put("readDataSource",readDataSource());
map.put("writeDataSource" ,writeDataSource());
//此处存放多数据源进入map,根据key动态切换
multipleDataSource.setTargetDataSources(map);
return multipleDataSource;
}
//配置sqlSessionFactory
@Bean
public SqlSessionFactoryBean sqlSessionFactory() throws IOException {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource());
//指定mapper.xml的位置
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(DataSourceConfig.MAPPER_LOCATION));
//配置mybatis配置的位置
sqlSessionFactoryBean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource(DataSourceConfig.CONFIG_LOCATION));
return sqlSessionFactoryBean;
}
//配置sqlSessionTemplate
@Bean
public SqlSessionTemplate sqlSessionTemplate() throws Exception {
SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory().getObject());
return sqlSessionTemplate;
}
//配置事务管理
@Bean
public DataSourceTransactionManager transactionManager() {
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(dataSource());
return dataSourceTransactionManager;
} }
此刻我们数据源已经配好,接下来可以手动切换数据源,通过DataSourceHolder 的各种方法获取,清除,重置.使用完数据源做好调用清除方法,避免缓存导致无法切换数据源
我们也可以指定一个切面类去动态切换数据源
@Aspect @Order(-1) @Component public class DataSourceSwitch { @Before("此处填写切入点表达式") public void before(){ 切换为读数据源 DataSourceHolder.set("writeDataSource"); }
@Before("此处填写切入点表达式") public void before1(){ 切换为读数据源 DataSourceHolder.set("readDataSource"); } @After("此处填写切入点表达式") public void after(){ //移除数据源 DataSourceHolder.remove(); } @After("此处填写切入点表达式") public void after1(){ //移除数据源 DataSourceHolder.remove(); } }
在多数据源和事务结合起来的情况下,无法一个事务下切换数据源,因此只能一个事务下指定一个数据源,比如我们想读和写,那么最好使用写数据源,只读就只指定读数据源.
最后在我们方法级别加上@Transactional
在启动类上加@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})排除spring的默认数据源配置
