SpringBoot多数据源与动态数据源

最新推荐文章于 2024-05-30 17:00:00 发布

lizc_lizc

最新推荐文章于 2024-05-30 17:00:00 发布

阅读量991

点赞数

分类专栏： SpringBoot 文章标签： spring boot

本文链接：https://blog.csdn.net/lizc_lizc/article/details/126296873

版权

SpringBoot 专栏收录该内容

54 篇文章 14 订阅

订阅专栏

数据准备

准备两个数据库test1和test2
test1新建user_info表

CREATE TABLE user_info (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(50) NOT NULL COMMENT '用户名',
  gender varchar(2) DEFAULT NULL COMMENT '性别 W-女 M-男',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';

test2新建product_info表

CREATE TABLE product_info (
  id int(11) NOT NULL AUTO_INCREMENT,
  product_name varchar(50) NOT NULL COMMENT '产品名称',
  product_intro varchar(50) NOT NULL COMMENT '产品介绍test2',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='产品信息表';

依赖文件

SpringBoot版本为2.4.1

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>

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

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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

多数据源

在这里插入图片描述

设置数据源信息

Test1DataSourceConfig作为默认的数据源

/**
 * basePackages: 设置包扫描路径
 * sqlSessionTemplateRef: 设置 SqlSessionTemplate
 */
@Configuration
@MapperScan(basePackages = "com.example.multidatasource.mapper.test1", sqlSessionTemplateRef = "test1SqlSessionTemplate")
public class Test1DataSourceConfig {
    /**
     * 设置数据源
     * @Primary 代表该数据源为默认的数据源
     * @return
     */
    @Bean(name = "test1DataSource")
    @Primary
    public DruidDataSource test1DataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        return dataSource;
    }

    @Bean(name = "test1SqlSessionFactory")
    @Primary
    public SqlSessionFactory test1SqlSessionFactory(@Qualifier("test1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        // 设置数据源
        factory.setDataSource(dataSource);
        // 设置全局配置
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        // 开启自动驼峰命名规则
        configuration.setMapUnderscoreToCamelCase(true);
        /**
         * 打印详细SQL
         */
        // configuration.setLogImpl(StdOutImpl.class);

        factory.setConfiguration(configuration);
        // 指定Mapper文件的位置
        factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/test1/*.xml"));
        return factory.getObject();
    }

    @Bean(name = "test1PlatformTransactionManager")
    @Primary
    public PlatformTransactionManager test1PlatformTransactionManager(@Qualifier("test1DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "test1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate test1SqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

Test2DataSourceConfig

/**
 * basePackages: 设置包扫描路径
 * sqlSessionTemplateRef: 设置 SqlSessionTemplate
 */
@Configuration
@MapperScan(basePackages = "com.example.multidatasource.mapper.test2",sqlSessionTemplateRef = "test2SqlSessionTemplate")
public class Test2DataSourceConfig {
    /**
     * 设置数据源
     * @return
     */
    @Bean(name = "test2DataSource")
    public DruidDataSource test2DataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        return dataSource;
    }

    @Bean(name = "test2SqlSessionFactory")
    public SqlSessionFactory test2SqlSessionFactory(@Qualifier("test2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        // 设置数据源
        factory.setDataSource(dataSource);
        // 设置全局配置
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        // 开启自动驼峰命名规则
        configuration.setMapUnderscoreToCamelCase(true);
        /**
         * 打印详细SQL
         */
        // configuration.setLogImpl(StdOutImpl.class);

        factory.setConfiguration(configuration);
        // 指定Mapper文件的位置
        factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/test2/*.xml"));
        return factory.getObject();
    }

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

    @Bean(name = "test2SqlSessionTemplate")
    public SqlSessionTemplate test2SqlSessionTemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

编写Mapper文件

在com.example.multidatasource.mapper.test1新建UserInfoDAO

public interface UserInfoDAO {
    int insert(UserInfoDTO userInfoDTO);
}

在com.example.multidatasource.mapper.test2新建ProductInfoDAO

public interface ProductInfoDAO {
    int insert(ProductInfoDTO productInfoDTO);
}

在resources/mapper/test1新建UserMapper.xml

<?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.example.multidatasource.mapper.test1.UserInfoDAO">
    <select id="listAll" resultType="com.example.multidatasource.dto.UserInfoDTO">
        select * from user_info
    </select>

    <insert id="insert">
        insert into user_info(username,gender)values(#{username},#{gender})
    </insert>
</mapper>

在resources/mapper/test2新建ProductMapper.xml

<?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.example.multidatasource.mapper.test2.ProductInfoDAO">
    <insert id="insert">
        insert into product_info(product_name,product_intro)values(#{productName},#{productIntro})
    </insert>
</mapper>

编写测试类

@SpringBootTest
@Slf4j
class MultiDataSourceApplicationTests {

    @Autowired
    private UserInfoDAO userInfoDAO;

    @Autowired
    private ProductInfoDAO productInfoDAO;

    @Transactional
    @Test
    void saveUserInfo() {
        UserInfoDTO a = new UserInfoDTO();
        a.setUsername("123");
        a.setGender("M");
        userInfoDAO.insert(a);

        UserInfoDTO b = new UserInfoDTO();
        b.setUsername("456");
        b.setGender("W");
        userInfoDAO.insert(b);
    }

    /**
     * transactionManager: 指定事务管理器, 多数据源时, 如果没有指定, 默认使用 @Primary 修饰的事务管理器
     */
    @Transactional(transactionManager = "test2PlatformTransactionManager")
    @Test
    void saveProductInfo() {
        ProductInfoDTO a = new ProductInfoDTO();
        a.setProductName("123");
        a.setProductIntro("M");
        productInfoDAO.insert(a);

        ProductInfoDTO b = new ProductInfoDTO();
        b.setProductName("456");
        b.setProductIntro("W");
        productInfoDAO.insert(b);
    }
}

动态数据源

确定数量的多个数据源共用一个会话工厂，根据条件动态选取数据源进行连接、SQL 操作。
动态数据源不再是为每个数据库建立一套独立的数据处理逻辑，而是根据实际业务需求动态选择数据源，所以我们不需要在Service层或者Mapper层对数据库进行划分。
SpringBoot动态数据源的本质是将多个DataSource存储在一个Map集合中，当需要用到某个数据源时，从Map中获取此数据源进行处理。Spring提供了抽象类AbstractRoutingDataSource，实现了此功能，所以我们实现动态数据源时继承它，实现自己的获取数据源的逻辑即可。
在这里插入图片描述

多数据源注解

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface CurDataSource {
    DataSourceEnum dataSource();
}

多数据源枚举

public enum DataSourceEnum {
    TEST1("test1"),
    TEST2("test2")
    ;

    private String value;

    DataSourceEnum(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }
}

设置动态数据源

@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {
    /**
     * ThreadLocal 用于提供线程局部变量，在多线程环境可以保证各个线程里的变量独立于其它线程里的变量。
     * 也就是说 ThreadLocal 可以为每个线程创建一个【单独的变量副本】，相当于线程的 private static 类型变量。
     */
    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();

    /**
     * 决定使用哪个数据源之前需要把多个数据源的信息以及默认数据源信息配置好
     *
     * @param defaultTargetDataSource 默认数据源
     * @param targetDataSources       目标数据源
     */
    public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
        super.setDefaultTargetDataSource(defaultTargetDataSource);
        super.setTargetDataSources(targetDataSources);
        super.afterPropertiesSet();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        log.info("determineCurrentLookupKey CONTEXT_HOLDER.get() = {}",CONTEXT_HOLDER.get());
        return CONTEXT_HOLDER.get();
    }

    public static void setDataSource(String dataSource) {
        CONTEXT_HOLDER.set(dataSource);
    }

    public static void clearDataSource() {
        CONTEXT_HOLDER.remove();
    }
}

设置数据源

@Configuration
@MapperScan(basePackages = "com.example.dynamicdatasource.mapper", sqlSessionTemplateRef = "sqlSessionTemplate")
public class DataSourceConfig {
    /**
     * 设置数据源
     * @return
     */
    @Bean(name = "test1DataSource")
    public DataSource test1DataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        return dataSource;
    }

    @Bean(name = "test2DataSource")
    public DataSource test2DataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        return dataSource;
    }

    @Bean(name = "dynamicDataSource")
    @Primary
    public DataSource dynamicDataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceEnum.TEST1.getValue(),test1DataSource());
        targetDataSources.put(DataSourceEnum.TEST2.getValue(),test2DataSource());
        DynamicDataSource dynamicDataSource = new DynamicDataSource(test1DataSource(), targetDataSources);
        return dynamicDataSource;
    }


    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        // 设置数据源
        factory.setDataSource(dataSource);
        // 设置全局配置
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        // 开启自动驼峰命名规则
        configuration.setMapUnderscoreToCamelCase(true);
        /**
         * 打印详细SQL
         */
        // configuration.setLogImpl(StdOutImpl.class);

        factory.setConfiguration(configuration);
        // 指定Mapper文件的位置
        factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/**/*.xml"));
        return factory.getObject();
    }

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

    @Bean(name = "sqlSessionTemplate")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

切面类

@Slf4j
@Aspect
@Component
@Order(1) // 这个切面需要比事务管理器的切面先执行, 否则会导致使用默认的数据源
public class DataSourceAspect {
    @Pointcut("@annotation(com.example.dynamicdatasource.aop.CurDataSource)")
    public void dataSourcePointCut() {

    }

    @Around("dataSourcePointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Method method = signature.getMethod();
        CurDataSource ds = method.getAnnotation(CurDataSource.class);
        if (ds == null) {
            DynamicDataSource.setDataSource(DataSourceEnum.TEST1.getValue());
            log.info("default dataSource " +DataSourceEnum.TEST1.getValue());
        } else {
            DynamicDataSource.setDataSource(ds.dataSource().getValue());
            log.info("cur datasource is " + ds.dataSource().getValue());
        }
        try {
            return point.proceed();
        } finally {
            DynamicDataSource.clearDataSource();
            log.info("clean datasource");
        }
    }
}

Mapper文件

UserInfoDAO

public interface UserInfoDAO {
    @CurDataSource(dataSource = DataSourceEnum.TEST1)
    int insert(UserInfoDTO userInfoDTO);
}

ProductInfoDAO

public interface ProductInfoDAO {
    @CurDataSource(dataSource = DataSourceEnum.TEST2)
    int insert(ProductInfoDTO productInfoDTO);
}

启动类去掉自动装配

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class DynamicDataSourceApplication {
    public static void main(String[] args) {
        SpringApplication.run(DynamicDataSourceApplication.class, args);
    }
}

事务问题

AbstractRoutingDataSource 只支持单库事务，也就是说切换数据源要在开启事务之前执行。
Spring DataSourceTransactionManager进行事务管理，开启事务，会将数据源缓存到DataSourceTransactionObject对象中进行后续的commit rollback等事务操作。
因此我们需要在事务开启之前进行数据源切换。

新建一个函数式接口

@FunctionalInterface
public interface FunctionTransaction {
    void processTransaction() throws Exception;
}

新建service类

@Service
public class WrapService {
    @CurDataSource(dataSource = DataSourceEnum.TEST1)
    @Transactional(transactionManager = "platformTransactionManager", rollbackFor = Exception.class)
    public void processTransactionTest1(FunctionTransaction functionTransaction) throws Exception{
        functionTransaction.processTransaction();
    }

    @CurDataSource(dataSource = DataSourceEnum.TEST2)
    @Transactional(transactionManager = "platformTransactionManager", rollbackFor = Exception.class)
    public void processTransactionTest2(FunctionTransaction functionTransaction) throws Exception{
        functionTransaction.processTransaction();
    }
}

编写测试类

@Autowired
    private WrapService wrapService;

    @Test
    void saveUserInfo() {
        try {
            wrapService.processTransactionTest1(() -> {
                UserInfoDTO a = new UserInfoDTO();
                a.setUsername("123");
                a.setGender("M");
                userInfoDAO.insert(a);

                UserInfoDTO b = new UserInfoDTO();
                b.setUsername("456");
                b.setGender("W");
                userInfoDAO.insert(b);
            });
        } catch (Exception e) {
            log.error("saveUserInfo error:{}",e.getMessage(),e);
        }
    }

    @Test
    void saveProductInfo() {
        try {
            wrapService.processTransactionTest2(() -> {
                ProductInfoDTO a = new ProductInfoDTO();
                a.setProductName("123");
                a.setProductIntro("M");
                productInfoDAO.insert(a);

                ProductInfoDTO b = new ProductInfoDTO();
                // b.setProductName("456");
                b.setProductIntro("W");
                productInfoDAO.insert(b);
            });
        } catch (Exception e) {
            log.error("saveProductInfo error:{}",e.getMessage(),e);
        }

    }