SpringBoot+Mybatis多数据源配置，以及加入事物控制

最新推荐文章于 2021-10-18 11:32:29 发布

DayFight_DayUp

最新推荐文章于 2021-10-18 11:32:29 发布

阅读量1.3k

点赞数

分类专栏： # SpringBoot 文章标签： SpringBoot+Mybatis多数据源配置，以及加入事

本文链接：https://blog.csdn.net/a907691592/article/details/97305520

版权

SpringBoot 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

有时候为了让数据分割，不让数据库压力太大，数据会放在两个数据源中（两个逻辑库），但是我又不想为一个数据库搭建一个微服务，这个时候就要让一个Web服务管理两个数据源。SpringBoot中就能配置双数据源，接下来我们就来配置多数据源。

SpringBoot多数据源配置：

有两个数据源就要配置两个DataSource数据源，要使用Mybatis，也要有两个SqlSessionFactory，同时也要有两个SqlSessionTemplate，

配置文件：application.properties

######################################################## test1 database
spring.datasource.test1.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false
spring.datasource.test1.username=root
spring.datasource.test1.password=root
spring.datasource.test1.driver-class-name=com.mysql.cj.jdbc.Driver
#自动提交 默认值true
spring.datasource.test1.auto-commit=true
#此属性控制客户端（即您）将等待来自池的连接的最大毫秒数。 默认值30000（30秒）
spring.datasource.test1.connection-timeout=30000
#此属性控制允许连接在池中闲置的最长时间。 默认值600000(10分钟)，此设置仅适用于minimumIdle定义为小于maximumPoolSize。
spring.datasource.test1.idle-timeout=600000
#此属性控制池中连接的最大生存期。 1800000(30分钟)
spring.datasource.test1.max-lifetime=1800000
#该属性控制HikariCP尝试在池中维护的最小空闲连接数。不推荐使用这个，默认是和maximum-pool-size相等
spring.datasource.test1.minimum-idle=100
#此属性控制池允许达到的最大大小，包括空闲和正在使用的连接。默认值10
spring.datasource.test1.maximum-pool-size=100
#mybatis对应的映射文件路径
spring.datasource.test1.mapper-locations=classpath:mapper1/*.xml
#mybatis对应的实体类
spring.datasource.test1.type-aliases-package=com.wangye.spbootmybatis1.model1


######################################################## test2 database
spring.datasource.test2.url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false
spring.datasource.test2.username=root
spring.datasource.test2.password=root
spring.datasource.test2.driver-class-name=com.mysql.cj.jdbc.Driver
#自动提交 默认值true
spring.datasource.test2.auto-commit=true
#此属性控制客户端（即您）将等待来自池的连接的最大毫秒数。 默认值30000（30秒）
spring.datasource.test2.connection-timeout=30000
#此属性控制允许连接在池中闲置的最长时间。 默认值600000(10分钟)，此设置仅适用于minimumIdle定义为小于maximumPoolSize。
spring.datasource.test2.idle-timeout=600000
#此属性控制池中连接的最大生存期。 1800000(30分钟)
spring.datasource.test2.max-lifetime=1800000
#该属性控制HikariCP尝试在池中维护的最小空闲连接数。不推荐使用这个，默认是和maximum-pool-size相等
spring.datasource.test2.minimum-idle=100
#此属性控制池允许达到的最大大小，包括空闲和正在使用的连接。默认值10
spring.datasource.test2.maximum-pool-size=100
#mybatis对应的映射文件路径
spring.datasource.test2.mapper-locations=classpath:mapper2/*.xml
#mybatis对应的实体类
spring.datasource.test2.type-aliases-package=com.wangye.spbootmybatis1.model2

为了加载这些配置，我们要使用如下配置类：

这是Datasource2的属性值：

@Configuration
@ConfigurationProperties(prefix = "spring.datasource.test1")
public class Datasource1Properties {

    private String url;

    private String username;

    private String password;

    private String driverClassName;

    private Boolean autoCommit;

    private Long connectionTimeout;

    private Long idleTimeout;

    private Long maxLifetime;

    private Integer minimumIdle;

    private Integer maximumPoolSize;

    private String mapperLocations;

    private String typeAliasesPackage;

    get 和 set方法
}

这是Datasource2的属性值：

@Configuration
@ConfigurationProperties(prefix = "spring.datasource.test2")
public class Datasource2Properties {

    private String url;

    private String username;

    private String password;

    private String driverClassName;

    private Boolean autoCommit;

    private Long connectionTimeout;

    private Long idleTimeout;

    private Long maxLifetime;

    private Integer minimumIdle;

    private Integer maximumPoolSize;

    private String mapperLocations;

    private String typeAliasesPackage;

    get 和 set 方法
}

Datasource1的配置和第一个SqlSessionFactory以及第一个SqlSessionTemplate

@Configuration
@MapperScan(basePackages = "com.wangye.spbootmultidatasource.mapper1", sqlSessionFactoryRef = "test1SqlSessionFactory")
public class Datasource1Configuration {

    private Datasource1Properties datasourceProperties;

    @Autowired
    public void setDatasourceProperties(Datasource1Properties datasourceProperties) {
        this.datasourceProperties = datasourceProperties;
    }

    @Bean(name = "test1DataSource")
    @Primary// 表示这个数据源是默认数据源 // 读取application.properties中的配置参数映射成为一个对象
    public DataSource getDateSource1() {
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl(datasourceProperties.getUrl());
        hikariConfig.setUsername(datasourceProperties.getUsername());
        hikariConfig.setPassword(datasourceProperties.getPassword());
        hikariConfig.setDriverClassName(datasourceProperties.getDriverClassName());
        hikariConfig.setAutoCommit(datasourceProperties.getAutoCommit());
        hikariConfig.setConnectionTimeout(datasourceProperties.getConnectionTimeout());
        hikariConfig.setIdleTimeout(datasourceProperties.getIdleTimeout());
        hikariConfig.setMaxLifetime(datasourceProperties.getMaxLifetime());
        hikariConfig.setMinimumIdle(datasourceProperties.getMinimumIdle());
        hikariConfig.setMaximumPoolSize(datasourceProperties.getMaximumPoolSize());
        return new HikariDataSource(hikariConfig);
    }

    @Bean(name = "test1SqlSessionFactory")
    @Primary // 表示这个数据源是默认数据源 // @Qualifier表示查找Spring容器中名字为test1DataSource的对象
    public SqlSessionFactory test1SqlSessionFactory(@Qualifier("test1DataSource") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        // 设置mybatis的xml所在位置
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(datasourceProperties.getMapperLocations()));
        bean.setTypeAliasesPackage(datasourceProperties.getTypeAliasesPackage());
        return bean.getObject();
    }

    @Bean("test1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate test1sqlsessiontemplate(
            @Qualifier("test1SqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }

    /**
     * 为 test1DataSource 配置事务管理器
     * @param test1DataSource test2数据源
     * @return DataSourceTransactionManager 事务管理器
     */
    @Bean // 这里方法名会设置为@Bean的名字
    @Primary
    public PlatformTransactionManager test1TransactionManager(@Qualifier("test1DataSource")DataSource test1DataSource) {
        return new DataSourceTransactionManager(test1DataSource);
    }
}

Datasource2的配置和第一个SqlSessionFactory以及第一个SqlSessionTemplate

@Configuration
@MapperScan(basePackages = "com.wangye.spbootmultidatasource.mapper2", sqlSessionFactoryRef = "test2SqlSessionFactory")
public class Datasource2Configuration {

    private Datasource2Properties datasourceProperties;

    @Autowired
    public void setDatasourceProperties(Datasource2Properties datasourceProperties) {
        this.datasourceProperties = datasourceProperties;
    }

    @Bean(name = "test2DataSource")// 读取application.properties中的配置参数映射成为一个对象
    public DataSource getDateSource2() {
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl(datasourceProperties.getUrl());
        hikariConfig.setUsername(datasourceProperties.getUsername());
        hikariConfig.setPassword(datasourceProperties.getPassword());
        hikariConfig.setDriverClassName(datasourceProperties.getDriverClassName());
        hikariConfig.setAutoCommit(datasourceProperties.getAutoCommit());
        hikariConfig.setConnectionTimeout(datasourceProperties.getConnectionTimeout());
        hikariConfig.setIdleTimeout(datasourceProperties.getIdleTimeout());
        hikariConfig.setMaxLifetime(datasourceProperties.getMaxLifetime());
        hikariConfig.setMinimumIdle(datasourceProperties.getMinimumIdle());
        hikariConfig.setMaximumPoolSize(datasourceProperties.getMaximumPoolSize());
        return new HikariDataSource(hikariConfig);
    }

    @Bean(name = "test2SqlSessionFactory")// @Qualifier表示查找Spring容器中名字为test1DataSource的对象
    public SqlSessionFactory test2SqlSessionFactory(@Qualifier("test2DataSource") DataSource datasource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(datasource);
        // 设置mybatis的xml所在位置
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(datasourceProperties.getMapperLocations()));
        bean.setTypeAliasesPackage(datasourceProperties.getTypeAliasesPackage());
        return bean.getObject();
    }

    @Bean("test2SqlSessionTemplate")
    public SqlSessionTemplate test2sqlsessiontemplate(@Qualifier("test2SqlSessionFactory") SqlSessionFactory sessionfactory) {
        return new SqlSessionTemplate(sessionfactory);
    }

    /**
     * 为test2DataSource配置事务管理器
     * @param test2DataSource test2DataSource数据源
     * @return PlatformTransactionManager事务管理器
     */
    @Bean // 这里方法名会设置为@Bean的名字
    public PlatformTransactionManager test2TransactionManager(@Qualifier("test2DataSource")DataSource test2DataSource) {
        return new DataSourceTransactionManager(test2DataSource);
    }

}

接下来就看看项目目录结构吧：

接下来就来测试一下：

UserMapper1.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.wangye.spbootmultidatasource.mapper1.UserMapper1">

    <select id="getUserById1" resultType="com.wangye.spbootmultidatasource.model1.User1">
        select
            id as id,
            name as name,
            age as age
        from
            user
        where
            id = #{id}
    </select>

    <insert id="addUser" parameterType="com.wangye.spbootmultidatasource.model1.User1">
        insert into
            user
        (name,age)
        values (
        #{name},#{age}
        )
    </insert>

</mapper>

UserMapper2.xml:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.wangye.spbootmultidatasource.mapper2.UserMapper2">

    <select id="getUserById1" resultType="com.wangye.spbootmultidatasource.model2.User2">
        select
            id as id,
            name as name,
            age as age
        from
            user
        where
            id = #{id}
    </select>

    <insert id="addUser" parameterType="com.wangye.spbootmultidatasource.model2.User2">
        insert into
            user
        (name,age)
        values (
        #{name},#{age}
        )
    </insert>
</mapper>

UserMapper1.java

@Repository
public interface UserMapper1 {

    User1 getUserById1(@Param("id") Long id);

    @Select({"<script>",
            "select",
            "   id as id,",
            "   name as name,",
            "   age as age",
            "from",
            "   user",
            "<where>",
            "   <if test ='id != null'>",
            "   and",
            "       id = #{id}",
            "   </if>",
            "</where>",
            "</script>"})
    User1 getUserById(@Param("id") Long id);

    void addUser(User1 user1);
}

UserMapper2.java:

@Repository
public interface UserMapper2 {

    User2 getUserById1(@Param("id") Long id);

    @Select({"<script>",
            "select",
            "   id as id,",
            "   name as name,",
            "   age as age",
            "from",
            "   user",
            "<where>",
            "   <if test ='id != null'>",
            "   and",
            "       id = #{id}",
            "   </if>",
            "</where>",
            "</script>"})
    User2 getUserById(@Param("id") Long id);

    void addUser(User2 user2);
}

User1.java:

public class User1 {

    private Long id;

    private String name;

    private Integer age;

    set 和 get 方法
}

User2.java：

public class User2 {

    private Long id;

    private String name;

    private Integer age;

    set 和 get 方法
}

UserService.java:

@Service
public class UserService {

    private UserMapper1 userMapper1;

    @Autowired
    public void setUserMapper1(UserMapper1 userMapper1) {
        this.userMapper1 = userMapper1;
    }

    @Autowired
    public void setUserMapper2(UserMapper2 userMapper2) {
        this.userMapper2 = userMapper2;
    }

    private UserMapper2 userMapper2;

    public String addUser(){
        User1 user1 = new User1();
        user1.setName("小四");
        user1.setAge(15);
        userMapper1.addUser(user1);

        User2 user2 = new User2();
        user2.setName("小屋");
        user2.setAge(16);
        userMapper2.addUser(user2);
        return "success";
    }
    
}

UserController.java

@RestController
public class UserController {

    private UserService userService;

    @Autowired
    public void setUserService(UserService userService) {
        this.userService = userService;
    }

    @RequestMapping("/addUser")
    public String addUser(){
        return userService.addUser();
    }
}

对了还没有贴数据库，贴一下：

接下来所有的事情都做完了，那么就来测试一下接口：

localhost:8080/addUser。

发现会返回success

多数据源下事物管理：

事物管理默认管理的是@Primary修饰的Datasource，也就是我们的test1DataSource。如果要切换事物管理器，使用@Transactional的时候指定value值：下面两个方法抛出异常了，所以数据不会添加成功。

    @Transactional(value = "test1TransactionManager")
    public String adUserTransaction1(){
        User1 user1 = new User1();
        user1.setName("小四");
        user1.setAge(15);
        userMapper1.addUser(user1);
        int i = 1 / 0;
        return "success";
    }

    @Transactional(value = "test2TransactionManager")
    public String adUserTransaction2(){
        User2 user2 = new User2();
        user2.setName("小留");
        user2.setAge(16);
        userMapper2.addUser(user2);
        int i = 1 / 0;
        return "success";
    }

向上面这样就能为单个数据源控制事物。

注意下面代码：下面代码虽然不能插入成功，是因为@Transactional默认的value值的@Primary注解修饰的数据源事物管理器，代码在插入第二个数据源的时候就已经异常了，不会往下面执行，所以回滚的是test1DataSource的事务

    @Transactional
    public String addUserToTwoDataSource1(){
        User1 user1 = new User1();
        user1.setName("小四");
        user1.setAge(15);
        userMapper1.addUser(user1);
        int i = 1 / 0 ;
        User2 user2 = new User2();
        user2.setName("小屋");
        user2.setAge(16);
        userMapper2.addUser(user2);
        return "success";
    }

再看看下面的代码：下面会往test2DataSource中插入数据，不会向test1DataSource中插入，因为在插入test1DataSource前就已经抛异常了，代码不会往下执行了。所以数据库会往test2DataSource插入一条数据。

    @Transactional
    public String addUserToTwoDataSource2(){
        User2 user2 = new User2();
        user2.setName("小屋");
        user2.setAge(16);
        userMapper2.addUser(user2);
        int i = 1 / 0 ;

        User1 user1 = new User1();
        user1.setName("小四");
        user1.setAge(15);
        userMapper1.addUser(user1);
        return "success";
    }

接下来在看看下面两个方法的代码：上面已经说过了，@Transactional默认的@Value值是@Primary修饰的事务管理器，所以下面两个方法都会往test2DataSource中插入一条数据，但是test1DataSource中会回滚，不插如任何数据

    @Transactional
    public String addUserToTwoDataSource3(){
        User2 user2 = new User2();
        user2.setName("小屋");
        user2.setAge(16);
        userMapper2.addUser(user2);


        User1 user1 = new User1();
        user1.setName("小四");
        user1.setAge(15);
        userMapper1.addUser(user1);
        int i = 1 / 0 ;
        return "success";
    }

    @Transactional
    public String addUserToTwoDataSource4(){
        User1 user1 = new User1();
        user1.setName("小四");
        user1.setAge(15);
        userMapper1.addUser(user1);

        User2 user2 = new User2();
        user2.setName("小屋");
        user2.setAge(16);
        userMapper2.addUser(user2);
        int i = 1 / 0 ;
        return "success";
    }

那么怎么实现一个@Transactional控制两个数据源呢，答案是不行，因为这个是普通的事物管理器，如果是实现了XA协议，并且实现了两段提交的，三段提交的，就能一个@Transactional控制两个或者三个数据源提交事物。MySQL是支持XA协议的。