springboot整合多数据源配置(包含事务处理)

最新推荐文章于 2024-06-16 00:10:05 发布

醉酒听雨

最新推荐文章于 2024-06-16 00:10:05 发布

阅读量2.8k

点赞数 3

分类专栏： springboot的一些小配置文章标签： spring boot mysql

本文链接：https://blog.csdn.net/cuiwenbo3000/article/details/121627963

版权

springboot的一些小配置专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、第一种

springboot+druid+mybatisplus+jdk8使用注解整合

1.1 application.yml 配置文件如下

server:
  port: 8080
spring:
  datasource:
    dynamic:
      primary: database1 # 配置默认数据库
      datasource:
        database1: # 数据源1配置
          url: jdbc:mysql://127.0.0.1:3306/database1?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
          username: root
          password: root
          driver-class-name: com.mysql.cj.jdbc.Driver
        database2: # 数据源2配置
          url: jdbc:mysql://127.0.0.1:3306/database2?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
          username: root
          password: root
          driver-class-name: com.mysql.cj.jdbc.Driver
      durid:
        initial-size: 1
        max-active: 20
        min-idle: 1
        max-wait: 60000
  autoconfigure:
    # 去除druid配置
    exclude:  com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure

1.2 配置启动类

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

1.3 给使用非默认数据源添加注解@DS

@DS("database2")
public interface DemoMapper extends BaseMapper<User> {
    @Select("SELECT * FROM test")
    @DS("database2")
    List<User> selectAll();
}
//-------------------
@Service
@DS("database2")
public class DemoServiceImpl extends ServiceImpl<DemoMapper, DemoVo> implements IDemoService {}
//-------------------

二、第二种

springboot+mybatis使用分包方式整合

spring-boot-starter-web
mybatis-spring-boot-starter
mysql-connector-java
lombok

1.1 application.yml

server:
  port: 8080 # 启动端口
spring:
  datasource: 
    db1: # 数据源1
      jdbc-url: jdbc:mysql://localhost:3306/db1?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver
    db2: # 数据源2
      jdbc-url: jdbc:mysql://localhost:3306/db2?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver

1.2 多数据源的配置类

SpringBoot2.0以前需要在方法上加Primary注解，来判断默认的数据库连接

@Configuration
@MapperScan(basePackages = "com.edu.multipledatasource.mapper.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSourceConfig1 {

    @Primary // 表示这个数据源是默认数据源, 这个注解必须要加，因为不加的话spring将分不清楚那个为主数据源（默认数据源）
    @Bean("db1DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db1") //读取application.yml中的配置参数映射成为一个对象
    public DataSource getDb1DataSource(){
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean("db1SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // mapper的xml形式文件位置必须要配置，不然将报错：no statement （这种错误也可能是mapper的xml中，namespace与项目的路径不一致导致）
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mybatis/db1/*.xml"));
        return bean.getObject();
    }

    @Primary
    @Bean("db1SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

1.3 配置文件2

@Configuration
@MapperScan(basePackages = "com.edu.multipledatasource.mapper.db2", sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSourceConfig2 {

    @Bean("db2DataSource")
    @ConfigurationProperties(prefix = "spring.datasource.db2")
    public DataSource getDb1DataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean("db2SqlSessionFactory")
    public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/db2/*.xml"));
        return bean.getObject();
    }

    @Bean("db2SqlSessionTemplate")
    public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

---------------------------------------------------------------------------------------------------------------------------------

多数据源事务处理（与上述的多数据源整合区分开）

1、需要使用jta-atomikos进行事务管理(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">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.edu</groupId>
    <artifactId>multiple-data-source</artifactId>
    <version>1.0</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
    </parent>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- MySQL 连接驱动依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.39</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jta-atomikos</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
    </dependencies>

</project>

2、修改application.yml文件

server:
  port: 8080
spring:
  jta:
    log-dir: /logs
  datasource:
    demo1:
      jdbc-url: jdbc:mysql://localhost:3306/demo1?useUnicode=true&characterEncoding=utf-8&useSSL=false
      username: root
      password: cwb138
      driver-class-name: com.mysql.jdbc.Driver
      borrowConnectionTimeout: 30
      loginTimeout: 30
      maintenanceInterval: 60
      maxIdleTime: 60
      maxLifetime: 20000
      maxPoolSize: 25
      minPoolSize: 3
      uniqueResourceName: demo1DataSource
    demo2:
      jdbc-url: jdbc:mysql://localhost:3306/demo2?useUnicode=true&characterEncoding=utf-8&useSSL=false
      username: root
      password: cwb138
      driver-class-name: com.mysql.jdbc.Driver
      borrowConnectionTimeout: 30
      loginTimeout: 30
      maintenanceInterval: 60
      maxIdleTime: 60
      maxLifetime: 20000
      maxPoolSize: 25
      minPoolSize: 3
      uniqueResourceName: demo2DataSource

3、配置类1(Demo1Config、DataSourceConfig1)

package com.edu.config.vo;

import lombok.Data;
import lombok.ToString;
import org.springframework.boot.context.properties.ConfigurationProperties;

@Data
@ToString
@ConfigurationProperties(prefix = "spring.datasource.demo1")
public class Demo1Config {

    private String jdbcUrl;
    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;
    private String uniqueResourceName;
}

package com.edu.config;

import com.edu.config.vo.Demo1Config;
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.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

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

@Configuration
@MapperScan(value = "com.edu.mapper.demo1",sqlSessionTemplateRef = "demo1SqlSessionTemplate")
public class DataSourceConfig1 {

    @Bean(name = "demo1DataSource")
    public DataSource demo1DataSource (Demo1Config demo1Config) throws SQLException {
        System.out.println(demo1Config);
        MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
        mysqlXaDataSource.setUrl(demo1Config.getJdbcUrl());
        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
        mysqlXaDataSource.setPassword(demo1Config.getPassword());
        mysqlXaDataSource.setUser(demo1Config.getUsername());
        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
        //注册到全局事务
        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(mysqlXaDataSource);
        xaDataSource.setUniqueResourceName(demo1Config.getUniqueResourceName());
        xaDataSource.setMinPoolSize(demo1Config.getMinPoolSize());
        xaDataSource.setMaxPoolSize(demo1Config.getMaxPoolSize());
        xaDataSource.setMaxLifetime(demo1Config.getMaxLifetime());
        xaDataSource.setBorrowConnectionTimeout(demo1Config.getBorrowConnectionTimeout());
        xaDataSource.setLoginTimeout(demo1Config.getLoginTimeout());
        xaDataSource.setMaintenanceInterval(demo1Config.getMaintenanceInterval());
        xaDataSource.setMaxIdleTime(demo1Config.getMaxIdleTime());
        xaDataSource.setTestQuery(demo1Config.getTestQuery());
        return xaDataSource;
    }

    @Bean(name = "demo1SqlSessionFactory")
    public SqlSessionFactory demo1SqlSessionFactory (@Qualifier("demo1DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }


    @Bean(name = "demo1SqlSessionTemplate")
    public SqlSessionTemplate demo1SqlSessionTemplate (@Qualifier("demo1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

3、配置类2(Demo2Config、DataSourceConfig2)

package com.edu.config.vo;

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

@Data
@ConfigurationProperties(prefix = "spring.datasource.demo2")
public class Demo2Config {

    private String jdbcUrl;
    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;
    private String uniqueResourceName;
}

package com.edu.config;

import com.edu.config.vo.Demo2Config;
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.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

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

@Configuration
@MapperScan(value = "com.edu.mapper.demo2",sqlSessionTemplateRef = "demo2SqlSessionTemplate")
public class DataSourceConfig2 {


    @Bean(name = "demo2DataSource")
    public DataSource demo2DataSource (Demo2Config demo2Config) throws SQLException {
        System.out.println(demo2Config);
        MysqlXADataSource mysqlXaDataSource = new MysqlXADataSource();
        mysqlXaDataSource.setUrl(demo2Config.getJdbcUrl());
        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
        mysqlXaDataSource.setPassword(demo2Config.getPassword());
        mysqlXaDataSource.setUser(demo2Config.getUsername());
        mysqlXaDataSource.setPinGlobalTxToPhysicalConnection(true);
        //注册到全局事务
        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(mysqlXaDataSource);
        xaDataSource.setUniqueResourceName(demo2Config.getUniqueResourceName());
        xaDataSource.setMinPoolSize(demo2Config.getMinPoolSize());
        xaDataSource.setMaxPoolSize(demo2Config.getMaxPoolSize());
        xaDataSource.setMaxLifetime(demo2Config.getMaxLifetime());
        xaDataSource.setBorrowConnectionTimeout(demo2Config.getBorrowConnectionTimeout());
        xaDataSource.setLoginTimeout(demo2Config.getLoginTimeout());
        xaDataSource.setMaintenanceInterval(demo2Config.getMaintenanceInterval());
        xaDataSource.setMaxIdleTime(demo2Config.getMaxIdleTime());
        xaDataSource.setTestQuery(demo2Config.getTestQuery());
        return xaDataSource;
    }

    @Bean(name = "demo2SqlSessionFactory")
    public SqlSessionFactory demo2SqlSessionFactory (@Qualifier("demo2DataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        return bean.getObject();
    }


    @Bean(name = "demo2SqlSessionTemplate")
    public SqlSessionTemplate demo2SqlSessionTemplate (@Qualifier("demo2SqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

这就配置完多数据源，以及全都注册到了全局的事务，接下来是测试

1、创建两个数据库，并分别向两个库创建一个表

2、编写mapper

package com.edu.mapper.demo1;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface Demo1Mapper {

    @Insert("insert into demo_user (username,age,sal) values (#{username},#{age},#{sal})")
    int addDemo1(@Param("username") String username,@Param("age") int age,@Param("sal") Double sal);
}

package com.edu.mapper.demo2;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface Demo2Mapper {

    @Insert("insert into demo2_city (city) values (#{city})")
    int addDemo2(@Param("city") String city);
}

3、编写controller类

package com.edu.controller;

import com.edu.mapper.demo1.Demo1Mapper;
import com.edu.mapper.demo2.Demo2Mapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;

@RestController
public class DemoController {

    @Autowired
    private Demo1Mapper demo1Mapper;
    @Autowired
    private Demo2Mapper demo2Mapper;

    @Transactional(rollbackFor = Exception.class)
    @GetMapping("/test1")
    public String test1 (@RequestParam String username, int age, Double sal) {
        return demo1Mapper.addDemo1(username,age,sal)>0?"success":"fail";
    }

    @Transactional(rollbackFor = Exception.class)
    @GetMapping("/test2/{city}")
    public String test2 (@PathVariable("city") String city) {
        String flag = demo2Mapper.addDemo2(city)>0?"success":"fail";
        int a = Integer.valueOf(city);
        return flag;
    }

    @Transactional(rollbackFor = Exception.class)
    @GetMapping("/test3")
    public String test3 (@RequestParam String username, int age, Double sal, String city) {
        int i = demo1Mapper.addDemo1(username, age, sal);
        int j = demo2Mapper.addDemo2(city);
        int a = Integer.valueOf(city);
        return j>0?"success":"fail";
    }
}

经过测试test1、test2、test3报错之后，两个数据库中的两个表插入的数据都回滚了.

--------------------------------------

补充：

如果是整合了mybatis-plus，需要修改sqlSessionFactory为mybatis-plus的MybatisSqlSessionFactoryBean。

并且整合多数据源后，mybatis-plus的分页会出现问题，mybatis-plus的TableLogic逻辑删除注解也会出现问题，需要重新加载分页插件和逻辑删除插件，即DataSourceConfig1和DataSourceConfig2中的sqlSessionFactory应该修改为：(DataSourceConfig2中修改的方法同理)

/**
     * 创建sqlSessionFactory
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "demo1SqlSessionFactory")
    public SqlSessionFactory demo1SqlSessionFactory(@Qualifier("demo1DataSource") DataSource dataSource) throws Exception {
		//逻辑删除 如果值为1,表示已经删除,如果为0,表示未删除
        GlobalConfiguration globalConfig = new GlobalConfiguration();
        globalConfig.setLogicDeleteValue("1");
        globalConfig.setLogicNotDeleteValue("0");
        globalConfig.setSqlInjector(new LogicSqlInjector());
        //分页插件
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.addInterceptor(new PaginationInterceptor());

        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setConfiguration(configuration);
        sqlSessionFactoryBean.setGlobalConfig(globalConfig);
        return sqlSessionFactoryBean.getObject();
    }

如果任使用mybatis的SqlSessionFactoryBean，当调用BaseMapper的方法时，会报Invalid bound statement (not found)异常。

本文GIT源码: https://github.com/cuiwenbo1992/multiple-data-source

醉酒听雨

关注

3
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
springboot整合多数据源配置(包含事务处理)

一、第一种springboot+druid+mybatisplus+jdk8使用注解整合1.1application.yml 配置文件如下server: port: 8080spring: datasource: dynamic: primary: database1 # 配置默认数据库 datasource: database1: # 数据源1配置 url: jdbc:mysql://127.0.0.1:330..
复制链接

扫一扫