Springboot——多数据源事务、切换数据源+事务

前言

项目有多个数据源，根据配置文件配置的连接数来自动生成多数据源配置，并且使用 aop切换数据源，使用的是 AbstractRoutingDataSource 重写 determineCurrentLookupKey 方法。

 

在切换数据源之前 @Transactional 先执行，此时会去获取数据源，如果不使用用前置通知(@Before)，那么此时数据源还没有切换就会获取默认的数据源 ，这种情况会导致数据源切换失败。

 

源码：

一、其它方式

1.1 引入Maven依赖

<parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.10.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!--如果要用传统的xml或properties配置，则需要添加此依赖--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> </dependency> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper-spring-boot-starter</artifactId> <version>2.1.5</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> <!-- swagger --> <dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger-ui</artifactId> <version>2.9.2</version> </dependency> <dependency> <groupId>io.springfox</groupId> <artifactId>springfox-swagger2</artifactId> <version>2.9.2</version> </dependency> <!-- spring security --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-security</artifactId> </dependency> <!-- jwt --> <dependency> <groupId>io.jsonwebtoken</groupId> <artifactId>jjwt</artifactId> <version>0.9.1</version> </dependency> <!-- fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.70</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <!--<dependency> <groupId>org.apache.directory.studio</groupId> <artifactId>org.apache.commons.codec</artifactId> <version>1.8</version> </dependency>--> <dependency> <groupId>commons-codec</groupId> <artifactId>commons-codec</artifactId> <version>1.14</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> <plugin> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-maven-plugin</artifactId> <version>1.3.6</version> <configuration> <configurationFile> ${basedir}/src/main/resources/generator/generatorConfig.xml </configurationFile> <overwrite>true</overwrite> <verbose>true</verbose> </configuration> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.41</version> </dependency> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper</artifactId> <version>4.1.5</version> </dependency> </dependencies> </plugin> </plugins> </build>

1.2 配置数据源

# 默认数据源 spring.datasource.url=jdbc:mysql://localhost:3306/user_center?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC spring.datasource.hikari.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.hikari.username=root spring.datasource.hikari.password=yibo # 配置第一个数据源 spring.datasource.hikari.db1.jdbc-url=jdbc:mysql://localhost:3306/user_center?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC spring.datasource.hikari.db1.username=root spring.datasource.hikari.db1.password=yibo spring.datasource.hikari.db1.driver-class-name=com.mysql.cj.jdbc.Driver # 配置第二个数据源 spring.datasource.hikari.db2.jdbc-url=jdbc:mysql://localhost:3306/trade?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC spring.datasource.hikari.db2.username=root spring.datasource.hikari.db2.password=yibo spring.datasource.hikari.db2.driver-class-name=com.mysql.cj.jdbc.Driver #mybatis.type-aliases-package: com.yibo.center.domain.entity #mybatis.mapper-locations: classpath:mapper/*.xml mapper.identity: MYSQL mapper.not-empty: false #是否激活 swagger true or false swagger.enable=true

1.3 构建数据源

• 默认数据源SpringBoot会自动构建

• 构建非默认数据源

import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.SqlSessionTemplate; 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 tk.mybatis.spring.annotation.MapperScan; import javax.sql.DataSource; /** * @author: huangyibo * @Date: 2020/6/19 2:26 * @Description: */ @Configuration @MapperScan(basePackages = "com.yibo.multisource.mapper.user", sqlSessionTemplateRef = "db1SqlSessionTemplate") public class DataSource1Config { /** * 生成数据源. @Primary 注解声明为默认数据源 */ @Bean(name = "db1DataSource") @ConfigurationProperties(prefix = "spring.datasource.hikari.db1") @Primary public DataSource testDataSource() { return DataSourceBuilder.create().build(); } /** * 创建 SqlSessionFactory */ @Bean(name = "db1SqlSessionFactory") @Primary public SqlSessionFactory testSqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/user/*.xml")); return bean.getObject(); } /** * 配置事务管理 */ @Bean(name = "db1TransactionManager") @Primary public DataSourceTransactionManager testTransactionManager(@Qualifier("db1DataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean(name = "db1SqlSessionTemplate") @Primary public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } } import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.SqlSessionTemplate; 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 tk.mybatis.spring.annotation.MapperScan; import javax.sql.DataSource; /** * @author: huangyibo * @Date: 2020/6/19 2:27 * @Description: */ @Configuration @MapperScan(basePackages = "com.yibo.multisource.mapper.trade", sqlSessionTemplateRef = "db2SqlSessionTemplate") public class DataSource2Config { @Bean(name = "db2DataSource") @ConfigurationProperties(prefix = "spring.datasource.hikari.db2") public DataSource testDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "db2SqlSessionFactory") public SqlSessionFactory testSqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/trade/*.xml")); return bean.getObject(); } @Bean(name = "db2TransactionManager") public DataSourceTransactionManager testTransactionManager(@Qualifier("db2DataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } @Bean(name = "db2SqlSessionTemplate") public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } }

非primary的事务管理器 需要在@Transactional中加上事务管理器beanName，不然事务不会起作用，默认使用的是primary的事务管理器

1.4 具体使用

import com.yibo.multisource.domain.entity.trade.TradeGoods; import com.yibo.multisource.domain.entity.vo.TradeGoodsAO; import com.yibo.multisource.mapper.trade.TradeGoodsMapper; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.Date; import java.util.List; /** * @author: huangyibo * @Date: 2020/6/11 0:23 * @Description: */ @Service public class TradeGoodsService { @Autowired private TradeGoodsMapper tradeGoodsMapper; @Transactional public List<TradeGoods> findAll(){ return tradeGoodsMapper.selectAll(); } @Transactional("db2TransactionManager") public String addTradeGoods(TradeGoodsAO tradeGoodsAO){ TradeGoods tradeGoods = new TradeGoods(); BeanUtils.copyProperties(tradeGoodsAO,tradeGoods); tradeGoods.setAddTime(new Date()); tradeGoodsMapper.insert(tradeGoods); return "SUCCESS"; } } import com.yibo.multisource.domain.entity.user.User; import com.yibo.multisource.domain.entity.vo.UserAO; import com.yibo.multisource.mapper.user.UserMapper; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.Date; import java.util.List; /** * @author: huangyibo * @Date: 2020/6/10 23:46 * @Description: */ @Service public class UserService { @Autowired private UserMapper userMapper; public List<User> findAll(){ return userMapper.selectAll(); } @Transactional public User findById(Integer id){ User user = new User(); user.setId(id); return userMapper.selectOne(user); } @Transactional("db1TransactionManager") public String addUser(UserAO userAO){ User user = new User(); BeanUtils.copyProperties(userAO,user); user.setCreateTime(new Date()); user.setUpdateTime(new Date()); userMapper.insert(user); return "SUCCESS"; } }

参考： https://blog.csdn.net/sinat_36454672/article/details/117529762

https://blog.csdn.net/study_zmm/article/details/106790031