侧边栏壁纸
  • 累计撰写 93 篇文章
  • 累计创建 85 个标签
  • 累计收到 9 条评论

springboot配置多数据源

bearjun
2021-03-01 / 0 评论 / 0 点赞 / 1,017 阅读 / 5,583 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2021-03-01,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

背景说明

以前的项目是配置的单数据源,现在需要对接,需要多数据源的支持。

开始开发

由于原来已经系统在用了,为了不影响原有的功能,所以选择通过扫描不同的mapper包来实现。
yaml文件:

#该设置对外部Tomcat无效,以外部Tomcat自身的端口号为准
server:
  port: 80

spring:
  datasource:
    druid:
    ## mysql数据源
      primary:
        url: jdbc:mysql://localhost:3306/ceshi?zeroDateTimeBehavior=convertToNull&autoReconnect=true&useUnicode=true&characterEncoding=utf-8
        username: root
        password: 123456
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        initial-size: 0
        min-idle: 0
        max-active: 10
        max-wait: 60000
        time-between-eviction-runsMillis: 60000
        min-evictable-idle-timeMillis: 300000
        validation-query: SELECT 1
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        pool-prepared-statements: true
        max-open-prepared-statements: -1
        max-pool-prepared-statement-per-connection-size: 20
        filters: stat
        connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
        use-global-data-source-stat: true
    ## oracle数据源
      datasource2:
        url: jdbc:oracle:thin:@localhost:1521:ORCL
        username: root
        password: 123
        driver-class-name: oracle.jdbc.driver.OracleDriver
        type: com.alibaba.druid.pool.DruidDataSource
        initial-size: 0
        min-idle: 0
        max-active: 10
        max-wait: 60000
        time-between-eviction-runsMillis: 60000
        min-evictable-idle-timeMillis: 300000
        validation-query: SELECT 1 FROM DUAL
        test-while-idle: true
        test-on-borrow: false
        test-on-return: false
        pool-prepared-statements: true
        max-open-prepared-statements: -1
        max-pool-prepared-statement-per-connection-size: 20
        filters: stat
        connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
        use-global-data-source-stat: true

# mybtis相关的配置
mybatis:
  mapper-locations: classpath:mybatis/**/*Mapper.xml
  type-aliases-package: com.bearjun.bean

MysqlDataSourceConfig(第一个数据源)配置文件:

@Configuration
@MapperScan(basePackages = {"com.bearjun.jianyitong.mapper"}, sqlSessionFactoryRef = "sqlSessionFactory")
public class MysqlDataSourceConfig {

    @Bean(name = "dataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid.primary")
    @Primary
    public DataSource dataSource() {
        //return DataSourceBuilder.create().build();
        return  new DruidDataSource();
    }

    @Bean(name = "sqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "transactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "sqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

OracleDataSourceConfig(第二个数据源)配置文件

@Configuration
@MapperScan(basePackages = {"com.bearjun.jianyitong.oracleMapper"}, sqlSessionFactoryRef = "sqlSessionFactory2")
public class OracleDataSourceConfig {

    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource.druid.datasource2")
    public DataSource dataSource() {
        //return DataSourceBuilder.create().build();
        return  new DruidDataSource();
    }

    @Bean(name = "sqlSessionFactory2")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource2") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "transactionManager2")
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource2") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "sqlSessionTemplate2")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory2") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

Druid的配置文件:可以通过ip:port/druid来访问Druid的控制台

@Configuration
public class DruidMoniterConfig {
    /**
     * 1、配置一个管理后台的Servlet
     */
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String,String> initParams = new HashMap<>();
        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","admin");
        //默认就是允许所有访问
        initParams.put("allow","");
        //黑名单的IP
        initParams.put("deny","192.168.15.21");
        bean.setInitParameters(initParams);
        return bean;
    }

    /**
     * 2、配置一个web监控的filter
     */
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        Map<String,String> initParams = new HashMap<>(1);
        initParams.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));
        return  bean;
    }
}

通过上述的代码配置测试,是没有问题的。

相关问题说明

  • 关于oracle相关的问题处理
    win10安装PLSQL和项目引入maven依赖的问题:https://bearjun.com/others/487.html
  • 923:ORA-00923: 未找到要求的 FROM 关键字
    可能原因有:1、SQL出现问题 2、 数据库的驱动写错了
  • 未找到要求的 FROM 关键字 errorCode 923, state 42000
    validation-query字段的对照表:
    DataBase|validationQuery
    :--:|:--:
    hsqldb| select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
    Oracle| select 1 from dual
    DB2 | select 1 from sysibm.sysdummy1
    MySql | select 1
    Microsoft SqlServer | select1
    postgresql | select version()
    ingres | select 1
    derby | values 1
    H2 | select 1

validationQuery是用来验证数据库连接的查询语句,这个查询语句必须是至少返回一条数据的SELECT语句。每种数据库都有各自的验证语句

0

评论区