Implementing Database Read-Write Separation with Spring Boot and MySQL

In high‑concurrency applications where read operations dominate, separating read and write traffic between a master and one or more slave databases can reduce contention and improve performance.

The article first shows how to configure master and slave data sources in Spring Boot using @ConfigurationProperties and Alibaba Druid, defining beans for the master, slave, a dynamic routing data source, SqlSessionFactory, SqlSessionTemplate, and transaction manager.

/**
 * 主从配置
 *
 * @author wyq
 */
@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;
    }

    /**
     * 配置sessionFactory
     * @param dynamicDataSource
     * @return
     * @throws Exception
     */
    @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();
    }

    /**
     * 创建sqlTemplate
     * @param sqlSessionFactory
     * @return
     */
    @Bean
    public SqlSessionTemplate sqlTemplate(@Qualifier("sessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    /**
     * 事务配置
     * @param dynamicDataSource
     * @return
     */
    @Bean(name = "dataSourceTx")
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dynamicDB") DataSource dynamicDataSource) {
        DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
        dataSourceTransactionManager.setDataSource(dynamicDataSource);
        return dataSourceTransactionManager;
    }
}

Next, it introduces a routing data source by extending AbstractRoutingDataSource . The overridden determineCurrentLookupKey() method retrieves the current data source name from a thread‑local holder.

public class DataSourceRouter extends AbstractRoutingDataSource {

    /**
     * 最终的determineCurrentLookupKey返回的是从DataSourceContextHolder中拿到的,因此在动态切换数据源的时候注解
     * 应该给DataSourceContextHolder设值
     */
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.get();
    }
}

A DataSourceContextHolder class uses a ThreadLocal<String> to store, retrieve, and clear the current data source identifier, ensuring thread‑safety.

/**
 * 利用ThreadLocal封装的保存数据源上线的上下文context
 */
public class DataSourceContextHolder {

    private static final ThreadLocal
context = new ThreadLocal<>();

    /**
     * 赋值
     * @param datasourceType
     */
    public static void set(String datasourceType) {
        context.set(datasourceType);
    }

    /**
     * 获取值
     * @return
     */
    public static String get() {
        return context.get();
    }

    public static void clear() {
        context.remove();
    }
}

To switch data sources declaratively, a custom annotation @DataSourceSwitcher is defined with attributes for the target data source and whether to clear the context after execution.

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
@Documented
public @interface DataSourceSwitcher {
    /**
     * 默认数据源
     */
    DataSourceEnum value() default DataSourceEnum.MASTER;
    /**
     * 清除
     */
    boolean clear() default true;
}

An AOP aspect DataSourceContextAop intercepts methods annotated with @DataSourceSwitcher , sets the appropriate data source in the context holder before proceeding, and optionally clears it afterward.

@Slf4j
@Aspect
@Order(value = 1)
@Component
public class DataSourceContextAop {

    @Around("@annotation(com.wyq.mysqlreadwriteseparate.annotation.DataSourceSwitcher)")
    public Object setDynamicDataSource(ProceedingJoinPoint pjp) throws Throwable {
        boolean clear = false;
        try {
            Method method = this.getMethod(pjp);
            DataSourceSwitcher dataSourceSwitcher = method.getAnnotation(DataSourceSwitcher.class);
            clear = dataSourceSwitcher.clear();
            DataSourceContextHolder.set(dataSourceSwitcher.value().getDataSourceName());
            log.info("数据源切换至：{}", dataSourceSwitcher.value().getDataSourceName());
            return pjp.proceed();
        } finally {
            if (clear) {
                DataSourceContextHolder.clear();
            }
        }
    }

    private Method getMethod(JoinPoint pjp) {
        MethodSignature signature = (MethodSignature) pjp.getSignature();
        return signature.getMethod();
    }
}

Finally, the article demonstrates usage in a service layer: read methods are annotated with @DataSourceSwitcher(DataSourceEnum.SLAVE) and write methods with @DataSourceSwitcher(DataSourceEnum.MASTER) . It also mentions an automatic naming convention based on method prefixes.

@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);
    }
}

The conclusion emphasizes that the core of read‑write separation is the routing mechanism, which relies on extending AbstractRoutingDataSource and managing the data source context, combined with Spring’s JDBC template and transaction management to achieve a complete solution.