Spring Boot MyBatis 資料庫集群訪問實現
- 2019 年 10 月 3 日
- 筆記
Spring Boot MyBatis 資料庫集群訪問實現
本示例主要介紹了Spring Boot程式方式實現資料庫集群訪問，讀庫輪詢方式實現負載均衡。閱讀本示例前，建議你有AOP編程基礎、mybatis基本功能會使用、資料庫集群基本概念，這樣你可以更快的理解和實現它
MySql主從配置
關於配置請參考《MySQL主從複製配置》
Spring Boot實現方式
讀寫分離要做的事情就是對於一條SQL該選擇哪個資料庫去執行，至於誰來做選擇資料庫這件事兒，一般來講，主要有兩種實現方式，分別為：
- 1.使用中間件，比如Atlas，cobar，TDDL，mycat，heisenberg，Oceanus，vitess，OneProxy等
- 2.使用程式自己實現，利用Spring Boot提供的路由數據源以及AOP，實現起來簡單快捷（本文要介紹的方法）
程式程式碼實現
1.首先我們配置下pom.xml，添加示例必要的依賴
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.0</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> </dependencies>2.數據源路由類功能RoutingDataSource.java
基於特定的key路由到特定的數據源。它內部維護了一組目標數據源，並且做了路由key與目標數據源之間的映射，提供基於key查找數據源的方法。
a.類關係圖
b.程式碼很簡單,調用下DBContext的get方法就可以了
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; public class RoutingDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DBContext.get(); } }3.數據源上下文類DBContext.java
import com.easy.mybatis.multidatasource.enums.DBTypeEnum; import lombok.extern.slf4j.Slf4j; import java.util.concurrent.atomic.AtomicInteger; @Slf4j public class DBContext { private static final ThreadLocal<DBTypeEnum> dbContext = new ThreadLocal<>(); private static final AtomicInteger counter = new AtomicInteger(-1); public static void set(DBTypeEnum dbType) { dbContext.set(dbType); } public static DBTypeEnum get() { return dbContext.get(); } public static void master() { set(DBTypeEnum.MASTER); log.info("切換到master庫"); } public static void slave() { // 讀庫負載均衡(輪詢方式) int index = counter.getAndIncrement() % 2; log.info("slave庫訪問執行緒數==>{}", counter.get()); if (index == 0) { set(DBTypeEnum.SLAVE1); log.info("切換到slave1庫"); } else { set(DBTypeEnum.SLAVE2); log.info("切換到slave2庫"); } } } 4.資料庫枚舉類DBTypeEnum.java
public enum DBTypeEnum { MASTER, SLAVE1, SLAVE2 }這裡我們配置三個庫，分別是一個寫庫Master，2個讀庫slave1,slave2
5.資料庫配置類DataSourceConfig.java
package com.easy.mybatis.multidatasource.config; import com.easy.mybatis.multidatasource.enums.DBTypeEnum; 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 javax.sql.DataSource; import java.util.HashMap; import java.util.Map; @Configuration public class DataSourceConfig { @Bean @ConfigurationProperties("spring.datasource.master") public DataSource masterDataSource() { return DataSourceBuilder.create().build(); } @Bean @ConfigurationProperties("spring.datasource.slave1") public DataSource slave1DataSource() { return DataSourceBuilder.create().build(); } @Bean @ConfigurationProperties("spring.datasource.slave2") public DataSource slave2DataSource() { return DataSourceBuilder.create().build(); } @Bean public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource, @Qualifier("slave1DataSource") DataSource slave1DataSource, @Qualifier("slave2DataSource") DataSource slave2DataSource) { Map<Object, Object> targetDataSources = new HashMap<>(); targetDataSources.put(DBTypeEnum.MASTER, masterDataSource); targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource); targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource); RoutingDataSource routingDataSource = new RoutingDataSource(); routingDataSource.setDefaultTargetDataSource(masterDataSource); routingDataSource.setTargetDataSources(targetDataSources); return routingDataSource; } } 6.mybatis配置類DataSourceConfig.java
package com.easy.mybatis.multidatasource.config; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; import org.mybatis.spring.annotation.MapperScan; 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 org.springframework.transaction.PlatformTransactionManager; import org.springframework.transaction.annotation.EnableTransactionManagement; import javax.annotation.Resource; import javax.sql.DataSource; @EnableTransactionManagement @Configuration @MapperScan("com.easy.mybatis.multidatasource.mapper") public class MyBatisConfig { @Resource(name = "myRoutingDataSource") private DataSource myRoutingDataSource; @Bean public SqlSessionFactory sqlSessionFactory() throws Exception { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setDataSource(myRoutingDataSource); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml")); return sqlSessionFactoryBean.getObject(); } @Bean public PlatformTransactionManager platformTransactionManager() { return new DataSourceTransactionManager(myRoutingDataSource); } }7.切面類DataSourceAop.java
package com.easy.mybatis.multidatasource.config; import org.aspectj.lang.annotation.Aspect; import org.aspectj.lang.annotation.Before; import org.aspectj.lang.annotation.Pointcut; import org.springframework.stereotype.Component; @Aspect @Component public class DataSourceAop { @Pointcut("@annotation(com.easy.mybatis.multidatasource.annotation.Master) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.insert*(..)) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.add*(..)) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.update*(..)) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.edit*(..)) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.delete*(..)) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.remove*(..))") public void writePointcut() { } @Pointcut("!@annotation(com.easy.mybatis.multidatasource.annotation.Master) " + "&& (execution(* com.easy.mybatis.multidatasource.service..*.select*(..)) " + "|| execution(* com.easy.mybatis.multidatasource.service..*.get*(..)))") public void readPointcut() { } @Before("writePointcut()") public void write() { DBContext.master(); } @Before("readPointcut()") public void read() { DBContext.slave(); } }8.註解類Master.java
package com.easy.mybatis.multidatasource.annotation; /** * 主庫，可讀寫 */ public @interface Master { }9.用戶的xml,mapper,service類
UserMapper.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.easy.mybatis.multidatasource.mapper.UserMapper"> <select id="selectById" resultType="com.easy.mybatis.multidatasource.entity.User" parameterType="int"> SELECT * from user WHERE id = #{id} </select> <select id="selectList" resultType="com.easy.mybatis.multidatasource.entity.User"> SELECT * from user </select> <insert id="insert" parameterType="com.easy.mybatis.multidatasource.entity.User"> INSERT into user(id,name,age,email) VALUES(#{id}, #{name},#{age},#{email}) </insert> <update id="updateById" parameterType="com.easy.mybatis.multidatasource.entity.User"> UPDATE user SET name =#{name}, age =#{age},email =#{email} WHERE id =#{id} </update> <delete id="deleteById" parameterType="int"> DELETE FROM user WHERE id =#{id} </delete> </mapper> UserMapper.java
package com.easy.mybatis.multidatasource.mapper; import com.easy.mybatis.multidatasource.entity.User; import org.springframework.stereotype.Repository; import java.io.Serializable; import java.util.List; @Repository public interface UserMapper { /** * 插入一條記錄 * * @param entity 實體對象 */ int insert(User entity); /** * 根據 ID 刪除 * * @param id 主鍵ID */ int deleteById(Serializable id); /** * 根據 ID 修改 * * @param entity 實體對象 */ int updateById(User entity); /** * 根據 ID 查詢 * * @param id 主鍵ID */ User selectById(Serializable id); List<User> selectList(); }UserServiceImpl.java
package com.easy.mybatis.multidatasource.service.impl; import com.easy.mybatis.multidatasource.annotation.Master; import com.easy.mybatis.multidatasource.entity.User; import com.easy.mybatis.multidatasource.mapper.UserMapper; import com.easy.mybatis.multidatasource.service.IUserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.io.Serializable; import java.util.List; @Service public class UserServiceImpl implements IUserService { @Autowired private UserMapper userMapper; /** * 插入一條記錄 * * @param entity 實體對象 */ @Override public int insert(User entity) { return userMapper.insert(entity); } /** * 根據 ID 刪除 * * @param id 主鍵ID */ @Override public int deleteById(Serializable id) { return userMapper.deleteById(id); } /** * 根據 ID 修改 * * @param entity 實體對象 */ @Override public int updateById(User entity) { return userMapper.updateById(entity); } /** * 根據 ID 查詢 * * @param id 主鍵ID */ @Master @Override public User selectById(Serializable id) { return userMapper.selectById(id); } @Override public List<User> selectList() { return userMapper.selectList(); } }這裡我們注意到，本來selectById應該訪問的是讀庫（slave庫），我這裡通過註解的方式，手動把它指到了可寫庫（master庫）
10.最後我貼上yaml配置文件application.yml
# DataSource Config spring: datasource: master: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/easy_web?useSSL=false&serverTimezone=UTC username: root password: 123456 slave1: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/easy_web?useSSL=false&serverTimezone=UTC username: root password: 123456 slave2: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://localhost:3306/easy_web?useSSL=false&serverTimezone=UTC username: root password: 123456該配置文件配置了三個數據源（這裡我為了方便，把三個數據源指到了同個庫，實際生產環境會有不同的庫和讀寫用戶）
編寫測試用例，查看執行結果，分析資料庫調用情況
1.單元測試類MultiDataSourceServiceTest.java
package com.easy.mybatis.multidatasource; import com.easy.mybatis.multidatasource.entity.User; import com.easy.mybatis.multidatasource.service.IUserService; import lombok.extern.slf4j.Slf4j; import org.junit.FixMethodOrder; import org.junit.Test; import org.junit.runner.RunWith; import org.junit.runners.MethodSorters; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import javax.annotation.Resource; import java.util.List; import static org.assertj.core.api.Assertions.assertThat; /** * <p> * 內置 CRUD 演示 * </p> */ @RunWith(SpringRunner.class) @SpringBootTest @Slf4j //指定單元測試按字母順序執行 @FixMethodOrder(value = MethodSorters.NAME_ASCENDING) public class MultiDataSourceServiceTest { @Resource private IUserService userService; @Test public void aInsert() { User user = new User(); user.setId(20l); user.setName("小羊"); user.setAge(3); user.setEmail("[email protected]"); log.info("開始執行insert方法，id={}", user.getId()); assertThat(userService.insert(user)); // 成功直接拿會寫的 ID assertThat(user.getId()).isNotNull(); } @Test public void bUpdate() { User user = new User(); user.setId(20l); user.setName("小羊update"); user.setAge(3); user.setEmail("[email protected]"); log.info("開始執行updateById方法，id={}", user.getId()); assertThat(userService.updateById(user) > 0); } @Test public void cSelectById() { int id = 20; log.info("開始執行selectById方法，id={}", id); log.info("數據為=={}", userService.selectById(id)); } @Test public void dDelete() { int id = 20; log.info("開始執行deleteById方法，id={}", id); assertThat(userService.deleteById(id)); } @Test public void eSelectList() { for (int i = 0; i < 5; i++) { log.info("開始執行selectList方法，index={}", i); List<User> list = userService.selectList(); log.info("查詢到的數據為，list={}", list); } } }2.查看控制台執行結果
2019-08-29 16:36:04.684 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Starting MultiDataSourceServiceTest on YHE6OR5UXQJ6D35 with PID 13028 (started by Administrator in E:projectspring-boot-demomybatis-multi-datasource) 2019-08-29 16:36:04.685 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : No active profile set, falling back to default profiles: default 2019-08-29 16:36:08.172 INFO 13028 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor' 2019-08-29 16:36:08.814 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : Started MultiDataSourceServiceTest in 4.85 seconds (JVM running for 5.918) 2019-08-29 16:36:09.008 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 開始執行insert方法，id=20 2019-08-29 16:36:09.018 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切換到master庫 2019-08-29 16:36:09.054 INFO 13028 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2019-08-29 16:36:09.256 INFO 13028 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. 2019-08-29 16:36:09.547 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 開始執行updateById方法，id=20 2019-08-29 16:36:09.548 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切換到master庫 2019-08-29 16:36:09.731 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 開始執行selectById方法，id=20 2019-08-29 16:36:09.732 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切換到master庫 2019-08-29 16:36:10.213 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 數據為==User(id=20, name=小羊update, age=3, [email protected]) 2019-08-29 16:36:10.216 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 開始執行deleteById方法，id=20 2019-08-29 16:36:10.216 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切換到master庫 2019-08-29 16:36:10.402 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 開始執行selectList方法，index=0 2019-08-29 16:36:10.403 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave庫訪問執行緒數==>0 2019-08-29 16:36:10.403 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切換到slave2庫 2019-08-29 16:36:10.405 INFO 13028 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Starting... 2019-08-29 16:36:10.418 INFO 13028 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Start completed. 2019-08-29 16:36:10.422 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 查詢到的數據為，list=[User(id=1, name=Jone, age=18, [email protected]), User(id=2, name=mp, age=null, [email protected]), User(id=5, name=Billie, age=24, [email protected])] 2019-08-29 16:36:10.422 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 開始執行selectList方法，index=1 2019-08-29 16:36:10.422 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave庫訪問執行緒數==>1 2019-08-29 16:36:10.422 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切換到slave1庫 2019-08-29 16:36:10.422 INFO 13028 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Starting... 2019-08-29 16:36:10.428 INFO 13028 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Start completed. 2019-08-29 16:36:10.429 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 查詢到的數據為，list=[User(id=1, name=Jone, age=18, [email protected]), User(id=2, name=mp, age=null, [email protected]), User(id=5, name=Billie, age=24, [email protected])] 2019-08-29 16:36:10.429 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 開始執行selectList方法，index=2 2019-08-29 16:36:10.429 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave庫訪問執行緒數==>2 2019-08-29 16:36:10.429 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切換到slave2庫 2019-08-29 16:36:10.431 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 查詢到的數據為，list=[User(id=1, name=Jone, age=18, [email protected]), User(id=2, name=mp, age=null, [email protected]), User(id=5, name=Billie, age=24, [email protected])] 2019-08-29 16:36:10.431 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 開始執行selectList方法，index=3 2019-08-29 16:36:10.431 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave庫訪問執行緒數==>3 2019-08-29 16:36:10.431 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切換到slave1庫 2019-08-29 16:36:10.432 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 查詢到的數據為，list=[User(id=1, name=Jone, age=18, [email protected]), User(id=2, name=mp, age=null, [email protected]), User(id=5, name=Billie, age=24, [email protected])] 2019-08-29 16:36:10.432 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 開始執行selectList方法，index=4 2019-08-29 16:36:10.433 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : slave庫訪問執行緒數==>4 2019-08-29 16:36:10.433 INFO 13028 --- [ main] c.e.m.multidatasource.config.DBContext : 切換到slave2庫 2019-08-29 16:36:10.435 INFO 13028 --- [ main] c.e.m.m.MultiDataSourceServiceTest : 查詢到的數據為，list=[User(id=1, name=Jone, age=18, [email protected]), User(id=2, name=mp, age=null, [email protected]), User(id=5, name=Billie, age=24, [email protected])] 2019-08-29 16:36:10.444 INFO 13028 --- [ Thread-2] o.s.s.concurrent.ThreadPoolTaskExecutor : Shutting down ExecutorService 'applicationTaskExecutor' 2019-08-29 16:36:10.446 INFO 13028 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Shutdown initiated... 2019-08-29 16:36:10.463 INFO 13028 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Shutdown completed. 2019-08-29 16:36:10.463 INFO 13028 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Shutdown initiated... 2019-08-29 16:36:10.497 INFO 13028 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Shutdown completed. 2019-08-29 16:36:10.497 INFO 13028 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated... 2019-08-29 16:36:10.500 INFO 13028 --- [ Thread-2] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.- 我們看到service的insert,updateById,deleteById方法執行的是可寫庫(master庫)
- 而selectById方法，因為我們在service使用註解的方式手動切到了master庫，所以數據不會去slave庫讀取了（用來解決有些開發不規範，查詢介面帶有寫庫方法的情況）
- selectList方法，我們循環調用了5次，看到結果成功在slave1和slave2輪詢讀取了
資料
- mybatis-multi-datasource 示例源碼
- 官網數據源配置文檔
- 參考資料
- 碼雲倉庫 Spring Boot、Spring Cloud示例學習
- GitHub倉庫 Spring Boot、Spring Cloud示例學習
