This is the 10th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

background

When the amount of data is too large, the operation of updating and querying the single table will sometimes lead to the lock of the table, so that the read and write speed can not keep up, a page will take 2-3 seconds, which requires the use of read and write separation. In many applications, database read operations are more intensive than write operations, and the query conditions are relatively complex, so most of the database performance is consumed in the query operation. In order to ensure the consistency of database data, we require that all database update operations are for the master database, and read operations are performed from the database.

code

The configuration file

Add a database configuration for dual data sources

spring:
  datasource:
      datasource1:
        url: jdbc:mysql://localhost:3306/user? useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true&useAffectedRows =true
        username: admin
        password: 1024571
        driver-class-name: com.mysql.jdbc.Driver
        filters: stat,wall
        initial-size: 1
        min-idle: 1
        max-active: 20
        max-wait: 60000
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 30000
        validation-query: SELECT 'x'
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        pool-prepared-statements: false
        max-pool-prepared-statement-per-connection-size: 20
	  datasource2:
        url: jdbc:mysql://localhost:3307/user? useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true&useAffectedRows =true
        username: admin
        password: 1024571
        driver-class-name: com.mysql.jdbc.Driver
        filters: stat,wall
        initial-size: 1
        min-idle: 1
        max-active: 20
        max-wait: 60000
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 30000
        validation-query: SELECT 'x'
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        pool-prepared-statements: false
        max-pool-prepared-statement-per-connection-size: 20	
Copy the code
Custom annotations

Custom data source key annotation, value is the data source key

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface DataSource {
    String value(a) default "data1";  
}
Copy the code
Data source Key Settings
@Slf4j
public class DataSourceContextHolder {

    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    // Set the data source name
    public static void setDataSource(String dataSource){
        contextHolder.set(dataSource);
    }

    public static String getDataSource(a){
        return contextHolder.get();
    }

    // Clear the data source
    public static void clearDataSource(a){ contextHolder.remove(); }}Copy the code
Dynamic data source class
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey(a) {
        returnDataSourceContextHolder.getDataSource(); }}Copy the code
Data source configuration class

Define key and bean mappings for dual data sources

@Configuration
public class DataSourceConfig {
    /** * Data source 1 */
    @Bean(name = "data1")
    @ConfigurationProperties(prefix = "spring.datasource.data1")
    public DataSource Data1(a){
        return DataSourceBuilder.create().build();
    }

    /** * Data source 2 */
    @Bean(name = "data2")
    @ConfigurationProperties(prefix = "spring.datasource.data2")
    public DataSource Data2(a){
        return DataSourceBuilder.create().build();
    }

    /** * Data source switching: Dynamically switch between different data sources using AOP */
    @Primary
    @Bean
    public DataSource dynamicDataSource(a){

        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        // Set the default data source
        dynamicDataSource.setDefaultTargetDataSource(Data1());
        // Configure multiple data sources
        Map<Object,Object> dsMap = new HashMap<>();
        dsMap.put("data1",Data1());
        dsMap.put("data2",Data2());

        dynamicDataSource.setTargetDataSources(dsMap);
        return dynamicDataSource;
    }

    / * * * configuration@TransactionalAnnotation transaction@return* /
    @Bean
    public PlatformTransactionManager transactionManager(a) {
        return newDataSourceTransactionManager(dynamicDataSource()); }}Copy the code
Custom section

The aspect implementation method switches between different data sources using values in annotations.

@Aspect
@Component
public class DataSourceAspect {
    @Pointcut("@annotation(com.wqy.data.annotation.DataSource)")
    public void pointcutConfig(a){}@Before("pointcutConfig()")
    public void before(JoinPoint joinPoint){
        // Get the class currently accessedClass<? > className = joinPoint.getTarget().getClass();// Get the method name to access
        String methodName = joinPoint.getSignature().getName();
        // Get the type of the method's argument
        Class[] argClass = ((MethodSignature)joinPoint.getSignature()).getParameterTypes();

        String dataSource = null;
        try {
            // Get the method object to access
            Method method = className.getMethod(methodName, argClass);
            // Check whether the @datasource annotation exists
            if (method.isAnnotationPresent(DataSource.class)) {
                DataSource annotation = method.getAnnotation(DataSource.class);
                // Fetch the data source name in the annotationdataSource = annotation.value(); }}catch (Exception e) {
            e.printStackTrace();
        }
        // Set the data source key
        DataSourceContextHolder.setDataSource(dataSource);
    }

    @After("pointcutConfig()")
    public void after(JoinPoint joinPoint){ DataSourceContextHolder.clearDataSource(); }}Copy the code
Using annotations

By declaring data sources with custom data source annotations on top of methods, you can implement different methods and different data source calls.

    @DataSource("dataSource1")
    public void queryUser(a) {
        userMapper.select();
    }
Copy the code

The principle of analytic

The method is cut through AOP, the value in the annotation is obtained and set as the data source Key, and the database bean corresponding to the data source is obtained through the data source configuration class, and then the data source switch is realized.

AbstractRoutingDataSource

AbstractRoutingDataSource AbstractDataSource inheritance, if a statement after a class DynamicDataSource AbstractRoutingDataSource inheritance, A DynamicDataSource is itself a data source. Therefore AbstractRoutingDataSource will have getConnection () method to obtain the database connection.

For general process, through determineCurrentLookupKey method to obtain a key, through the key obtained from resolvedDataSources DataSource object. DetermineCurrentLookupKey () is an abstract method, we need to inherit AbstractRoutingDataSource class implements; The resolvedDataSources is a Map
, which should save all the current switchable data sources.,>


protected DataSource determineTargetDataSource(a) {
    Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
    Object lookupKey = determineCurrentLookupKey();
    DataSource dataSource = this.resolvedDataSources.get(lookupKey);
    if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
        dataSource = this.resolvedDefaultDataSource;
    }
    if (dataSource == null) {
        throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
    }
    returndataSource; }Copy the code