SpringBoot+Druid+Mybatis配置多数据源

github源码demo

1.application.yml配置，区分个数据源的相关配置

server:
  port: 8080
spring:
  application:
    name: service-multi-mysql
  #mysql
  datasource:
    mysql:
      primary:
        driverClassName: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: ictsoft
        password: ictsoft
        url: jdbc:mysql://172.16.0.171:3306/test1?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
        #druid配置####
        initialSize: 5
        minIdle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙，此处是filter修改的地方
        filters:
          commons-log.connection-logger-name: stat,wall,log4j
        maxPoolPreparedStatementPerConnectionSize: 20
        useGlobalDataSourceStat: true
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=50
      secondary:
        driverClassName: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
        username: ictsoft
        password: ictsoft
        url: jdbc:mysql://172.16.0.171:3306/test2?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
        initialSize: 5
        minIdle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        filters:
          commons-log.connection-logger-name: stat,wall,log4j
        maxPoolPreparedStatementPerConnectionSize: 20
        useGlobalDataSourceStat: true
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=50

2.druid监控配置，由Alibaba提供的监控配置，对SQL连接、慢查询等可以实现监控

/**
 * druid 监控配置类
 */
@Configuration
public class DruidMonitorConfig {
    /**
     * druid 数据源状态监控
     * @return
     */
    @Bean
    public ServletRegistrationBean statViewServlet(){
        //创建servlet注册实体
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
        //设置ip白名单
//        servletRegistrationBean.addInitParameter("allow","127.0.0.1");
        //设置ip黑名单，如果allow与deny共同存在时,deny优先于allow
//        servletRegistrationBean.addInitParameter("deny","192.168.0.19");
        //设置控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername","admin");
        servletRegistrationBean.addInitParameter("loginPassword","123456");
        //是否可以重置数据
        servletRegistrationBean.addInitParameter("resetEnable","false");
        return servletRegistrationBean;
    }

    /**
     * druid 过滤器
     * @return
     */
    @Bean
    public FilterRegistrationBean statFilter(){
        //创建过滤器
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        //设置过滤器过滤路径
        filterRegistrationBean.addUrlPatterns("/*");
        //忽略过滤的形式
        filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

3.分别配置数据，对应的mapper接口和xml文件互相区分

primary数据源配置，需要添加注解@Primary，设置为初始默认数据源

@Configuration
//前缀为spring.datasource.mysql的配置信息
@MapperScan(basePackages = "com.cat.code.mappers.primary", sqlSessionFactoryRef = "primaryMysqlSessionFactory")
public class PrimaryMysqlDataSourceConfig {
    
    // 主数据源使用@Primary注解进行标识
    @Primary
    @Bean(name = "primaryMysqlDataSource", destroyMethod = "close",initMethod = "init")
    @ConfigurationProperties(prefix = "spring.datasource.mysql.primary") // 将所有前缀为spring.datasource.mysql下的配置项都加载到DataSource中
    public DataSource primaryMysqlDataSource() {
        return new DruidDataSource();
    }

    // 创建该数据源的事务管理
    @Primary
    @Bean(name = "primaryMysqlTransactionManager")
    public DataSourceTransactionManager primaryMysqlTransactionManager() {
        return new DataSourceTransactionManager(primaryMysqlDataSource());
    }

    // 创建Mybatis的连接会话工厂实例
    @Primary
    @Bean(name = "primaryMysqlSessionFactory")
    public SqlSessionFactory primaryMysqlSessionFactory(@Qualifier("primaryMysqlDataSource") DataSource mysqlDataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(mysqlDataSource);  // 设置数据源bean
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mappers/primary/*.xml"));  // 设置mapper文件路径

        return sessionFactory.getObject();
    }
    
}

secondary第二数据源配置

@Configuration
//前缀为spring.datasource.mysql的配置信息
@MapperScan(basePackages = "com.cat.code.mappers.secondary", sqlSessionFactoryRef = "secondaryMysqlSessionFactory")
public class SecondaryMysqlDataSourceConfig {

    @Bean(name = "secondaryMysqlDataSource", destroyMethod = "close",initMethod = "init")
    @ConfigurationProperties(prefix = "spring.datasource.mysql.secondary") // 将所有前缀为spring.datasource.mysql下的配置项都加载到DataSource中
    public DataSource secondaryMysqlDataSource() {
        return new DruidDataSource();
    }

    // 创建该数据源的事务管理
    @Bean(name = "secondaryMysqlTransactionManager")
    public DataSourceTransactionManager secondaryMysqlTransactionManager() {
        return new DataSourceTransactionManager(secondaryMysqlDataSource());
    }

    // 创建Mybatis的连接会话工厂实例

    @Bean(name = "secondaryMysqlSessionFactory")
    public SqlSessionFactory secondaryMysqlSessionFactory(@Qualifier("secondaryMysqlDataSource") DataSource mysqlDataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(mysqlDataSource);  // 设置数据源bean
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mappers/secondary/*.xml"));  // 设置mapper文件路径

        return sessionFactory.getObject();
    }
    
}

4.调用相关mapper接口就可以实现调用

@Autowired
    private PrimaryMapper primaryMapper;

    @Autowired
    private SecondaryMapper secondaryMapper;

    public Integer primaryService(){
        Integer num = primaryMapper.countInfoTest1();
        logger.debug("primary test1 count:{}",num);
        return num;
    }


    public Integer secondaryService(){
        Integer num = secondaryMapper.countInfoTest2();
        logger.debug("secondary test2 count:{}",num);
        return num;
    }
}