springboot实现动态数据库切换

快来打我* 2023-06-25 04:50 5阅读 0赞

### springboot实现动态数据库切换 ###

随着项目中的开发，有的时候我们项目需要可以从多个数据库查询结果。比如：user表从datasource来查询，user1从datasource1查询。具体的业务的话具体实现。

那如果我们从springboot角度开发，怎么办?

#### aop注解方式 ####

准备工作

![20191226060234369.png][]

二个数据库和二张表建好了，其中testdatasource1为芳年25岁的张三， testdatasource2为芳年30岁的李四。

新建springboot项目，添加依赖

<dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
    
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-aop</artifactId>
                <version>5.1.5.RELEASE</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
            </dependency>
            <dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjweaver</artifactId>
                <version>1.9.2</version>
            </dependency>

配置二个数据源

#配置主数据库
    spring.datasource.primary.jdbc-url=jdbc:mysql://localhost:3306/testdatasource1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
    spring.datasource.primary.username=root
    spring.datasource.primary.password=root
    spring.datasource.primary.driver-class-name=com.mysql.cj.jdbc.Driver
    
    ##配置次数据库
    spring.datasource.secondary.jdbc-url=jdbc:mysql://localhost:3306/testdatasource2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&useSSL=false
    spring.datasource.secondary.username=root
    spring.datasource.secondary.password=root
    spring.datasource.secondary.driver-class-name=com.mysql.cj.jdbc.Driver
    
    
    spring.http.encoding.charset=UTF-8
    spring.http.encoding.enabled=true
    spring.http.encoding.force=true

###### 注意 ######

Springboot2.0 在配置数据库连接的时候需要使用jdbc-url，如果只使用url的话会报`jdbcUrl is required with driverClassName.`错误。

新建一个配置类

@Configuration
    @MapperScan(basePackages = "com.mzd.multipledatasources.mapper", sqlSessionFactoryRef = "SqlSessionFactory") //basePackages 我们接口文件的地址
    public class DynamicDataSourceConfig {
    
        // 将这个对象放入Spring容器中
        @Bean(name = "PrimaryDataSource")
        // 表示这个数据源是默认数据源
        @Primary
        // 读取application.properties中的配置参数映射成为一个对象
        // prefix表示参数的前缀
        @ConfigurationProperties(prefix = "spring.datasource.primary")
        public DataSource getDateSource1() {
            return DataSourceBuilder.create().build();
        }
    
    
        @Bean(name = "SecondaryDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.secondary")
        public DataSource getDateSource2() {
            return DataSourceBuilder.create().build();
        }
    
    
        @Bean(name = "dynamicDataSource")
        public DynamicDataSource DataSource(@Qualifier("PrimaryDataSource") DataSource primaryDataSource,
                                            @Qualifier("SecondaryDataSource") DataSource secondaryDataSource) {
    
            //这个地方是比较核心的targetDataSource 集合是我们数据库和名字之间的映射
            Map<Object, Object> targetDataSource = new HashMap<>();
            targetDataSource.put(DataSourceType.DataBaseType.Primary, primaryDataSource);
            targetDataSource.put(DataSourceType.DataBaseType.Secondary, secondaryDataSource);
            DynamicDataSource dataSource = new DynamicDataSource(); 
            dataSource.setTargetDataSources(targetDataSource);
            dataSource.setDefaultTargetDataSource(primaryDataSource);//设置默认对象
            return dataSource;
        }
    
    
        @Bean(name = "SqlSessionFactory")
        public SqlSessionFactory SqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dynamicDataSource)
                throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dynamicDataSource);
            bean.setMapperLocations(
                    new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/*/*.xml"));//设置我们的xml文件路径
            return bean.getObject();
        }
    }

public class DynamicDataSource extends AbstractRoutingDataSource {
    
        @Override
        protected Object determineCurrentLookupKey() {
            DataSourceType.DataBaseType dataBaseType = DataSourceType.getDataBaseType();
            return dataBaseType;
        }
    
    }

public class DataSourceType {
    
        //内部枚举类，用于选择特定的数据类型
        public enum DataBaseType {
            Primary, Secondary
        }
    
        // 使用ThreadLocal保证线程安全
        private static final ThreadLocal<DataBaseType> TYPE = new ThreadLocal<DataBaseType>();
    
        // 往当前线程里设置数据源类型
        public static void setDataBaseType(DataBaseType dataBaseType) {
            if (dataBaseType == null) {
                throw new NullPointerException();
            }
            TYPE.set(dataBaseType);
        }
    
        // 获取数据源类型
        public static DataBaseType getDataBaseType() {
            DataBaseType dataBaseType = TYPE.get() == null ? DataBaseType.Primary : TYPE.get();
            return dataBaseType;
        }
    
        // 清空数据类型
        public static void clearDataBaseType() {
            TYPE.remove();
        }
    
    }

@Component
    @Mapper
    public interface PrimaryUserMapper {
    
        List<User> findAll();
    }
    
    
    @Component
    @Mapper
    public interface SecondaryUserMapper {
    
        List<User> findAll();
    }

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.jdkcb.mybatisstuday.mapper.one.PrimaryUserMapper">
    
        <select id="findAll" resultType="com.jdkcb.mybatisstuday.pojo.User">
                    select * from sys_user;
        </select>
    
    </mapper>
    
    
    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.jdkcb.mybatisstuday.mapper.two.SecondaryUserMapper">
    
        <select id="findAll" resultType="com.jdkcb.mybatisstuday.pojo.User">
                    select * from sys_user2;
        </select>
    
    
    </mapper>

接下来配合我们的自定义注解来实现动态切换

/**
     * 切换数据注解 可以用于类或者方法级别 方法级别优先级 > 类级别
     */
    @Target({ElementType.METHOD, ElementType.TYPE, ElementType.PARAMETER})
    @Retention(RetentionPolicy.RUNTIME)
    @Documented
    public @interface DataSource {
        String value() default "primary"; //该值即key值，默认使用默认数据库
    }

@Aspect
    @Component
    public class DynamicDataSourceAspect {
        private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceAspect.class);
    
        @Before("@annotation(dataSource)")//拦截我们的注解
        public void changeDataSource(JoinPoint point, DataSource dataSource) throws Throwable {
            String value = dataSource.value();
            if (value.equals("primary")){
                DataSourceType.setDataBaseType(DataSourceType.DataBaseType.Primary);
            }else if (value.equals("secondary")){
                DataSourceType.setDataBaseType(DataSourceType.DataBaseType.Secondary);
            }else {
                DataSourceType.setDataBaseType(DataSourceType.DataBaseType.Primary);//默认使用主数据库
            }
    
        }
    
        @After("@annotation(dataSource)") //清除数据源的配置
        public void restoreDataSource(JoinPoint point, DataSource dataSource) {
            DataSourceType.clearDataBaseType();
    
    
        }
    }

具体使用方法：

@Component
    @Mapper
    public interface PrimaryUserMapper {
    
        @DataSource
        List<User> findAll();
    }
    
    @Component
    @Mapper
    public interface SecondaryUserMapper {
    
        @DataSource("secondary")//指定数据源为:secondary
        List<User> findAll();
    }

接下来，大家测试一下效果就好 了。欢迎关注

[20191226060234369.png]: https://img-blog.csdnimg.cn/20191226060234369.png