在实际系统开发中有时一个程序可能需要连接多个数据库，这就用到了多数据功能，废话多说直接上代码…
1.配置文件
# 数据源
db:
mysql:
driverClassName: com.mysql.jdbc.Driver
m1:
datasource:
password: 123456
url: jdbc:mysql://localhost:3306/m1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username: root
s1:
datasource:
password: TESTDB2020!pubaogz.com
url: jdbc:mysql://localhost:3306/s1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username: root
# 连接池参数，可以不用配置 由dba在数据库层统一控制
m1pool:
datasource:
initialSize: 20
maxActive: 200
maxPoolPreparedStatementPerConnectionSize: 20
maxWait: 60000
minEvictableIdleTimeMillis: 300000
minIdle: 20
poolPreparedStatements: true
testOnBorrow: false
testOnReturn: false
testWhileIdle: true
timeBetweenEvictionRunsMillis: 60000
m2:
datasource:
password: TESTDB2020!pubaogz.com
url: jdbc:mysql://localhost:3306/m2?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username: root
s2:
datasource:
password: 123456
url: jdbc:mysql://localhost:3306/s2?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8
username: root
# 连接池参数，可以不用配置 由dba在数据库层统一控制
m2pool:
datasource:
initialSize: 20
maxActive: 200
maxPoolPreparedStatementPerConnectionSize: 20
maxWait: 60000
minEvictableIdleTimeMillis: 300000
minIdle: 20
poolPreparedStatements: true
testOnBorrow: false
testOnReturn: false
testWhileIdle: true
timeBetweenEvictionRunsMillis: 60000
- Druid 监控
package com.app.db.datasource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* Druid 控制台
* @author liuli
*/
@Configuration
public class DruidServletConfig {
/**
* 注册Servlet信息， 配置监控视图
* @return
*/
@Bean
@ConditionalOnMissingBean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
//白名单：
//servletRegistrationBean.addInitParameter("allow","192.168.6.195");
//IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
//servletRegistrationBean.addInitParameter("deny","192.168.6.73");
//登录查看信息的账号密码, 用于登录Druid监控后台
servletRegistrationBean.addInitParameter("loginUsername", "admin");
servletRegistrationBean.addInitParameter("loginPassword", "admin");
//是否能够重置数据.
servletRegistrationBean.addInitParameter("resetEnable", "true");
return servletRegistrationBean;
}
/**
* 注册Filter信息, 监控拦截器
* @return
*/
@Bean
@ConditionalOnMissingBean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}
3.关键类
@Configuration
@MapperScan(basePackages = DataSourceConstant.M1_PACKAGE, sqlSessionFactoryRef = "m1SqlSessionFactory")
/**
* 数据源配置
* @author liuli
*/
public class M1DataSourceConfig {
/**M1*/
@Value("${m1.datasource.url}")
private String m1Url;
@Value("${m1.datasource.username}")
private String m1User;
@Value("${m1.datasource.password}")
private String m1Password;
/**S1*/
@Value("${s1.datasource.url}")
private String s1Url;
@Value("${s1.datasource.username}")
private String s1User;
@Value("${s1.datasource.password}")
private String s1Password;
/**驱动*/
@Value("${db.mysql.driverClassName}")
private String driverClass;
/**pool*/
@Value("${m1pool.datasource.maxActive}")
private Integer maxActive;
@Value("${m1pool.datasource.minIdle}")
private Integer minIdle;
@Value("${m1pool.datasource.initialSize}")
private Integer initialSize;
@Value("${m1pool.datasource.maxWait}")
private Long maxWait;
@Value("${m1pool.datasource.timeBetweenEvictionRunsMillis}")
private Long timeBetweenEvictionRunsMillis;
@Value("${m1pool.datasource.minEvictableIdleTimeMillis}")
private Long minEvictableIdleTimeMillis;
@Value("${m1pool.datasource.testWhileIdle}")
private Boolean testWhileIdle;
@Value("${m1pool.datasource.testWhileIdle}")
private Boolean testOnBorrow;
@Value("${m1pool.datasource.testOnBorrow}")
private Boolean testOnReturn;
@Value("${m2pool.datasource.maxPoolPreparedStatementPerConnectionSize}")
private Integer maxPoolPreparedStatementPerConnectionSize;
@Bean(name = "m1DataSource")
@Primary
public DataSource m1DataSource() {
M1DynamicDataSource dynamicDataSource = M1DynamicDataSource.getInstance();
DruidDataSource masterDataSource = new DruidDataSource();
masterDataSource.setDriverClassName(driverClass);
masterDataSource.setUrl(m1Url);
masterDataSource.setUsername(m1User);
masterDataSource.setPassword(m1Password);
//连接池配置
masterDataSource.setMaxActive(maxActive);
masterDataSource.setMinIdle(minIdle);
masterDataSource.setInitialSize(initialSize);
masterDataSource.setMaxWait(maxWait);
masterDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
masterDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
masterDataSource.setTestWhileIdle(testWhileIdle);
masterDataSource.setTestOnBorrow(testOnBorrow);
masterDataSource.setTestOnReturn(testOnReturn);
masterDataSource.setValidationQuery("SELECT 'x'");
masterDataSource.setPoolPreparedStatements(true);
masterDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
//监控统计用的filter:stat 日志用的filter:log4j 防御sql注入的filter:wall
try {
masterDataSource.setFilters("stat,wall");
} catch (SQLException e) {
}
DruidDataSource slaveDataSource = new DruidDataSource();
slaveDataSource.setDriverClassName(driverClass);
slaveDataSource.setUrl(s1Url);
slaveDataSource.setUsername(s1User);
slaveDataSource.setPassword(s1Password);
//连接池配置
slaveDataSource.setMaxActive(maxActive);
slaveDataSource.setMinIdle(minIdle);
slaveDataSource.setInitialSize(initialSize);
slaveDataSource.setMaxWait(maxWait);
slaveDataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
slaveDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
slaveDataSource.setTestWhileIdle(testWhileIdle);
slaveDataSource.setTestOnBorrow(testOnBorrow);
slaveDataSource.setTestOnReturn(testOnReturn);
slaveDataSource.setValidationQuery("SELECT 'x'");
slaveDataSource.setPoolPreparedStatements(true);
slaveDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
//通过别名的方式配置扩展插件，常用的插件有：
//监控统计用的filter:stat 日志用的filter:log4j 防御sql注入的filter:wall
slaveDataSource.setFilters("stat,wall");
} catch (SQLException e) {
}
Map<Object,Object> map = new HashMap<>();
map.put(DataSourceConstant.DB_MASTER1, masterDataSource);
map.put(DataSourceConstant.DB_SLAVE1, slaveDataSource);
dynamicDataSource.setTargetDataSources(map);
// 默认数据源 MASTER
dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
return dynamicDataSource;
}
@Bean(name = "m1TransactionManager")
@Primary
public DataSourceTransactionManager m1TransactionManager() {
return new DataSourceTransactionManager(m1DataSource());
}
@Bean(name = "m1SqlSessionFactory")
@Primary
public SqlSessionFactory m1SqlSessionFactory(@Qualifier("m1DataSource") DataSource m1DataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(m1DataSource);
sessionFactory.setTypeAliasesPackage(DataSourceConstant.M1_PACKAGE);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(DataSourceConstant.M1_MAPPER_LOCATION));
return sessionFactory.getObject();
}
/**
* db1 数据源切换
* @author liuli
*/
public class M1DataBaseContextHolder {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
private static Logger log = LoggerFactory.getLogger(M1DataBaseContextHolder.class);
/**切换主库*/
public static void setMaster() {
contextHolder.set(DataSourceConstant.DB_MASTER1);
}
/**切换从库*/
public static void setSalve() {
contextHolder.set(DataSourceConstant.DB_SLAVE1);
}
/**获取数据源*/
public static String getDBKey() {
return contextHolder.get();
}
/**清除数据源*/
public static void clearDBKey() {
contextHolder.remove();
}
}
/**
* 动态数据源
* @author liuli
*
*/
public class M1DynamicDataSource extends AbstractRoutingDataSource {
private static M1DynamicDataSource instance;
private static byte[] lock=new byte[0];
private static Map<Object,Object> dataSourceMap=new HashMap<Object, Object>();
@Override
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
super.setTargetDataSources(targetDataSources);
dataSourceMap.putAll(targetDataSources);
// 必须添加该句，否则新添加数据源无法识别到
super.afterPropertiesSet();
}
public Map<Object, Object> getDataSourceMap() {
return dataSourceMap;
}
@Override
protected Object determineCurrentLookupKey() {
String dbKey = M1DataBaseContextHolder.getDBKey();
/*if (StringUtils.isBlank(dbKey)) {
dbKey = "read";
}*/
return dbKey;
}
private M1DynamicDataSource() {}
public static synchronized M1DynamicDataSource getInstance(){
if(instance==null){
synchronized (lock){
if(instance==null){
instance=new M1DynamicDataSource();
}
}
}
return instance;
}
}
