springboot 实现数据库的读写分离

springboot + mybatis +druid 主从数据库，所有select语句都走从数据库

在 Spring Boot 应用中，MyRoutingDataSource 可以与 DataSource 配置结合使用，通过 @Configuration 类来配置数据源和事务管理器，并使用 @Primary 注解来指定主数据源

添加依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.20</version> <!-- 根据需要选择合适的版本 -->
</dependency>

配置文件

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://localhost:3306/master_db?useSSL=false&serverTimezone=UTC
      username: master_user
      password: master_password
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave1:
      jdbc-url: jdbc:mysql://localhost:3306/slave_db1?useSSL=false&serverTimezone=UTC
      username: slave_user
      password: slave_password
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave2:
      jdbc-url: jdbc:mysql://localhost:3306/slave_db2?useSSL=false&serverTimezone=UTC
      username: slave_user
      password: slave_password
      driver-class-name: com.mysql.cj.jdbc.Driver

  # MyBatis配置
  mybatis:
    mapper-locations: classpath:/mapper/*.xml
    type-aliases-package: com.example.demo.model
    configuration:
      map-underscore-to-camel-case: true

# MyBatis配置，可以指定config-location来加载mybatis-config.xml配置文件
# mybatis.config-location: classpath:mybatis-config.xml

或者

# 主数据源配置
spring.datasource.master.jdbc-url=jdbc:mysql://localhost:3306/master_db?useSSL=false&serverTimezone=UTC
spring.datasource.master.username=master_user
spring.datasource.master.password=master_password
spring.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver

# 从数据源配置
spring.datasource.slave1.jdbc-url=jdbc:mysql://localhost:3306/slave_db1?useSSL=false&serverTimezone=UTC
spring.datasource.slave1.username=slave_user
spring.datasource.slave1.password=slave_password
spring.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver

spring.datasource.slave2.jdbc-url=jdbc:mysql://localhost:3306/slave_db2?useSSL=false&serverTimezone=UTC
spring.datasource.slave2.username=slave_user
spring.datasource.slave2.password=slave_password
spring.datasource.slave2.driver-class-name=com.mysql.cj.jdbc.Driver

# MyBatis配置
mybatis.mapper-locations=classpath:/mapper/*.xml
mybatis.type-aliases-package=com.example.demo.model
mybatis.configuration.map-underscore-to-camel-case=true

# MyBatis配置，可以指定config-location来加载mybatis-config.xml配置文件
# mybatis.config-location=classpath:mybatis-config.xml

druid配置

druid:
  initial-size: 5
  min-idle: 5
  max-active: 20
  max-wait: 60000

配置类

@Configuration
public class DataSourceConfig {

    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    @Bean(name = "slave1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave1")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }
    @Bean(name = "slave2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave2")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }
}

定义数据源上下文持有者 (DbContextHolder)：
这是一个用于存储和检索当前请求所选择的数据源类型的类，通常使用 ThreadLocal 来保证线程安全。

public class DbContextHolder {
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public static void setDbType(String dbType) {
        contextHolder.set(dbType);
    }

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

    public static void clearDbType() {
        contextHolder.remove();
    }
}

自定义一个数据源路由类MyRoutingDataSource 类，并且继承 AbstractRoutingDataSource，并且重写了 determineCurrentLookupKey() 方法，用于确定当前请求应该使用哪个数据源。这个方法的实现通常基于一些业务逻辑或者执行上下文来决定数据源的选择。

实现 determineCurrentLookupKey() 方法： 在这个方法中，你可以通过调用 DbContextHolder.getDbType() 来获取当前请求的数据源类型。这个类型可以是一个简单的字符串、枚举值或其他可以唯一标识数据源的类型。

public class MyRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        // 根据业务逻辑获取数据源类型
        String dbType = DbContextHolder.getDbType();
        if (dbType == null) {
            // 如果没有设置，可以使用默认的数据源类型
            dbType = "defaultDataSource";
        }
        if(dbType.equals("select")){
            dbType = "slaveDataSource"
        }
        return  dbType;
    }
}

配置路由

@Configuration
public class DataSourceConfig {

    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    @Bean(name = "slave1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave1")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }
    @Bean(name = "slave2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave2")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    @Bean
    public MyRoutingDataSource dataSource() {
        MyRoutingDataSource dataSource = new MyRoutingDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("masterDataSource", masterDataSource());
        targetDataSources.put("slave1DataSource", slaveDataSource());
        targetDataSources.put("slave2DataSource", slaveDataSource());
        dataSource.setTargetDataSources(targetDataSources);
        dataSource.setDefaultTargetDataSource(masterDataSource()); // 设置默认数据源
        return dataSource;
    }

}

使用 AOP 拦截 SQL 语句

@Aspect
@Component
public class DataSourceAspect {

    @Before("execution(* org.springframework.jdbc.core.JdbcTemplate.query(..))")
    public void setReadDataSource() {
        // 所有查询操作前设置使用从数据库
        DbContextHolder.setDbType("select");
    }

    @After("execution(* org.springframework.jdbc.core.JdbcTemplate.query(..))")
    public void clearReadDataSource() {
        // 查询操作后清除设置
        DbContextHolder.clearDbType();
    }

    // 可以添加更多的切点来处理其他类型的数据操作
}

配置事务管理器

@Configuration
public class DataSourceConfig {

    @Bean(name = "masterDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    @Bean(name = "slave1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave1")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }
    @Bean(name = "slave2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.slave2")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    @Bean(name = "masterTransactionManager")
public PlatformTransactionManager masterTransactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
}

@Bean(name = "slaveTransactionManager1")
public PlatformTransactionManager slaveTransactionManager1(@Qualifier("slave2DataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "slaveTransactionManager2")
public PlatformTransactionManager slaveTransactionManager1(@Qualifier("slave2DataSource") DataSource dataSource) {
    return new DataSourceTransactionManager(dataSource);
}

    @Bean
    public MyRoutingDataSource dataSource() {
        MyRoutingDataSource dataSource = new MyRoutingDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("masterDataSource", masterDataSource());
        targetDataSources.put("slave1DataSource", slaveDataSource());
        targetDataSources.put("slave2DataSource", slaveDataSource());
        dataSource.setTargetDataSources(targetDataSources);
        dataSource.setDefaultTargetDataSource(masterDataSource()); // 设置默认数据源
        return dataSource;
    }

}

以上就配置好了

另外如果你对性能和可扩展性要求比较高，还可以为每个数据源创建不同的MyBatis会话工厂。可以为每个数据源配置SqlSessionFactory Bean，并注入对应的数据源。

@Bean
public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource) throws Exception {
    SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
    sessionFactory.setDataSource(masterDataSource);
    sessionFactory.setMapperLocations(mapperLocations());
    // 其他MyBatis配置...
    return sessionFactory.getObject();
}

@Bean
public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource slaveDataSource) throws Exception {
    SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
    sessionFactory.setDataSource(slaveDataSource);
    sessionFactory.setMapperLocations(slaveMapperLocations());
    // 其他MyBatis配置...
    return sessionFactory.getObject();
}

配置MyBatis模板：为每个会话工厂配置SqlSessionTemplate。

@Bean
public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory masterSqlSessionFactory) {
    return new SqlSessionTemplate(masterSqlSessionFactory);
}

@Bean
public SqlSessionTemplate slaveSqlSessionTemplate(@Qualifier("slaveSqlSessionFactory") SqlSessionFactory slaveSqlSessionFactory) {
    return new SqlSessionTemplate(slaveSqlSessionFactory);
}