Springboot动态切换多个数据源

最新推荐文章于 2024-10-13 01:45:07 发布

码匠S10

最新推荐文章于 2024-10-13 01:45:07 发布

阅读量1k

点赞数 3

分类专栏： Springboot 文章标签： mybatis spring boot java

本文链接：https://blog.csdn.net/qq_27676247/article/details/127461431

版权

Springboot 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

一.前言
通过springboot操作mysql数据库，但是在实际业务场景中，数据量迅速增长，一个库一个表已经满足不了我们的需求的时候，我们就会考虑分库分表的操作，在springboot中如何实现多数据源，动态数据源切换，读写分离等操作。
二.多源数据库
主要涉及的maven包

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<!--mybatis springboot-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>

<!-- jdbc driver end-->
<dependency>
    <groupId>org.aspectj</groupId>
    <artifactId>aspectjweaver</artifactId>
</dependency>

数据源配置

#多数据源 1主2从
datasource:
    #从库数量
    readSize: 2
    # 使用druid数据源
    type: com.alibaba.druid.pool.DruidDataSource
    #主库
    write:
        url: jdbc:mysql://127.0.0.1:3306/lss0555?useUnicode=true&characterEncoding=utf8
        username: root
        password: 888888
        driver-class-name: com.mysql.jdbc.Driver
        filters: stat
        maxActive: 20
        initialSize: 1
        maxWait: 60000
        minIdle: 1
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQueryTimeout: 900000
        validationQuery: SELECT 1
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxOpenPreparedStatements: 20
    read1:
        url: jdbc:mysql://127.0.0.1:3306/lss05552?useUnicode=true&characterEncoding=utf8
        username: root
        password: 888888
        driver-class-name: com.mysql.jdbc.Driver
        filters: stat
        maxActive: 20
        initialSize: 1
        maxWait: 60000
        minIdle: 1
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQueryTimeout: 900000
        validationQuery: SELECT 1
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxOpenPreparedStatements: 20
    read2:
        url: jdbc:mysql://127.0.0.1:3306/lss05553?useUnicode=true&characterEncoding=utf8
        username: root
        password: 888888
        driver-class-name: com.mysql.jdbc.Driver
        filters: stat
        maxActive: 20
        initialSize: 1
        maxWait: 60000
        minIdle: 1
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQueryTimeout: 900000
        validationQuery: SELECT 1
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxOpenPreparedStatements: 20

1.自定义注解标签TargetDataSource,主要用于在接口处通过注解来切换数据源

@Target({ ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
    String value();
}

2.定义切面DataSourceAop
通过获取接口处的注解,获取注解所需要切换的数据源名称,从而来切换该数据源

@Aspect
@Component
@Slf4j
public class DataSourceAop {
    @Before("@annotation(TargetDataSource)")
    public void setWriteDataSourceType(JoinPoint point) {
        //获得当前访问的class
        Class<?> className = point.getTarget().getClass();
        //获得访问的方法名
        String methodName = point.getSignature().getName();
        //得到方法的参数的类型
        Class[] argClass = ((MethodSignature)point.getSignature()).getParameterTypes();
        //获取默认的数据源名称
        String dataSource = DataSourceContextHolder.DEFAULT_DS;
        try {
            // 得到访问的方法对象
            Method method = className.getMethod(methodName, argClass);
            // 判断是否存在@注解
            if (method.isAnnotationPresent(TargetDataSource.class)) {
                TargetDataSource annotation = method.getAnnotation(TargetDataSource.class);
                // 取出注解中的数据源名
                dataSource = annotation.value();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 切换数据源
        DataSourceContextHolder.setJdbcType(dataSource);
    }

    @After("@annotation(TargetDataSource)")
    public void afterSwitchDS(JoinPoint point){
        DataSourceContextHolder.clearDB();
    }
}

3.设置本地线程全局变量

@Slf4j
public class DataSourceContextHolder {
    private static final ThreadLocal<String> local = new ThreadLocal<String>();
    public static final String DEFAULT_DS = "write";//默认数据源

    public static ThreadLocal<String> getLocal() {
        return local;
    }

    public static String getJdbcType() {
        return local.get();
    }

    public static void setJdbcType(String dbType) {
        log.info("dataSource切换到："+dbType);
        local.set(dbType);
    }

    // 清除数据源名
    public static void clearDB() {
        local.remove();
    }
}

4.数据库配置：解析application-pro.yml文件
通过@ConfigurationProperties注解获取配置文件属性自动配置,绑定其属性
通过@Bean注解,申请实例对象

@Slf4j
@Configuration
public class DataBaseConfiguration {
    @Value("${datasource.type}")
    private Class<? extends DataSource> dataSourceType;

    @Bean(name = "writeDataSource")
    @Primary
    @ConfigurationProperties(prefix = "datasource.write")
    public DataSource writeDataSource() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }

    /** * 有多少个从库就要配置多少个 * @return */
    @Bean(name = "readDataSource1")
    @ConfigurationProperties(prefix = "datasource.read1")
    public DataSource readDataSourceOne() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }

    @Bean(name = "readDataSource2")
    @ConfigurationProperties(prefix = "datasource.read2")
    public DataSource readDataSourceTwo() {
        return DataSourceBuilder.create().type(dataSourceType).build();
    }
}

5.通过枚举法区分读写库标识

public enum DataSourceType {
    read("read", "从库"),
    write("write", "主库");
    @Getter
    private String type;
    @Getter
    private String name;

    DataSourceType(String type, String name) {
        this.type = type;
        this.name = name;
    }
}

6.通过继承AbstractRoutingDataSource实现其动态选择数据源

public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {
    //配置的读库数量
    private final int dataSourceNumber;
    private AtomicInteger count = new AtomicInteger(0);
    /** * @dataSourceNumber 从库的数量 **/
    public MyAbstractRoutingDataSource(int dataSourceNumber) {
        this.dataSourceNumber = dataSourceNumber;
    }
    @Override
    protected Object determineCurrentLookupKey() {
        //获取通过aop设置的数据源名称
        String typeKey = DataSourceContextHolder.getJdbcType();
        //如果是当前数据源是默认主库,直接返回主库
        if (typeKey.equals(DataSourceType.write.getType())){
            return DataSourceType.write.getType();
        }
        //从库 读 简单负载均衡
        int number = count.getAndAdd(1);
        int lookupKey = number % dataSourceNumber;
        return new Integer(lookupKey);

        //如果不进行负载,直接指定数据源的话,则可以这边修改
    }
}

7.配置mybatis

@Slf4j
@Configuration
@ConditionalOnClass({ EnableTransactionManagement.class})
@Import({ DataBaseConfiguration.class})
public class MybatisConfiguration {
    @Value("${datasource.type}")
    private Class<? extends DataSource> dataSourceType;

    @Value("${datasource.readSize}")
    private String dataSourceSize;

    @Resource(name = "writeDataSource")
    private DataSource dataSource;

    @Resource(name = "readDataSource1")
    private DataSource read1DataSources;

    @Resource(name = "readDataSource2")
    private DataSource read2DataSources;

    List<DataSource> readDataSources;

    @Bean
    @ConditionalOnMissingBean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
        return sqlSessionFactoryBean.getObject();
    }

    /** * 设置默认数据库,其他数据源 * @return */
    @Bean(name = "dynamicDataSource")
    public AbstractRoutingDataSource roundRobinDataSouceProxy() {
        int size = Integer.parseInt(dataSourceSize);
        MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource(size);
        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
        // DataSource writeDataSource = SpringContextHolder.getBean("writeDataSource");
        // 写
        targetDataSources.put(DataSourceType.write.getType(),dataSource);
        // targetDataSources.put(DataSourceType.read.getType(),readDataSource);
        //多个读数据库时
        readDataSources=new ArrayList<DataSource>();
        readDataSources.add(read1DataSources);
        readDataSources.add(read2DataSources);
        for (int i = 0; i < size; i++) {
            targetDataSources.put(i, readDataSources.get(i));
        }
        proxy.setDefaultTargetDataSource(dataSource);
        proxy.setTargetDataSources(targetDataSources);
        return proxy;
    }
}

8.自定义事务

@Configuration
@EnableTransactionManagement
@Slf4j
@Import({ MybatisConfiguration.class})
public class DataSourceTransactionManager extends             DataSourceTransactionManagerAutoConfiguration {
    /** * 自定义事务 * MyBatis自动参与到spring事务管理中，无需额外配置，只要                org.mybatis.spring.SqlSessionFactoryBean引用的数据源与DataSourceTransactionManager引用的数据源一致即可，否则事务管理会不起作用。 * @return */
    @Resource(name = "dynamicDataSource")
    private DataSource dataSource;

    @Bean(name = "transactionManager")
    public org.springframework.jdbc.datasource.DataSourceTransactionManager     transactionManagers() {
        return new org.springframework.jdbc.datasource.DataSourceTransactionManager(dataSource);
    }
}

三、使用

1、定义Dao层接口

public interface UserInfoDaoInter {
    User userInfo(int id);
    int addStudent(Student student);
}

2、定义Dao层接口实现

@Repository
public class UserInfoDaoImpl implements UserInfoDaoInter {

    @Resource
    UserDaoMapper userDaoMapper;

    @Resource
    StudentDaoMapper studentDaoMapper;

    @TargetDataSource("read")
    @Override
    public User userInfo(int id) {
        return userDaoMapper.userInfo(id);
    }

    @TargetDataSource("write")
    @Override
    public int addStudent(Student student) {
        return studentDaoMapper.addStudent(student);
    }    
}

3、定义Mapper层接口

UserDaoMapper

@Mapper
public interface UserDaoMapper {
    User userInfo(int id);
}

StudentDaoMapper

@Mapper
public interface StudentDaoMapper {
    int addStudent(Student student);
}

mapper的sql实现代码略

3、定义测试Service层接口与实现

UserServiceInter

public interface UserServiceInter {
    User userInfo(int id);
    int addStudent(Student student);
}

实现层 UserServiceImpl

@Service
public class UserServiceImpl implements UserServiceInter {

    @Resource
    UserInfoDaoInter userInfoDaoInter ;


    @Override
    public ResponseData userInfo(int id) {
        ResponseData data = new ResponseData();
        User user=userInfoDaoInter .userInfo(id);
        data.setData(user);
        return data;
    }

    @Override
    public ResponseData addStudent(Student student) {
        ResponseData data = new ResponseData();
        int addresult=userInfoDaoInter.addStudent(student);
        data.setModel(addresult);
        return data;
    }
}