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 {<span class="hljs-comment"><span class="hljs-comment">/** 本地线程共享对象（保证在同一线程下切换后不要被其他线程修改） */</span></span> <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> final <span class="hljs-keyword"><span class="hljs-keyword">static</span></span> ThreadLocal<String> local = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> ThreadLocal<>(); <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> </span><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">static</span></span></span><span class="hljs-function"> </span><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">void</span></span></span><span class="hljs-function"> </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">putDataSource</span></span></span><span class="hljs-function">(</span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">String name</span></span></span><span class="hljs-function">)</span></span>{ local.<span class="hljs-keyword"><span class="hljs-keyword">set</span></span>(name); } <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> </span><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">static</span></span></span><span class="hljs-function"> String </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">getDataSource</span></span></span><span class="hljs-function">(</span><span class="hljs-params"></span><span class="hljs-function"><span class="hljs-params"></span>)</span></span>{ <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> local.<span class="hljs-keyword"><span class="hljs-keyword">get</span></span>(); } <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> </span><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">static</span></span></span><span class="hljs-function"> </span><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">void</span></span></span><span class="hljs-function"> </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">removeDataSource</span></span></span><span class="hljs-function">(</span><span class="hljs-params"></span><span class="hljs-function"><span class="hljs-params"></span>)</span></span>{ local.<span class="hljs-keyword"><span class="hljs-keyword">remove</span></span>(); }}
- 创建一个枚举类，用来存放每个数据源的标识符（标识符是自定义的）
public enum DataSourceType { Mysql("mysql"), Oracle("oracle");<span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String name; DataSourceType(String name) { <span class="hljs-keyword"><span class="hljs-keyword">this</span></span>.name = name; } <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> String </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">getName</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">()</span></span></span><span class="hljs-function"> </span></span>{ <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> name; } <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> </span><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">void</span></span></span><span class="hljs-function"> </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">setName</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">(String name)</span></span></span><span class="hljs-function"> </span></span>{ <span class="hljs-keyword"><span class="hljs-keyword">this</span></span>.name = name; }}
- 在启动类上，禁用springboot自动配置的数据源：(exclude = DataSourceAutoConfiguration.class)
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class) @ComponentScan("com.dcm.*.**.**") public class MoreDsApplication extends SpringBootServletInitializer {public static void main(<span class="hljs-type"><span class="hljs-type">String</span></span>[] args) { <span class="hljs-type"><span class="hljs-type">SpringApplication</span></span>.run(<span class="hljs-type"><span class="hljs-type">MoreDsApplication</span></span>.<span class="hljs-keyword"><span class="hljs-keyword">class</span></span>, args); <span class="hljs-type"><span class="hljs-type">System</span></span>.out.println(<span class="hljs-string"><span class="hljs-string">"[---------------more_ds项目: started......]"</span></span>); } <span class="hljs-comment"><span class="hljs-comment">/** 创建一个SpringApplicationBuilder交付给springboot框架来完成初始化运行配置 */</span></span> <span class="hljs-meta"><span class="hljs-meta">@Override</span></span> <span class="hljs-keyword"><span class="hljs-keyword">protected</span></span> <span class="hljs-type"><span class="hljs-type">SpringApplicationBuilder</span></span> configure(<span class="hljs-type"><span class="hljs-type">SpringApplicationBuilder</span></span> application) { <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> application.sources(<span class="hljs-type"><span class="hljs-type">MoreDsApplication</span></span>.<span class="hljs-keyword"><span class="hljs-keyword">class</span></span>); }}
- 创建动态数据源类，接管springboot的数据源配置
public class DynamicDataSource extends AbstractRoutingDataSource {<span class="hljs-comment"><span class="hljs-comment">/** 数据源路由，此方法用于产生要选取的数据源逻辑名称 */</span></span> <span class="hljs-meta"><span class="hljs-meta">@Override</span></span> <span class="hljs-keyword"><span class="hljs-keyword">protected</span></span> <span class="hljs-type"><span class="hljs-type">Object</span></span> determineCurrentLookupKey() { <span class="hljs-comment"><span class="hljs-comment">//从共享线程中获取数据源名称</span></span> <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> <span class="hljs-type"><span class="hljs-type">JdbcContextHolder</span></span>.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 {<span class="hljs-keyword"><span class="hljs-keyword">private</span></span> Logger logger = LoggerFactory.getLogger(<span class="hljs-keyword"><span class="hljs-keyword">this</span></span>.getClass()); <span class="hljs-comment"><span class="hljs-comment">// -----------------------------------------mysql config-------------------------------------</span></span> <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.mysql.url}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String dbUrl; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.mysql.username}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String username; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.mysql.password}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String password; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.mysql.driverClassName}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String driverClassName; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.mysql.validationQuery}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String validationQuery; <span class="hljs-meta"><span class="hljs-meta">@Bean</span></span>(name=<span class="hljs-string"><span class="hljs-string">"dataSourceMysql"</span></span>) <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> DataSource </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">dataSourceMysql</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">()</span></span></span></span>{ System.out.println(<span class="hljs-string"><span class="hljs-string">"----------------主配"</span></span> + dbUrl); DruidDataSource datasource = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> DruidDataSource(); datasource.setUrl(dbUrl); datasource.setUsername(username); datasource.setPassword(password); datasource.setDriverClassName(driverClassName); datasource.setValidationQuery(validationQuery); setDruidOptions(datasource); <span class="hljs-comment"><span class="hljs-comment">// 设置druid数据源的属性</span></span> <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> datasource; } <span class="hljs-comment"><span class="hljs-comment">// -----------------------------------------oracle config-------------------------------------</span></span> <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.oracle.url}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String oracleUrl; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.oracle.username}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String oracleUsername; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.oracle.password}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String oraclePassword; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.oracle.driverClassName}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String oracleDriverClassName; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.oracle.validationQuery}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String oracleValidationQuery; <span class="hljs-meta"><span class="hljs-meta">@Bean</span></span>(name=<span class="hljs-string"><span class="hljs-string">"dataSourceOracle"</span></span>) <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> DataSource </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">dataSourceOracle</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">()</span></span></span></span>{ System.out.println(<span class="hljs-string"><span class="hljs-string">"----------------次配"</span></span> + oracleUrl); DruidDataSource datasource = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> DruidDataSource(); datasource.setUrl(oracleUrl); datasource.setUsername(oracleUsername); datasource.setPassword(oraclePassword); datasource.setDriverClassName(oracleDriverClassName); datasource.setValidationQuery(oracleValidationQuery); setDruidOptions(datasource); <span class="hljs-comment"><span class="hljs-comment">// 设置druid数据源的属性</span></span> <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> datasource; } <span class="hljs-comment"><span class="hljs-comment">// -----------------------------------------druid config-------------------------------------</span></span> <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.druid.initialSize}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> <span class="hljs-keyword"><span class="hljs-keyword">int</span></span> initialSize; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.druid.minIdle}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> <span class="hljs-keyword"><span class="hljs-keyword">int</span></span> minIdle; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.druid.maxActive}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> <span class="hljs-keyword"><span class="hljs-keyword">int</span></span> maxActive; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.druid.maxWait}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> <span class="hljs-keyword"><span class="hljs-keyword">int</span></span> maxWait; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.druid.timeBetweenEvictionRunsMillis}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> <span class="hljs-keyword"><span class="hljs-keyword">int</span></span> timeBetweenEvictionRunsMillis; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.druid.minEvictableIdleTimeMillis}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> <span class="hljs-keyword"><span class="hljs-keyword">int</span></span> minEvictableIdleTimeMillis; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.druid.testWhileIdle}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> <span class="hljs-keyword"><span class="hljs-keyword">boolean</span></span> testWhileIdle; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.druid.testOnBorrow}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> <span class="hljs-keyword"><span class="hljs-keyword">boolean</span></span> testOnBorrow; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.druid.testOnReturn}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> <span class="hljs-keyword"><span class="hljs-keyword">boolean</span></span> testOnReturn; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.druid.poolPreparedStatements}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> <span class="hljs-keyword"><span class="hljs-keyword">boolean</span></span> poolPreparedStatements; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.druid.maxPoolPreparedStatementPerConnectionSize}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> <span class="hljs-keyword"><span class="hljs-keyword">int</span></span> maxPoolPreparedStatementPerConnectionSize; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"${datasource.druid.filters}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String filters; <span class="hljs-meta"><span class="hljs-meta">@Value</span></span>(<span class="hljs-string"><span class="hljs-string">"{datasource.druid.connectionProperties}"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> String connectionProperties; <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">private</span></span></span><span class="hljs-function"> </span><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">void</span></span></span><span class="hljs-function"> </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">setDruidOptions</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">(DruidDataSource datasource)</span></span></span></span>{ 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); <span class="hljs-keyword"><span class="hljs-keyword">try</span></span> { datasource.setFilters(filters); } <span class="hljs-keyword"><span class="hljs-keyword">catch</span></span> (SQLException e) { logger.error(<span class="hljs-string"><span class="hljs-string">"druid configuration initialization filter Exception"</span></span>, e); } datasource.setConnectionProperties(connectionProperties); } <span class="hljs-meta"><span class="hljs-meta">@Bean</span></span>(name = <span class="hljs-string"><span class="hljs-string">"dynamicDataSource"</span></span>) <span class="hljs-meta"><span class="hljs-meta">@Primary</span></span> <span class="hljs-comment"><span class="hljs-comment">// 优先使用，多数据源</span></span> <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> DataSource </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">dataSource</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">()</span></span></span></span>{ DynamicDataSource dynamicDataSource = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> DynamicDataSource(); DataSource mysql = dataSourceMysql(); DataSource oracle = dataSourceOracle(); <span class="hljs-comment"><span class="hljs-comment">//设置默认数据源</span></span> dynamicDataSource.setDefaultTargetDataSource(mysql); <span class="hljs-comment"><span class="hljs-comment">//配置多个数据源</span></span> Map<Object,Object> map = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> HashMap<>(); map.put(DataSourceType.Mysql.getName(),mysql); map.put(DataSourceType.Oracle.getName(),oracle); dynamicDataSource.setTargetDataSources(map); <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> dynamicDataSource; } <span class="hljs-meta"><span class="hljs-meta">@Bean</span></span> <span class="hljs-comment"><span class="hljs-comment">// 事务管理</span></span> <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> PlatformTransactionManager </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">txManager</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">()</span></span></span><span class="hljs-function"> </span></span>{ <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> DataSourceTransactionManager(dataSource()); } <span class="hljs-meta"><span class="hljs-meta">@Bean</span></span>(name=<span class="hljs-string"><span class="hljs-string">"druidServlet"</span></span>) <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> ServletRegistrationBean </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">druidServlet</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">()</span></span></span><span class="hljs-function"> </span></span>{ ServletRegistrationBean reg = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> ServletRegistrationBean(); reg.setServlet(<span class="hljs-keyword"><span class="hljs-keyword">new</span></span> StatViewServlet()); reg.addUrlMappings(<span class="hljs-string"><span class="hljs-string">"/druid/*"</span></span>); reg.addInitParameter(<span class="hljs-string"><span class="hljs-string">"allow"</span></span>, <span class="hljs-string"><span class="hljs-string">""</span></span>); <span class="hljs-comment"><span class="hljs-comment">// 白名单</span></span> <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> reg; } <span class="hljs-meta"><span class="hljs-meta">@Bean</span></span>(name = <span class="hljs-string"><span class="hljs-string">"filterRegistrationBean"</span></span>) <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> FilterRegistrationBean </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">filterRegistrationBean</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">()</span></span></span><span class="hljs-function"> </span></span>{ FilterRegistrationBean filterRegistrationBean = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> FilterRegistrationBean(); filterRegistrationBean.setFilter(<span class="hljs-keyword"><span class="hljs-keyword">new</span></span> WebStatFilter()); filterRegistrationBean.addUrlPatterns(<span class="hljs-string"><span class="hljs-string">"/*"</span></span>); filterRegistrationBean.addInitParameter(<span class="hljs-string"><span class="hljs-string">"exclusions"</span></span>, <span class="hljs-string"><span class="hljs-string">"*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"</span></span>); filterRegistrationBean.addInitParameter(<span class="hljs-string"><span class="hljs-string">"profileEnable"</span></span>, <span class="hljs-string"><span class="hljs-string">"true"</span></span>); filterRegistrationBean.addInitParameter(<span class="hljs-string"><span class="hljs-string">"principalCookieName"</span></span>,<span class="hljs-string"><span class="hljs-string">"USER_COOKIE"</span></span>); filterRegistrationBean.addInitParameter(<span class="hljs-string"><span class="hljs-string">"principalSessionName"</span></span>,<span class="hljs-string"><span class="hljs-string">"USER_SESSION"</span></span>); filterRegistrationBean.addInitParameter(<span class="hljs-string"><span class="hljs-string">"DruidWebStatFilter"</span></span>,<span class="hljs-string"><span class="hljs-string">"/*"</span></span>); <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> filterRegistrationBean; }}
- 自定义注解，作为AOP切面范围的一个条件（这样更加灵活）
@Documented @Retention(RetentionPolicy.RUNTIME) @Target(ElementType.METHOD) public @interface TargetDataSource {<span class="hljs-selector-tag"><span class="hljs-selector-tag">DataSourceType</span></span> <span class="hljs-selector-tag"><span class="hljs-selector-tag">value</span></span>() <span class="hljs-selector-tag"><span class="hljs-selector-tag">default</span></span> <span class="hljs-selector-tag"><span class="hljs-selector-tag">DataSourceType</span></span><span class="hljs-selector-class"><span class="hljs-selector-class">.Mysql</span></span>;}
- 创建AOP切换，动态切换数据源
@Aspect @Order(2) @Component public class DataSourceAspect {<span class="hljs-keyword"><span class="hljs-keyword">private</span></span> Logger logger = LoggerFactory.getLogger(<span class="hljs-keyword"><span class="hljs-keyword">this</span></span>.getClass()); <span class="hljs-comment"><span class="hljs-comment">// 切入点：service类的方法上(这个包的子包及所有包的里面的以Service结尾的类的任意方法名任意参数的方法，都讲被切到)</span></span> <span class="hljs-meta"><span class="hljs-meta">@Pointcut</span></span>(<span class="hljs-string"><span class="hljs-string">"execution(* com.dcm.more_ds..*Service..*(..))"</span></span>) <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> </span><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">void</span></span></span><span class="hljs-function"> </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">dataSourcePointCut</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">()</span></span></span></span>{ System.out.println(<span class="hljs-string"><span class="hljs-string">"dataSourcePointCut service"</span></span>); } <span class="hljs-meta"><span class="hljs-meta">@Before</span></span>(<span class="hljs-string"><span class="hljs-string">"dataSourcePointCut()"</span></span>) <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">private</span></span></span><span class="hljs-function"> </span><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">void</span></span></span><span class="hljs-function"> </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">before</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">(JoinPoint joinPoint)</span></span></span></span>{ Object target = joinPoint.getTarget(); String method = joinPoint.getSignature().getName(); Class<?> classz = target.getClass(); Class<?>[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getMethod().getParameterTypes(); <span class="hljs-keyword"><span class="hljs-keyword">try</span></span> { Method m = classz.getMethod(method,parameterTypes); <span class="hljs-comment"><span class="hljs-comment">// 如果 m 上存在切换数据源的注解，则根据注解内容进行数据源切换</span></span> <span class="hljs-keyword"><span class="hljs-keyword">if</span></span> (m != <span class="hljs-keyword"><span class="hljs-keyword">null</span></span> && m.isAnnotationPresent(TargetDataSource.class)){ TargetDataSource data = m.getAnnotation(TargetDataSource.class); JdbcContextHolder.putDataSource(data.value().getName()); logger.info(<span class="hljs-string"><span class="hljs-string">"》》》》》》》 current thread "</span></span> + Thread.currentThread().getName() + <span class="hljs-string"><span class="hljs-string">" add 【 "</span></span> + data.value().getName() + <span class="hljs-string"><span class="hljs-string">" 】 to ThreadLocal"</span></span>); } <span class="hljs-keyword"><span class="hljs-keyword">else</span></span> { <span class="hljs-comment"><span class="hljs-comment">// 如果不存在，则使用默认数据源</span></span> logger.info(<span class="hljs-string"><span class="hljs-string">"》》》》》》》 use default datasource"</span></span>); } }<span class="hljs-keyword"><span class="hljs-keyword">catch</span></span> (Exception e){ e.printStackTrace(); } } <span class="hljs-comment"><span class="hljs-comment">// 执行完切面后，将线程共享中的数据源名称清空</span></span> <span class="hljs-meta"><span class="hljs-meta">@After</span></span>(<span class="hljs-string"><span class="hljs-string">"dataSourcePointCut()"</span></span>) <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> </span><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">void</span></span></span><span class="hljs-function"> </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">after</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">(JoinPoint joinPoint)</span></span></span></span>{ 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)); }<span class="hljs-meta"><span class="hljs-meta">@TargetDataSource</span></span>(DataSourceType.Oracle) <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> List<T> </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">selectByRequestBodyIds</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">(@RequestBody List<P> ids)</span></span></span><span class="hljs-function"> </span></span>{ <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> selectByPrimaryKeys(ids); } <span class="hljs-comment"><span class="hljs-comment">// .......more</span></span> - 配置mybatis
@Configuration // 扫描指定包下的dao，这样就不用每个dao interface上面写@Mapper了 @MapperScan(basePackages = "com.dcm.more_ds.dao.*.**") public class MyBatisConf {<span class="hljs-meta"><span class="hljs-meta">@Autowired</span></span> <span class="hljs-meta"><span class="hljs-meta">@Qualifier</span></span>(<span class="hljs-string"><span class="hljs-string">"dynamicDataSource"</span></span>) <span class="hljs-keyword"><span class="hljs-keyword">private</span></span> DataSource dataSource; <span class="hljs-meta"><span class="hljs-meta">@Bean</span></span> <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> SqlSessionFactory </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">sqlSessionFactory</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">()</span></span></span><span class="hljs-function"> </span></span>{ SqlSessionFactoryBean bean = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> SqlSessionFactoryBean(); bean.setDataSource(dataSource); <span class="hljs-comment"><span class="hljs-comment">// 分页插件</span></span> PageHelper pageHelper = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> PageHelper(); Properties properties = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> Properties(); properties.setProperty(<span class="hljs-string"><span class="hljs-string">"reasonable"</span></span>, <span class="hljs-string"><span class="hljs-string">"true"</span></span>); properties.setProperty(<span class="hljs-string"><span class="hljs-string">"supportMethodsArguments"</span></span>, <span class="hljs-string"><span class="hljs-string">"true"</span></span>); properties.setProperty(<span class="hljs-string"><span class="hljs-string">"returnPageInfo"</span></span>, <span class="hljs-string"><span class="hljs-string">"check"</span></span>); properties.setProperty(<span class="hljs-string"><span class="hljs-string">"params"</span></span>, <span class="hljs-string"><span class="hljs-string">"count=countSql"</span></span>); properties.setProperty(<span class="hljs-string"><span class="hljs-string">"autoRuntimeDialect"</span></span>,<span class="hljs-string"><span class="hljs-string">"true"</span></span>); <span class="hljs-comment"><span class="hljs-comment">// 运行时根据数据源自动选择方言 （这句很重要）</span></span> pageHelper.setProperties(properties); <span class="hljs-comment"><span class="hljs-comment">// 添加插件</span></span> bean.setPlugins(<span class="hljs-keyword"><span class="hljs-keyword">new</span></span> Interceptor[] { pageHelper }); <span class="hljs-comment"><span class="hljs-comment">// 添加XML目录</span></span> ResourcePatternResolver resolver = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> PathMatchingResourcePatternResolver(); <span class="hljs-keyword"><span class="hljs-keyword">try</span></span> { bean.setMapperLocations(resolver.getResources(<span class="hljs-string"><span class="hljs-string">"classpath:com/dcm/more_ds/dao/*/*.xml"</span></span>)); bean.setConfigLocation(resolver.getResource(<span class="hljs-string"><span class="hljs-string">"classpath:mybatis-conf.xml"</span></span>)); <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> bean.getObject(); } <span class="hljs-keyword"><span class="hljs-keyword">catch</span></span> (Exception e) { e.printStackTrace(); <span class="hljs-keyword"><span class="hljs-keyword">throw</span></span> <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> RuntimeException(e); } } <span class="hljs-meta"><span class="hljs-meta">@Bean</span></span>(name = <span class="hljs-string"><span class="hljs-string">"sqlSessionTemplate"</span></span>) <span class="hljs-function"><span class="hljs-keyword"><span class="hljs-function"><span class="hljs-keyword">public</span></span></span><span class="hljs-function"> SqlSessionTemplate </span><span class="hljs-title"><span class="hljs-function"><span class="hljs-title">sqlSessionTemplate</span></span></span><span class="hljs-params"><span class="hljs-function"><span class="hljs-params">()</span></span></span><span class="hljs-function"> </span></span>{ <span class="hljs-keyword"><span class="hljs-keyword">return</span></span> <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> SqlSessionTemplate(sqlSessionFactory()); }}
源码地址
springboot2 + mybatis + mysql + oracle多数据源的配置
- 源码中用到了AOP的其它应用：统一入参校验，统一异常处理。接口是伪Restful的。
- 源码中的mapper.xml，dao，service，serviceImpl，controller都是代码生成器生成的。(本贴破10w阅读量就分享代码生成器)
文末寄语：
- 转载请注明出处
- 感谢