Spring Mybatis multi-data source

Recently in the project encountered the need to separate read and write requirements optimization, here just record the implementation process and encountered problems and some of my own expansion (wit like me). The way to do this is with some common solutions:

1 inheritance AbstractRoutingDataSource rewrite determineCurrentLookupKey method

Switch data sources using custom annotation AOP

Using the above method can basically complete the read and write separation of multiple data sources, so what are we waiting for?

1 Customize dynamic data sources

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();
  }
}
Copy the code

Turn it over to Spring

@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); / / had better not exceed cpus basicDataSource. SetMaxIdle (5); / / number of connection pool Max idle basicDataSource setMinIdle (3); Minimum number of idle basicDataSource. / / connection pool setInitialSize (10); / / initialize the connection pool connection number basicDataSource. SetMaxConnLifetimeMillis (60000); basicDataSource.setRemoveAbandonedTimeout(30); return basicDataSource; } // @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; 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); SqlSessionFactory (@qualifier ("dataSource") RoutingDataSource)  { SqlSessionFactory sessionFactory = null; try { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); sessionFactory = bean.getObject(); } catch (Exception e) { e.printStackTrace(); } return sessionFactory; }Copy the code

As you can see, you define two data sources: master and slave

2 Custom annotations

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
  String value() default "master";
}
Copy the code

Custom annotations we should be able to, here the code as above do not do too much explanation, custom annotations do not know to go to Baidu!!

The data source switch not only uses aop’s aspect programming, but also uses ThreadLocal to store which database is being 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(); }}Copy the code

Define a database name that holds the current thread in a holder, and then define the facets.

@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());
  }
}
Copy the code

The main annotation here is to implement the database to be used into threadLocal, to implement the data source switch in the RoutingDataSource

Usage Examples:

 @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");
  }
Copy the code

Use the same code as above so you can happily switch data sources. But aop also has some problems, such as the following code can cause problems, in fact, the dynamic proxy problem is that the dynamic proxy can not be used in the same class method nested methods, said a little bit of the following code will know.

 @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();
  }
Copy the code

Do you have any ideas or solutions after reading the above code? This problem can occur when dynamic proxy is not used properly. Give you 30 seconds to think about it.

Spring generates a proxy class for TestService when the getNickName() method is called. The proxy class performs the business in the section first. If we switch the data source, then this executes getNickName, which is the real class to call getNickName 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 that the data source cannot be switched. No AOP is immune to the above problems

The e question is, what to do? I need this way to call that how to solve!

The solution is as follows

@Service
@EnableAspectJAutoProxy(proxyTargetClass = true, exposeProxy = true)
public class TestService {
Copy the code

Add the @enableAspectJAutoProxy (proxyTargetClass = True, exposeProxy = True) switch to the class in the method

public void insert(){
    TestService currentclass= (TestService ) AopContext.currentProxy();
    currentclass.insertLive();
    currentclass.insertUser();
  }
Copy the code

If you look at the code it’s not hard to understand that you’re just going to get the proxy class and call these internal methods from the proxy class

The above approach is fine, but what if we don’t implement read/write separation and are really multi-data sources? Does the above method still work well in most cases, but if a transaction is introduced, there will be a problem. It will not be pleasant to switch data sources when joining a transaction.

Spring Mybatis spring Mybatis Spring Mybatis Spring Mybatis Spring Mybatis

Ha ha ha ha ha ha above is the content I share, if what say wrong place, timely give me a message I correct immediately, if mislead the other is learning of the children, that old man is guilty!!