# dynamic-datasource-spring-boot-parent **Repository Path**: myprofile/dynamic-datasource-spring-boot-parent ## Basic Information - **Project Name**: dynamic-datasource-spring-boot-parent - **Description**: 支持多数据源事务，以及事务传播机制，隔离机制，异常回滚等功能的动态数据源库 - **Primary Language**: Java - **License**: Apache-2.0 - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 2 - **Forks**: 1 - **Created**: 2022-08-08 - **Last Updated**: 2025-03-28 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # 如果在使用过程中有什么需求或者问题，欢迎提issue `目前支持mybatis,mybatis-plus以及jdbc和原生方式,相关demo请查看dynamic-datasource-spring-boot-examples` ## 配置，支持配置引用以及定制配置 - `配置引用，相当于通用配置，通过ref指定通用配置的前缀` - `定制配置，只属于对应数据源的配置，优先级比ref高，可覆盖ref引用的配置` - `配置参数无自动提示，需要使用者针对对应的数据库连接池中的set方法做相应设置` ```yaml spring: ds: hikari: maximum-pool-size: 10 driver-class-name: com.mysql.cj.jdbc.Driver idleTimeout: 300000 auto-commit: false connection-timeout: 5000 dynamic: primary: master data-sources: master: type: com.zaxxer.hikari.HikariDataSource maxLifetime: 600000 pool-name: master connection-test-query: SELECT 1 minimumIdle: 5 url: jdbc:mysql://mysql.lhstack.dev:3306/test?characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true ref: spring.ds.hikari connection-timeout: 3000 idle-timeout: 300000 username: root password: 123456 slave: type: com.zaxxer.hikari.HikariDataSource url: jdbc:mysql://mysql.lhstack.dev:3306/test1?characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true driver-class-name: com.mysql.cj.jdbc.Driver connection-test-query: SELECT 'X' pool-name: slave maxLifetime: 1800000 idleTimeout: 600000 maximum-pool-size: 3 minimumIdle: 1 username: root password: 123456 ``` ## 动态数据源 ```java package com.lhstack.dynamic.datasource; import com.zaxxer.hikari.HikariDataSource; import org.junit.Test; import org.springframework.jdbc.datasource.DriverManagerDataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; /** * @Description TODO * @Copyright: Copyright (c) 2022 ALL RIGHTS RESERVED. * @Author lhstack * @Date 2022/7/18 15:29 * @Modify by */ public class DynamicRoutingDataSourceApplicationTests { @Test public void testOneDataSource() throws Exception { DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource(); driverManagerDataSource.setUrl("jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true"); driverManagerDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); driverManagerDataSource.setUsername("root"); driverManagerDataSource.setPassword("123456"); DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource("master", driverManagerDataSource, true); Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM aaa"); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { int id = resultSet.getInt("id"); String value = resultSet.getString("value"); System.out.printf("id=%s,value=%s\r\n", id, value); } } @Test public void testTwoDataSource() throws Exception { DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource(); driverManagerDataSource.setUrl("jdbc:mysql://localhost:3306/test1?characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true"); driverManagerDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); driverManagerDataSource.setUsername("root"); driverManagerDataSource.setPassword("123456"); DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource("master", driverManagerDataSource, true); Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM sss"); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { int id = resultSet.getInt("id"); String value = resultSet.getString("value"); System.out.printf("id=%s,value=%s\r\n", id, value); } } @Test public void testMixinDataSource() throws Exception { HikariDataSource hikariDataSource = new HikariDataSource(); hikariDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true"); hikariDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); hikariDataSource.setUsername("root"); hikariDataSource.setPassword("123456"); DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource("master", hikariDataSource, true); Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM aaa"); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { int id = resultSet.getInt("id"); String value = resultSet.getString("value"); System.out.printf("id=%s,value=%s\r\n", id, value); } connection.close(); hikariDataSource = new HikariDataSource(); hikariDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test1?characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true"); hikariDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); hikariDataSource.setUsername("root"); hikariDataSource.setPassword("123456"); dataSource.add("slave", hikariDataSource); DynamicRoutingDataSourceHolder.reset(); DynamicRoutingDataSourceHolder.ds("slave"); connection = dataSource.getConnection(); preparedStatement = connection.prepareStatement("SELECT * FROM sss"); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { int id = resultSet.getInt("id"); String value = resultSet.getString("value"); System.out.printf("id=%s,value=%s\r\n", id, value); } hikariDataSource = new HikariDataSource(); hikariDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test1?characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true"); hikariDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver"); hikariDataSource.setUsername("root"); hikariDataSource.setPassword("123456"); dataSource.add("slave", hikariDataSource, true); connection = dataSource.getConnection(); preparedStatement = connection.prepareStatement("SELECT * FROM sss"); resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { int id = resultSet.getInt("id"); String value = resultSet.getString("value"); System.out.printf("id=%s,value=%s\r\n", id, value); } dataSource.remove("slave"); dataSource.close(); } } ``` ## 切换数据源 `在类或者方法上面加上DS指定需要使用的数据源即可` ```java @DS("slave") @Repository public interface SssMapper extends BaseMapper { @Insert("INSERT INTO sss(`value`) VALUES(#{value})") int insert(@Param("value") String value); } ``` ```java @Override @DS("slave") public void run(String... args) throws Exception { Connection connection = dataSource.getConnection(); connection.setAutoCommit(false); try (Statement statement = connection.createStatement()) { statement.executeUpdate("SET TRANSACTION READ ONLY"); } PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM sss"); ResultSet resultSet = preparedStatement.executeQuery(); System.out.println(resultSet); connection.commit(); PreparedStatement preparedStatement1 = connection.prepareStatement("INSERT INTO sss(`value`) VALUES('测试数据')"); System.out.println(preparedStatement1.executeUpdate()); connection.commit(); System.out.println(connection); } ``` ## 多数据源事务 - `如下TestService中的rollbackService方法，当抛出异常，aaaService.insert和sssService.insert会回滚，而aaaService.insert1不会回滚，因为他没有加入事务` - `如下rollbackServiceExpectArithmeticException，当抛出异常，都不会回滚，因为事务期望的异常是ArithmeticException` `TestService.java` ```java package com.lhstack.dynamic.datasource.mp.service; import com.lhstack.dynamic.datasource.annotation.Transactional; import com.lhstack.dynamic.datasource.mp.mapper.AaaMapper; import com.lhstack.dynamic.datasource.mp.mapper.SssMapper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; /** * @Description TODO * @Copyright: Copyright (c) 2022 ALL RIGHTS RESERVED. * @Author lhstack * @Date 2022/7/16 23:24 * @Modify By */ @Service public class TestService { @Autowired private AaaMapper aaaMapper; @Autowired private SssMapper sssMapper; @Autowired private SssService sssService; @Autowired private AaaService aaaService; @Transactional(rollbackFor = Exception.class) public void rollbackService(){ this.aaaService.insert("rollback-master-service"); this.aaaService.insert1("rollback-master1-service"); this.sssService.insert("rollback-slave-service"); throw new RuntimeException("err"); } @Transactional(rollbackFor = ArithmeticException.class) public void rollbackServiceExpectArithmeticException(){ this.aaaService.insert("master-service"); this.aaaService.insert1("master1-service"); this.sssService.insert("slave-service"); throw new RuntimeException("err"); } } ``` `AaaService.java` ```java package com.lhstack.dynamic.datasource.mp.service; import com.baomidou.mybatisplus.extension.service.IService; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.lhstack.dynamic.datasource.annotation.Transactional; import com.lhstack.dynamic.datasource.mp.entity.Aaa; import com.lhstack.dynamic.datasource.mp.mapper.AaaMapper; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Isolation; import org.springframework.transaction.annotation.Propagation; /** * @Description TODO * @Copyright: Copyright (c) 2022 ALL RIGHTS RESERVED. * @Author lhstack * @Date 2022/7/17 12:36 * @Modify By */ @Service public class AaaService extends ServiceImpl implements IService { public void insert(String value) { this.save(new Aaa().setValue(value)); } @Transactional(readOnly = false, isolation = Isolation.REPEATABLE_READ, propagation = Propagation.NEVER) public void insert1(String master) { this.save(new Aaa().setValue(master)); } } ``` `SssService.java` ```java package com.lhstack.dynamic.datasource.mp.service; import com.baomidou.mybatisplus.extension.service.IService; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.lhstack.dynamic.datasource.mp.entity.Sss; import com.lhstack.dynamic.datasource.mp.mapper.SssMapper; import org.springframework.stereotype.Service; /** * @Description TODO * @Copyright: Copyright (c) 2022 ALL RIGHTS RESERVED. * @Author lhstack * @Date 2022/7/17 12:36 * @Modify By */ @Service public class SssService extends ServiceImpl implements IService { public void insert(String value) { this.save(new Sss().setValue(value)); } } ```