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

原创

小波同学 2023-01-09 02:10:20 博主文章分类：SpringBoot ©著作权

文章标签 spring 数据源 SpringBoot 多数据源多数据源事务 文章分类 Java 后端开发

©著作权归作者所有：来自51CTO博客作者小波同学的原创作品，请联系作者获取转载授权，否则将追究法律责任

前言

项目有多个数据源，根据配置文件配置的连接数来自动生成多数据源配置，并且使用 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