springboot2多数据源完整示例

转载

mob604756f828bf 2019-06-24 18:04:00

文章标签 java经验集锦通用实践 Spring SpringBoot Mysql 文章分类 代码人生

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 ><span >/** 本地线程共享对象（保证在同一线程下切换后不要被其他线程修改） */</span></span>
<span ><span >private</span></span> final <span ><span >static</span></span> ThreadLocal<String> local = <span ><span >new</span></span> ThreadLocal<>();

<span ><span ><span ><span >public</span></span></span><span > </span><span ><span ><span >static</span></span></span><span > </span><span ><span ><span >void</span></span></span><span > </span><span ><span ><span >putDataSource</span></span></span><span >(</span><span ><span ><span >String name</span></span></span><span >)</span></span>{
    local.<span ><span >set</span></span>(name);
}

<span ><span ><span ><span >public</span></span></span><span > </span><span ><span ><span >static</span></span></span><span > String </span><span ><span ><span >getDataSource</span></span></span><span >(</span><span ></span><span ><span ></span>)</span></span>{
    <span ><span >return</span></span> local.<span ><span >get</span></span>();
}

<span ><span ><span ><span >public</span></span></span><span > </span><span ><span ><span >static</span></span></span><span > </span><span ><span ><span >void</span></span></span><span > </span><span ><span ><span >removeDataSource</span></span></span><span >(</span><span ></span><span ><span ></span>)</span></span>{
    local.<span ><span >remove</span></span>();
}

}
创建一个枚举类，用来存放每个数据源的标识符（标识符是自定义的）public enum DataSourceType { Mysql("mysql"), Oracle("oracle");

<span ><span >private</span></span> String name;

DataSourceType(String name) {
    <span ><span >this</span></span>.name = name;
}

<span ><span ><span ><span >public</span></span></span><span > String </span><span ><span ><span >getName</span></span></span><span ><span ><span >()</span></span></span><span > </span></span>{
    <span ><span >return</span></span> name;
}

<span ><span ><span ><span >public</span></span></span><span > </span><span ><span ><span >void</span></span></span><span > </span><span ><span ><span >setName</span></span></span><span ><span ><span >(String name)</span></span></span><span > </span></span>{
    <span ><span >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 ><span >String</span></span>[] args) {

    <span ><span >SpringApplication</span></span>.run(<span ><span >MoreDsApplication</span></span>.<span ><span >class</span></span>, args);
    <span ><span >System</span></span>.out.println(<span ><span >"[---------------more_ds项目: started......]"</span></span>);

}

<span ><span >/** 创建一个SpringApplicationBuilder交付给springboot框架来完成初始化运行配置 */</span></span>
<span ><span >@Override</span></span>
<span ><span >protected</span></span> <span ><span >SpringApplicationBuilder</span></span> configure(<span ><span >SpringApplicationBuilder</span></span> application) {
    <span ><span >return</span></span> application.sources(<span ><span >MoreDsApplication</span></span>.<span ><span >class</span></span>);
}

}
创建动态数据源类，接管springboot的数据源配置public class DynamicDataSource extends AbstractRoutingDataSource {

<span ><span >/** 数据源路由，此方法用于产生要选取的数据源逻辑名称 */</span></span>
    <span ><span >@Override</span></span>
    <span ><span >protected</span></span> <span ><span >Object</span></span> determineCurrentLookupKey() {
        <span ><span >//从共享线程中获取数据源名称</span></span>
        <span ><span >return</span></span> <span ><span >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 ><span >private</span></span> Logger logger = LoggerFactory.getLogger(<span ><span >this</span></span>.getClass());

<span ><span >// -----------------------------------------mysql config-------------------------------------</span></span>

<span ><span >@Value</span></span>(<span ><span >"${datasource.mysql.url}"</span></span>)
<span ><span >private</span></span> String dbUrl;

<span ><span >@Value</span></span>(<span ><span >"${datasource.mysql.username}"</span></span>)
<span ><span >private</span></span> String username;

<span ><span >@Value</span></span>(<span ><span >"${datasource.mysql.password}"</span></span>)
<span ><span >private</span></span> String password;

<span ><span >@Value</span></span>(<span ><span >"${datasource.mysql.driverClassName}"</span></span>)
<span ><span >private</span></span> String driverClassName;

<span ><span >@Value</span></span>(<span ><span >"${datasource.mysql.validationQuery}"</span></span>)
<span ><span >private</span></span> String validationQuery;

<span ><span >@Bean</span></span>(name=<span ><span >"dataSourceMysql"</span></span>)
<span ><span ><span ><span >public</span></span></span><span > DataSource </span><span ><span ><span >dataSourceMysql</span></span></span><span ><span ><span >()</span></span></span></span>{
    System.out.println(<span ><span >"----------------主配"</span></span> + dbUrl);

    DruidDataSource datasource = <span ><span >new</span></span> DruidDataSource();
    datasource.setUrl(dbUrl);
    datasource.setUsername(username);
    datasource.setPassword(password);
    datasource.setDriverClassName(driverClassName);
    datasource.setValidationQuery(validationQuery);
    setDruidOptions(datasource); <span ><span >// 设置druid数据源的属性</span></span>

    <span ><span >return</span></span> datasource;
}

<span ><span >// -----------------------------------------oracle config-------------------------------------</span></span>

<span ><span >@Value</span></span>(<span ><span >"${datasource.oracle.url}"</span></span>)
<span ><span >private</span></span> String oracleUrl;

<span ><span >@Value</span></span>(<span ><span >"${datasource.oracle.username}"</span></span>)
<span ><span >private</span></span> String oracleUsername;

<span ><span >@Value</span></span>(<span ><span >"${datasource.oracle.password}"</span></span>)
<span ><span >private</span></span> String oraclePassword;

<span ><span >@Value</span></span>(<span ><span >"${datasource.oracle.driverClassName}"</span></span>)
<span ><span >private</span></span> String oracleDriverClassName;

<span ><span >@Value</span></span>(<span ><span >"${datasource.oracle.validationQuery}"</span></span>)
<span ><span >private</span></span> String oracleValidationQuery;

<span ><span >@Bean</span></span>(name=<span ><span >"dataSourceOracle"</span></span>)
<span ><span ><span ><span >public</span></span></span><span > DataSource </span><span ><span ><span >dataSourceOracle</span></span></span><span ><span ><span >()</span></span></span></span>{
    System.out.println(<span ><span >"----------------次配"</span></span> + oracleUrl);

    DruidDataSource datasource = <span ><span >new</span></span> DruidDataSource();
    datasource.setUrl(oracleUrl);
    datasource.setUsername(oracleUsername);
    datasource.setPassword(oraclePassword);
    datasource.setDriverClassName(oracleDriverClassName);
    datasource.setValidationQuery(oracleValidationQuery);
    setDruidOptions(datasource); <span ><span >// 设置druid数据源的属性</span></span>

    <span ><span >return</span></span> datasource;
}

<span ><span >// -----------------------------------------druid config-------------------------------------</span></span>

<span ><span >@Value</span></span>(<span ><span >"${datasource.druid.initialSize}"</span></span>)
<span ><span >private</span></span> <span ><span >int</span></span> initialSize;

<span ><span >@Value</span></span>(<span ><span >"${datasource.druid.minIdle}"</span></span>)
<span ><span >private</span></span> <span ><span >int</span></span> minIdle;

<span ><span >@Value</span></span>(<span ><span >"${datasource.druid.maxActive}"</span></span>)
<span ><span >private</span></span> <span ><span >int</span></span> maxActive;

<span ><span >@Value</span></span>(<span ><span >"${datasource.druid.maxWait}"</span></span>)
<span ><span >private</span></span> <span ><span >int</span></span> maxWait;

<span ><span >@Value</span></span>(<span ><span >"${datasource.druid.timeBetweenEvictionRunsMillis}"</span></span>)
<span ><span >private</span></span> <span ><span >int</span></span> timeBetweenEvictionRunsMillis;

<span ><span >@Value</span></span>(<span ><span >"${datasource.druid.minEvictableIdleTimeMillis}"</span></span>)
<span ><span >private</span></span> <span ><span >int</span></span> minEvictableIdleTimeMillis;

<span ><span >@Value</span></span>(<span ><span >"${datasource.druid.testWhileIdle}"</span></span>)
<span ><span >private</span></span> <span ><span >boolean</span></span> testWhileIdle;

<span ><span >@Value</span></span>(<span ><span >"${datasource.druid.testOnBorrow}"</span></span>)
<span ><span >private</span></span> <span ><span >boolean</span></span> testOnBorrow;

<span ><span >@Value</span></span>(<span ><span >"${datasource.druid.testOnReturn}"</span></span>)
<span ><span >private</span></span> <span ><span >boolean</span></span> testOnReturn;

<span ><span >@Value</span></span>(<span ><span >"${datasource.druid.poolPreparedStatements}"</span></span>)
<span ><span >private</span></span> <span ><span >boolean</span></span> poolPreparedStatements;

<span ><span >@Value</span></span>(<span ><span >"${datasource.druid.maxPoolPreparedStatementPerConnectionSize}"</span></span>)
<span ><span >private</span></span> <span ><span >int</span></span> maxPoolPreparedStatementPerConnectionSize;

<span ><span >@Value</span></span>(<span ><span >"${datasource.druid.filters}"</span></span>)
<span ><span >private</span></span> String filters;

<span ><span >@Value</span></span>(<span ><span >"{datasource.druid.connectionProperties}"</span></span>)
<span ><span >private</span></span> String connectionProperties;

<span ><span ><span ><span >private</span></span></span><span > </span><span ><span ><span >void</span></span></span><span > </span><span ><span ><span >setDruidOptions</span></span></span><span ><span ><span >(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 ><span >try</span></span> {
        datasource.setFilters(filters);
    } <span ><span >catch</span></span> (SQLException e) {
        logger.error(<span ><span >"druid configuration initialization filter Exception"</span></span>, e);
    }
    datasource.setConnectionProperties(connectionProperties);
}


<span ><span >@Bean</span></span>(name = <span ><span >"dynamicDataSource"</span></span>)
<span ><span >@Primary</span></span>  <span ><span >// 优先使用，多数据源</span></span>
<span ><span ><span ><span >public</span></span></span><span > DataSource </span><span ><span ><span >dataSource</span></span></span><span ><span ><span >()</span></span></span></span>{

    DynamicDataSource dynamicDataSource = <span ><span >new</span></span> DynamicDataSource();
    DataSource mysql = dataSourceMysql();
    DataSource oracle = dataSourceOracle();

    <span ><span >//设置默认数据源</span></span>
    dynamicDataSource.setDefaultTargetDataSource(mysql);

    <span ><span >//配置多个数据源</span></span>
    Map<Object,Object> map = <span ><span >new</span></span> HashMap<>();
    map.put(DataSourceType.Mysql.getName(),mysql);
    map.put(DataSourceType.Oracle.getName(),oracle);
    dynamicDataSource.setTargetDataSources(map);

    <span ><span >return</span></span> dynamicDataSource;
}

<span ><span >@Bean</span></span> <span ><span >// 事务管理</span></span>
<span ><span ><span ><span >public</span></span></span><span > PlatformTransactionManager </span><span ><span ><span >txManager</span></span></span><span ><span ><span >()</span></span></span><span > </span></span>{
    <span ><span >return</span></span> <span ><span >new</span></span> DataSourceTransactionManager(dataSource());
}


<span ><span >@Bean</span></span>(name=<span ><span >"druidServlet"</span></span>)
<span ><span ><span ><span >public</span></span></span><span > ServletRegistrationBean </span><span ><span ><span >druidServlet</span></span></span><span ><span ><span >()</span></span></span><span > </span></span>{
    ServletRegistrationBean reg = <span ><span >new</span></span> ServletRegistrationBean();
    reg.setServlet(<span ><span >new</span></span> StatViewServlet());
    reg.addUrlMappings(<span ><span >"/druid/*"</span></span>);
    reg.addInitParameter(<span ><span >"allow"</span></span>, <span ><span >""</span></span>); <span ><span >// 白名单</span></span>
    <span ><span >return</span></span> reg;
}

<span ><span >@Bean</span></span>(name = <span ><span >"filterRegistrationBean"</span></span>)
<span ><span ><span ><span >public</span></span></span><span > FilterRegistrationBean </span><span ><span ><span >filterRegistrationBean</span></span></span><span ><span ><span >()</span></span></span><span > </span></span>{
    FilterRegistrationBean filterRegistrationBean = <span ><span >new</span></span> FilterRegistrationBean();
    filterRegistrationBean.setFilter(<span ><span >new</span></span> WebStatFilter());
    filterRegistrationBean.addUrlPatterns(<span ><span >"/*"</span></span>);
    filterRegistrationBean.addInitParameter(<span ><span >"exclusions"</span></span>, <span ><span >"*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"</span></span>);
    filterRegistrationBean.addInitParameter(<span ><span >"profileEnable"</span></span>, <span ><span >"true"</span></span>);
    filterRegistrationBean.addInitParameter(<span ><span >"principalCookieName"</span></span>,<span ><span >"USER_COOKIE"</span></span>);
    filterRegistrationBean.addInitParameter(<span ><span >"principalSessionName"</span></span>,<span ><span >"USER_SESSION"</span></span>);
    filterRegistrationBean.addInitParameter(<span ><span >"DruidWebStatFilter"</span></span>,<span ><span >"/*"</span></span>);
    <span ><span >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 ><span >private</span></span> Logger logger = LoggerFactory.getLogger(<span ><span >this</span></span>.getClass());

<span ><span >// 切入点：service类的方法上(这个包的子包及所有包的里面的以Service结尾的类的任意方法名任意参数的方法，都讲被切到)</span></span>
<span ><span >@Pointcut</span></span>(<span ><span >"execution(* com.dcm.more_ds..*Service..*(..))"</span></span>)
<span ><span ><span ><span >public</span></span></span><span > </span><span ><span ><span >void</span></span></span><span > </span><span ><span ><span >dataSourcePointCut</span></span></span><span ><span ><span >()</span></span></span></span>{
    System.out.println(<span ><span >"dataSourcePointCut service"</span></span>);
}

<span ><span >@Before</span></span>(<span ><span >"dataSourcePointCut()"</span></span>)
<span ><span ><span ><span >private</span></span></span><span > </span><span ><span ><span >void</span></span></span><span > </span><span ><span ><span >before</span></span></span><span ><span ><span >(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 ><span >try</span></span> {
        Method m = classz.getMethod(method,parameterTypes);

        <span ><span >// 如果 m 上存在切换数据源的注解，则根据注解内容进行数据源切换</span></span>
        <span ><span >if</span></span> (m != <span ><span >null</span></span> && m.isAnnotationPresent(TargetDataSource.class)){
            TargetDataSource data = m.getAnnotation(TargetDataSource.class);
            JdbcContextHolder.putDataSource(data.value().getName());
            logger.info(<span ><span >"》》》》》》》 current thread "</span></span> + Thread.currentThread().getName() + <span ><span >" add 【 "</span></span> + data.value().getName() + <span ><span >" 】 to ThreadLocal"</span></span>);
        } <span ><span >else</span></span> { <span ><span >// 如果不存在，则使用默认数据源</span></span>
            logger.info(<span ><span >"》》》》》》》 use default datasource"</span></span>);
        }
    }<span ><span >catch</span></span> (Exception e){
        e.printStackTrace();
    }
}

<span ><span >// 执行完切面后，将线程共享中的数据源名称清空</span></span>
<span ><span >@After</span></span>(<span ><span >"dataSourcePointCut()"</span></span>)
<span ><span ><span ><span >public</span></span></span><span > </span><span ><span ><span >void</span></span></span><span > </span><span ><span ><span >after</span></span></span><span ><span ><span >(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 ><span >@TargetDataSource</span></span>(DataSourceType.Oracle)
<span ><span ><span ><span >public</span></span></span><span > List<T> </span><span ><span ><span >selectByRequestBodyIds</span></span></span><span ><span ><span >(@RequestBody List<P> ids)</span></span></span><span > </span></span>{
    <span ><span >return</span></span> selectByPrimaryKeys(ids);
}
<span ><span >// .......more</span></span>

配置mybatis@Configuration // 扫描指定包下的dao，这样就不用每个dao interface上面写@Mapper了 @MapperScan(basePackages = "com.dcm.more_ds.dao.*.**") public class MyBatisConf {

<span ><span >@Autowired</span></span>
<span ><span >@Qualifier</span></span>(<span ><span >"dynamicDataSource"</span></span>)
<span ><span >private</span></span> DataSource dataSource;

<span ><span >@Bean</span></span>
<span ><span ><span ><span >public</span></span></span><span > SqlSessionFactory </span><span ><span ><span >sqlSessionFactory</span></span></span><span ><span ><span >()</span></span></span><span > </span></span>{
    SqlSessionFactoryBean bean = <span ><span >new</span></span> SqlSessionFactoryBean();
    bean.setDataSource(dataSource);

    <span ><span >// 分页插件</span></span>
    PageHelper pageHelper = <span ><span >new</span></span> PageHelper();
    Properties properties = <span ><span >new</span></span> Properties();
    properties.setProperty(<span ><span >"reasonable"</span></span>, <span ><span >"true"</span></span>);
    properties.setProperty(<span ><span >"supportMethodsArguments"</span></span>, <span ><span >"true"</span></span>);
    properties.setProperty(<span ><span >"returnPageInfo"</span></span>, <span ><span >"check"</span></span>);
    properties.setProperty(<span ><span >"params"</span></span>, <span ><span >"count=countSql"</span></span>);
    properties.setProperty(<span ><span >"autoRuntimeDialect"</span></span>,<span ><span >"true"</span></span>); <span ><span >// 运行时根据数据源自动选择方言 （这句很重要）</span></span>
    pageHelper.setProperties(properties);

    <span ><span >// 添加插件</span></span>
    bean.setPlugins(<span ><span >new</span></span> Interceptor[] { pageHelper });

    <span ><span >// 添加XML目录</span></span>
    ResourcePatternResolver resolver = <span ><span >new</span></span> PathMatchingResourcePatternResolver();
    <span ><span >try</span></span> {
        bean.setMapperLocations(resolver.getResources(<span ><span >"classpath:com/dcm/more_ds/dao/*/*.xml"</span></span>));
        bean.setConfigLocation(resolver.getResource(<span ><span >"classpath:mybatis-conf.xml"</span></span>));
        <span ><span >return</span></span> bean.getObject();
    } <span ><span >catch</span></span> (Exception e) {
        e.printStackTrace();
        <span ><span >throw</span></span> <span ><span >new</span></span> RuntimeException(e);
    }
}

<span ><span >@Bean</span></span>(name = <span ><span >"sqlSessionTemplate"</span></span>)
<span ><span ><span ><span >public</span></span></span><span > SqlSessionTemplate </span><span ><span ><span >sqlSessionTemplate</span></span></span><span ><span ><span >()</span></span></span><span > </span></span>{
    <span ><span >return</span></span> <span ><span >new</span></span> SqlSessionTemplate(sqlSessionFactory());
}