springboot 配置多druid数据源

由于项目需要进行数据表水平拆分，需要将原来一个表的数据拆分成两个库，根据自增ID进行取模，为0的路由在DB0，为1的路由在DB1，所有就需要配置三个数据源。

项目环境：SpringBoot2x，jdk8

pom.xml 引入需要的包

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.3</version>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>
<!-- 阿里系的Druid依赖包 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.9</version>
</dependency>

一，application.properties配置文件

#mysql 配置
spring.datasource.olddb.url=jdbc:mysql://192.168.2.200:3306/sunshine_aunt_test?characterEncoding=utf-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.datasource.olddb.username=root
spring.datasource.olddb.password=123456
#db0
spring.datasource.db0.url=jdbc:mysql://192.168.2.185:3306/springboot_bigdata?characterEncoding=utf-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.datasource.db0.username=root
spring.datasource.db0.password=123456
#db1
spring.datasource.db1.url=jdbc:mysql://192.168.2.185:3307/springboot_bigdata?characterEncoding=utf-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.datasource.db1.username=root
spring.datasource.db1.password=123456

#阿里druid连接池驱动配置信息
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#连接池的配置信息
#初始化大小，最小，最大
spring.datasource.initialSize=2
spring.datasource.minIdle=2
spring.datasource.maxActive=3
#配置获取连接等待超时的时间
spring.datasource.maxWait=6000
#配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
#配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM DUAL
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
#打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
#配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j
#通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

#新增数据后自动获取数据库自增主键, 需要使用 keyProperty 指定主键
mybatis.configuration.use-generated-keys=true
#开启自动驼峰命名转换
mybatis.configuration.map-underscore-to-camel-case=true

里面配置了三个数据源，olddb，db0 和db1，将olddb库的数据查询出来根据自增id进行取模路由到db0库和db1库

二，druid 的config配置文件，三份，一个库一份

1，OldDBDruidDBConfig

@Configuration
@MapperScan(sqlSessionTemplateRef = OldDBDruidDBConfig.SqlSessionTemplate, basePackages = OldDBDruidDBConfig.BASE_PACKAGES)
@Slf4j
public class OldDBDruidDBConfig {

    private static final String DB_PREFIX = "spring.datasource";

    public static final String BASE_PACKAGES = "springboot.bigdata.demo.mappers.old_db";

    private static final String MAPPER_LOCATIONS = "classpath*:mappers/old_db/*.xml";

    public static final String SqlSessionTemplate = "oldDBSqlSessionTemplate";

    private static final String SqlSessionFactory = "oldDBSqlSessionFactory";

    private static final String PlatformTransactionManager = "oldDBPlatformTransactionManager";

    private static final String DataSource = "oldDBDataSource";
	
    @Bean
    public ServletRegistrationBean druidServlet() {
        log.info("init olddb Druid Servlet Configuration ");
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        // IP白名单
        servletRegistrationBean.addInitParameter("allow", "");
        // IP黑名单(共同存在时，deny优先于allow)
        servletRegistrationBean.addInitParameter("deny", "");
        //控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername", "");
        servletRegistrationBean.addInitParameter("loginPassword", "");
        //是否能够重置数据 禁用HTML页面上的“Reset All”功能
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

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

    @Bean(name = SqlSessionFactory)
    @Autowired
    public SqlSessionFactory sqlSessionFactory(@Qualifier(DataSource) DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATIONS));
        return bean.getObject();
    }

    @Bean(name = PlatformTransactionManager)
    @Autowired
    public PlatformTransactionManager platformTransactionManager(@Qualifier(DataSource) DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }

    @Value("${spring.datasource.olddb.url}")
    private String spring_datasource_url;
    @Value("${spring.datasource.olddb.username}")
    private String spring_datasource_username;
    @Value("${spring.datasource.olddb.password}")
    private String spring_datasource_password;

    // 解决 spring.datasource.filters=stat,wall,log4j 无法正常注册进去
    @ConfigurationProperties(prefix = DB_PREFIX)
    class IDataSourceProperties {
        private String url = spring_datasource_url;
        private String username = spring_datasource_username;
        private String password = spring_datasource_password;
        private String driverClassName;
        private int initialSize;
        private int minIdle;
        private int maxActive;
        private int maxWait;
        private int timeBetweenEvictionRunsMillis;
        private int minEvictableIdleTimeMillis;
        private String validationQuery;
        private boolean testWhileIdle;
        private boolean testOnBorrow;
        private boolean testOnReturn;
        private boolean poolPreparedStatements;
        private int maxPoolPreparedStatementPerConnectionSize;
        private String filters;
        private String connectionProperties;

        @Bean(name = DataSource)     //声明其为Bean实例
//        @Primary  //在同样的DataSource中，首先使用被标注的DataSource(分库后多数据源不需要配置)
        public DataSource dataSource() {
            DruidDataSource datasource = new DruidDataSource();
            datasource.setUrl(url);
            datasource.setUsername(username);
            datasource.setPassword(password);
            datasource.setDriverClassName(driverClassName);

            //configuration
            datasource.setInitialSize(initialSize);
            datasource.setMinIdle(minIdle);
            datasource.setMaxActive(maxActive);
            datasource.setMaxWait(maxWait);
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setValidationQuery(validationQuery);
            datasource.setTestWhileIdle(testWhileIdle);
            datasource.setTestOnBorrow(testOnBorrow);
            datasource.setTestOnReturn(testOnReturn);
            datasource.setPoolPreparedStatements(poolPreparedStatements);
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            try {
                datasource.setFilters(filters);
            } catch (SQLException e) {
                log.error("druid configuration initialization filter: " + e);
            }
            datasource.setConnectionProperties(connectionProperties);
            return datasource;
        }

2，DB0DruidDBConfig

@Configuration
@MapperScan(sqlSessionTemplateRef = DB0DruidDBConfig.SqlSessionTemplate, basePackages = DB0DruidDBConfig.BASE_PACKAGES)
@Slf4j
public class DB0DruidDBConfig {

    private static final String DB_PREFIX = "spring.datasource";

    public static final String BASE_PACKAGES = "springboot.bigdata.demo.mappers.db0";

    private static final String MAPPER_LOCATIONS = "classpath*:mappers/db0/*.xml";

    public static final String SqlSessionTemplate = "db0SqlSessionTemplate";

    private static final String SqlSessionFactory = "db0SqlSessionFactory";

    private static final String PlatformTransactionManager = "db0PlatformTransactionManager";

    private static final String DataSource = "db0DataSource";

    @Bean
    public ServletRegistrationBean druidServlet() {
        log.info("init db0 Druid Servlet Configuration ");
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        // IP白名单
        servletRegistrationBean.addInitParameter("allow", "");
        // IP黑名单(共同存在时，deny优先于allow)
        servletRegistrationBean.addInitParameter("deny", "");
        //控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername", "");
        servletRegistrationBean.addInitParameter("loginPassword", "");
        //是否能够重置数据 禁用HTML页面上的“Reset All”功能
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

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

    @Bean(name = SqlSessionFactory)
    @Autowired
    public SqlSessionFactory sqlSessionFactory(@Qualifier(DataSource) DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATIONS));
        return bean.getObject();
    }

    @Bean(name = PlatformTransactionManager)
    @Autowired
    public PlatformTransactionManager platformTransactionManager(@Qualifier(DataSource) DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }

    @Value("${spring.datasource.db0.url}")
    private String spring_datasource_url;
    @Value("${spring.datasource.db0.username}")
    private String spring_datasource_username;
    @Value("${spring.datasource.db0.password}")
    private String spring_datasource_password;

    // 解决 spring.datasource.filters=stat,wall,log4j 无法正常注册进去
    @ConfigurationProperties(prefix = DB_PREFIX)
    class IDataSourceProperties {
        private String url = spring_datasource_url;
        private String username = spring_datasource_username;
        private String password = spring_datasource_password;
        private String driverClassName;
        private int initialSize;
        private int minIdle;
        private int maxActive;
        private int maxWait;
        private int timeBetweenEvictionRunsMillis;
        private int minEvictableIdleTimeMillis;
        private String validationQuery;
        private boolean testWhileIdle;
        private boolean testOnBorrow;
        private boolean testOnReturn;
        private boolean poolPreparedStatements;
        private int maxPoolPreparedStatementPerConnectionSize;
        private String filters;
        private String connectionProperties;

        @Bean(name = DataSource)     //声明其为Bean实例
//        @Primary  //在同样的DataSource中，首先使用被标注的DataSource(分库后多数据源不需要配置)
        public DataSource dataSource() {
            DruidDataSource datasource = new DruidDataSource();
            datasource.setUrl(url);
            datasource.setUsername(username);
            datasource.setPassword(password);
            datasource.setDriverClassName(driverClassName);

            //configuration
            datasource.setInitialSize(initialSize);
            datasource.setMinIdle(minIdle);
            datasource.setMaxActive(maxActive);
            datasource.setMaxWait(maxWait);
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setValidationQuery(validationQuery);
            datasource.setTestWhileIdle(testWhileIdle);
            datasource.setTestOnBorrow(testOnBorrow);
            datasource.setTestOnReturn(testOnReturn);
            datasource.setPoolPreparedStatements(poolPreparedStatements);
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            try {
                datasource.setFilters(filters);
            } catch (SQLException e) {
                log.error("druid configuration initialization filter: " + e);
            }
            datasource.setConnectionProperties(connectionProperties);
            return datasource;
        }

3，DB1DruidDBConfig

@Configuration
@MapperScan(sqlSessionTemplateRef = DB1DruidDBConfig.SqlSessionTemplate, basePackages = DB1DruidDBConfig.BASE_PACKAGES)
@Slf4j
public class DB1DruidDBConfig {

    private static final String DB_PREFIX = "spring.datasource";

    public static final String BASE_PACKAGES = "springboot.bigdata.demo.mappers.db1";

    private static final String MAPPER_LOCATIONS = "classpath*:mappers/db1/*.xml";

    public static final String SqlSessionTemplate = "db1SqlSessionTemplate";

    private static final String SqlSessionFactory = "db1SqlSessionFactory";

    private static final String PlatformTransactionManager = "db1PlatformTransactionManager";

    private static final String DataSource = "db1DataSource";

    @Bean
    public ServletRegistrationBean druidServlet() {
        log.info("init db1 Druid Servlet Configuration ");
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        // IP白名单
        servletRegistrationBean.addInitParameter("allow", "");
        // IP黑名单(共同存在时，deny优先于allow)
        servletRegistrationBean.addInitParameter("deny", "");
        //控制台管理用户
        servletRegistrationBean.addInitParameter("loginUsername", "");
        servletRegistrationBean.addInitParameter("loginPassword", "");
        //是否能够重置数据 禁用HTML页面上的“Reset All”功能
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

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

    @Bean(name = SqlSessionFactory)
    @Autowired
    public SqlSessionFactory sqlSessionFactory(@Qualifier(DataSource) DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATIONS));
        return bean.getObject();
    }

    @Bean(name = PlatformTransactionManager)
    @Autowired
    public PlatformTransactionManager platformTransactionManager(@Qualifier(DataSource) DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }

    @Value("${spring.datasource.db1.url}")
    private String spring_datasource_url;
    @Value("${spring.datasource.db1.username}")
    private String spring_datasource_username;
    @Value("${spring.datasource.db1.password}")
    private String spring_datasource_password;

    // 解决 spring.datasource.filters=stat,wall,log4j 无法正常注册进去
    @ConfigurationProperties(prefix = DB_PREFIX)
    class IDataSourceProperties {
        private String url = spring_datasource_url;
        private String username = spring_datasource_username;
        private String password = spring_datasource_password;
        private String driverClassName;
        private int initialSize;
        private int minIdle;
        private int maxActive;
        private int maxWait;
        private int timeBetweenEvictionRunsMillis;
        private int minEvictableIdleTimeMillis;
        private String validationQuery;
        private boolean testWhileIdle;
        private boolean testOnBorrow;
        private boolean testOnReturn;
        private boolean poolPreparedStatements;
        private int maxPoolPreparedStatementPerConnectionSize;
        private String filters;
        private String connectionProperties;

        @Bean(name = DataSource)     //声明其为Bean实例
//        @Primary  //在同样的DataSource中，首先使用被标注的DataSource(分库后多数据源不需要配置)
        public DataSource dataSource() {
            DruidDataSource datasource = new DruidDataSource();
            datasource.setUrl(url);
            datasource.setUsername(username);
            datasource.setPassword(password);
            datasource.setDriverClassName(driverClassName);

            //configuration
            datasource.setInitialSize(initialSize);
            datasource.setMinIdle(minIdle);
            datasource.setMaxActive(maxActive);
            datasource.setMaxWait(maxWait);
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setValidationQuery(validationQuery);
            datasource.setTestWhileIdle(testWhileIdle);
            datasource.setTestOnBorrow(testOnBorrow);
            datasource.setTestOnReturn(testOnReturn);
            datasource.setPoolPreparedStatements(poolPreparedStatements);
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
            try {
                datasource.setFilters(filters);
            } catch (SQLException e) {
                log.error("druid configuration initialization filter: " + e);
            }
            datasource.setConnectionProperties(connectionProperties);
            return datasource;
        }

以上每个类里面的代码基本一样，不同之处在于不同的数据源扫描不同的mapper包与xml文件路径，从而通过调用不同包下的mapper时使用不同的链接源，确保在多个数据源下，通过不同的包来自动选择数据源，需要操作哪个库下面的数据就用哪个包下面的mapper（都是主库，不是主从分离）

三，项目基本结构

springboot 配置多druid数据源

db0和db1里面的代码表结构一模一样，代码有些冗余，自己修改优化一下就好了

springboot 配置多druid数据源

一，application.properties配置文件

二，druid 的config配置文件，三份，一个库一份

三，项目基本结构

继续阅读

mysql优化（sql优化）

数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案

微服务-性能压测\缓存redis和分布式锁redisson和SpringCache

redis集群数据一致性_RedisRaft为Redis集群带来强大的数据一致性

宝塔面板mysql恢复2018.1.8更新

java 8 Lamda 求 List 中 Bigdecimal类型的各种值求最大值求最小值求和求平均值

Centos7 MySQL 5.7 安装MySQL 5.7 安装

查找入职员工时间排名倒数第三的员工所有信息

Hibernate使用Hibernate的“3个准备，7个步骤”Hibernate API简介操作实体对象对象识别

云计算面试题——mysql/存储引擎/备份

SQL语言基础：常用的数据查询语句

(SpringBoot)日志种类：log、monitor、access、out、gc、backup

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

MySQL的4种隔离级别？出现问题

neo4j之cypher使用文档

mysql使用source命令导入.sql文件