SpringBoot 整合多数据源和事务管理

最新推荐文章于 2024-08-12 08:30:00 发布

X_ABU

最新推荐文章于 2024-08-12 08:30:00 发布

阅读量263

点赞数

分类专栏： SpringBoot 配置文件 MySQL 文章标签： mysql java 分布式 mybatis spring boot

本文链接：https://blog.csdn.net/X_ABU/article/details/109724934

版权

SpringBoot 同时被 3 个专栏收录

10 篇文章 0 订阅

订阅专栏

配置文件

8 篇文章 0 订阅

订阅专栏

MySQL

7 篇文章 0 订阅

订阅专栏

文章目录

一、创建SpringBoot项目

这里使用maven快速搭建

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.example</groupId>
    <artifactId>myproject</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>myproject</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
        <springboot.version>2.1.18.RELEASE</springboot.version>
        <mysql.version>5.1.34</mysql.version>
        <mybatis.version>1.3.2</mybatis.version>
        <junit.version>4.12</junit.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
            <version>${springboot.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${springboot.version}</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.version}</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
    </dependencies>

</project>

创建主函数文件路径package com.example.myproject;

@SpringBootApplication
public class MyprojectApplication {
    public static void main(String[] args) {
        SpringApplication.run(MyprojectApplication.class, args);
    }
}

这里使用web方式测试，创建Controller 文件路径com.example.myproject.api.controllers.v1;

@RestController
public class HelloWorldController {
    @RequestMapping("/hello")
    public String index() {
        return "Hello World";
    }
}

启动后测试，浏览器返回显示Hello World表示工程搭建完成

二、添加多数据源

修改 application.yml

spring:
  datasource:
    test1:
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.17.131:3306/db01?useUnicode=true&characterEncoding=utf-8
      username: root
      password: root
    test2:
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.17.131:3306/db02?useUnicode=true&characterEncoding=utf-8
      username: root
      password: root

创建两个数据库

CREATE DATABASE `db01`
CREATE DATABASE `db02`

分别创建一个一样的表

CREATE TABLE `db01_dept` (
  `dept_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `dept_name` varchar(255) DEFAULT NULL COMMENT '部门名称',
  `data_source` varchar(255) DEFAULT NULL COMMENT '数据库来源',
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

CREATE TABLE `db02_dept` (
  `dept_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `dept_name` varchar(255) DEFAULT NULL COMMENT '部门名称',
  `data_source` varchar(255) DEFAULT NULL COMMENT '数据库来源',
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

添加实体映射对象Dept

package com.example.myproject.domain.entity;

import com.example.myproject.infra.constant.DataSourceType;

public class Dept {
    private Long deptId;
    private String deptName;
    private DataSourceType dataSource;

    public Long getDeptId() {
        return deptId;
    }

    public void setDeptId(Long deptId) {
        this.deptId = deptId;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    public DataSourceType getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSourceType dataSource) {
        this.dataSource = dataSource;
    }
}

这里添加一个数据源字段，查看数据是否分别正确的插入到两个库中

package com.example.myproject.infra.constant;

public enum DataSourceType {
    db01,
    db02
}

创建mapper,添加插入方法

package com.example.myproject.infra.mapper.db01;

import com.example.myproject.infra.constant.DataSourceType;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface Dept01Mapper {
    @Insert("insert into db01_dept(dept_name,data_source) values(#{deptName},#{dataSource});")
    public int addDept(@Param("deptName") String deptName, @Param("dataSource") DataSourceType dataSource);
}

package com.example.myproject.infra.mapper.db02;

import com.example.myproject.infra.constant.DataSourceType;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface Dept02Mapper {
    @Insert("insert into db02_dept(dept_name,data_source) values(#{deptName},#{dataSource});")
    public int addDept(@Param("deptName") String deptName, @Param("dataSource") DataSourceType dataSource);
}

通过上面的代码，我把两个不同库的文件分开了，这里要主要不分开在包扫描时系统分不清库，这样在使用的时候会抛错；

创建两个数据源配置文件

package com.example.myproject.config;

import com.atomikos.jdbc.AtomikosDataSourceBean;
import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
@MapperScan(basePackages = "com.example.myproject.infra.mapper.db01", sqlSessionFactoryRef = "db01SqlSessionFactory")
public class DbSource1Config {

    /**
     * 配置db01数据库
     */
    @Bean(name = "db01DataSource")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.db01")
    public DataSource testDataSource() {
        return DataSourceBuilder.create().build();
    }

    /*
     * db01 sql会话工厂
     */
    @Bean(name = "db01SqlSessionFactory")
    @Primary
    public SqlSessionFactory DbSqlSessionFactory(@Qualifier("db01DataSource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    /**
     * db01 事物管理
     */
    @Bean(name = "db01TransactionManager")
    @Primary
    public DataSourceTransactionManager DbTransactionManager(@Qualifier("db01DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "db01SqlSessionTemplate")
    public SqlSessionTemplate DbSqlSessionTemplate(
            @Qualifier("db01SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

package com.example.myproject.config;

import com.atomikos.jdbc.AtomikosDataSourceBean;
import com.mysql.jdbc.jdbc2.optional.MysqlXADataSource;
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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
@MapperScan(basePackages = "com.example.myproject.infra.mapper.db02", sqlSessionFactoryRef = "db02SqlSessionFactory")
public class DbSource2Config {

    /**
     * 配置db01数据库
     */
    @Bean(name = "db02DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db02")
    public DataSource DbDataSource() {
        return DataSourceBuilder.create().build();
    }

    /*
     * db02 sql会话工厂
     */
    @Bean(name = "db02SqlSessionFactory")
    public SqlSessionFactory DbSqlSessionFactory(@Qualifier("db02DataSource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }

    /**
     * db02 事物管理
     */
    @Bean(name = "db02TransactionManager")
    public DataSourceTransactionManager DbTransactionManager(@Qualifier("db02DataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "db02SqlSessionTemplate")
    public SqlSessionTemplate DbSqlSessionTemplate(
            @Qualifier("db02SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

创建一个service的接口

package com.example.myproject.app.service;

public interface DeptService {
    void addDept();
}

实现类

package com.example.myproject.app.service.impl;


import com.example.myproject.app.service.DeptService;
import com.example.myproject.infra.constant.DataSourceType;
import com.example.myproject.infra.mapper.db01.Dept01Mapper;
import com.example.myproject.infra.mapper.db02.Dept02Mapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class DeptServiceImpl implements DeptService {
    @Autowired
    private Dept01Mapper dept01Mapper;
    @Autowired
    private Dept02Mapper dept02Mapper;

    @Override
    public void addDept() {
        dept01Mapper.addDept("db Dept001", DataSourceType.db01);
        dept02Mapper.addDept("db Dept002", DataSourceType.db02);
    }
}

修改一下controller,添加一个方法

package com.example.myproject.api.controllers.v1;

import com.example.myproject.app.service.DeptService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class HelloWorldController {
    @Autowired
    private DeptService deptService;

    @RequestMapping("/hello")
    public String index() {
        return "Hello World";
    }

    @RequestMapping("/add")
    public String add() {
        deptService.addDept();
        return "success";
    }
}

运行主方法，测试/add接口，发现我们的数据是可以分别插入到两个库里的；

到这里我们的多数据源配置完成

这里要注意一个注解@Primary，它只能配置在多数据源其中的一个上，指定在同一个接口有多个实现类可以注入的时候，默认选择这一个，就是默认的数据源是这一个。

三、分布式事务(jta-atomikos)

这里要数一下两个数据库的事务问题，这里使用的方式是jta+atomikos，spring2.5以前，对分布式多数据源不支持，但是在spring3.0之后针对这个问题的做出了解决方案，我这里使用的是2.1.18.RELEASE 所以使用这样的方式

修改一下上面的例子

@Service
public class DeptServiceImpl implements DeptService {
    @Autowired
    private Dept01Mapper dept01Mapper;
    @Autowired
    private Dept02Mapper dept02Mapper;

    @Override
    @Transactional
    public void addDept() {
        dept01Mapper.addDept("db Dept001", DataSourceType.db01);
        dept02Mapper.addDept("db Dept002", DataSourceType.db02);
        int i = 1 / 0;
    }
}

在这里添加事务，抛出一个异常，运行后发现事务只对db01有效，其他没有效果，这里就要使用多数据源的分布式事务了

这里要改一下application.yml

spring:
  datasource:
    db01:
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.17.131:3306/db01?useUnicode=true&characterEncoding=utf-8
      username: root
      password: root
      minPoolSize: 3
      maxPoolSize: 25
      maxLifetime: 20000
      borrowConnectionTimeout: 30
      loginTimeout: 30
      maintenanceInterval: 60
      maxIdleTime: 60
      testQuery: select 1
    db02:
      driverClassName: com.mysql.jdbc.Driver
      url: jdbc:mysql://192.168.17.131:3306/db02?useUnicode=true&characterEncoding=utf-8
      username: root
      password: root
      minPoolSize: 3
      maxPoolSize: 25
      maxLifetime: 20000
      borrowConnectionTimeout: 30
      loginTimeout: 30
      maintenanceInterval: 60
      maxIdleTime: 60
      testQuery: select 1

我们要获取这个类，这里需要创建两个实体来获取

package com.example.myproject.config;

import org.springframework.boot.context.properties.ConfigurationProperties;

@ConfigurationProperties(prefix = "spring.datasource.db01")
public class DBConfig1 {
    private String url;
    private String username;
    private String password;
    private int minPoolSize;
    private int maxPoolSize;
    private int maxLifetime;
    private int borrowConnectionTimeout;
    private int loginTimeout;
    private int maintenanceInterval;
    private int maxIdleTime;
    private String testQuery;

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getMinPoolSize() {
        return minPoolSize;
    }

    public void setMinPoolSize(int minPoolSize) {
        this.minPoolSize = minPoolSize;
    }

    public int getMaxPoolSize() {
        return maxPoolSize;
    }

    public void setMaxPoolSize(int maxPoolSize) {
        this.maxPoolSize = maxPoolSize;
    }

    public int getMaxLifetime() {
        return maxLifetime;
    }

    public void setMaxLifetime(int maxLifetime) {
        this.maxLifetime = maxLifetime;
    }

    public int getBorrowConnectionTimeout() {
        return borrowConnectionTimeout;
    }

    public void setBorrowConnectionTimeout(int borrowConnectionTimeout) {
        this.borrowConnectionTimeout = borrowConnectionTimeout;
    }

    public int getLoginTimeout() {
        return loginTimeout;
    }

    public void setLoginTimeout(int loginTimeout) {
        this.loginTimeout = loginTimeout;
    }

    public int getMaintenanceInterval() {
        return maintenanceInterval;
    }

    public void setMaintenanceInterval(int maintenanceInterval) {
        this.maintenanceInterval = maintenanceInterval;
    }

    public int getMaxIdleTime() {
        return maxIdleTime;
    }

    public void setMaxIdleTime(int maxIdleTime) {
        this.maxIdleTime = maxIdleTime;
    }

    public String getTestQuery() {
        return testQuery;
    }

    public void setTestQuery(String testQuery) {
        this.testQuery = testQuery;
    }
}

package com.example.myproject.config;

import org.springframework.boot.context.properties.ConfigurationProperties;

@ConfigurationProperties(prefix = "spring.datasource.db02")
public class DBConfig2 {
    private String url;
    private String username;
    private String password;
    private int minPoolSize;
    private int maxPoolSize;
    private int maxLifetime;
    private int borrowConnectionTimeout;
    private int loginTimeout;
    private int maintenanceInterval;
    private int maxIdleTime;
    private String testQuery;

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getMinPoolSize() {
        return minPoolSize;
    }

    public void setMinPoolSize(int minPoolSize) {
        this.minPoolSize = minPoolSize;
    }

    public int getMaxPoolSize() {
        return maxPoolSize;
    }

    public void setMaxPoolSize(int maxPoolSize) {
        this.maxPoolSize = maxPoolSize;
    }

    public int getMaxLifetime() {
        return maxLifetime;
    }

    public void setMaxLifetime(int maxLifetime) {
        this.maxLifetime = maxLifetime;
    }

    public int getBorrowConnectionTimeout() {
        return borrowConnectionTimeout;
    }

    public void setBorrowConnectionTimeout(int borrowConnectionTimeout) {
        this.borrowConnectionTimeout = borrowConnectionTimeout;
    }

    public int getLoginTimeout() {
        return loginTimeout;
    }

    public void setLoginTimeout(int loginTimeout) {
        this.loginTimeout = loginTimeout;
    }

    public int getMaintenanceInterval() {
        return maintenanceInterval;
    }

    public void setMaintenanceInterval(int maintenanceInterval) {
        this.maintenanceInterval = maintenanceInterval;
    }

    public int getMaxIdleTime() {
        return maxIdleTime;
    }

    public void setMaxIdleTime(int maxIdleTime) {
        this.maxIdleTime = maxIdleTime;
    }

    public String getTestQuery() {
        return testQuery;
    }

    public void setTestQuery(String testQuery) {
        this.testQuery = testQuery;
    }
}

到这里要导入两个包

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jta-atomikos</artifactId>
            <version>${springboot.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <version>${springboot.version}</version>
            <optional>true</optional>
        </dependency>

然后修改一下我们的配置文件类,修改方法DbSource

@Primary
@Bean(name = "db01DataSource")
public DataSource DbSource(DBConfig1 testConfig) throws SQLException {
    MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
    mysqlXaDataSource.setUrl(testConfig.getUrl());
    mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
    mysqlXaDataSource.setPassword(testConfig.getPassword());
    mysqlXaDataSource.setUser(testConfig.getUsername());
    mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
    AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
    xaDataSource.setXaDataSource(mysqlXaDataSource);
    xaDataSource.setUniqueResourceName("db01DataSource");
    xaDataSource.setMinPoolSize(testConfig.getMinPoolSize());
    xaDataSource.setMaxPoolSize(testConfig.getMaxPoolSize());
    xaDataSource.setMaxLifetime(testConfig.getMaxLifetime());
    xaDataSource.setBorrowConnectionTimeout(testConfig.getBorrowConnectionTimeout());
    xaDataSource.setLoginTimeout(testConfig.getLoginTimeout());
    xaDataSource.setMaintenanceInterval(testConfig.getMaintenanceInterval());
    xaDataSource.setMaxIdleTime(testConfig.getMaxIdleTime());
    xaDataSource.setTestQuery(testConfig.getTestQuery());
    return xaDataSource;
}

@Bean(name = "db02DataSource")
public DataSource DbSource(DBConfig2 testConfig) throws SQLException {
    MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
    mysqlXaDataSource.setUrl(testConfig.getUrl());
    mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
    mysqlXaDataSource.setPassword(testConfig.getPassword());
    mysqlXaDataSource.setUser(testConfig.getUsername());
    mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
    AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
    xaDataSource.setXaDataSource(mysqlXaDataSource);
    xaDataSource.setUniqueResourceName("db02DataSource");
    xaDataSource.setMinPoolSize(testConfig.getMinPoolSize());
    xaDataSource.setMaxPoolSize(testConfig.getMaxPoolSize());
    xaDataSource.setMaxLifetime(testConfig.getMaxLifetime());
    xaDataSource.setBorrowConnectionTimeout(testConfig.getBorrowConnectionTimeout());
    xaDataSource.setLoginTimeout(testConfig.getLoginTimeout());
    xaDataSource.setMaintenanceInterval(testConfig.getMaintenanceInterval());
    xaDataSource.setMaxIdleTime(testConfig.getMaxIdleTime());
    xaDataSource.setTestQuery(testConfig.getTestQuery());
    return xaDataSource;
}

修改启动方法，使用@ConfigurationProperties注解要在启动方法上启动这个启动器(@EnableConfigurationProperties)实现bean的加载

@SpringBootApplication
@EnableConfigurationProperties(value = {DBConfig1.class, DBConfig2.class})
public class MyprojectApplication {
    public static void main(String[] args) {
        SpringApplication.run(MyprojectApplication.class, args);
    }
}