×
首页数据库正文
IT序号网

springboot2多数据源完整示例

qq123 2021年06月14日 数据库 313 0

springboot2 + mybatis + mysql + oracle + sqlserver多数据源的配置

  • 相信很多朋友在开发的时候，可能会碰到需要一个项目，配置多个数据源的需求，可能是同一种数据库，也可能是不同种类的数据库。
  • 这种情况，我们就需要配置多数据源对程序的支持了。
  • （本例理论上支持很多种数据库，或者同种数据库配置多个库分别作数据源也许。拓展新强）

环境介绍

  • web框架：SpringBoot2
  • orm框架：Mybatis
  • 数据库连接池：Druid
  • 主数据源：Mysql
  • 从数据源1：Oracle
  • 从数据源2：SqlServer
  • 运行平台：Jdk8
  • 接口文档：Swagger-ui （提供伪Restful接口）
  • 日志配置：Logback

基本思路

  • 自定义多个数据源，并指定切换规则
  • 引入ThreadLocal来保存和管理数据源上下文标识
  • 使用AOP切面编程，根据某些自定义条件，动态切换数据源(反射)
  • 访问接口测试效果

大致步骤

  1. 创建一个拥有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&lt;String&gt; local = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> ThreadLocal&lt;&gt;(); 
 
<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>(); 
} 

     

    }
  2. 创建一个枚举类，用来存放每个数据源的标识符（标识符是自定义的）
    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; 
} 

     

    }
  3. 在启动类上，禁用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>); 
} 

     

    }
  4. 创建动态数据源类，接管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(); 
    } 
}
  5. 在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
  6. 根据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&lt;Object,Object&gt; map = <span class="hljs-keyword"><span class="hljs-keyword">new</span></span> HashMap&lt;&gt;(); 
    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; 
} 

     

    }
  7. 自定义注解，作为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>; 

     

    }
  8. 创建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&lt;?&gt; classz = target.getClass(); 
    Class&lt;?&gt;[] 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> &amp;&amp; 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(); 
} 

     

    }
  9. 在需要切换数据源的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&lt;T&gt; </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&lt;P&gt; 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>
  10. 配置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阅读量就分享代码生成器)
文末寄语：
  • 转载请注明出处
  • 感谢
原文地址：https://blog.csdn.net/xu_san_duo/article/details/83860675

评论关闭
IT序号网
IT序号网 Spring Boot

微信公众号号：IT虾米 （左侧二维码扫一扫）欢迎添加！