Implementing Database Read‑Write Separation with Spring Boot and MyBatis

In high‑concurrency scenarios, common database optimization techniques such as read‑write separation, caching, master‑slave clustering, and sharding are employed; most internet applications exhibit a "read‑most‑write‑few" pattern, which motivates the use of a master (write) and a slave (read) database.

The article notes the limitations of master‑slave replication, especially replication lag that can cause stale reads when a write has not yet been propagated to the slave, and mentions that in strict consistency requirements read‑write separation may not be suitable.

1. Master‑Slave DataSource Configuration – The master and slave data sources are defined in application.properties and bound to Java beans using @ConfigurationProperties . Druid is used as the connection pool, and beans for the master, slave, dynamic routing data source, SqlSessionFactory , SqlSessionTemplate , and transaction manager are created.

/**
 * 主从配置
 */
@Configuration
@MapperScan(basePackages = "com.wyq.mysqlreadwriteseparate.mapper", sqlSessionTemplateRef = "sqlTemplate")
public class DataSourceConfig {

    /** 主库 */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource master() {
        return DruidDataSourceBuilder.create().build();
    }

    /** 从库 */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaver() {
        return DruidDataSourceBuilder.create().build();
    }

    /** 实例化数据源路由 */
    @Bean
    public DataSourceRouter dynamicDB(@Qualifier("master") DataSource masterDataSource,
                                      @Autowired(required = false) @Qualifier("slaver") DataSource slaveDataSource) {
        DataSourceRouter dynamicDataSource = new DataSourceRouter();
        Map
targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceEnum.MASTER.getDataSourceName(), masterDataSource);
        if (slaveDataSource != null) {
            targetDataSources.put(DataSourceEnum.SLAVE.getDataSourceName(), slaveDataSource);
        }
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
        return dynamicDataSource;
    }

    @Bean
    public SqlSessionFactory sessionFactory(@Qualifier("dynamicDB") DataSource dynamicDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/*Mapper.xml"));
        bean.setDataSource(dynamicDataSource);
        return bean.getObject();
    }

    @Bean
    public SqlSessionTemplate sqlTemplate(@Qualifier("sessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean(name = "dataSourceTx")
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dynamicDB") DataSource dynamicDataSource) {
        DataSourceTransactionManager tx = new DataSourceTransactionManager();
        tx.setDataSource(dynamicDataSource);
        return tx;
    }
}

2. DataSource Router Configuration – A custom router extends AbstractRoutingDataSource and overrides determineCurrentLookupKey() to obtain the current datasource name from DataSourceContextHolder .

public class DataSourceRouter extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.get();
    }
}

3. DataSource Context Holder – Uses a ThreadLocal<String> to store, retrieve, and clear the current datasource identifier, ensuring thread‑safety.

public class DataSourceContextHolder {
    private static final ThreadLocal
context = new ThreadLocal<>();
    public static void set(String datasourceType) { context.set(datasourceType); }
    public static String get() { return context.get(); }
    public static void clear() { context.remove(); }
}

4. Switching Annotation and AOP Configuration – The @DataSourceSwitcher annotation declares the target datasource and whether to clear it after method execution. An AOP aspect intercepts methods annotated with this annotation, sets the datasource in the context holder before proceeding, and optionally clears it in a finally block.

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@Documented
public @interface DataSourceSwitcher {
    DataSourceEnum value() default DataSourceEnum.MASTER;
    boolean clear() default true;
}

@Aspect
@Component
public class DataSourceContextAop {
    @Around("@annotation(com.wyq.mysqlreadwriteseparate.annotation.DataSourceSwitcher)")
    public Object setDynamicDataSource(ProceedingJoinPoint pjp) throws Throwable {
        boolean clear = false;
        try {
            Method method = ((MethodSignature) pjp.getSignature()).getMethod();
            DataSourceSwitcher ds = method.getAnnotation(DataSourceSwitcher.class);
            clear = ds.clear();
            DataSourceContextHolder.set(ds.value().getDataSourceName());
            return pjp.proceed();
        } finally {
            if (clear) { DataSourceContextHolder.clear(); }
        }
    }
}

5. Usage and Testing – In service or DAO layers, annotate read‑only methods with @DataSourceSwitcher(DataSourceEnum.SLAVE) and write methods with @DataSourceSwitcher(DataSourceEnum.MASTER) . An alternative automatic approach switches based on method name prefixes (e.g., select , update ).

@Service
public class OrderService {
    @Resource
    private OrderMapper orderMapper;

    @DataSourceSwitcher(DataSourceEnum.SLAVE)
    public List
getOrder(String orderId) {
        return orderMapper.listOrders(orderId);
    }

    @DataSourceSwitcher(DataSourceEnum.MASTER)
    public List
insertOrder(Long orderId) {
        Order order = new Order();
        order.setOrderId(orderId);
        return orderMapper.saveOrder(order);
    }
}

6. Summary – The core of read‑write separation lies in the routing mechanism that extends AbstractRoutingDataSource and the global DataSourceContextHolder . Combined with Spring’s JDBC template, transaction management, and AOP‑driven annotations, the solution provides a clean and effective way to achieve database read‑write separation.