本章介绍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文件如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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>
项目目录:
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
Dog.java
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
项目测试
1、启动项使用 http://localhost:8080/users 地址访问获取所有用户,由此说明masterDataSource能正常使用
2、使用地址 http://localhost:8080/dogs 访问获取所有狗,由此说明slaveDataSource能正常使用
3、使用地址 http://localhost:8080/dog/save,新增数据,查看数据库中数据是否增加,未增加,判断说明事务已生效。