SpringBoot配置多数据源多数据库
Springboot支持配置多数据源。默认情况，在yml文件中只会配置一个数据库。如果涉及到操作多个数据库的情况，在同实例中（即同一个ip地址下的不同数据库），可以采用数据库名点数据库表的方式，实现跨库表的操作。（database.table）但这种方式属于硬编码，如果数据库名变化意味着代码也要变化，不易维护。
下面是在springboot项目中，配置多数据源。需要添加几个配置类，和相关注解的方式，实现在某个特定方法层面下切换数据源。
1、目录结构
2、具体代码
1）DataSource
@Target({ElementType.METHOD, ElementType.TYPE})@Retention(RetentionPolicy.RUNTIME)@Documented@Inheritedpublic @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\"); } } @Override public 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> 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 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 contextHolder = new ThreadLocal(); public DynamicDataSource(DataSource defaultTargetDataSource, Map targetDataSources) { //设置默认数据源 super.setDefaultTargetDataSource(defaultTargetDataSource); super.setTargetDataSources(targetDataSources); super.afterPropertiesSet(); } @Override protected 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 @Primary public DynamicDataSource dataSource(DataSource firstDataSource, DataSource secondDataSource) { Map 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 datasource = new LinkedHashMap(); public Map getDatasource() { return datasource; } public void setDatasource(Map datasource) { this.datasource = datasource; }}10）yml配置
spring: datasource: type: com.alibaba.druid.pool.DruidDataSource druid: first: #MySQL url: jdbc:mysql://localhost:3306/database1?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8 username: root password: root second: #MySQL url: jdbc:mysql://localhost:3306/database2?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=GMT%2B8 username: root password: root driver-class-name: com.mysql.cj.jdbc.Driver initial-size: 10 max-active: 100 min-idle: 10 max-wait: 6000 pool-prepared-statements: true max-pool-prepared-statement-per-connection-size: 20 time-between-eviction-runs-millis: 60000 min-evictable-idle-time-millis: 300000 #Oracle需要打开注释 # validation-query: SELECT 1 FROM DUAL test-while-idle: true test-on-borrow: false test-on-return: false stat-view-servlet: enabled: true url-pattern: /druid/*3、测试代码
1）DynamicDataSourceTestService
/** * 测试多数据源 * * @author Mark sunlightcs@gmail.com */@Service//@DataSource(name = DataSourceNames.FIRST)public class DynamicDataSourceTestService { @Resource private SysUserDao sysUserDao; //@Transactional public 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) @Transactional public 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)@SpringBootTestpublic class DynamicDataSourceTest { @Resource private DynamicDataSourceTestService dynamicDataSourceTestService; @Test public void test() { Long id = 1067246875800000001L;// dynamicDataSourceTestService.updateUser(id);// dynamicDataSourceTestService.updateUserBySlave1(id); dynamicDataSourceTestService.updateUserBySlave2(id); }}
