SpringBoot配置多數據源多數據庫

Springboot支持配置多數據源。默認情況，在yml文件中只會配置一個數據庫。如果涉及到操作多個數據庫的情況，在同實例中（即同一個ip地址下的不同數據庫），可以采用數據庫名點數據庫表的方式，實現跨庫表的操作。（database.table）但這種方式屬于硬編碼，如果數據庫名變化意味著代碼也要變化，不易維護。

下面是在springboot項目中，配置多數據源。需要添加幾個配置類，和相關注解的方式，實現在某個特定方法層面下切換數據源。

1、目錄結構

2、具體代碼

1）DataSource

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface DataSource {String name() default "";
}

2）DataSourceAspect

/*** 多數據源，切面處理類**/
@Aspect
@Component
@Order(Ordered.HIGHEST_PRECEDENCE)
public class DataSourceAspect implements Ordered {protected Logger logger = LoggerFactory.getLogger(getClass());@Pointcut("@annotation(io.installer.commons.dynamic.datasource.annotation.DataSource)")public void dataSourcePointCut() {}@Around("dataSourcePointCut()")public Object around(ProceedingJoinPoint point) throws Throwable {MethodSignature signature = (MethodSignature) point.getSignature();Method method = signature.getMethod();DataSource ds = method.getAnnotation(DataSource.class);if (ds == null) {DynamicDataSource.setDataSource(DataSourceNames.FIRST);logger.debug("set datasource is " + DataSourceNames.FIRST);} else {DynamicDataSource.setDataSource(ds.name());logger.debug("set datasource is " + ds.name());}try {return point.proceed();} finally {DynamicDataSource.clearDataSource();logger.debug("clean datasource");}}@Overridepublic int getOrder() {return 1;}
}

3）DataSourceNames

public interface DataSourceNames {String FIRST = "first";String SECOND = "second";
}

4）DynamicContextHolder

/*** 多數據源上下文**/
public class DynamicContextHolder {private static final ThreadLocal<Deque<String>> CONTEXT_HOLDER = ThreadLocal.withInitial(ArrayDeque::new);/*** 獲得當前線程數據源** @return 數據源名稱*/public static String peek() {return CONTEXT_HOLDER.get().peek();}/*** 設置當前線程數據源** @param dataSource 數據源名稱*/public static void push(String dataSource) {CONTEXT_HOLDER.get().push(dataSource);}/*** 清空當前線程數據源*/public static void poll() {Deque<String> deque = CONTEXT_HOLDER.get();deque.poll();if (deque.isEmpty()) {CONTEXT_HOLDER.remove();}}}

5）DynamicDataSource

/*** 多數據源** @author Mark sunlightcs@gmail.com* @since 1.0.0*/
public class DynamicDataSource extends AbstractRoutingDataSource {private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {//設置默認數據源super.setDefaultTargetDataSource(defaultTargetDataSource);super.setTargetDataSources(targetDataSources);super.afterPropertiesSet();}@Overrideprotected Object determineCurrentLookupKey() {//獲取數據源，沒有指定，則為默認數據源return getDataSource();}public static void setDataSource(String dataSource) {contextHolder.set(dataSource);}public static String getDataSource() {return contextHolder.get();}public static void clearDataSource() {contextHolder.remove();}}

6）DynamicDataSourceConfig

@Configuration
@EnableConfigurationProperties(DynamicDataSourceProperties.class)
public class DynamicDataSourceConfig {//數據源1，讀取spring.datasource.druid.first下的配置信息@Bean@ConfigurationProperties("spring.datasource.druid.first")public DataSource firstDataSource() {return DruidDataSourceBuilder.create().build();}//數據源2，讀取spring.datasource.druid.second下的配置信息@Bean@ConfigurationProperties("spring.datasource.druid.second")public DataSource secondDataSource() {return DruidDataSourceBuilder.create().build();}//加了@Primary注解，表示指定DynamicDataSource為Spring的數據源//因為DynamicDataSource是繼承與AbstractRoutingDataSource，而AbstractR//outingDataSource又是繼承于AbstractDataSource，AbstractDataSource實現了統一//的DataSource接口，所以DynamicDataSource也可以當做DataSource使用@Bean@Primarypublic DynamicDataSource dataSource(DataSource firstDataSource, DataSource secondDataSource) {Map<Object, Object> targetDataSources = new HashMap<>();targetDataSources.put(DataSourceNames.FIRST, firstDataSource);targetDataSources.put(DataSourceNames.SECOND, secondDataSource);return new DynamicDataSource(firstDataSource, targetDataSources);}
}

7）DynamicDataSourceFactory

public class DynamicDataSourceFactory {public static DruidDataSource buildDruidDataSource(DataSourceProperties properties) {DruidDataSource druidDataSource = new DruidDataSource();druidDataSource.setDriverClassName(properties.getDriverClassName());druidDataSource.setUrl(properties.getUrl());druidDataSource.setUsername(properties.getUsername());druidDataSource.setPassword(properties.getPassword());druidDataSource.setInitialSize(properties.getInitialSize());druidDataSource.setMaxActive(properties.getMaxActive());druidDataSource.setMinIdle(properties.getMinIdle());druidDataSource.setMaxWait(properties.getMaxWait());druidDataSource.setTimeBetweenEvictionRunsMillis(properties.getTimeBetweenEvictionRunsMillis());druidDataSource.setMinEvictableIdleTimeMillis(properties.getMinEvictableIdleTimeMillis());druidDataSource.setMaxEvictableIdleTimeMillis(properties.getMaxEvictableIdleTimeMillis());druidDataSource.setValidationQuery(properties.getValidationQuery());druidDataSource.setValidationQueryTimeout(properties.getValidationQueryTimeout());druidDataSource.setTestOnBorrow(properties.isTestOnBorrow());druidDataSource.setTestOnReturn(properties.isTestOnReturn());druidDataSource.setPoolPreparedStatements(properties.isPoolPreparedStatements());druidDataSource.setMaxOpenPreparedStatements(properties.getMaxOpenPreparedStatements());druidDataSource.setSharePreparedStatements(properties.isSharePreparedStatements());try {// druidDataSource.setFilters(properties.getFilters());druidDataSource.init();} catch (SQLException e) {e.printStackTrace();}return druidDataSource;}
}

8）DataSourceProperties

public class DataSourceProperties {private String driverClassName;private String url;private String username;private String password;/*** Druid默認參數*/private int initialSize = 2;private int maxActive = 10;private int minIdle = -1;private long maxWait = 60 * 1000L;private long timeBetweenEvictionRunsMillis = 60 * 1000L;private long minEvictableIdleTimeMillis = 1000L * 60L * 30L;private long maxEvictableIdleTimeMillis = 1000L * 60L * 60L * 7;private String validationQuery = "select 1";private int validationQueryTimeout = -1;private boolean testOnBorrow = false;private boolean testOnReturn = false;private boolean testWhileIdle = true;private boolean poolPreparedStatements = false;private int maxOpenPreparedStatements = -1;private boolean sharePreparedStatements = false;private String filters = "stat,wall";public String getDriverClassName() {return driverClassName;}public void setDriverClassName(String driverClassName) {this.driverClassName = driverClassName;}public String getUrl() {return url;}public void setUrl(String url) {this.url = url;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public int getInitialSize() {return initialSize;}public void setInitialSize(int initialSize) {this.initialSize = initialSize;}public int getMaxActive() {return maxActive;}public void setMaxActive(int maxActive) {this.maxActive = maxActive;}public int getMinIdle() {return minIdle;}public void setMinIdle(int minIdle) {this.minIdle = minIdle;}public long getMaxWait() {return maxWait;}public void setMaxWait(long maxWait) {this.maxWait = maxWait;}public long getTimeBetweenEvictionRunsMillis() {return timeBetweenEvictionRunsMillis;}public void setTimeBetweenEvictionRunsMillis(long timeBetweenEvictionRunsMillis) {this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;}public long getMinEvictableIdleTimeMillis() {return minEvictableIdleTimeMillis;}public void setMinEvictableIdleTimeMillis(long minEvictableIdleTimeMillis) {this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;}public long getMaxEvictableIdleTimeMillis() {return maxEvictableIdleTimeMillis;}public void setMaxEvictableIdleTimeMillis(long maxEvictableIdleTimeMillis) {this.maxEvictableIdleTimeMillis = maxEvictableIdleTimeMillis;}public String getValidationQuery() {return validationQuery;}public void setValidationQuery(String validationQuery) {this.validationQuery = validationQuery;}public int getValidationQueryTimeout() {return validationQueryTimeout;}public void setValidationQueryTimeout(int validationQueryTimeout) {this.validationQueryTimeout = validationQueryTimeout;}public boolean isTestOnBorrow() {return testOnBorrow;}public void setTestOnBorrow(boolean testOnBorrow) {this.testOnBorrow = testOnBorrow;}public boolean isTestOnReturn() {return testOnReturn;}public void setTestOnReturn(boolean testOnReturn) {this.testOnReturn = testOnReturn;}public boolean isTestWhileIdle() {return testWhileIdle;}public void setTestWhileIdle(boolean testWhileIdle) {this.testWhileIdle = testWhileIdle;}public boolean isPoolPreparedStatements() {return poolPreparedStatements;}public void setPoolPreparedStatements(boolean poolPreparedStatements) {this.poolPreparedStatements = poolPreparedStatements;}public int getMaxOpenPreparedStatements() {return maxOpenPreparedStatements;}public void setMaxOpenPreparedStatements(int maxOpenPreparedStatements) {this.maxOpenPreparedStatements = maxOpenPreparedStatements;}public boolean isSharePreparedStatements() {return sharePreparedStatements;}public void setSharePreparedStatements(boolean sharePreparedStatements) {this.sharePreparedStatements = sharePreparedStatements;}public String getFilters() {return filters;}public void setFilters(String filters) {this.filters = filters;}
}

9）DynamicDataSourceProperties

@ConfigurationProperties(prefix = "dynamic")
public class DynamicDataSourceProperties {private Map<String, DataSourceProperties> datasource = new LinkedHashMap<>();public Map<String, DataSourceProperties> getDatasource() {return datasource;}public void setDatasource(Map<String, DataSourceProperties> datasource) {this.datasource = datasource;}
}

10）yml配置

spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedruid:first:#MySQLurl: jdbc:mysql://localhost:3306/database1?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8username: rootpassword: rootsecond:#MySQLurl: jdbc:mysql://localhost:3306/database2?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8username: rootpassword: rootdriver-class-name: com.mysql.cj.jdbc.Driverinitial-size: 10max-active: 100min-idle: 10max-wait: 6000pool-prepared-statements: truemax-pool-prepared-statement-per-connection-size: 20time-between-eviction-runs-millis: 60000min-evictable-idle-time-millis: 300000#Oracle需要打開注釋#      validation-query: SELECT 1 FROM DUALtest-while-idle: truetest-on-borrow: falsetest-on-return: falsestat-view-servlet:enabled: trueurl-pattern: /druid/*

3、測試代碼

1）DynamicDataSourceTestService

/*** 測試多數據源** @author Mark sunlightcs@gmail.com*/
@Service
//@DataSource(name = DataSourceNames.FIRST)
public class DynamicDataSourceTestService {@Resourceprivate SysUserDao sysUserDao;//@Transactionalpublic void updateUser(Long id) {SysUserEntity user = new SysUserEntity();user.setId(id);user.setMobile("13500000000");//sysUserDao.updateById(user);System.out.println(sysUserDao.selectById(id));}@DataSource(name = DataSourceNames.SECOND)@Transactionalpublic void updateUserBySlave1(Long id) {SysUserEntity user = new SysUserEntity();user.setId(id);user.setMobile("13500000001");//sysUserDao.updateById(user);System.out.println(sysUserDao.selectById(id));}//    @DataSource(name = DataSourceNames.SECOND)
//    @Transactional
//    public void updateUserBySlave2(Long id){
//        SysUserEntity user = new SysUserEntity();
//        user.setId(id);
//        user.setMobile("13500000002");
//        sysUserDao.updateById(user);
//
//        //測試事物
//        int i = 1/0;
//    }
}

2）DynamicDataSourceTest

@RunWith(SpringRunner.class)
@SpringBootTest
public class DynamicDataSourceTest {@Resourceprivate DynamicDataSourceTestService dynamicDataSourceTestService;@Testpublic void test() {Long id = 1067246875800000001L;//        dynamicDataSourceTestService.updateUser(id);
//        dynamicDataSourceTestService.updateUserBySlave1(id);dynamicDataSourceTestService.updateUserBySlave2(id);}}