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

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

数据库准备

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

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

 1 -- ----------------------------
 2 -- Table structure for user
 3 -- ----------------------------
 4 DROP TABLE IF EXISTS `user`;
 5 CREATE TABLE `user` (
 6   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 7   `name` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '名称',
 8   PRIMARY KEY (`id`)
 9 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
10 
11 -- ----------------------------
12 -- Records of user
13 -- ----------------------------
14 BEGIN;
15 INSERT INTO `user` VALUES (1, '张三');
16 INSERT INTO `user` VALUES (2, '李四');
17 INSERT INTO `user` VALUES (3, '王五');
18 COMMIT;

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

 1 -- ----------------------------
 2 -- Table structure for dog
 3 -- ----------------------------
 4 DROP TABLE IF EXISTS `dog`;
 5 CREATE TABLE `dog` (
 6   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 7   `dog_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '狗名',
 8   PRIMARY KEY (`id`)
 9 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
10 
11 -- ----------------------------
12 -- Records of dog
13 -- ----------------------------
14 BEGIN;
15 INSERT INTO `dog` VALUES (1, '旺财');
16 INSERT INTO `dog` VALUES (2, '二哈');
17 INSERT INTO `dog` VALUES (3, '大黑');
18 COMMIT;

项目搭建

　　1、新建一个SpringBoot Web项目

　　　　完成pom文件如下：

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <project xmlns="http://maven.apache.org/POM/4.0.0"
 3          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 4          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 5     <modelVersion>4.0.0</modelVersion>
 6 
 7     <groupId>com.test</groupId>
 8     <artifactId>test-springboot-muldatasource</artifactId>
 9     <version>1.0-SNAPSHOT</version>
10 
11     <parent>
12         <groupId>org.springframework.boot</groupId>
13         <artifactId>spring-boot-starter-parent</artifactId>
14         <version>2.1.8.RELEASE</version>
15     </parent>
16 
17     <properties>
18 
19         <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
20         <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
21         <java.version>1.8</java.version>
22     </properties>
23 
24     <dependencies>
25 
26         <dependency>
27             <groupId>org.springframework.boot</groupId>
28             <artifactId>spring-boot-starter-web</artifactId>
29         </dependency>
30 
31         <dependency>
32             <groupId>org.mybatis.spring.boot</groupId>
33             <artifactId>mybatis-spring-boot-starter</artifactId>
34             <version>2.0.1</version>
35         </dependency>
36 
37         <!-- mysql -->
38         <dependency>
39             <groupId>mysql</groupId>
40             <artifactId>mysql-connector-java</artifactId>
41             <version>8.0.12</version>
42         </dependency>
43 
44         <dependency>
45             <groupId>org.springframework.boot</groupId>
46             <artifactId>spring-boot-starter-test</artifactId>
47             <scope>test</scope>
48         </dependency>
49 
50     </dependencies>
51 
52 
53     <!-- SpringBoot打包插件，可以将代码打包成一个可执行的jar包 -->
54     <build>
55         <plugins>
56             <plugin>
57                 <groupId>org.springframework.boot</groupId>
58                 <artifactId>spring-boot-maven-plugin</artifactId>
59             </plugin>
60         </plugins>
61     </build>
62 
63 </project>

pom.xml

　　　　项目目录：

　　2、配置文件application.yml

 1 #master 数据源配置
 2 master:
 3   datasource:
 4     username: admin
 5     password: 123456
 6     # 默认HikariDataSource数据源没有url属性，有的是jdbcUrl属性
 7     # 写url会报错：jdbcUrl is required with driverClassName
 8     jdbc-url: jdbc:mysql://127.0.0.1:3306/test_mybatis?allowPublicKeyRetrieval=true&useSSL=true
 9     # url: jdbc:mysql://127.0.0.1:3306/test_mybatis?allowPublicKeyRetrieval=true&useSSL=true
10     driver-class-name: com.mysql.jdbc.Driver
11 
12 #slave 数据源配置
13 slave:
14   datasource:
15     username: admin
16     password: 123456
17     jdbc-url: jdbc:mysql://127.0.0.1:3306/test_mybatis2?allowPublicKeyRetrieval=true&useSSL=true
18     driver-class-name: com.mysql.jdbc.Driver

　　3、数据源配置

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

　　　　MasterDataSourceConfig.java

 1 @Configuration
 2 @MapperScan(basePackages = "com.test.springboot.dao.master",
 3         sqlSessionFactoryRef = "masterSqlSessionFactory")
 4 public class MasterDataSourceConfig {
 5 
 6 
 7     @Bean(name = "masterDataSource")
 8     @Primary
 9     // @ConfigurationProperties给数据源（DataSource）赋值
10     @ConfigurationProperties(prefix = "master.datasource")
11     public DataSource masterDataSource() {
12         DataSource dataSource = DataSourceBuilder.create().build();
13         return dataSource;
14     }
15 
16 
17     @Bean(name = "masterSqlSessionFactory")
18     @Primary
19     public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
20         SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
21         bean.setDataSource(dataSource);
22         bean.setMapperLocations(
23                 new PathMatchingResourcePatternResolver().getResources("classpath*:/mybatis/mapper/master/*Mapper.xml"));
24         return bean.getObject();
25     }
26 
27     @Bean(name = "masterTransactionManager")
28     @Primary
29     public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
30         return new DataSourceTransactionManager(dataSource);
31     }
32 
33     @Bean(name = "masterSqlSessionTemplate")
34     @Primary
35     public SqlSessionTemplate testSqlSessionTemplate(
36             @Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
37         return new SqlSessionTemplate(sqlSessionFactory);
38     }
39 }

　　　　SlaveDataSourceConfig.java

 1 @Configuration
 2 @MapperScan(basePackages = "com.test.springboot.dao.slave",
 3         sqlSessionFactoryRef = "slaveSqlSessionFactory")
 4 public class SlaveDataSourceConfig {
 5 
 6     @Value("${slave.datasource.driver-class-name}")
 7     private String driverClassName;
 8 
 9     @Value("${slave.datasource.jdbc-url}")
10     private String url;
11 
12     @Value("${slave.datasource.username}")
13     private String username;
14 
15     @Value("${slave.datasource.password}")
16     private String password;
17 
18     @Bean(name = "slaveDataSource")
19     public DataSource slaveDataSource() {
20         HikariDataSource dataSource = new HikariDataSource();
21         dataSource.setUsername(username);
22         dataSource.setPassword(password);
23         dataSource.setJdbcUrl(url);
24         dataSource.setDriverClassName(driverClassName);
25         dataSource.setMaximumPoolSize(10);
26         dataSource.setMinimumIdle(5);
27         dataSource.setPoolName("slaveDataSourcePool");
28         return dataSource;
29     }
30 
31 
32     @Bean(name = "slaveSqlSessionFactory")
33     public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {
34         SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
35         bean.setDataSource(dataSource);
36         // 设置Mybatis全局配置路径
37         bean.setConfigLocation(new ClassPathResource("mybatis/mybatis-config.xml"));
38         bean.setMapperLocations(
39                 new PathMatchingResourcePatternResolver().getResources("classpath*:/mybatis/mapper/slave/*Mapper.xml"));
40         return bean.getObject();
41     }
42 
43     @Bean(name = "slaveTransactionManager")
44     public DataSourceTransactionManager transactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {
45         return new DataSourceTransactionManager(dataSource);
46     }
47 
48     @Bean(name = "slaveSqlSessionTemplate")
49     public SqlSessionTemplate testSqlSessionTemplate(
50             @Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
51         return new SqlSessionTemplate(sqlSessionFactory);
52     }
53 }

　　4、Dao层

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

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

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

1 public interface DogDao {
2 
3     List<Dog> getAll();
4 
5     @Select("INSERT INTO dog (dog_name) VALUES (#{dogName})")
6     void save(Dog dog);
7 }

　　　　Xml文件

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

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration
 3         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4         "http://mybatis.org/dtd/mybatis-3-config.dtd">
 5 <configuration>
 6 
 7     <settings>
 8         <setting name="mapUnderscoreToCamelCase" value="true"/>
 9     </settings>
10 </configuration>

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

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.4//EN"
 3         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4 <mapper namespace="com.test.springboot.dao.master.UserDao">
 5     <resultMap type="com.test.springboot.bean.User" id="user">
 6         <id property="id" column="id"/>
 7         <result property="name" column="name"/>
 8     </resultMap>
 9 
10     <!-- 获取所有用户 -->
11     <select id="getAll" resultMap="user">
12         select * from user
13     </select>
14 
15 </mapper>

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

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.4//EN"
 3         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 4 <mapper namespace="com.test.springboot.dao.slave.DogDao">
 5 
 6     <!-- 获取所有狗 -->
 7     <select id="getAll" resultType="com.test.springboot.bean.Dog">
 8         select * from dog
 9     </select>
10 
11 </mapper>

　　5、Servcie层

 1 @Service
 2 public class DogService {
 3 
 4     @Autowired
 5     DogDao dogDao;
 6 
 7     /**
 8      * 在springboot中已经默认对jpa、jdbc、mybatis开启了事事务，引入它们依赖的时候，事物就默认开启。
 9      * springboot开启事务很简单，只需要一个注解@Transactional 就可以了。
10      * @Transactional可以在在方法上和类上使用。
11      * @return
12      */
13     @Transactional(value = "slaveTransactionManager")
14     public Integer save() {
15 
16         Dog dog = new Dog();
17         dog.setDogName("大黄");
18         dogDao.save(dog);
19 
20         return 1/0;
21     }
22 }

　　6、Controller层

 1 @RestController
 2 public class TestController {
 3 
 4     @Autowired
 5     UserDao userDao;
 6 
 7     @Autowired
 8     DogDao dogDao;
 9 
10     @Autowired
11     DogService dogService;
12 
13     @RequestMapping("/users")
14     public List<User> users(){
15         return userDao.getAll();
16     }
17 
18 
19     @RequestMapping("/dogs")
20     public List<Dog> dogs(){
21         return dogDao.getAll();
22     }
23 
24     @RequestMapping("/dog/save")
25     public Integer save(){
26         return dogService.save();
27     }
28

　　7、实例

　　　　User.java

 1 package com.test.springboot.bean;
 2 
 3 public class Dog {
 4     private Integer id;
 5     private String dogName;
 6 
 7     public Integer getId() {
 8         return id;
 9     }
10 
11     public void setId(Integer id) {
12         this.id = id;
13     }
14 
15     public String getDogName() {
16         return dogName;
17     }
18 
19     public void setDogName(String dogName) {
20         this.dogName = dogName;
21     }
22 }

View Code

　　　　Dog.java

 1 package com.test.springboot.bean;
 2 
 3 public class User {
 4 
 5     private Integer id;
 6     private String name;
 7 
 8     public Integer getId() {
 9         return id;
10     }
11 
12     public void setId(Integer id) {
13         this.id = id;
14     }
15 
16     public String getName() {
17         return name;
18     }
19 
20     public void setName(String name) {
21         this.name = name;
22     }
23 }

View Code

项目测试

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

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

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

posted on 2020-03-19 00:48 H__D 阅读(2039) 评论(1) 编辑收藏举报

刷新页面返回顶部


Copyright © 2024 H__D Powered by .NET 9.0 on Kubernetes 博客园

导航

数据库准备

项目搭建

项目测试