SpringBoot多数据源配置
一.在配置文件中配置多个数据数据源 这里配了3数据源，注意如果在同一个ip上 端口号一定不可以一样 不然会报错
spring:
datasource:
druid:
type: com.alibaba.druid.pool.DruidDataSource
password-callback: com.unqd.ims.utils.DbPwdCallback
first:
url: jdbc:sqlserver://192.168.111.51:1433;DatabaseName=UNQDIMS
username: dev1
password: NrPK+5eEJ8t6oZuWUOZZCwfXY4RYUiIciV18gOo8k2eE/MbomXHvkambrT1Vs0BaG2snppx5Yr7KViJp6q7tBQ==
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
publicKey: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAIl9if+jPfZsPvnfaCo8prXUgpAEOx7aEYacTOoDgNoiYdeg9ubKegNlQslSiaFDinwHMNhCYudJYmQDipqN4QUCAwEAAQ==
connection-properties: config.decrypt=true;publickey=${spring.datasource.druid.first.publicKey};password=${spring.datasource.druid.first.password}
second:
url: jdbc:mysql://114.116.111.95:3306/lianjian-resourcepool?useUnicode=true&characterEncoding=utf8
username: root
password: BFpmysWrX+gCM+A6gOgAau3oel/QFWoISDjTRFHXdNFxy124B+tbMOLIThOmpKXvQfBg4Kaq5OzyZsmbZqaTYA==
driver-class-name: com.mysql.jdbc.Driver
publicKey: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAJ9NJABzcWRV96EtN8DmfymCP9qOgVSIqlqeCNar1GvLwIJYBoc9h6ArmXbPIusmgsJAwqJ09luq9GjkBpJ5H+MCAwEAAQ==
connection-properties: config.decrypt=true;publickey=${spring.datasource.druid.second.publicKey};password=${spring.datasource.druid.second.password}
third:
url: jdbc:mysql://114.116.243.11:3306/lianjian-manage?useUnicode=true&characterEncoding=utf8
username: root
password: BFpmysWrX+gCM+A6gOgAau3oel/QFWoISDjTRFHXdNFxy124B+tbMOLIThOmpKXvQfBg4Kaq5OzyZsmbZqaTYA==
driver-class-name: com.mysql.jdbc.Driver
publicKey: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAJ9NJABzcWRV96EtN8DmfymCP9qOgVSIqlqeCNar1GvLwIJYBoc9h6ArmXbPIusmgsJAwqJ09luq9GjkBpJ5H+MCAwEAAQ==
connection-properties: config.decrypt=true;publickey=${spring.datasource.druid.third.publicKey};password=${spring.datasource.druid.third.password}
initial-size: 1
max-active: 20
min-idle: 1
max-wait: 60000
pool-prepared-statements: false
max-pool-prepared-statement-per-connection-size: 20
validation-query: SELECT 'x'
test-on-borrow: false
test-on-return: false
test-while-idle: true
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
filters: stat
二.项目目录
三.代码
1.DataSource.java 注解类
package com.unqd.ims.datasources.annotation;
import java.lang.annotation.*;
/**
* 多数据源注解
* @author xiaoming
* @email fyzhangs@126.com
* @date 2020/3/15
*/
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
String name() default "";
}
2.编写切边类 DataSourceAspect.java
package com.unqd.ims.datasources.aspect;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.Ordered;
import org.springframework.stereotype.Component;
import com.unqd.ims.datasources.DynamicDataSource;
import com.unqd.ims.datasources.annotation.DataSource;
import java.lang.reflect.Method;
/**
* 多数据源，切面处理类
* @author xiaoming
* @email fyzhangs@126.com
* @date 2020/3/15
*/
@Aspect
@Component
public class DataSourceAspect implements Ordered
{
protected Logger logger = LoggerFactory.getLogger(getClass());
@Pointcut("@annotation(com.unqd.ims.datasources.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("first");
logger.debug("set datasource is " + "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.java
package com.unqd.ims.datasources;
/**
* 增加多数据源，在此配置
* @author xiaoming
* @email fyzhangs@126.com
* @date 2020/3/15
*/
public interface DataSourceNames {
String FIRST = "first";
String SECOND = "second";
String THIRD = "third";
}
4.DynamicDataSource.java动态数据源
package com.unqd.ims.datasources;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
/**
* 动态数据源
* @author xiaoming
* @email fyzhangs@126.com
* @date 2020/3/15
*/
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();
}
}
4.DynamicDataSourceConfig.java 数据库配置类
package com.unqd.ims.datasources;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
/**
* 配置多数据源
* @author xiaoming
* @email fyzhangs@126.com
* @date 2020/3/15
*/
@Configuration
public class DynamicDataSourceConfig {
private static final Logger LOGGER = LoggerFactory.getLogger(DynamicDataSourceConfig.class);
@Value("${spring.datasource.druid.type}")
private String dbType;
@Value("${spring.datasource.druid.initial-size}")
private int initialSize;
@Value("${spring.datasource.druid.min-idle}")
private int minIdle;
@Value("${spring.datasource.druid.max-active}")
private int maxActive;
@Value("${spring.datasource.druid.max-wait}")
private int maxWait;郑州哪里做人流好 http://www.kdrlyy.com/
@Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.druid.validation-query}")
private String validationQuery;
@Value("${spring.datasource.druid.test-while-idle}")
private boolean testWhileIdle;
@Value("${spring.datasource.druid.test-on-borrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.druid.test-on-return}")
private boolean testOnReturn;
@Value("${spring.datasource.druid.pool-prepared-statements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.druid.filters}")
private String filters;
@Value("${spring.datasource.druid.first.connection-properties}")
private String firstConnectionProperties;
@Value("${spring.datasource.druid.second.connection-properties}")
private String secondConnectionProperties;
@Value("${spring.datasource.druid.third.connection-properties}")
private String thirdConnectionProperties;
@Value("${spring.datasource.druid.password-callback}")
private String passwordCallbackClassName;
@Bean
@ConfigurationProperties("spring.datasource.druid.first")
public DataSource firstDataSource(){
DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
dataSource.setConnectionProperties(firstConnectionProperties);
addCommonProperties(dataSource);
return dataSource;
}
@Bean
@ConfigurationProperties("spring.datasource.druid.second")
public DataSource secondDataSource(){
DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
dataSource.setConnectionProperties(secondConnectionProperties);
addCommonProperties(dataSource);
return dataSource;
}
@Bean
@ConfigurationProperties("spring.datasource.druid.third")
public DataSource thirdDataSource(){
DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
dataSource.setConnectionProperties(thirdConnectionProperties);
addCommonProperties(dataSource);
return dataSource;
}
private void addCommonProperties(DruidDataSource dataSource)
{
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
dataSource.setMaxWait(maxWait);
dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dataSource.setValidationQuery(validationQuery);
dataSource.setTestWhileIdle(testWhileIdle);
dataSource.setTestOnBorrow(testOnBorrow);
dataSource.setTestOnReturn(testOnReturn);
dataSource.setPoolPreparedStatements(poolPreparedStatements);
dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
dataSource.setDbType(dbType);
try {
dataSource.setPasswordCallbackClassName(passwordCallbackClassName);
} catch (Exception e) {
LOGGER.error("druid configuration initialization passwordCallbackClassName", e);
}
try {
dataSource.setFilters(filters);
} catch (SQLException e) {
LOGGER.error("druid configuration initialization filter", e);
}
}
@Bean
@Primary
public DynamicDataSource dataSource(DataSource firstDataSource, DataSource secondDataSource,DataSource thirdDataSource) {
Map targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceNames.FIRST, firstDataSource);
targetDataSources.put(DataSourceNames.SECOND, secondDataSource);
targetDataSources.put(DataSourceNames.THIRD, thirdDataSource);
return new DynamicDataSource(firstDataSource, targetDataSources);
}
}
使用时在server层方法上加上@DataSource(name = DataSourceNames.SECOND)
指定要链接的数据源名称
例如：
@Override
@DataSource(name = DataSourceNames.SECOND)
public void saveOrUpdate()
{
}
