#Multiple data sources under spring mybatis
Recently, I encountered the requirement optimization of separation of reading and writing in the project. Here I just record the implementation process, problems and some extensions (smart like me). The implementation method is some public schemes:
* * overriding datalookcurrentdetail1 * *
2. Switch data sources by using user-defined annotation aop
Using the above methods, we can basically complete the read-write separation of multiple data sources. What else?
1. Customize dynamic data source
```
public class RoutingDataSource extends AbstractRoutingDataSource {
public RoutingDataSource(DataSource master,DataSource slave) {
setTargetDataSources(ImmutableMap.builder().put("master",master).put("slave",slave).build());
}
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDataSourceKey();
}
}
```spring management
```
@Configuration
public class DataSourceConfig {
@Autowired
private Props props;
@Primary
@Bean("master")
DataSource masterDataSource() {
return dataSource(props.getMasterUrl(),props.getMasterUsername(),props.getMasterPassword());
}
@Bean("slave")
DataSource slaveDataSource() {
return dataSource(props.getSlaveUrl(),props.getSlaveUsername(),props.getSlavePassword());
}
private DataSource dataSource(String url, String user, String pwd) {
BasicDataSource basicDataSource = new BasicDataSource();
basicDataSource.setUrl(url);
basicDataSource.setUsername(user);
basicDataSource.setPassword(pwd);
basicDataSource.setMaxTotal(24); // It's best not to exceed the number of CPUs
basicDataSource.setMaxIdle(5); // Maximum free number of connection pool
basicDataSource.setMinIdle(3); // Minimum free number of connection pools
basicDataSource.setInitialSize(10); // Number of connections when initializing the connection pool
basicDataSource.setMaxConnLifetimeMillis(60000);
basicDataSource.setRemoveAbandonedTimeout(30);
return basicDataSource;
}
//Distributed transaction XA
// @Primary
// @Bean("master")
// DataSource masterDataSource() {
// return atomicDataSource(props.getMasterUrl(),props.getMasterUsername(),props.getMasterPassword(),"master");
// }
// @Bean("slave")
// DataSource slaveDataSource() {
// return atomicDataSource(props.getSlaveUrl(),props.getSlaveUsername(),props.getSlavePassword(),"slave");
// }
// private AtomikosDataSourceBean atomicDataSource(String url, String user, String pwd,String name) {
// AtomikosDataSourceBean atomikosDataSourceBean = new AtomikosDataSourceBean();
// atomikosDataSourceBean.setUniqueResourceName(name);
// atomikosDataSourceBean.setXaDataSourceClassName(
// "com.mysql.cj.jdbc.MysqlXADataSource");
// Properties properties = new Properties();
// properties.put("URL",url);
// properties.put("user", user);
// properties.put("password", pwd);
// atomikosDataSourceBean.setXaProperties(properties);
// return atomikosDataSourceBean;
// }
//Dynamic data source
@Bean("dataSource")
public RoutingDataSource dataSource(@Qualifier("master") DataSource master,@Qualifier("slave") DataSource slave) {
// return new RoutingDataSource(dataSource(props.getMasterUrl(),props.getMasterUsername(),props.getMasterPassword()),dataSource(props.getSlaveUrl(),props.getSlaveUsername(),props.getSlavePassword()));
return new RoutingDataSource(master,slave);
}
//Define the dynamic session of mybatis
@Bean
SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") RoutingDataSource dataSource) {
SqlSessionFactory sessionFactory = null;
try {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
sessionFactory = bean.getObject();
} catch (Exception e) {
e.printStackTrace();
}
return sessionFactory;
}
```You can see that two data sources, master data source and slave data source, are defined first
2. User defined annotation
```
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
String value() default "master";
}
```Everyone should know the custom annotation. The code here is like the above. Don't explain too much. If you don't know the custom annotation, hurry to Baidu!!!
3 aop
This data source switching not only uses the aspect programming of aop, but also uses ThreadLocal to save the database currently used
public class DataSourceContextHolder {
private static final ThreadLocal<String> contextHolder = ThreadLocal.withInitial(() -> "master");
public static void setDataSourceKey(String key) {
contextHolder.set(key);
}
public static String getDataSourceKey() {
return contextHolder.get();
}
public static void clearDataSourceKey() {
contextHolder.remove();
}
}Define a holder to store the database name of the current thread, and then define the section
```
@Aspect
@Component
public class DataSourceAspect {
private final Logger logger = LoggerFactory.getLogger(getClass());
private static final List<String> DATA_SOURCE_KEYS = Arrays.asList("master", "slave");
@Before("@annotation(targetDataSource)")
public void switchDataSource(JoinPoint joinPoint,TargetDataSource targetDataSource){
if (!DATA_SOURCE_KEYS.contains(targetDataSource.value())) {
logger.error(
"datasource [{}] doesn't exist, use default datasource [{}]", targetDataSource.value());
} else {
DataSourceContextHolder.setDataSourceKey(targetDataSource.value());
logger.info(
"switch datasource to [{}] in method [{}]",
DataSourceContextHolder.getDataSourceKey(),
joinPoint.getSignature());
}
}
@After("@annotation(targetDataSource))")
public void restoreDataSource(JoinPoint point, TargetDataSource targetDataSource) {
DataSourceContextHolder.clearDataSourceKey();
logger.info(
"restore datasource to [{}] in method [{}]",
DataSourceContextHolder.getDataSourceKey(),
point.getSignature());
}
}
```Here is mainly the annotation of the section to put the database to be used into threadLocal to switch the data source of RoutingDataSource
Usage example:
```
@TargetDataSource("master")
// @Transactional
public String getNickName(){
// String title = getLiveTitle();
System.out.println(title);
return userMapper.getUserNameById(2022L);
}
@TargetDataSource("slave")
public String getLiveTitle(){
return liveMapper.getLiveName("83a09baea4c7");
}
```The usage is the same as the above code, so you can switch the data source happily. However, aop also has some problems. For example, there will be problems with codes like the following. In fact, the problem of dynamic proxy is that dynamic proxy cannot proxy nested methods of methods in the same class. It's a little unclear. Just look at the following code.
```
@Service
public class TestService {
@Autowired
private UserMapper userMapper;
@Autowired
private LiveMapper liveMapper;
@Autowired
private LiveUserMapper liveUserMapper;
@Autowired
private Test3Service test3Service;
@TargetDataSource("master")
// @Transactional
public String getNickName(){
String title = getLiveTitle();
System.out.println(title);
return userMapper.getUserNameById(2022L);
}
public String getSalveTest(){
return getLiveTitle();
}
```After reading the above code, do you have any ideas or solutions? This problem will occur if the dynamic agent is not used properly. Let's give you 30 seconds to think about what to do...
The reason for this problem is that when calling the getNickName() method, spring generates an agent class for TestService. In the proxy class, we first execute the business in the cut plane, such as our data source, then this executes getNickName, which is the getNickName that the real class calls and then calls its internal getLiveTitle() method. If getlivetitle is not called by the proxy class, there will be no operation to switch the data source, so an error will be reported and the data source cannot be switched.
aop cannot avoid the above problems
The problem is coming. What should I do?? I need to call in this way, so how to solve it!
The solution is as follows
```
@Service
@EnableAspectJAutoProxy(proxyTargetClass = true, exposeProxy = true)
public class TestService {
```
Add on class @EnableAspectJAutoProxy(proxyTargetClass = true, exposeProxy = true)This switch is then in the method
```
public void insert(){
TestService currentclass= (TestService ) AopContext.currentProxy();
currentclass.insertLive();
currentclass.insertUser();
}
```It's not hard to understand from the code. It's just to get the proxy class and call these internal methods through the proxy class
The above methods can realize read-write separation, but what if we don't realize read-write separation and multiple data sources? Can the above method still be used
In fact, most cases are OK, but if transactions are introduced, there will be problems. When adding transactions, you can't happily switch data sources.
What should I do? Don't worry. Welcome to my next chapter * * spring mybatis multi data source opening transaction, solutions to data source failure and source code analysis**
Hahaha, the above is what I share. If anything is wrong, leave me a message in time and I will correct it immediately. If I mislead other children who are learning, I will be sinful, hahaha!!!
