springboot2多数据源完整示例
springboot2 + mybatis + mysql + oracle + sqlserver多数据源的配置
- 相信很多朋友在开发的时候，可能会碰到需要一个项目，配置多个数据源的需求，可能是同一种数据库，也可能是不同种类的数据库。
- 这种情况，我们就需要配置多数据源对程序的支持了。
- （本例理论上支持很多种数据库，或者同种数据库配置多个库分别作数据源也许。拓展新强）
环境介绍
- web框架：SpringBoot2
- orm框架：Mybatis
- 数据库连接池：Druid
- 主数据源：Mysql
- 从数据源1：Oracle
- 从数据源2：SqlServer
- 运行平台：Jdk8
- 接口文档：Swagger-ui （提供伪Restful接口）
- 日志配置：Logback
基本思路
- 自定义多个数据源，并指定切换规则
- 引入ThreadLocal来保存和管理数据源上下文标识
- 使用AOP切面编程，根据某些自定义条件，动态切换数据源(反射)
- 访问接口测试效果
大致步骤
- 创建一个拥有ThreadLocal变量的类，用来存取数据源名称
public class JdbcContextHolder {/** 本地线程共享对象（保证在同一线程下切换后不要被其他线程修改） */
private final static ThreadLocal<String> local = new ThreadLocal<>();public static void putDataSource(String name){
local.set(name);
}public static String getDataSource(){
return local.get();
}public static void removeDataSource(){
local.remove();
}}
- 创建一个枚举类，用来存放每个数据源的标识符（标识符是自定义的）
public enum DataSourceType { Mysql("mysql"), Oracle("oracle");private String name;
DataSourceType(String name) {
this.name = name;
}public String getName() {
return name;
}public void setName(String name) {
this.name = name;
}
} - 在启动类上，禁用springboot自动配置的数据源：(exclude = DataSourceAutoConfiguration.class)
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class) @ComponentScan("com.dcm.*.**.**") public class MoreDsApplication extends SpringBootServletInitializer {public static void main(String[] args) {
SpringApplication.run(MoreDsApplication.class, args);
System.out.println("[---------------more_ds项目: started......]");}
/** 创建一个SpringApplicationBuilder交付给springboot框架来完成初始化运行配置 */
@Override
protected SpringApplicationBuilder configure(SpringApplicationBuilder application) {
return application.sources(MoreDsApplication.class);
}
} - 创建动态数据源类，接管springboot的数据源配置
public class DynamicDataSource extends AbstractRoutingDataSource {/** 数据源路由，此方法用于产生要选取的数据源逻辑名称 */
@Override
protected Object determineCurrentLookupKey() {
//从共享线程中获取数据源名称
return JdbcContextHolder.getDataSource();
}
} - 在application.yml文件中加入自定义的多数据源的配置
datasource: druid: type: com.alibaba.druid.pool.DruidDataSource initialSize: 1 minIdle: 3 maxActive: 20 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 30000 testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 20 # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 filters: stat,wall,slf4j # 通过connectProperties属性来打开mergeSql功能；慢SQL记录 connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 # 合并多个DruidDataSource的监控数据 #useGlobalDataSourceStat: true mysql: url: jdbc:mysql://192.168.0.241:3306/pmpmain?useUnicode=true&useSSL=false&characterEncoding=utf8 username: sdcm password: Sdcm_123456 driverClassName: com.mysql.jdbc.Driver validationQuery: select 'x' oracle: url: jdbc:oracle:thin:@192.168.0.200:1522:sdcm username: sdcm password: Sdcm123456 driverClassName: oracle.jdbc.OracleDriver validationQuery: select 1 from dual - 根据application.yml的多数据源配置，初始化各数据源并指定默认数据源
@Configuration public class DataSourceConfig {private Logger logger = LoggerFactory.getLogger(this.getClass());
// -----------------------------------------mysql config-------------------------------------
@Value("${datasource.mysql.url}")
private String dbUrl;@Value("${datasource.mysql.username}")
private String username;@Value("${datasource.mysql.password}")
private String password;@Value("${datasource.mysql.driverClassName}")
private String driverClassName;@Value("${datasource.mysql.validationQuery}")
private String validationQuery;@Bean(name="dataSourceMysql")
public DataSource dataSourceMysql(){
System.out.println("----------------主配" + dbUrl);DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(dbUrl);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
datasource.setValidationQuery(validationQuery);
setDruidOptions(datasource); // 设置druid数据源的属性return datasource;
}// -----------------------------------------oracle config-------------------------------------
@Value("${datasource.oracle.url}")
private String oracleUrl;@Value("${datasource.oracle.username}")
private String oracleUsername;@Value("${datasource.oracle.password}")
private String oraclePassword;@Value("${datasource.oracle.driverClassName}")
private String oracleDriverClassName;@Value("${datasource.oracle.validationQuery}")
private String oracleValidationQuery;@Bean(name="dataSourceOracle")
public DataSource dataSourceOracle(){
System.out.println("----------------次配" + oracleUrl);DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(oracleUrl);
datasource.setUsername(oracleUsername);
datasource.setPassword(oraclePassword);
datasource.setDriverClassName(oracleDriverClassName);
datasource.setValidationQuery(oracleValidationQuery);
setDruidOptions(datasource); // 设置druid数据源的属性return datasource;
}// -----------------------------------------druid config-------------------------------------
@Value("${datasource.druid.initialSize}")
private int initialSize;@Value("${datasource.druid.minIdle}")
private int minIdle;@Value("${datasource.druid.maxActive}")
private int maxActive;@Value("${datasource.druid.maxWait}")
private int maxWait;@Value("${datasource.druid.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;@Value("${datasource.druid.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;@Value("${datasource.druid.testWhileIdle}")
private boolean testWhileIdle;@Value("${datasource.druid.testOnBorrow}")
private boolean testOnBorrow;@Value("${datasource.druid.testOnReturn}")
private boolean testOnReturn;@Value("${datasource.druid.poolPreparedStatements}")
private boolean poolPreparedStatements;@Value("${datasource.druid.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;@Value("${datasource.druid.filters}")
private String filters;@Value("{datasource.druid.connectionProperties}")
private String connectionProperties;private void setDruidOptions(DruidDataSource datasource){
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
logger.error("druid configuration initialization filter Exception", e);
}
datasource.setConnectionProperties(connectionProperties);
}@Bean(name = "dynamicDataSource")
@Primary // 优先使用，多数据源
public DataSource dataSource(){DynamicDataSource dynamicDataSource = new DynamicDataSource();
DataSource mysql = dataSourceMysql();
DataSource oracle = dataSourceOracle();//设置默认数据源
dynamicDataSource.setDefaultTargetDataSource(mysql);//配置多个数据源
Map<Object,Object> map = new HashMap<>();
map.put(DataSourceType.Mysql.getName(),mysql);
map.put(DataSourceType.Oracle.getName(),oracle);
dynamicDataSource.setTargetDataSources(map);return dynamicDataSource;
}@Bean // 事务管理
public PlatformTransactionManager txManager() {
return new DataSourceTransactionManager(dataSource());
}@Bean(name="druidServlet")
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean reg = new ServletRegistrationBean();
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid/*");
reg.addInitParameter("allow", ""); // 白名单
return reg;
}@Bean(name = "filterRegistrationBean")
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/");
filterRegistrationBean.addInitParameter("exclusions", ".js,.gif,.jpg,.png,.css,.ico,/druid/");
filterRegistrationBean.addInitParameter("profileEnable", "true");
filterRegistrationBean.addInitParameter("principalCookieName","USER_COOKIE");
filterRegistrationBean.addInitParameter("principalSessionName","USER_SESSION");
filterRegistrationBean.addInitParameter("DruidWebStatFilter","/*");
return filterRegistrationBean;
}
} - 自定义注解，作为AOP切面范围的一个条件（这样更加灵活）
@Documented @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.METHOD) public @interface TargetDataSource {DataSourceType value() default DataSourceType.Mysql;
} - 创建AOP切换，动态切换数据源
@Aspect @Order(2) @Component public class DataSourceAspect {private Logger logger = LoggerFactory.getLogger(this.getClass());
// 切入点：service类的方法上(这个包的子包及所有包的里面的以Service结尾的类的任意方法名任意参数的方法，都讲被切到)
@Pointcut("execution(* com.dcm.more_ds..Service..(..))")
public void dataSourcePointCut(){
System.out.println("dataSourcePointCut service");
}@Before("dataSourcePointCut()")
private void before(JoinPoint joinPoint){
Object target = joinPoint.getTarget();
String method = joinPoint.getSignature().getName();
Class<?> classz = target.getClass();
Class<?>[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getMethod().getParameterTypes();
try {
Method m = classz.getMethod(method,parameterTypes);// 如果 m 上存在切换数据源的注解，则根据注解内容进行数据源切换
if (m != null && m.isAnnotationPresent(TargetDataSource.class)){
TargetDataSource data = m.getAnnotation(TargetDataSource.class);
JdbcContextHolder.putDataSource(data.value().getName());
logger.info("》》》》》》》 current thread " + Thread.currentThread().getName() + " add 【 " + data.value().getName() + " 】 to ThreadLocal");
} else { // 如果不存在，则使用默认数据源
logger.info("》》》》》》》 use default datasource");
}
}catch (Exception e){
e.printStackTrace();
}
}// 执行完切面后，将线程共享中的数据源名称清空
@After("dataSourcePointCut()")
public void after(JoinPoint joinPoint){
JdbcContextHolder.removeDataSource();
}}
- 在需要切换数据源的dao interface或者service interface上(具体看你切面切的范围)，加上自定义的注解（这里随便列举两个）
@Transactional(readOnly = true) @TargetDataSource(DataSourceType.Oracle) public List<T> list(Integer startNum, Integer limit) { setDao(); return baseDao.selectWithPage(Arrays.asList(startNum, limit)); }@TargetDataSource(DataSourceType.Oracle)
public List<T> selectByRequestBodyIds(@RequestBody List<P> ids) {
return selectByPrimaryKeys(ids);
}
// .......more - 配置mybatis
@Configuration // 扫描指定包下的dao，这样就不用每个dao interface上面写@Mapper了 @MapperScan(basePackages = "com.dcm.more_ds.dao.*.**") public class MyBatisConf {@Autowired
@Qualifier("dynamicDataSource")
private DataSource dataSource;@Bean
public SqlSessionFactory sqlSessionFactory() {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);// 分页插件
PageHelper pageHelper = new PageHelper();
Properties properties = new Properties();
properties.setProperty("reasonable", "true");
properties.setProperty("supportMethodsArguments", "true");
properties.setProperty("returnPageInfo", "check");
properties.setProperty("params", "count=countSql");
properties.setProperty("autoRuntimeDialect","true"); // 运行时根据数据源自动选择方言 （这句很重要）
pageHelper.setProperties(properties);// 添加插件
bean.setPlugins(new Interceptor[] { pageHelper });// 添加XML目录
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
try {
bean.setMapperLocations(resolver.getResources("classpath:com/dcm/more_ds/dao//.xml"));
bean.setConfigLocation(resolver.getResource("classpath:mybatis-conf.xml"));
return bean.getObject();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}@Bean(name = "sqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate() {
return new SqlSessionTemplate(sqlSessionFactory());
}}
源码地址
springboot2 + mybatis + mysql + oracle多数据源的配置
- 源码中用到了AOP的其它应用：统一入参校验，统一异常处理。接口是伪Restful的。
- 源码中的mapper.xml，dao，service，serviceImpl，controller都是代码生成器生成的。(本贴破10w阅读量就分享代码生成器)
文末寄语：
- 转载请注明出处
- 感谢
浙公网安备 33010602011771号