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檔案