基于Spring AOP实现读写分离

方案①：通过配置多个SqlSessionFactoryBean扫描不同的Mapper.xml实现读写分离

@Configuration
public class DBConfig {

    //读数据源
    @Bean
    public DataSource readDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://10.211.55.7:3306/db_read");
        dataSource.setUsername("root");
        dataSource.setPassword("Gz2021..");
        return dataSource;
    }

    //写数据源
    @Bean
    public DataSource writeDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://10.211.55.7:3306/db_write");
        dataSource.setUsername("root");
        dataSource.setPassword("Gz2021..");
        return dataSource;
    }

    //读，扫描select Mapper.xml
    @Bean
    @Qualifier("readSqlSessionFactoryBean")
    public SqlSessionFactoryBean readSqlSessionFactoryBean() throws Exception{
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(readDataSource());
        sqlSessionFactoryBean.setMapperLocations(new FileSystemResource("/Users/dongzhang/develop/workspace/echo20222022/open_src/spring_cloud_demo/cloud-user/src/main/resources/mappers/read/UserMapper.xml"));
        return sqlSessionFactoryBean;
    }

    //写，扫描update Mapper.xml
    @Bean
    @Qualifier("writeSqlSessionFactoryBean")
    public SqlSessionFactoryBean writeSqlSessionFactoryBean() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(writeDataSource());
        sqlSessionFactoryBean.setMapperLocations(new FileSystemResource("/Users/dongzhang/develop/workspace/echo20222022/open_src/spring_cloud_demo/cloud-user/src/main/resources/mappers/write/UserMapper.xml"));
        return sqlSessionFactoryBean;
    }
}

 

@SpringCloudApplication
//配置多个MapperScanner
@MapperScan(basePackages = "com.cloud.user.dao.read", sqlSessionFactoryRef="readSqlSessionFactoryBean")
@MapperScan(basePackages = "com.cloud.user.dao.write", sqlSessionFactoryRef="writeSqlSessionFactoryBean")
public class UserApplication {
    public static void main(String[] args) {
        SpringApplication.run(UserApplication.class);
    }
}

@Autowired
private ReadUserMapper readUserMapper;
@Autowired
private WriteUserMapper writeUserMapper;

@Override
public Response<Map<String, Object>> getReadUser() {
    //读
    Map<String, Object> data = readUserMapper.getUserById(1).get(0);
    Response<Map<String, Object>> response = new Response<>();
    response.setCode(200);
    response.setSuccess(true);
    response.setData(data);
    return response;
}

@Override
public Response<Map<String, Object>> getWriteUser() {
    //写
    Map<String, Object> data = writeUserMapper.getUserById(1).get(0);
    Response<Map<String, Object>> response = new Response<>();
    response.setCode(200);
    response.setSuccess(true);
    response.setData(data);
    return response;
}

测试结果： 

➜  open_src git:(master) ✗ curl http://localhost:8084/api/user/getReadUser
{"code":200,"success":true,"desc":null,"data":{"sex":"male","name":"read","id":1,"age":10}}%
➜  open_src git:(master) ✗ curl http://localhost:8084/api/user/getWriteUser
{"code":200,"success":true,"desc":null,"data":{"sex":"male","name":"write","id":1,"age":10}}%

方案②：基于Spring AOP实现读写分离

//标记注解
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface DS {
    DynamicOperationType value() default DynamicOperationType.READ;
}

public enum DynamicOperationType {
    READ, WRITE
}

//用ThreadLocal存储操作类型
public class DynamicOperationHolder {
    private static final ThreadLocal<DynamicOperationType> THREAD_LOCAL = new InheritableThreadLocal<>();
    public static void put(DynamicOperationType dataSourceType) {
        THREAD_LOCAL.set(dataSourceType);
    }
    public static DynamicOperationType get() {
        return THREAD_LOCAL.get();
    }
    public static void clear() {
        THREAD_LOCAL.remove();
    }
}


//基于spring jdbc的数据源路由接口实现一个具有动态路由功能的数据源
public class CloudDynamicDataSource extends AbstractRoutingDataSource {

    //一个写数据源
    private DataSource writeDataSource;
    //多个读数据源
    private List<DataSource> readDataSource;

    private int readDataSourceSize;
    private int readDataSourceRollPattern;  //选择策略，0-随机 1-轮询
    private AtomicLong counter = new AtomicLong(0);
    private final Lock lock = new ReentrantLock();

    private static final Long MAX_POLL = Long.MAX_VALUE;

    //该方法反的是DataSource对应的Bean ID
    @Override
    protected Object determineCurrentLookupKey() {

        //获取当前的操作类型
        DynamicOperationType operationType = DynamicOperationHolder.get();

        //以下三种情况返回写库
        if (operationType == null ||
             operationType == DynamicOperationType.WRITE ||
            readDataSourceSize == 0) {
            return DynamicOperationType.WRITE.name();
        }

        int readDSIdx = 0;
        //否则返回读库
        //随机返回一个读库
        if (readDataSourceRollPattern == 0) {
            readDSIdx = ThreadLocalRandom.current().nextInt(0, readDataSourceSize);
        } else {
            //轮询

            //处理潮界的情况，将计数器归零
            long counterValue = counter.incrementAndGet();
            System.out.println("counter → " + counterValue + " readDataSourceSize → " + readDataSourceSize + " idx → " + (int)counterValue % readDataSourceSize);
            if (counterValue + 1 >= MAX_POLL) {
                try {
                    lock.lock();
                    if (counterValue + 1 > MAX_POLL) {
                        counter.set(0);
                    }
                }finally {
                    lock.unlock();
                }
            }

            readDSIdx = (int)counterValue % readDataSourceSize;
        }

        return operationType.name() + "-" + readDSIdx;
    }


    @Override
    public void afterPropertiesSet() {
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put(DynamicOperationType.WRITE.name(), writeDataSource);
        //setTargetDataSources("READ", );
        int idx = 0;
        for (DataSource ds : getReadDataSource()) {
            dataSourceMap.put(DynamicOperationType.READ.name() + "-" + idx, ds);
            idx ++;
        }
        setTargetDataSources(dataSourceMap);
        System.out.println("dataSourceMap → " + dataSourceMap);
        super.afterPropertiesSet();
    }

    public DataSource getWriteDataSource() {
        return writeDataSource;
    }

    public void setWriteDataSource(DataSource writeDataSource) {
        this.writeDataSource = writeDataSource;
    }

    public List<DataSource> getReadDataSource() {
        return readDataSource;
    }

    public void setReadDataSource(List<DataSource> readDataSource) {
        this.readDataSourceSize = readDataSource.size();
        this.readDataSource = readDataSource;
    }

    public void setReadDataSourceRollPattern(int readDataSourceRollPattern) {
        this.readDataSourceRollPattern = readDataSourceRollPattern;
    }
}


@Mapper
public interface UserMapper {

    //标记写操作
    @DS(DynamicOperationType.WRITE)
    @Insert("insert into user(name, age, sex) values (#{name}, #{age}, #{sex});")
    int insert(User user);

    //标记读操作
    @DS(DynamicOperationType.READ)
    @Select("select * from user where id = #{id}")
    Map<String, Object> selectById(@Param("id") Integer id);
}



配置
DBConfig.java
public DataSource readDataSource1() {
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://10.211.55.7:3306/db_read1");
    dataSource.setUsername("root");
    dataSource.setPassword("Gz2021..");
    return dataSource;
}

public DataSource readDataSource2() {
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://10.211.55.7:3306/db_read2");
    dataSource.setUsername("root");
    dataSource.setPassword("Gz2021..");
    return dataSource;
}

public DataSource writeDataSource() {
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://10.211.55.7:3306/db_write");
    dataSource.setUsername("root");
    dataSource.setPassword("Gz2021..");
    return dataSource;
}

@Bean
public DataSource dataSource() {
    CloudDynamicDataSource dataSource = new CloudDynamicDataSource();
    dataSource.setWriteDataSource(writeDataSource());
    List<DataSource> readDataSourceList = new ArrayList<>();
    readDataSourceList.add(readDataSource1());
    readDataSourceList.add(readDataSource2());
    dataSource.setReadDataSource(readDataSourceList);
    //轮询策略
    dataSource.setReadDataSourceRollPattern(1);
    return dataSource;
}

application.yml
mybatis:
  # check-config-location: true
  #  mybatis框架配置文件，对mybatis的生命周期起作用
  config-location: "classpath:mybatis/mybatis-config.xml"
  #  配置xml路径
  mapper-locations: "classpath:mappers/*Mapper.xml"
  #  配置model包路径
  type-aliases-package: "com.cloud.user.bean.*"