springboot配置多个数据源

实际业务中；在一个项目里面读取多个数据库的数据来进行展示，例如读取mysql，pgsql，oracle的不同数据库，springboto对同时配置多个数据源是支持的。

使用springboot+mybatis的框架来进行演示，

在配置文件中配置多个数据源，然后通过配置类来获取数据源以及mapper相关的扫描配置

pom.xml文件

<parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>...</version><relativePath/></parent><dependencies><!-- druid数据源驱动 --><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.1.0</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><!--mybatis SpringBoot依赖 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-tomcat</artifactId><scope>compile</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId></dependency><!-- aop依赖 --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-aop</artifactId></dependency><!-- mybatis --><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>1.3.1</version></dependency><!-- 通用mapper --><dependency><groupId>tk.mybatis</groupId><artifactId>mapper-spring-boot-starter</artifactId><version>1.1.5</version></dependency><!-- druid监控依赖 --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.0.28</version></dependency></dependencies>

application.yml 配置文件中可以配置多个不同数据源，例如下面配置了mysql和pgsql两个不同数据源。

spring:datasource:mysql-op:   //数据源1，mysqltype: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/test2?useUnicode=true&characterEncoding=utf8username: rootpassword: 123456schema: tasa_devpgsql-npaaa: //数据源2，pgsqltype: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.pgsql.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=utf8username2: rootpassword2: 123456schema: tasa_test

如果使用type类型 com.alibaba.druid.pool.DruidDataSource，需要进行DruidDBConfig 连接池相关配置，如下所示，如果时候type是其他类型，例如spring.datasource.type=com.zaxxer.hikari.HikariDataSource，就可以不配置下面的内容。

package com.xbz.common.config;import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;/*** Druid监控*/
@SuppressWarnings("AlibabaRemoveCommentedCode")
@Configuration
public class DruidDBConfig {private Logger logger = LoggerFactory.getLogger(DruidDBConfig.class);/*** 注册ServletRegistrationBean* @return*/@Beanpublic ServletRegistrationBean druidServlet() {ServletRegistrationBean reg = new ServletRegistrationBean();reg.setServlet(new StatViewServlet());reg.addUrlMappings("/druid/*");reg.addInitParameter("allow", ""); //白名单return reg;}/*** 注册FilterRegistrationBean* @return*/@Beanpublic FilterRegistrationBean filterRegistrationBean() {FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();filterRegistrationBean.setFilter(new WebStatFilter());filterRegistrationBean.addUrlPatterns("/*");filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");filterRegistrationBean.addInitParameter("profileEnable", "true");filterRegistrationBean.addInitParameter("principalCookieName", "USER_COOKIE");filterRegistrationBean.addInitParameter("principalSessionName", "USER_SESSION");filterRegistrationBean.addInitParameter("DruidWebStatFilter", "/*");return filterRegistrationBean;}
}

配置数据源mysql-op的连接信息，包含配置数据库连接信息，扫描对应的实体类路径。

package com.xbz.common.config;import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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;
import java.sql.SQLException;@Configuration
@MapperScan(basePackages  = {"com.biz.data.process.mapper.op", //配置数据源需要读取的mapper文件路径，也就是需要在这个数据源下执行的sql"com.biz.data.process.mapper.npa","com.biz.data.process.mapper.cust"} , sqlSessionFactoryRef = "mysqlSqlSessionFactory")
public class MysqlDbConfig {private Logger logger = LoggerFactory.getLogger(MysqlDbConfig.class);private static final String MAPPER_LOCATION = "classpath*:mapper/master/*.xml";private static final String DOMAIN_PACKAGE = "com.xbz.**.domain";//从配置文件中读取数据库连接信息@Value("${spring.datasource.url}")private String dbUrl;//从配置文件中读取数据库连接信息@Value("${spring.datasource.username}")private String username;//从配置文件中读取数据库连接信息@Value("${spring.datasource.password}")private String password;//从配置文件中读取数据库连接信息@Value("${spring.datasource.driverClassName}")private String driverClassName;//从配置文件中读取数据库连接信息@Value("${spring.datasource.schema}")private String schema;@AutowiredMybatisPlusInterceptor mybatisPlusInterceptor; //分页插件注入@Bean(name="masterDataSource")   //声明其为Bean实例@Primary  //在同样的DataSource中，首先使用被标注的DataSourcepublic DataSource MysqlDbConfig () {DruidDataSource datasource = new DruidDataSource();//不适用Druid时，使用下面的数据源连接//DriverManagerDataSource  datasource=new DriverManagerDataSource ();datasource.setUrl(dbUrl);datasource.setUsername(username);datasource.setPassword(password);datasource.setDriverClassName(driverClassName);datasource.setSchema(schema);return datasource;}//事务处理异常的类@Bean(name = "mysqlTransactionManager")@Primarypublic DataSourceTransactionManager masterTransactionManager() {return new DataSourceTransactionManager(masterDataSource());}@Bean(name = "mysqlSqlSessionFactory")@Primarypublic SqlSessionFactory masterSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource mysqlDataSource)throws Exception {final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();sessionFactory.setDataSource(mysqlDataSource);      // mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MasterDbConfig.MAPPER_LOCATION));//domain的具体路径，读取do类和数据库表的映射sessionFactory.setTypeAliasesPackage(DOMAIN_PACKAGE);// 设置分页sessionFactory.setPlugins(mybatisPlusInterceptor);//mybatis 数据库字段与实体类属性驼峰映射配置sessionFactory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);return sessionFactory.getObject();}@Bean(name = "db1SqlSessionTemplate")@Primarypublic SqlSessionTemplate dbSqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory)  {return new SqlSessionTemplate(sqlSessionFactory);}
}

配置数据源pgsql的连接信息，包含配置数据库连接信息，扫描对应的实体类路径。

package com.xbz.common.config;import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;
import java.sql.SQLException;/*** 从数据源配置* 若需要配置更多数据源 , 直接在yml中添加数据源配置再增加相应的新的数据源配置类即可*/
@Configuration
@MapperScan(basePackages  ={"com.biz.data.process.mapper.op", //配置数据源需要读取的mapper文件路径，也就是需要在这个数据源下执行的sql"com.biz.data.process.mapper.npa","com.biz.data.process.mapper.cust"} , sqlSessionFactoryRef = "clusterSqlSessionFactory")
public class PgsqlDbConfig {private Logger logger = LoggerFactory.getLogger(PgsqlDbConfig .class);// 精确到 cluster 目录，以便跟其他数据源隔离static final String PACKAGE = "com.xbz.**.dao.cluster";private static final String MAPPER_LOCATION = "classpath*:mapper/cluster/*.xml";private static final String DOMAIN_PACKAGE = "com.xbz.**.domain";@Value("${spring.datasource.url2}")private String dbUrl;@Value("${spring.datasource.username2}")private String username;@Value("${spring.datasource.password2}")private String password;@Value("${spring.datasource.driverClassName}")private String driverClassName;@AutowiredMybatisPlusInterceptor mybatisPlusInterceptor; //分页插件注入@Bean(name="pgsqlDataSource")   //声明其为Bean实例public DataSource pgsqlDataSource() {//不适用Druid时，使用下面的数据源连接//DriverManagerDataSource  datasource=new DriverManagerDataSource ();     DruidDataSource datasource = new DruidDataSource();datasource.setUrl(dbUrl);datasource.setUsername(username);datasource.setPassword(password);datasource.setDriverClassName(driverClassName);return datasource;}@Bean(name = "pgsqlTransactionManager")public DataSourceTransactionManager clusterTransactionManager() {return new DataSourceTransactionManager(clusterDataSource());}@Bean(name = "pgsqlSqlSessionFactory")public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("pgsqlDataSource") DataSource pgsqlDataSource)throws Exception {final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();sessionFactory.setDataSource(pgsqlDataSource);// mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(ClusterDbConfig.MAPPER_LOCATION));sessionFactory.setTypeAliasesPackage(DOMAIN_PACKAGE);/ 设置分页sessionFactory.setPlugins(mybatisPlusInterceptor);//mybatis 数据库字段与实体类属性驼峰映射配置sessionFactory.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);return sessionFactory.getObject();}
}

不同的数据源配置不同的mapper扫描位置，需要查询哪一个数据源的数据，就直接调用该数据源对应的mapper类文件（位于Domain包下）就可以。

MybatisPlus分页配置

package com.whut.multisource.config;import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;/*** MybatisPlus分页配置*/
@Configuration
public class MybatisPlusConfig {/*** 分页插件*/@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));return interceptor;}}