sprng boot多个sqlserver数据库或者mysql数据库数据源配置及事物处理

你的名字 2022-01-06 05:19 279阅读 0赞

**1.新建spring boot项目，sqlserver数据库的话，依赖可能无法下载，需要手动下载并且引入的maven项目中，其他数据库类型，直接在pom中引入即可。**

**2.项目结构**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70]

**3.配置文件application.properties：**

server.port=8894
    #主库的配置
    spring.datasource.config.jdbc-url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=config
    spring.datasource.config.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
    spring.datasource.config.username=sa
    spring.datasource.config.password=123456
    #次库
    spring.datasource.factory.jdbc-url=jdbc:sqlserver://127.0.0.1:1433;DatabaseName=factory
    spring.datasource.factory.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
    spring.datasource.factory.username=sa
    spring.datasource.factory.password=123456

**4.主库和从库配置说明**

package com.sheng.multipledatasources.config;
    
    import javax.sql.DataSource;
    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;
    
    /**
     * @Author: Mr sheng.z
     * @Description:
     * @Date: Create in 14:25 2019/6/21
     */
    @Configuration//注册到springboot容器
    //扫描包需要执行的数据源
    @MapperScan(basePackages = {"com.sheng.multipledatasources.configdao"}, sqlSessionFactoryRef = "configSqlSessionFactory")
    public class DataSourceConfig {
        //注入到这个容器
        @Bean(name = "configDataSource")
        /**
         *  表示取application.properties配置文件中的前缀
         */
        @ConfigurationProperties(prefix = "spring.datasource.config")
        @Primary
        public DataSource DataSource() {
            return DataSourceBuilder.create().build();
        }
    
    
        @Bean(name = "configSqlSessionFactory")
        @Primary
        public SqlSessionFactory configSqlSessionFactory(@Qualifier("configDataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:configMapper/*.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "configTransactionManager")//配置事务
        @Primary
        public DataSourceTransactionManager configTransactionManager(@Qualifier("configDataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "configSqlSessionTemplate")
        @Primary
        public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("configSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    
    
    }

**5.次数据源配置文件**

package com.sheng.multipledatasources.config;
    
    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 javax.sql.DataSource;
    
    /**
     * @Author: Mr sheng.z
     * @Description:
     * @Date: Create in 14:25 2019/6/21
     */
    @Configuration//注册到springboot容器
    @MapperScan(basePackages = {"com.sheng.multipledatasources.factorydao"}, sqlSessionFactoryRef = "factorySqlSessionFactory")
    public class DataSourceFactory {
        //注入到这个容器
        @Bean(name = "factoryDataSource")
        /**
         *  表示取application.properties配置文件中的前缀
         */
        @ConfigurationProperties(prefix = "spring.datasource.factory")
        public DataSource DataSource() {
            return DataSourceBuilder.create().build();
        }
    
    
        @Bean(name = "factorySqlSessionFactory")
        public SqlSessionFactory configSqlSessionFactory(@Qualifier("factoryDataSource") DataSource dataSource) throws Exception {
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:factoryMapper/*.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "factorySqlSessionFactory")//配置事务
        public DataSourceTransactionManager configTransactionManager(@Qualifier("factoryDataSource") DataSource dataSource) {
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "factorySqlSessionTemplate")
        public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("factorySqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    
    
    }

**6.pom文件配置**

<?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>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.1.5.RELEASE</version>
            <relativePath/> 
        </parent>
        <groupId>com.sheng</groupId>
        <artifactId>multipledatasources</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>multipledatasources</name>
        <description>multipledatasources project for Spring Boot</description>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>com.microsoft.sqlserver</groupId>
                <artifactId>sqljdbc4</artifactId>
                <version>4.0</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.1</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
            <resources>
                <resource>
                    <directory>src/main/java</directory>
                    <includes>**/*.yml</includes>
                    <includes>**/*.xml</includes>
                </resource>
                <resource>
                    <directory>src/main/resource</directory>
                </resource>
            </resources>
        </build>
    </project>

**7.测试用的controller**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70 1]  
**7.实现类UserServersImpl**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70 2]

**8.两个dao**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70 4]

**9.两个xml**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70 6]

**10.这里面主要是两个数据源的config配置文件（DataSourceConfig，DataSourceFactory），然后xml里面需要和dao层接口方法名必须一致，其他的都是我们平常写的三层架构。这里需要注意的是，因为我们使用的是多数据源，我们自己在application.properties里面指定mybatis的xml扫描路径是不会生效的，我们需要自己在创建数据源的java配置文件时候，指定一下这个数据源的扫描路径即可，包括事物也是，需要手动指定。因为我们没有使用springboot的默认数据源配置，需要手动处理。  
我上面的事物处理，不同方法之间，只用指定采用哪个事物即可，事物是会生效的。但是如果是在同一个方法内，同时操作两个或两个及以上数据库，出现异常以后，只有指定的数据源异常数据会被回滚，没有指定的，不会回滚。 此时 我这种事物就会失效，不适用统一方法内的多个数据源。 如果想要多个数据源同时回滚，需要采用分布式事物处理。详情，请见我下一篇博客 多数据源下面的分布式事务处理**

**11.源码位置，可以下载源码到本地进行测试**  
[https://download.csdn.net/download/z\_\_sheng/11256362][https_download.csdn.net_download_z_sheng_11256362]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70]: /images/20211227/854dd380d0484af398a6df8355eb8cfa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70 1]: /images/20211227/56c56b243bb348fca5efeaf878d124ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70 2]: /images/20211227/d160d3ccae6d4c409551762d9c3ef01b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70 3]: /images/20211227/73ec39f7a5b54e008757d07acd45e110.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70 4]: /images/20211227/322b7acd8b2749768db3f895098e4a32.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70 5]: /images/20211227/ebf8e125a1954c9cac6bead3e6247ac8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1pfX1NoZW5n_size_16_color_FFFFFF_t_70 6]: /images/20211227/069571a4c7654d9a9e2c5620b1dcaa6e.png
[https_download.csdn.net_download_z_sheng_11256362]: https://download.csdn.net/download/z__sheng/11256362