SpringBoot多数据源配置:
有两个数据源就要配置两个DataSource数据源,要使用Mybatis,也要有两个SqlSessionFactory,同时也要有两个SqlSessionTemplate。
本人项目(子项目)的多数据源整合思路是根据分包进行处理的,即使用不同的数据源业务放在不同的包下面:
1、项目目录结构如下
2、pom.xml文件内容如下(每个人的项目配置不尽相同)
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>CMIS</artifactId>
<groupId>com.sinosoft</groupId>
<version>1.0.1</version>
<relativePath>../pom.xml</relativePath>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>cmis-etl</artifactId>
<packaging>jar</packaging>
<version>1.0.0</version>
<name>数据抽取服务</name>
<description>数据抽取服务</description>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-dependencies</artifactId>
<version>Finchley.RC1</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<dependency>
<groupId>com.sinosoft</groupId>
<artifactId>sinofw-common</artifactId>
<version>1.0.1</version>
</dependency>
<dependency>
<groupId>com.sinosoft</groupId>
<artifactId>sinofw-MyBatisGenerator</artifactId>
<scope>compile</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<exclusions>
<!-- 打包时过滤掉spring-boot默认集成的tomcat8相关jar包 -->
<exclusion>
<artifactId>org.springframework.boot</artifactId>
<groupId>spring-boot-starter-tomcat</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-legacy</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<!--jdbc -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- 阿里连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<!--mybatis相关 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<!--mapper -->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
</dependency>
<!--pagehelper分页插件 -->
<!-- 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
</dependency>
<!--spring-eureka -->
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-starter-eureka-server</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
</dependency>
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
</dependency>
</dependencies>
<!-- spring-cloud需要的 ,必须引入,否则缺失jar包 -->
<repositories>
<repository>
<id>spring-milestones</id>
<name>Spring Milestones</name>
<url>https://repo.spring.io/milestone</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
</repository>
</repositories>
<build>
<!-- 定义项目打包时的名字 -->
<finalName>${project.artifactId}</finalName>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<fork>true</fork>
<outputDirectory>${jar.target}</outputDirectory>
</configuration>
</plugin>
</plugins>
</build>
</project>
3、父项目(即有父pom.xml文件的项目)中dev.properties文件内容
profiles.active=dev
eureka.defaultZone=http://localhost:801/eureka
database.url=jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&allowMultiQueries=true
database.username=root
database.password=root123456
his.database.url=jdbc:oracle:thin:@localhost:1521:TEST
his.database.username=root
his.database.password=root123456
redis.host=127.0.0.1
redis.port=6379
redis.timeout=5000
#单个上传文件的大小 -1代表不受限制
maxFileSize=1024MB
#单次请求的文件的总大小
maxRequestSize=1024MB
4、子项目中application.properties及application-dev.properties文件内容如下
spring.profiles.active=${profiles.active}
spring.application.name=${project.artifactId}
#tomcat服务器的相关配置
server.tomcat.uri-encoding=UTF-8
#mybatis
mybatis.mapper-locations=classpath:mybatis/**/*.xml
#显示SQL的执行情况
logging.level.com.sinosoft.mysql.mapper.*=debug
logging.level.com.sinosoft.oracle.mapper.*=debug
server.port=8210
debug=false
#ERROR, WARN, INFO, DEBUG or TRACE
logging.level.root=info
logging.file=E:/log/${project.artifactId}/${project.artifactId}.log
#mysql数据源配置
spring.datasource.primary.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.primary.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.primary.jdbc-url=${database.url}
spring.datasource.primary.username=${database.username}
spring.datasource.primary.password=${database.password}
#oracle数据源配置
spring.datasource.secondary.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.secondary.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.secondary.jdbc-url=${his.database.url}
spring.datasource.secondary.username=${his.database.username}
spring.datasource.secondary.password=${his.database.password}
redis.jedis.host=${redis.host}
redis.jedis.port=${redis.port}
redis.redisson.host=${redis.host}
redis.redisson.port=${redis.port}
eureka.client.healthcheck.enabled=true
eureka.client.serviceUrl.defaultZone=${eureka.defaultZone}
eureka.instance.prefer-ip-address=true
mybatis.sql-log.start=true
5、DataSourceconfig包里面的数据源配置类(包括对分页插件的配置)
5.1 MySqlDataSourceConfig.java 这个类是默认的数据源配置类(默认数据源:添加@Primary)。
package com.sinosoft.datasource;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.ibatis.plugin.Interceptor;
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.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 com.github.pagehelper.PageInterceptor;
/**
* @name: MySqlDataSourceConfig
* @Description: mysql数据源的读取 (默认的数据源配置类)
* @author wanglong
* @date 2019年12月5日下午9:57:00
* @version 1.0
*/
@Configuration // 注册到springboot容器中
@MapperScan(basePackages = "com.sinosoft.mysql.mapper", sqlSessionTemplateRef = "primarySqlSessionTemplate")
public class MySqlDataSourceConfig {
// 创建 DataSource
@Bean(name = "primaryDataSource") // 表示注入到Spring 容器中去
@ConfigurationProperties(prefix = "spring.datasource.primary") // properties中读取
@Primary
public DataSource primaryDataSource() {
return DataSourceBuilder.create().build();
}
// 创建 SqlSessionFactory
@Bean(name = "primarySqlSessionFactory")
@Primary // 表示这个数据源是默认数据源
// 读取application-dev.properties中的配置参数映射成为一个对象
public SqlSessionFactory primarySqlSessionFactory(@Qualifier("primaryDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
//分页插件
Interceptor interceptor = new PageInterceptor();
Properties properties = new Properties();
//数据库
properties.setProperty("helperDialect", "mysql");
//是否将参数offset作为PageNum使用
properties.setProperty("offsetAsPageNum", "true");
//是否进行count查询
properties.setProperty("rowBoundsWithCount", "true");
//是否分页合理化
properties.setProperty("reasonable", "true");
interceptor.setProperties(properties);
bean.setPlugins(new Interceptor[] {interceptor});
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mysql/**/*.xml"));
return bean.getObject();
}
// 创建事务
@Bean(name = "primaryTransactionManager") // 配置事务
@Primary
// @Qualifier表示查找Spring容器中名字为primaryDataSource的对象
public DataSourceTransactionManager primaryTransactionManager(@Qualifier("primaryDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
// 包装到 SqlSessionTemplate 中
@Bean(name = "primarySqlSessionTemplate")
@Primary
public SqlSessionTemplate primarySqlSessionTemplate(@Qualifier("primarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
最关键的地方就是这块了,一层一层注入,首先创建 DataSource,然后创建 SqlSessionFactory 再创建事务,最后包装到 SqlSessionTemplate 中。其中需要指定分库的 mapper 文件地址,以及分库dao层代码
@MapperScan(basePackages = "com.sinosoft.mysql.mapper", sqlSessionTemplateRef = "primarySqlSessionTemplate")
这块的注解就是指明了扫描 dao 层,并且给 dao 层注入指定的 SqlSessionTemplate。所有@Bean
都需要按照命名指定正确。
5.2 OracleDataSourceConfig.java 第二个数据源配置
package com.sinosoft.datasource;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.ibatis.plugin.Interceptor;
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.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
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 com.github.pagehelper.PageInterceptor;
/**
* @name: OracleDataSourceConfig
* @Description: oracle数据源的读取
* @author wanglong
* @date 2019年12月5日下午9:57:00
* @version 1.0
*/
@Configuration
@MapperScan(basePackages = "com.sinosoft.oracle.mapper", sqlSessionTemplateRef = "secondarySqlSessionTemplate")
public class OracleDataSourceConfig {
@Bean(name = "secondaryDataSource")
@ConfigurationProperties(prefix = "spring.datasource.secondary")
public DataSource secondaryDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "secondarySqlSessionFactory")
public SqlSessionFactory secondarySqlSessionFactory(@Qualifier("secondaryDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
//分页插件
Interceptor interceptor = new PageInterceptor();
Properties properties = new Properties();
//数据库
properties.setProperty("helperDialect", "oracle");
//是否将参数offset作为PageNum使用
properties.setProperty("offsetAsPageNum", "true");
//是否进行count查询
properties.setProperty("rowBoundsWithCount", "true");
//是否分页合理化
properties.setProperty("reasonable", "true");
interceptor.setProperties(properties);
bean.setPlugins(new Interceptor[] {interceptor});
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/oracle/**/*.xml"));
return bean.getObject();
}
@Bean(name = "secondaryTransactionManager")
public DataSourceTransactionManager secondaryTransactionManager(@Qualifier("secondaryDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "secondarySqlSessionTemplate")
public SqlSessionTemplate secondarySqlSessionTemplate(@Qualifier("secondarySqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
这样就已经可以实现多数据源的切换了。
但在使用的过程中,会发现控制台会报错:出现“多个分页插件”错误。这里不做分析,大家可百度其他博主写的分析文章。这里将解决方法贴出:
Application启动类
<1>首先要将springboot自带的DataSourceAutoConfiguration禁掉,因为它会读取application-dev.properties文件的spring.datasource.* 属性并自动配置单数据源。
<2>在@SpringBootApplication注解中添加exclude属性排除。因为系统启动的时候PageHelperAutoConfiguration会自动注册,在@SpringBootApplication注解中添加exclude属性排除自动配置。
package com;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.cloud.client.discovery.EnableDiscoveryClient;
import org.springframework.cloud.client.loadbalancer.LoadBalanced;
import org.springframework.context.annotation.Bean;
import org.springframework.http.converter.StringHttpMessageConverter;
import org.springframework.web.client.RestTemplate;
import java.nio.charset.StandardCharsets;
/**
* @name: EtlApplication
* @Description: 数据转换项目启动入口类
* @author wanglong
* @date 2019年12月4日下午9:41:04
* @version 1.0
*/
@SpringBootApplication(exclude = { com.github.pagehelper.autoconfigure.PageHelperAutoConfiguration.class,
org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration.class,
com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure.class })
@EnableDiscoveryClient
//@MapperScan({"com.sinosoft.mapper"}) // 因为用多数据源,所以dao接口的扫描放在配置类中进行
public class EtlApplication {
public static void main(String[] args) {
SpringApplication.run(EtlApplication.class, args);
}
@Bean
@LoadBalanced
RestTemplate restTemplate(){
RestTemplate restTemplate = new RestTemplate();
restTemplate.getMessageConverters().set(1, new StringHttpMessageConverter(StandardCharsets.UTF_8));
return restTemplate;
}
}
解决了分页插件报错问题,下面来说事务管理。
6、多数据源下事务管理
事务管理默认管理的是@Primary修饰的Datasource,也就是PrimaryDataSource。如果要切换事务管理器,使用@Transactional的时候指定value值:下面两个方法(此处仅列出serviceImpl层样例)抛出异常了,所以数据不会添加成功。
@Transactional(value = "primaryTransactionManager")
public String adUserTransaction1(){
User1 user1 = new User1();
user1.setName("小四");
user1.setAge(15);
userMapper1.addUser(user1);
int i = 1 / 0;
return "success";
}
@Transactional(value = "secondaryTransactionManager")
public String adUserTransaction2(){
User2 user2 = new User2();
user2.setName("小留");
user2.setAge(16);
userMapper2.addUser(user2);
int i = 1 / 0;
return "success";
}
像上面这样就能为单个数据源控制事务。
注意下面代码:下面代码虽然不能插入成功,是因为@Transactional默认的value值的@Primary注解修饰的数据源事务管理器,代码在插入第二个数据源的时候就已经异常了,不会往下面执行,所以回滚的是primaryDataSource的事务。
@Transactional
public String addUserToTwoDataSource1(){
User1 user1 = new User1();
user1.setName("小四");
user1.setAge(15);
userMapper1.addUser(user1);
int i = 1 / 0 ;
User2 user2 = new User2();
user2.setName("小屋");
user2.setAge(16);
userMapper2.addUser(user2);
return "success";
}
再看看下面的代码:下面会往secondaryDataSource中插入数据,不会向primaryDataSource中插入,因为在插入primaryDataSource前就已经抛异常了,代码不会往下执行了。所以数据库会往secondaryDataSource插入一条数据。
@Transactional
public String addUserToTwoDataSource2(){
User2 user2 = new User2();
user2.setName("小屋");
user2.setAge(16);
userMapper2.addUser(user2);
int i = 1 / 0 ;
User1 user1 = new User1();
user1.setName("小四");
user1.setAge(15);
userMapper1.addUser(user1);
return "success";
}
接下来在看看下面两个方法的代码:上面已经说过了,@Transactional默认的@Value值是@Primary修饰的事务管理器,所以下面两个方法都会往secondaryDataSource中插入一条数据,但是primaryDataSource中会回滚,不插入任何数据。
@Transactional
public String addUserToTwoDataSource3(){
User2 user2 = new User2();
user2.setName("小屋");
user2.setAge(16);
userMapper2.addUser(user2);
User1 user1 = new User1();
user1.setName("小四");
user1.setAge(15);
userMapper1.addUser(user1);
int i = 1 / 0 ;
return "success";
}
@Transactional
public String addUserToTwoDataSource4(){
User1 user1 = new User1();
user1.setName("小四");
user1.setAge(15);
userMapper1.addUser(user1);
User2 user2 = new User2();
user2.setName("小屋");
user2.setAge(16);
userMapper2.addUser(user2);
int i = 1 / 0 ;
return "success";
}
那么怎么实现一个@Transactional控制两个数据源呢,答案是不行,因为这个是普通的事务管理器,如果是实现了XA协议,并且实现了两段提交的,三段提交的,就能一个@Transactional控制两个或者三个数据源提交事务。MySQL是支持XA协议的。
总结:
1、事务的使用必须要指明是哪个数据源的事务管理器,否则事务失效。例如:@Transactional(value = "primaryDataSource")
2、如果一个方法涉及多个数据源操作,此时无法做到同时回滚,尽量避免这种操作,这里涉及到分布式事务处理,可自行研究。