Pom dependency
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.2.RELEASE</version>
<relativePath/>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.14</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<!-- Paging plugin -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.3.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
</dependencies>
application.yml
# data source
spring:
datasource:
# master main data source configuration
master:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds1?characterEncoding=UTF-8 & serverTimezone=GMT+8
username: root
password: root
# cluster is configured from the data source
cluster:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds2?characterEncoding=UTF-8 & serverTimezone=GMT+8
username: root
password: root
#pagehelperPaging plug-in configuration
pagehelper:
master:
helper-dialect: mysql #Set database type
reasonable: true #Turn on rationalization: page number <=0 to query the first page, page number >= total number of pages to query the last page
support-methods-arguments: true #Support passing paging parameters through Mapper interface parameters
params: count=countsql
Multiple data source configuration
Create master-slave data source configuration in Java code. Use @Primary: to specify the injection priority of the bean. Bean objects modified by @Primary are injected first
MasterDataSourceConfig
/**
* @ClassName: MasterDataSourceConfig
* @Description: mysql main library configuration class
*/
@Configuration
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE,sqlSessionTemplateRef = "masterSqlSessionTemplate")
public class MasterDataSourceConfig {
/**
* Set the path to scan the package
*/
public static final String PACKAGE = "cn.zysheep.dao.master";
/**
* Mapper configuration file path
*/
public static final String MAPPER_LOCATION = "classpath*:mapper/master/*.xml";
/**
* Entity alias
*/
public static final String ENTITY_ALIASES = "cn.zysheep.entity";
@Value("${spring.datasource.master.driverClassName}")
private String driverClassName;
@Value("${spring.datasource.master.url}")
private String url;
@Value("${spring.datasource.master.username}")
private String user;
@Value("${spring.datasource.master.password}")
private String password;
@Value("${pagehelper.master.helper-dialect}")
private String helperDialect;
@Value("${pagehelper.master.reasonable}")
private String reasonable;
/**
*Create data source
* @return DataSource
* @Primary: used to specify the injection priority of the bean. Bean objects modified by @Primary are injected first
*/
@Bean(name = "masterDataSource")
@Primary
public DataSource masterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClassName);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
/**
* Create factory
*@param dataSource
*@throwsException
*@return SqlSessionFactory
*/
@Bean(name = "masterSqlSessionFactory")
@Primary
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
sessionFactory.setTypeAliasesPackage(ENTITY_ALIASES);
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
//The field value can also be inserted if it is empty
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setCallSettersOnNulls(true);
// mybatis log
configuration.setLogImpl(StdOutImpl.class);
sessionFactory.setConfiguration(configuration);
PageInterceptor pageInterceptor = new PageInterceptor();
//Paging configuration
Properties p = new Properties();
p.setProperty("helperDialect", helperDialect);
p.setProperty("reasonable", reasonable);
pageInterceptor.setProperties(p);
sessionFactory.setPlugins(pageInterceptor);
return sessionFactory.getObject();
}
/**
* Create transaction
*@param dataSource
*@return DataSourceTransactionManager
*/
@Bean(name = "masterTransactionManager")
@Primary
public DataSourceTransactionManager masterDataSourceTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
/**
* Create template
*@param sqlSessionFactory
*@return SqlSessionTemplate
*/
@Bean(name = "masterSqlSessionTemplate")
@Primary
public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
ClusterDataSourceConfig
/**
* @ClassName: DataSourceConfig
* @Description: mysql slave library configuration class
*/
@Configuration
@MapperScan(basePackages = ClusterDataSourceConfig.PACKAGE,sqlSessionTemplateRef = "clusterSqlSessionTemplate")
public class ClusterDataSourceConfig {
/**
* Set the path to scan the package
*/
public static final String PACKAGE = "cn.zysheep.dao.cluster";
/**
* Mapper configuration file path
*/
public static final String MAPPER_LOCATION = "classpath*:mapper/cluster/*.xml";
/**
* Entity alias
*/
public static final String ENTITY_ALIASES = "cn.zysheep.entity";
@Value("${spring.datasource.cluster.driverClassName}")
private String driverClassName;
@Value("${spring.datasource.cluster.url}")
private String url;
@Value("${spring.datasource.cluster.username}")
private String user;
@Value("${spring.datasource.cluster.password}")
private String password;
@Value("${pagehelper.master.helper-dialect}")
private String helperDialect;
@Value("${pagehelper.master.reasonable}")
private String reasonable;
/**
*Create data source
*@return DataSource
*/
@Bean(name = "clusterDataSource")
public DataSource masterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClassName);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
return dataSource;
}
/**
* Create factory
*@param dataSource
*@throwsException
*@return SqlSessionFactory
*/
@Bean(name = "clusterSqlSessionFactory")
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("clusterDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
sessionFactory.setTypeAliasesPackage(ENTITY_ALIASES);
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
//The field value can also be inserted if it is empty
configuration.setJdbcTypeForNull(JdbcType.NULL);
configuration.setCallSettersOnNulls(true);
// mybatis log
configuration.setLogImpl(StdOutImpl.class);
sessionFactory.setConfiguration(configuration);
PageInterceptor pageInterceptor = new PageInterceptor();
//Paging configuration
Properties p = new Properties();
p.setProperty("helperDialect", helperDialect);
p.setProperty("reasonable", reasonable);
pageInterceptor.setProperties(p);
sessionFactory.setPlugins(pageInterceptor);
return sessionFactory.getObject();
}
/**
* Create transaction manager
*@param dataSource
*@return DataSourceTransactionManager
*/
@Bean(name = "clusterTransactionManager")
public DataSourceTransactionManager masterDataSourceTransactionManager(@Qualifier("clusterDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
/**
* Create template
*@param sqlSessionFactory
*@return SqlSessionTemplate
*/
@Bean(name = "clusterSqlSessionTemplate")
public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("clusterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
Startup class
Add the @EnableTransactionManagement annotation to the startup class to enable annotation transactions
@EnableTransactionManagement
@SpringBootApplication(exclude = PageHelperAutoConfiguration.class)
public class MultiDataSourceApplication {
public static void main(String[] args) {
SpringApplication.run(MultiDataSourceApplication.class, args);
}
}
Use
Add the @Transactional annotation to the service layer class or method. And specify the value of the attribute transaction manager transactionManager, and select different data transaction managers according to the different data sources used.
@Transactional(transactionManager = "masterTransactionManager")
public void insertUser() {
// TODO add user business operation
}
@Transactional(transactionManager = "clusterTransactionManager")
public void listUser() {
// TODO query user business operations
}
Thinking: @Transactional can only specify one transaction manager, and annotations are not allowed to be repeated, so you can only use the transaction manager of one data source. So what should we do if a method involves multiple data source operations and needs to ensure transaction consistency?
Implement cross-data source transactions
Using programmatic transactions to implement cross-data source transactions based on annotations + AOP
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface MultiDataSourceTransactional {
/**
*Transaction manager array
*/
String[] transactionManagers();
}
First, transactions from multiple data sources are opened separately, and then each transaction executes the corresponding SQL (this is the so-called first-stage commit). Finally, if all transactions are successful, all transactions will be submitted. As long as one fails, all transactions will be rolled back. –This is the so-called second stage submission.
/**
*Points to note:
* 1) The order of declaring transactions and committing transactions or rolling back transactions should be reversed, that is, first in, last out, so a stack is used for storage.
*2) Remember to clear local variables after the thread execution ends.
* 3) You can refer to the attribute upgrade functions of @Transactional, such as isolation level rollback exceptions, etc.
*/
@Component
@Aspect
public class MultiTransactionAop {
/**
* Thread local variables: why use the stack? ※In order to achieve the last-in-first-out effect※
*/
private static final ThreadLocal<Stack<Map<DataSourceTransactionManager, TransactionStatus>>> THREAD_LOCAL = new ThreadLocal<>();
private static Logger logger = LoggerFactory.getLogger(MultiTransactionAop.class);
/**
* Used to get the transaction manager
*/
@Autowired
private ApplicationContext applicationContext;
/**
* Statement of affairs
*/
private DefaultTransactionDefinition def = new DefaultTransactionDefinition();
{
// non-read-only mode
def.setReadOnly(false);
//Transaction isolation level: using database
def.setIsolationLevel(TransactionDefinition.ISOLATION_DEFAULT);
// Transaction propagation behavior
def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
}
/**
* Cut-off point
*/
@Pointcut("@annotation(cn.zysheep.datasource.anntation.MultiDataSourceTransactional)")
public void pointcut() {
}
/**
*Declaration transaction
*
* @param transactional annotation
*/
@Before("pointcut() & amp; & amp; @annotation(transactional)")
public void before(MultiDataSourceTransactional transactional) {
//Declare in order according to the set transaction name and put it in ThreadLocal
String[] transactionManagerNames = transactional.transactionManagers();
Stack<Map<DataSourceTransactionManager, TransactionStatus>> pairStack = new Stack<>();
for (String transactionManagerName : transactionManagerNames) {
DataSourceTransactionManager transactionManager = applicationContext.getBean(transactionManagerName, DataSourceTransactionManager.class);
TransactionStatus transactionStatus = transactionManager.getTransaction(def);
Map<DataSourceTransactionManager, TransactionStatus> transactionMap = new HashMap<>();
transactionMap.put(transactionManager, transactionStatus);
pairStack.push(transactionMap);
}
THREAD_LOCAL.set(pairStack);
}
/**
* Post enhancement, equivalent to AfterReturningAdvice, executed when the method exits
*
* Submit transaction
*/
@AfterReturning("pointcut()")
public void afterReturning() {
// ※Pop from the top of the stack (last in, first out)
Stack<Map<DataSourceTransactionManager, TransactionStatus>> pairStack = THREAD_LOCAL.get();
while (!pairStack.empty()) {
Map<DataSourceTransactionManager, TransactionStatus> pair = pairStack.pop();
pair.forEach((key,value)->key.commit(value));
}
THREAD_LOCAL.remove();
}
/**
* Exception throwing enhancement, equivalent to ThrowsAdvice
*
* Rollback transaction
*/
@AfterThrowing(value = "pointcut()")
public void afterThrowing() {
// ※Pop from the top of the stack (last in, first out)
Stack<Map<DataSourceTransactionManager, TransactionStatus>> pairStack = THREAD_LOCAL.get();
logger.info("=========================");
logger.info("Pair Stack:{}", pairStack);
logger.info("=========================");
while (!pairStack.empty()) {
Map<DataSourceTransactionManager, TransactionStatus> pair = pairStack.pop();
pair.forEach((key,value)->key.rollback(value));
}
THREAD_LOCAL.remove();
}
}
Add our previous multi-data source transaction annotation to the transaction method
/**
* Test multiple data source transactions
*/
@MultiDataSourceTransactional(transactionManagers = {"masterTransactionManager", "clusterTransactionManager"})
@Override
public void saveUser() {
String rid = UUID.randomUUID().toString();
User user = new User();
user.setId(rid);
userMapper.saveUser(user);
Log log = new Log();
log.setId(rid);
logMapper.saveLog(log);
throw new RuntimeException();
}
This concludes this article about SpringBoot integrating Mybatis to implement multi-data source configuration and cross-data source transaction instances.
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Cloud native entry-level skills treeHomepageOverview 16743 people are learning the system