多数据源
导入依赖
<!-- MySql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.9</version>
</dependency>修改配置文件
我这里是配置了五个数据源 ttxnsaasaccount,ttxnsaasproject,ttxnsaassys,ttxnsaasexam,ttxnsaasorders
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
druid:
ttxnsaasaccount:
url: jdbc:mysql://127.0.0.1:3306/ttxnsaasaccount?characterEncoding=UTF-8&useUnicode=true&useSSL=false
username: root
password: root
ttxnsaasproject:
url: jdbc:mysql://127.0.0.1:3306/ttxnsaasproject?characterEncoding=UTF-8&useUnicode=true&useSSL=false
username: root
password: root
ttxnsaassys:
url: jdbc:mysql://127.0.0.1:3306/ttxnsaassys?characterEncoding=UTF-8&useUnicode=true&useSSL=false
username: root
password: root
ttxnsaasexam:
url: jdbc:mysql://127.0.0.1:3306/ttxnsaasexam?characterEncoding=UTF-8&useUnicode=true&useSSL=false
username: root
password: root
ttxnsaasorders:
url: jdbc:mysql://127.0.0.1:3306/ttxnsaasorders?characterEncoding=UTF-8&useUnicode=true&useSSL=false
username: root
password: root定义数据库名称常量
/**
* @author 萧一旬
* @date Create in 10:58 2020/7/13
* <p>
* 数据库名称
*/
public interface DataSourceNames {
String TTXN_SAAS_ACCOUNT = "TTXN_SAAS_ACCOUNT";
String TTXN_SAAS_PROJECT = "TTXN_SAAS_PROJECT";
String TTXN_SAAS_EXAM = "TTXN_SAAS_EXAM";
String TTXN_SAAS_ORDERS = "TTXN_SAAS_ORDERS";
String TTXN_SAAS_SYS = "TTXN_SAAS_SYS";
}创建动态数据源
/**
* 动态数据源
*
* @author 萧一旬
* @date Create in 11:00 2020/7/13
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
/**
* 配置DataSource, defaultTargetDataSource为主数据库
*/
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
super.setTargetDataSources(targetDataSources);
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey() {
return getDataSource();
}
public static void setDataSource(String dataSource) {
contextHolder.set(dataSource);
}
public static String getDataSource() {
return contextHolder.get();
}
public static void clearDataSource() {
contextHolder.remove();
}
}配置动态数据源
/**
* @author 萧一旬
* @date Create in 11:02 2020/7/13
*/
@Configuration
public class DynamicDataSourceConfig {
/**
* 创建 DataSource Bean
*/
@Bean(name = "ttxnSaasAccountDataSource")
@ConfigurationProperties(prefix = "spring.datasource.druid.ttxnsaasaccount")
public DataSource ttxnSaasAccountDataSource() {
DataSource dataSource = DruidDataSourceBuilder.create().build();
return dataSource;
}
@Bean(name = "ttxnSaasProjectDataSource")
@ConfigurationProperties("spring.datasource.druid.ttxnsaasproject")
public DataSource ttxnSaasProjectDataSource() {
DataSource dataSource = DruidDataSourceBuilder.create().build();
return dataSource;
}
/**
* @return
*/
@Bean(name = "ttxnSaasExamDataSource")
@ConfigurationProperties("spring.datasource.druid.ttxnsaasexam")
public DataSource ttxnSaasExamDataSource() {
DataSource dataSource = DruidDataSourceBuilder.create().build();
return dataSource;
}
@Bean(name = "ttxnSaasOrdersDataSource")
@ConfigurationProperties("spring.datasource.druid.ttxnsaasorders")
public DataSource ttxnSaasOrdersDataSource() {
DataSource dataSource = DruidDataSourceBuilder.create().build();
return dataSource;
}
@Bean(name = "ttxnSaasSysDataSource")
@ConfigurationProperties("spring.datasource.druid.ttxnsaassys")
public DataSource ttxnSaasSysDataSource() {
DataSource dataSource = DruidDataSourceBuilder.create().build();
return dataSource;
}
/**
* 如果还有数据源,在这继续添加 DataSource Bean
*/
@Bean
@Primary
public DynamicDataSource dataSource(DataSource ttxnSaasAccountDataSource,
DataSource ttxnSaasProjectDataSource,
DataSource ttxnSaasExamDataSource,
DataSource ttxnSaasOrdersDataSource,
DataSource ttxnSaasSysDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceNames.TTXN_SAAS_ACCOUNT, ttxnSaasAccountDataSource);
targetDataSources.put(DataSourceNames.TTXN_SAAS_EXAM, ttxnSaasExamDataSource);
targetDataSources.put(DataSourceNames.TTXN_SAAS_ORDERS, ttxnSaasOrdersDataSource);
targetDataSources.put(DataSourceNames.TTXN_SAAS_PROJECT, ttxnSaasProjectDataSource);
targetDataSources.put(DataSourceNames.TTXN_SAAS_SYS, ttxnSaasSysDataSource);
// 还有数据源,在targetDataSources中继续添加
System.out.println("DataSources:" + targetDataSources);
return new DynamicDataSource(ttxnSaasAccountDataSource, targetDataSources);
}
}定义动态数据源注解
/**
* @author 萧一旬
* @date Create in 11:02 2020/7/13
*/
@Documented
@Target({java.lang.annotation.ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSource {
String value();
}数据源AOP代理
/**
* 数据源AOP切面处理
*
* @author 萧一旬
* @date Create in 11:22 2020/7/13
*/
@Aspect
@Component
@Slf4j
public class DataSourceAspect implements Ordered {
/**
* 切点: 所有配置 DataSource 注解的方法
*/
@Pointcut("@annotation(com.ttxn.common.data.DataSource)")
public void dataSourcePointCut() {
}
@Around("dataSourcePointCut()")
public Object around(ProceedingJoinPoint point) throws Throwable {
MethodSignature signature = (MethodSignature) point.getSignature();
Method method = signature.getMethod();
DataSource ds = method.getAnnotation(DataSource.class);
// 通过判断 DataSource 中的值来判断当前方法应用哪个数据源
DynamicDataSource.setDataSource(ds.value());
System.out.println("当前数据源: " + ds.value());
log.debug("set datasource is " + ds.value());
try {
return point.proceed();
} finally {
DynamicDataSource.clearDataSource();
log.debug("clean datasource");
}
}
@Override
public int getOrder() {
return 1;
}
}修改启动类
/**
* 项目启动类
*/
//@SpringBootApplication
@EnableSwagger2
//动态数据源配置,需要将自有的配置依赖(DynamicDataSourceConfig),将原有的依赖去除(DataSourceAutoConfiguration)
@Import({DynamicDataSourceConfig.class})
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class CompanyMainApplication {
public static void main(String[] args) {
// System.setProperty("spring.devtools.restart.enabled", "false");
SpringApplication.run(CompanyMainApplication.class, args);
}
}测试
只需要在方法上加上我们的自定义注解，并指定好数据源即可
@Override
@DataSource(DataSourceNames.TTXN_SAAS_ACCOUNT)
public Integer selectNewAccountCount() {
return this.baseMapper.selectNewAccountCount();
}
@Override
@DataSource(DataSourceNames.TTXN_SAAS_ACCOUNT)
public Integer queryAccountStudentPendingCount(Integer companyId) {
return this.baseMapper.queryAccountStudentPendingCount(companyId);
}多数据源事务处理
当配置了多数据源之后，原来单数据源的事务就不起作用了，甚至还会引起报错，所以使用的时候要把原来加的@Transactional注解全部删掉
Atomikos
导入依赖
<dependency>
<groupId>com.atomikos</groupId>
<artifactId>transactions</artifactId>
<version>4.0.6</version>
</dependency>
<dependency>
<groupId>com.atomikos</groupId>
<artifactId>transactions-api</artifactId>
<version>4.0.6</version>
</dependency>
<dependency>
<groupId>com.atomikos</groupId>
<artifactId>transactions-jta</artifactId>
<version>4.0.6</version>
</dependency>
<dependency>
<groupId>com.atomikos</groupId>
<artifactId>transactions-jdbc</artifactId>
<version>4.0.6</version>
</dependency>
<dependency>
<groupId>com.atomikos</groupId>
<artifactId>atomikos-util</artifactId>
<version>4.0.6</version>
</dependency>
<dependency>
<groupId>javax.transaction</groupId>
<artifactId>javax.transaction-api</artifactId>
<version>1.2</version>
</dependency>修改多数据源配置类
将DynamicDataSourceConfig代码修改一下
@Data
public class DbConfig implements Serializable {
private static final long serialVersionUID = 1L;
private String url;
private String username;
private String password;
}
-------------------------------
/**
* @author 萧一旬
* @date Create in 11:02 2020/7/13
*/
@Configuration
public class DynamicDataSourceConfig {
@Bean("accountConfig")
@ConfigurationProperties(prefix = "spring.datasource.druid.ttxnsaasaccount")
public DbConfig accountConfig() {
return new DbConfig();
}
/**
* 创建 DataSource Bean
*/
@Bean(name = "ttxnSaasAccountDataSource")
public DataSource ttxnSaasAccountDataSource(@Qualifier("accountConfig") DbConfig accountConfig) {
// DataSource dataSource = DruidDataSourceBuilder.create().build();
MysqlXADataSource mysqlXADataSource = new MysqlXADataSource();
mysqlXADataSource.setUrl(accountConfig.getUrl());
mysqlXADataSource.setPinGlobalTxToPhysicalConnection(true);
mysqlXADataSource.setPassword(accountConfig.getPassword());
mysqlXADataSource.setUser(accountConfig.getUsername());
AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
atomikosDataSourceBean.setXaDataSource(mysqlXADataSource);
atomikosDataSourceBean.setUniqueResourceName("ttxnSaasAccountDataSource");
return atomikosDataSourceBean;
// return dataSource;
}
@Bean("projectConfig")
@ConfigurationProperties("spring.datasource.druid.ttxnsaasproject")
public DbConfig projectConfig() {
return new DbConfig();
}
@Bean(name = "ttxnSaasProjectDataSource")
public DataSource ttxnSaasProjectDataSource(@Qualifier("projectConfig") DbConfig projectConfig) {
MysqlXADataSource mysqlXADataSource = new MysqlXADataSource();
mysqlXADataSource.setUrl(projectConfig.getUrl());
mysqlXADataSource.setPinGlobalTxToPhysicalConnection(true);
mysqlXADataSource.setPassword(projectConfig.getPassword());
mysqlXADataSource.setUser(projectConfig.getUsername());
AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
atomikosDataSourceBean.setXaDataSource(mysqlXADataSource);
atomikosDataSourceBean.setUniqueResourceName("ttxnSaasProjectDataSource");
return atomikosDataSourceBean;
}
@Bean("examConfig")
@ConfigurationProperties("spring.datasource.druid.ttxnsaasexam")
public DbConfig examConfig() {
return new DbConfig();
}
@Bean(name = "ttxnSaasExamDataSource")
public DataSource ttxnSaasExamDataSource(@Qualifier("examConfig") DbConfig examConfig) {
MysqlXADataSource mysqlXADataSource = new MysqlXADataSource();
mysqlXADataSource.setUrl(examConfig.getUrl());
mysqlXADataSource.setPinGlobalTxToPhysicalConnection(true);
mysqlXADataSource.setPassword(examConfig.getPassword());
mysqlXADataSource.setUser(examConfig.getUsername());
AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
atomikosDataSourceBean.setXaDataSource(mysqlXADataSource);
atomikosDataSourceBean.setUniqueResourceName("ttxnSaasExamDataSource");
return atomikosDataSourceBean;
}
@Bean("ordersConfig")
@ConfigurationProperties("spring.datasource.druid.ttxnsaasorders")
public DbConfig ordersConfig() {
return new DbConfig();
}
@Bean(name = "ttxnSaasOrdersDataSource")
public DataSource ttxnSaasOrdersDataSource(@Qualifier("ordersConfig") DbConfig ordersConfig) {
MysqlXADataSource mysqlXADataSource = new MysqlXADataSource();
mysqlXADataSource.setUrl(ordersConfig.getUrl());
mysqlXADataSource.setPinGlobalTxToPhysicalConnection(true);
mysqlXADataSource.setPassword(ordersConfig.getPassword());
mysqlXADataSource.setUser(ordersConfig.getUsername());
AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
atomikosDataSourceBean.setXaDataSource(mysqlXADataSource);
atomikosDataSourceBean.setUniqueResourceName("ttxnSaasOrdersDataSource");
return atomikosDataSourceBean;
}
@Bean("sysConfig")
@ConfigurationProperties("spring.datasource.druid.ttxnsaassys")
public DbConfig sysConfig() {
return new DbConfig();
}
@Bean(name = "ttxnSaasSysDataSource")
public DataSource ttxnSaasSysDataSource(@Qualifier("sysConfig") DbConfig sysConfig) {
MysqlXADataSource mysqlXADataSource = new MysqlXADataSource();
mysqlXADataSource.setUrl(sysConfig.getUrl());
mysqlXADataSource.setPinGlobalTxToPhysicalConnection(true);
mysqlXADataSource.setPassword(sysConfig.getPassword());
mysqlXADataSource.setUser(sysConfig.getUsername());
AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
atomikosDataSourceBean.setXaDataSource(mysqlXADataSource);
atomikosDataSourceBean.setUniqueResourceName("ttxnSaasSysDataSource");
return atomikosDataSourceBean;
}
/**
* 如果还有数据源,在这继续添加 DataSource Bean
*/
@Bean
@Primary
public DynamicDataSource dataSource(DataSource ttxnSaasAccountDataSource,
DataSource ttxnSaasProjectDataSource,
DataSource ttxnSaasExamDataSource,
DataSource ttxnSaasOrdersDataSource,
DataSource ttxnSaasSysDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceNames.TTXN_SAAS_ACCOUNT, ttxnSaasAccountDataSource);
targetDataSources.put(DataSourceNames.TTXN_SAAS_EXAM, ttxnSaasExamDataSource);
targetDataSources.put(DataSourceNames.TTXN_SAAS_ORDERS, ttxnSaasOrdersDataSource);
targetDataSources.put(DataSourceNames.TTXN_SAAS_PROJECT, ttxnSaasProjectDataSource);
targetDataSources.put(DataSourceNames.TTXN_SAAS_SYS, ttxnSaasSysDataSource);
// 还有数据源,在targetDataSources中继续添加
System.out.println("DataSources:" + targetDataSources);
return new DynamicDataSource(ttxnSaasAccountDataSource, targetDataSources);
}
}配置多数据源的事务处理
/***
* 分布式事物管理
*/
@Configuration
public class AutoProxyRegist {
@Bean(name="atomikosTransactionManager",initMethod="init",destroyMethod="close")
public UserTransactionManager userTransactionManager(){
UserTransactionManager transactionManager = new UserTransactionManager();
transactionManager.setForceShutdown(false);
return transactionManager;
}
@Bean(name="atomikosUserTransaction")
public UserTransactionImp userTransactionImp() throws SystemException {
UserTransactionImp transactionImp = new UserTransactionImp();
transactionImp.setTransactionTimeout(120);
return transactionImp;
}
@Bean("springTransactionManager")
public JtaTransactionManager transactionManager(
@Qualifier("atomikosTransactionManager") UserTransactionManager transactionManager,
@Qualifier("atomikosUserTransaction") UserTransactionImp userTransaction) {
JtaTransactionManager jtaTM = new JtaTransactionManager();
jtaTM.setTransactionManager(transactionManager);
jtaTM.setUserTransaction(userTransaction);
jtaTM.setAllowCustomIsolationLevels(true);
return jtaTM;
}
@Bean(name = "txAdvice")
public TransactionInterceptor getAdvisor(@Qualifier("springTransactionManager") JtaTransactionManager txManager)
throws Exception {
TransactionInterceptor tsi = new TransactionInterceptor();
Properties properties = new Properties();
properties.setProperty("get*", "PROPAGATION_SUPPORTS");
properties.setProperty("select*", "PROPAGATION_SUPPORTS");
properties.setProperty("load*", "PROPAGATION_SUPPORTS");
properties.setProperty("query*", "PROPAGATION_SUPPORTS");
properties.setProperty("list*", "PROPAGATION_SUPPORTS");
properties.setProperty("add*", "PROPAGATION_REQUIRED");
properties.setProperty("insert*", "PROPAGATION_REQUIRED");
properties.setProperty("save*", "PROPAGATION_REQUIRED");
properties.setProperty("update*", "PROPAGATION_REQUIRED");
properties.setProperty("modify*", "PROPAGATION_REQUIRED");
properties.setProperty("do*", "PROPAGATION_REQUIRED");
properties.setProperty("del*", "PROPAGATION_REQUIRED");
properties.setProperty("remove*", "PROPAGATION_REQUIRED");
properties.setProperty("process*", "PROPAGATION_REQUIRED");
properties.setProperty("create*", "PROPAGATION_REQUIRED");
properties.setProperty("valid*", "PROPAGATION_REQUIRED");
properties.setProperty("do*", "PROPAGATION_REQUIRED");
properties.setProperty("write*", "PROPAGATION_REQUIRED");
properties.setProperty("cancel*", "PROPAGATION_REQUIRED");
properties.setProperty("*", "readOnly");
tsi.setTransactionAttributes(properties);
tsi.setTransactionManager(txManager);
return tsi;
}
@Bean
public BeanNameAutoProxyCreator txProxy() {
BeanNameAutoProxyCreator creator = new BeanNameAutoProxyCreator();
creator.setInterceptorNames("txAdvice");
creator.setBeanNames("*ServiceImpl");
creator.setProxyTargetClass(true);
return creator;
}
}注意
当你只有一个模块配置了atomikos的话，这样子就可以了，但是如果多项目都配置了atomikos，那么只能启动一个项目，第二个项目启动就会报错 com.atomikos.icatch.SysException: Error in init(): Log already in use 这个问题是因为每个项目输出日志的文件不能是同一个，也就是文件名com.atomikos.icatch.log_base_name不能重复
所以还需要在使用到的每个项目里加上一个配置文件transactions-defaults.properties
#===============================================================
#============ 事务管理器(TM)配置参数 ==============
#===============================================================
#指定是否启动磁盘日志，默认为true。在生产环境下一定要保证为true，否则数据的完整性无法保证
com.atomikos.icatch.enable_logging=true
#JTA/XA资源是否应该自动注册
com.atomikos.icatch.automatic_resource_registration=true
#JTA事务的默认超时时间，默认为10000ms
com.atomikos.icatch.default_jta_timeout=10000
#事务的最大超时时间，默认为300000ms。这表示事务超时时间由 UserTransaction.setTransactionTimeout()较大者决定。4.x版本之后，指定为0的话则表示不设置超时时间
com.atomikos.icatch.max_timeout=300000
#指定在两阶段提交时，是否使用不同的线程(意味着并行)。3.7版本之后默认为false，更早的版本默认为true。如果为false，则提交将按照事务中访问资源的顺序进行。
com.atomikos.icatch.threaded_2pc=false
#指定最多可以同时运行的事务数量，默认值为50，负数表示没有数量限制。在调用 UserTransaction.begin()方法时，可能会抛出一个”Max number of active transactions reached”异常信息，表示超出最大事务数限制
com.atomikos.icatch.max_actives=50
#是否支持subtransaction，默认为true
com.atomikos.icatch.allow_subtransactions=true
#指定在可能的情况下，否应该join 子事务(subtransactions)，默认值为true。如果设置为false，对于有关联的不同subtransactions，不会调用XAResource.start(TM_JOIN)
com.atomikos.icatch.serial_jta_transactions=true
#指定JVM关闭时是否强制(force)关闭事务管理器，默认为false
com.atomikos.icatch.force_shutdown_on_vm_exit=false
#在正常关闭(no-force)的情况下，应该等待事务执行完成的时间，默认为Long.MAX_VALUE
com.atomikos.icatch.default_max_wait_time_on_shutdown=9223372036854775807
#===============================================================
#========= 事务日志(Transaction logs)记录配置 =======
#===============================================================
#事务日志目录，默认为./。
com.atomikos.icatch.log_base_dir=./
#事务日志文件前缀，默认为tmlog。事务日志存储在文件中，文件名包含一个数字后缀，日志文件以.log为扩展名，如tmlog1.log。遇到checkpoint时，新的事务日志文件会被创建，数字增加。
com.atomikos.icatch.log_base_name=tmlog
#指定两次checkpoint的时间间隔，默认为500
com.atomikos.icatch.checkpoint_interval=500
#===============================================================
#========= 事务日志恢复(Recovery)配置 =============
#===============================================================
#指定在多长时间后可以清空无法恢复的事务日志(orphaned)，默认86400000ms
com.atomikos.icatch.forget_orphaned_log_entries_delay=86400000
#指定两次恢复扫描之间的延迟时间。默认值为与com.atomikos.icatch.default_jta_timeout相同
com.atomikos.icatch.recovery_delay=${com.atomikos.icatch.default_jta_timeout}
#提交失败时，再抛出一个异常之前，最多可以重试几次，默认值为5
com.atomikos.icatch.oltp_max_retries=5
#提交失败时，每次重试的时间间隔，默认10000ms
com.atomikos.icatch.oltp_retry_interval=10000
===============================================================
========= 其他 =============================== ==
===============================================================
java.naming.factory.initial=com.sun.jndi.rmi.registry.RegistryContextFactory
com.atomikos.icatch.client_demarcation=false
java.naming.provider.url=rmi://localhost:1099
com.atomikos.icatch.rmi_export_class=none
com.atomikos.icatch.trust_client_tm=false
7637
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
