preface
- Whether you use multiple data sources on your project is one thing, whether you use them yourself is another.
- SpringBoot2.0.8 is easy to integrate MybatisPlus for multiple data sources, but transactions never work?
- MybatisPlus provides a multi-data source plug-in (linkCan I not use it?
- In fact, multiple data sources are good, but the transaction has not been effective. It was finally settled today.
Project Structure:
The main configuration classes are DsAspect, DataSourceConfiguration, MyRoutingDataSource, MybatisConfiguration, and TransactionConfig. I’ll explain what each class does one by one.
Configuration file:
spring:
# data source configuration
datasource:
druid:
type: com.alibaba.druid.pool.DruidDataSource
defaultDs: master
master:
name: master
url: jdbc:mysql://ip:3306/wx_edu? useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
initial-size: 10
min-idle: 10
max-active: 100
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT version()
validation-query-timeout: 10000
test-while-idle: true
test-on-borrow: false
test-on-return: false
remove-abandoned: true
remove-abandoned-timeout: 86400
filters: stat,wall
connection-properties: druid.stat.mergeSql=true;
web-stat-filter:
enabled: true
url-pattern: / *
exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
stat-view-servlet:
enabled: true
url-pattern: /druid/*
reset-enable: false
login-username: admin
login-password: admin
filter:
stat:
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
config:
enabled: true
# slave data source
slave:
name: slave
url: jdbc:mysql://ip:3307/wx_edu? useUnicode=true&characterEncoding=UTF-8&useSSL=false
username: root
password: 123456
driver-class-name: com.mysql.jdbc.Driver
# connection parameters
initial-size: 10
min-idle: 10
max-active: 100
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT version()
validation-query-timeout: 10000
test-while-idle: true
test-on-borrow: false
test-on-return: false
remove-abandoned: true
remove-abandoned-timeout: 86400
filters: stat,wall
connection-properties: druid.stat.mergeSql=true;
web-stat-filter:
enabled: true
url-pattern: / *
exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
stat-view-servlet:
enabled: true
url-pattern: /druid/*
reset-enable: false
login-username: admin
login-password: admin
filter:
stat:
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
config:
enabled: true
mybatis-plus:
global-config:
SQL > select * from user where ID = 1; SQL > select * from user where ID = 1;
id-type: 0
# field policy 0:" ignore judgment ",1:" non-null judgment "),2:" non-null judgment"
field-strategy: 0
# Hump underline conversion
db-column-underline: true
# Refresh mapper debugger
refresh-mapper: true
# database uppercase underline conversion
#capital-mode: true
# logical delete configuration (3 following configurations)
logic-delete-value: 0
logic-not-delete-value: 1
# SQL parse cache. Multi-tenant @SQLParser annotation takes effect when enabled
# sql-parser-cache: true
Copy the codeDataSourceConfiguration:
Configure a Bean for multiple data sources
@Configuration
public class DataSourceConfiguration {
/** * Default is data source */
@Value("${spring.datasource.druid.defaultDs}")
private String defaultDs;
@Bean(name = "dataSourceMaster")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.druid.master")
public DataSource dataSourceMaster(a) {
DataSource druidDataSource = DruidDataSourceBuilder.create().build();
DbContextHolder.addDataSource(CommonEnum.DsType.DS_MASTER.getValue(), druidDataSource);
return druidDataSource;
}
@Bean(name = "dataSourceSlave")
@ConfigurationProperties(prefix = "spring.datasource.druid.slave")
public DataSource dataSourceSlave(a) {
DataSource druidDataSource = DruidDataSourceBuilder.create().build();
DbContextHolder.addDataSource(CommonEnum.DsType.DS_SLAVE.getValue(), druidDataSource);
return druidDataSource;
}
@Bean(name = "myRoutingDataSource")
public MyRoutingDataSource dataSource(@Qualifier("dataSourceMaster") DataSource dataSourceMaster, @Qualifier("dataSourceSlave") DataSource dataSourceSlave) {
MyRoutingDataSource dynamicDataSource = new MyRoutingDataSource();
Map<Object, Object> targetDataResources = new HashMap<>();
targetDataResources.put(CommonEnum.DsType.DS_MASTER.getValue(), dataSourceMaster);
targetDataResources.put(CommonEnum.DsType.DS_SLAVE.getValue(), dataSourceSlave);
// Set the default data source
dynamicDataSource.setDefaultTargetDataSource(dataSourceMaster);
dynamicDataSource.setTargetDataSources(targetDataResources);
DbContextHolder.setDefaultDs(defaultDs);
returndynamicDataSource; }}Copy the codeThe dataSource Bean encapsulates the configuration file as the dataSource Bean. MyRoutingDataSource is the dataSource we use, including the transaction configuration.
MyRoutingDataSource
public class MyRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey(a) {
returnDbContextHolder.getCurrentDsStr(); }}Copy the codeIncluding AbstractRoutingDataSource is Spring JDBC modules provide an abstract class, the class ACTS as a DataSource routing mediation, can at run time, according to some key value to dynamically switch to real DataSource, Rewrite the determineCurrentLookupKey () method, which can realize data source switching. This means that if you want to play with multiple data sources, use this class. I’m also using a DbContextHolder utility class (the holder of the data source), which is basically copied from the web with a few modifications:
public class DbContextHolder {
/** * Configure the data source */ in the project
private static Map<String, DataSource> dataSources = new ConcurrentHashMap<>();
/** * Default data source */
private static String defaultDs = "";
In order to support nested switching, e.g. ABC three services are different data sources * where A business needs to call B's method, B's method needs to call C's method. Level by level calls switch, forming a chain. * The traditional way of setting only the current thread cannot meet this business requirement and must simulate the stack, last in, first out. * */
private static final ThreadLocal<Deque<String>> contextHolder = new ThreadLocal() {
@Override
protected Object initialValue(a) {
return newArrayDeque(); }};/** * Sets the data source ** used by the current thread@param dsName
*/
public static void setCurrentDsStr(String dsName) {
if (StringUtils.isBlank(dsName)) {
log.error("==========>dbType is null,throw NullPointerException");
throw new NullPointerException();
}
if(! dataSources.containsKey(dsName)) { log.error("==========>datasource not exists,dsName={}", dsName);
throw new RuntimeException("==========>datasource not exists,dsName={" + dsName +"}");
}
contextHolder.get().push(dsName);
}
/** * get the current data source **@return* /
public static String getCurrentDsStr(a) {
return contextHolder.get().peek();
}
/** * Empties the current thread data source * * only removes the current thread data source name *
*/
public static void clearCurrentDsStr(a) {
Deque<String> deque = contextHolder.get();
deque.poll();
if(deque.isEmpty()){ contextHolder.remove(); }}/** * Add data source **@param dsName
* @param dataSource
*/
public static void addDataSource(String dsName, DataSource dataSource) {
if (dataSources.containsKey(dsName)) {
log.error("==========>dataSource={} already exist", dsName);
//throw new RuntimeException("dataSource={" + dsName + "} already exist");
return;
}
dataSources.put(dsName, dataSource);
}
/** * gets the specified data source **@return* /
public static DataSource getDefaultDataSource(a) {
if (StringUtils.isBlank(defaultDs)) {
log.error("==========>default datasource must be configured");
throw new RuntimeException("default datasource must be configured.");
}
if(! dataSources.containsKey(defaultDs)) { log.error("==========>The default datasource must be included in the datasources");
throw new RuntimeException("==========>The default datasource must be included in the datasources");
}
return dataSources.get(defaultDs);
}
/** Set the default data source *@param defaultDsStr
*/
public static void setDefaultDs(String defaultDsStr) {
defaultDs = defaultDsStr;
}
/** Get all data sources *@return* /
public static Map<String, DataSource> getDataSources(a) {
return dataSources;
}
/ * * *@return* /
public static String getDefaultDs(a) {
return defaultDs;
}
Copy the codeMybatisConfiguration:
This is the MybatisPlus configuration class, if you are using Mybatis is a little simpler. Because Mybatis only need to configure the SqlSessionFactory, while MybatisSqlSessionFactoryBean MybatisPlus is configuration
@Slf4j
@Configuration
@AutoConfigureAfter({DataSourceConfiguration.class})
@MapperScan(basePackages = {"com.sqt.edu.*.mapper*"."com.sqt.edu.*.api.mapper*"})
public class MybatisConfiguration {
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier(value = "myRoutingDataSource") MyRoutingDataSource myRoutingDataSource) throws
Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
return sqlSessionFactoryBean.getObject();
}
@Bean(name = "mybatisSqlSessionFactoryBean")
@Primary
public MybatisSqlSessionFactoryBean sqlSessionFactoryBean(@Qualifier(value = "myRoutingDataSource") DataSource dataSource) throws Exception {
log.info("= = = = = = = = = = > start injection MybatisSqlSessionFactoryBean");
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
Set<Resource> result = new LinkedHashSet<>(16);
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
try {
result.addAll(Arrays.asList(resolver.getResources("classpath*:mapper/*.xml")));
result.addAll(Arrays.asList(resolver.getResources("classpath*:config/mapper/*/*.xml")));
result.addAll(Arrays.asList(resolver.getResources("classpath*:mapper/*/*.xml")));
} catch (IOException e) {
log.error("Get [classpath: mapper / * / *. XML, the classpath: config/mapper / * / *. XML] resources error! Exception message :{}", e);
}
bean.setMapperLocations(result.toArray(new org.springframework.core.io.Resource[0]));
bean.setDataSource(dataSource);
bean.setVfs(SpringBootVFS.class);
com.baomidou.mybatisplus.core.MybatisConfiguration configuration = new com.baomidou.mybatisplus.core.MybatisConfiguration();
configuration.setLogImpl(StdOutImpl.class);
configuration.setMapUnderscoreToCamelCase(true);
// Add optimistic locking plugins
configuration.addInterceptor(optimisticLockerInterceptor());
bean.setConfiguration(configuration);
GlobalConfig globalConfig = GlobalConfigUtils.defaults();
// Set the field to be filled automatically
globalConfig.setMetaObjectHandler(new MyMetaObjectHandler());
bean.setGlobalConfig(globalConfig);
log.info("= = = = = = = = = = > injection MybatisSqlSessionFactoryBean done!");
returnbean; }}Copy the codeConfigured hereSqlSessionFactoryandMybatisSqlSessionFactoryBeanNeed to beMyRoutingDataSourceThis data source.
DsAspect:
Data source switch aspect configuration class
@Order(0)
@Aspect
@Component
@Slf4j
public class DsAspect {
/** * The pointcut for configuring the AOP aspect * switches to the methods of the service interface */
@Pointcut("execution(* com.sqt.. service.. *Service.*(..) )")
public void dataSourcePointCut(a) {}/** * Whether the call function is described with the TargetDataSource facet annotation based on the pointcut information, and if the data source is set, the data source switch is performed
@Before("dataSourcePointCut()")
public void before(JoinPoint joinPoint) {
if (StringUtils.isNotBlank(DbContextHolder.getCurrentDsStr())) {
log.info("==========>current thread {} use dataSource[{}]",
Thread.currentThread().getName(), DbContextHolder.getCurrentDsStr());
return;
}
String method = joinPoint.getSignature().getName();
Method m = ((MethodSignature) joinPoint.getSignature()).getMethod();
try {
if (null! = m && m.isAnnotationPresent(DS.class)) {// Switch data sources according to annotations
DS td = m.getAnnotation(DS.class);
String dbStr = td.value();
DbContextHolder.setCurrentDsStr(dbStr);
log.info("==========>current thread {} add dataSource[{}] to ThreadLocal, request method name is : {}",
Thread.currentThread().getName(), dbStr, method);
} else {
DbContextHolder.setCurrentDsStr(DbContextHolder.getDefaultDs());
log.info("==========>use default datasource[{}] , request method name is : {}", DbContextHolder.getDefaultDs(), method); }}catch (Exception e) {
log.error("==========>current thread {} add data to ThreadLocal error,{}", Thread.currentThread().getName(), e);
throwe; }}/** * After performing the section, the data source name in the thread share is cleared, * data source is restored to the original default data source */
@After("dataSourcePointCut()")
public void after(JoinPoint joinPoint) {
log.info("==========>clean datasource[{}]", DbContextHolder.getCurrentDsStr()); DbContextHolder.clearCurrentDsStr(); }}Copy the codeThis class is a simple aspect configuration that switches the data source before the Service method and defines a DS() annotation on the Service method to indicate whether it is master or slave.
Transaction configuration:
Here we go! Here we go! After the above configuration, the multiple data sources have been configured. But the transaction is not in effect at this time, whether you are@TransactionalDoes not apply to Service classes or methods! At this point you also need to configure a transaction manager, and theMyRoutingDataSourceWe customize the data source to the transaction manager. See TransactionConfig:
@Aspect
@Configuration
@Slf4j
public class TransactionConfig {
@Autowired
ConfigurableApplicationContext applicationContext;
private static final int TX_METHOD_TIMEOUT = 300;
private static final String AOP_POINTCUT_EXPRESSION = "execution(*com.sqt.. service.. *Service.*(..) )";
@Bean(name = "txAdvice")
public TransactionInterceptor txAdvice(a) {
NameMatchTransactionAttributeSource source = new NameMatchTransactionAttributeSource();
// Read only transaction, no update operation
RuleBasedTransactionAttribute readOnlyTx = new RuleBasedTransactionAttribute();
readOnlyTx.setReadOnly(true);
readOnlyTx.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
// Use the current transaction if it exists, or create a new transaction if it does not exist
RuleBasedTransactionAttribute requiredTx = new RuleBasedTransactionAttribute();
requiredTx.setRollbackRules(Collections.singletonList(new RollbackRuleAttribute(Exception.class)));
requiredTx.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
requiredTx.setTimeout(TX_METHOD_TIMEOUT);
Map<String, TransactionAttribute> txMap = new HashMap<>();
txMap.put("add*", requiredTx);
txMap.put("save*", requiredTx);
txMap.put("insert*", requiredTx);
txMap.put("create*", requiredTx);
txMap.put("update*", requiredTx);
txMap.put("batch*", requiredTx);
txMap.put("modify*", requiredTx);
txMap.put("delete*", requiredTx);
txMap.put("remove*", requiredTx);
txMap.put("exec*", requiredTx);
txMap.put("set*", requiredTx);
txMap.put("do*", requiredTx);
txMap.put("get*", readOnlyTx);
txMap.put("query*", readOnlyTx);
txMap.put("find*", readOnlyTx);
txMap.put("*", requiredTx);
source.setNameMap(txMap);
TransactionInterceptor txAdvice = new TransactionInterceptor(transactionManager(), source);
return txAdvice;
}
@Bean
public Advisor txAdviceAdvisor(@Qualifier("txAdvice") TransactionInterceptor txAdvice) {
AspectJExpressionPointcut pointcut = new AspectJExpressionPointcut();
pointcut.setExpression(AOP_POINTCUT_EXPRESSION);
return new DefaultPointcutAdvisor(pointcut, txAdvice);
}
/** The custom transaction manager manages our custom MyRoutingDataSource data source *@return* /
@Bean(name = "transactionManager")
public DataSourceTransactionManager transactionManager(a) {
DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(applicationContext.getBean(MyRoutingDataSource.class));
return transactionManager;
}
Copy the codeConfiguration DataSourceTransactionManager is key! ! ! Configuration DataSourceTransactionManager is key! ! !
Since I’m customizing the aspect configuration transaction, this code is a little longer. The point is to configure the transaction manager and give our dynamic routing data source (MyRoutingDataSource) to the transaction manager so that our transaction will roll back!
Conclusion:
- The focus of configuring multiple data sources is to customize a data source inheritance
AbstractRoutingDataSourceAnd register multiple data sources. - Transactions don’t work because Spring’s default transaction manager doesn’t take over our custom data source. The solution is to configure a transaction manager to stuff our custom data source into it