springboot 配置多druid数据源

关注

原创最新推荐文章于 2026-05-18 12:21:31 发布 2.1k 阅读 AI 写同款· GEO 优化 ›

Spring Boot整合MyBatis与Druid实现多数据源配置在现代企业级Java应用开发中，随着业务规模的扩大和系统复杂度的提升，单一数据源已经无法满足系统的高可用性、性能以及数据隔离等需求。多数据源配置应运而生，旨在通过为不同业务模块或数据访问需求分配独立的数据源，从而实现数据库资源的合理分配与高效利用。多数据源通常适用于以下场景：读写分离：将读操作与写操作分配到不同的数据库实例，提升系统并发能力。数据隔离：不同业务模块使用不同数据库，降低耦合度，提升系统安全性。高可用与灾备：通过配置多个数据源实现故障切换，保障系统稳定性。阅读详情

由于项目需要进行数据表水平拆分，需要将原来一个表的数据拆分成两个库，根据自增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（都是主库，不是主从分离）