Springboot 配置多数据源、切换数据源

r囧r小猫 2024-04-18 17:28 31阅读 0赞

有些项目场景需要支持不同数据源切换或者多种数据源（mysql、sqlserver、oracle），本次分享就可以满足这一需求。

**1.配置第一数据源**（我这里写为主数据源、有些不太准确）

import com.alibaba.druid.pool.DruidDataSource;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import javax.sql.DataSource;
    
    
    /**
     *
     * @Description: 主数据源切换 mysql sqlServer oracle
     * @Author: xyh
     * @Date: 2019/8/15 13:33
     */
    @Configuration
    @ConfigurationProperties(prefix = "spring.datasource.master")
    @MapperScan(basePackages = "com.test.mapper.master", sqlSessionFactoryRef = "masterSqlSessionFactory")
    public class MasterDataSourceConfig {
    
        @Value("${spring.datasource.master.url}")
        private String url;
    
        @Value("${spring.datasource.master.username}")
        private String username;
    
        @Value("${spring.datasource.master.password}")
        private String password;
    
        @Value("${spring.datasource.master.driverClassName}")
        private String driverClass;
    
        @Value("${spring.datasource.master.type}")
        private String type;
    
        /**
         * mybatis xml路径
         */
        private String mybatisPattern;
    
        @Primary
        @Bean(name = "masterDataSource")
        public DataSource dataSource() {
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setDriverClassName(driverClass);
            dataSource.setUrl(url);
            dataSource.setUsername(username);
            dataSource.setPassword(password);
            dataSource.setDbType(type);
            mybatisPattern = "classpath:mybatis/master/*.xml";
            if (url.contains("sqlserver")) {
                dataSource.setValidationQuery("select 1");
            } else if (url.contains("oracle")) {
                dataSource.setValidationQuery("select 1 from dual");
            }
            return dataSource;
    
        }
    
        @Primary
        @Bean(name = "masterSqlSessionFactory")
        public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
            factoryBean.setDataSource(dataSource);
            factoryBean.setTypeAliasesPackage("com.ewell.portal.domain");
            factoryBean.setMapperLocations(
                    new PathMatchingResourcePatternResolver().getResources(mybatisPattern));
            return factoryBean.getObject();
        }
    
        @Primary
        @Bean(name = "masterTransactionManager")
        public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "masterSqlSessionTemplate")
        @Primary
        public SqlSessionTemplate sqlSessionTemplate(
                @Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    
    }

**2.配置文件配置**

#mysql数据源
    spring.datasource.master.url=jdbc:mysql://192.168.10.74:3306/test?useUnicode=true&characterEncoding=UTF8
    spring.datasource.master.username=test
    spring.datasource.master.password=test
    spring.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
    spring.datasource.master.driverClassName=com.mysql.cj.jdbc.Driver
    
    
    #sqlserver数据源
    #spring.datasource.master.url=jdbc:jtds:sqlserver://192.168.10.76:1433;DatabaseName=test
    #spring.datasource.master.username=test
    #spring.datasource.master.password=test
    #spring.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
    #spring.datasource.master.driverClassName=net.sourceforge.jtds.jdbc.Driver
    
    
    #oracle数据源
    #spring.datasource.master.url=jdbc:oracle:thin:@192.168.150.160:1521:test
    #spring.datasource.master.username=test
    #spring.datasource.master.password=test
    #spring.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
    #spring.datasource.master.driverClassName=oracle.jdbc.driver.OracleDriver
    
    
    
    #其他数据源
    #spring.datasource.slave.url=jdbc:jtds:sqlserver://192.168.10.76:1433;DatabaseName=test
    #spring.datasource.slave.username=test
    #spring.datasource.slave.password=test
    #spring.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource
    #spring.datasource.slave.driverClassName=net.sourceforge.jtds.jdbc.Driver

**3.配置第二数据源**（或者更多）

import com.alibaba.druid.pool.DruidDataSource;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    
    import javax.sql.DataSource;
    
    
    /**
     *
     * @Description: 其他数据源配置 mysql sqlServer oracle
     * @Author: xyh
     * @Date: 2019/8/15 13:33
     */
    @Configuration
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    @MapperScan(basePackages = "com.test.mapper.slave", sqlSessionFactoryRef = "slaveSqlSessionFactory")
    public class SlaveDataSourceConfig {
    
        @Value("${spring.datasource.slave.url}")
        private String url;
    
        @Value("${spring.datasource.slave.username}")
        private String username;
    
        @Value("${spring.datasource.slave.password}")
        private String password;
    
        @Value("${spring.datasource.slave.driverClassName}")
        private String driverClass;
    
        @Value("${spring.datasource.slave.type}")
        private String type;
    
        /**
         * mybatis xml路径
         */
        private String mybatisPattern;
    
    
        @Bean(name = "slaveDataSource")
        public DataSource dataSource() {
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setDriverClassName(driverClass);
            dataSource.setUrl(url);
            dataSource.setUsername(username);
            dataSource.setPassword(password);
            dataSource.setDbType(type);
            mybatisPattern = "classpath:mybatis/slave/*.xml";
            if (url.contains("sqlserver")) {
                dataSource.setValidationQuery("select 1");
            } else if (url.contains("oracle")) {
                dataSource.setValidationQuery("select 1 from dual");
            }
            return dataSource;
    
        }
    
    
        @Bean(name = "slaveSqlSessionFactory")
        public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
            factoryBean.setDataSource(dataSource);
            factoryBean.setTypeAliasesPackage("com.ewell.portal.domain");
            factoryBean.setMapperLocations(
                    new PathMatchingResourcePatternResolver().getResources(mybatisPattern));
            return factoryBean.getObject();
        }
    
    
        @Bean(name = "slaveTransactionManager")
        public DataSourceTransactionManager transactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "slaveSqlSessionTemplate")
        public SqlSessionTemplate sqlSessionTemplate(
                @Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    
    }

**4.项目结构**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ1NjQ2Ng_size_16_color_FFFFFF_t_70][]

**5.maven引用**

<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<version>8.0.13</version>
    		</dependency>
    
             
            <dependency>
    			<groupId>com.oracle</groupId>
    			<artifactId>ojdbc6</artifactId>
    			<version>11.2.0.3</version>
    		</dependency>
    
    	    
    		<dependency>
    			<groupId>com.microsoft.sqlserver</groupId>
    			<artifactId>sqljdbc4</artifactId>
    			<version>4.0</version>
    		</dependency>

**总结：数据源配置类通过读取配置文件配置，连接数据库。配置类中配置mapper、xml扫描路径，service根据业务逻辑调用不同的数据源的mapper，以此实现该需求。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQ1NjQ2Ng_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/2423fa20d202431e85078a2179d83a56.png