Usually, in business development, we use multiple data sources. For example, some data exist in mysql instances and some data are in oracle databases. At this time, the project is based on springboot and mybatis. In fact, we only need to configure two data sources according to

dataSource - SqlSessionFactory - SqlSessionTemplate can be configured.

In the following code, first, we configure a master data source, which is identified as a default data source through the @ Primary annotation, and through spring. In the configuration file Datasource is configured as a data source to generate a SqlSessionFactoryBean. Finally, a SqlSessionTemplate is configured.

 1 @Configuration
 2 @MapperScan(basePackages = "com.xxx.mysql.mapper", sqlSessionFactoryRef = "primarySqlSessionFactory")
 3 public class PrimaryDataSourceConfig {
 4 
 5     @Bean(name = "primaryDataSource")
 6     @Primary
 7     @ConfigurationProperties(prefix = "spring.datasource")
 8     public DataSource druid() {
 9         return new DruidDataSource();
10     }
11 
12     @Bean(name = "primarySqlSessionFactory")
13     @Primary
14     public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
15         SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
16         bean.setDataSource(dataSource);
17         bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
18         bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
19         return bean.getObject();
20     }
21 
22     @Bean("primarySqlSessionTemplate")
23     @Primary
24     public SqlSessionTemplate primarySqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sessionFactory) {
25         return new SqlSessionTemplate(sessionFactory);
26     }
27 }

Then, configure an oracle based data source according to the same process, configure basePackages through annotation, scan the corresponding package, implement the mapper interface under the specific package, and use the specific data source.

 1 @Configuration
 2 @MapperScan(basePackages = "com.nbclass.oracle.mapper", sqlSessionFactoryRef = "oracleSqlSessionFactory")
 3 public class OracleDataSourceConfig {
 4 
 5     @Bean(name = "oracleDataSource")
 6     @ConfigurationProperties(prefix = "spring.secondary")
 7     public DataSource oracleDruid(){
 8         return new DruidDataSource();
 9     }
10 
11     @Bean(name = "oracleSqlSessionFactory")
12     public SqlSessionFactory oracleSqlSessionFactory(@Qualifier("oracleDataSource") DataSource dataSource) throws Exception {
13         SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
14         bean.setDataSource(dataSource);
15         bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:oracle/mapper/*.xml"));
16         return bean.getObject();
17     }
18 
19     @Bean("oracleSqlSessionTemplate")
20     public SqlSessionTemplate oracleSqlSessionTemplate(@Qualifier("oracleSqlSessionFactory") SqlSessionFactory sessionFactory) {
21         return new SqlSessionTemplate(sessionFactory);
22     }
23 }

In this way, the function of using multiple data sources in one project is realized. In fact, this implementation method is simple enough. However, if there are many database instances and each instance is configured with master-slave configuration, maintenance here will inevitably lead to too many package names and inflexibility.

Now consider implementing a scheme that is small enough to invade the business and can support the specified data source in the granularity of the mapper method. Naturally, it comes to mind that it can be implemented through annotation. First, customize an annotation @ DBKey:

1 @Retention(RetentionPolicy.RUNTIME)
2 @Target({ElementType.METHOD, ElementType.TYPE})
3 public @interface DBKey {
4 
5     String DEFAULT = "default"; // Default database node
6 
7     String value() default DEFAULT;
8 }

The idea is similar to the above configuration based on the native springboot. First, define a default database node. When the mapper interface method / class does not specify any annotation, it will go to this node by default. The annotation supports passing in the value parameter to represent the name of the selected data source node. As for the implementation logic of annotation, you can obtain the annotation value of mapper interface method / class through reflection, and then specify a specific data source.

When will this operation be performed? You can consider weaving spring AOP into the mapper layer. Before the pointcut executes the specific mapper method, put the corresponding data source configuration into threaLocal. With this logic, you can implement it immediately:

First, define a context object for db configuration. Maintain all data source key instances and data source keys used by the current thread:

 1 public class DBContextHolder {
 2     
 3     private static final ThreadLocal<String> DB_KEY_CONTEXT = new ThreadLocal<>();
 4 
 5     //All data sources are loaded when the app starts, and concurrency does not need to be considered
 6     private static Set<String> allDBKeys = new HashSet<>();
 7 
 8     public static String getDBKey() {
 9         return DB_KEY_CONTEXT.get();
10     }
11 
12     public static void setDBKey(String dbKey) {
13         //The key must be in the configuration
14         if (containKey(dbKey)) {
15             DB_KEY_CONTEXT.set(dbKey);
16         } else {
17             throw new KeyNotFoundException("datasource[" + dbKey + "] not found!");
18         }
19     }
20 
21     public static void addDBKey(String dbKey) {
22         allDBKeys.add(dbKey);
23     }
24 
25     public static boolean containKey(String dbKey) {
26         return allDBKeys.contains(dbKey);
27     }
28 
29     public static void clear() {
30         DB_KEY_CONTEXT.remove();
31     }
32 }

Then, define the pointcut. In the pointcut before method, select the corresponding data source key according to the @ @ DBKey annotation of the current mapper interface:

 1 @Aspect
 2 @Order(Ordered.LOWEST_PRECEDENCE - 1)
 3 public class DSAdvice implements BeforeAdvice {
 4 
 5     @Pointcut("execution(* com.xxx..*.repository.*.*(..))")
 6     public void daoMethod() {
 7     }
 8 
 9     @Before("daoMethod()")
10     public void beforeDao(JoinPoint point) {
11         try {
12             innerBefore(point, false);
13         } catch (Exception e) {
14             logger.error("DefaultDSAdviceException",
15                     "Failed to set database key,please resolve it as soon as possible!", e);
16         }
17     }
18 
19     /**
20      * @param isClass Interception class or interface
21      */
22     public void innerBefore(JoinPoint point, boolean isClass) {
23         String methodName = point.getSignature().getName();
24 
25         Class<?> clazz = getClass(point, isClass);
26         //Use default data source
27         String dbKey = DBKey.DEFAULT;
28         Class<?>[] parameterTypes = ((MethodSignature) point.getSignature()).getMethod().getParameterTypes();
29         Method method = null;
30         try {
31             method = clazz.getMethod(methodName, parameterTypes);
32         } catch (NoSuchMethodException e) {
33             throw new RuntimeException("can't find " + methodName + " in " + clazz.toString());
34         }
35         //There are annotations on the method, and the datasource defined by the method is used
36         if (method.isAnnotationPresent(DBKey.class)) {
37             DBKey key = method.getAnnotation(DBKey.class);
38             dbKey = key.value();
39         } else {
40             //There is no annotation on the method. Use the annotation defined on the class
41             clazz = method.getDeclaringClass();
42             if (clazz.isAnnotationPresent(DBKey.class)) {
43                 DBKey key = clazz.getAnnotation(DBKey.class);
44                 dbKey = key.value();
45             }
46         }
47         DBContextHolder.setDBKey(dbKey);
48     }
49 
50 
51     private Class<?> getClass(JoinPoint point, boolean isClass) {
52         Object target = point.getTarget();
53         String methodName = point.getSignature().getName();
54 
55         Class<?> clazz = target.getClass();
56         if (!isClass) {
57             Class<?>[] clazzList = target.getClass().getInterfaces();
58 
59             if (clazzList == null || clazzList.length == 0) {
60                 throw new MutiDBException("can't find mapper class,methodName =" + methodName);
61             }
62             clazz = clazzList[0];
63         }
64 
65         return clazz;
66     }
67 }

Since the data source finally used by the mapper interface has been put into threadLocal before the mapper is executed, you only need to rewrite the new routing data source interface logic:

 1 public class RoutingDatasource extends AbstractRoutingDataSource {
 2 
 3     @Override
 4     protected Object determineCurrentLookupKey() {
 5         String dbKey = DBContextHolder.getDBKey();
 6         return dbKey;
 7     }
 8 
 9     @Override
10     public void setTargetDataSources(Map<Object, Object> targetDataSources) {
11         for (Object key : targetDataSources.keySet()) {
12             DBContextHolder.addDBKey(String.valueOf(key));
13         }
14         super.setTargetDataSources(targetDataSources);
15         super.afterPropertiesSet();
16     }
17 }

In addition, when we start the service and configure mybatis, we load all db configurations:

 1 @Bean
 2     @ConditionalOnMissingBean(DataSource.class)
 3     @Autowired
 4     public DataSource dataSource(MybatisProperties mybatisProperties) {
 5         Map<Object, Object> dsMap = new HashMap<>(mybatisProperties.getNodes().size());
 6         for (String nodeName : mybatisProperties.getNodes().keySet()) {
 7             dsMap.put(nodeName, buildDataSource(nodeName, mybatisProperties));
 8             DBContextHolder.addDBKey(nodeName);
 9         }
10         RoutingDatasource dataSource = new RoutingDatasource();
11         dataSource.setTargetDataSources(dsMap);
12         if (null == dsMap.get(DBKey.DEFAULT)) {
13             throw new RuntimeException(
14                     String.format("Default DataSource [%s] not exists", DBKey.DEFAULT));
15         }
16         dataSource.setDefaultTargetDataSource(dsMap.get(DBKey.DEFAULT));
17         return dataSource;
18     }
19 
20 
21 
22 @ConfigurationProperties(prefix = "mybatis")
23 @Data
24 public class MybatisProperties {
25 
26     private Map<String, String> params;
27 
28     private Map<String, Object> nodes;
29 
30     /**
31      * mapper File path: multiple location s separated by
32      */
33     private String mapperLocations = "classpath*:com/iqiyi/xiu/**/mapper/*.xml";
34 
35     /**
36      * Mapper The base package of the class
37      */
38     private String basePackage = "com.iqiyi.xiu.**.repository";
39 
40     /**
41      * mybatis Profile path
42      */
43     private String configLocation = "classpath:mybatis-config.xml";
44 }

When will the key in threadLocal be destroyed? In fact, you can customize an Interceptor Based on mybatis and actively call dbcontextholder in the interceptor The clear () method destroys the key. The specific code will not be posted. In this way, we have completed an annotation based Middleware Supporting Multi data source switching.

Is there any point that can be optimized? In fact, it can be found that reflection is used to obtain the annotation of the mapper interface / class. We know that reflection calls generally consume performance, so we can consider adding a local cache here to optimize the performance:

 1     private final static Map<String, String> METHOD_CACHE = new ConcurrentHashMap<>();
 2 //.... 
 3 public void innerBefore(JoinPoint point, boolean isClass) {
 4         String methodName = point.getSignature().getName();
 5 
 6         Class<?> clazz = getClass(point, isClass);
 7         //key is the class name + method name
 8         String keyString = clazz.toString() + methodName;
 9         //Use default data source
10         String dbKey = DBKey.DEFAULT;
11         //If the key of the data source corresponding to the mapper method already exists in the cache, set it directly
12         if (METHOD_CACHE.containsKey(keyString)) {
13             dbKey = METHOD_CACHE.get(keyString);
14         } else {
15             Class<?>[] parameterTypes =
16                     ((MethodSignature) point.getSignature()).getMethod().getParameterTypes();
17             Method method = null;
18 
19             try {
20                 method = clazz.getMethod(methodName, parameterTypes);
21             } catch (NoSuchMethodException e) {
22                 throw new RuntimeException("can't find " + methodName + " in " + clazz.toString());
23             }
24              //There are annotations on the method, and the datasource defined by the method is used
25             if (method.isAnnotationPresent(DBKey.class)) {
26                 DBKey key = method.getAnnotation(DBKey.class);
27                 dbKey = key.value();
28             } else {
29                 clazz = method.getDeclaringClass();
30                 //Use annotations defined on the class
31                 if (clazz.isAnnotationPresent(DBKey.class)) {
32                     DBKey key = clazz.getAnnotation(DBKey.class);
33                     dbKey = key.value();
34                 }
35             }
36            //Put local cache first
37             METHOD_CACHE.put(keyString, dbKey);
38         }
39         DBContextHolder.setDBKey(dbKey);
40     }

In this way, only when the mapper interface is called for the first time, the logic of reflection call will be used to obtain the corresponding data source, and then the local cache will be used to improve the performance.