1.动态切换数据源
1.1 声明多个数据源
@Bean(name = "oracledb")
public DataSource handlerOracle(List attributes) {
return handlerDatasource(attributes);
}
@Bean(name = "mysqldb")
public DataSource handlerMysql(List attributes) {
return handlerDatasource(attributes);
}
1.2 装配动态数据源
@Bean
public DynamicDataSource dynamicDataSource(
@Qualifier("mysqldb") DataSource mysqldb,
@Qualifier("oracledb") DataSource oracledb) {
DynamicDataSource source = new DynamicDataSource();
Map<Object, Object> map = new HashMap<>();
map.put("mysqldb", mysqldb);
map.put("oracledb", oracledb);
// 添加多种数据源
source.setTargetDataSources(map);
return source;
}
1.3 装配JDBC模板
@Bean
public JdbcTemplate jdbcTemplate(DynamicDataSource dynamicDataSource) {
return new JdbcTemplate(dynamicDataSource);
}
1.4 定义切换数据源的类
package com.sitech.ddoe.server.core;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class DynamicDataSource extends AbstractRoutingDataSource {
public static final String DATA_SOURCE_MYSQL = "mysqldb";
public static final String DATA_SOURCE_ORACLE = "oracledb";
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
public static void setCustomerType(String customerType) {
contextHolder.set(customerType);
}
public static String getCustomerType() {
return contextHolder.get();
}
public static void clearCustomerType() {
contextHolder.remove();
}
@Override
protected Object determineCurrentLookupKey() {
return getCustomerType();
}
}
1.5 根据需求切换数据源
if(dbType == DBTypeConstant.DBTYPE_MYSQL)
DynamicDataSource.setCustomerType(DynamicDataSource.DATA_SOURCE_MYSQL);
else if(dbType == DBTypeConstant.DBTYPE_ORACLE)
DynamicDataSource.setCustomerType(DynamicDataSource.DATA_SOURCE_ORACLE);
2.开启事务
2.1 装配事务管理器
@Bean
public PlatformTransactionManager transactionManager(DynamicDataSource dynamicDataSource) {
return new DataSourceTransactionManager(dynamicDataSource);
}
2.2 在JDBC操作方法上添加@Transactional
@Transactional
@Override
public int insert(InsertSqlData insertSqlData) {
// ...
}
然后测试没有效果,注意查看以下地方
2.3 数据源关闭自动提交
原来是true,要关闭自动提交
dataSource.setDefaultAutoCommit(false);
2.4 在配置类上开启事务注解
@Configuration
@EnableTransactionManagement
public class ServerReader {
}
@EnableTransactionManagement相当于xml中的<tx:annotation-driven />
再次测试,事务起到了作用,但是mysql中如果主键是自增的话,新增失败即使事务回滚,主键的值仍是增加。比如第一次新增id为1成功,第二次新增id为2,3失败的话,第三次新增的话数据库插入的主键是4,而不是2了。这是mysql的一种设计。