Business background

E-commerce order items are divided into two parts: forward database records the basic information of the order, including basic information of the order, product information of the order, discount volume information, invoice information, account period information, settlement information, order remarks information, consignee information, etc. The reverse database mainly contains the return information and maintenance information of goods. When the amount of data exceeds 5 million lines, it is necessary to consider the separation of database, table and read and write. Then, when we operate forward and reverse operations, we need to dynamically switch to the corresponding database for related operations.

solution

Now the structure design of the project is basically based on MVC, so the operation of the database is centralized in the DAO layer, the main business logic is processed in the Service layer, and the controller layer processes the request. Assuming we can replace the DataSource with the one we want to perform the operation on before executing the DAO layer code, this problem is solved

Environment Preparation:

1. The entity class

@Data
public class Product {    
    private Integer id;    
    private String name;    
    private Double price;
}
Copy the code

2.ProductMapper 

public interface ProductMapper { 
    @Select("select * from product") 
    public List<Product> findAllProductM(); 
    @Select("select * from product") 
    public List<Product> findAllProductS(); 
} 
Copy the code

3.ProductService

@Service public class ProductService { @Autowired private ProductMapper productMapper; Public void findAllProductM () {/ / query Master List < Product > allProductM = productMapper. FindAllProductM (); System.out.println(allProductM); } public void findAllProductS () {/ / query Slave List < Product > allProductS. = productMapper findAllProductS (); System.out.println(allProductS); }}Copy the code

The specific implementation

Step 1: Configure multiple data sources

First, we configure two data sources in application.properties

spring.druid.datasource.master.password=root 
spring.druid.datasource.master.username=root 
spring.druid.datasource.master.jdbc- url=jdbc:mysql://localhost:3306/product_master? useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC 
spring.druid.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver 

spring.druid.datasource.slave.password=root 
spring.druid.datasource.slave.username=root 
spring.druid.datasource.slave.jdbc- url=jdbc:mysql://localhost:3306/product_slave? useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC 
spring.druid.datasource.slave.driver-class-name=com.mysql.cj.jdbc.Driver
Copy the code

In the SpringBoot configuration code, we initialize two data sources:

@Configuration public class MyDataSourceConfiguratioin { Logger logger = LoggerFactory.getLogger(MyDataSourceConfiguratioin.class); /*** Master data source. */ @Bean("masterDataSource") @ConfigurationProperties(prefix = "spring.druid.datasource.master") DataSource masterDataSource() { logger.info("create master datasource..." ); return DataSourceBuilder.create().build(); } /*** Slave data source. */ @Bean("slaveDataSource") @ConfigurationProperties(prefix = "spring.druid.datasource.slave")  DataSource slaveDataSource() { logger.info("create slave datasource..." ); return DataSourceBuilder.create().build(); } @Bean @Primary DataSource primaryDataSource(@Autowired @Qualifier("masterDataSource")DataSource masterDataSource, @Autowired @Qualifier("masterDataSource")DataSource slaveDataSource){ logger.info("create routing datasource..." ); Map<Object, Object> map = new HashMap<>(); map.put("masterDataSource", masterDataSource); map.put("slaveDataSource", slaveDataSource); RoutingDataSource routing = new RoutingDataSource(); routing.setTargetDataSources(map); routing.setDefaultTargetDataSource(masterDataSource); return routing; }}Copy the code

Step 2: Write the RoutingDataSource

We then use Spring’s built-in RoutingDataSource to proxy two real data sources as one dynamic data source:

public class RoutingDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return RoutingDataSourceContext.getDataSourceRoutingKey(); }}Copy the code

Step 3: Write the RoutingDataSourceContext

Used to store which data source to switch to currently

public class RoutingDataSourceContext { // holds data source key in thread local: static final ThreadLocal<String> threadLocalDataSourceKey = new ThreadLocal<>(); public static String getDataSourceRoutingKey() { String key = threadLocalDataSourceKey.get(); return key == null ? "masterDataSource" : key; } public RoutingDataSourceContext(String key) { threadLocalDataSourceKey.set(key); } public void close() { threadLocalDataSourceKey.remove(); }}Copy the code

Test (the code below is the code in controller)

@GetMapping("/findAllProductM")
public String findAllProductM() {    
    String key = "masterDataSource";    
    RoutingDataSourceContext routingDataSourceContext = new RoutingDataSourceContext(key);    
    productService.findAllProductM();    
    return "master";
}
@GetMapping("/findAllProductS")
public String findAllProductS() {    
    String key = "slaveDataSource";
    RoutingDataSourceContext routingDataSourceContext = new RoutingDataSourceContext(key);
    productService.findAllProductS();
    return "slave";
}
Copy the code

The above code can achieve dynamic switching of data sources

Optimization:

The above code works, but where you need to read the database, you need to add a large RoutingDataSourceContext

ctx = … Code, very inconvenient to use. Here are the optimizations

We can declare a custom annotation by placing the value from the RoutingDataSourceContext above in the annotation’s value property,

Then we define a section class. When we annotate custom annotations on the method, we execute the section logic to obtain the values in the annotations and set them to RoutingDataSourceContext, so as to dynamically switch the data source through annotations

Here is the code implementation:

Annotation class

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

Cut class:

@Aspect @Component public class RoutingAspect { @Around("@annotation(routingWith)") public Object routingWithDataSource(ProceedingJoinPoint joinPoint, RoutingWith routingWith) throws Throwable { String key = routingWith.value(); RoutingDataSourceContext ctx = new RoutingDataSourceContext(key); return joinPoint.proceed(); }}Copy the code

Modify Controller method

@RoutingWith("masterDataSource") 
@GetMapping("/findAllProductM") 
public String findAllProductM() {
 productService.findAllProductM(); return "lagou"; 
}

@RoutingWith("slaveDataSource") 
@GetMapping("/findAllProductS") 
public String findAllProductS() {
  productService.findAllProductS(); return "lagou";
 }
Copy the code

The above is the implementation and optimization of all the code, give dish chicken a thumbs up 😭😭😭😭