一、yml配置文件信息
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
druid:
master:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.0.15:15520/genius?useUnicode=true&rewriteBatchedStatements=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: sdfCf34f1f
erp:
# 从数据源开关/默认关闭
enabled: true
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/cloud?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
username: root
password: 123456
# 初始连接数
initialSize: 5
# 最小连接池数量
minIdle: 10
# 最大连接池数量
maxActive: 20
# 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
# 配置一个连接在池中最大生存的时间,单位是毫秒
maxEvictableIdleTimeMillis: 900000
# 配置检测连接是否有效
validationQuery: SELECT 1
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
webStatFilter:
enabled: true
statViewServlet:
enabled: true
# 设置白名单,不填则允许所有访问
allow:
url-pattern: /druid/*
# 控制台管理用户名和密码
login-username: ruoyi
login-password: 123456
filter:
stat:
enabled: true
# 慢SQL记录
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
二、druid 配置多数据源
/**
* druid 配置多数据源
*
*/
@Configuration
public class DruidConfig
{
@Bean(name = "masterDataSource")
@ConfigurationProperties("spring.datasource.druid.master")
public DataSource masterDataSource(DruidProperties druidProperties)
{
DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
return druidProperties.dataSource(dataSource);
}
@Bean(name = "erpDataSource")
@ConfigurationProperties("spring.datasource.druid.erp")
@ConditionalOnProperty(prefix = "spring.datasource.druid.erp", name = "enabled", havingValue = "true")
public DataSource erpDataSource(DruidProperties druidProperties)
{
DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
return druidProperties.dataSource(dataSource);
}
@Bean(name = "dynamicDataSource")
@Primary
public DynamicDataSource dataSource(DataSource masterDataSource)
{
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
setDataSource(targetDataSources, DataSourceType.ERP.name(), "erpDataSource");
return new DynamicDataSource(masterDataSource, targetDataSources);
}
/**
* 设置数据源
*
* @param targetDataSources 备选数据源集合
* @param sourceName 数据源名称
* @param beanName bean名称
*/
public void setDataSource(Map<Object, Object> targetDataSources, String sourceName, String beanName)
{
try
{
DataSource dataSource = SpringUtils.getBean(beanName);
targetDataSources.put(sourceName, dataSource);
}
catch (Exception e)
{
}
}
/**
* 去除监控页面底部的广告
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
@Bean
@ConditionalOnProperty(name = "spring.datasource.druid.statViewServlet.enabled", havingValue = "true")
public FilterRegistrationBean removeDruidFilterRegistrationBean(DruidStatProperties properties)
{
// 获取web监控页面的参数
DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
// 提取common.js的配置路径
String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";
String commonJsPattern = pattern.replaceAll("\\*", "js/common.js");
final String filePath = "support/http/resources/js/common.js";
// 创建filter进行过滤
Filter filter = new Filter()
{
@Override
public void init(javax.servlet.FilterConfig filterConfig) throws ServletException
{
}
@Override
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
throws IOException, ServletException
{
chain.doFilter(request, response);
// 重置缓冲区,响应头不会被重置
response.resetBuffer();
// 获取common.js
String text = Utils.readFromResource(filePath);
// 正则替换banner, 除去底部的广告信息
text = text.replaceAll("<a.*?banner\"></a><br/>", "");
text = text.replaceAll("powered.*?shrek.wang</a>", "");
response.getWriter().write(text);
}
@Override
public void destroy()
{
}
};
FilterRegistrationBean registrationBean = new FilterRegistrationBean();
registrationBean.setFilter(filter);
registrationBean.addUrlPatterns(commonJsPattern);
return registrationBean;
}
}
三 DruidProperties 配置属性
@Configuration
public class DruidProperties
{
@Value("${spring.datasource.druid.initialSize}")
private int initialSize;
@Value("${spring.datasource.druid.minIdle}")
private int minIdle;
@Value("${spring.datasource.druid.maxActive}")
private int maxActive;
@Value("${spring.datasource.druid.maxWait}")
private int maxWait;
@Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.druid.maxEvictableIdleTimeMillis}")
private int maxEvictableIdleTimeMillis;
@Value("${spring.datasource.druid.validationQuery}")
private String validationQuery;
@Value("${spring.datasource.druid.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource.druid.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.druid.testOnReturn}")
private boolean testOnReturn;
public DruidDataSource dataSource(DruidDataSource datasource)
{
/** 配置初始化大小、最小、最大 */
datasource.setInitialSize(initialSize);
datasource.setMaxActive(maxActive);
datasource.setMinIdle(minIdle);
/** 配置获取连接等待超时的时间 */
datasource.setMaxWait(maxWait);
/** 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 */
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
/** 配置一个连接在池中最小、最大生存的时间,单位是毫秒 */
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setMaxEvictableIdleTimeMillis(maxEvictableIdleTimeMillis);
/**
* 用来检测连接是否有效的sql,要求是一个查询语句,常用select 'x'。如果validationQuery为null,testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
*/
datasource.setValidationQuery(validationQuery);
/** 建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。 */
datasource.setTestWhileIdle(testWhileIdle);
/** 申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。 */
datasource.setTestOnBorrow(testOnBorrow);
/** 归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。 */
datasource.setTestOnReturn(testOnReturn);
return datasource;
}
}
四、配置动态数据源
public class DynamicDataSource extends AbstractRoutingDataSource
{
public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources)
{
super.setDefaultTargetDataSource(defaultTargetDataSource);
super.setTargetDataSources(targetDataSources);
super.afterPropertiesSet();
}
@Override
protected Object determineCurrentLookupKey()
{
return DynamicDataSourceContextHolder.getDataSourceType();
}
}
五 数据源切换处理配置
public class DynamicDataSourceContextHolder
{
public static final Logger log = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);
/**
* 使用ThreadLocal维护变量,ThreadLocal为每个使用该变量的线程提供独立的变量副本,
* 所以每一个线程都可以独立地改变自己的副本,而不会影响其它线程所对应的副本。
*/
private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
/**
* 设置数据源的变量
*/
public static void setDataSourceType(String dsType)
{
log.info("切换到{}数据源", dsType);
CONTEXT_HOLDER.set(dsType);
}
/**
* 获得数据源的变量
*/
public static String getDataSourceType()
{
return CONTEXT_HOLDER.get();
}
/**
* 清空数据源变量
*/
public static void clearDataSourceType()
{
CONTEXT_HOLDER.remove();
}
}
六 DataSource注解
@Target({ ElementType.METHOD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface DataSource
{
/**
* 切换数据源名称
*/
public DataSourceType value() default DataSourceType.MASTER;
}
七 数据源枚举
public enum DataSourceType
{
/**
* 主库
*/
MASTER,
/**
* 从库
*/
ERP
}
八 、数据源配置
@Configuration
public class DruidConfig
{
@Bean(name = "masterDataSource")
@ConfigurationProperties("spring.datasource.druid.master")
public DataSource masterDataSource(DruidProperties druidProperties)
{
DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
return druidProperties.dataSource(dataSource);
}
@Bean(name = "erpDataSource")
@ConfigurationProperties("spring.datasource.druid.erp")
@ConditionalOnProperty(prefix = "spring.datasource.druid.erp", name = "enabled", havingValue = "true")
public DataSource erpDataSource(DruidProperties druidProperties)
{
DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
return druidProperties.dataSource(dataSource);
}
@Bean(name = "dynamicDataSource")
@Primary
public DynamicDataSource dataSource(DataSource masterDataSource)
{
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DataSourceType.MASTER.name(), masterDataSource);
setDataSource(targetDataSources, DataSourceType.ERP.name(), "erpDataSource");
return new DynamicDataSource(masterDataSource, targetDataSources);
}
/**
* 设置数据源
*
* @param targetDataSources 备选数据源集合
* @param sourceName 数据源名称
* @param beanName bean名称
*/
public void setDataSource(Map<Object, Object> targetDataSources, String sourceName, String beanName)
{
try
{
DataSource dataSource = SpringUtils.getBean(beanName);
targetDataSources.put(sourceName, dataSource);
}
catch (Exception e)
{
}
}
/**
* 去除监控页面底部的广告
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
@Bean
@ConditionalOnProperty(name = "spring.datasource.druid.statViewServlet.enabled", havingValue = "true")
public FilterRegistrationBean removeDruidFilterRegistrationBean(DruidStatProperties properties)
{
// 获取web监控页面的参数
DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
// 提取common.js的配置路径
String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";
String commonJsPattern = pattern.replaceAll("\\*", "js/common.js");
final String filePath = "support/http/resources/js/common.js";
// 创建filter进行过滤
Filter filter = new Filter()
{
@Override
public void init(javax.servlet.FilterConfig filterConfig) throws ServletException
{
}
@Override
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
throws IOException, ServletException
{
chain.doFilter(request, response);
// 重置缓冲区,响应头不会被重置
response.resetBuffer();
// 获取common.js
String text = Utils.readFromResource(filePath);
// 正则替换banner, 除去底部的广告信息
text = text.replaceAll("<a.*?banner\"></a><br/>", "");
text = text.replaceAll("powered.*?shrek.wang</a>", "");
response.getWriter().write(text);
}
@Override
public void destroy()
{
}
};
FilterRegistrationBean registrationBean = new FilterRegistrationBean();
registrationBean.setFilter(filter);
registrationBean.addUrlPatterns(commonJsPattern);
return registrationBean;
}
}
九、数据源配置SqlSessionFactory
非必须,但是为了使用mybatis-plus的现成的方法,方便还是配置一些好,不然每个方法都要继承后加上
@DataSource(value = DataSourceType.ERP) 标明具体数据源才行,配置过后只需要在类上加上就可以了。
/**
* @author liu
* @date 2024/08/28 10:56
**/
@Configuration
@MapperScan(basePackages = "com.ruoyi.system.mapper", sqlSessionFactoryRef = "erpSqlSessionFactory")
public class ErpMybatisConfig {
@Autowired
@Qualifier("erpDataSource")
private DataSource erpDataSource;
@Bean(name = "erpSqlSessionFactory")
@Primary
public SqlSessionFactory erpSqlSessionFactory() throws Exception {
MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();//mybatis-plus
// SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean(); //mybatis
sessionFactory.setDataSource(erpDataSource);
MybatisConfiguration configuration = new MybatisConfiguration();
configuration.setMapUnderscoreToCamelCase(true);
configuration.setDefaultFetchSize(100);
configuration.setDefaultStatementTimeout(30);
sessionFactory.setConfiguration(configuration);
sessionFactory.setTypeAliasesPackage("com.ruoyi.**.domain");
sessionFactory.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/**/*Mapper.xml"));
return sessionFactory.getObject();
}
/**
* 创建事务管理器
* @return
*/
@Bean(name = "erpTransactionManager")
@Primary
public DataSourceTransactionManager erpTransactionManager() {
return new DataSourceTransactionManager(erpDataSource);
}
/**
* 创建sqlSession模板
* @param sqlSessionFactory
* @return
*/
@Bean(name = "erpSqlSessionTemplate")
@Primary
public SqlSessionTemplate erpSqlSessionTemplate(@Qualifier("erpSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
@Primary 注解可以设置默认的SqlSessionFactory是什么数据源,如果需要设置默认数据源可以加上。
/**
* @author liu
* @date 2024/08/28 10:52
**/
@Configuration
@MapperScan(basePackages = "com.ruoyi.system.mapper", sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterMybatisConfig {
@Autowired
@Qualifier("masterDataSource")
private DataSource masterDataSource;
@Bean(name = "masterSqlSessionFactory")
public SqlSessionFactory masterSqlSessionFactory() throws Exception {
MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();//mybatis-plus
// SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();//mybatis
sessionFactory.setDataSource(masterDataSource);
MybatisConfiguration configuration = new MybatisConfiguration();
// ... 其他配置,比如MapperLocations等
configuration.setMapUnderscoreToCamelCase(true);
configuration.setDefaultFetchSize(100);
configuration.setDefaultStatementTimeout(30);
sessionFactory.setTypeAliasesPackage("com.ruoyi.**.domain");
sessionFactory.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/**/*Mapper.xml"));
sessionFactory.setConfiguration(configuration);
return sessionFactory.getObject();
}
/**
* 创建事务管理器
* 事务参考https://www.jianshu.com/p/2014d9a7b41d
* @return
*/
@Bean(name = "masterTransactionManager")
public DataSourceTransactionManager masterTransactionManager() {
return new DataSourceTransactionManager(masterDataSource);
}
/**
* 创建sqlSession模板
* @param sqlSessionFactory
* @return
*/
@Bean(name = "masterSqlSessionTemplate")
public SqlSessionTemplate masterSqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
十、具体使用
@Resource
private PpTrackJobMapper ppTrackJobMapper;
@Resource
private PpTrackJobMapperCaiGang ppTrackJobMapperCaiGang;
@Override
public EmployeeUnit getEmployeeUnit(String id) {
//会切换不同的数据源
EmployeeUnit employeeUnit = ppTrackJobMapper.selectById(id);
System.out.println(employeeUnit);
EmployeeUnit employeeUnit2 = ppTrackJobMapperCaiGang.selectById(id);
System.out.println(employeeUnit2);
return null;
}
@Mapper
@DataSource(value = DataSourceType.ERP)
public interface PpTrackJobMapper extends BaseMapper<EmployeeUnit> {
}
@Mapper
//@DataSource(value = DataSourceType.MASTER) //Master是默认数据源,如果没有指定,优先用Master
public interface PpTrackJobMapperCaiGang extends BaseMapper<EmployeeUnit> {
}