【SpringBoot】SpringBoot 与 MyBatis 多数据源配置（二十九）

　　本章介绍SpringBoot 与 MyBatis 多数据源配置，SpringBoot与Mybatis整合内容可以参考【SpringBoot】SpringBoot 与Mybatis整合（十三）

数据库准备

　　1、准备2个数据库，本例以mysql为例

　　　　在第一个数据库test_mysql中，新建表user

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES (1, '张三');
INSERT INTO `user` VALUES (2, '李四');
INSERT INTO `user` VALUES (3, '王五');
COMMIT;

　　　　在第二个数据库test_mysql2中，新建表dog

-- ----------------------------
-- Table structure for dog
-- ----------------------------
DROP TABLE IF EXISTS `dog`;
CREATE TABLE `dog` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `dog_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '狗名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Records of dog
-- ----------------------------
BEGIN;
INSERT INTO `dog` VALUES (1, '旺财');
INSERT INTO `dog` VALUES (2, '二哈');
INSERT INTO `dog` VALUES (3, '大黑');
COMMIT;

项目搭建

　　1、新建一个SpringBoot Web项目

　　　　完成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>

    <groupId>com.test</groupId>
    <artifactId>test-springboot-muldatasource</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.8.RELEASE</version>
    </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>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>

        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.12</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

    </dependencies>


    <!-- SpringBoot打包插件，可以将代码打包成一个可执行的jar包 -->
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

　　　　项目目录：

　　　　　　

　　2、配置文件application.yml

#master 数据源配置
master:
  datasource:
    username: admin
    password: 123456
    # 默认HikariDataSource数据源没有url属性，有的是jdbcUrl属性
    # 写url会报错：jdbcUrl is required with driverClassName
    jdbc-url: jdbc:mysql://127.0.0.1:3306/test_mybatis?allowPublicKeyRetrieval=true&useSSL=true
    # url: jdbc:mysql://127.0.0.1:3306/test_mybatis?allowPublicKeyRetrieval=true&useSSL=true
    driver-class-name: com.mysql.jdbc.Driver

#slave 数据源配置
slave:
  datasource:
    username: admin
    password: 123456
    jdbc-url: jdbc:mysql://127.0.0.1:3306/test_mybatis2?allowPublicKeyRetrieval=true&useSSL=true
    driver-class-name: com.mysql.jdbc.Driver

　　3、数据源配置

　　　　编写2个数据配置类，masterDataSource对应test_mybatis数据库，slaveDataSource对应test_mybatis2数据库，

　　　　MasterDataSourceConfig.java

@Configuration
@MapperScan(basePackages = "com.test.springboot.dao.master",
        sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {


    @Bean(name = "masterDataSource")
    @Primary
    // @ConfigurationProperties给数据源（DataSource）赋值
    @ConfigurationProperties(prefix = "master.datasource")
    public DataSource masterDataSource() {
        DataSource dataSource = DataSourceBuilder.create().build();
        return dataSource;
    }


    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:/mybatis/mapper/master/*Mapper.xml"));
        return bean.getObject();
    }

    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "masterSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

　　　　SlaveDataSourceConfig.java

@Configuration
@MapperScan(basePackages = "com.test.springboot.dao.slave",
        sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class SlaveDataSourceConfig {

    @Value("${slave.datasource.driver-class-name}")
    private String driverClassName;

    @Value("${slave.datasource.jdbc-url}")
    private String url;

    @Value("${slave.datasource.username}")
    private String username;

    @Value("${slave.datasource.password}")
    private String password;

    @Bean(name = "slaveDataSource")
    public DataSource slaveDataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        dataSource.setJdbcUrl(url);
        dataSource.setDriverClassName(driverClassName);
        dataSource.setMaximumPoolSize(10);
        dataSource.setMinimumIdle(5);
        dataSource.setPoolName("slaveDataSourcePool");
        return dataSource;
    }


    @Bean(name = "slaveSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        // 设置Mybatis全局配置路径
        bean.setConfigLocation(new ClassPathResource("mybatis/mybatis-config.xml"));
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath*:/mybatis/mapper/slave/*Mapper.xml"));
        return bean.getObject();
    }

    @Bean(name = "slaveTransactionManager")
    public DataSourceTransactionManager transactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "slaveSqlSessionTemplate")
    public SqlSessionTemplate testSqlSessionTemplate(
            @Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

　　4、Dao层

　　　　UserDao在com.test.springboot.dao.master包下

public interface UserDao {
    List<User> getAll();
}

　　　　DogDao在com.test.springboot.dao.slave包下

public interface DogDao {

    List<Dog> getAll();

    @Select("INSERT INTO dog (dog_name) VALUES (#{dogName})")
    void save(Dog dog);
}

 　　　　Xml文件

　　　　mybatis全局配置文件mybatis-config.xml，路径classpath:mybatis/mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

　　　　UserMapper.xml，文件路径classpath:mybatis/mapper/master/UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.4//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.springboot.dao.master.UserDao">
    <resultMap type="com.test.springboot.bean.User" id="user">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
    </resultMap>

    <!-- 获取所有用户 -->
    <select id="getAll" resultMap="user">
        select * from user
    </select>

</mapper>

　　　　DogMapper.xml，文件路径classpath:mybatis/mapper/slave/DogMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.4//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.test.springboot.dao.slave.DogDao">

    <!-- 获取所有狗 -->
    <select id="getAll" resultType="com.test.springboot.bean.Dog">
        select * from dog
    </select>

</mapper>

　　5、Servcie层

@Service
public class DogService {

    @Autowired
    DogDao dogDao;

    /**
     * 在springboot中已经默认对jpa、jdbc、mybatis开启了事事务，引入它们依赖的时候，事物就默认开启。
     * springboot开启事务很简单，只需要一个注解@Transactional 就可以了。
     * @Transactional可以在在方法上和类上使用。
     * @return
     */
    @Transactional(value = "slaveTransactionManager")
    public Integer save() {

        Dog dog = new Dog();
        dog.setDogName("大黄");
        dogDao.save(dog);

        return 1/0;
    }
}

　　6、Controller层

@RestController
public class TestController {

    @Autowired
    UserDao userDao;

    @Autowired
    DogDao dogDao;

    @Autowired
    DogService dogService;

    @RequestMapping("/users")
    public List<User> users(){
        return userDao.getAll();
    }


    @RequestMapping("/dogs")
    public List<Dog> dogs(){
        return dogDao.getAll();
    }

    @RequestMapping("/dog/save")
    public Integer save(){
        return dogService.save();
    }
 

　　7、实例

　　　　User.java

package com.test.springboot.bean;

public class Dog {
    private Integer id;
    private String dogName;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDogName() {
        return dogName;
    }

    public void setDogName(String dogName) {
        this.dogName = dogName;
    }
}

 

　　　　Dog.java

package com.test.springboot.bean;

public class User {

    private Integer id;
    private String name;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

 

项目测试

　　1、启动项使用 http://localhost:8080/users 地址访问获取所有用户，由此说明masterDataSource能正常使用

　　　　

　　2、使用地址 http://localhost:8080/dogs 访问获取所有狗，由此说明slaveDataSource能正常使用

　　　　

　　3、使用地址 http://localhost:8080/dog/save，新增数据，查看数据库中数据是否增加，未增加，判断说明事务已生效。